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 |