Asp.Net將Excel資料匯入Sql Server資料庫的的例子,是調用預存程序的
前言:cmd.CommandText="Proc_Address";這句話是調用預存程序"Proc_Address"
該預存程序代碼為:
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')' --插入一條記錄
exec (@strSql)
GO private void Button1_Click(object sender, System.EventArgs e)
{
CreateTable(); //先將EXCEL匯入到資料庫,一:先把EXCEL匯入dateView,二:然後將dateView裡的資料匯入到資料庫裡面 //EXCEL 的串連串
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + File1.PostedFile.FileName.ToString() + ";" +
"Extended Properties=Excel 8.0;"; //建立EXCEL的串連
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; //測試代碼,用來測試是否能讀出EXCEL上面的資料
// DataGrid1.DataBind(); DataTable dt = objDataset1.Tables[0];
DataView myView = new DataView(dt); //SQL SERVER的資料庫連接
SqlConnection conn;
string dns =System.Configuration.ConfigurationSettings.AppSettings["ConStr"];//串連串
conn=new SqlConnection(dns); SqlCommand cmd =conn.CreateCommand();
cmd.CommandType =CommandType.StoredProcedure;
cmd.CommandText="Proc_Address"; int count=0;//用來記錄出錯的條數 try
{
foreach (DataRowView myDrv in myView)
{
count++;
//要關閉上一次的SQL Server的串連
if (conn.State.ToString()!="Closed")
conn.Close(); //每一次都要清空所有的CMD的參數
cmd.Parameters.Clear(); //執行預存程序
//首先獲得參數 共 3個
//@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); //表示是輸入參數
paraCardNo.Direction = ParameterDirection.Input;
paraToAddress.Direction = ParameterDirection.Input;
paraCCAddress.Direction = ParameterDirection.Input;
paraYYYYMM.Direction = ParameterDirection.Input; //參數賦值
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();//寫入SQL資料庫
}
}
catch
{
Page.Response.Write("alert('第"+count.ToString()+"條資料出錯!');"
objConn.Close();//關閉EXCEL的串連
} objConn.Close();//關閉EXCEL的串連 |
