using System;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;
/// <summary>
/// SqlDbHelper ,以System.Data.OleDb類來操作數據庫
/// </summary>
public class OleDbHelper
{
private string strMsg = "";
private string strConnString = "";
private OleDbConnection objConn = null;
private int iPageSize = 0;
private int iPageCount = 0;
private int iRecordCount = 0;
/// <summary>
/// SqlDbHelper ,以System.Data.OleDb類來操作數據庫
/// </summary>
public OleDbHelper()
{
//
// TODO: 在此加入建構函式的程式碼
//
}
public OleDbHelper(string sConnString)
{
//
// TODO: 在此加入建構函式的程式碼
//
this.GetConn(sConnString);
}
#region 屬性區域
/// <summary>
/// 信息属性
/// </summary>
public string Message
{
get
{
return strMsg;
}
set
{
strMsg = value;
}
}
public string ConnectionString
{
get { return strConnString; }
set { strConnString = value; }
}
public int PageSize
{
get { return iPageSize; }
set { iPageSize = value; }
}
public int PageCount
{
get { return iPageCount; }
set { iPageCount = value; }
}
public int RecordCount
{
get { return iRecordCount; }
set { iRecordCount = value; }
}
/// <summary>
/// 連接對象
/// </summary>
public OleDbConnection Connection
{
get { return objConn; }
set { objConn = value; }
}
#endregion
/// <summary>
/// Get DB conncection string from web.config file.
/// By OleDbConnString
/// </summary>
/// <returns></returns>
public string GetConnectString()
{
string strConn = "";
try
{
//Get DB conncection string from web.config file.
strConn = System.Configuration.ConfigurationSettings.AppSettings["OleDbConnString"];
}
catch (Exception ex)
{
this.Message = ex.Message;
}
return strConn;
}
/// <summary>
/// 取得Sqlcle数据库存连接,需在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <returns></returns>
public OleDbConnection GetConn()
{
string strConn;
try
{
OleDbConnection Conn = new OleDbConnection();
//Get DB conncection string from web.config file.
strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlDbConnString"];
Conn.ConnectionString = strConn;
Conn.Open();
this.Connection = Conn;
return Conn;
}
catch
{
return null;
}
}
/// <summary>
/// 取得Sqlcle数据库存连接,需在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="sSqlDbConnString">在web.config中的appSetting处设定连接字符串名稱</param>
/// <returns></returns>
public OleDbConnection GetConn(string sSqlDbConnString)
{
string strConn;
try
{
OleDbConnection Conn = new OleDbConnection();
//Get DB conncection string from web.config file.
strConn = System.Configuration.ConfigurationSettings.AppSettings[sSqlDbConnString];
Conn.ConnectionString = strConn;
Conn.Open();
this.Connection = Conn;
return Conn;
}
catch (Exception ex)
{
this.Message = ex.Message + ex.Source;
return null;
}
}
/// <summary>
/// 打開數據庫連接
/// </summary>
/// <param name="sSqlDbConnString"></param>
/// <returns></returns>
public Boolean OpenConn(string sSqlDbConnString)
{
try
{
OleDbConnection Conn = new OleDbConnection();
Conn.ConnectionString = sSqlDbConnString;
Conn.Open();
this.Connection = Conn;
return true;
}
catch (Exception ex)
{
this.Message = ex.Message;
return false;
}
}
/// <summary>
/// 取得Sqlcle数据库存连接
/// </summary>
/// <param name="sTNSName">连接SqlCLE的tnsname</param>
/// <param name="sUserId">用户名</param>
/// <param name="sPassword">密码</param>
/// <returns></returns>
public OleDbConnection GetConn(string sServerName, string sDataBaseName, string sUserId, string sPassword)
{
string strConn;
try
{
OleDbConnection Conn = new OleDbConnection();
strConn = "Provider=SQLOLEDB;server=" + sServerName + ";database=" + sDataBaseName + ";uid=" + sUserId + ";pwd=" + sPassword;
Conn.ConnectionString = strConn;
Conn.Open();
this.Connection = Conn;
return Conn;
}
catch
{
return null;
}
}
/// <summary>
/// 取得数据集
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSQL, OleDbConnection Conn)
{
OleDbDataAdapter Adpter;
DataSet ds;
try
{
//strSQL = "select * from t_PublicInfo ";
Adpter = new OleDbDataAdapter(strSQL, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
}
catch (Exception ex)
{
this.Message = ex.Message + ex.Source;
return null;
}
return ds;
}
/// <summary>
/// 取得数据集,默認連接的數據庫存是在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSQL)
{
OleDbDataAdapter Adpter;
DataSet ds;
OleDbConnection Conn = new OleDbConnection();
Conn = GetConn();
try
{
Adpter = new OleDbDataAdapter(strSQL, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
}
catch
{
return null;
}
return ds;
}
/// <summary>
/// 得到一张表,根据SQL语句,如果沒有找到資料,連接正確,行個數為0,如果連接異常的,返回為空
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public DataTable GetDataTable(string strSql)
{
OleDbDataAdapter Adpter;
DataSet ds;
DataTable dtTable = new DataTable();
OleDbConnection Conn = new OleDbConnection();
Conn = this.Connection;
if (Conn == null)
{
return dtTable;
}
try
{
Adpter = new OleDbDataAdapter(strSql, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
dtTable = ds.Tables[0];
}
catch (Exception ex)
{
this.Message = ex.Message + ex.StackTrace;
}
return dtTable;
}
public DataTable GetDataTable(string strSql, string sConnString)
{
OleDbDataAdapter Adpter;
DataSet ds;
DataTable dtTable = new DataTable();
OleDbConnection conn = new OleDbConnection();
try
{
conn.ConnectionString = sConnString;
conn.Open();
Adpter = new OleDbDataAdapter(strSql, conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
dtTable = ds.Tables[0];
}
catch (Exception ex)
{
this.Message = ex.Message + ex.StackTrace;
}
finally
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
return dtTable;
}
/// <summary>
/// 根据分页的要求,开发分页查询功能
/// </summary>
/// <param name="sSql"></param>
/// <param name="sSortExpression"></param>
/// <param name="iPageSize"></param>
/// <param name="iPageNumber"></param>
/// <param name="iMaxPageNumber"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public DataTable GetDataTable(String sSql, String sSortExpression, int iPageSize, int iPageNumber, out int iMaxPageNumber, out DataTable dataTable)
{
DataTable dt = new DataTable();
Boolean lbResult = false;
string _sMessage = "";
String lsSQL = String.Empty;
dataTable = new DataTable();
iMaxPageNumber = 0;
if (iPageSize <= 0)
{
iPageSize = 10;
}
if (iPageNumber < 0)
{
iPageNumber = 1;
}
if (sSortExpression.Equals(""))
{
lsSQL = sSql;
}
else
{
lsSQL = String.Format("SELECT * FROM ({0}) ORDER BY {1}", sSql, sSortExpression);
}
//if (iPageNumber.Equals(0))
//{
// dt = this.GetDataTable(lsSQL);
// if (dt.Rows.Count > 0)
// {
// iMaxPageNumber = dt.Rows.Count / iPageSize + ((dt.Rows.Count % iPageSize) == 0 ? 0 : 1);
// if (iPageNumber > iMaxPageNumber)
// {
// iPageNumber = iMaxPageNumber;
// }
// }
//}
//else
//{
// dt = this.GetDataTable(String.Format("SELECT COUNT(*) QTY FROM ({0})", sSql));
// iRecordCount = int.Parse(dt.Rows[0][0].ToString());
// iMaxPageNumber = iRecordCount / iPageSize + ((iRecordCount % iPageSize) == 0 ? 0 : 1);
// if (iPageNumber > iMaxPageNumber)
// {
// iPageNumber = iMaxPageNumber;
// }
// lsSQL = String.Format("SELECT * FROM (SELECT ROWNUM as RN ,T1.* FROM ({0}) T1 WHERE ROWNUM<={1}) WHERE RN>{2} ORDER BY RN ", lsSQL, (iPageSize * iPageNumber).ToString(), (iPageSize * (iPageNumber - 1)).ToString());
// dt = this.GetDataTable(lsSQL);
//}
//if (iPageNumber.Equals(0))
//{
// dt = this.GetDataTable(lsSQL);
// if (dt.Rows.Count > 0)
// {
// iMaxPageNumber = dt.Rows.Count / iPageSize + ((dt.Rows.Count % iPageSize) == 0 ? 0 : 1);
// if (iPageNumber > iMaxPageNumber)
// {
// iPageNumber = iMaxPageNumber;
// }
// }
//}
if (iPageNumber.Equals(1))
{
dt = this.GetDataTable(String.Format("SELECT COUNT(*) QTY FROM ({0})", sSql));
iRecordCount = int.Parse(dt.Rows[0][0].ToString());
iMaxPageNumber = iRecordCount / iPageSize + ((iRecordCount % iPageSize) == 0 ? 0 : 1);
if (iPageNumber > iMaxPageNumber)
{
iPageNumber = iMaxPageNumber;
}
lsSQL = String.Format("SELECT * FROM (SELECT ROWNUM as RN ,T1.* FROM ({0}) T1 WHERE ROWNUM<={1}) WHERE RN>{2} ORDER BY RN ", lsSQL, (iPageSize * iPageNumber).ToString(), (iPageSize * (iPageNumber - 1)).ToString());
dt = this.GetDataTable(lsSQL);
}
else
{
lsSQL = String.Format("SELECT * FROM (SELECT ROWNUM as RN ,T1.* FROM ({0}) T1 WHERE ROWNUM<={1}) WHERE RN>{2} ORDER BY RN ", lsSQL, (iPageSize * iPageNumber).ToString(), (iPageSize * (iPageNumber - 1)).ToString());
dt = this.GetDataTable(lsSQL);
}
this.PageSize = iPageSize;
this.PageCount = iPageNumber;
this.RecordCount = iRecordCount;
dataTable = dt;
return dt;
}
/// <summary>
/// 取得DataReader,默認連接的數據庫存是在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public OleDbDataReader GetReader(string strSQL)
{
//OleDbDataAdapter Adpter;
OleDbDataReader oReader;
OleDbConnection Conn = new OleDbConnection();
OleDbCommand oComm = new OleDbCommand();
Conn = GetConn();
oComm.Connection = Conn;
oComm.CommandText = strSQL;
try
{
oReader = oComm.ExecuteReader();
}
catch
{
return null;
}
return oReader;
}
/// <summary>
/// 取得DataReader
/// </summary>
/// <param name="strSQL"></param>
/// <param name="oConn"></param>
/// <returns></returns>
public OleDbDataReader GetReader(string strSQL, OleDbConnection oConn)
{
//OleDbDataAdapter Adpter;
OleDbDataReader oReader;
OleDbCommand oComm = new OleDbCommand();
oComm.Connection = oConn;
oComm.CommandText = strSQL;
try
{
oReader = oComm.ExecuteReader();
}
catch
{
return null;
}
return oReader;
}
/// <summary>
/// 执行 sql command
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public string ExecuteNonQuery(string strSQL, OleDbConnection Conn)
{
OleDbCommand Cmd = new OleDbCommand();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strSQL;
Cmd.Connection = Conn;
try
{
Cmd.Transaction = Conn.BeginTransaction();
Cmd.ExecuteNonQuery();
Cmd.Transaction.Commit();
return "";
}
catch (Exception Err)
{
Cmd.Transaction.Rollback();
return Err.Message;
}
finally
{
Cmd.Dispose();
}
}
/// <summary>
/// 执行 sql command,默認連接的數據庫存是在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public Boolean ExecuteNonQuery(string strSQL)
{
OleDbConnection Conn = new OleDbConnection();
Conn = this.Connection;
OleDbCommand Cmd = new OleDbCommand();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strSQL;
Cmd.Connection = Conn;
try
{
Cmd.Transaction = Conn.BeginTransaction();
Cmd.ExecuteNonQuery();
Cmd.Transaction.Commit();
return true;
}
catch (Exception Err)
{
Cmd.Transaction.Rollback();
this.Message = Err.Message;
return false;
}
finally
{
Cmd.Dispose();
}
}
/// <summary>
/// 關閉數據庫連接
/// </summary>
public void Close()
{
if (this.Connection != null)
{
if (this.Connection.State == ConnectionState.Open)
{
this.Connection.Close();
this.Connection.Dispose();
}
}
}
}
/// <summary>
/// SqlDbClientHelper,以System.Data.SqlClient類來操作數據庫
/// </summary>
public class SqlClientDbHelper
{
public string strConnString = "";
public string strMsg = "";
public SqlConnection objConn;
/// <summary>
/// 構造函數,默認為SqlClientDbConnString連接字符串的DataBase
/// </summary>
public SqlClientDbHelper()
{
//
// TODO: 在此加入建構函式的程式碼
//
this.GetConn();
}
/// <summary>
/// 帶參數構造函數
/// </summary>
/// <param name="strConnString"></param>
public SqlClientDbHelper(string strConnString)
{
this.GetConn(strConnString);
}
#region 屬性區域
/// <summary>
/// 信息属性
/// </summary>
public string Message
{
get
{
return strMsg;
}
set
{
strMsg = value;
}
}
public string ConnectionString
{
get { return strConnString; }
set { strConnString = value; }
}
/// <summary>
/// 連接對象
/// </summary>
public SqlConnection Connection
{
get { return objConn; }
set { objConn = value; }
}
#endregion
/// <summary>
/// 取得SqlClientClientcle数据库存连接,需在web.config中的appSetting处设定连接字符串SqlClientDbConnString
/// </summary>
/// <returns></returns>
public SqlConnection GetConn()
{
string strConn;
SqlConnection sqlConn = new SqlConnection();
try
{
//Get DB conncection string from web.config file.
strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlClientDbConnString"];
sqlConn.ConnectionString = strConn;
sqlConn.Open();
this.ConnectionString = strConn;
this.Connection = sqlConn;
return sqlConn;
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
}
/// <summary>
/// 取得SqlClient数据库存连接,需在web.config中的appSetting处设定连接字符串SqlClientDbConnString
/// </summary>
/// <param name="sSqlClientDbConnString">在web.config中的appSetting处设定连接字符串名稱</param>
/// <returns></returns>
public SqlConnection GetConn(string sSqlClientDbConnString)
{
string strConn;
SqlConnection sqlConn = new SqlConnection();
try
{
//Get DB conncection string from web.config file.
strConn = System.Configuration.ConfigurationSettings.AppSettings[sSqlClientDbConnString];
sqlConn.ConnectionString = strConn;
sqlConn.Open();
this.Connection = sqlConn;
return sqlConn;
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
}
/// <summary>
/// 增加此方法,
/// </summary>
/// <param name="sSqlClientDbConnString"></param>
/// <returns></returns>
public Boolean OpenConn(string sSqlClientDbConnString)
{
string strConn;
SqlConnection sqlConn = new SqlConnection();
try
{
sqlConn.ConnectionString = sSqlClientDbConnString;
sqlConn.Open();
this.Connection = sqlConn;
return true;
}
catch (Exception ex)
{
this.Message = ex.Message;
return false;
}
}
/// <summary>
/// 取得SqlClient数据库存连接
/// </summary>
/// <param name="sTNSName">连接SqlCLE的tnsname</param>
/// <param name="sUserId">用户名</param>
/// <param name="sPassword">密码</param>
/// <returns></returns>
public SqlConnection GetConn(string sServerName, string sDataBaseName, string sUserId, string sPassword)
{
string strConn;
SqlConnection sqlConn = new SqlConnection();
try
{
strConn = "server=" + sServerName + ";database=" + sDataBaseName + ";uid=" + sUserId + ";pwd=" + sPassword;
sqlConn.ConnectionString = strConn;
sqlConn.Open();
this.Connection = sqlConn;
return Connection;
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
}
/// <summary>
/// 取得数据集,默認連接的數據庫存是在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSQL)
{
SqlDataAdapter Adpter;
DataSet ds;
SqlConnection Conn = new SqlConnection();
Conn = this.Connection;
try
{
Adpter = new SqlDataAdapter(strSQL, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
return ds;
}
/// <summary>
/// 取得数据集
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public DataSet GetDataSet(string strSQL, SqlConnection Conn)
{
SqlDataAdapter Adpter;
DataSet ds;
try
{
Adpter = new SqlDataAdapter(strSQL, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
return ds;
}
/// <summary>
/// 得到一张表,根据SQL语句,如果沒有找到資料,連接正確,行個數為0,如果連接異常的,返回為空
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public DataTable GetDataTable(string strSql)
{
SqlDataAdapter Adpter;
DataSet ds;
DataTable dtTable = new DataTable();
SqlConnection Conn = new SqlConnection();
Conn = this.Connection;
if (Conn == null)
{
return dtTable;
}
try
{
Adpter = new SqlDataAdapter(strSql, Conn);
ds = new DataSet();
Adpter.Fill(ds, "table1");
dtTable = ds.Tables[0];
this.Message = null;
}
catch (Exception ex)
{
this.Message = "sql:" + strSql + ";" + ex.Message + ";" + ex.StackTrace;
}
return dtTable;
}
/// <summary>
/// 根据分页的要求,开发分页查询功能
/// </summary>
/// <param name="sSql"></param>
/// <param name="sSortExpression"></param>
/// <param name="iPageSize"></param>
/// <param name="iPageNumber"></param>
/// <param name="iMaxPageNumber"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public DataTable GetDataTable(String sSql, String sSortExpression, int iPageSize, int iPageNumber, out int iMaxPageNumber, out DataTable dataTable)
{
DataTable dt = new DataTable();
Boolean lbResult = false;
string _sMessage = "";
int iRowCount = 0;
String lsSQL = String.Empty;
dataTable = new DataTable();
iMaxPageNumber = 0;
if (iPageSize <= 0)
{
iPageSize = 10;
}
if (iPageNumber < 0)
{
iPageNumber = 1;
}
if (sSortExpression.Equals(""))
{
lsSQL = sSql;
}
else
{
lsSQL = String.Format("SELECT * FROM ({0}) ORDER BY {1}", sSql, sSortExpression);
}
if (iPageNumber.Equals(0))
{
dt = this.GetDataTable(lsSQL);
if (dt.Rows.Count > 0)
{
iMaxPageNumber = dt.Rows.Count / iPageSize + ((dt.Rows.Count % iPageSize) == 0 ? 0 : 1);
if (iPageNumber > iMaxPageNumber)
{
iPageNumber = iMaxPageNumber;
}
}
}
else
{
dt = this.GetDataTable(String.Format("SELECT COUNT(*) QTY FROM ({0})", sSql));
iRowCount = dt.Rows.Count;
iMaxPageNumber = iRowCount / iPageSize + ((iRowCount % iPageSize) == 0 ? 0 : 1);
if (iPageNumber > iMaxPageNumber)
{
iPageNumber = iMaxPageNumber;
}
lsSQL = String.Format("SELECT * FROM (SELECT ROWNUM as RN ,T1.* FROM ({0}) T1 WHERE ROWNUM<={1}) WHERE RN>{2} ORDER BY RN ", lsSQL, (iPageSize * iPageNumber).ToString(), (iPageSize * (iPageNumber - 1)).ToString());
dt = this.GetDataTable(lsSQL);
}
return dt;
}
/// <summary>
/// 取得DataReader,默認連接的數據庫存是在web.config中的appSetting处设定连接字符串SqlDbConnString
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public SqlDataReader GetReader(string strSQL)
{
//SqlDataAdapter Adpter;
SqlDataReader oReader = null;
SqlConnection Conn = new SqlConnection();
SqlCommand oComm = new SqlCommand();
Conn = this.Connection;
oComm.Connection = Conn;
oComm.CommandText = strSQL;
try
{
oReader = oComm.ExecuteReader();
}
catch (Exception ex)
{
this.Message += ex.Message;
return null;
}
return oReader;
}
/// <summary>
/// 取得DataReader
/// </summary>
/// <param name="strSQL"></param>
/// <param name="oConn"></param>
/// <returns></returns>
public SqlDataReader GetReader(string strSQL, SqlConnection oConn)
{
//SqlDataAdapter Adpter;
SqlDataReader oReader;
SqlCommand oComm = new SqlCommand();
oComm.Connection = oConn;
oComm.CommandText = strSQL;
try
{
oReader = oComm.ExecuteReader();
}
catch (Exception ex)
{
this.Message = ex.Message;
return null;
}
return oReader;
}
/// <summary>
/// 执行SQL语句,以事务提交方法处理,并异常以字符串返回!
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public string ExecuteNonQuery(string strSQL)
{
SqlConnection Conn = new SqlConnection();
Conn = this.Connection;
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strSQL;
Cmd.Connection = Conn;
try
{
Cmd.Transaction = Conn.BeginTransaction();
Cmd.ExecuteNonQuery();
Cmd.Transaction.Commit();
return "";
}
catch (Exception ex)
{
Cmd.Transaction.Rollback();
return ex.Message + ex.StackTrace;
}
finally
{
Cmd.Dispose();
}
}
/// <summary>
/// 执行 sql command 并以Transaction进行提交数据
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public string ExecuteNonQuery(string strSQL, SqlConnection Conn)
{
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = strSQL;
Cmd.Connection = Conn;
try
{
Cmd.Transaction = Conn.BeginTransaction();
Cmd.ExecuteNonQuery();
Cmd.Transaction.Commit();
return "";
}
catch (Exception ex)
{
Cmd.Transaction.Rollback();
return ex.Message + ex.StackTrace;
}
finally
{
Cmd.Dispose();
}
}
/// <summary>
/// 關閉數據庫連接
/// </summary>
public void Close()
{
if (this.Connection != null)
{
if (this.Connection.State == ConnectionState.Open)
{
this.Connection.Close();
this.Connection.Dispose();
}
}
}
}
#region Microsoft Excel幫助類
/// <summary>
/// Microsoft Excel幫助類
/// </summary>
public class ExcelHelper
{
string strMessage = "";
/// <summary>
/// 構造函數
/// </summary>
public ExcelHelper()
{
}
/// <summary>
/// 信息屬性
/// </summary>
public string Message
{
set { strMessage = value; }
get { return strMessage; }
}
/// <summary>
/// 返回一張表,默認為Sheet1
/// </summary>
/// <param name="strFileFullName"></param>
/// <returns></returns>
public DataTable GetTableByExcel2003(string strFileFullName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
/// <summary>
/// 返回一張表,默認為Sheet1
/// </summary>
/// <param name="strFileFullName"></param>
/// <returns></returns>
public DataTable GetTableByExcel2007(string strFileFullName)
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileFullName + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
/// <summary>
/// 根据Excel驱动类型来读取EXcel文件
/// </summary>
/// <param name="strDrive">例如 Microsoft.ACE.OLEDB.12.0 </param>
/// <param name="strFileFullName">文件全路径,包括文件名</param>
/// <returns></returns>
public DataTable GetTableByExcel(string strDrive, string strFileFullName)
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileFullName + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
}
#endregion