如何将此查询用作MSSQL | 如何将此查询用作MSSQL

本文介绍了如何将此查询用作MSSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

"SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due],Remarks from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between #" + dtpInvoiceDateFrom.Text + "# And #" + dtpInvoiceDateTo.Text + "# order by InvoiceDate desc", con);

推荐答案

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due],Remarks from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between @FROM And @TO", con))
        {
        cmd.Parameters.AddWithValue("@FROM", dtpInvoiceDateFrom.Value);
        cmd.Parameters.AddWithValue("@TO", dtpInvoiceDateTo.Value);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                ...
                }
            }
        }
    }

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
StringBuilder sb = new StringBuilder();
            sb.Append("SELECT invoiceNo as [Invoice No],InvoiceDate as [Invoice Date] ");
            sb.Append(" ,Sales.CustomerID as [Customer ID],CustomerName as [Customer Name] ");
            sb.Append(" ,SubTotal as [SubTotal],VATPercentage as [Vat+ST %],VATAmount as [VAT+ST Amount] ");
			sb.Append(" ,GrandTotal as [Grand Total],TotalPayment as [Total Payment] ");
            sb.Append(" ,PaymentDue as [Payment Due],Remarks ");
			sb.Append(" from Sales,Customer ");
            sb.AppendFormat(" Sales.CustomerID= {0}", CustomerID);
            sb.AppendFormat(" AND InvoiceDate BETWEEN '{0}' AND '{1}' ",startDate,endDate ); 

    sqlCommand command = new sqlCommand (sb, con );
                sqlAdapter dataAdapter = new sqlAdapter (command);

                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "t");

                connection.Close();

                return dataSet;
    }

这篇关于如何将此查询用作MSSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！