Click here to Skip to main content
15,860,972 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

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
 
Share this answer
 
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