Click here to Skip to main content
14,924,466 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a table with following column in sql.


And I have a excel for the columns.


I need code for upload the excel data into sql and CreatedBY, CreatedOn column field will be automatically insert the seesion userid and current date.

Please help the above scenario.

Vinoth P

/* code snippet */
//Upload and save the file
            string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(fuLeadupload.PostedFile.FileName);

            string conString = string.Empty;
            string extension = Path.GetExtension(fuLeadupload.PostedFile.FileName);
            switch (extension)
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;

            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
                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[2] {
                //new DataColumn("LeadID", typeof(int)),
                new DataColumn("CustomerFirstName", typeof(string)),
                new DataColumn("CustomerLastName",typeof(string))

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

                string consString = ConfigurationManager.ConnectionStrings["Retention"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.tbl_LeadMasterDetails";

                        //[OPTIONAL]: Map the Excel columns with that of the database table

                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Addresse", "Address");


Updated 25-Mar-15 2:47am
KaushalJB 25-Mar-15 3:36am
What have you tried in code?
Sinisa Hajnal 25-Mar-15 3:55am
Show the code that doesn't work...and explain what you think it should do and what it actually does.
v2vinoth 25-Mar-15 4:09am
/* to Question */
What is the issue with the code?
v2vinoth 25-Mar-15 4:33am
Code is working but I want to add createdBY and createdON column automatically while uploading excel file..Like createdBY is session username and createdon is currentdateandtime
Sinisa Hajnal 25-Mar-15 9:42am
You're already adding two columns...
new DataColumn("CustomerFirstName", typeof(string)),
new DataColumn("CustomerLastName",typeof(string))

What's preventing you from adding two more? Then you go through the datatable and fill required data in added columns
v2vinoth 25-Mar-15 9:51am
In database(Name,Address,CreatedBy,CreatedON) columns are available in sql table. In excel two columns only available(Name,Address). While uploading the excel and add data for createBY column as session username and createdon column as currentdatetime automatically
Rajesh Varma Buddaraju 25-Mar-15 11:20am
It won't be happened automatically. Once you fill the data from excel using oda.Fill(dtExcelData);
add the two columns CreatedBy and CreatedOn.
2. Put the sessionID and createddate on all the rows.
3. pass the datatable to bulk copy.

It will work.
v2vinoth 25-Mar-15 11:30am
Hi Rajesh,

Please tell me the code so that I can copy and try it..Please

1 solution

string CreatedBY=Session["Username"].toString();
Datetime CreatedOn=DateTime.Now

Change your query:
"SELECT FirstName,Lastname,Address,"+CreatedBY+","+CreatedOn+"FROM [Sheet1$]"

Also add 2 new Data Columns for these 2 new values
v2vinoth 6-Apr-15 2:13am
It will not showing any error but the createdBy,createdOn column is showing null value only
v2vinoth 6-Apr-15 2:14am
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT FirstName,Lastname,Address,"+CreatedBY+","+CreatedOn+" [" + sheet1 + "]", excel_con))
Reshma Babu 13-Apr-15 5:41am
Did you add 2 columns for the same in dtExcelData? Also, under Column Mappings?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900