How to Import Excel Data into SQL2008 Database

Time:2019-8-13

How to Import Excel Data into SQL2008 Database

Copy codeThe code is as follows:
private void AddManyData_Click(object sender, RoutedEventArgs e)
       {
           OpenFileDialog openFileDialog = new OpenFileDialog();
OpenFileDialog. Filter = Excel file |*. xls;

           if ((bool)openFileDialog.ShowDialog())  
           {  
                FileInfo fileInfo = new FileInfo(openFileDialog.FileName);  
                string filePath = fileInfo.FullName;  
                string connExcel = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filePath + “;Extended Properties=Excel 8.0”;

                using (OleDbConnection oleDbConn = new OleDbConnection(connExcel))
                {
                    oleDbConn.Open();

// Get excel table
                    DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Get the table name of the Excel table
                    string tableName = dt.Rows[0][2].ToString().Trim();
// Remove the blanks
                    tableName = “[” + tableName.Replace(“‘”, “”) + “]”;

// Getting data from Excel files by using SQL statements
String query = @ “SELECT number, name, public welfare work, electronic technology practice, operating system, computer composition, numerical analysis, network equipment and integration, Dynamic Website Development Experimental week, dynamic website development, average score, ranking FROM”;+ TableName;
                     DataSet dataSet = new DataSet();

                     using (OleDbCommand oleDbcomm = oleDbConn.CreateCommand())
                     {
                         oleDbcomm.CommandText = query;
                         OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleDbcomm);
                         oleAdapter.Fill(dataSet);
                     }
                     string connStr = “Data Source=HESHUHUA-PC;Initial Catalog=RSMSystem;Integrated Security=True”;
// Batch insertion of data using SqlBulkCopy
                    using (SqlBulkCopy sqlbc = new SqlBulkCopy(connStr))
                    {
                        sqlbc.DestinationTableName = “T_StuScore”;
// sqlbc. ColumnMappings. Add (“student number”, “StuNum”), the first parameter corresponds to the column name in the database.
// The second parameter corresponds to the column name of the corresponding table in the database.
Sqlbc. Column Mappings. Add (“student number”, “StuNum”);
Sqlbc. Column Mappings. Add (“name”, “StuName”);
Sqlbc. Column Mappings. Add (“Public Welfare Labor”, “Activity”);
Sqlbc.Column Mappings.Add (“Electronic Technology Internship”, “ElecAct”);
Sqlbc. Column Mappings. Add (“Operating System”);
Sqlbc.Column Mappings.Add (“Computer Composition”, “ComputerMaded”);
Sqlbc.Column Mappings.Add (“numerical analysis”, “Data Analysis”);
Sqlbc.Column Mappings.Add (“Network Devices and Integration”, “NetWork”);
Sqlbc.Column Mappings.Add (“Dynamic Web Site Development Week”, “WebWeek”);
Sqlbc.Column Mappings.Add (“Dynamic Web Development”, “WebMake”);
Sqlbc. Column Mappings. Add (“AvScore”);
Sqlbc. Column Mappings. Add (“ranking”, “StuPaiMing”);
                        sqlbc.WriteToServer(dataSet.Tables[0]);
MessageBox. Show (“Data Import Successful! “”;

                    }
                }  

           }  

       }

Recommended Today

Intelligent work: how artificial intelligence will reshape telework

Introduction:Although the emergence of artificial intelligence has been doubted and feared by employers and employees in varying degrees, its potential in telework cannot be underestimated. Although the emergence of artificial intelligence has been doubted and feared by employers and employees in varying degrees, its potential in telework cannot be underestimated. Machine learning is already actively […]