The example of ASP. net importing Excel data into the SQL Server database 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