C # import SQL Server in Excel

I am working on a health check management system that involves importing Excel Data to sqlserver. I thought it was a simple task, but I still had a hard time. I used three methods to update the data to the database after reading the data, I think this is the best option. Let's look at the code.

B S system, first use the fileupload control to save the current Excel file to the server, and then read the data in Excel, as shown below:

Public dataset getexceltabel () {string isxls = ""; // used to save the file extension // check whether the file exists if (fileupload1.hasfile = false) // hasfile is used to check whether fileupload has a specified file {page. clientscript. registerstartupscript (page. getType (), "message", "<script language = 'javascript 'defer> alert (' select an Excel file! '); </SCRIPT> "); // return NULL if no file exists;} // system. io. path. get the file extension isxls = system. io. path. getextension (fileupload1.filename ). tostring (). tolower (); // if it is not an Excel file, the prompt if (isxls! = ". Xls ") {page. clientscript. registerstartupscript (page. getType (), "message", "<script language = 'javascript 'defer> alert (' can only select an Excel file! '); </SCRIPT> "); // return NULL if the selected file is not an Excel file.} // Add the time string filename = datetime to the file name. now. tostring ("yyyymmddhhmmss") + fileupload1.filename; // obtain the datetime function of the execle file name string savepath = server. mappath (("~ \ Uploads \ ") + filename); // server. mappath obtains the relative path of the virtual server. // clear clearfile (server. mappath ("~ \ Uploads "); fileupload1.saveas (savepath); // saveas stores the uploaded file content on the server. Try {// connection string strconn =" provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ savepath +"; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '"; // create an adapter to obtain the Excel Data oledbdataadapter da = new oledbdataadapter ("select * from [sheet1 $]", strconn ); // fill in the read Excel Data to dataset DS = new dataset (); DA. fill (DS, "t_teampatient"); Return Ds;} catch (exception) {return NULL; throw new exception ("failed to get data in Excel ");}}

Import the read data to the database:

Public bool importtosql () {dataset DS = getexceltabel (); // dataset dspre = new dataset () obtained from Excel (); // dataset obtained from the database // create a database connection string connstring = "Server = .; database = chaoyang_hosptxt; uid = sa; Pwd = 123456 "; string sqlnowtxt =" select * From t_teampatient "; sqlconnection sqlconn = new sqlconnection (connstring); sqlcommand sqlcmd = new sqlcommand (sqlnowtxt, sqlconn); try {sqlconn. open (); // create a sqling sqldataadapter da = new sqldataadapter (sqlcmd); DA. fill (dspre, "t_teampatient"); // obtain data from the database // copy the data table datatable dt = Ds in the Excel table dataset. tables [0]. copy (); // Add it to the dataset foreach (datarow DR in DT. rows) {dspre. tables [0]. rows. add (dr. itemarray);} // update the database sqlcommandbuilder SCB = new sqlcommandbuilder (DA); DA. updatecommand = sqlcmd; DA. update (dspre, "t_teampatient"); Return true;} catch (exception) {return false; throw new exception ("failed to import data to Database");} finally {sqlconn. close (); sqlcmd. dispose ();}}

Here, we will provide you with two other ideas when updating dspre:

             DataTable dt = ds.Tables[0].Copy ();            dsPre.Tables.Add(dt);            dsPre.Tables.Remove(dsPre.Tables[0]);

That is to say, first Delete the table from the database, then add the Excel data table, and then update it. Why do we need to copy it first? Because DT originally belongs to DS, if it is forced to be added to dspre, an "already belongs to another dataset" error is thrown. This method is more suitable for replacing data than supplementing data.

Another method is to convert the read Excel data into a datarow array and merge the two tables using merge:

      dsPre .Merge (dataRow[]);

Of course, it is best to write a database connection as an independent function for other functions to call. Note that the "select" statement is always required to let sqldataadapter know which table to update the database; the name of the database table mapped to the Update time.

During the maintenance of the examination system, there are functions that import Excel files to the database, and you want to paste them directly. Instead, the changes become messy and you simply write one.