using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Reflection;
namespace
{
/// <summary>
/// 泛型化数据库操作方法接口
/// </summary>
/// <typeparam name="TConnection">数据库连接对象</typeparam>
/// <typeparam name="TCommand">数据库执行对象</typeparam>
/// <typeparam name="TDataReader">数据库读取对象</typeparam>
public interface IConnector<TConnection, TCommand, TDataReader, TParameter>
{
#region 数据库连接对象
/// <summary>
/// 创建一个新的数据库连接对象
/// </summary>
TConnection Connection { get; }
#endregion
#region 创建T-SQL语句执行对象
/// <summary>
/// 创建执行 Transact-SQL 语句的对象。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
TCommand CreateCommand(string procedureName);
/// <summary>
/// 创建执行的 Transact-SQL 语句的对象。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="Params">参数列表</param>
TCommand CreateCommand(string procedureName, Dictionary<String, Object> Params);
#endregion
#region ExecuteReader 执行存储过程并返回结果集
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="command">执行 Transact-SQL 语句的对象。</param>
TDataReader ExecuteReader(TCommand command);
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="procedureName">存储过程名称</param>
TDataReader ExecuteReader(string procedureName);
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="Params">参数列表</param>
TDataReader ExecuteReader(string procedureName, Dictionary<String, Object> Params);
#endregion
#region ExecuteNonQuery 执行存储过程并返回受影响行数
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="Command">Transact-SQL 语句的对象</param>
bool ExecuteNonQuery(TCommand Command);
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="procedureName">存储过程名称</param>
bool ExecuteNonQuery(string procedureName);
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="procedureName">将要执行的存储过程名称</param>
/// <param name="Params">参数列表</param>
bool ExecuteNonQuery(string procedureName, Dictionary<String, Object> Params);
#endregion
#region ExecuteTable
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="command">T-SQL 语句执行对象</param>
/// <returns>内存中数据的一个表</returns>
DataTable ExecuteTable(TCommand command);
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>内存中数据的一个表</returns>
DataTable ExecuteTable(string procedureName);
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="paramters">需要的参数列表</param>
/// <returns>内存中数据的一个表</returns>
DataTable ExecuteTable(string procedureName, Dictionary<string, object> Params);
#endregion
#region ExecuteScalar 返回第一行第一列
/// <summary>
/// 执行存储过程并返回第一行第一列
/// </summary>
/// <param name="procedureName">存储过程名称</param>
object ExecuteScalar(string procedureName);
/// <summary>
/// 执行存储过程并返回第一行第一列
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="Params">参数值</param>
object ExecuteScalar(string procedureName, Dictionary<String, Object> Params);
#endregion
}
/// <summary>
/// 提供通用的公共方法和属性
/// </summary>
public class DBConnector<TParameter>
{
/// <summary>
/// 数据库连接字符串
/// </summary>
protected readonly static string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// 操作是否执行成功
/// </summary>
/// <param name="result">执行结果</param>
protected bool Success(int result)
{
return result > 0 ? true : false;
}
/// <summary>
/// 检测属性是否为空
/// </summary>
/// <param name="property">属性对象</param>
/// <param name="value">属性的值</param>
protected object IsNull(PropertyInfo property, object value)
{
switch (property.PropertyType.FullName)
{
case "System.DateTime":
if (value.Equals(DBNull.Value))
{
return DateTime.MinValue;
}
else
{
return value.Equals(DateTime.MinValue) ? DBNull.Value : value;
}
case "System.String": return value == null ? string.Empty : value;
default: return value;
}
}
/// <summary>
/// 构造参数列表
/// </summary>
///<param name="Params">键和值的列表</param>
protected TParameter[] CreateParameters(Dictionary<string, object> Params)
{
List<TParameter> list = new List<TParameter>();
foreach (var item in Params)
{
TParameter param = Activator.CreateInstance<TParameter>();
SetValue(param, "ParameterName", item.Key);
SetValue(param, "Value", item.Value);
list.Add(param);
}
return list.ToArray();
}
#region 对象属性映射
/// <summary>
/// 设置对象属性的值
/// </summary>
/// <param name="obj">将设置属性值的对象</param>
/// <param name="name">将设置属性的名称</param>
/// <param name="value">词属性的新值</param>
protected void SetValue(object obj, string name, object value)
{
obj.GetType().GetProperty(name).SetValue(obj, value, null);
}
/// <summary>
/// 设置对象属性的值
/// </summary>
/// <param name="obj">将设置属性值的对象</param>
/// <param name="name">将设置属性的名称</param>
/// <param name="value">词属性的新值</param>
/// <param name="index">索引</param>
protected void SetValue(object obj, string name, object value, object[] index)
{
obj.GetType().GetProperty(name).SetValue(obj, value, index);
}
#endregion
}
}
<pre lang="x-c#">using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace SWSoft.MVC.Model
{
/// <summary>
/// SqlServer数据库访问类
/// </summary>
public partial class SqlServer<T> : DBConnector<SqlParameter>, IConnector<SqlConnection, SqlCommand, SqlDataReader, SqlParameter>
{
#region 数据库连接对象
/// <summary>
/// 数据库连接字符串
/// </summary>
public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// 一个新的数据库连接对象
/// </summary>
public SqlConnection Connection { get { return new SqlConnection(ConnectionString); } }
#endregion
#region SqlCommand 创建T-SQL语句执行对象
/// <summary>
/// 创建执行 Transact-SQL 语句的对象。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
public SqlCommand CreateCommand(string procedureName)
{
SqlCommand comm = new SqlCommand { Connection = Connection, CommandType = CommandType.StoredProcedure, CommandText = procedureName };
comm.Connection.Open();
return comm;
}
/// <summary>
/// 创建执行的 Transact-SQL 语句的对象。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="Params">参数列表</param>
public SqlCommand CreateCommand(string procedureName, Dictionary<String, Object> Params)
{
SqlCommand comm = CreateCommand(procedureName);
comm.Parameters.AddRange(CreateParameters(Params));
return comm;
}
#endregion
#region ExecuteReader 执行存储过程并返回结果集
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="command">执行 Transact-SQL 语句的对象。</param>
public SqlDataReader ExecuteReader(SqlCommand command)
{
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="procedureName">存储过程名称</param>
public SqlDataReader ExecuteReader(string procedureName)
{
return ExecuteReader(CreateCommand(procedureName));
}
/// <summary>
/// 执行存储过程并返回结果集
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="Params">参数列表</param>
public SqlDataReader ExecuteReader(string procedureName, Dictionary<String, Object> Params)
{
return ExecuteReader(CreateCommand(procedureName, Params));
}
#endregion
#region ExecuteTable
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="command">T-SQL 语句执行对象</param>
/// <returns>内存中数据的一个表</returns>
public DataTable ExecuteTable(SqlCommand command)
{
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>内存中数据的一个表</returns>
public DataTable ExecuteTable(string procedureName)
{
return ExecuteTable(CreateCommand(procedureName));
}
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="Params">需要的参数列表</param>
/// <returns>内存中数据的一个表</returns>
public DataTable ExecuteTable(string procedureName, Dictionary<string, object> Params)
{
return ExecuteTable(CreateCommand(procedureName, Params));
}
#endregion
#region ExecuteNonQuery 执行存储过程并返回受影响行数
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="Command">Transact-SQL 语句的对象</param>
public bool ExecuteNonQuery(SqlCommand Command)
{
return Success(Command.ExecuteNonQuery());
}
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="procedureName">存储过程名称</param>
public bool ExecuteNonQuery(string procedureName)
{
return ExecuteNonQuery(CreateCommand(procedureName));
}
/// <summary>
/// 执行存储过程并返回操作是否成功执行
/// </summary>
/// <param name="procedureName">将要执行的存储过程名称</param>
/// <param name="Params">参数列表</param>
public bool ExecuteNonQuery(string procedureName, Dictionary<String, Object> Params)
{
return ExecuteNonQuery(CreateCommand(procedureName, Params));
}
#endregion
#region ExecuteScalar 返回第一行第一列
/// <summary>
/// 执行存储过程并返回第一行第一列
/// </summary>
/// <param name="procedureName">存储过程名称</param>
public object ExecuteScalar(string procedureName)
{
return CreateCommand(procedureName).ExecuteScalar();
}
/// <summary>
/// 执行存储过程并返回第一行第一列
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="Params">参数值</param>
public object ExecuteScalar(string procedureName, Dictionary<String, Object> Params)
{
return CreateCommand(procedureName, Params).ExecuteScalar();
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace SWSoft.MVC.Model
{
/// <summary>
/// 数据库访问器,提供数据访问的基本方法和对象关系映射
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
public partial class SqlServer<T>
{
/// <summary>
/// 提供存储过程的参数列表
/// </summary>
protected Dictionary<String, Object> Params { get; set; }
/// <summary>
/// 默认构造方法
/// </summary>
public SqlServer()
{
Params = new Dictionary<string, object>();
}
/// <summary>
/// 获得参数的键值对集合
/// </summary>
/// <param name="model">包含参数的实体对象</param>
protected Dictionary<string, object> GetParameters(T model)
{
Dictionary<string, object> Params = new Dictionary<string, object>();
foreach (var item in model.GetType().GetProperties())
{
Params.Add("@" + item.Name, item.GetValue(item, null));
}
return Params;
}
/// <summary>
/// 封装为实体对象
/// </summary>
/// <param name="reader">数据库读取器</param>
/// <returns>实体对象的泛型集合</returns>
protected List<T> ToModels(SqlDataReader reader)
{
List<T> list = new List<T>();
while (reader.Read())
{
T t = Activator.CreateInstance<T>();
foreach (var item in t.GetType().GetProperties())
{
SetValue(t, item.Name, IsNull(item, reader[item.Name]));
}
list.Add(t);
}
return list;
}
/// <summary>
/// 封装为实体对象
/// </summary>
/// <param name="reader">数据库读取器</param>
/// <returns>单个实体对象</returns>
protected T ToModel(SqlDataReader reader)
{
List<T> list = ToModels(reader);
return list != null && list.Count > 0 ? list[0] : default(T);
}
#region Execute
/// <summary>
/// 执行一个存储过程,返回一个实体对象
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>实体对象集合</returns>
protected List<T> Execute(string procedureName)
{
return ToModels(ExecuteReader(procedureName, Params));
}
#endregion
#region ExecuteModel
/// <summary>
/// 执行一个存储过程,返回一个实体对象
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>实体对象</returns>
protected T ExecuteModel(string procedureName)
{
return ToModel(ExecuteReader(procedureName, Params));
}
#endregion
#region ExecuteModels
/// <summary>
/// 执行一个存储过程,返回一个实体对象集合
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>实体对象</returns>
protected List<T> ExecuteModels(string procedureName)
{
return ToModels(ExecuteReader(procedureName, Params));
}
#endregion
#region ExcuteInt32
/// <summary>
/// 执行一个存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>第一行第一列</returns>
protected int ExecuteInt32(string procedureName)
{
return Convert.ToInt32(ExecuteScalar(procedureName, Params));
}
#endregion
#region ExecuteBool
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>是否执行成功</returns>
protected bool ExecuteBool(string procedureName)
{
return ExecuteNonQuery(procedureName, Params);
}
#endregion
}
}
自己写的MVC框架,可能存在很多问题或者错误希望各位高手们指出!
