1

I read through a terrific post from Mudassar Ahmed Khan at http://www.aspsnippets.com/Articles/Import-Upload-CSV-file-data-to-SQL-Server-database-in-ASPNet-using-C-and-VBNet.aspx. It covers how to upload an Excel file into a SQL Server database table using asp.net and C#.

It works just fine, but I would like to get it to work for a table where the Id column is auto incremented. I would like to know how to modify the code to allow users to upload the file without having to specify a value for the Id column.

Any help would be greatly appreciated.

I'm using the code provided in the aforementioned tutorial as-is - I didn't make any changes. I'm just trying to understand how before I attempt to apply it to my actual application.

Please let me know if I need to provide additional information.

Any help would be greatly appreciated.

Thanks, J

Here is the code I would like to modify:

protected void Upload(object sender, EventArgs e) {
   //Upload and save the file
   string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
   FileUpload1.SaveAs(excelPath);

   string conString = string.Empty;
   string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

   switch (extension)
   {
       case ".xls": //Excel 97-03
          conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
          break;

      case ".xlsx": //Excel 07 or higher
          conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
          break;
   }

   conString = string.Format(conString, excelPath);

   using (OleDbConnection excel_con = new OleDbConnection(conString))
   {
       excel_con.Open();
       string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
       DataTable dtExcelData = new DataTable();

       //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
       dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });

       using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
       {
           oda.Fill(dtExcelData);
       }

       excel_con.Close();

       string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

       using (SqlConnection con = new SqlConnection(consString))
       {
           using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
           {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");

               con.Open();
               sqlBulkCopy.WriteToServer(dtExcelData);
               con.Close();
           }
       }
    }
}

Here is the aspx page markup:

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick = "Upload" runat="server" />

Here are the Excel specific connection strings for Excel 2003 and Excel 2007 or higher formats:

<add name = "Excel03ConString" connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name = "Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1916528
  • 379
  • 4
  • 23
  • 3
    Don't add the Id field to the column mapping and set the column in SQL as an identity column which auto increments. But would this just be used for INSERT operations or would it also have to UPDATE the database based on the Name? – Sean Cox Nov 16 '15 at 02:35
  • I'm not sure but i think for bulkcopy yo must insert in all columns so an identity column will fail. In that case add a column to yor datatable and loop its rows through a for and increment the column: row["newColumn"] = ++i; – AiApaec Nov 16 '15 at 02:44
  • Thank you for the responses. I think you may be right @AiApaec. I tried to remove the mapping for the Id field as suggested by TheShaman, but I received an error stating: Cannot insert the value NULL into column 'Id'. I don't know how to do what you are suggesting, but I'll study it and give it a try. – user1916528 Nov 16 '15 at 02:47
  • i added an answer, try it. – AiApaec Nov 16 '15 at 02:58
  • Try this reference [SQLBulkCopy Insert with Identity Column](http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column) – harsha.cs Feb 04 '16 at 10:55

1 Answers1

0

Since you can'nt use an identity column for bulkcopy you can add a column to the datatable:

 //...
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
    oda.Fill(dtExcelData);
}
excel_con.Close();

dtExcelData.Columns.Add("Id", typeof(int));

int i = 0;
foreach(DataRow dr in dtExcelData.Rows)
{
    dr["Id"] = ++i;  
}
//...
AiApaec
  • 660
  • 1
  • 6
  • 12