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

最新推荐文章于 2022-10-20 08:00:15 发布

技术员puc

最新推荐文章于 2022-10-20 08:00:15 发布

阅读量673

点赞数

文章标签：数据库 sql c#

本文链接：https://blog.csdn.net/photonaaa/article/details/77512620

版权

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.cs
namespace 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.cs
namespace 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.cs
namespace 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.cs
namespace 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;
            }
        }
    }
}