Click here to Skip to main content
15,889,808 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi dear,

I have a screen with 2 control

1: fileupload
2: save button

when user click on file upload it can select xls, csv files.
when he click on upload button i want to save data into my predefined Table in database.


i want to use insert data using SqlBulCopy.

i read this artical

Excel To DataBase Table[^]

i want to insert sqlbulcopy.

please help me.

give me a step to step way to solve it.
Posted
Comments
CS2011 4-Jul-13 4:18am    
what have you tried till now ?
prince_rumeel 4-Jul-13 4:23am    
I am doing this task first time, so i am not conferm how to start.
i need a taturial
Sushil Mate 4-Jul-13 4:23am    
what's wrong with the link you provided. is it not working?
prince_rumeel 4-Jul-13 4:24am    
the link is working fine.
it read line by line and insert.

my excel sheet have 100000 records.
i need to use bulk record save at a time.

mean dump whole excel file

You need to use SqlBulkCopy.WriteToServer Method (DataTable)[^]

These are some links might help you, They have shown detailed step by step bulk insertion of data into DB.

Transferring Data Using SqlBulkCopy[^]

using-sqlbulkcopy-for-high-performance-inserts/[^]

http://www.4guysfromrolla.com/articles/102109-1.aspx[^]
 
Share this answer
 
Comments
prince_rumeel 4-Jul-13 7:38am    
I want to read data from Excel sheet and than dump it into Table.

I am not getting how can i read data from excel sheet
Sushil Mate 4-Jul-13 7:39am    
Does the link which you provided in question is working at your end?
prince_rumeel 4-Jul-13 8:38am    
ok fine thx.yup it is working :)
Sushil Mate 4-Jul-13 10:48am    
you are welcome
C#
protected void btnsave_Click(object sender, EventArgs e)
        {
            if (FileUpload.HasFile)
            {                
                importDataFromExcel(FileUpload.PostedFile.FileName);
            }
        }

        public void importDataFromExcel(string excelFilePath)
        {
            //Declare Variables - Edit these based on your particular situation
            string sSQLTable = "dbo.Import_SFS";
            // make sure your sheet name is correct, here sheet name is Data, so you can change your sheet name if have different
            string myExcelDataQuery = "Select Brand,Region_ID,Account_Name from [Data$]";
            try
            {
                //Create our connection strings
                string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";

                string sSqlConnectionString = "Data Source=localhost;Initial Catalog=MDFW;Integrated Security=True";
                //Execute a query to erase any previous data from our destination table
                string sClearSQL = "DELETE FROM " + sSQLTable;
                SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
                SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                SqlConn.Close();
                //Series of commands to bulk copy data from the excel file into our SQL table
                OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
                OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
                OleDbConn.Open();
                OleDbDataReader dr = OleDbCmd.ExecuteReader();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
                bulkCopy.DestinationTableName = sSQLTable;
                while (dr.Read())
                {
                    bulkCopy.WriteToServer(dr);
                }
                OleDbConn.Close();
            }
            catch (Exception ex)
            {
                //handle exception
            }
        } 



this is the solution of my problem.

i did RND with your given links.and my ownself also search.and finaly i solve it.
Thanks
 
Share this answer
 
v2

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