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:
Hi,

I have a table with following column in sql.

Name
Address
CreatedBy
CreatedOn

And I have a excel for the columns.

Name,Addess

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.

Thanks
Vinoth P

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

            string conString = string.Empty;
            string extension = Path.GetExtension(fuLeadupload.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[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))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();

                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");

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

            }
Posted
Updated 25-Mar-15 2:47am
v2
Comments
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

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

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


Also add 2 new Data Columns for these 2 new values
   
Comments
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