c#数据库领域模型操作类
来源：互联网 发布：淘宝店铺信誉怎么提升 编辑：程序博客网 时间：2024/05/01 22:46
概括：
添加数据到表：int row = new DBModel(表名).Add(数据);
删除数据：int row = new DBModel(表名).Where(where sql语句).Param(参数).Delete();
更改数据：int row = new DBModel(表名).Where(where sql语句).Param(参数).Update();
查询数据：DataTable dt = new DBModel(表名).Where(where sql语句).Param(参数).Find();
DataRow dr = new DBModel(表名).Where(where sql语句).Param(参数).FindOne();
Object obj = new DBModel(表名).Where(where sql语句).Param(参数).FindFiled(字段名);
DataTable dt = new DBModel(__TS__表名1).Join(__TS__表名2 join sql语句).Where(where sql语句).Param(参数).Find();
该类实现借鉴领域模型，只实现了其中对数据操作的部分，对表的映射因为简单的项目中使用少，而且项目时间要求比较紧，就没有实现。支持链式操作，更加贴近自然语言使用习惯，使sql操作使用更加简单，以上示例中的参数不一定全部需要，根据需要选择需要的参数，可以实现大部分的sql操作，只需要一句代码，不需要写复杂的sql语句，包括简单的连表、分页等等。
一、数据库连接查询类
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Configuration;using System.Data.SqlClient;using System.Data;namespace EditorOnline.Util{ public class DBService { private static String tableSuffix = ConfigurationManager.AppSettings["tableSuffix"]; private static String connectionString = ConfigurationManager.AppSettings["ConnectionString"]; public const String tableSuffixTag = "__TS__"; public static String GetTableSuffix() { //tableSuffix = "e1_"; //connectionString = @"server=np:\\.\pipe\LOCALDB#E2DA15A0\tsql\query;database=DB1;Integrated Security=True"; return tableSuffix; } public static String getTableName(String tableName) { return tableSuffix + tableName; } public static void setConnectionString(String _connectionString) { connectionString = _connectionString; } /// <summary> /// 将sql中表名前缀换为真实前缀 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static String ReplaceTS(String sql) { sql = sql.Replace(tableSuffixTag, tableSuffix); return sql; } /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteUpdate(string SQLString) { SQLString = ReplaceTS(SQLString); using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteUpdate(string SQLString, params SqlParameter[] cmdParms) { SQLString = ReplaceTS(SQLString); using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } } } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataTable ExecuteQuery(string SQLString, params SqlParameter[] cmdParms) { SQLString = ReplaceTS(SQLString); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds); cmd.Parameters.Clear(); return ds.Tables[0]; } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } } } } /// <summary> /// 执行一条语句，返回第一条记录的第一列查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object ExecuteQuerySingle(string SQLString, params SqlParameter[] cmdParms) { SQLString = ReplaceTS(SQLString); using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// sql语句过滤 /// </summary> /// <param name="sSql"></param> /// <returns></returns> public static String FilterSql(string sSql) { sSql = sSql.ToLower().Trim(); sSql = sSql.Replace("exec", ""); sSql = sSql.Replace("delete", ""); sSql = sSql.Replace("master", ""); sSql = sSql.Replace("truncate", ""); sSql = sSql.Replace("declare", ""); sSql = sSql.Replace("create", ""); sSql = sSql.Replace("xp_", "no"); sSql = sSql.Replace("'", ""); return sSql; } }}二、数据库查询模型类，因为只是简单的项目，所以没有完全按照领域模型进行封装，如果需要可以自行进行实现，只是更新时自动获取主键using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;using System.Data.SqlClient;using System.Collections;using System.Collections.Specialized;namespace EditorOnline.Util{ public class DBModel { /// <summary> /// 数据库表前缀 /// </summary> public String tableSuffix { get; set; } /// <summary> /// 没有前缀的表 /// </summary> private String[] notSuffixTables; /// <summary> /// 数据库表数组 /// </summary> private String[] tables; /// <summary> /// 查询where条件 /// </summary> private String whereSql = ""; /// <summary> /// 分页页数 /// </summary> private int page = 0; /// <summary> /// 分页长度 /// </summary> private int pageLength = 0; /// <summary> /// 字段 /// </summary> private String[] fields; /// <summary> /// 排序sql /// </summary> private String orderSql = ""; /// <summary> /// join sql /// </summary> private String join = ""; /// <summary> /// 预处理参数 /// </summary> private Dictionary<String, Object> param; public String lastSql { get; set; } public DBModel() { this.tableSuffix = DBService.GetTableSuffix(); } public DBModel(params String[] tables) { this.tableSuffix = DBService.GetTableSuffix(); this.notSuffixTables = tables; this.tables = AddTableSuffix(tables); } /// <summary> /// 清除查询条件 /// </summary> private void Remove() { this.whereSql = ""; this.page = 0; this.pageLength = 0; this.fields = new String[]{}; this.orderSql = ""; this.join = ""; this.param = null; } /// <summary> /// 多表时指定别名，格式为表名.别名，如user.u /// </summary> /// <param name="tables"></param> /// <returns></returns> public DBModel Table(params String[] tables) { this.notSuffixTables = tables; this.tables = AddTableSuffix(tables); return this; } public String[] AddTableSuffix(String[] tables) { int i = 0; foreach(String table in tables) { tables[i] = tableSuffix + table; i++; } return tables; } /// <summary> /// sql where条件 /// </summary> /// <param name="whereSql"></param> /// <returns></returns> public DBModel Where(String whereSql) { this.whereSql = whereSql; return this; } public DBModel Page(int page, int pageLength) { this.page = page; this.pageLength = pageLength; return this; } public DBModel Field(params String[] fields) { this.fields = fields; return this; } public DBModel OrderBy(String orderSql) { this.orderSql = orderSql; return this; } /// <summary> /// 自带where条件，继续join则继续调用Join /// </summary> /// <param name="join"></param> /// <returns></returns> public DBModel Join(String join) { this.join = join; return this; } /// <summary> /// 设置预处理参数 /// </summary> /// <param name="param"></param> /// <returns></returns> public DBModel Param(Dictionary<String, Object> param) { this.param = param; return this; } /// <summary> /// 返回字典类型键值组成的SqlParameter数组，并加上param中的dic /// </summary> /// <param name="dic"></param> /// <returns></returns> private SqlParameter[] GetSqlParameter(Dictionary<String, Object> dic) { int paramCount = 0; if (param != null) { paramCount = param.Count(); } SqlParameter[] sqlParameterArray = new SqlParameter[dic.Count + paramCount]; int i = 0; foreach(var item in dic) { SqlParameter sqlParameter = new SqlParameter(item.Key, item.Value); sqlParameterArray[i] = sqlParameter; i++; } if(paramCount > 0) { foreach(var item in param) { SqlParameter sqlParameter = new SqlParameter(item.Key, item.Value); sqlParameterArray[i] = sqlParameter; i++; } } return sqlParameterArray; } /// <summary> /// dic中键与表中键一致，值对应相应的值 /// </summary> /// <param name="sql"></param> /// <param name="dic"></param> /// <returns></returns> public int ExecuteUpdate(String sql, Dictionary<String, Object> dic) { int row = DBService.ExecuteUpdate(sql, GetSqlParameter(dic)); return row; } public DataTable ExecuteQuery(String sql, Dictionary<String, Object> dic) { DataTable dt = DBService.ExecuteQuery(sql, GetSqlParameter(dic)); return dt; } /// <summary> /// 查询，没有dic，传递一个空dic进去 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable ExecuteQuery(String sql) { DataTable dt = DBService.ExecuteQuery(sql, GetSqlParameter(new Dictionary<String, Object>())); return dt; } public Object ExecuteQuerySingle(String sql, Dictionary<String, Object> dic) { Object obj = DBService.ExecuteQuerySingle(sql, GetSqlParameter(dic)); return obj; } public Object ExecuteQuerySingle(String sql) { Object obj = DBService.ExecuteQuerySingle(sql, GetSqlParameter(new Dictionary<String, Object>())); return obj; } /// <summary> /// 添加数据 /// </summary> /// <param name="dic"></param> /// <returns></returns> public int Add(Dictionary<String, Object> dic) { String sql = SqlCreate.CreateAddSql(tables[0], dic); lastSql = sql; int row = ExecuteUpdate(sql, dic); return row; } /// <summary> /// 直接把post数组添加进数据库 /// input name跟数据库数组一致 /// </summary> /// <param name="postData"></param> /// <returns></returns> public int Add(NameValueCollection postData) { Dictionary<String, Object> dic = new Dictionary<String, Object>(); foreach(String key in postData) { dic.Add(key, postData[key]); } int row = Add(dic); return row; } public static Dictionary<String, Object> GetDictionary(NameValueCollection postData) { Dictionary<String, Object> dic = new Dictionary<String, Object>(); foreach (String key in postData) { dic.Add(key, postData[key]); } return dic; } /// <summary> /// 使用post值以及dic键值对添加数据到数据库 /// </summary> /// <param name="postData"></param> /// <param name="dic"></param> /// <returns></returns> public int Add(NameValueCollection postData, Dictionary<String, Object> dic) { foreach (String key in postData) { dic.Add(key, postData[key]); } int row = Add(dic); return row; } public int Delete(Dictionary<String, Object> dic) { String where = SqlCreate.CreateWhereSql(whereSql, dic); String sql = String.Format("delete from {0} {1}", tables[0], where); int row = ExecuteUpdate(sql, dic); lastSql = sql; return row; } public int Delete() { String where = SqlCreate.CreateWhereSql(whereSql); String sql = "delete from " + tables[0] + " " + where; lastSql = sql; int row = DBService.ExecuteUpdate(sql); return row; } /// <summary> /// 获取表主键，没有返回空 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public String GetPKName(String tableName) { String sql = SqlCreate.CreatePKNameSql(tableName); lastSql = sql; DataTable dt = DBService.ExecuteQuery(sql); foreach(DataRow dr in dt.Rows) { return (String)dr["name"]; } return ""; } /// <summary> /// 更改表数据，主键可做为更改条件 /// </summary> /// <param name="dic"></param> /// <returns></returns> public int Update(Dictionary<String, Object> dic) { String pkName = GetPKName(tables[0]); String pkWhere = SqlCreate.CreatePKWhere(pkName, dic); if(whereSql == "") { whereSql = pkWhere; } else { whereSql = pkWhere + " and " + whereSql; } dic.Remove(pkName); String updateSql = SqlCreate.CreateUpdateSql(dic); String where = SqlCreate.CreateWhereSql(whereSql); String sql = String.Format("update {0} set {1} {2}", tables[0], updateSql, where); lastSql = sql; int row = ExecuteUpdate(sql, dic); return row; } public static Dictionary<String, Object> GetDictionary(String key, Object value) { Dictionary<String, Object> dic = new Dictionary<string, object>(); dic.Add(key, value); return dic; } /// <summary> /// 查找 /// </summary> /// <returns></returns> public DataTable Find() { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String selectSql = ""; String where = SqlCreate.CreateWhereSql(whereSql); if (page != 0 && pageLength != 0) { selectSql = String.Format("select top {0} {1} from (select row_number() over(order by {2}) as ROW_ID, {3} from {4} {5} {6}) t where ROW_ID > {7}", pageLength, fieldSql, orderSql, fieldSql, tableSql, join, where, (page - 1) * pageLength); } else { orderSql = SqlCreate.CreateOrderSql(orderSql); selectSql = String.Format("select {0} from {1} {2} {3} {4}", fieldSql, tableSql, join, where, orderSql); } lastSql = selectSql; DataTable dt = ExecuteQuery(selectSql); return dt; } public DataTable Find(Dictionary<String, Object> dic) { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql, dic); String selectSql = ""; if (page != 0 && pageLength != 0) { selectSql = String.Format("select top {0} {1} from (select row_number() over(order by {2}) as ROW_ID, {3} from {4} {5} {6}) t where ROW_ID > {7}", pageLength,fieldSql, orderSql, fieldSql, tableSql, join, where, (page - 1) * pageLength); } else { orderSql = SqlCreate.CreateOrderSql(orderSql); selectSql = String.Format("select {0} from {1} {2} {3} {4}", fieldSql, tableSql, join, where, orderSql); } lastSql = selectSql; DataTable dt = ExecuteQuery(selectSql, dic); return dt; } public static Dictionary<String, Object> GetDictionary(String[] keyArray, Object[] valueArray) { Dictionary<String, Object> dic = new Dictionary<string, object>(); for (int i = 0; i < keyArray.Length; i++ ) { dic.Add(keyArray[i], valueArray[i]); } return dic; } public DataRow FindOne(Dictionary<String, Object> dic) { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql, dic); String selectSql = String.Format("select {0} from {1} {2} {3}", fieldSql, tableSql, join, where); lastSql = selectSql; DataTable dt = ExecuteQuery(selectSql, dic); if(dt != null && dt.Rows.Count > 0) { return dt.Rows[0]; } else { return null; } } public DataRow FindOne() { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql); String selectSql = String.Format("select {0} from {1} {2} {3}", fieldSql, tableSql, join, where); lastSql = selectSql; DataTable dt = ExecuteQuery(selectSql); if (dt != null && dt.Rows.Count > 0) { return dt.Rows[0]; } else { return null; } } public Object FindOneField(String field, Dictionary<String, Object> dic) { String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql, dic); String selectSql = String.Format("select {0} from {1} {2} {3}", field, tableSql, join, where); lastSql = selectSql; Object obj = ExecuteQuerySingle(selectSql, dic); return obj; } public Object FindOneField(String field) { String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql); String selectSql = String.Format("select {0} from {1} {2} {3}", field, tableSql, join, where); lastSql = selectSql; Object obj = ExecuteQuerySingle(selectSql); return obj; } /// <summary> /// 执行sql函数 /// </summary> /// <param name="fuctionName"></param> /// <param name="dic"></param> /// <returns></returns> public Object Fuction(String fuctionName, Dictionary<String, Object> dic) { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql, dic); String selectSql = String.Format("select {0}({1}) from {2} {3} {4}", fuctionName, fieldSql, tableSql, join, where); lastSql = selectSql; Object obj = ExecuteQuerySingle(selectSql, dic); return obj; } public Object Fuction(String fuctionName) { String fieldSql = SqlCreate.CreateFieldSql(fields); String tableSql = SqlCreate.CreateTableSql(tables); String where = SqlCreate.CreateWhereSql(whereSql); String selectSql = String.Format("select {0}({1}) from {2} {3} {4}", fuctionName, fieldSql, tableSql, join, where); lastSql = selectSql; Object obj = ExecuteQuerySingle(selectSql); return obj; } public int Count(Dictionary<String, Object> dic) { return (int)Fuction("count", dic); } public int Count() { return (int)Fuction("count"); } public int Max(Dictionary<String, Object> dic) { return (int)Fuction("max", dic); } public int Max() { return (int)Fuction("max"); } public int Min() { return (int)Fuction("min"); } }}三、sql拼接类，SqlServer数据库，如果需要支持多个数据库，可以使用接口来实现
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Collections;namespace EditorOnline.Util{ public class SqlCreate { /// <summary> /// /// </summary> /// <param name="table"></param> /// <param name="dic"></param> /// <returns>insert into table (field1, field2,) values (@field1, @field2)</returns> public static String CreateAddSql(String table, Dictionary<String, Object> dic) { String keySql = ""; String dataSql = ""; int i = 0; foreach (var item in dic) { i++; if (i < dic.Count) { keySql = keySql + item.Key + ", "; dataSql = dataSql + "@" + item.Key + ", "; } else { keySql = keySql + item.Key; dataSql = dataSql + "@" + item.Key; } } String sql = "insert into " + table + " ( " + keySql + " ) " + "values" + " ( " + dataSql + " )"; return sql; } /// <summary> /// /// </summary> /// <param name="fields"></param> /// <returns>table1, table2, table3</returns> public static String CreateFieldSql(String[] fields) { String fieldSql = ""; if(fields == null || fields.Length == 0) { fieldSql = "*"; return fieldSql; } int i = 0; foreach (String field in fields) { i++; if (i < fields.Length) { fieldSql = fieldSql + field + ", "; } else { fieldSql = fieldSql + field; } } return fieldSql; } public static String CreateTableSql(String[] tables) { String tableSql = ""; int j = 0; foreach (String table in tables) { j++; if (j < tables.Length) { tableSql = tableSql + table + ", "; } else { tableSql = tableSql + table; } } return tableSql; } public static String CreateWhereSql(Dictionary<String, Object> dic) { String dicSql = ""; int k = 0; foreach (var item in dic) { k++; if (k < dic.Count) { dicSql = dicSql + item.Key + " = @" + item.Key + " and "; } else { dicSql = dicSql + item.Key + " = @" + item.Key; } } return dicSql; } public static String CreateWhereSql(String where, Dictionary<String, Object> dic) { String dicSql = CreateWhereSql(dic); if(where == null || where == "") { return "where " + dicSql; } else { return "where " + where + " and " + dicSql; } } public static String CreateWhereSql(String where) { if(where == null || where == "") { return ""; } else { return "where " + where; } } public static String CreateOrderSql(String orderSql) { if(orderSql == null || orderSql == "") { return ""; } else { return "order by " + orderSql; } } public static String CreatePKNameSql(String table) { return @"select c.name from sysindexes i join sysobjects o ON i.id = o.id join sysobjects pk ON i.name = pk.name AND pk.parent_obj = i.id AND pk.xtype = 'PK' join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid join syscolumns c ON ik.id = c.id AND ik.colid = c.colid where o.name = '" + table + "' order by ik.keyno"; } public static String CreatePKWhere(String pkName, Dictionary<String, Object> dic) { String pkValue = ""; String pkWhere = ""; if (pkName != "" && dic.ContainsKey(pkName)) { //将主键做为更改条件 //pkValue = (String)dic[pkName]; //pkWhere = pkName + " = '" + pkValue + "'"; pkWhere = pkName + " = '" + dic[pkName] + "'"; return pkWhere; } return ""; } public static String CreateUpdateSql(Dictionary<String, Object> dic) { String dicSql = ""; int i = 0; foreach (var item in dic) { i++; if (i < dic.Count) { dicSql = dicSql + item.Key + " = " + "@" + item.Key + ", "; } else { dicSql = dicSql + item.Key + " = " + "@" + item.Key; } } return dicSql; } }}四、调用示例
1、管理员登陆
public static DataRow GetAdmin(String userNameOrEmail, String password) { Dictionary<String, object> dic = new Dictionary<string, object>(); dic.Add("userEmail", userNameOrEmail); dic.Add("userName", userNameOrEmail); dic.Add("userPassword", password); DBModel dm = new DBModel("user"); DataRow dr = dm.Where("userTypeId = '2' and userPassword = @userPassword and (userEmail = @userEmail or userName = @userName)").Param(dic).FindOne(); return dr; }2、获取资讯public static DataTable GetNewsTable(int newsTypeId,String language,int page, int pagelength) { Dictionary<String, Object> dic = new Dictionary<string, object>(); dic.Add("newsTypeId", newsTypeId); dic.Add("language", language); DBModel dm = new DBModel("news"); DataTable dt = dm.OrderBy("newsId desc").Field("newsId", "newsTitle", "newsSummary", "pictureUrl").Where("status = '1' and addTime < getdate()").Page(page, pagelength).Find(dic); return dt; }3、直接添加数据到表，post数组与表列一致
int row = new DBModel("navigationBar").Add(Request.Form); 0 0
- c#数据库领域模型操作类
- 领域类之模型
- C#数据库操作类
- c#数据库操作类
- C#数据库操作类
- C#数据库操作类
- C#数据库操作类
- C#数据库操作类
- C#数据库操作类
- C#数据库操作类
- C#数据库类操作
- C# 数据库操作类
- C#数据库操作类
- c#数据库操作类
- C#数据库操作类
- c# 数据库操作类Sql
- C# 数据库操作基类
- C# 通用数据库类操作
- uboot 源码官方下载地址
- 自然语言处理(nlp)的流程图
- [Golang]妙用channel
- Java处理带BOM(字节顺序标记)的文本
- OpenCV函数cvFindContours
- c#数据库领域模型操作类
- 黑马程序员——Java面向对象之构造方法、静态、this关键字
- OpenCV数据结构之Mat
- TCP/UDP和HTTP简单解释
- android混淆排除所有注解类(android annotation proguard config)
- 中关村－DIY浏览器之Chrome基础
- assets目录下文件 引起ResouceNotFound问题
- Objective-C基础——面向对象语法04
- 【bzoj 1692】后缀数组
