Asp.net Excel批量導入數據到SqlServer數據庫

下面是自己添加的一個類：在上面的頁面中的按鈕單擊事件中調用。

public class ExcelToMSSql     public static int count = 0;
    public static string error = "";
public ExcelToMSSql()    //
   //TODO: 在此處添加構造函數邏輯 }
     //傳入Excel文件的路徑。
    public static void DataTranf(string execlpath)         DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
        for (int i = 0; i < dtExcel.Rows.Count; i++)             InsertToDB(dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][1].ToString().Trim(), dtExcel.Rows[i][2].ToString().Trim(), FormsAuthentication.HashPassWordForStoringInConfigFile(dtExcel.Rows[i][3].ToString().Trim(), "MD5"), dtExcel.Rows[i][4].ToString().Trim(),dtExcel.Rows[i][5].ToString().Trim());

//此處根據Excel中字段，自己設定要取用的字段。     }

    public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)         string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended PropertIEs=Excel 5.0;";
        string strExcel = string.Format("select * from [{0}$]", strSheetName);
        DataSet ds = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(strConn))             conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds, strSheetName);
            conn.Close();
        }

        return ds.Tables[strSheetName];
    }

//插入記錄到SqlServer數據庫
    public static void InsertToDB(string studno, string studname,string studsex,string studpwd,string studclass,string middleschool)         string connstr = System.Configuration.ConfigurationSettings.APPSettings["constr"];
        SqlConnection conn = new SqlConnection(connstr);
        conn.Open();
        string str_insertstudent = "insert into student (userno,username,sex,pwd,classname,address) values('" + studno + "','" + studname + "','" + studsex + "','" + studpwd + "','" + studclass + "','" + middleschool + "')";
        SqlCommand cmd = new SqlCommand(str_insertstudent, conn);
        try             cmd.ExecuteNonQuery();
            count++;         catch(Exception e)             error += e.Message.ToString() + "<br />";     }

     大同小異，導入到其他數據庫只要更改下連接串即可。

   但是此方法似乎好像只能在網站所在的機子上實現批量插入。遠程批量插入會不成功，本人的解決辦法是先上傳Excel文件到網站，然後在批量插入即可。