首页 > 代码库 > 泛型连接数据库
泛型连接数据库
这些天用泛型做了一个数据库的底层,自我感觉很方便,但是功能不是很完善,会继续考虑下去下面给大家分享一下代码
1数据库连接的帮助类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace DAL { public class DBHelper { /// <summary> /// 从web.config中获得数据库连接字符串 /// </summary> public static string Connstr = "Data Source=.;Initial Catalog=Pet;Integrated Security=True"; #region sql语句部分 /// <summary> /// 执行sql语句返回DataTable /// </summary> /// <param name="sql">sql</param> /// <returns>根据sql语句得到所有记录</returns> public static DataTable GetDataTable(string sql) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlDataAdapter sda = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } } /// <summary> /// 执行带参数的sql语句返回DataTable /// </summary> /// <param name="sql">带参数的sql语句</param> /// <param name="values">参数</param> /// <returns>根据sql语句得到所有记录</returns> public static DataTable GetDataTable(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); cmd.Parameters.AddRange(values); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); conn.Close(); return dt; } } /// <summary> /// 执行无参数的sql语句 /// </summary> /// <param name="sql">sql语句</param> /// <returns>影响的行数</returns> public static int ExecuteCommand(string sql) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); int num = cmd.ExecuteNonQuery(); conn.Close(); return num; } } /// <summary> /// 执行有参数的sql语句 /// </summary> /// <param name="sql">sql语句</param> /// <param name="values">参数</param> /// <returns>影响的行数</returns> public static int ExecuteCommand(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(values); conn.Open(); int num = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); return num; } } /// <summary> /// 执行无参数的sql语句,返回首行首列 /// </summary> /// <param name="sql">sql语句</param> /// <returns>返回首行首列</returns> public static object GetScalar(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> /// 执行有参数的sql语句,返回首行首列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="values">参数,是否返回首行首列</param> /// <returns>返回首行首列</returns> public static object ExecuteScalar(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddRange(values); conn.Open(); object obj = cmd.ExecuteScalar(); conn.Close(); return obj; } } /// <summary> /// 带事务的执行多条sql语句 /// </summary> /// <param name="sqls"></param> /// <returns></returns> //public static bool ExecuteCommandByTran(params ExecuteSql[] sqls) //{ // using (SqlConnection conn = new SqlConnection(Connstr)) // { // conn.Open(); // //为数据库连接打开事务 // SqlTransaction tran = conn.BeginTransaction(); // SqlCommand cmd = new SqlCommand(); // cmd.Connection = conn; // //为命令对象指定要应用事务 // cmd.Transaction = tran; // try // { // for (int i = 0; i < sqls.Length; i++) // { // cmd.CommandText = sqls[i].SqlString; // cmd.Parameters.AddRange(sqls[i].Pars); // cmd.ExecuteNonQuery(); // } // //没有异常的情况下,提交事务 // tran.Commit(); // return true; // } // catch (Exception ex) // { // //事务滚回去 // tran.Rollback(); // return false; // } // finally // { // conn.Close(); // } // } //} #endregion #region 存储过程部分 /// <summary> /// 执行sql语句返回DataTable /// </summary> /// <param name="sql">安全的sql语句</param> /// <returns>根据sql语句得到所有记录</returns> public static DataTable GetDataTableByProcedure(string sql) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); conn.Close(); return dt; } } /// <summary> /// 执行带参数的sql语句返回DataTable /// </summary> /// <param name="sql">带参数的sql语句</param> /// <param name="values">参数</param> /// <returns>根据sql语句得到所有记录</returns> public static DataTable GetDataTableByProcedure(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); //指定查询的方式使用存储过程 cmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.Parameters.AddRange(values); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); conn.Close(); return dt; } } /// <summary> /// 执行无参数的sql语句 /// </summary> /// <param name="sql">sql语句</param> /// <returns>影响的行数</returns> public static int ExecuteCommandByProcedure(string sql) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); int num = cmd.ExecuteNonQuery(); conn.Close(); return num; } } /// <summary> /// 执行有参数的sql语句 /// </summary> /// <param name="sql">sql语句</param> /// <param name="values">参数</param> /// <returns>影响的行数</returns> public static int ExecuteCommandByProcedure(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(values); conn.Open(); int num = cmd.ExecuteNonQuery(); conn.Close(); return num; } } /// <summary> /// 执行无参数的sql语句,返回首行首列 /// </summary> /// <param name="sql">sql语句</param> /// <returns>返回首行首列</returns> public static object GetScalarByProcedure(string sql) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); object obj = cmd.ExecuteScalar(); conn.Close(); return obj; } } /// <summary> /// 执行有参数的sql语句,返回首行首列 /// </summary> /// <param name="sql">sql语句</param> /// <param name="values">参数,是否返回首行首列</param> /// <returns>返回首行首列</returns> public static object ExecuteScalarByProcedure(string sql, params SqlParameter[] values) { using (SqlConnection conn = new SqlConnection(Connstr)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(values); conn.Open(); object obj = cmd.ExecuteScalar(); conn.Close(); return obj; } } #endregion #region 帮助方法 //判断是否为主键 public static bool IsPrimaryKey(String columnName, String tableName) { using (SqlConnection conn = new SqlConnection(Connstr)) { conn.Open(); SqlParameter[] parameters = new SqlParameter[3]; parameters[0] = new SqlParameter("@table_name", tableName); parameters[1] = new SqlParameter("@table_owner", null); parameters[2] = new SqlParameter("@table_qualifier", null); SqlCommand comm = new SqlCommand(); //指定存储过程名称 comm.CommandText = "sp_pkeys"; comm.Connection = conn; //声明执行命令的类型 comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddRange(parameters); SqlDataAdapter ad = new SqlDataAdapter(comm); DataTable dt = new DataTable(); ad.Fill(dt); DataRowCollection drc = dt.Rows; if (drc.Count == 0 || (drc[0]["COLUMN_NAME"].ToString().ToLower() != columnName.ToLower())) return false; else return true; } } //判断是否为外键 public static bool IsForeignKey(String columnName, String tableName) { using (SqlConnection conn = new SqlConnection(Connstr)) { conn.Open(); SqlParameter[] parameters = new SqlParameter[3]; parameters[0] = new SqlParameter("@pktable_name", tableName); parameters[1] = new SqlParameter("@pktable_owner", null); parameters[2] = new SqlParameter("@pktable_qualifier", null); SqlCommand comm = new SqlCommand(); //指定存储过程名称 comm.CommandText = "sp_fkeys"; comm.Connection = conn; //声明执行命令的类型 comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddRange(parameters); SqlDataAdapter ad = new SqlDataAdapter(comm); DataTable dt = new DataTable(); ad.Fill(dt); DataRowCollection drc = dt.Rows; if (drc.Count == 0 || (drc[0]["COLUMN_NAME"].ToString().ToLower() != columnName.ToLower())) return false; else return true; } } #endregion } }
2BaseDAL定义了一些主要的增删改查的方法因为只是开始做 所以只能是对单张表的操作
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Reflection; using Model; namespace DAL { public abstract class BassDAL<T> where T : new() { //单表查询 public virtual List<T> GetAllModels() { List<T> tList = new List<T>(); string name = new T().GetType().Name; string sqlStr = "select * from " + name; DataTable dt = DBHelper.GetDataTable(sqlStr); foreach (DataRow dr in dt.Rows) { T t = new T(); PropertyInfo[] proList = t.GetType().GetProperties(); foreach (PropertyInfo pi in proList) { string piName = pi.Name; if (dt.Columns.Contains(piName)) { if (!pi.CanWrite) continue; object value =http://www.mamicode.com/ dr[piName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } tList.Add(t); } return tList; } //添加 public virtual bool AddModel(T model) { PropertyInfo[] proList = model.GetType().GetProperties(); string name = model.GetType().Name; string sqlStr = "insert into " + name; string filed = "("; string values = " values ("; foreach (PropertyInfo pi in proList) { filed += pi.Name + ","; values += "‘" + pi.GetValue(model, null).ToString() + "‘,"; } filed = filed.Substring(0, filed.Length - 1) + ")"; values = values.Substring(0, values.Length - 1) + ")"; sqlStr = sqlStr + filed + values; return DBHelper.ExecuteCommand(sqlStr) != 0 ? true : false; } //修改 public virtual bool UpdateModel(T model) { PropertyInfo[] proList = model.GetType().GetProperties(); string name = model.GetType().Name; string sqlStr = "update " + name + " set "; foreach (PropertyInfo pi in proList) { if (pi.GetValue(model, null) != null && DBHelper.IsPrimaryKey(pi.Name, name) == false) { sqlStr += pi.Name + "=‘" + pi.GetValue(model, null).ToString() + "‘,"; } } sqlStr = sqlStr.Substring(0, sqlStr.Length - 1); foreach (PropertyInfo pi in proList) { if (pi.GetValue(model, null) != null && DBHelper.IsPrimaryKey(pi.Name, name) != false) { sqlStr += " where " + pi.Name + "=‘" + pi.GetValue(model, null).ToString() + "‘"; } } return DBHelper.ExecuteCommand(sqlStr) != 0 ? true : false; } //删除 public virtual bool DeleteModel(T model) { PropertyInfo[] proList = model.GetType().GetProperties(); string name = model.GetType().Name; string sqlStr = "delete " + name + " where "; foreach (PropertyInfo pi in proList) { if (pi.GetValue(model, null) != null) { if (DBHelper.IsPrimaryKey(pi.Name, name) != false) { sqlStr += pi.Name + "=‘" + pi.GetValue(model, null).ToString()+"‘"; } } } return DBHelper.ExecuteCommand(sqlStr) != 0 ? true : false; } } }
3使用BaseDAL
用实体类的DAL继承BaseDAL由于BaseDAL是一个泛型类所以要在继承的时候传入实体类即BassDAL<PetInfo>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Model; namespace DAL { public class Pet:BassDAL<PetInfo> { } }
4这样就可以使用了
using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using DAL; using Model; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { PetInfo petinfo = new PetInfo { Id = 1, }; new Pet().DeleteModel(petinfo); this.GridView1.DataSource = new Pet().GetAllModels(); this.GridView1.DataBind(); } }
有什么不足希望大家指出来,一起完善哈
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。