C#DAL数据访问层
BaseDAL：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public class BaseDAL
{
public static int Insert(ModelBase obj)
{
Type type = obj.GetType(); //获取类型
object[] attr = type.GetCustomAttributes(typeof(MyKeyAttribute), false); //获取自定义特性
if (attr.Length == 0)
throw new Exception("必须定义主键列");
string Key = (attr[0] as MyKeyAttribute).Key; //获取主键列
bool IsIdentity = (attr[0] as MyKeyAttribute).IsIdentity; //是否自增长
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
List<SqlParameter> ps = new List<SqlParameter>();
PropertyInfo[] infos = type.GetProperties(); //获取所有属性，即字段名，组装成sql语句
foreach (PropertyInfo p in infos)
{
if (p.GetValue(obj, null) != null)
{
if (p.Name.ToLower() == Key.ToLower() && IsIdentity) //如果是主键列且自增长就跳过
continue;
sb1.Append(p.Name + ",");
sb2.Append("@" + p.Name + ",");
ps.Add(new SqlParameter("@" + p.Name, p.GetValue(obj, null)));
}
}
string sql = string.Format("insert into {0}({1}) values({2})", type.Name, sb1.ToString().Trim(','), sb2.ToString().Trim(','));
return DBHelper.CUD(sql, ps);
}
public static int InsertID(ModelBase obj)
{
Type type = obj.GetType(); //获取类型
object[] attr = type.GetCustomAttributes(typeof(MyKeyAttribute), false); //获取自定义特性
if (attr.Length == 0)
throw new Exception("必须定义主键列");
string Key = (attr[0] as MyKeyAttribute).Key; //获取主键列
bool IsIdentity = (attr[0] as MyKeyAttribute).IsIdentity; //是否自增长
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
List<SqlParameter> ps = new List<SqlParameter>();
PropertyInfo[] infos = type.GetProperties(); //获取所有属性，即字段名，组装成sql语句
foreach (PropertyInfo p in infos)
{
if (p.GetValue(obj, null) != null)
{
if (p.Name.ToLower() == Key.ToLower() && IsIdentity) //如果是主键列且自增长就跳过
continue;
sb1.Append(p.Name + ",");
sb2.Append("@" + p.Name + ",");
ps.Add(new SqlParameter("@" + p.Name, p.GetValue(obj, null)));
}
}
string sql = string.Format("insert into {0}({1}) values({2});select @@identity;", type.Name, sb1.ToString().Trim(','), sb2.ToString().Trim(','));
return Convert.ToInt32(DBHelper.SelectObject(sql, ps));
}
public static int Update(ModelBase obj)
{
Type type = obj.GetType(); //获取类型
object[] attr = type.GetCustomAttributes(typeof(MyKeyAttribute), false); //通过自定义特性，获取主键列
if (attr.Length == 0)
throw new Exception("必须设定主键列！");
string Key = (attr[0] as MyKeyAttribute).Key;
object KeyValue = type.GetProperty(Key).GetValue(obj, null); //获取主键列值
if (KeyValue == null)
throw new Exception("必须给主键列赋值！");
StringBuilder sb1 = new StringBuilder();
List<SqlParameter> ps = new List<SqlParameter>();
PropertyInfo[] infos = type.GetProperties(); //获取所有列值，组装成sql语句
foreach (PropertyInfo p in infos)
{
if (p.GetValue(obj, null) != null)
{
if (p.Name.ToLower() != Key.ToLower())
{
sb1.Append(p.Name + "=@" + p.Name + ",");
ps.Add(new SqlParameter("@" + p.Name, p.GetValue(obj, null)));
}
}
}
string wstr = Key + "=@" + Key; //获取主键列，组装成sql语句
ps.Add(new SqlParameter("@" + Key, KeyValue));
string sql = string.Format("update {0} set {1} where {2}", type.Name, sb1.ToString().Trim(','), wstr);
return DBHelper.CUD(sql, ps);
}
public static int Delete<T>(object objID)
{
Type type = typeof(T);
object[] attr = type.GetCustomAttributes(typeof(MyKeyAttribute), false);
if (attr.Length == 0)
throw new Exception("必须指定主键列！");
string Key = (attr[0] as MyKeyAttribute).Key;
string sql = string.Format("delete from {0} where {1}=@{1}", type.Name, Key);
List<SqlParameter> ps = new List<SqlParameter>();
ps.Add(new SqlParameter("@" + Key, objID));
return DBHelper.CUD(sql, ps);
}
public static T SelectObj<T>(object objID)
{
Type type = typeof(T);
object[] attr = type.GetCustomAttributes(typeof(MyKeyAttribute), false);
if (attr.Length == 0)
throw new Exception("必须设定主键列！");
string Key = (attr[0] as MyKeyAttribute).Key;
string sql = string.Format("select * from {0} where {1}=@{1}", type.Name, Key);
List<SqlParameter> ps = new List<SqlParameter>();
ps.Add(new SqlParameter("@" + Key, objID));
T obj = Activator.CreateInstance<T>();
using (DataTable dt = DBHelper.SelectTable(sql, ps))
{
for (int r = 0; r < dt.Rows.Count; r++)
{
PropertyInfo[] infos = type.GetProperties();
foreach (PropertyInfo p in infos)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (p.Name.ToLower() == dt.Columns[i].ColumnName.ToLower())
{
if (dt.Rows[r][p.Name] != DBNull.Value)
p.SetValue(obj, dt.Rows[r][p.Name], null);
}
}
}
}
}
return obj;
}
public static List<T> SelectList<T>(string sql = null, List<SqlParameter> ps = null)
{
List<T> list = new List<T>();
Type type = typeof(T);
using (DataTable dt = (sql == null ? DBHelper.SelectTable("select * from " + type.Name) : DBHelper.SelectTable(sql, ps)))
{
for (int r = 0; r < dt.Rows.Count; r++)
{
T obj = Activator.CreateInstance<T>();
PropertyInfo[] infos = type.GetProperties();
foreach (PropertyInfo p in infos)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (p.Name.ToLower() == dt.Columns[i].ColumnName.ToLower())
{
if (dt.Rows[r][p.Name] != DBNull.Value)
p.SetValue(obj, dt.Rows[r][p.Name], null);
}
}
}
list.Add(obj);
}
}
return list;
}
}
EntityDAL:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
public class EntityDAL : BaseDAL
{
//对表或视图实体的数据操作
}
ModelBase:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
public class ModelBase
{
}
Entity:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
[MyKey("ID",true)]
public class Entity : ModelBase
{
public System.Int32 ID { get; set; }
public System.String ClassName { get; set; }
public Entity()
{
ClassName = string.Empty;
}
}
MyKeyAttribute:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//自定义特性类
public class MyKeyAttribute : Attribute
{
public string Key { get; set; } //主键列
public bool IsIdentity { get; set; } //是否自增长
public MyKeyAttribute(string key, bool isIdentity)
{
this.Key = key;
this.IsIdentity = isIdentity;
}
}
DBHelper:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class DBHelper
{
public static string connstr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
/// <summary>
///无参的增改删
/// </summary>
public static int CUD(string sql)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
}
/// <summary>
///有参的增改删
/// </summary>
public static int CUD(string sql, List<SqlParameter> ps)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (ps != null)
cmd.Parameters.AddRange(ps.ToArray());
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
}
/// <summary>
///无参、断开式数据操作
/// </summary>
public static DataSet SelectSet(string sql)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
/// <summary>
///无参、断开式数据操作
/// </summary>
public static DataTable SelectTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
}
/// <summary>
///有参、断开式数据操作
/// </summary>
public static DataTable SelectTable(string sql,List<SqlParameter> ps)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
if (ps != null)
da.SelectCommand.Parameters.AddRange(ps.ToArray());
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
}
/// <summary>
///无参、连接式数据操作
/// </summary>
public static SqlDataReader SelectReader(string sql)
{
SqlConnection conn = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sr;
}
/// <summary>
///有参、连接式数据操作
/// </summary>
public static SqlDataReader SelectReader(string sql,List<SqlParameter> ps)
{
SqlConnection conn = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (ps != null)
cmd.Parameters.AddRange(ps.ToArray());
conn.Open();
SqlDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sr;
}
/// <summary>
///调用存储过程，返回DataSet
/// </summary>
public static DataSet SelectProc(string sql, List<SqlParameter> ps)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//指定命令类型为存储过程模式
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (ps != null)
da.SelectCommand.Parameters.AddRange(ps.ToArray());
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
/// <summary>
/// 调用存储过程，返回DataTable数组
/// </summary>
public static DataTable[] SelectProcTable(string sql, List<SqlParameter> ps)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//指定命令类型为存储过程模式
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (ps != null)
da.SelectCommand.Parameters.AddRange(ps.ToArray());
DataSet ds = new DataSet();
da.Fill(ds);
DataTable[] dts = new DataTable[2];
ds.Tables.CopyTo(dts, 0);
return dts;
}
}
/// <summary>
/// 取单行单列的数据对象
/// </summary>
public static object SelectObject(string sql)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
object obj = cmd.ExecuteScalar();
//conn.Close();
return obj;
}
}
/// <summary>
/// 取单行单列的数据对象
/// </summary>
public static object SelectObject(string sql, List<SqlParameter> ps)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
if (ps != null)
cmd.Parameters.AddRange(ps.ToArray());
object obj = cmd.ExecuteScalar();
//conn.Close();
return obj;
}
}
}
