数据库与类的连接

来源：互联网发布：java面试宝典2016下载编辑：程序博客网时间：2024/05/16 14:06

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EntityToSQLNS
{
    public enum SQLType
    {
        Insert,
        Delete,
        Update,
        Select
    }
    public interface IEntity
    {
    }
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = true)]
    public class DTableAttribute : Attribute
    {
        public string Name
        {
            get;
            set;
        }
    }
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class DFiledAttribute : Attribute
    {
        public string Name
        {
            get;
            set;
        }
        public Type FieldType
        {
            get;
            set;
        }
        public bool Key
        {
            get;
            set;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using EntityToSQLNS;

namespace SQLCreate
{
    enum SQLType
    {
        Insert,
        Delete,
        Update,
        Select
    }
    class Program
    {
        static void Main(string[] args)
        {
            User user = new User();
            user.Number = 1;
            user.StuNumber = "1234567890";
            user.PassWord = "123456";

            Ann ann = new Ann();
            ann.Number = 3;
            ann.Title = "通知";
            ann.Content = "不放假";

GetSQL gsql = new GetSQL();
Console.WriteLine(gsql.CreateSQL(ann, SQLType.Select));

            GetSQL<IEntity> gsqlg = new GetSQL<IEntity>();
            Console.WriteLine(gsqlg.CreateSQL(user, SQLType.Insert));
        }
    }
    class GetSQL
    {
        public string CreateSQL(IEntity entity, SQLType sqlType)
        {
            string SQL = "";
            switch (sqlType)
            {
                case SQLType.Select:
                    SQL = SelectSQL(entity);
                    break;
                case SQLType.Insert:
                    SQL = InsertSQL(entity);
                    break;
                case SQLType.Delete:
                    SQL = DeleteSQL(entity);
                    break;
                case SQLType.Update:
                    SQL = UpdateSQL(entity);
                    break;
            }
            return SQL;
        }
        string InsertSQL(IEntity entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();
            string values = "";
            foreach (PropertyInfo pi in ProArr)
            {
                values += ((DFiledAttribute)(pi.GetCustomAttributes(false)[0])).Name + "='" + pi.GetValue(entity, null) + "',";
            }
            values = values.TrimEnd(',');
            return string.Format("insert into {0} values({1})", TableName, values);
        }
        string SelectSQL(IEntity entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

            string values = "";
            foreach (PropertyInfo pi in ProArr)
            {
                DFiledAttribute DF = ((DFiledAttribute)(pi.GetCustomAttributes(false)[0]));
                values += DF.Name + ",";

            }
            values = values.TrimEnd(',');
            return string.Format("select {0} from {1}", values, TableName);
        }
        string DeleteSQL(IEntity entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

            string condition = "";
            foreach (PropertyInfo pi in ProArr)
            {
                DFiledAttribute DF = ((DFiledAttribute)(pi.GetCustomAttributes(false)[0]));
                if (DF.Key)
                {
                    condition = " where " + DF.Name + "='" + pi.GetValue(entity, null) + "'";
                }
            }
            return string.Format("delete {0} {1} ", TableName, condition);
        }
        string UpdateSQL(IEntity entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

            string values = "";
            foreach (PropertyInfo pi in ProArr)
            {
                DFiledAttribute DF = ((DFiledAttribute)(pi.GetCustomAttributes(false)[0]));
                values += DF.Name + "='" + pi.GetValue(entity, null) + "',";
            }
            values = values.TrimEnd(',');
            return string.Format("update {0} set {1}", TableName, values);
        }
    }

    class GetSQL<T>
    {
        public string CreateSQL(T entity, SQLType sqlType)
        {
            string SQL = "";
            switch (sqlType)
            {
                case SQLType.Select:
                    SQL = SelectSQL(entity);
                    break;
                case SQLType.Insert:
                    SQL = InsertSQL(entity);
                    break;
                case SQLType.Delete:
                    SQL = DeleteSQL(entity);
                    break;
                case SQLType.Update:
                    SQL = UpdateSQL(entity);
                    break;
            }
            return SQL;
        }
        string InsertSQL(T entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();
            string values = "";
            foreach (PropertyInfo pi in ProArr)
            {
                values += ((DFiledAttribute)(pi.GetCustomAttributes(false)[0])).Name + "='" + pi.GetValue(entity, null) + "',";
            }
            values = values.TrimEnd(',');
            return string.Format("insert into {0} values({1})", TableName, values);
        }
        string SelectSQL(T entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

            }
            values = values.TrimEnd(',');
            return string.Format("select {0} from {1}", values, TableName);
        }
        string DeleteSQL(T entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

            string condition = "";
            foreach (PropertyInfo pi in ProArr)
            {
                DFiledAttribute DF = ((DFiledAttribute)(pi.GetCustomAttributes(false)[0]));
                if (DF.Key)
                {
                    condition = " where " + DF.Name + "='" + pi.GetValue(entity, null) + "'";
                }
            }
            return string.Format("delete {0} {1} ", TableName, condition);
        }
        string UpdateSQL(T entity)
        {
            Type TableType = entity.GetType();
            string TableName = ((DTableAttribute)(TableType.GetCustomAttributes(false)[0])).Name;
            PropertyInfo[] ProArr = TableType.GetProperties();

    interface IEntity
    {
    }
    [DTable(Name = "StuUsers")]
    class User : IEntity
    {
        [DFiled(Name = "ID", FieldType = typeof(int), Key = true)]
        public int Number
        {
            get;
            set;
        }
        [DFiled(Name = "StuNum", FieldType = typeof(string), Key = false)]
        public string StuNumber
        {
            get;
            set;
        }
        [DFiled(Name = "Passwd", FieldType = typeof(string), Key = false)]
        public string PassWord
        {
            get;
            set;
        }

    }
    [DTable(Name = "Announcement")]
    class Ann : IEntity
    {
        [DFiled(Name = "ID", FieldType = typeof(int), Key = true)]
        public int Number
        {
            get;
            set;
        }
        [DFiled(Name = "PubTitle", FieldType = typeof(string), Key = false)]
        public string Title
        {
            get;
            set;
        }
        [DFiled(Name = "PubCon", FieldType = typeof(string), Key = false)]
        public string Content
        {
            get;
            set;
        }
    }
}