C # import Excel data into a database (MySQL or SQL Server)

I've been very busy lately and haven't written a blog for a long time. Today, let's explain how to import Excel data into Excel in C #, and at the end of the article, how to import data using SQL Server and MySQL tools.

The import process is broadly divided into two steps:

• 1. Import the Excel data into the dataset;
• 2. Insert the data traversal in the dataset into the corresponding database

First, import Excel data into the dataset

Need to add reference using System.Data; Using System.Data.OleDb;

     Public StaticDataSet Readexcel (stringfilepath) {            Try            {                stringstrconn ="Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ filepath +"; Extended properties= ' Excel 8.0; Hdr=false;imex=1 '"; OleDbConnection Oleconn=NewOleDbConnection (strconn);                Oleconn.open (); stringsql ="select * FROM [sheet1$]"; OleDbDataAdapter oledaexcel=NewOleDbDataAdapter (SQL, oleconn); DataSet Oledsexcel=NewDataSet (); Oledaexcel.fill (Oledsexcel,"table1");                Oleconn.close (); returnOledsexcel; }            Catch(Exception) {return NULL; Throw; }        }

Insert the data traversal in the dataset into the corresponding database

The first thing you can tell about Excel is that if you already know where the file is located, this step is skipped.

      string @" C:\Users\Administrator\Desktop\ gift list \ Gift Zhang Xi Consolidated version (Card coupon creates complete information). xlsx " ;             if (! System.IO.File.Exists (filename))                 Throw New Exception ("The specified path of the Excel file does not exist!") ");

Second, the data inside the dataset is read out and packaged into a data insertion statement.

DataSet ds =readexcel (filename); List<string> list = ( fromDataRow rowinchDs. tables["table1"]. RowsSelectString.Format ("INSERT INTO Test_1 (Id,card_id,card_type, title, product price, unit, number of gifts, mall, gift number) values (' {0} ', ' {1} ', ' {2} ', ' {3} ', ' {4} ', ' {5} ', ' {6} ', ' {7} ', ' {8} ')", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])). ToList ();

2.1 Inserting data from a dataset into MySQL

Insert we use ADO to process, first we have to add reference mysql.data in the extension; and MySql.Data.MySqlClient;

string strconnect = configurationmanager.connectionstrings["mysqlconnstring" ]. ToString ();
strconnect="Server=127.0.0.1;user id=***;p assword=***;p ersistsecurityinfo=true;port=3306;database=test";

New mysqlconnection (strconnect);
Conn. Open ();
 　　foreach (string in

New Mysqlcommand (item, conn);


Conn. Close ();

2.2 Inserting data from a dataset into SQL Server

Need to add reference using System.Data; using System.Data.SqlClient;

 string  strconnect = Configurationmanager.connectionstrings[ " sqlserverconnstring  "  ].　　　　　　　ToString (); or strconnect= "Data source=.;i Nitial catalog=test; Persist Security info=true; User id=***; password=***;            Multipleactiveresultsets=true "; SqlConnection conn  = new   SqlConnection (            StrConnect); Conn.            Open ();  foreach  (string  item in   list) {SqlCommand COMn  = new   SqlCommand (item, conn); COMn.            ExecuteNonQuery (); }

3. Importing Excel with Tools

I am using Toad for SQL Server 6.5 freeware and Toad for MySQL 7.2 freeware and SQL SERVER2008 (here is only toad for MySQL 7.2 freeware as an example)

　　

C # import Excel data into a database (MySQL or SQL Server)