ASP. NET data import implementation Excel to MSSQL

What is the process of importing ASP. NET data to Excel to MSSQL? What do you need to pay attention?

When creating a website project, you may encounter ASP. NET data is required to be imported to the SQL Server database, and Excel is also considered a database. It is technically difficult to use OleDb to connect and then read data and write it into the SQL Server database connected with SqlClient.

However, a problem to be considered is that the system installation environment is generally dedicated SQL server + web server + client, while Excel files are often imported on the client. If you directly use SQL statements to read the files, because SQL runs on a web server and cannot read the Excel address of the client, an error occurs.

Now that you know the cause of the error, the solution will be:

1. Upload the client Excel file to a folder on the web server. Be sure to set the "write" permission for the network user for the folder;

2. Use OleDb + SQL statements to read Excel files on the web server

3. Import the read ASP. NET data to the SQL Server database.

4. Delete temporary Excel files on the web server

Some code is as follows:

 
 

  
  
StringFilename = FileUpload1.PostedFile. FileName. Substring (FileUpload1.PostedFile. FileName. LastIndexOf ("\\"));
  
  
FileUpload1.PostedFile. SaveAs (Server. MapPath ("Fileupload \\") + Filename );// Upload a file 
  
  
 
  
  
StringConn ="Provider = Microsoft. Jet. OLEDB.4.0; Data Source ="+
  
  
Server. MapPath ("Fileupload \") + filename +"; Extended Properties = Excel 8.0 ";
  
  
StringSqlin ="SELECT * FROM [Sheet2 $]";
  
  
OleDbCommand olecommand =NewOleDbCommand (sqlin,NewOleDbConnection (conn ));
  
  
OleDbDataAdapter adapterin =NewOleDbDataAdapter (olecommand );
  
  
DataSet dsin =NewDataSet ();
  
  
Adapterin. Fill (dsin );
  
  
DataTable dtin = dsin. Tables [0];// Connect and read Excel Data 
  
  
 
  
  
For(I = 3; I <= totalrow; I ++)// Import the data of the first worksheet in the Excel file to the SQL database scjd_youliaoxiaohaojihua table 
  
  
{
  
  
StringSQL ="Insert into scjd_youliaoxiaohaojihua (yuexuhao, danwei, youpin,
  
  
Cheliangmingchengxinghao, zichanxingzhi, chew.hao, qiyou, chaiyou, beizhu) values ('";
  
  
IntJ;
  
  
SQL + = DropDownList1.SelectedValue. ToString () + DropDownList3.SelectedValue. ToString ();
  
  
If(DropDownList1.SelectedValue. ToString ()! = DateTime. Now. Year. ToString () |Int. Parse
  
  
(DateTime. Now. Month. ToString ())! =Int. Parse (DropDownList3.SelectedValue. ToString ()))
  
  
{
  
  
SQL + ="20";
  
  
}
  
  
Else 
  
  
{
  
  
If(Int. Parse (DateTime. Now. Day. ToString () <10)
  
  
SQL + ="0";
  
  
SQL + = DateTime. Now. Day. ToString ();
  
  
}
  
  
IfI-3 + count <9)
  
  
SQL + ="00";
  
  
Else IfI-3 + count <99)
  
  
SQL + ="0";
  
  
SQL + = (I-3 + 1 + count). ToString ();
  
  
SQL + ="','";
  
  
For(J = 1; j <8; j ++)
  
  
{// Traverse all columns in a row in the Excel table, except the last column 
  
  
SQL + = dtin. Rows [I] [j]. ToString (). Trim ();
  
  
SQL + ="','";
  
  
}
  
  
SQL + = dtin. Rows [I] [8]. ToString (). Trim ();
  
  
SQL + ="')";
  
  
Try 
  
  
{
  
  
DoSql (SQL );
  
  
}
  
  
Catch(Exception eeeeeee)
  
  
{
  
  
Response. Write ("<Script> alert ('data import error, please check the Excel file') </script>");
  
  
Return;
  
  
}
  
  
}
  
  
 
  
  
FileInfo file =NewFileInfo (Server. MapPath ("Fileupload /") + Filename );
  
  
If(File. Exists)
  
  
{// Delete an object 
  
  
File. Delete ();
  
  
}
  
  
 
  
  
Protected VoidDoSql (StringSQL)// Functions used to execute SQL statements 
  
  
{
  
  
SqlConnection conn =NewSqlConnection ();// Create a connection object 
  
  
Conn. ConnectionString = ConfigurationManager. etettings ["Conn"]. ToString ();// Assign a value to the connection string 
  
  
Conn. Open ();// Open the database 
  
  
SqlCommand cmd =NewSqlCommand (SQL, conn );
  
  
Cmd. ExecuteNonQuery ();// 
  
  
Conn. Close ();// Close the database 
  
  
}
 
 

Note:

1. The code in this article is based on a small project that I just created recently. Some data is highly targeted and not universal, and only has a general idea.

2. Due to the capability and level problems, some code seems clumsy. If you can use the Stored Procedure flexibly, you should be able to greatly simplify the code.

This section describes how to import ASP. NET data to Excel to MSSQL. We hope you can understand how to import ASP. NET data to Excel to MSSQL.

1. Introduction to data caching in ASP. NET data cache
2. Implementation of ASP. NET data collection
3. Use of SqlHelperSqlHelper in ASP. NET data access layer
4. Hash Algorithm for ASP. NET Data Encryption
5. Implement ASP. NET data encryption using symmetric encryption algorithms