This article describes the method of C# to import Excel table data into Sql Server database. Share it for your reference, as follows:
There are many ways to import Excel table data into Sql Server database. Here we only introduce one of them:
1. First, we need to create a new my_test table in the test database, which has three fields tid int type, tname nvarchar type, tt nvarchar type
(Note: The data type in the my_test table must be the same as the type of the corresponding field in Excel)
2. We use SELECT * FROM OPENROWSET( '.4.0 ', 'Excel 5.0;DatabASE=[Path of Excel table.xsl file];HDR=YES;IMEX=1 ', Sheet1 to read the data in the Excel table. The read data is the same as the data read from the table in the database, and also includes the field name and data. Of course, we can also use the field name list to obtain departmental data in the Excel table. SELECT Field 1, Field 2, Field 3 [...] FROM OPENROWSET( '.4.0 ', 'Excel 5.0;DatabASE=[Path of Excel table.xsl file];HDR=YES;IMEX=1 ', Sheet1
Notice:HDR=Yes, which means that the first line is the title and is not used as data; IMEX ( IMport EXport mode ) setting
IMEX has three modes:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to explain here is the IMEX parameters, because different modes represent different read and write behaviors:
When IMEX=0 is "export mode", the Excel file enabled in this mode can only be used for "write" purposes.
When IMEX=1 is "Incoming Mode", the Excel file enabled in this mode can only be used for "reading".
When IMEX=2 is "connect mode", the Excel file enabled in this mode can support both "read" and "write" purposes.
The meaning is as follows:
0 ---Output mode;
1---Input mode;
2----Link mode (full update capability)
3. The first row in Excel defines the column name, and the data starts from the second row. The data read from Excel through the Sql statement also starts from the second row, and the second column name becomes the field name. If your first row has a defined column name, the names of each field of the data obtained from Excel are the column names in Excel. For example: the field names of the data obtained from the sheet table are number Name Notes. If the first row of the Excel table you define does not have a column name, then the field names of the retrieved data are F1, F2, F3, and so on. If you just want to get the data in the columns in the Excel table, then you can use the above content.
4. Create a new web form in VS (Note: Winform form is also OK), add a Button control to it, and execute the import as soon as you click the button. Double-click this button to define the event handling function. The code in it is as follows:
using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
public partial class admin_test :
{
protected void Page_Load(object sender, EventArgs e)
{
}
public SqlConnection con()
{
return new SqlConnection("server=localhost;uid=test;pwd=test;database=test");
//The test in uid=test here must be System Administror, otherwise an error will occur }
protected void Button1_Click1(object sender, EventArgs e)
{
SqlConnection mycon = con();
string sqlstr = "insert into my_test select serial number, Name, Remark from OPENROWSET('.4.0','Excel 5.0;HDR=YES;DATABASE=e:\\',sheet1$)";
/* Here you can use * instead of number, name, and notes, which represent column names in Excel */
SqlCommand cmd = new SqlCommand(sqlstr, mycon);
();
();
();
}
}
Executing the above code may cause the following problems:
SQL Server blocks access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component has been shut down as part of this server's security configuration. System administrators can enable 'Ad Hoc Distributed Queries' by using sp_configure. For more information on enabling 'Ad Hoc Distributed Queries', see "Peripheral Application Configurator" in SQL Server Books Online.
Solution:
/*Enable Ad Hoc Distributed Queries: */ exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure /*After use is complete, close Ad Hoc Distributed Queries: */ exec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure
For more information about C# related content, please check out the topic of this site:Summary of C# operation skills》、《Summary of thread usage techniques for C# programming》、《Summary of XML file operation skills in C#》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Summary of C# array operation skills"and"Introduction to C# object-oriented programming tutorial》
I hope this article will be helpful to everyone's C# programming.