62,054
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch(Exception ex)
{
tx.Rollback();
return 0;
}
}
}
/// <summary>
/// 讀取EXCEL
/// </summary>
/// <param name="path">EXCEL文件的路徑</param>
/// <returns></returns>
public DataSet ReadExcel(string path)
{
DataSet ds = new DataSet();
string strMessage = "";
try
{
string strcon = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + path + ";Extended Properties='Excel 8.0;HRD=no;IMEX=2;'";
OleDbConnection con = new OleDbConnection(strcon);
con.Open();
string strSQL = "select * from [sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, con);
int flag = da.Fill(ds, "excel");
con.Close();
return ds;
}
catch (Exception ex)
{
strMessage = "錯誤訊息:1.可能是要上傳的EXCEL在打開狀態。2.也可能是" + ex.ToString();
MessageAjaxHelper.showAlert(UpdatePanel1, strMessage);
return ds;
}
}
<a href='http://blog.csdn.net/ajaxtop/article/details/6670449'>请查阅</a>
DataTable Excel_UserInfo = new DataTable();
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileInfo.FullName + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
string strExcel = "select * from [sheet1$]";
using (OleDbDataAdapter adaptor = new OleDbDataAdapter(strExcel, strConn))
{
DataSet ds = new DataSet();
adaptor.Fill(ds);
Excel_UserInfo = ds.Tables[0];
}
/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileUpload">文件上传控件对象</param>
/// <param name="filePath">要存放文件的服务器路径</param>
/// <param name="gridview">目标GridView</param>
/// <param name="keyColIndex">用于导入的主键列</param>
/// <returns>返回没有导入的数据DataSet</returns>
public static DataSet ExcelToGridView(FileUpload fileUpload, string filePath, GridView gridview, int keyColIndex, string strMsg)
{
string errMsg = string.Empty;
DataSet ds = null ;
try
{
if ( ! filePath.EndsWith("\"))
{
filePath += "\";
}
string fileName = fileUpload.FileName.Replace(".xls", "") + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string fullFileName = filePath + fileName;
fileUpload.SaveAs(fullFileName);
ds = ExcelToGridView(fullFileName, gridview, keyColIndex,errMsg);
if ( errMsg != string.Empty)
{
errMsg += "将数据导入GridView失败." + errMsg ;
};
}
catch (Exception ex)
{
errMsg += "上传excel文件失败." ;
}
strMsg = errMsg;
return ds ;
}
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'C:\Excel.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(connStr);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet ds = new DataSet();
myDa.Fill(ds);
DataTable dt = ds.Tables[0];