ASP. NET imports Excel data into the SQL Server database, which calls the Stored Procedure
Cmd. commandtext = "proc_address"; this statement calls the stored procedure "proc_address"
This Stored ProcedureCodeIs:
Create proc proc_address @ cardno char (20), @ toaddress char (50), @ ccaddress char (50), @ yyyymm char (12)
As
Declare @ strsql char (400) Set @ strsql = 'insert into emailaddress' + @ yyyymm + 'values (''+ @ cardno +'', ''+ @ toaddress + '', ''+ @ ccaddress +'', ''+ '1') '-- insert a record.
Exec (@ strsql)
Go Private void button#click (Object sender, system. eventargs E)
{
Createtable (); // Import the Excel file to the database first. 1. Import the Excel file to dateview first. 2. Import the data in dateview to the database. // Excel connection string
String sconnectionstring = "provider = Microsoft. Jet. oledb.4.0;" +
"Data Source =" + file1.postedfile. filename. tostring () + ";" +
"Extended properties = Excel 8.0 ;"; // Create an Excel connection
Oledbconnection objconn = new oledbconnection (sconnectionstring ); Objconn. open (); Oledbcommand objcmdselect = new oledbcommand ("select * from [sheet1 $]", objconn );
Oledbdataadapter objadapter1 = new oledbdataadapter ();
Objadapter1.selectcommand = objcmdselect; Dataset objdataset1 = new dataset (); Objadapter1.fill (objdataset1, "xldata "); // Datagrid1.datasource = objdataset1.tables [0]. defaultview; // test the code to test whether the data on the Excel file can be read.
// Datagrid1.databind (); Datatable dt = objdataset1.tables [0];
Dataview myview = new dataview (DT ); // SQL Server database connection
Sqlconnection conn;
String DNS = system. configuration. configurationsettings. receivettings ["constr"]; // connection string
Conn = new sqlconnection (DNS ); Sqlcommand cmd = conn. createcommand ();
Cmd. commandtype = commandtype. storedprocedure;
Cmd. commandtext = "proc_address "; Int COUNT = 0; // The number of error records. Try
{
Foreach (datarowview mydrv in myview)
{
Count ++;
// Close the last SQL server connection
If (conn. state. tostring ()! = "Closed ")
Conn. Close (); // All cmd parameters must be cleared each time.
Cmd. Parameters. Clear (); // Execute the Stored Procedure
// Obtain three parameters first
// @ Cardno, @ toaddress, @ ccaddress
Sqlparameter paracardno = cmd. Parameters. Add ("@ cardno", sqldbtype. Char );
Sqlparameter paratoaddress = cmd. Parameters. Add ("@ toaddress", sqldbtype. Char );
Sqlparameter paraccaddress = cmd. Parameters. Add ("@ ccaddress", sqldbtype. Char );
Sqlparameter parayyyymm = cmd. Parameters. Add ("@ yyyymm", sqldbtype. Char ); // Indicates the input parameter.
Paracardno. Direction = parameterdirection. input;
Paratoaddress. Direction = parameterdirection. input;
Paraccaddress. Direction = parameterdirection. input;
Parayyyymm. Direction = parameterdirection. input; // Parameter value assignment
Paracardno. value = mydrv [0]. tostring (). Trim ();
Paratoaddress. value = mydrv [1]. tostring (). Trim ();
Paraccaddress. value = mydrv [2]. tostring (). Trim ();
Parayyyymm. value = ddlyear. items [ddlyear. selectedindex]. Value + ddlmonth. items [ddlmonth. selectedindex]. value; Conn. open ();
Cmd. executenonquery (); // write data to the SQL database
}
}
Catch
{
Page. response. Write ("alert ('nth" + count. tostring () + "data error! ');"
Objconn. Close (); // close the Excel connection
} Objconn. Close (); // close the Excel connection |
