首页 > 代码库 > DAL数据访问层
DAL数据访问层
BaseDAL：（待改进点：sql语句中的每个表名和字段名得用中括号[]包围，以防表名或字段名与数据库特殊用字重名而导致在执行查询时引发异常）
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 = http://www.mamicode.com/type.GetProperty(Key).GetValue(obj, null); //获取主键列值"必须给主键列赋值！");
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;
}
}
}
DAL数据访问层
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。