使用字符串SQL插入数据时tblTransaction表插入失败问题

执行数据插入操作时，其他数据库表均可成功插入数据，但tblTransaction表始终无法插入。tblCustomerPayment表的插入语句可正常工作。

相关代码 #

SaleEntry.cs 完整代码 #

public class SaleEntry
{
    private CloudErpVEntities db = new CloudErpVEntities();

    public string selectcustomerid = string.Empty;
    DataTable dtEntries = null;
    public string ConfirmSale(int CompanyID, int BranchID, int UserID, string InvoiceNo, string CustomerInvoiceID, float Amount, string CustomerID, string Customername, bool isPayment)
    {
        try
        {
            dtEntries = null;
            string saletitle = "Sale To " + Customername.Trim();
            var financialCheck = DatabaseQuery.Retrive("select top 1 FinancialYearID from tblFinancialYear where IsActive = 1");
            string FinancialYearID = (financialCheck != null ? Convert.ToString(financialCheck.Rows[0][0]) : string.Empty);
            if (string.IsNullOrEmpty(FinancialYearID))
            {
                return "Your Company Financial Year is not Set Please Contact to Adminstrator!";
            }

            string successmessage = "Sale Success";

            string AccountHeadID = string.Empty;
            string AccountControlID = string.Empty;
            string AccountSubControlID = string.Empty;

            var saleAccount = db.tblAccountSettings.Where(a => a.AccountActivityID == 5 && a.CompanyID == CompanyID && a.BranchID == BranchID).FirstOrDefault();
            //Credit Entry Sale
            AccountHeadID = Convert.ToString(saleAccount.AccountHeadID);
            AccountControlID = Convert.ToString(saleAccount.AccountControlID);
            AccountSubControlID = Convert.ToString(saleAccount.AccountSubControlID);

            string transectiontitle = string.Empty;

            transectiontitle = "Sale To " + Customername.Trim();
            //SetEntries(FinancialYearID, AccountHeadID, AccountControlID, AccountSubControlID, InvoiceNo, UserID.ToString(), "0", Convert.ToString(Amount), DateTime.Now, transectiontitle);
            SetEntries(FinancialYearID, AccountHeadID, AccountSubControlID, AccountSubControlID, CompanyID.ToString(), BranchID.ToString(), InvoiceNo, UserID.ToString(), Convert.ToString(Amount), "0", DateTime.Now, transectiontitle);


            //Debit Entry Sale
            saleAccount = db.tblAccountSettings.Where(a => a.AccountActivityID == 8 && a.CompanyID == CompanyID && a.BranchID == BranchID).FirstOrDefault();
            if (saleAccount == null)
            {
                return "Sale Account (i.e. Sale Payment Pending/Succeed) not found in your <b>Account Flow</b>.";
            }
            AccountHeadID = Convert.ToString(saleAccount.AccountHeadID);
            AccountControlID = Convert.ToString(saleAccount.AccountControlID);
            AccountSubControlID = Convert.ToString(saleAccount.AccountSubControlID);

            transectiontitle = Customername.Trim() + " , Sale Payment is Pending!";
            //SetEntries(FinancialYearID, AccountHeadID, AccountControlID, AccountSubControlID, InvoiceNo, UserID.ToString(), "0", Convert.ToString(Amount), DateTime.Now, transectiontitle);
            SetEntries(FinancialYearID, AccountHeadID, AccountSubControlID, AccountSubControlID, CompanyID.ToString(), BranchID.ToString(), InvoiceNo, UserID.ToString(), "0", Convert.ToString(Amount),  DateTime.Now, transectiontitle);

            if (isPayment == true)
            {
                string payinvoicenno = "INP" + DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond;

                saleAccount = db.tblAccountSettings.Where(a => a.AccountActivityID == 8 && a.CompanyID == CompanyID && a.BranchID == BranchID).FirstOrDefault();
                AccountHeadID = Convert.ToString(saleAccount.AccountHeadID);
                AccountControlID = Convert.ToString(saleAccount.AccountControlID);
                AccountSubControlID = Convert.ToString(saleAccount.AccountSubControlID);

                transectiontitle = "Sale Payment Paid By " + Customername;
                SetEntries(FinancialYearID, AccountHeadID, AccountSubControlID, AccountSubControlID, CompanyID.ToString(), BranchID.ToString(), payinvoicenno, UserID.ToString(), Convert.ToString(Amount), "0", DateTime.Now, transectiontitle);

                saleAccount = db.tblAccountSettings.Where(a => a.AccountActivityID == 12 && a.CompanyID == CompanyID && a.BranchID == BranchID).FirstOrDefault();
                AccountHeadID = Convert.ToString(saleAccount.AccountHeadID);
                AccountControlID = Convert.ToString(saleAccount.AccountControlID);
                AccountSubControlID = Convert.ToString(saleAccount.AccountSubControlID);
                transectiontitle = Customername + " , Sale Payment is Succesed!";
                SetEntries(FinancialYearID, AccountHeadID, AccountControlID, AccountSubControlID, CompanyID.ToString(), BranchID.ToString(), payinvoicenno, UserID.ToString(), "0", Convert.ToString(Amount), DateTime.Now, transectiontitle);

                string paymentquery = string.Format("insert into tblCustomerPayment(CustomerID,CustomerInvoiceID,UserID,invoiceNo,TotalAmount,PaidAmount,RemainingBalance,CompanyID,BranchID) " +
                "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                CustomerID, CustomerInvoiceID, UserID, payinvoicenno, Amount, Amount, "0", CompanyID, BranchID);
                DatabaseQuery.Insert(paymentquery);

                successmessage = successmessage + " with Payment.";
            }

            foreach (DataRow entryrow in dtEntries.Rows)
            {
                string entryquery = string.Format("insert into tblTransaction(FinancialYearID,AccountHeadID,AccountControlID,AccountSubControlID,UserID,InvoiceNo,Credit,Debit,TransectionDate,TransactionTitle,CompanyID,BranchID) " +
                                    "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')",
                                    Convert.ToString(entryrow[0]), Convert.ToString(entryrow[1]), Convert.ToString(entryrow[2]), Convert.ToString(entryrow[3]), Convert.ToString(entryrow[4]), Convert.ToString(entryrow[5]), Convert.ToString(entryrow[6]), Convert.ToString(entryrow[7]), (entryrow[8]), Convert.ToString(entryrow[9]), CompanyID, BranchID);
                DatabaseQuery.Insert(entryquery);
            }

            return successmessage;
        }
        catch (Exception ex)
        {
            return $"Unexpected Error: {ex.Message}. Please try Again!";
        }
    }

    private void SetEntries(
        string FinancialYearID, string AccountControlID,
        string AccountSubControlID, string AccountHeadID,
        string CompanyID, string BranchID, string InvoiceNo,
        string UserID, string Credit, string Debit,
        DateTime TransacctionDate, string TransactionTitle)
    {
        if (dtEntries == null)
        {
            dtEntries = new DataTable();
            dtEntries.Columns.Add("FinancialYearID");
            dtEntries.Columns.Add("AccountControlID");
            dtEntries.Columns.Add("AccountSubControlID");
            dtEntries.Columns.Add("AccountHeadID");
            dtEntries.Columns.Add("CompanyID");
            dtEntries.Columns.Add("BranchID");
            dtEntries.Columns.Add("InvoiceNo");
            dtEntries.Columns.Add("UserID");
            dtEntries.Columns.Add("Credit");
            dtEntries.Columns.Add("Debit");
            dtEntries.Columns.Add("TransacctionDate");
            dtEntries.Columns.Add("TransactionTitle");
        }

        if (dtEntries != null)
        {
            dtEntries.Rows.Add(
                FinancialYearID,
                AccountControlID,
                AccountSubControlID,
                AccountHeadID,
                CompanyID,
                BranchID,
                InvoiceNo,
                UserID,
                Credit,
                Debit,
                TransacctionDate,
                TransactionTitle);

        }
    }
}

插入tblTransaction的SQL逻辑 #

foreach (DataRow entryrow in dtEntries.Rows)
{
    string entryquery = string.Format("insert into tblTransaction(FinancialYearID,AccountHeadID,AccountControlID,AccountSubControlID,UserID,InvoiceNo,Credit,Debit,TransectionDate,TransactionTitle,CompanyID,BranchID) " +
                        "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')",
                        Convert.ToString(entryrow[0]), Convert.ToString(entryrow[1]), Convert.ToString(entryrow[2]), Convert.ToString(entryrow[3]), Convert.ToString(entryrow[4]), Convert.ToString(entryrow[5]), Convert.ToString(entryrow[6]), Convert.ToString(entryrow[7]), (entryrow[8]), Convert.ToString(entryrow[9]), CompanyID, BranchID);
    DatabaseQuery.Insert(entryquery);
}

问题排查与修复方案 #

1. 参数顺序完全错位（核心原因） #

• SetEntries调用参数错误：调用SetEntries时，参数顺序与方法定义不匹配。例如第一次调用：

  SetEntries(FinancialYearID, AccountHeadID, AccountSubControlID, AccountSubControlID, ...)
  

  方法定义的第二个参数是AccountControlID，第三个是AccountSubControlID，第四个是AccountHeadID，但代码中传入的是AccountHeadID作为第二个参数，且重复传入AccountSubControlID，导致DataTable中存储的账户字段完全混乱。
  正确调用应为：

  SetEntries(FinancialYearID, AccountControlID, AccountSubControlID, AccountHeadID, CompanyID.ToString(), BranchID.ToString(), InvoiceNo, UserID.ToString(), Convert.ToString(Amount), "0", DateTime.Now, transectiontitle);
  

• 插入SQL字段与DataTable列不匹配：插入SQL的字段顺序是FinancialYearID,AccountHeadID,AccountControlID,AccountSubControlID，但DataTable的列顺序是FinancialYearID, AccountControlID, AccountSubControlID, AccountHeadID，导致插入时字段值完全错位，必然触发数据库约束（如外键、数据类型）报错。
  需调整SQL字段顺序与DataTable列一致，或修改DataTable列顺序匹配SQL。

2. 日期格式与SQL注入风险 #

• 直接拼接DateTime类型到SQL语句中，可能因数据库日期格式不兼容导致插入失败，建议改用参数化查询。
• 使用string.Format拼接SQL存在严重SQL注入风险，同时会引发数据类型转换问题，应替换为参数化查询，示例：

  string entryquery = "insert into tblTransaction(FinancialYearID,AccountHeadID,AccountControlID,AccountSubControlID,UserID,InvoiceNo,Credit,Debit,TransectionDate,TransactionTitle,CompanyID,BranchID) values(@FinancialYearID,@AccountHeadID,@AccountControlID,@AccountSubControlID,@UserID,@InvoiceNo,@Credit,@Debit,@TransectionDate,@TransactionTitle,@CompanyID,@BranchID)";
  // 需确保DatabaseQuery.Insert支持参数化，添加对应参数
  

3. 异常信息不足 #

当前catch块仅返回异常消息，无法获取详细堆栈信息，建议添加日志记录，方便排查：

catch (Exception ex)
{
    // 写入日志到文件/数据库，记录完整异常信息
    return $"Unexpected Error: {ex.Message}. StackTrace: {ex.StackTrace}. Please try Again!";
}

内容的提问来源于stack exchange，提问作者Timir Bhingradiya