1、新建一个 数据库访问基类DBHandler
2、新建一个参数类
3、新建一个Sqlser数据库访问类
4、新建一个工厂类
5.....其它的数据库访问类,可以参照sqlserver数据库访问类创建。
- /// <summary>
- /// 数据库访问基类
- /// </summary>
- public abstract class DBHandler
- {
- public DBHandler() { }
- #region 需要在子类中初始化的与数据库相关的特征类
- protected DbConnection dbConnection = null; //连接对象
- protected DbTransaction dbTransaction = null; //事务对象
- protected abstract DbCommand CreateCommand(); //从子类中构建DbCommand对象
- protected abstract DbDataAdapter CreateAdapter(); //从子类中构建DbDataAdapter对象
- protected abstract void BuilderCommand(DbDataAdapter adapter); //用于Update方法,构建DbDataAdapter中的UpdateCommand/InsertCommand/DeleteCommand
- protected abstract int GetTotalCount(); //用于分页查询,获取总记录数
- #endregion
- #region 子类中要用的数据或方法
- protected List<Parameter> parameters = new List<Parameter>();
- protected bool IsInTransaction = false; //是否处于事务当中
- //用于分页查询,检查当前SQL是否符合基本查询要求
- protected void CheckPageSQL()
- {
- this.CommandType = CommandType.Text;
- if (!this.CommandText.StartsWith("select", true, null))
- {
- throw new Exception("sql语句必须是select开头");
- }
- if (IsInTransaction)
- {
- throw new Exception("分页查询不能在事务中");
- }
- }
- #endregion
- #region 用于输入初始条件的属性和方法
- /// <summary>
- /// sql语句或存储过程名称
- /// </summary>
- public string CommandText { get; set; }
- /// <summary>
- /// 执行类型是SQL还是存储过程
- /// </summary>
- public CommandType CommandType { get; set; }
- /// <summary>
- /// 添加参数,将参数添加到List<T>列表中保存起来
- /// </summary>
- /// <param name="paraName">参数名</param>
- /// <param name="paraValue">参数值</param>
- public void AddParameter(string paraName, string paraValue)
- {
- this.parameters.Add(new Parameter(paraName,paraValue));
- }
- /// <summary>
- /// 清空当前参数列表
- /// </summary>
- public void ClearParameter()
- {
- this.parameters.Clear();
- }
- /// <summary>
- /// 用于分页查询,获取查询到的总记录数
- /// </summary>
- public int TotalCount
- {
- get
- {
- return this.GetTotalCount();
- }
- }
- #endregion
- #region 获取数据库的返回值
- /// <summary>
- /// 获取执行结果的第一行第一列的值
- /// </summary>
- /// <returns></returns>
- public object ExecuteScalar()
- {
- try
- {
- if (dbConnection.State != ConnectionState.Open)
- {
- dbConnection.Open();
- }
- DbCommand cmd = this.CreateCommand();
- object r = cmd.ExecuteScalar();
- if (!this.IsInTransaction)
- {
- dbConnection.Close();
- }
- return r;
- }
- catch (Exception ex)
- {
- this.dbConnection.Close();
- throw new Exception(ex.Message);
- }
- }
- /// <summary>
- /// 执行没有数据集的命令 Update/Delete/Insert
- /// </summary>
- /// <returns></returns>
- public int ExecuteNonQuery()
- {
- try
- {
- if (this.dbConnection.State != ConnectionState.Open)
- {
- this.dbConnection.Open();
- }
- DbCommand cmd = this.CreateCommand();
- int r = cmd.ExecuteNonQuery();
- if (!IsInTransaction)
- {
- dbConnection.Close();
- }
- return r;
- }
- catch (Exception ex)
- {
- dbConnection.Close();
- throw new Exception(ex.Message);
- }
- }
- /// <summary>
- /// 执行结果的DataTable集
- /// </summary>
- /// <returns></returns>
- public DataTable ExecuteDataTable()
- {
- try
- {
- if (this.dbConnection.State != ConnectionState.Open)
- {
- this.dbConnection.Open();
- }
- DbDataAdapter adapter = this.CreateAdapter();
- DataTable dt = new DataTable();
- adapter.FillSchema(dt, SchemaType.Mapped);
- adapter.Fill(dt);
- if (!IsInTransaction)
- {
- dbConnection.Close();
- }
- return dt;
- }
- catch (Exception ex)
- {
- dbConnection.Close();
- throw new Exception(ex.Message);
- }
- }
- /// <summary>
- /// 用于分页查询,获取指定页码的数据集
- /// </summary>
- /// <param name="pageSize">每页记录数</param>
- /// <param name="currentPageIndex">当前页码</param>
- /// <returns>指定页的记录集</returns>
- public abstract DataTable ExecuteDataTable(int pageSize,int currentPageIndex);
- #endregion
- #region 将DataTable更新到数据库中
- public int UpdateData(DataTable dt)
- {
- try
- {
- if (this.dbConnection.State != ConnectionState.Open)
- {
- this.dbConnection.Open();
- }
- DbDataAdapter adapter = this.CreateAdapter();
- if (this.CommandType == CommandType.StoredProcedure)
- {
- this.CommandType = CommandType.Text;
- }
- this.BuilderCommand(adapter);
- int r = adapter.Update(dt);
- if (!IsInTransaction)
- {
- dbConnection.Close();
- }
- return r;
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- #endregion
- #region 事务处理
- /// <summary>
- /// 开始一个事务
- /// </summary>
- public void BegionTransaction()
- {
- try
- {
- if (this.dbConnection.State != ConnectionState.Open)
- {
- this.dbConnection.Open();
- }
- this.dbConnection.BeginTransaction();
- this.IsInTransaction = true;
- }
- catch (Exception ex)
- {
- this.dbConnection.Close();
- this.IsInTransaction = false;
- throw ex;
- }
- }
- /// <summary>
- /// 回滚一个事务
- /// </summary>
- public void RollbackTransaction()
- {
- try
- {
- this.dbTransaction.Rollback();
- this.dbConnection.Close();
- this.IsInTransaction = false;
- }
- catch (Exception ex)
- {
- this.dbConnection.Close();
- this.IsInTransaction = false;
- throw ex;
- }
- }
- /// <summary>
- /// 提交一个事务
- /// </summary>
- public void CommitTransaction()
- {
- try
- {
- this.dbTransaction.Commit();
- this.dbConnection.Close();
- this.IsInTransaction = false;
- }
- catch (Exception ex)
- {
- this.dbConnection.Close();
- this.IsInTransaction = false;
- throw ex;
- }
- }
- #endregion
- #region 建立数据行对象
- #endregion
- #region 对序列的读取
- /// <summary>
- /// 获取序列的值,对于非oracle数据库,必须建立一个名为System_Sequence的表,表的字段为(Name(nvarchar,50),Value(int))
- /// </summary>
- /// <param name="sequenceName"></param>
- /// <returns></returns>
- public abstract int GetSequenceValue(string sequenceName);
- #endregion
- }
- public class Parameter
- {
- public string Name = string.Empty;
- public object Value = null;
- public Parameter(string name,string value)
- {
- this.Name = name;
- this.Value = value;
- }
- }
- internal class DBhANDlerSQLServer:DBHandler
- {
- public DBhANDlerSQLServer(string connectionString)
- : base()
- {
- this.dbConnection = new SqlConnection(connectionString);
- }
- protected override DbCommand CreateCommand()
- {
- SqlCommand cmd = new SqlCommand();
- cmd.Connection = (SqlConnection)this.dbConnection;
- if (this.IsInTransaction)
- {
- cmd.Transaction = (SqlTransaction)this.dbTransaction;
- }
- if (this.CommandType == CommandType.TableDirect)
- {
- cmd.CommandType = CommandType.Text;
- cmd.CommandText = string.Format("select * from {0}", this.CommandText);
- }
- else
- {
- cmd.CommandType=CommandType;
- cmd.CommandText = CommandText;
- }
- if (this.parameters.Count > 0)
- {
- foreach (Parameter p in parameters)
- {
- cmd.Parameters.AddWithValue(p.Name, p.Value);
- }
- }
- return cmd;
- }
- protected override DbDataAdapter CreateAdapter()
- {
- SqlCommand cmd = (SqlCommand)this.CreateCommand();
- SqlDataAdapter adapter = new SqlDataAdapter(cmd);
- return adapter;
- }
- protected override void BuilderCommand(DbDataAdapter adapter)
- {
- new SqlCommandBuilder((SqlDataAdapter)adapter);
- }
- protected override int GetTotalCount()
- {
- this.CheckPageSQL();
- string sql = this.CommandText; //保留原始SQL
- string sqlWithOutOrderField = string.Empty; //将原始SQL语句去掉 order by 后的部分,用于查询总记录数
- int startIndex = sql.LastIndexOf("order by");
- if (startIndex >= 0)
- {
- sqlWithOutOrderField = sql.Substring(0, startIndex);
- }
- else
- {
- sqlWithOutOrderField = sql;
- }
- this.CommandText = string.Format("select count(*)from ({0}) t1",sqlWithOutOrderField);
- int r = int.Parse(this.ExecuteScalar().ToString());
- this.CommandText = sql;
- return r;
- }
- public override DataTable ExecuteDataTable(int pageSize, int currentPageIndex)
- {
- this.CheckPageSQL();
- string sql = this.CommandText; //保留原始SQL
- string orderBy = string.Empty; //将order by字句保留下来
- string sqlWithSelectAndOrder = sql.Substring(6); //去掉select以及整个order by
- int startIndex = sqlWithSelectAndOrder.ToLower().LastIndexOf("order by");
- if (startIndex > 0)
- {
- orderBy = sqlWithSelectAndOrder.Substring(startIndex);
- sqlWithSelectAndOrder = sqlWithSelectAndOrder.Substring(0, startIndex);
- }
- else
- {
- throw new Exception("sql的分页查询必须有order by");
- }
- if (pageSize == 0) //返回所有数据
- {
- this.CommandText = sql;
- this.ExecuteDataTable();
- }
- DataTable dt = new DataTable();
- if (currentPageIndex == 1) //如果当前页为1
- {
- this.CommandText = string.Format("select top {0} {1} {2}", pageSize, sqlWithSelectAndOrder, orderBy);
- dt = this.ExecuteDataTable();
- }
- else //适合sqlserver2005及以上的版本,但必须带 order by子句
- {
- StringBuilder sb = new StringBuilder();
- sb.Append("select * from ");
- sb.AppendFormat("(select Row_Number() over ({0}) as RowNum,{1})t1",orderBy,sqlWithSelectAndOrder);
- sb.AppendFormat(" where RowNum between {0} and {1}",pageSize*(currentPageIndex-1),pageSize*currentPageIndex-1);
- this.CommandText = sb.ToString();
- dt = this.ExecuteDataTable();
- }
- this.CommandText = sql;
- return dt;
- }
- public override int GetSequenceValue(string sequenceName)
- {
- //sqlserver 先要建立配套的系列表 System_Sequence
- /*建表语句:if not exists(select * from sysobjects where Name='System_Sequence')
- create table System_Sequence
- {
- Name varchar(50),
- Value int,
- constraint "PK_SsystemSequence" primary key(Name)
- };
- insert into System_Sequence(Name,Value) values('Sequence_<表名>',0); */
- this.CommandType = CommandType.Text;
- this.BegionTransaction();
- this.CommandText = string.Format("Update System_Sequence set Value=Value+1 where Name='{0}'", sequenceName);
- this.ExecuteNonQuery();
- this.CommandText = string.Format("select Value from System_Sequence where Name='{0}'", sequenceName);
- int r = this.ExecuteNonQuery();
- this.CommitTransaction();
- return r;
- }
- }
- public enum DatabaseType
- {
- SqlServer = 1,
- Oracle = 2,
- ODBC = 3,
- OLEDB = 4
- }
- ublic class DBHandlerFactory
- {
- //禁止产生类的实例
- private DBHandlerFactory() { }
- /// <summary>
- /// 读取webconfig中的ConnectionString配置节点构造实例
- /// </summary>
- /// <param name="connStr"></param>
- /// <returns></returns>
- public static DBHandler GetHandler(string connStr)
- {
- ConnectionStringSettings ccs = ConfigurationManager.ConnectionStrings[connStr];
- string providerName = ccs.ProviderName.ToLower();
- DatabaseType dbType = DatabaseType.SqlServer;
- switch (providerName)
- {
- case "":
- case "sqlserver":
- case "system.data.sqlclient":
- dbType = DatabaseType.SqlServer;
- break;
- case "oracle":
- case"system.data.oracleclient":
- dbType = DatabaseType.Oracle;
- break;
- case "odbc":
- case "system.data.odbc":
- dbType = DatabaseType.ODBC;
- break;
- case "oledb":
- case "system.data.oledb":
- dbType = DatabaseType.OLEDB;
- break;
- default:
- throw new Exception("请按照格式定义ProviderName属性");
- }
- switch (dbType)
- {
- case DatabaseType.SqlServer:
- return new DBhANDlerSQLServer(ccs.ConnectionString);
- case DatabaseType.Oracle:
- return null;
- case DatabaseType.ODBC:
- return null;
- case DatabaseType.OLEDB:
- return null;
- default:
- return null;
- }
- }
- /// <summary>
- /// 直接以连接字符串和数据库类型构造
- /// </summary>
- /// <param name="connStr"></param>
- /// <param name="dbType"></param>
- /// <returns></returns>
- public static DBHandler GetHandler(string connStr,DatabaseType dbType)
- {
- switch (dbType)
- {
- case DatabaseType.SqlServer:
- return new DBhANDlerSQLServer(connStr);
- case DatabaseType.Oracle:
- return null;
- case DatabaseType.ODBC:
- return null;
- case DatabaseType.OLEDB:
- return null;
- default:
- return null;
- }
- }
- }