asp.net 數據庫訪問層

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace DataHelp{ /// <summary> /// 提供对SQL Server进行数据操作, /// 连接字符串默认读取配置文件connectionString节点中名称为conString的内容 /// </summary> public static class SqlDataAccess { private static string conString; /// <summary> /// 获取或设置数据库连接字符串 /// </summary> public static string ConString { get { return conString == null ? System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString : conString; } set { conString = value; } } #region 接受字符串的SQL命令 /// <summary> /// 向数据库提交增删改命令 /// </summary> /// <param name="sql">SQL命令文本</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql) { using (SqlConnection con = new SqlConnection(ConString)) { SqlCommand com = new SqlCommand(sql, con); con.Open(); return com.ExecuteNonQuery(); } } /// <summary> /// 向数据库执行查询命令 /// </summary> /// <param name="sql">SQL命令文本</param> /// <returns>第一行第一列的值</returns> public static object ExecuteScalar(string sql) { using (SqlConnection con1 = new SqlConnection(ConString)) { SqlCommand com1 = new SqlCommand(sql, con1); con1.Open(); return com1.ExecuteScalar(); } } /// <summary> /// 向数据库执行查询命令 /// </summary> /// <param name="sql">SQL命令文本</param> /// <returns>数据读取器，使用完数据读取器后，需要手动关闭该对象</returns> public static SqlDataReader ExecuteReader(string sql) { SqlDataReader read = null; SqlConnection con = new SqlConnection(ConString); try { SqlCommand com = new SqlCommand(sql, con); con.Open(); read = com.ExecuteReader(CommandBehavior.CloseConnection); return read; } catch (Exception ex) { if (con.State == ConnectionState.Open) { con.Close(); } throw ex; } } /// <summary> /// 执行数据填充 /// </summary> /// <param name="sql">sql命令文本</param> /// <returns>填充后的DataTable</returns> public static DataTable FillTable(string sql) { DataTable table = new DataTable(); SqlConnection con = new SqlConnection(ConString); SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(table); return table; } #endregion #region 接受带参数的SQL命令 private static SqlCommand GetCommand(SQLParameterManager manager) { SqlCommand com = new SqlCommand(); com.CommandText = manager.SqlText; com.CommandType = manager.CommandType; foreach (SqlParameter p in manager) { com.Parameters.Add(p); } return com; } /// <summary> /// 向数据库提交增删改命令 /// </summary> /// <param name="manager">参数管理类</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SQLParameterManager manager) { using (SqlConnection con = new SqlConnection(ConString)) { SqlCommand com = GetCommand(manager); com.Connection = con; con.Open(); return com.ExecuteNonQuery(); } } /// <summary> /// 向数据库执行查询命令 /// </summary> /// <param name="manager">参数管理类</param> /// <returns>第一行第一列的值</returns> public static object ExecuteScalar(SQLParameterManager manager) { using (SqlConnection con = new SqlConnection(ConString)) { SqlCommand com = GetCommand(manager); com.Connection = con; con.Open(); return com.ExecuteScalar(); } } /// <summary> /// 向数据库执行查询命令 /// </summary> /// <param name="manager">参数管理类</param> /// <returns>数据读取器，使用完数据读取器后，需要手动关闭该对象</returns> public static SqlDataReader ExecuteReader(SQLParameterManager manager) { SqlConnection con = new SqlConnection(ConString); try { SqlCommand com = GetCommand(manager); com.Connection = con; con.Open(); return com.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { if (con.State == ConnectionState.Open) { con.Close(); } throw ex; } } /// <summary> /// 执行数据填充 /// </summary> /// <param name="manager">参数管理类</param> /// <returns>填充后的DataTable</returns> public static DataTable FillTable(SQLParameterManager manager) { DataTable table = new DataTable(); SqlConnection con = new SqlConnection(ConString); SqlCommand com = GetCommand(manager); com.Connection = con; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = com; da.Fill(table); return table; } #endregion }}

 

using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;namespace DataHelp{ /// <summary> /// 参数管理类，为SQLDataAccess执行参数化命令提供参数 /// </summary> public class SQLParameterManager : List<SqlParameter> { /// <summary> /// 为SQL命令或存储过程添加参数 /// </summary> /// <param name="name">参数名称</param> /// <param name="value">参数的值</param> public SqlParameter Add(string name, object value) { SqlParameter p = new SqlParameter(name, value); this.Add(p); return p; } /// <summary> /// 为SQL命令或存储过程添加参数 /// </summary> /// <param name="name">参数名称</param> /// <param name="value">参数的值</param> /// <param name="type">参数的数据类型</param> public SqlParameter Add(string name, object value, SqlDbType type) { SqlParameter p = new SqlParameter(name, type); p.SqlValue = value; this.Add(p); return p; } /// <summary> /// 为SQL命令或存储过程添加参数 /// </summary> /// <param name="name">参数名称</param> /// <param name="value">参数的值</param> /// <param name="type">参数的数据类型</param> /// <param name="size">参数值的大小</param> public SqlParameter Add(string name, object value, SqlDbType type, int size) { SqlParameter p = new SqlParameter(name, type, size); p.SqlValue = value; this.Add(p); return p; } /// <summary> /// 为存储过程添加输出参数 /// </summary> /// <param name="name">参数名称</param> /// <param name="type">参数的数据类型</param> public SqlParameter AddOutParament(string name, SqlDbType type) { SqlParameter p = new SqlParameter(name, type); p.Direction = ParameterDirection.Output; this.Add(p); return p; } /// <summary> /// 为存储过程添加输出参数 /// </summary> /// <param name="name">参数名称</param> /// <param name="type">参数的数据类型</param> /// <param name="size">参数值的大小</param> public SqlParameter AddOutParament(string name, SqlDbType type, int size) { SqlParameter p = new SqlParameter(name, type); p.Size = size; p.Direction = ParameterDirection.Output; this.Add(p); return p; } /// <summary> /// 根据参数名称获取或设置参数 /// </summary> /// <param name="name">参数名称</param> /// <returns>参数对象</returns> public SqlParameter this[string name] { get { foreach (SqlParameter p in this) { if (p.ParameterName == name) { return p; } } throw new Exception("无法找到指定的参数"); } set { for (int i = 0; i < this.Count; i++) { if (this[i].ParameterName == name) { this[i] = value; } } throw new Exception("无法找到指定的参数"); } } private string sql = null; /// <summary> /// 保护参数的SQL命令文本 /// </summary> public string SqlText { get { return sql; } set { sql = value; } } private CommandType comType = CommandType.Text; /// <summary> /// 执行的命令的类型（SQL命令或存储过程） /// </summary> public CommandType CommandType { get { return comType; } set { comType = value; } } /// <summary> /// 创建SQLParameterManager对象 /// </summary> public SQLParameterManager() { } /// <summary> /// 创建SQLParameterManager对象 /// </summary> /// <param name="sql">要执行的SQL命令文本</param> public SQLParameterManager(string sql) { SqlText = sql; } /// <summary> /// 创建SQLParameterManager对象 /// </summary> /// <param name="sql">要执行的SQL命令文本或存储过程名称</param> /// <param name="type">执行的命令的类型</param> public SQLParameterManager(string sql, CommandType type) { SqlText = sql; CommandType = type; } }}

 

using System;using System.Collections.Generic;using System.Text;namespace DataHelp{ public abstract class ModelBase { public abstract string GetPrimaryKey(); private List<float> list2 = new List<float>(); private List<string> list = new List<string>(); public void SetValue(string name) { if (!list.Contains(name)) { list.Add(name); } } public int Count() { return list.Count; } public string GetValue(int index) { return list[index]; } public bool IsValue(string name) { return list.Contains(name); } }}

 

using System;using System.Collections.Generic;using System.Text;using System.Reflection;using System.Data.SqlClient;namespace DataHelp{ public class DALBase { public static int Insert(ModelBase info) { SQLParameterManager manager = InsertBase(info); return SqlDataAccess.ExecuteNonQuery(manager); } public static int InserAndIdentity(ModelBase model) { SQLParameterManager manager = InsertBase(model); manager.SqlText += ";select @@identity"; return int.Parse(SqlDataAccess.ExecuteScalar(manager).ToString()); } private static SQLParameterManager InsertBase(ModelBase info) { if (info.Count() == 0) { throw new Exception("无法执行没有列的Insert命令"); } Type type = info.GetType(); //获得实体类的类名（表名）.. string tableName = type.Name; SQLParameterManager manager = new SQLParameterManager(); StringBuilder txt1 = new StringBuilder(); StringBuilder txt2 = new StringBuilder(); for (int i = 0; i < info.Count(); i++) { string name = info.GetValue(i); txt1.Append(name + ","); txt2.Append("@" + name + ","); PropertyInfo pinfo = type.GetProperty(name); manager.Add("@" + name, pinfo.GetValue(info, null)); } txt1.Remove(txt1.Length - 1, 1); txt2.Remove(txt2.Length - 1, 1); string sql = string.Format("insert into {0}({1}) values({2})", tableName, txt1.ToString(), txt2.ToString()); manager.SqlText = sql; return manager; } public static int Update(ModelBase model) { string key = model.GetPrimaryKey(); if (key == null || key.Length == 0) { throw new Exception("无法执行没有主键的Update命令"); } if (!model.IsValue(key)) { throw new Exception("无法执行没有主键的Update命令"); } if (model.Count() < 2) { throw new Exception("无法执行Update命令"); } Type type = model.GetType(); string tableName = type.Name; SQLParameterManager manager = new SQLParameterManager(); StringBuilder txt = new StringBuilder(); for (int i = 0; i < model.Count(); i++) { string name = model.GetValue(i); if (name != key) { txt.Append(name + "=@" + name + ","); PropertyInfo pinfo = type.GetProperty(name); manager.Add("@" + name, pinfo.GetValue(model, null)); } } txt.Remove(txt.Length - 1, 1); string sql = string.Format("update {0} set {1} where {2}=@{2}", tableName, txt.ToString(), key); PropertyInfo keyinfo = type.GetProperty(key); manager.Add("@" + key, keyinfo.GetValue(model, null)); manager.SqlText = sql; return SqlDataAccess.ExecuteNonQuery(manager); } public static int Delete(ModelBase info) { string key = info.GetPrimaryKey(); if (key == null || key.Length == 0) { throw new Exception("无法执行没有主键的Delete命令"); } if (!info.IsValue(key)) { throw new Exception("无法执行没有主键的Delete命令"); } Type type = info.GetType(); string sql = string.Format("delete {0} where {1}=@{1}", type.Name, key); SQLParameterManager manager = new SQLParameterManager(sql); PropertyInfo pinfo = type.GetProperty(key); manager.Add("@" + key, pinfo.GetValue(info, null)); return SqlDataAccess.ExecuteNonQuery(manager); } public static void SelectForID(ModelBase info) { string key = info.GetPrimaryKey(); if (string.IsNullOrEmpty(key)) { throw new Exception("不存在主键，无法查询"); } if (!info.IsValue(key)) { throw new Exception("主键没有赋值，无法查询"); } Type type = info.GetType(); string tableName = type.Name; string sql = string.Format("select * from {0} where {1}=@{1}", tableName, key); SQLParameterManager manager = new SQLParameterManager(sql); PropertyInfo pinfo = type.GetProperty(key); manager.Add("@" + key, pinfo.GetValue(info, null)); Select(info, manager); } public static void Select(ModelBase info, SQLParameterManager manager) { Type type = info.GetType(); using (SqlDataReader read = SqlDataAccess.ExecuteReader(manager)) { if (read.Read()) { for (int i = 0; i < read.FieldCount; i++) { object value = read[i]; if (value == DBNull.Value) { continue; } string cname = read.GetName(i); foreach (PropertyInfo pinfo in type.GetProperties()) { if (pinfo.Name.ToLower() == cname.ToLower()) { pinfo.SetValue(info, value,null); } } } } } } public static List<T> SelectForAll<T>() where T : ModelBase { string tableName = typeof(T).Name; string sql = "select * from " + tableName; SQLParameterManager manager = new SQLParameterManager(sql); return Selects<T>(manager); } public static List<T> Selects<T>(SQLParameterManager manager) where T : ModelBase { Type type = typeof(T); List<T> list = new List<T>(); using (SqlDataReader read = SqlDataAccess.ExecuteReader(manager)) { while (read.Read()) { T t = (T)Activator.CreateInstance(type); for (int i = 0; i < read.FieldCount; i++) { object value = read[i]; if (value == DBNull.Value) { continue; } string cname = read.GetName(i); foreach (PropertyInfo pinfo in type.GetProperties()) { if (pinfo.Name.ToLower() == cname.ToLower()) { pinfo.SetValue(t, value, null); } } } list.Add(t); } } return list; } }}