在SqlBulkCopy中添加自己的值

本文关键字：自己的 添加 SqlBulkCopy | 更新日期: 2023-09-27 17:50:32

我目前正在从xml文件中提取数据并将其复制到数据库中存储。是否有一种方法可以让我在SqlBulkCopy期间添加自己的值?我想添加我自己的值itemsTotalPrice，因为我想计算物品的总价格，但无法使其工作。我该怎么解决这个问题?

 DataTable sourceData = ds.Tables["Invoice"];
 sourceData.Columns.Add("itemsTotalPrice", typeof(System.Decimal));
 sourceData.Columns.Add("invoiceTotal", typeof(System.Decimal));
 sqlConn.Open();
       using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
            {
                sqlbc.DestinationTableName = "Invoice";
                sqlbc.ColumnMappings.Add("invoiceDate", "invoiceDate");
                sqlbc.ColumnMappings.Add("orderID", "orderID");
                sqlbc.ColumnMappings.Add("sellerID", "sellerID");
                sqlbc.ColumnMappings.Add("itemsTotalPrice", itemsTotalPrice);
                //sqlbc.ColumnMappings.Add("shippingCharges", "shippingCharges");
                sqlbc.ColumnMappings.Add("invoiceTotal", "invoiceTotal");
                sqlbc.WriteToServer(sourceData);
            }

在SqlBulkCopy中添加自己的值

您可以尝试将Xml加载到DataTable中，根据您的选择修改列中的值，并尝试使用WriteToServer

参考代码

if (FileUploadControl.HasFile)
        {

            try
            {
                if (FileUploadControl.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || FileUploadControl.PostedFile.ContentType == "application/vnd.ms-excel")
                {
                    if (FileUploadControl.PostedFile.ContentLength >= 0)
                    {
                        string filename = Path.GetFileNameWithoutExtension(FileUploadControl.FileName);
                        string fileExt = Path.GetExtension(FileUploadControl.FileName);
                        FileUploadControl.SaveAs(Server.MapPath("~/DownloadedExcelFilesOp4/myfile" + fileExt));
                        if (FileUploadControl.PostedFile.ContentLength == 0)
                        {
                            lblMsg.Text = fun.UploadStatus;
                            lblMsg.ForeColor = Color.Green;
                        }
                        else
                            lblMsg.Text = fun.UploadStatus;
                        lblMsg.ForeColor = Color.Green;
                        string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HRD=YES;IMEX=1'", Server.MapPath(@"~'DownloadedExcelFilesOp4'myfile" + fileExt));// + "''" + FileUploadControl.PostedFile.FileName.ToString());
                        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                        {
                            OleDbCommand command = new OleDbCommand(("Select [Customer] ,[InvoiceDate] , [InvoiceNo] , [CustomerPo],[SoLine] ,[VendorName] ,[Category] ,[Item] ,[PickQty] ,[Price] ,[PriceExtentio]  FROM [Sheet1$]"), connection);
                            connection.Open();
                            using (DbDataReader dr = command.ExecuteReader())
                            {
                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
                                {
                                    bulkCopy.DestinationTableName = "TempExamQuation4Master";
                                    bulkCopy.ColumnMappings.Add("Customer", "Customer");
                                    bulkCopy.ColumnMappings.Add("InvoiceDate", "InvoiceDate");
                                    bulkCopy.ColumnMappings.Add("InvoiceNo", "InvoiceNo");
                                    bulkCopy.ColumnMappings.Add("CustomerPo", "CustomerPo");
                                    bulkCopy.ColumnMappings.Add("SoLine", "SoLine");
                                    bulkCopy.ColumnMappings.Add("VendorName", "VendorName");
                                    bulkCopy.ColumnMappings.Add("Categor", "Categor");
                                    bulkCopy.ColumnMappings.Add("Item", "Item");
                                    bulkCopy.ColumnMappings.Add("PickQty", "PickQty");
                                    bulkCopy.ColumnMappings.Add("Price", "Price");
                                    bulkCopy.ColumnMappings.Add("PriceExtentio", "PriceExtentio");
                                    bulkCopy.WriteToServer(dr);
                                }
                            }
                        }
相关文章：