误差SqlBulkCopyColumnMapping [英] Error with SqlBulkCopyColumnMapping

问题描述

我能够导入Excel工作表数据导入SQL Server表，但我无法实施列映射。请帮忙。

I am able to import excel sheet data into sql server table but i am unable to implement column mapping. please help.

protected void Button1_Click(object sender, EventArgs e)
{
    string strFileType = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
    string strFileName = FileUpload1.PostedFile.FileName.ToString();

    FileUpload1.SaveAs(Server.MapPath("~/Import/" + strFileName + strFileType));
    string strNewPath = Server.MapPath("~/Import/" + strFileName + strFileType);






        string excelConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+strNewPath +"; Extended Properties=Excel 8.0;");

    //string excelConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\myFolder\\Book1.xls;" + "Extended Properties=Excel 8.0;"); 


        // Create Connection to Excel Workbook
        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
        {
            OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Sheet1$]", connection);

            connection.Open();

            // Create DbDataReader to Data Worksheet
            using (DbDataReader dr = command.ExecuteReader())
            {
                // SQL Server Connection String
                string sqlConnectionString = "Data Source=DITSEC3;Initial Catalog=test;Integrated Security=True";

                con.Open();
                DataTable dt1 = new DataTable();
                string s = "select count(*) from ExcelTable"; 
                string r = ""; 
                SqlCommand cmd1 = new SqlCommand(s, con);
                try 
                { 
                    SqlDataAdapter da1 = new SqlDataAdapter(cmd1); 
                    da1.Fill(dt1);
                }
                catch { } 
                int RecordCount; 
                RecordCount = Convert.ToInt32(cmd1.ExecuteScalar());
                r = RecordCount.ToString(); 
                Label1.Text = r;
                con.Close();
                int prv = Convert.ToInt32(r);





                // Bulk Copy to SQL Server
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {



                    bulkCopy.DestinationTableName = "ExcelTable";
                    bulkCopy.WriteToServer(dr);

                    con.Open();
                    SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("id", "ida");
                    SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("data", "dataa");
                    con.Close();

                }
                con.Open();



                DataTable dt = new DataTable(); 
                s = "select count(*) from ExcelTable";  r = ""; 
                SqlCommand cmd = new SqlCommand(s, con); 
                try { SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt); 
                }
                catch { } 
                RecordCount = Convert.ToInt32(cmd.ExecuteScalar()); 
                r = RecordCount.ToString(); Label1.Text = r;
                con.Close();
                int ltr = Convert.ToInt32(r);
                if (prv == ltr)
                {
                    Label1.Text = "No records Added";
                }
                else
                {
                    Label1.Text = "Records Added Successfully !";
                }

            }
        }

错误：
没有一个或多个所需的参数定值。

Error: No value given for one or more required parameters.

推荐答案

您创建，但并没有映射添加到 SqlBulkCopy的。

You created but didn't add the mappings to SqlBulkCopy.

添加低于code：

Add the code below:

bulkCopy.ColumnMappings.Add(mapping1);
bulkCopy.ColumnMappings.Add(mapping2);

这篇关于误差SqlBulkCopyColumnMapping的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！