一个简单的数据库访问工具类
View Code
1 public class TinyDb 2 { 3 public TinyDb(DbConnection connection) 4 { 5 this.connection = connection; 6 } 7 8 [Obsolete] 9 private readonly DbConnection connection = null; 10 protected DbConnection Connection 11 { 12 get { return this.connection; } 13 } 14 15 private void Open() 16 { 17 if (this.Connection.State != ConnectionState.Open) 18 { 19 this.Connection.Open(); 20 } 21 } 22 23 private void Close() 24 { 25 this.Connection.Close(); 26 } 27 28 private DbCommand CreateCommand(string commandText, params object[] values) 29 { 30 var command = Connection.CreateCommand(); 31 command.Connection = Connection; 32 command.CommandText = commandText; 33 if (values != null) 34 { 35 for (int i = 0; i < values.Length; i++) 36 { 37 var param = command.CreateParameter(); 38 param.ParameterName = i.ToString(); 39 param.Value = values[i]; 40 command.Parameters.Add(param); 41 } 42 } 43 return command; 44 } 45 46 public int ExecuteNonQuery(string commandText, params object[] values) 47 { 48 DbCommand command = this.CreateCommand(commandText, values); 49 try 50 { 51 this.Open(); 52 return command.ExecuteNonQuery(); 53 } 54 finally 55 { 56 this.Close(); 57 } 58 } 59 60 public object ExecuteScalar(string commandText, params object[] values) 61 { 62 DbCommand command = this.CreateCommand(commandText, values); 63 try 64 { 65 this.Open(); 66 return command.ExecuteScalar(); 67 } 68 finally 69 { 70 this.Close(); 71 } 72 } 73 74 public dynamic ExecuteFirstResult(string commandText, params object[] values) 75 { 76 DbCommand command = this.CreateCommand(commandText, values); 77 try 78 { 79 this.Open(); 80 using (DbDataReader reader = command.ExecuteReader()) 81 { 82 if (reader.Read()) 83 { 84 DbDynamicObject result = new DbDynamicObject(); 85 string[] fields = new string[reader.FieldCount]; 86 for (int i = 0; i < fields.Length; i++) 87 { 88 fields[i] = reader.GetName(i); 89 result[fields[i]] = reader.GetValue(i); 90 } 91 return result; 92 93 } 94 } 95 } 96 finally 97 { 98 this.Close(); 99 } 100 return null; 101 } 102 103 public List<dynamic> ExecuteResults(string commandText, params object[] values) 104 { 105 List<dynamic> results = new List<dynamic>(); 106 DbCommand command = this.CreateCommand(commandText, values); 107 try 108 { 109 this.Open(); 110 using (var reader = command.ExecuteReader()) 111 { 112 if (reader.HasRows) 113 { 114 string[] fields = new string[reader.FieldCount]; 115 while (reader.Read()) 116 { 117 DbDynamicObject item = new DbDynamicObject(); 118 for (int i = 0; i < fields.Length; i++) 119 { 120 fields[i] = reader.GetName(i); 121 item[fields[i]] = reader.GetValue(i); 122 } 123 results.Add(item); 124 } 125 } 126 127 } 128 } 129 finally 130 { 131 this.Close(); 132 } 133 return results; 134 } 135 136 }
自定义的dynamic扩展
View Code
1 public class DbDynamicObject : DynamicObject 2 { 3 public object this[string field] 4 { 5 get 6 { 7 return members.ContainsKey(field) ? members[field] : null; 8 } 9 set 10 { 11 if (members.ContainsKey(field)) 12 { 13 members[field] = value; 14 } 15 else 16 { 17 members.Add(field, value); 18 } 19 } 20 } 21 22 private class IgnoreCaseStringComparer : StringComparer 23 { 24 public override int Compare(string x, string y) 25 { 26 return x.ToLower().CompareTo(y.ToLower()); 27 } 28 29 public override bool Equals(string x, string y) 30 { 31 return x.Equals(y, StringComparison.CurrentCultureIgnoreCase); 32 } 33 34 public override int GetHashCode(string obj) 35 { 36 return obj.ToLower().GetHashCode(); 37 } 38 } 39 40 private Dictionary<string, object> members = new Dictionary<string, object>(new IgnoreCaseStringComparer()); 41 42 public override IEnumerable<string> GetDynamicMemberNames() 43 { 44 return members.Keys.AsEnumerable(); 45 } 46 47 public override bool TrySetMember(SetMemberBinder binder, object value) 48 { 49 this[binder.Name] = value; 50 return true; 51 } 52 53 public override bool TryGetMember(GetMemberBinder binder, out object result) 54 { 55 result = this[binder.Name]; 56 return true; 57 } 58 }
实现SQL Server访问
View Code
1 public class SqlTinyDb : TinyDb 2 { 3 public new SqlConnection Connection 4 { 5 get { return (SqlConnection)base.Connection; } 6 } 7 8 public SqlTinyDb(SqlConnection connection) 9 : base(connection) 10 { 11 } 12 13 public SqlTinyDb(string connectionString) 14 : this(new SqlConnection(connectionString)) 15 { 16 } 17 18 public SqlTinyDb(SqlTinyDb tinydb) 19 : this(tinydb.Connection) 20 { 21 22 } 23 }
扩展TinyDb
View Code
1 public static class TinyDbExtensions 2 { 3 public static int Insert(this TinyDb db, string table, object vars, string prefix = "[", string suffix = "]") 4 { 5 var dict = vars.AsDictionary(); 6 string commandText = null; 7 object[] values = null; 8 if (dict.Count > 0) 9 { 10 var names = dict.Keys.ToArray(); 11 values = new object[names.Length]; 12 var indexs = new int[names.Length]; 13 for (int i = 0; i < names.Length; i++) 14 { 15 values[i] = dict[names[i]]; 16 indexs[i] = i; 17 } 18 string strFields = string.Join(", ", names.Select(e => string.Format("{1}{0}{2}", e, prefix, suffix))); 19 string strIndexs = string.Join(", ", indexs.Select(i => "@" + i)); 20 commandText = string.Format("INSERT INTO {3}{0}{4} ({1}) VALUES ({2})", table, strFields, strIndexs, prefix, suffix); 21 } 22 return db.ExecuteNonQuery(commandText, values); 23 } 24 25 public static int Delete(this TinyDb db, string table, object where, string prefix = "[", string suffix = "]") 26 { 27 var dict = where.AsDictionary(); 28 StringBuilder sqlBuilder = new StringBuilder(); 29 sqlBuilder.AppendFormat("DELETE FROM {1}{0}{2} ", table, prefix, suffix); 30 // 构建where条件 31 var whereTuple = GenerateCondition(where, 0, prefix, suffix); 32 sqlBuilder.Append(whereTuple.Item1); 33 string commandText = sqlBuilder.ToString(); 34 35 return db.ExecuteNonQuery(commandText, whereTuple.Item2); 36 } 37 38 public static int Update(this TinyDb db, string table, object vars, object where = null, string prefix = "[", string suffix = "]") 39 { 40 StringBuilder sqlBuilder = new StringBuilder(); 41 sqlBuilder.AppendFormat("UPDATE {1}{0}{2}", table, prefix, suffix); 42 var setTuple = GetUpdateSetValues(vars, prefix, suffix); 43 // 构建where条件 44 var whereTuple = GenerateCondition(where, setTuple.Item2.Length, prefix, suffix); 45 sqlBuilder.Append(setTuple.Item1); 46 sqlBuilder.Append(whereTuple.Item1); 47 var values = new List<object>(); 48 values.AddRange(setTuple.Item2); 49 values.AddRange(whereTuple.Item2); 50 return db.ExecuteNonQuery(sqlBuilder.ToString(), values.ToArray()); 51 } 52 53 public static dynamic SelectFirstSelect(this TinyDb db, string table, object where = null, string prefix = "[", string suffix = "]") 54 { 55 StringBuilder sqlBuilder = new StringBuilder(); 56 sqlBuilder.AppendFormat("SELECT * FROM {1}{0}{2}", table, prefix, suffix); 57 var whereTuple = GenerateCondition(where, 0, prefix, suffix); 58 sqlBuilder.Append(whereTuple.Item1); 59 return db.ExecuteFirstResult(sqlBuilder.ToString(), whereTuple.Item2); 60 } 61 62 public static List<dynamic> Select(this TinyDb db, string table, object where = null, string prefix = "[", string suffix = "]") 63 { 64 StringBuilder sqlBuilder = new StringBuilder(); 65 sqlBuilder.AppendFormat("SELECT * FROM {1}{0}{2}", table, prefix, suffix); 66 var whereTuple = GenerateCondition(where, 0, prefix, suffix); 67 sqlBuilder.Append(whereTuple.Item1); 68 return db.ExecuteResults(sqlBuilder.ToString(), whereTuple.Item2); 69 } 70 71 #region Update 设置 72 private static Tuple<string, object[]> GetUpdateSetValues(object vars, string prefix, string suffix) 73 { 74 StringBuilder setSqlBuilder = new StringBuilder(); 75 var dict = vars.AsDictionary(); 76 var fields = dict.Keys.ToArray(); 77 var values = new object[fields.Length]; 78 for (int i = 0; i < fields.Length; i++) 79 { 80 values[i] = dict[fields[i]]; 81 setSqlBuilder.Append(i == 0 ? " SET " : ", "); 82 setSqlBuilder.AppendFormat("{2}{0}{3} = @{1}", fields[i], i, prefix, suffix); 83 } 84 return Tuple.Create(setSqlBuilder.ToString(), values); 85 } 86 #endregion 87 88 #region 构建where的条件 89 private static Tuple<string, object[]> GenerateCondition(object wheres, int skipParamCount, string prefix, string suffix, bool isAnd = true) 90 { 91 string join = isAnd ? "AND" : "OR"; 92 StringBuilder whereSqlBuilder = new StringBuilder(); 93 var dict = wheres.AsDictionary(); 94 var fields = dict.Keys.ToArray(); 95 var valueList = new List<object>(); 96 for (int i = 0; i < fields.Length; i++) 97 { 98 var value = dict[fields[i]]; 99 // 判断是否是第一次添加 100 if (i == 0) 101 { 102 whereSqlBuilder.AppendFormat(" WHERE", fields[i], i + skipParamCount); 103 } 104 else 105 { 106 whereSqlBuilder.Append(" " + join); // AND ? OR ? 107 } 108 // null Or DBNull 109 if (value == null || value == DBNull.Value) 110 { 111 whereSqlBuilder.AppendFormat(" {1}{0}{2} IS NULL", fields[i], prefix, suffix); 112 } 113 else 114 { 115 whereSqlBuilder.AppendFormat(" {2}{0}{3} = @{1}", fields[i], i + skipParamCount, prefix, suffix); 116 valueList.Add(value); 117 } 118 } 119 return Tuple.Create(whereSqlBuilder.ToString(), valueList.ToArray()); 120 } 121 #endregion 122 }
扩展Object->Dictionary
View Code
1 public static class ObjectExtensions 2 { 3 public static IDictionary<string, object> AsDictionary(this object value) 4 { 5 if (value is IDictionary<string, object>) 6 { 7 return value as IDictionary<string, object>; 8 } 9 10 IDictionary<string, object> dict = new Dictionary<string, object>(); 11 12 if (value != null) 13 { 14 var properties = value.GetType().GetProperties(); 15 foreach (var item in properties) 16 { 17 if (item.CanRead) 18 { 19 dict.Add(item.Name, item.GetValue(value, null)); 20 } 21 } 22 } 23 24 return dict; 25 } 26 }
调用预览
View Code
1 class Program 2 { 3 static void Main(string[] args) 4 { 5 var db = new SqlTinyDb(DbHelper.ConnectionString); 6 // 插入数据 7 db.Insert("Test", new { id = 1, name = "zhangsan1" }); 8 db.Insert("Test", new { id = 2, name = "zhangsan2" }); 9 db.Insert("Test", new { id = 3, name = "zhangsan3" }); 10 db.Insert("Test", new { id = 4, name = "zhangsan4", borndate = DateTime.Now }); 11 // 更新dbnull数据 12 int uRowCount = db.Update("Test", new { name = "新测试数据" }, new { borndate = DBNull.Value }); 13 // 删除数据 14 int dRowCount = db.Delete("Test", new { id = 1}); 15 // 查询数据 16 var result = db.Select("Test"); 17 // 查询borndate为空的数据还可以重置表的前缀 Test -> [Test](默认支持MS SQL, 如果是Mysql或者oracle需要重置成``) 18 var result2 = db.Select("Test", where: new { borndate = DBNull.Value }, prefix: "[", suffix: "]"); 19 20 Console.ReadLine(); 21 } 22 }
