如何使用参数转换为正确的数据类型。用SQL查询？

I am trying to save some values to data table in MS SQL , but i have some problem with the Data types . i need to use Parameters to convert those values to proper data types

我试图将一些值保存到MS SQL中的数据表,但我有一些数据类型的问题。我需要使用参数将这些值转换为正确的数据类型

this is my table ,,,

这是我的桌子,,,

Table image

this is the code that i'm using to pass values to the database ,, i need to use Parameters to convert data, please help me to do it ...

这是我用来将值传递给数据库的代码,我需要使用参数来转换数据,请帮我做...

private void InsertRec(StringCollection sc)
    {
        var conn = new SqlConnection(GetConnectionString());
        var sb = new StringBuilder(string.Empty);
        var splitItems = (string[])null;
        foreach (string item in sc)
        {
            const string sqlStatement =
                "INSERT INTO DEL_PurchasesLines1 (DealerCode,InvoiceNo,InvoiceDate,ItemIdentityCode,PurchasingPrice,DiscountRate,Discount,IssueMode,Qty,Total,ExpireDate,BatchNumber,UploadedStatus,InsertedDate,UploadedDate,Force,Principle,NewTotal) VALUES";

            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
                sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8], splitItems[9], splitItems[10], splitItems[11], splitItems[12], splitItems[13], splitItems[14], splitItems[15], splitItems[16], splitItems[17]);
            }
        }

        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn) { CommandType = CommandType.Text };
            cmd.ExecuteNonQuery();

            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }
    }

this is how i take the values in to query.

这就是我如何将值用于查询。

    protected void btnSaave_Click(object sender, EventArgs e)
    {
        int rowIndex = 0;
        StringCollection sc = new StringCollection();
        if (ViewState["CurrentData"] != null)
        {
            DataTable dtCurrentTable = (DataTable)ViewState["CurrentData"];
            DataRow drCurrentRow = null;
            if (dtCurrentTable.Rows.Count > 0)
            {
                for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                {
                    var dtDealerCode = txtIDealerCode.Text;
                    var dtInvoiceNo = txtInvoiceNumber.Text;
                    var dtInvoiceDate = txtInvoiceDate.Text;
                    var dtItemIdentityCode = (Label)GridView1.Rows[rowIndex].Cells[1].FindControl("ItemCode");
                    var dtPurchasingPrice = (Label)GridView1.Rows[rowIndex].Cells[3].FindControl("UnitPrice");
                    var dtDiscountRate = txtDiscount.Text;
                    var dtDiscount = txtProductDiscount.Text;
                    var dtIssueMode = ddlIssueMode.SelectedValue;
                    var dtQty = (Label)GridView1.Rows[rowIndex].Cells[6].FindControl("Quantity");
                    var dtTotal = (Label)GridView1.FooterRow.FindControl("GetTotal");
                    var dtExpireDate = (Label)GridView1.Rows[rowIndex].Cells[5].FindControl("ExpiaryDate");
                    var dtBatchNumber = (Label)GridView1.Rows[rowIndex].Cells[4].FindControl("Batch");
                    var dtUploadedStatus = txtInvoiceDate.Text;
                    var dtInsertedDate = "1";
                    var dtUploadedDate = txtInvoiceDate.Text;
                    var dtForce = txtForce.Text;
                    var dtPrinciple = txtPrinciple.Text;
                    var NewTotal = (Label)GridView1.FooterRow.FindControl("GetQuantity");

                    sc.Add(dtDealerCode + "," + dtInvoiceNo + "," + dtInvoiceDate + "," + dtItemIdentityCode.Text + "," + dtPurchasingPrice.Text + "," + dtDiscountRate + "," + dtDiscount + "," + dtIssueMode + "," + dtQty.Text + "," + dtTotal.Text + "," + dtExpireDate + "," + dtBatchNumber.Text + "," + dtUploadedStatus + "," + dtInsertedDate + "," + dtUploadedDate + "," + dtForce + "," + dtPrinciple + "," + dtPrinciple + "," + NewTotal.Text);
                    rowIndex++;
                }

                InsertRec(sc);
            }
        }
    }

2 个解决方案

#1

The correct way is to build a method that asks for the specific values it expects, either by having an parameter for each item or via a class type, ie either this:

正确的方法是构建一个方法,询问它所期望的特定值,方法是为每个项目或通过类类型提供参数,即:

private void InsertRec(DEL_PurchasesLines1 lineToInsert)

where DEL_PurchasesLines1 is a class you will create (named after the table), or this:

其中DEL_PurchasesLines1是您要创建的类(以表格命名),或者:

private void InsertRec(string DealerCode,string InvoiceNo,DateTime InvoiceDate,
            string ItemIdentityCode,decimal PurchasingPrice, decimal DiscountRate,
            decimal Discount,string IssueMode,decimal Qty,decimal Total,
            DateTime ExpireDate,string BatchNumber,string UploadedStatus,
            string InsertedDate,DateTime UploadedDate,string Force,
            decimal Principle,decimal NewTotal)

FWIW, that many columns in a single table almost always means something is wrong with the table design, but I'll leave that alone for now. Going with the second method, your button click would look like this:

FWIW,单个表中的许多列几乎总是意味着表设计有问题,但我现在暂时不管它。使用第二种方法,您的按钮单击将如下所示:

protected void btnSave_Click(object sender, EventArgs e)
{
    if (ViewState["CurrentData"] == null) return;
    DataTable dtCurrentTable = (DataTable)ViewState["CurrentData"];
    if (dtCurrentTable.Rows.Count == 0) return;

    int rowIndex = 0;

    var dtDealerCode = txtIDealerCode.Text;
    var dtInvoiceNo = txtInvoiceNumber.Text;
    var dtInvoiceDate = DateTime.Parse(txtInvoiceDate.Text);
    var dtDiscountRate = decimal.Parse(txtDiscount.Text);
    var dtDiscount = decimal.Parse(txtProductDiscount.Text);
    var dtIssueMode = ddlIssueMode.SelectedValue;
    var dtUploadedStatus = DateTime.Parse(txtInvoiceDate.Text);
    var dtInsertedDate = "1"; //Really? 
    var dtUploadedDate = DateTime.Parse(txtInvoiceDate.Text);
    var dtForce = txtForce.Text;
    var dtPrinciple = decimal.Parse(txtPrinciple.Text);

    for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
    {
        var dtItemIdentityCode = (Label)GridView1.Rows[rowIndex].Cells[1].FindControl("ItemCode");
        var dtPurchasingPrice = decimal.Parse((Label)GridView1.Rows[rowIndex].Cells[3].FindControl("UnitPrice"));
        var dtQty = decimal.Parse((Label)GridView1.Rows[rowIndex].Cells[6].FindControl("Quantity"));
        var dtTotal = decimal.Parse((Label)GridView1.FooterRow.FindControl("GetTotal"));
        var dtExpireDate = DateTime.Parse((Label)GridView1.Rows[rowIndex].Cells[5].FindControl("ExpiaryDate"));
        var dtBatchNumber = (Label)GridView1.Rows[rowIndex].Cells[4].FindControl("Batch");
        var NewTotal = decimal.Parse((Label)GridView1.FooterRow.FindControl("GetQuantity"));

        InsertRec(dtDealerCode,dtInvoiceNo,dtInvoiceDate,dtItemIdentityCode,
            dtPurchasingPrice,dtDiscountRate,dtDiscount,dtIssueMode,dtQty,
            dtTotal,dtExpireDate,dtBatchNumber,dtUploadedStatus,dtInsertedDate,
            dtUploadedDate,dtForce,dtPrinciple,NewTotal);

        rowIndex++;
    }
}

To bring it all together, InsertRec() will look like this:

要将它们组合在一起,InsertRec()将如下所示:

private void InsertRec(string DealerCode,string InvoiceNo,DateTime InvoiceDate,
            string ItemIdentityCode,decimal PurchasingPrice, decimal DiscountRate,
            decimal Discount,string IssueMode,decimal Qty,decimal Total,
            DateTime ExpireDate,string BatchNumber,string UploadedStatus,
            string InsertedDate,DateTime UploadedDate,string Force,
            decimal Principle,decimal NewTotal)
{

    const string sqlStatement =
            "INSERT INTO DEL_PurchasesLines1 (" +
              "DealerCode,InvoiceNo,InvoiceDate,ItemIdentityCode,PurchasingPrice,DiscountRate,Discount,IssueMode,Qty,Total,ExpireDate,BatchNumber,UploadedStatus,InsertedDate,UploadedDate,Force,Principle,NewTotal" + 
            ") VALUES (" +
              "@DealerCode, @InvoiceNo, @InvoiceDate, @ItemIdentityCode, @PurchasingPrice, @DiscountRate, @Discount, @IssueMode, @Qty, @Total, @ExpireDate, @BatchNumber, @UploadSTatus, @InsertedDate, @UploadedDate, @Force, @Principle, @NewTotal" +
            ")";

    using (conn = new SqlConnection(GetConnectionString())
    using (cmd = new SqlCommand(sqlStatement, conn))
    {
        cmd.Parameters.Add("@DealerCode", SqlDbType.NVarChar, 10).Value = DealerCode;
        cmd.Parameters.Add("@InvoiceNo", SqlDbType.NVarChar, 10).Value = InvoiceNo;
        cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = InvoiceDate;
        cmd.Parameters.Add("@ItemIdentityCode", SqlDbType.NVarChar, 10).Value = ItemIdentityCode;
        cmd.Parameters.Add("@PurchasingPrice", SqlDbType.Decimal).Value = PurchasingPrice;
        cmd.Parameters.Add("@DiscountRate", SqlDbType.Decimal).Value = DiscountRate;
        //...

        conn.Open();
        cmd.ExecuteNonQuery();
    }

    Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
}

What you were doing with the StringBuilder and Format strings there was totally inappropriate, thanks to a little thing called Sql Injection. It's kind of important, so you should definitely do some reading about it.

由于一个名为Sql Injection的小东西,你使用StringBuilder和Format字符串做了什么是完全不合适的。这很重要,所以你一定要做一些关于它的阅读。

I also see that you're trying to group the inserts into a single batch. That's nice, but thanks to connection pooling it doesn't really get you much. It's also harder to do with correct query parameterization. It's not impossible, though, so I'll show you an example now using the other method signature, which makes it a bit easier:

我还看到您正在尝试将插入分组到一个批处理中。这很好,但是由于连接池,它并没有真正让你受益匪浅。使用正确的查询参数化也更难。但是,这并非不可能,所以我现在将使用其他方法签名向您展示一个示例,这使得它更容易:

private void InsertRec(DEL_PurchasesLines1[] linesToInsert)
{
    const string sqlBase =
            "INSERT INTO DEL_PurchasesLines1 (" +
              "(DealerCode,InvoiceNo,InvoiceDate,ItemIdentityCode,PurchasingPrice,DiscountRate,Discount,IssueMode,Qty,Total,ExpireDate,BatchNumber,UploadedStatus,InsertedDate,UploadedDate,Force,Principle,NewTotal)" + 
            " VALUES ";

    const string valueBase = 
              "{0}(@DealerCode{1}, @InvoiceNo{1}, @InvoiceDate{1}, @ItemIdentityCode{1}, @PurchasingPrice{1}, @DiscountRate{1}, @Discount{1}, @IssueMode{1}, @Qty{1}, @Total{1}, @ExpireDate{1}, @BatchNumber{1}, @UploadSTatus{1}, @InsertedDate{1}, @UploadedDate{1}, @Force{1}, @Principle{1}, @NewTotal{1})";

    var sb = new StringBuilder(sqlBase);
    if (DEL_PurchasesLines1.Length > 1) sb.Append("(")
    var delimiter = "";
    for (int i = 0;i<DEL_PurchasesLines1.Length;i++)
    {
         sb.AppendFormat(valueBase, i, delimiter);
         delimiter = ",";
    }
    if (DEL_PurchasesLines1.Length > 1) sb.Append(")")

    using (conn = new SqlConnection(GetConnectionString())
    using (cmd = new SqlCommand(sqlStatement, conn))
    {

        for (int i = 0;i<DEL_PurchasesLines1.Length;i++)
        {
            cmd.Parameters.Add("@DealerCode" + i, SqlDbType.NVarChar, 10).Value = linesToInsert[i].DealerCode;
            cmd.Parameters.Add("@InvoiceNo" + i, SqlDbType.NVarChar, 10).Value = linesToInsert[i].InvoiceNo;
            cmd.Parameters.Add("@InvoiceDate + i", SqlDbType.DateTime).Value = linesToInsert[i].InvoiceDate;
            cmd.Parameters.Add("@ItemIdentityCode" + i, SqlDbType.NVarChar, 10).Value = linesToInsert[i].ItemIdentityCode;
            cmd.Parameters.Add("@PurchasingPrice" + i, SqlDbType.Decimal).Value = linesToInsert[i].PurchasingPrice;
            cmd.Parameters.Add("@DiscountRate" + i, SqlDbType.Decimal).Value = linesToInsert[i].DiscountRate;
        //...
        }
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

#2

This is kind of some nasty code. I don't know if you are new to programming but you should use a Stored Procedure for this where the procedure defines the 14 or so parameters that need to be passed in as arguments. This will guide your code so that you add parameters for the parameterized stored procedure call, each argument will need to be strongly typed and this will guide you to make the right conversions for each column value of your table. You should also create a C# class that defines, as public properties all your table column values. Excuse the VB.NET but you will get the idea with this simple Data Access Layer (D.A.L) example snippet, the UpdateOffer could be renamed AddUpdateOffer and this could check to see whether the record exists or not by passing in the record id to the AddUpdate stored procedure if it is an update, if the record id is null the stored procedure knows to do an insert else update:

这是一些讨厌的代码。我不知道你是不熟悉编程,但你应该使用一个存储过程,其中过程定义了需要作为参数传递的14个左右的参数。这将指导您的代码,以便为参数化存储过程调用添加参数,每个参数都需要进行强类型化,这将指导您为表的每个列值进行正确的转换。您还应该创建一个C#类,将所有表列值定义为公共属性。请原谅,VB.NET,但你会得到的想法用这个简单的数据访问层(DAL)例如片段中,UpdateOffer可以改名AddUpdateOffer,这可以检查看到的记录ID传递给AddUpdate记录是否存在存储过程如果是更新,如果记录标识为空,则存储过程知道插入其他更新:

 Public Class Offer

        Public OfferID As Integer
        Public PropertyID As Integer
        Public AgentUserID As Integer
        Public AgentName As String
        Public BuyerUserID As Integer
        Public BuyerName As String
        Public BuyerType As String
        Public Offer As Integer
        Public NetOffer As Integer
        Public ClosingCost As Integer
        Public Allowances As Integer
        Public RepairCosts As Integer
        Public TotalCredits As Integer
        Public OfferType As String
        Public OfferDate As String
        Public ProxyOffer As Integer
        Public NetProxyOffer As Integer
        Public ResultResponse As SUBMIT_OFFER_RESULT
        'Public ResultAcceptedOffer As Integer
        Public ResultAcceptedNetOffer As Integer
        'Public ResultHighestOffer As Integer
        Public ResultHighestNetOffer As Integer
        Public Notifications As ArrayList = New ArrayList
        Public EarnestMoneyDeposit As Integer
        Public DownPayment As Integer
        Public TypeOfFinancing As String
        Public OfferStatus As String
        Public Note As String
        Public Visble As Boolean = True
        Public OfferStatusChangedDate As DateTime
        Public EstimatedCloseDate As DateTime
        Public SourceType As String

        Public Sub GetOffer(ByVal offerID As Integer)

            Dim offerDB As OSP.DataAccess.OfferDB = New OSP.DataAccess.OfferDB
            Dim rs As SqlClient.SqlDataReader
            rs = offerDB.GetOffer(offerID)
            Do While rs.Read
                Me.OfferID = offerID
                Me.PropertyID = rs("PROPERTY_ID")
                Me.AgentUserID = rs("AGENT_USER_ID")
                Me.BuyerUserID = IIf(IsDBNull(rs("BUYER_USER_ID")), 0, rs("BUYER_USER_ID"))
                Me.Offer = rs("OFFER")
                Me.NetOffer = rs("NET_OFFER")
                Me.TotalCredits = rs("TOTAL_CREDITS")
                Me.ProxyOffer = rs("PROXY_OFFER")
                Me.OfferType = rs("OFFERTYPE")
                Me.OfferDate = rs("OFFER_DATE")
                Me.DownPayment = IIf(IsDBNull(rs("DOWN_PAYMENT")), 0, rs("DOWN_PAYMENT"))
                Me.EarnestMoneyDeposit = IIf(IsDBNull(rs("EARNEST_MONEY_DEPOSIT")), 0, rs("EARNEST_MONEY_DEPOSIT"))
                Me.TypeOfFinancing = rs("TYPE_OF_FINANCING")
                Me.BuyerName = GlobalFunctions.DefaultString(rs("BUYER_NAME"))
                Me.BuyerType = GlobalFunctions.DefaultString(rs("BUYER_TYPE"))
                Me.AgentName = rs("OFFER_BY_NAME")
                Me.OfferStatus = GlobalFunctions.DefaultString(rs("OFFER_STATUS"))
                Me.Note = GlobalFunctions.DefaultString(rs("NOTE"))
                Me.OfferStatusChangedDate = IIf(IsDBNull(rs("OFFER_STATUS_CHANGED_DATE")), Me.OfferStatusChangedDate, rs("OFFER_STATUS_CHANGED_DATE"))
                Me.Visble = IIf(IsDBNull(rs("VISIBLE")), True, rs("VISIBLE"))
                Me.EstimatedCloseDate = IIf(IsDBNull(rs("ESTIMATED_CLOSE_DATE")), DateTime.MinValue, rs("ESTIMATED_CLOSE_DATE"))
            Loop

            Try
                If Not rs.IsClosed Then
                    rs.Close() : rs = Nothing
                End If
                If Not offerDB Is Nothing Then
                    offerDB.Dispose() : offerDB = Nothing
                End If
            Catch : End Try

        End Sub

        Public Function UpdateOffer() As Integer

            Dim offerDB As OSP.DataAccess.OfferDB = New OSP.DataAccess.OfferDB
            Return offerDB.UpdateOffer(Me)

        End Function
End Class

 Public Class OfferDB
        Implements System.IDisposable


        Dim db As SQLDatabase

        Public Sub New()
            db = New SQLDatabase(GlobalSettings.GetDefaultConnectionString)
        End Sub

        Public Sub Dispose() Implements System.IDisposable.Dispose

            If Not db Is Nothing Then
                db = Nothing
            End If

        End Sub

  Public Function GetOffer(ByVal offerID As Integer) As SqlClient.SqlDataReader

            Dim dbCommand As DbCommand = db.GetStoredProcCommand("OSP_GET_OFFER")
            db.AddInParameter(dbCommand, "@OFFER_ID", SqlDbType.Int, offerID)

            Try
                Return db.ExecuteReader(dbCommand)
            Catch ex As Exception
                Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "EXCEPTION_CRITICAL")
                If (rethrow) Then
                    Throw
                End If
            End Try


        End Function


        Public Function UpdateOffer(ByVal offer As OSP.Offer) As Integer

            Dim dbCommand As DbCommand = db.GetStoredProcCommand("OSP_UPDATE_OFFER")

            db.AddInParameter(dbCommand, "@OFFER_ID", SqlDbType.Int, offer.OfferID)
            db.AddInParameter(dbCommand, "@BUYER_USER_ID", SqlDbType.Int, offer.BuyerUserID)
            db.AddInParameter(dbCommand, "@OFFER", SqlDbType.Int, offer.Offer)
            db.AddInParameter(dbCommand, "@TOTAL_CREDITS", SqlDbType.Int, offer.TotalCredits)
            db.AddInParameter(dbCommand, "@OFFER_TYPE", SqlDbType.VarChar, offer.OfferType)
            db.AddInParameter(dbCommand, "@OFFER_DATE", SqlDbType.VarChar, offer.OfferDate)
            db.AddInParameter(dbCommand, "@TYPE_OF_FINANCING", SqlDbType.VarChar, offer.TypeOfFinancing)
            db.AddInParameter(dbCommand, "@DOWN_PAYMENT", SqlDbType.Int, offer.DownPayment)
            db.AddInParameter(dbCommand, "@EARNEST_MONEY_DEPOSIT", SqlDbType.Int, offer.EarnestMoneyDeposit)
            db.AddInParameter(dbCommand, "@OFFER_STATUS", SqlDbType.VarChar, offer.OfferStatus)
            db.AddInParameter(dbCommand, "@NOTE", SqlDbType.VarChar, offer.Note)
            If Not offer.OfferStatusChangedDate = DateTime.MinValue Then
                db.AddInParameter(dbCommand, "@OFFER_STATUS_CHANGED_DATE", SqlDbType.DateTime, offer.OfferStatusChangedDate)
            End If

            Try
                Return db.ExecuteScalar(dbCommand)
            Catch ex As Exception
                Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "EXCEPTION_CRITICAL")
                If (rethrow) Then
                    Throw
                End If
            End Try

        End Function
End Class