C#操作数据库简单辅助工具

C#操作数据库简单辅助工具

前段时间开始学数据库，被各种SQL给蒙倒了，想存取几个数据这么麻烦，于是想着写个避免重复编写SQL的工具。
目标是这样

TestClass obj1;DatabaseTools.CreateTable(obj1.GetType());      //建表DatabaseTools.SaveItem(obj1);       //存盘TestClass obj2;obj2 = DatabaseTools.LoadItem(obj2.GetType(), "id=1");      //读取

一些市面上的工具有这样的功能（比如XAF），不过对于我这种只写些小东西的人来说太臃肿了。我希望只引用一个库就能实现这些操作。
首先需要类具有描述数据表的功能，这里使用C#的attribute

//DataTableAttr.csnamespace DatabaseTools {    //数据表Attribute    public class DataTableAttr : Attribute {        //Schema 名        private string schemaName;        public string SchemaName {            get {                return schemaName;            }            set {                schemaName = value;            }        }        //Table 名        private string tableName;        public string TableName {            get {                return tableName;            }            set {                tableName = value;            }        }        public DataTableAttr() {            schemeName = "_Default_";            tableName = "_Default_";        }    }}

//DataColumnAttr.csnamespace DatabaseTools {    //数据列 Attribute    public class DataColumnAttr : Attribute {        //名称        private string name;        public string Name {            get {                return name;            }            set {                name = value;            }        }        //数据类型        private string dataType;        public string DataType {            get {                return dataType;            }            set {                dataType = value;            }        }        //是否主键        private bool primaryKey;        public bool PrimaryKey {            get {                return primaryKey;            }            set {                primaryKey = value;            }        }        //是否不能为空        private bool notNull;        public bool NotNull {            get {                return notNull;            }            set {                notNull = value;            }        }        public DataColumnAttr() {            this.Name = "_Default_";            this.DataType = "_Default_";            this.PrimaryKey = false;            this.NotNull = false;        }        //-----------------------------------------------------        public PropertyInfo Info = null;        private static string SQLTemplete_PropertyString = "\t{0} \t{1} \t{0} \t{3}, \r\n";        public string ToPropertyString() {            string primaryKey_str;            string notNull_str;            if (this.PrimaryKey == true) primaryKey_str = "Primary Key";            else primaryKey_str = "";            if (this.NotNull == true) notNull_str = "Not Null";            else notNull_str = "Null";            return string.Format(SQLTemplete_PropertyString, this.Name, this.DataType, primaryKey_str, notNull_str);        }    }}

接下来需要一个根据数据表描述信息来生成SQL的类

//SQLCreateTools.csnamespace DatabaseTools {    public class SQLCreateTools {        //SQL生成工具保存        private static Dictionary<Type, SQLCreateTools> SQLCreateToolsSaved                                 = new Dictionary<Type, SQLCreateTools>();        //获取        public static SQLCreateTools Get(Type type) {            if (type == null) return null;            SQLCreateTools result = null;            if (SQLCreateToolsSaved.ContainKey(type)) {                result = SQLCreateToolsSaved[type];            }            if (result == null) {                result = SQLCreateTools.Create(type);            }            return result;        }        //新建        public static SQLCreateTools Create(Type type) {            SQLCreateTools tools = new SQLCreateTools(type);            if (tools.StructType == null) {                return null;            } else {                return tools;            }        }        //        public List<DataColumnAttr>     DataColumnAttrList;        //        private DataColumnAttr          PrimaryKeyAttr;        //        private DataTable               TableAttr;        //        private Type structType;        //        public Type StructType {            get {                return structType;            }            set {                structType = value;                //遍历目标类型的 Attribute 找 DataTableAttr                this.TableAttr = null;                foreach (Attribute attr in this.structType.GetCustomAttributes(true)) {                    DataTableAttr tableAttr = attr as DataTableAttr;                    if (tableAttr != null) {                        break;                    }                }                //若没找到则退出                if (tableAttr == null) {                    structType = null;                    return;                }                //遍历并导入目标类成员的 DataColumnAttr                this.DataColumnAttrList = new List<DataColumnAttr>();                this.PrimaryKeyAttr = null;                foreach (PropertyInfo property in this.structType.GetProperties()) {                    foreach (Attribute attribute in property.GetCustomAttributes(true)) {                        DataColumnAttr attr = attribute as DataColumnAttr;                        if (attr != null) {                            if (attr.PrimaryKey == true) {                                if (this.PrimaryKeyAttr == null) {                                    this.PrimaryKeyAttr = attr;                                } else {                                    //出现重复主键报错                                    throw new Exception();                                }                            }                            attr.Info = property;                            this.DataColumnAttrList.Add(attr);                        }                    }                }            }        }        public string SchemaName {            get {                return this.TableAttr.SchemaName;            }        }        public string TableName {            get {                return this.TableAttr.TableName;            }        }        public string FullTableName {            get {                return string.Format("{0}.{1}", this.SchemaName, this.TableName);            }        }        public string PrimaryKeyName {            get {                return PrimaryKeyAttr.Name;            }        }        public object GetPrimaryKeyValue(object item) {            if (item != null && this.PrimaryKeyAttr != null) {                return this.PrimaryKeyAttr.Info.GetValue(item, null);            }            return null;        }        public SQLCreateTools(Type type) {            this.StructType = type;        }        public string GetValueString(object item, bool hasName = false) {            if (!item.GetType().Equals(this.StructType)) {                //类型不匹配                return null;            }            string result = "(";            for (int i=0; i<this.DataColumnAttrList.Count; i++) {                if (i!=0) {                    result += ", ";                }                if (hasName == true) {                    result += (this.DataColumnAttrList[i].Name + " = ");                }                object val = this.DataColumnAttrList[i].GetValue(item);                result += ("'" + val + "'");            }            result += ")";            return result;        }        //------------------------------------------------------------------------------------        public static string SQLTemplete_CreateTable = "Create Table {0}(\r\n{1})\r\n";        public string SQL_CreateTable() {            string propertyString = "";            foreach (DataColumnAttr attr in this.DataColumnAttrList) {                propertyString += attr.ToPropertyString();            }            return String.Format(SQLTemplete_CreateTable, this.FullTableName,                                             propertyString);        }        //------------------------------------------------------------------------------------        public static string SQLTemplete_InsertRecord = "Insert into {0} values {1}";        public string SQL_InsertRecord(object obj) {            return string.Format(SQLTemplete_InsertRecord, this.FullTableName,                                              this.GetValueString(obj));        }        //------------------------------------------------------------------------------------        public static string SQLTemplete_DeleteRecord = "Delete form {0} where ({1})";        public string SQL_DeleteRecord(string expr) {            return string.Format(SQLTemplete_DeleteRecord, this.FullTableName, expr);        }        public string SQL_DeleteRecord(object obj) {            if (obj.GetType().Equals(this.StructType)) {                string expr = string.Format("{0} = {1}", this.PrimaryKeyName,                                             this.GetPrimaryKeyValue(obj));                return SQL_DeleteRecord(expr);            }        }        //------------------------------------------------------------------------------------        public static string SQLTemplete_SelectRecord = "Select * from {0} {1}";        public string SQL_SelectRecord(string expr) {            return string.Format(SQLTemplete_SelectRecord, this.FullTableName, expr);        }    }}

最后就是实际操作的工具类DatabaseTools

//DatabaseTools.csnamespace DatabaseTools {    public class DatabaseTools {        //这里我虚设了一个connect对象，因为这不在文章的讨论范围内        public static MSSQLConnect DefaultCnnect;        //建表 type-对象类型        public static CreateTable(Type type) {            SQLCreateTools tools = SQLCreateTools.Get(type);            if (tools != null) {                DefaultCnnect.ExecuteNonQuery(tools.SQL_CreateTable());            }        }        //删除项 type-对象类型, expr-表达式        public static void DeleteItem(Type type, string expr) {            SQLCreateTools tools = SQLCreateTools.Get(item.GetType());            if (tools != null) {                DefaultCnnect.Execute(tools.SQL_DeleteRecord(expr));            }        }        //删除项 根据主键 item-要删除的对象        public static void DeleteItem(object item) {            if (item == null) {return;}            SQLCreateTools tools = SQLCreateTools.Get(item.GetType());            if (tools != null) {                DefaultCnnect.Execute(tools.SQL_DeleteRecord(item));            }        }        //保存项 item-要保存的对象, cover-是否覆盖        public static void SaveItem(object item, bool cover=false) {            if (item == null) {return;}            SQLCreateTools tools = SQLCreateTools.Get(item.GetType());            if (tools != null) {                if (cover == true) {                    DeleteItem(item);                }                DefaultCnnect.ExecuteNonQuery(tools.SQL_InsertRecord(item));            }        }        //读取项 type-读取项的类型, expr-表达式        public static object LoadItem(Type type, string expr) {            SQLCreateTools tools = SQLCreateTools.Get(item.GetType());            if (tools != null) {                DefaultCnnect.Execute(tools.SQL_SelectRecord(expr));                List<object> list = ConvertTo(DefaultCnnect.QueryResultDataTable, type, tools);                if (list.Count > 0) {                    return list[0];                }            }            return null;        }        //转换DataTable为指定类型对象列表        private static List<object> ConvertTo(DataTable table, Type itemType,                                                     SQLCreateTools tools = null) {            List<object> resultList = new List<object>();            if (table != null) {                if (table.Rows.Count > 0) {                    if (tools == null) {                        tools = SQLCreateTools.Get(itemType);                    }                    foreach (DataRow row in table.Rows) {                        var item = ConvertTo(row, itemType, tools);                        resultList.Add(item);                    }                }            }            return resultList;        }        //转换DataRow为指定类型对象        private static object ConvertTo(DataRow row, Type type, SQLCreateTools tools = null) {            if (tools == null) {                tools = SQLCreateTools.Get(type);            }            //这个类型转换器也不在文章讨论范围内            object item = TypeHelper.CreateObject(type, null);            if (item != null) {                foreach (DataColumnAttr attr in tools.DataColumnAttrList) {                    object tempVal = row[attr.Name];                    if (attr.Info.PropertyType.Equals(typeof(string))) {                        string val = (tempVal as string).TrimEnd();                        attr.Info.SetValue(item, val, null);                    } else {                        attr.Info.SetValue(item, tempVal, null);                    }                }                return item;            } else {                return null;            }        }    }}

这样工具就完成了，虽然有很多可以改进的地方
实际使用一下

namespace Test_AutoSQL {    static class Program {        [STAThread]        static void Main() {            DatabaseTools.Create(typeof(TestClass));            TestClass obj1 = new TestClass();            obj1.Id = 1;            obj1.Name = "obj1";            obj1.RecordData = DataTime.Now;            DatabaseTools.SaveItem(obj1);            TestClass obj2 = null;            obj2 = DatabaseTools.LoadItem(obj2.GetType(), "ID = 1") as TestClass;        }    }    [DataTableAttr(SchemeName="dbo", TableName="TestClass")]    public class TestClass {        private int id;        [DataColumnAttr(Name="ID", DataType="int", PrimaryKey=true, NotNull=true)]        public int Id {            get {                return id;            }            set {                id = value;            }        }        private string name;        [DataColumnAttr(Name="Name", DataType="varchar(100)")]        public string Name {            get {                return name;            }            set {                name = value;            }        }        private DateTime recordDate;        [DataColumnAttr(Name="RecordDate", DataType="datetime")]        public DateTime RecordDate {            get {                return recordDate;            }            set {                recordDate = value;            }        }    }}