Import SQL server data tables in batches using Excel files in Asp.net

Using system;
Using system. collections;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. Web;
Using system. Web. sessionstate;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. htmlcontrols;
Using system. Data. sqlclient;
Using system. configuration;
Namespace webapplication1
{
/// <Summary>
/// Summary of tantest.
/// </Summary>
Public class tantest: system. Web. UI. Page
{
Protected system. Web. UI. webcontrols. Label labelupresult;
Protected system. Web. UI. webcontrols. Label labelfileext;
Protected system. Web. UI. webcontrols. Label labelfilesize;
Protected system. Web. UI. webcontrols. Button button1;
Protected system. Web. UI. webcontrols. Button button2;
Protected system. Web. UI. webcontrols. Label label1;
Protected system. Web. UI. htmlcontrols. htmlinputfile inputfile;
Protected system. Web. UI. webcontrols. Label labelfilename;
// Attributes
Private string excelsource
{
Get
{
Return (viewstate ["excelsource"] = NULL )? "" :( String) (viewstate ["excelsource"]);
}
Set
{
Viewstate ["excelsource"] = value;
}
}

 
Private void page_load (Object sender, system. eventargs E)
{

}

# Code generated by region web Form Designer
Override protected void oninit (eventargs E)
{
//
// Codegen: This call is required by the ASP. NET web form designer.
//
Initializecomponent ();
Base. oninit (E );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. button1.click + = new system. eventhandler (this. button#click );
This. button2.click + = new system. eventhandler (this. button2_click );
This. Load + = new system. eventhandler (this. page_load );

}
# Endregion

Private void button#click (Object sender, system. eventargs E)
{
// Check that the uploaded file is not empty
If (inputfile. postedfile. contentlength> 0)
{
// Set the path to save the uploaded file
String strsavedir = "./whhrreport /";
String strname = inputfile. postedfile. filename;
// Obtain the index of the last "." In the file name (including the path)
Int intext = strname. lastindexof (".");
// Get the file extension
String strext = strname. substring (intext );
// Here, I automatically name the File Based on the date and file size to ensure that the file name is not repeated
/*
Datetime datnow = datetime. now;
String strnewname = datnow. dayofyear. tostring () + inputfile. postedfile. contentlength. tostring () + strext ;*/
// Retrieve the last "\" index in the file name (including path)
Int intpath = strname. lastindexof ("\\");
// Get the file name (excluding the path)
String strnewname = strname. substring (intpath );
// Save the file to the directory you want. This is the upload directory under the IIS root directory. You can change it.
// Note: Here I use server. mappath () to retrieve the absolute directory of the current file. In Asp.net, "\" must be replaced "\"
Inputfile. postedfile. saveas (server. mappath (strsavedir + strnewname ));
Excelsource = server. mappath (strsavedir + strnewname). tostring ();
// Obtain the relevant attributes of the file: file name, file type, and file size.
Labelupresult. Text = "Upload successful! ";
Labelfilename. Text = "File Source:" + strname;
Labelfileext. Text = "file type:" + inputfile. postedfile. contenttype + "(" + strext + ")";
Labelfilesize. Text = "file size:" + (inputfile. postedfile. contentlength/1024). tostring () + "K byte (s )";
}
Else
{
Labelupresult. Text = "select the file you want to upload! ";
Labelfilename. Text = "";
Labelfileext. Text = "";
Labelfilesize. Text = "";
}

}

Private void button2_click (Object sender, system. eventargs E)
{
Sqlconnection conn = new sqlconnection (configurationsettings. etettings ["DSN"]);

// Connect to the Excel Data Source
String excelconnstring = @ "provider = Microsoft. Jet. oledb.4.0; Data Source =" + excelsource + "";
Excelconnstring + = @ "; extended properties =" "Excel 8.0; HDR = yes; IMEX = 1 """;
System. Data. oledb. oledbconnection excelconn = new system. Data. oledb. oledbconnection (excelconnstring );
String SQL = "select * from [sheet1 $]";
System. Data. oledb. oledbdataadapter mycomm = new system. Data. oledb. oledbdataadapter (SQL, excelconn );
Dataset myds = new dataset ();
Mycomm. Fill (myds, "SS ");

//
Sqlcommand CM = new sqlcommand ();
Cm. Connection = conn;
Conn. open ();
For (INT I = 0; I <myds. Tables [0]. Rows. Count; I ++)
{
String updatesql1 = "insert into tantest (ghid, xjhao, ylao, yliao, gjj) values ('" + myds. tables [0]. rows [I] ["employee ID"] + "','" + myds. tables [0]. rows [I] ["salary"] + "','" + myds. tables [0]. rows [I] ["Pension Fund"] + "','" + myds. tables [0]. rows [I] ["Medical Fund"] + "','" + myds. tables [0]. rows [I] ["Provident Fund"] + "')";
Cm. commandtext = updatesql1;
Try
{
Cm. executenonquery ();
}
Catch
{

}
Finally
{
This. label1.visible = true;
This. label1.text = "data import successful! ";

}

}
Cm. Dispose ();
Conn. Close ();
 
}
}
}