1.生成对象工厂
/// <summary>
/// 生成对象工厂
/// </summary>
public class DBFactory
{
/// <summary>
/// 创造实例
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="assemblyString">程序集名称</param>
/// <param name="typeName">类的全名</param>
/// <param name="parameters">构造函数参数</param>
/// <returns></returns>
public T Creatobject<T>(string assemblyString, string typeName, Object[] parameters)
{
try
{
return (T)System.Reflection.Assembly.Load(assemblyString).CreateInstance(typeName, true, System.Reflection.BindingFlags.Default, null, parameters, null, null);
}
catch (Exception ex)
{
string s = ex.Message;
}
return default(T);
}
}//end
2.数据库接口
public interface IDataBase
{
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="Link"></param>
void CloseDataBase(DbConnection Link);
/// <summary>
/// 获取命令
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
DbCommand GetSqlStringCommand(string query);
/// <summary>
/// 增加输入参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="ParmsName"></param>
/// <param name="ParmsType"></param>
/// <param name="value"></param>
void AddInParameter(DbCommand cmd, string ParmsName, DbType ParmsType, object value);
/// <summary>
/// 增加输出参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="ParmsName"></param>
/// <param name="ParmsType"></param>
/// <param name="value"></param>
void AddOutParameter(DbCommand cmd, string ParmsName, DbType ParmsType, object value); /// <summary>
/// 执行获取DataSet
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
DataSet ExecuteDataSet(DbCommand cmd);
/// <summary>
/// 执行sql语句返回受影响的行数
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
int ExecuteNonQuery(DbCommand cmd);
/// <summary>
/// 获取存储过程执行命令
/// </summary>
/// <param name="ProcName"></param>
/// <returns></returns>
DbCommand GetStoredProcCommand(string ProcName);
/// <summary>
/// 获取输出参数值或者输出参数的字典
/// </summary>
/// <param name="cmd"></param>
/// <param name="ParmsName"></param>
/// <returns></returns>
object GetParameterValueOrKeyValue(DbCommand cmd, string ParmsName = "");
}//end
3.mysql 实现接口
/// <summary>
/// mysql 实现接口
/// </summary>
public class MySQLHelper : IDataBase
{
internal MySqlConnection Link;
public MySQLHelper()
{ }
public MySQLHelper(string ConnectionStrings)
{
this.Link = null;
CreateDataBaseByConnectionStringsName(ConnectionStrings);
}
/// <summary>
/// 创建数据库连接，传入配置文件字段名
/// </summary>
/// <param name="ConnectionStringsName">数据库配置字段</param>
/// <returns></returns>
private void CreateDataBaseByConnectionStringsName(string ConnectionStringsName)
{
MySqlConnection link = new MySqlConnection();
string ConnectionStrings = ConfigurationManager.ConnectionStrings[ConnectionStringsName].ToString();
link.ConnectionString = ConnectionStrings;
try
{
link.Open();
this.Link = link;
}
catch (Exception e) { throw new Exception(e.Message); }
}
public void CloseDataBase(System.Data.Common.DbConnection Link)
{
MySqlConnection Link_ = (MySqlConnection)Link;
if (Link_ != null && Link_.State == ConnectionState.Open)
{
Link_.Close();
}
} public System.Data.Common.DbCommand GetSqlStringCommand(string query)
{
MySqlCommand cmd = null;
if (Link != null && query != "")
cmd = new MySqlCommand(query, Link);
return cmd;
} public void AddInParameter(System.Data.Common.DbCommand cmd, string ParmsName, System.Data.DbType ParmsType_, object value)
{
try
{
MySqlDbType ParmsType = BuildDbType(ParmsType_.ToString());
MySqlParameter parameter = new MySqlParameter(ParmsName, ParmsType);
parameter.Direction = ParameterDirection.Input;
if (value == null)
parameter.Value = DBNull.Value;
else
parameter.Value = value;
cmd.Parameters.Add(parameter);
}
catch (Exception e) { throw new Exception(e.Message); }
} public System.Data.DataSet ExecuteDataSet(System.Data.Common.DbCommand cmd_)
{
DataSet ds = null;
MySqlCommand cmd = cmd_ as MySqlCommand;
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
}
catch (Exception e) { throw new Exception(e.Message); }
return ds;
} public int ExecuteNonQuery(System.Data.Common.DbCommand cmd)
{
int returnCount = ;
try
{
returnCount = cmd.ExecuteNonQuery();
}
catch (Exception e) { throw new Exception(e.Message); }
return returnCount;
}
/// <summary>
/// 创建 DbType 类型
/// </summary>
/// <param name="t">System数据类型</param>
/// <returns></returns>
private MySqlDbType BuildDbType(string t)
{
switch (t)
{
case "Byte":
return MySqlDbType.Byte;
case "Byte[]":
return MySqlDbType.Binary;
case "Int32":
return MySqlDbType.Int32;
case "Int64":
return MySqlDbType.Int64;
case "UInt16":
return MySqlDbType.UInt16;
case "UInt32":
return MySqlDbType.UInt32;
case "UInt64":
return MySqlDbType.UInt64;
case "Decimal":
return MySqlDbType.Decimal;
case "Double":
return MySqlDbType.Double;
//case "Guid":
// return MySqlDbType.Guid;
//case "Xml":
// return MySqlDbType.Xml;
case "Object":
return MySqlDbType.Binary;
case "Boolean":
return MySqlDbType.Bit;
case "String":
return MySqlDbType.String;
case "DateTime":
return MySqlDbType.DateTime;
default:
return MySqlDbType.String;
}
}
}//end
4.sqlserver 实现接口
public class sqlserver : IDataBase
{
internal SqlConnection Link;
public sqlserver()
{ }
public sqlserver(string ConnectionStrings)
{
this.Link = null;
CreateDataBaseByConnectionStringsName(ConnectionStrings);
}
/// <summary>
/// 创建数据库连接，传入配置文件字段名
/// </summary>
/// <param name="ConnectionStringsName">数据库配置字段</param>
/// <returns></returns>
private void CreateDataBaseByConnectionStringsName(string ConnectionStringsName)
{
SqlConnection link = new SqlConnection();
string ConnectionStrings = ConfigurationManager.ConnectionStrings[ConnectionStringsName].ToString();
link.ConnectionString = ConnectionStrings;
try
{
link.Open();
this.Link = link;
}
catch (Exception e) { throw new Exception(e.Message); }
}
public void CloseDataBase(System.Data.Common.DbConnection Link)
{
SqlConnection Link_ = (SqlConnection)Link;
if (Link_ != null && Link_.State == ConnectionState.Open)
{
Link_.Close();
}
} public System.Data.Common.DbCommand GetSqlStringCommand(string query)
{
SqlCommand cmd = null;
if (Link != null && query != "")
cmd = new SqlCommand(query, Link);
return cmd;
} public void AddInParameter(System.Data.Common.DbCommand cmd, string ParmsName, System.Data.DbType ParmsType, object value)
{
try
{
SqlParameter parameter = new SqlParameter(ParmsName, ParmsType);
parameter.Direction = ParameterDirection.Input;
if (value == null)
parameter.Value = DBNull.Value;
else
parameter.Value = value;
cmd.Parameters.Add(parameter);
}
catch (Exception e) { throw new Exception(e.Message); }
} public System.Data.DataSet ExecuteDataSet(System.Data.Common.DbCommand cmd_)
{
DataSet ds = null;
SqlCommand cmd = cmd_ as SqlCommand;
try
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
}
catch (Exception e) { throw new Exception(e.Message); }
return ds;
} public int ExecuteNonQuery(System.Data.Common.DbCommand cmd)
{
int returnCount = ;
try
{
returnCount = cmd.ExecuteNonQuery();
}
catch (Exception e) { throw new Exception(e.Message); }
return returnCount;
}
/// <summary>
/// 创建 DbType 类型
/// </summary>
/// <param name="t">System数据类型</param>
/// <returns></returns>
private DbType BuildDbType(Type t)
{
switch (t.Name)
{
case "Byte":
return DbType.Byte;
case "Byte[]":
return DbType.Binary;
case "Int32":
return DbType.Int32;
case "Int64":
return DbType.Int64;
case "UInt16":
return DbType.UInt16;
case "UInt32":
return DbType.UInt32;
case "UInt64":
return DbType.UInt64;
case "Decimal":
return DbType.Decimal;
case "Double":
return DbType.Double;
case "Guid":
return DbType.Guid;
case "Xml":
return DbType.Xml;
case "Object":
return DbType.Binary;
case "Boolean":
return DbType.Boolean;
case "String":
return DbType.String;
case "DateTime":
return DbType.DateTime;
default:
return DbType.String;
}
} public void AddOutParameter(System.Data.Common.DbCommand cmd, string ParmsName, DbType ParmsType, object value)
{
try
{
SqlParameter parameter = new SqlParameter(ParmsName, ParmsType);
parameter.Direction = ParameterDirection.Output;
if (value == null)
parameter.Value = DBNull.Value;
else
parameter.Value = value;
cmd.Parameters.Add(parameter);
}
catch (Exception e) { throw new Exception(e.Message); }
} public System.Data.Common.DbCommand GetStoredProcCommand(string ProcName)
{
SqlCommand cmd = null;
if (Link != null && ProcName != "")
{
cmd = new SqlCommand(ProcName, Link);
cmd.CommandType = CommandType.StoredProcedure;
}
return cmd;
} public object GetParameterValueOrKeyValue(System.Data.Common.DbCommand cmd, string ParmsName = "")
{
Dictionary<string, object> dic = null;
try
{
dic = new Dictionary<string, object>();
foreach (SqlParameter parameter in cmd.Parameters)
{
if (parameter.Direction == ParameterDirection.Output)
{
dic.Add(parameter.ParameterName, parameter.Value);
}
}
if (dic != null && dic.Count > )
{
if (ParmsName != "" && dic.ContainsKey(ParmsName))
return dic[ParmsName];
}
else
dic = null;
}
catch (Exception e) { throw new Exception(e.Message); }
return dic;
}
}//end
5.生成连接对象
public class DataBase
{
public static DBFactory fac = new DBFactory();
public static IDataBase MySql_idb = null;
static DataBase()
{
MySql_idb = fac.Creatobject<IDataBase>("数据集", "数据集.MySQLHelper", new object[] { "配置名称" });
}
}//end
6.配置文件
<connectionStrings> <add name="MysqlContext" connectionString="Data Source=localhost;port=3306;Initial Catalog=数据库名称;user id=root;password=123456;charset=gb2312;" providerName="MySql.Data.MySqlClient" /> <add name="SQLserverContext" providerName="System.Data.SqlClient" connectionString="Server=.;Database=数据库名称;User ID=sa;Password=123456;Trusted_Connection=false" /> </connectionStrings>
7.实现例子
public bool Add(string telString)
{
string sqlStr = @"insert into Table
(telString)
values
(@telString)"; DbCommand cmd = MySql_idb.GetSqlStringCommand(sqlStr);
MySql_idb.AddInParameter(cmd, "@telString", System.Data.DbType.String, telString);return MySql_idb.ExecuteNonQuery(cmd) > ;
}
public static int execPro(int id, string name)
{
int result = ;
DbCommand cmd = MySql_idb.GetStoredProcCommand("Proc_ceshi");
MySql_idb.AddInParameter(cmd, "@id", System.Data.DbType.Int32, id);
MySql_idb.AddInParameter(cmd, "@name", System.Data.DbType.String, name);
MySql_idb.AddOutParameter(cmd, "@returnval", System.Data.DbType.Int32, result);
MySql_idb.ExecuteNonQuery(cmd);
result = Convert.ToInt32(MySql_idb.GetParameterValueOrKeyValue(cmd, "@returnval").ToString());
return result;
}