简单的数据库映射类

　　为了学习一下面向对像的基本知识，前些日子编写了一个简单的数据库操作类库。类库中抱括以下几个类： 

代码

public static class GETConnection //获取数据库链接串类
public class GetTable : Attribute //获取映射表名称属性类
public class GetProperty : Attribute //获取映射字段名称属性类
public class GetKeyProperty : Attribute //获取映射主键名称属性类
public class DBInstance<T> where T : new() //用于数据库操作的基类

  获取数据库链接串类 说明：此类为了更简单的获得数据库链接字符串而创建

代码

using System;
using System.Collections.Generic;
using System.Text;

namespace UserRulePopedomClassLibrary.AD
{
public static class GETConnection
{
static string stName = string.Empty;
public static string ConnectionName
{
get
{
return stName;
}
set
{
stName = value;
}
}
}
}

 以下属性类的代码说明：

public class GetTable : Attribute                 //获取映射表名称属性类
public class GetProperty : Attribute            //获取映射字段名称属性类
public class GetKeyProperty : Attribute     //获取映射主键名称属性类 

 [AttributeUsage(AttributeTargets.Class, AllowMultiple=false),Serializable]

GetTable类中的AttributeTargets.Class表示为类属性，AllowMultiple=false表示同一属性的内容（也就是说一个数据库中的表名都是唯一的）只能出现一次，Serializable对本类进行序列化。

[AttributeUsage(AttributeTargets.Property, AllowMultiple = true), Serializable]

GetProperty类中的AttributeTargets.Property表示为类属性，AllowMultiple=true表示同一属性的内容可以出现多次，Serializable对本类进行序列化。

 [AttributeUsage(AttributeTargets.Property, AllowMultiple = false), Serializable]

GetKeyProperty类中的AttributeTargets.Property表示为类属性，AllowMultiple=true表示同一属性的内容只可以出现一次，Serializable对本类进行序列化。

代码

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
namespace Service.AD
{
/// <summary>
/// 表名称属性
/// </summary>
[AttributeUsage(AttributeTargets.Class, AllowMultiple=false),Serializable]
public class GetTable : Attribute
{
static string strTable = string.Empty;
public GetTable()
{

}
public GetTable(string table)
{
TableName = table;
}
/// <summary>
/// 对应数据库的表名称
/// </summary>
public string TableName
{
get
{
return strTable;
}
set
{
strTable = value;
}
}

}

/// <summary>
/// 字段属性
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = true), Serializable]
public class GetProperty : Attribute//,ICloneable
{
string strColumn = string.Empty;
public GetProperty()
{

}
public GetProperty(string column)
{
Column = column;
}
/// <summary>
/// 对应数据库的表中字段的名称
/// </summary>
public string Column
{
get
{
return strColumn;
}
set
{
this.strColumn = value;
}
}
}

/// <summary>
/// 主键属性
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false), Serializable]
public class GetKeyProperty : Attribute//, ICloneable
{
string strColumn = string.Empty;
public GetKeyProperty()
{

}
public GetKeyProperty(string column)
{
Column = column;
}
/// <summary>
/// 对应数据库的表中主键字段的名称
/// </summary>
public string Column
{
get
{
return strColumn;
}
set
{
this.strColumn = value;
}
}
}
}

  数据库操作的基本类：

说明：此类主要功能是做为数据库中表映射类的基类。 

代码

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
namespace Service.AD
{
[Serializable]
public class DBInstance<T> where T : new()
{
private static SqlConnection connection;
private static string stConnName = string.Empty;
public static string strTableName = string.Empty;

/// <summary>
/// 建立数据库链接
/// </summary>
public static SqlConnection Connection
{

get
{
//ConnName = GETConnection.ConnectionName;
string stConnection = ConfigurationManager.ConnectionStrings[GETConnection.ConnectionName].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(stConnection);
connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
set
{
connection = value;
}
}

/// <summary>
/// 返回单条记录
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">SQL语句中的参数</param>
/// <returns>返回单条记录</returns>
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

/// <summary>
/// 输入SQL返回数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>数据集</returns>
public static DataTable GetDataTable(string sql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}

/// <summary>
/// 输入SQL返回数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">SQL语句的参数集</param>
/// <returns>数据集</returns>
public static DataTable GetDataTable(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}

/// <summary>
/// 执行存储过程返回数据集
/// </summary>
/// <param name="procedureName">储过程名称</param>
/// <param name="values">储过程参数集</param>
/// <returns>数据集</returns>
public static DataTable ExceuteProcedures(string procedureName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(procedureName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 6000;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 执行存储过程返回数据集
/// </summary>
/// <param name="procedureName"></param>
/// <param name="values"></param>
/// <returns>影响行数</returns>
public static int ExceuteProceduresReturnInt(string procedureName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(procedureName, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 6000;
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行插入删除或更新
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">SQL语句中的参数</param>
/// <returns>返回影响行数</returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 返回Count()的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>int COUNT()</returns>
public static int GetScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}

/// <summary>
/// 返回Count()的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">SQL语句中的参数</param>
/// <returns>int COUNT()</returns>
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}

/// <summary>
/// 返回Count()的值
/// </summary>
/// <param name="safeSql">SQL语句</param>
/// <returns>string COUNT()</returns>
public static string ReturnStringScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
try
{
string result = cmd.ExecuteScalar().ToString();
return result;
}
catch (Exception)
{
return "0";
}
}

/// <summary>
/// 输入主键得到对像
/// </summary>
/// <param name="ID">主键值</param>
/// <returns></returns>
public static T Find(object ID)
{
try
{
string stSql = string.Empty;
string stKey = string.Empty;
T tObj = new T();
string strTableName = ((GetTable)tObj.GetType().GetCustomAttributes(true)[0]).TableName;
PropertyInfo[] propertyInfos = tObj.GetType().GetProperties();
stSql = "select * from " + strTableName + " where ";
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (tObj.GetType().GetProperty(propertyInfo.Name).GetCustomAttributes(false)[0].GetType().Name.Equals("GetKeyProperty"))//看是不是KEY
{
stKey = "@" + GetColumnName(propertyInfo);
stSql = stSql + "[" + GetColumnName(propertyInfo) + "] = " + stKey;
break;
}
}
SqlDataReader sdr = GetReader(stSql, new SqlParameter(stKey, ID));
if (sdr.Read())
{
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
string strColumnName = GetColumnName(propertyInfo);
if (sdr[strColumnName].GetType().ToString().Equals("System.DBNull"))
{
tObj.GetType().GetProperty(propertyInfo.Name).SetValue(tObj, null, null);
}
else
{
tObj.GetType().GetProperty(propertyInfo.Name).SetValue(tObj, sdr[strColumnName], null);
}
}

}
sdr.Close();
return tObj;
}
else
{
sdr.Close();
return tObj;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}

/// <summary>
/// 输入列名和值
/// </summary>
/// <param name="ID">主键值</param>
/// <returns></returns>
public static T FindOne(string columnNames, string values)
{
try
{
string stSql = string.Empty;
string stKey = string.Empty;
string[] stsColumnName = columnNames.Split(',');
string[] stsValues = values.Split(',');
T tObj = new T();
string strTableName = ((GetTable)tObj.GetType().GetCustomAttributes(true)[0]).TableName;
PropertyInfo[] propertyInfos = tObj.GetType().GetProperties();
stSql = "select * from " + strTableName + " where ";
SqlParameter[] sqlp = new SqlParameter[stsColumnName.Length];
for (int i = 0; i < stsColumnName.Length; i++)
{
stKey = "@" + stsColumnName[i];
sqlp[i] = new SqlParameter(stKey, stsValues[i]);
stSql = stSql + "[" + stsColumnName[i] + "] = " + stKey;
}
SqlDataReader sdr = GetReader(stSql, sqlp);
if (sdr.Read())
{
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
string strColumnName = GetColumnName(propertyInfo);
if (sdr[strColumnName].GetType().ToString().Equals("System.DBNull"))
{
tObj.GetType().GetProperty(propertyInfo.Name).SetValue(tObj, null, null);
}
else
{
tObj.GetType().GetProperty(propertyInfo.Name).SetValue(tObj, sdr[strColumnName], null);
}
}

}
sdr.Close();
return tObj;
}
else
{
sdr.Close();
return tObj;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}

/// <summary>
/// 查找全部数据
/// </summary>
/// <returns>DataTable数据集</returns>
public static DataTable FindAll()
{
try
{
string stSql = string.Empty;
string stKey = string.Empty;
T tObj = new T();
string strTableName = ((GetTable)tObj.GetType().GetCustomAttributes(true)[0]).TableName;
stSql = "select * from " + strTableName;
return GetDataTable(stSql);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}

/// <summary>
/// 查找全部数据
/// </summary>
/// <param name="orderbys">排序参数 如：“Guid Desc ,name Asc”</param>
/// <returns>DataTable数据集</returns>
public static DataTable FindAll(string orderbys)
{
try
{
string stSql = string.Empty;
string stKey = string.Empty;
T tObj = new T();
string strTableName = ((GetTable)tObj.GetType().GetCustomAttributes(true)[0]).TableName;
stSql = "select * from [" + strTableName + "] " + orderbys;
return GetDataTable(stSql);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
/// <summary>
/// 创建新记录
/// </summary>
/// <returns>是否成功</returns>
public bool Create()
{
try
{
T obj = (T)this.MemberwiseClone();
string strTableName = ((GetTable)obj.GetType().GetCustomAttributes(true)[0]).TableName;
PropertyInfo[] propertyInfos = obj.GetType().GetProperties();
string stSQL = "insert into " + strTableName;
string stFiled = string.Empty;
string stValues = string.Empty;
int i = 0;
int k = 0;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
k++;
}
}
SqlParameter[] sqlParameter = new SqlParameter[k];

foreach (PropertyInfo propertyInfo in propertyInfos)
{
string strColumnName = string.Empty;
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
strColumnName = GetColumnName(propertyInfo);
stFiled += "[" + strColumnName + "],";
stValues += "@" + strColumnName + ",";
sqlParameter[i] = new SqlParameter("@" + strColumnName, obj.GetType().GetProperty(propertyInfo.Name).GetValue(obj, null));
i++;
}
}
stSQL = stSQL + " (" + stFiled.Substring(0, stFiled.Length - 1) + ") Values(" + stValues.Substring(0, stValues.Length - 1) + ")";
if (ExecuteCommand(stSQL, sqlParameter) == 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}

}

/// <summary>
/// 删除
/// </summary>
/// <returns>是否成功</returns>
public bool Delete()
{
try
{
T obj = (T)this.MemberwiseClone();
string stKey = string.Empty;
string strTableName = ((GetTable)obj.GetType().GetCustomAttributes(true)[0]).TableName;
string stSQL = "delete from " + strTableName + " where ";
PropertyInfo[] propertyInfos = obj.GetType().GetProperties();
PropertyInfo piKey = null;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (obj.GetType().GetProperty(propertyInfo.Name).GetCustomAttributes(false)[0].GetType().Name.Equals("GetKeyProperty"))//看是不是KEY
{
stKey = "@" + GetColumnName(propertyInfo);
stSQL = stSQL + "[" + GetColumnName(propertyInfo) + "] = " + stKey;
piKey = propertyInfo;
break;
}
}
string stKeyValue = piKey.GetValue(obj, null).ToString();
SqlParameter[] sqlParameter = new SqlParameter[]
{
new SqlParameter(stKey,stKeyValue)
};
if (ExecuteCommand(stSQL, sqlParameter) == 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}

/// <summary>
/// 修改
/// </summary>
/// <returns>是否成功</returns>
public bool Update()
{
try
{
T obj = (T)this.MemberwiseClone();
string strTableName = ((GetTable)obj.GetType().GetCustomAttributes(true)[0]).TableName;
PropertyInfo[] propertyInfos = obj.GetType().GetProperties();
string stSQL = "update " + strTableName + " set ";
string stFiled = string.Empty;
string stValues = string.Empty;
string stKey = string.Empty;
int i = 0;
int k = 0;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (obj.GetType().GetProperty(propertyInfo.Name).GetCustomAttributes(false)[0].GetType().Name.Equals("GetKeyProperty"))//看是不是KEY
{
stKey = GetColumnName(propertyInfo);

break;
}
}
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
k++;
}
}
SqlParameter[] sqlParameter = new SqlParameter[k];
foreach (PropertyInfo propertyInfo in propertyInfos)
{
string strColumnName = string.Empty;
if (propertyInfo.GetCustomAttributes(true).Length != 0)
{
strColumnName = GetColumnName(propertyInfo);
if (!strColumnName.Equals(stKey))
{
stFiled += "[" + strColumnName + "]= @" + strColumnName + ",";
sqlParameter[i] = new SqlParameter("@" + strColumnName, obj.GetType().GetProperty(propertyInfo.Name).GetValue(obj, null));
i++;
}
}
}
sqlParameter[k - 1] = new SqlParameter("@" + stKey, obj.GetType().GetProperty(stKey).GetValue(obj, null));
stSQL = stSQL + stFiled.Substring(0, stFiled.LastIndexOf(',')) + " where [" + stKey + "]=@" + stKey;
if (ExecuteCommand(stSQL, sqlParameter) == 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}

/// <summary>
/// 获取字段名
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
protected static string GetColumnName(PropertyInfo propertyInfo)
{
switch (propertyInfo.GetCustomAttributes(true)[0].GetType().Name)
{
case "GetProperty":
return ((GetProperty)(propertyInfo.GetCustomAttributes(true)[0])).Column;
case "GetKeyProperty":
return ((GetKeyProperty)(propertyInfo.GetCustomAttributes(true)[0])).Column;
default:
return ((GetKeyProperty)(propertyInfo.GetCustomAttributes(true)[0])).Column;
}
}
}
}

下面做一个简单的应用实例：

BO中的UserBaseInfo.cs

代码

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.ProviderBase;
using System.Data.SqlClient;
using UserRulePopedomClassLibrary.AD;

namespace UserRulePopedomClassLibrary.BO
{
/// <summary>
/// 用户类
/// </summary>
[GetTable("UserBaseInfo_sys")]
public class UserBaseInfo : DBInstance<UserBaseInfo>
{
#region 变量
string stGuid = string.Empty;
string stUserName = string.Empty;
string stLogOnName = string.Empty;
string stPassWord = string.Empty;
UserInfo ui = new UserInfo();
#endregion

#region 属性
/// <summary>
/// 用户编号（主键）
/// </summary>
[GetKeyProperty("GUID")]//GUID是表中的字段
public string Guid
{
get
{
return stGuid;
}
set
{

stGuid = value;
}
}
/// <summary>
/// 用户名
/// </summary>
[GetProperty("UserName")]//UserName是表中的字段
public string UserName
{
get
{
return stUserName;
}
set
{
stUserName = value;
}
}
/// <summary>
/// 登录名
/// </summary>
[GetProperty("LogOnName")]//LogOnName是表中的字段
public string LogOnName
{
get
{
return stLogOnName;
}
set
{
stLogOnName = value;
}
}
/// <summary>
/// 密码
/// </summary>
[GetProperty("PassWord")]//PassWord是表中的字段
public string PassWord
{
get
{
return stPassWord;
}
set
{
stPassWord = value;
}
}
#endregion
}
}

Web中的Global.asax

代码

<%@ Application Language="C#" %>

<script runat="server">

void Application_Start(object sender, EventArgs e)
{
// 在应用程序启动时运行的代码
UserRulePopedomClassLibrary.AD.GETConnection.ConnectionName = "NortheastLivelihoodCns";
}

void Application_End(object sender, EventArgs e)
{
// 在应用程序关闭时运行的代码

}

void Application_Error(object sender, EventArgs e)
{
// 在出现未处理的错误时运行的代码

}

void Session_Start(object sender, EventArgs e)
{
// 在新会话启动时运行的代码

}

void Session_End(object sender, EventArgs e)
{
// 在会话结束时运行的代码。
// 注意: 只有在 Web.config 文件中的 sessionstate 模式设置为
// InProc 时，才会引发 Session_End 事件。如果会话模式设置为 StateServer
// 或 SQLServer，则不会引发该事件。

}
</script>

Default.aspx.cs中对UserBaseInfo类的基本操作。

代码

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using UserRulePopedomClassLibrary.BO;
using UserRulePopedomClassLibrary.AD;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
#region 新建一位用户
UserBaseInfo ubiUser = new UserBaseInfo();
ubiUser.Guid = Guid.NewGuid().ToString();
ubiUser.LogOnName = "Admin";
ubiUser.PassWord = "***";
ubiUser.UserName = "简凡";
ubiUser.Create();
#endregion
#region 以主键查找用户
UserBaseInfo ubi = UserBaseInfo.Find("2");
#endregion
#region 删除用户
ubi.Delete();
#endregion
#region 查找所有用户
DataTable dtAllUser= UserBaseInfo.FindAll();
DataTable dt = UserBaseInfo.GetDataTable("select * from UserBaseInfo");
#endregion
#region 按条件查找用户集合
SqlParameter[] sqlp = new SqlParameter[1];
sqlp[0] = new SqlParameter("@Guid", "2");
DataTable dt2 = UserBaseInfo.GetDataTable("select * from UserBaseInfo where Guid=@Guid", sqlp);
#endregion
}
}

以上是本人原创的代码，希望能够和大家分享，有错误之处希望指正。谢谢：）

 

转载于:https://www.cnblogs.com/goodlife775/archive/2010/05/29/1747180.html