using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Configuration;
using System.Data;
namespace AutoEmailSender
{
/// <summary>
/// 數據庫交互類
/// </summary>
public class DB
{
/// <summary>
/// 獲得數據庫連接
/// </summary>
/// <returns></returns>
public static OleDbConnection GetDBConnection()
{
return new OleDbConnection(ConfigurationManager.AppSettings["ConnectString"]);
}
/// <summary>
/// 查詢結果集
/// </summary>
/// <param name="sql">執行語句</param>
/// <returns>返回壹個DataTable對象</returns>
public static DataTable ExecuteDataTable(string sql)
{
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
return ExecuteDataTable(cmd);
}
}
/// <summary>
/// 查詢結果集
/// </summary>
/// <param name="cmd">執行語句的OleDbCommand命令</param>
/// <returns>返回壹個DataTable對象</returns>
public static DataTable ExecuteDataTable(OleDbCommand cmd)
{
DataSet ds = new DataSet();
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
try
{
da.Fill(ds);
}
catch (Exception e)
{
throw e;
}
}
if (ds.Tables.Count > 0)
{
ds.Tables[0].DefaultView.RowStateFilter = DataViewRowState.Unchanged | DataViewRowState.Added | DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted;
return ds.Tables[0];
}
else
return null;
}
/// <summary>
/// 執行查詢，並返回查詢所返回的結果集中第壹行的第壹列。忽略其他列或行。
/// </summary>
/// <param name="sql">查詢語句</param>
/// <returns>返回結果集中第壹行的第壹列的object值</returns>
public static object ExecuteScalar(string sql)
{
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
return ExecuteScalar(cmd);
}
}
/// <summary>
/// 執行查詢，並返回查詢所返回的結果集中第壹行的第壹列。忽略其他列或行。
/// </summary>
/// <param name="cmd">查詢命令</param>
/// <returns>返回結果集中第壹行的第壹列的object值</returns>
public static object ExecuteScalar(OleDbCommand cmd)
{
try
{
cmd.Connection.Open();
object obj = cmd.ExecuteScalar();
cmd.Connection.Close();
return obj;
}
catch (Exception error)
{
cmd.Connection.Close();
throw error;
}
}
/// <summary>
/// 更新數據集
/// </summary>
/// <param name="dt">要更新的數據集</param>
/// <param name="insertCmd">插入SQL語句</param>
/// <param name="updateCmd">更新SQL語句</param>
/// <param name="deleteCmd">刪除SQL語句</param>
/// <returns></returns>
public static int UpdateDataSet(DataTable dt, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd)
{
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.InsertCommand = insertCmd;
da.UpdateCommand = updateCmd;
da.DeleteCommand = deleteCmd;
//da.UpdateBatchSize = 0; //UpdateBatchSize:指定可在壹次批處理中執行的命令的數量,在Access不被支持。0:批大小沒有限制。1:禁用批量更新。>1:更改是使用 UpdateBatchSize 操作的批處理壹次性發送的。
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
try
{
int row = da.Update(dt);
return row;
}
catch (Exception e)
{
throw e;
}
}
}
/// <summary>
/// 返回壹個查詢語句執行結果的表結構
/// </summary>
/// <param name="sql">查詢語句,不支持復雜SQL</param>
/// <returns></returns>
public static DataTable GetTableSchema(string sql)
{
sql = sql.ToUpper();
DataTable dt = null;
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
con.Open();
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection))
{
dt = dr.GetSchemaTable();
}
}
return dt;
}
/// <summary>
/// 根據輸入的查詢語句自動生成插入,更新,刪除命令
/// </summary>
/// <param name="sql">查詢語句</param>
/// <param name="insertCmd">插入命令</param>
/// <param name="updateCmd">更新命令</param>
/// <param name="deleteCmd">刪除命令</param>
public static void GenerateUpdateSQL(string sql, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd)
{
sql = sql.ToUpper();
DataTable dt = GetTableSchema(sql);
string tableName = dt.Rows[0]["BaseTableName"].ToString();
List<OleDbParameter> updatePrimarykeys = new List<OleDbParameter>();//主鍵參數集合
List<OleDbParameter> deletePrimarykeys = new List<OleDbParameter>();//主鍵參數集合,因為不能同時被OleDbCommand個命令引用,所以多申明壹個
List<OleDbParameter> insertFields = new List<OleDbParameter>();//字段參數集合
List<OleDbParameter> updateFields = new List<OleDbParameter>();//字段參數集合
string columns = string.Empty, values = "", set = "", where = "";
foreach (DataRow dr in dt.Rows)
{
if (dr["IsAutoIncrement"].ToString().Equals("False"))
{
insertFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
dr["BaseColumnName"].ToString()));
updateFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
dr["BaseColumnName"].ToString()));
if (!string.IsNullOrEmpty(columns))
columns += ",";
columns += dr["BaseColumnName"].ToString();
if (!string.IsNullOrEmpty(values))
values += ",";
values += "@" + dr["BaseColumnName"].ToString();
if (!string.IsNullOrEmpty(set))
set += ",";
set += dr["BaseColumnName"].ToString() + "=@" + dr["BaseColumnName"].ToString();
}
if (dr["IsKey"].ToString().Equals("True"))
{
updatePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
ParameterDirection.Input,
Convert.ToBoolean(dr["AllowDBNull"]),
Convert.ToByte(dr["NumericScale"]),
Convert.ToByte(dr["NumericPrecision"]),
dr["BaseColumnName"].ToString(), DataRowVersion.Original, null));
deletePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
ParameterDirection.Input,
Convert.ToBoolean(dr["AllowDBNull"]),
Convert.ToByte(dr["NumericScale"]),
Convert.ToByte(dr["NumericPrecision"]),
dr["BaseColumnName"].ToString(), DataRowVersion.Original, null));
if (!string.IsNullOrEmpty(where))
where += " and ";
where += dr["BaseColumnName"].ToString() + "=@OLD_" + dr["BaseColumnName"].ToString();
}
}
insertCmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, columns, values);
updateCmd.CommandText = string.Format("update {0} set {1} where {2}", tableName, set, where);
deleteCmd.CommandText = string.Format("delete from {0} where {1}", tableName, where);
insertCmd.Connection = GetDBConnection();
updateCmd.Connection = GetDBConnection();
deleteCmd.Connection = GetDBConnection();
foreach (OleDbParameter pa in insertFields)
{
insertCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in updateFields)
{
updateCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in updatePrimarykeys)
{
updateCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in deletePrimarykeys)
{
deleteCmd.Parameters.Add(pa);
}
}
}
}