珂珂的个人博客 - 一个程序猿的个人网站
上一篇定义了一些接口,接下来做一个实现呢
先来定义一个抽象类GetDataAbstract 作用是获取不同数据类型的值,方便操作，当然没有写完所有类型。
该类包含一个抽象方法 public abstract DataSet GetDataSet(IEnumerableparms, string sql);
然后下面这些方法就可以根据抽象方法取值,无非就是取不同类型的值嘛.
object GetObject(IEnumerableparms, string sql); object GetObject(string sql, params IDataParameter[] parms); string GetString(IEnumerableparms, string sql); string GetString(string sql, params IDataParameter[] parms); long GetLong(IEnumerableparms, string sql); long GetLong(string sql, params IDataParameter[] parms); double GetDouble(IEnumerableparms, string sql); double GetDouble(string sql, params IDataParameter[] parms); int GetInt(IEnumerableparms, string sql); int GetInt(string sql, params IDataParameter[] parms); bool GetBoolean(IEnumerableparms, string sql); bool GetBoolean(string sql, params IDataParameter[] parms); bool Exists(IEnumerableparms, string sql); bool Exists(string sql, params IDataParameter[] parms);
然后再来写一个类DbHelperAbstract继承上面的类，这个类仍然是抽象类
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Text;
using FYJ.Data.Util;
using System.Configuration;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;
namespace FYJ.Data
{
/// <summary>
/// 功能:数据库抽象类
/// 作者:fangyj
/// 创建日期:
/// 修改日期:
/// </summary>
public abstract class DbHelperAbstract : GetDataAbstract, IDbHelper
{
private DbProviderFactory factory;
private string connectionString;
#region 属性
public DbProviderFactory DbProviderFactoryInstance
{
get
{
return factory;
}
set
{
this.factory = value;
}
}
public virtual String ConnectionString
{
get
{
return connectionString;
}
set
{
this.connectionString = value;
}
}
public DbHelperType DbHelperTypeEnum
{
get
{
if (factory.GetType() == typeof(System.Data.SqlClient.SqlClientFactory))
{
return DbHelperType.SqlServer;
}
else if (factory.GetType() == typeof(System.Data.OleDb.OleDbFactory))
{
return DbHelperType.OleDb;
}
else if (factory.GetType() == typeof(System.Data.Odbc.OdbcFactory))
{
return DbHelperType.Odbc;
}
else if (factory.GetType().FullName.Contains("MySql.Data"))
{
return DbHelperType.MySql;
}
else if (factory.GetType().FullName.Contains("Oracle"))
{
return DbHelperType.Oracle;
}
else if (factory.GetType().FullName.Contains("SQLite"))
{
return DbHelperType.SqlLite;
}
return DbHelperType.Other;
}
}
public abstract DbTransaction Tran { get; }
#endregion
#region 构造函数
protected DbHelperAbstract()
{
}
protected DbHelperAbstract(string connectionName)
{
this.factory = DbProviderFactories.GetFactory(System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ProviderName);
this.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}
protected DbHelperAbstract(DbProviderFactory factory, string connectionString)
{
this.factory = factory;
this.ConnectionString = connectionString;
}
#endregion
#region 执行
public abstract int ExecuteSql(IEnumerable<IDataParameter> parms, string sql);
public abstract DataSet RunProcedure(IEnumerable<IDataParameter> parms, string storedProcName);
public abstract DataSet RunProcedure(string storedProcName, Dictionary<string, object> dic);
public abstract int ExecuteProcedure(IEnumerable<IDataParameter> parms, string storedProcName);
public abstract int ExecuteProcedure(string storedProcName, Dictionary<string, object> dic);
public int ExecuteSql(string sql, params IDataParameter[] parms)
{
return ExecuteSql(parms, sql);
}
public int ExecuteSql(string tableName, string pkName, bool iaAdd, IEnumerable<IDataParameter> parms)
{
string sql = "";
if (iaAdd)
{
sql = Helper.GetAddSql(tableName, pkName, parms);
}
else
{
sql = Helper.GetUpdateSql(tableName, pkName, parms);
}
return this.ExecuteSql(parms, sql);
}
public DataSet RunProcedure(string storedProcName, params IDataParameter[] parms)
{
IEnumerable<IDataParameter> par = parms;
return this.RunProcedure(par, storedProcName);
}
public int ExecuteProcedure(string storedProcName, params IDataParameter[] parms)
{
IEnumerable<IDataParameter> par = parms;
return this.ExecuteProcedure(par, storedProcName);
}
#endregion
#region 查询
/// <summary>
/// 根据表名查询该表是否存在
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public virtual bool ExistsTable(string tableName)
{
try
{
DataTable dt = GetDataTable(tableName, 1);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 获取查询的数据列表 IDataParameter参数前缀@ ：或者不加都会自动修正
/// count传递null表示不返回总数
/// </summary>
/// <param name="count">是否返回总记录条数 </param>
/// <param name="tableName">表或视图名 </param>
/// <param name="accessProperty">要访问的列</param>
/// <param name="where">查询条件</param>
/// <param name="orderProperty">排序</param>
/// <param name="currentPage">当前页 默认1</param>
/// <param name="pageSize">每页显示条数 默认20</param>
/// <returns></returns>
/// <author>fangyj 2012-07-15</author>
public virtual DataTable GetDataTable(ref long? count, string tableName, string order, string[] accessProperty = null, string where = null, int currentPage = 1, int pageSize = 20)
{
//如果当前页数传递0 则默认为第一页
if (currentPage == 0)
{
currentPage = 1;
}
if (pageSize < 1)
{
throw new Exception("分页大小不能小于1");
}
String select = "";
if (accessProperty == null)
select = "*";
else
{
foreach (String s in accessProperty)
{
select += s + ",";
}
}
select = select.TrimEnd(',');
//如果条件不为空 但是where参数没有包含where 则自动加上
if ((!String.IsNullOrEmpty(where)) && (!where.TrimStart().StartsWith("where", StringComparison.CurrentCultureIgnoreCase)))
where = " where " + where;
//如果排序不为空 但是order参数没有包含order by则自动加上
if (!String.IsNullOrEmpty(order))
{
if (!order.TrimStart().StartsWith("order", StringComparison.CurrentCultureIgnoreCase))
order = "order by " + order;
}
//构造sql语句
String sql = String.Format("select {0} from {1} {2} {3}", select, tableName, where, order);
if (currentPage > 0)
{
if (pageSize < 1)
throw new Exception("要求分页时分页大小必须是正整数！");
//sql server oledb access 分页
if ((this.DbHelperTypeEnum == DbHelperType.SqlServer)
|| (this.DbHelperTypeEnum == DbHelperType.OleDb))
{
if (String.IsNullOrEmpty(order))
{
throw new Exception("SqlServer 分页时order 不能为空");
}
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT * FROM");
sb.AppendLine("(");
sb.AppendLine("SELECT row_number() over(" + order + ") AS rownum," + select + " FROM " + tableName + " " + where);
sb.AppendLine(") AS table1 ");
sb.AppendLine(" WHERE (rownum BETWEEN " + ((currentPage - 1) * pageSize + 1) + " AND " + (currentPage * pageSize) + " )" + order);
sql = sb.ToString();
}
//sqllite mysql分页
if ((DbHelperTypeEnum == DbHelperType.SqlLite)
|| (DbHelperTypeEnum == DbHelperType.MySql))
{
sql = String.Format("select {0} from {1} {2} {3} limit {4},{5} ", select, tableName, where, order, pageSize * (currentPage - 1), pageSize);
}
//oracle分页
if (DbHelperTypeEnum == DbHelperType.Oracle)
{
sql = String.Format("select * from (select a.*,ROWNUM RN FROM (SELECT {0} FROM {1} {2} {3} ) A WHERE RN BETWEEN {4} AND {5})", select, tableName, where, order, pageSize * (currentPage - 1), pageSize * currentPage);
}
}
if (currentPage == 0) //如果不需要返回数据（对于只获取行数） 则返回null
{
if (count != null)
{
String countSql = String.Format("select count(*) from {0} {1}", tableName, where);
count = Convert.ToInt64(GetDataTable(countSql).Rows[0][0]);
}
return null;
}
else
{
DataSet ds = new DataSet();
if (count != null) //如果需要返回行数 则添加行数sql
{
String countSql = String.Format("select count(*) from {0} {1}", tableName, where);
sql += ";\n" + countSql;
}
ds = GetDataSet(sql);
if (count != null) //如果需要返回行数
{
count = Convert.ToInt64(ds.Tables[1].Rows[0][0]);
}
return ds.Tables[0];
}
}
public virtual List<string> GetTables()
{
List<String> list = new List<string>();
if (this.DbHelperTypeEnum == DbHelperType.SqlServer)
{
//select * from sysobjects where [type]='U'
string sql = "select * from information_schema.tables where table_type='base table'";
DataTable dt = this.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
list.Add(dr["TABLE_NAME"].ToString());
}
}
if (this.DbHelperTypeEnum == DbHelperType.MySql)
{
string sql = "SHOW TABLES"; //SHOW TABLES FROM database, SHOW COLUMNS FROM TABLE
DataTable dt = this.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
list.Add(dr[0].ToString());
}
}
return list;
}
/// <summary>
/// 获取前max行数据 如果max小于1 则返回所有行
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="max"></param>
/// <param name="isAddWithKey"></param>
/// <returns></returns>
/// <author>fangyj 2012-09-27</author>
public virtual DataTable GetDataTable(string tableName, long max)
{
DataTable dt = null;
if (max < 1)
{
dt = GetDataTable("select * from " + tableName);
}
else
{
//sql server oledb access 分页
if ((this.DbHelperTypeEnum == DbHelperType.SqlServer)
|| (this.DbHelperTypeEnum == DbHelperType.OleDb))
{
dt = GetDataTable("select top " + max + " * from " + tableName);
}
//sqllite mysql分页
if ((this.DbHelperTypeEnum == DbHelperType.SqlLite)
|| (this.DbHelperTypeEnum == DbHelperType.MySql))
{
dt = GetDataTable("select * from " + tableName + " limit 0," + max);
}
//oracle分页
if (this.DbHelperTypeEnum == DbHelperType.Oracle)
{
dt = GetDataTable(String.Format("select * from (select a.*,ROWNUM RN FROM (SELECT * FROM {0} ) A) WHERE RN BETWEEN {1} AND {2}", tableName, 0, max));
}
}
dt.TableName = tableName;
return dt;
}
#endregion
/// <summary>
/// 构造参数
/// </summary>
/// <param name="parameterName">[可选参数]参数名</param>
/// <param name="parameterValue">[可选参数]参数值</param>
/// <param name="direction">[可选参数]参数类型</param>
/// <returns></returns>
public virtual DbParameter CreateParameter(string parameterName = null, object parameterValue = null, ParameterDirection? direction = null)
{
DbParameter paramter = this.factory.CreateParameter();
if (parameterName != null)
{
paramter.ParameterName = parameterName;
}
if (parameterValue != null)
{
paramter.Value = parameterValue;
}
if (direction != null)
{
paramter.Direction = direction.Value;
}
return paramter;
}
/// <summary>
/// 开始事务
/// </summary>
public abstract void BeginTran();
/// <summary>
/// 提交事务
/// </summary>
public abstract void Commit();
/// <summary>
/// 回滚
/// </summary>
public abstract void Rollback();
public virtual bool TestCanConnectionOpen()
{
try
{
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = this.connectionString;
conn.Open();
conn.Close();
return true;
}
catch
{
return false;
}
}
}
}好了剩下的一个类写在下一篇