CRUD实体(2)

Demo.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;

using Common;

namespace WinPrint
{
    public partial class FrmUser : BaseForm
    {
        private string connectionString = "server=test;uid=sa;pwd=test;database=test";

        public FrmUser()
        {
            InitializeComponent();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                User user = new User();
                /* 1.Retrieve
                DataSet ds = Retrieve(user);
                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        WriteLog(row[1].ToString());
                    }
                }*/
                /* 2.Create
                user.Id = 100;
                user.Name = "test";
                user.IsAdmin = false;
                user.Salary = 8000m;
                user.Birthday = DateTime.Now;
                Create(user);*/
                /* 3.Update
                user.Id = 100;
                user.Name = "admin";
                user.IsAdmin = true;
                user.Salary = 80000m;
                user.Birthday = DateTime.Now.AddYears(-28);
                Update(user);*/
                user.Id = 100;
                Deleted(user);
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message);
            }
        }

        private string GetTableName(Type objType)
        {
            TableAttribute att = (TableAttribute)Attribute.GetCustomAttribute(objType, typeof(TableAttribute));
            if (att != null)
                return att.Name;
            else
                return objType.Name;
        }

        private bool IsDefault(Type type, object value)
        {
            if (value != null && type == typeof(bool) && ((bool)value == false))
                return true;
            else if (value != null && type == typeof(int) && ((int)value == 0))
                return true;
            else if (value != null && type == typeof(decimal) && ((decimal)value == 0))
                return true;
            else if (value != null && type == typeof(DateTime) && ((DateTime)value == DateTime.MinValue))
                return true;
            else if (value != null && type == typeof(string) && !string.IsNullOrEmpty(value.ToString()))
                return false;
            else if (value != null)
                return false;
            else
                return true;
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="user"></param>
        private void Create(User user)
        {
            Type objType = user.GetType();
            string tableName = GetTableName(objType);
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            sb1.Append("INSERT INTO [" + tableName + "](");
            sb2.Append("VALUES(");

            IList<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pro in objType.GetProperties())
            {
                string proName = pro.Name;
                MethodInfo minfo = objType.GetMethod("get_" + proName);
                object value = minfo.Invoke(user, new object[] { });
                ColumnAttribute catt = (ColumnAttribute)Attribute.GetCustomAttribute(pro, typeof(ColumnAttribute));
                // 确定实体的属性既有被ColumnAttribute也有值
                if (catt != null && !IsDefault(pro.PropertyType, value))
                {
                    if (sb1[sb1.Length - 1] == '(')
                    {
                        sb1.Append("[" + catt.Name + "]");
                        sb2.Append("@" + catt.Name);
                    }
                    else
                    {
                        sb1.Append(", [" + catt.Name + "]");
                        sb2.Append(", @" + catt.Name);
                    }
                    SqlParameter param = new SqlParameter("@" + catt.Name, value);
                    parameters.Add(param);
                }
            }
            sb1.Append(")");
            sb2.Append(")");
            sb1.Append(" " + sb2.ToString());

            WriteLog(sb1.ToString());
            SqlCommand command = new SqlCommand(sb1.ToString());
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
            ExecuteNonQuery(command);
        }

        /// <summary>
        /// 检索
        /// </summary>
        /// <param name="user"></param>
        private DataSet Retrieve(User user)
        {
            string columns = "*";
            Type objType = user.GetType();
            string tableName = GetTableName(objType);
            StringBuilder sb1 = new StringBuilder();
            string head = string.Format("SELECT {0} FROM [{1}]", columns, tableName);
            sb1.Append(head);

            IList<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pro in objType.GetProperties())
            {
                string proName = pro.Name;
                MethodInfo minfo = objType.GetMethod("get_" + proName);
                object value = minfo.Invoke(user, new object[] { });
                ColumnAttribute catt = (ColumnAttribute)Attribute.GetCustomAttribute(pro, typeof(ColumnAttribute));
                // 确定实体的属性既有被ColumnAttribute也有值
                if (catt != null && !IsDefault(pro.PropertyType, value))
                {
                    if (sb1.ToString() == head)
                    {
                        sb1.Append(" WHERE [" + catt.Name + "] = @" + catt.Name);
                    }
                    else
                    {
                        sb1.Append(" AND [" + catt.Name + "] = @" + catt.Name);
                    }
                    SqlParameter param = new SqlParameter("@" + catt.Name, value);
                    parameters.Add(param);
                }
            }

            WriteLog(sb1.ToString());
            SqlCommand command = new SqlCommand(sb1.ToString());
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
            return ExecuteDataSet(command);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="user"></param>
        private void Update(User user)
        {
            Type objType = user.GetType();
            string tableName = GetTableName(objType);
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            string head = "UPDATE [" + tableName + "] SET ";
            sb1.Append(head);

            TableAttribute att = (TableAttribute)Attribute.GetCustomAttribute(objType, typeof(TableAttribute));
            string pkeys = "";
            if (att != null)
                pkeys = att.PrimaryKeys;

            IList<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pro in objType.GetProperties())
            {
                string proName = pro.Name;
                MethodInfo minfo = objType.GetMethod("get_" + proName);
                object value = minfo.Invoke(user, new object[] { });
                ColumnAttribute catt = (ColumnAttribute)Attribute.GetCustomAttribute(pro, typeof(ColumnAttribute));
                // 确定实体的属性既有被ColumnAttribute也有值
                if (catt != null && !IsDefault(pro.PropertyType, value))
                {
                    string baseName = catt.Name;
                    if (pkeys.IndexOf(proName) != -1)
                    {
                        if (sb2.Length == 0)
                        {
                            sb2.Append(" WHERE [" + baseName + "] = @" + baseName);
                        }
                        else
                        {
                            sb2.Append(" AND [" + baseName + "] = @" + baseName);
                        }
                    }
                    else
                    {
                        if (sb1.ToString() == head)
                        {
                            sb1.Append("[" + baseName + "] = " + "@" + baseName);
                        }
                        else
                        {
                            sb1.Append(", [" + baseName + "] = " + "@" + baseName);
                        }
                    }
                    SqlParameter param = new SqlParameter("@" + catt.Name, value);
                    parameters.Add(param);
                }
            }
            sb1.Append(sb2.ToString());

            WriteLog(sb1.ToString());
            SqlCommand command = new SqlCommand(sb1.ToString());
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
            ExecuteNonQuery(command);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="user"></param>
        private void Deleted(User user)
        {
            Type objType = user.GetType();
            string tableName = GetTableName(objType);
            StringBuilder sb1 = new StringBuilder();
            string head = string.Format("DELETE FROM [{0}]", tableName);
            sb1.Append(head);

            IList<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pro in objType.GetProperties())
            {
                string proName = pro.Name;
                MethodInfo minfo = objType.GetMethod("get_" + proName);
                object value = minfo.Invoke(user, new object[] { });
                ColumnAttribute catt = (ColumnAttribute)Attribute.GetCustomAttribute(pro, typeof(ColumnAttribute));
                // 确定实体的属性既有被ColumnAttribute也有值
                if (catt != null && !IsDefault(pro.PropertyType, value))
                {
                    if (sb1.ToString() == head)
                    {
                        sb1.Append(" WHERE [" + catt.Name + "] = @" + catt.Name);
                    }
                    else
                    {
                        sb1.Append(" AND [" + catt.Name + "] = @" + catt.Name);
                    }
                    SqlParameter param = new SqlParameter("@" + catt.Name, value);
                    parameters.Add(param);
                }
            }

            WriteLog(sb1.ToString());
            SqlCommand command = new SqlCommand(sb1.ToString());
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
            ExecuteNonQuery(command);
        }

        #region ExecuteNonQuery & ExecuteDataSet

        protected int ExecuteNonQuery(SqlCommand command)
        {
            int rtn = 0;
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                command.Connection = connection;
                rtn = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return rtn;
        }

        protected DataSet ExecuteDataSet(SqlCommand command)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            DataSet ds;
            try
            {
                command.Connection = connection;
                SqlDataAdapter da = new SqlDataAdapter(command);
                ds = new DataSet();
                da.Fill(ds);
                command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return ds;
        }

        #endregion
    }
}

大家有什么好的方法可以一起讨论。