none
自己写的MVC框架,可能存在很多问题或者错误希望各位高手们指出! RRS feed

  • 问题






  • //SWSoft.MVC.Model.IConnector<TConnection, TCommand, TDataReader, TParameter> 提供数据操作基本方法
    
    
    //SWSoft.MVC.Model.DBConnector<TParameter> 提供数据库操作的通用方法
    
    
    //SWSoft.MVC.Model.SqlServer<T> 实现了对SqlServer数据库的访问
    
    
    //我想请各位前辈们指出存在的问题和错误,好让我优化这些代码继续实现对MySql、ODBC等数据访问方式的支持
    
    
    //目前有一个用ASP.NET连接MySql的需求,MySQl版本3.23.42,我尝试用MySql 的ODBC 3.51驱动连接数据库成功了,我想问问还有没其它更好的连接方式,比如Connector/NET
    
    
    
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Configuration;
    
    using System.Data;
    
    using System.Reflection;
    
    
    
    namespace 
    
    {
    
        /// <summary>
    
        /// 泛型化数据库操作方法接口
    
        /// </summary>
    
        /// <typeparam name="TConnection">数据库连接对象</typeparam>
    
        /// <typeparam name="TCommand">数据库执行对象</typeparam>
    
        /// <typeparam name="TDataReader">数据库读取对象</typeparam>
    
        public interface IConnector<TConnection, TCommand, TDataReader, TParameter>
    
        {
    
            #region 数据库连接对象
    
    
    
            /// <summary>
    
            /// 创建一个新的数据库连接对象
    
            /// </summary>
    
            TConnection Connection { get; }
    
    
    
            #endregion
    
    
    
            #region 创建T-SQL语句执行对象
    
    
    
            /// <summary>
    
            /// 创建执行 Transact-SQL 语句的对象。
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            TCommand CreateCommand(string procedureName);
    
    
    
            /// <summary>
    
            /// 创建执行的 Transact-SQL 语句的对象。
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <param name="Params">参数列表</param>
    
            TCommand CreateCommand(string procedureName, Dictionary<String, Object> Params);
    
    
    
            #endregion
    
    
    
            #region ExecuteReader 执行存储过程并返回结果集
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="command">执行 Transact-SQL 语句的对象。</param>
    
            TDataReader ExecuteReader(TCommand command);
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            TDataReader ExecuteReader(string procedureName);
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            /// <param name="Params">参数列表</param>
    
            TDataReader ExecuteReader(string procedureName, Dictionary<String, Object> Params);
    
    
    
            #endregion
    
    
    
            #region ExecuteNonQuery 执行存储过程并返回受影响行数
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="Command">Transact-SQL 语句的对象</param>
    
            bool ExecuteNonQuery(TCommand Command);
    
    
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            bool ExecuteNonQuery(string procedureName);
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="procedureName">将要执行的存储过程名称</param>
    
            /// <param name="Params">参数列表</param>
    
            bool ExecuteNonQuery(string procedureName, Dictionary<String, Object> Params);
    
    
    
            #endregion
    
    
    
            #region ExecuteTable
    
    
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="command">T-SQL 语句执行对象</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            DataTable ExecuteTable(TCommand command);
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            DataTable ExecuteTable(string procedureName);
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <param name="paramters">需要的参数列表</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            DataTable ExecuteTable(string procedureName, Dictionary<string, object> Params);
    
    
    
            #endregion
    
    
    
            #region ExecuteScalar 返回第一行第一列
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回第一行第一列
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            object ExecuteScalar(string procedureName);
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回第一行第一列
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            /// <param name="Params">参数值</param>
    
            object ExecuteScalar(string procedureName, Dictionary<String, Object> Params);
    
    
    
            #endregion
    
        }
    
    
    
        /// <summary>
    
        /// 提供通用的公共方法和属性
    
        /// </summary>
    
        public class DBConnector<TParameter>
    
        {
    
            /// <summary>
    
            /// 数据库连接字符串
    
            /// </summary>
    
            protected readonly static string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    
    
    
            /// <summary>
    
            /// 操作是否执行成功
    
            /// </summary>
    
            /// <param name="result">执行结果</param>
    
            protected bool Success(int result)
    
            {
    
                return result > 0 ? true : false;
    
            }
    
    
    
            /// <summary>
    
            /// 检测属性是否为空
    
            /// </summary>
    
            /// <param name="property">属性对象</param>
    
            /// <param name="value">属性的值</param>
    
            protected object IsNull(PropertyInfo property, object value)
    
            {
    
                switch (property.PropertyType.FullName)
    
                {
    
                    case "System.DateTime":
    
                        if (value.Equals(DBNull.Value))
    
                        {
    
                            return DateTime.MinValue;
    
                        }
    
                        else
    
                        {
    
                            return value.Equals(DateTime.MinValue) ? DBNull.Value : value;
    
                        }
    
                    case "System.String": return value == null ? string.Empty : value;
    
                    default: return value;
    
                }
    
            }
    
    
    
            /// <summary>
    
            /// 构造参数列表
    
            /// </summary>
    
            ///<param name="Params">键和值的列表</param>
    
            protected TParameter[] CreateParameters(Dictionary<string, object> Params)
    
            {
    
                List<TParameter> list = new List<TParameter>();
    
                foreach (var item in Params)
    
                {
    
                    TParameter param = Activator.CreateInstance<TParameter>();
    
                    SetValue(param, "ParameterName", item.Key);
    
                    SetValue(param, "Value", item.Value);
    
                    list.Add(param);
    
                }
    
                return list.ToArray();
    
            }
    
    
    
            #region 对象属性映射
    
    
    
            /// <summary>
    
            /// 设置对象属性的值
    
            /// </summary>
    
            /// <param name="obj">将设置属性值的对象</param>
    
            /// <param name="name">将设置属性的名称</param>
    
            /// <param name="value">词属性的新值</param>
    
            protected void SetValue(object obj, string name, object value)
    
            {
    
                obj.GetType().GetProperty(name).SetValue(obj, value, null);
    
            }
    
    
    
            /// <summary>
    
            /// 设置对象属性的值
    
            /// </summary>
    
            /// <param name="obj">将设置属性值的对象</param>
    
            /// <param name="name">将设置属性的名称</param>
    
            /// <param name="value">词属性的新值</param>        
    
            /// <param name="index">索引</param>
    
            protected void SetValue(object obj, string name, object value, object[] index)
    
            {
    
                obj.GetType().GetProperty(name).SetValue(obj, value, index);
    
            }
    
    
    
            #endregion
    
        }
    
    }
    
    <pre lang="x-c#">using System;
    
    using System.Collections.Generic;
    
    using System.Configuration;
    
    using System.Data;
    
    using System.Data.SqlClient;
    
    using System.Reflection;
    
    
    
    namespace SWSoft.MVC.Model
    
    {
    
        /// <summary>
    
        /// SqlServer数据库访问类
    
        /// </summary>
    
        public partial class SqlServer<T> : DBConnector<SqlParameter>, IConnector<SqlConnection, SqlCommand, SqlDataReader, SqlParameter>
    
        {
    
            #region 数据库连接对象
    
    
    
            /// <summary>
    
            /// 数据库连接字符串
    
            /// </summary>
    
            public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    
    
    
            /// <summary>
    
            /// 一个新的数据库连接对象
    
            /// </summary>
    
            public SqlConnection Connection { get { return new SqlConnection(ConnectionString); } }
    
    
    
            #endregion
    
    
    
            #region SqlCommand 创建T-SQL语句执行对象
    
    
    
            /// <summary>
    
            /// 创建执行 Transact-SQL 语句的对象。
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            public SqlCommand CreateCommand(string procedureName)
    
            {
    
                SqlCommand comm = new SqlCommand { Connection = Connection, CommandType = CommandType.StoredProcedure, CommandText = procedureName };
    
                comm.Connection.Open();
    
                return comm;
    
            }
    
    
    
            /// <summary>
    
            /// 创建执行的 Transact-SQL 语句的对象。
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <param name="Params">参数列表</param>
    
            public SqlCommand CreateCommand(string procedureName, Dictionary<String, Object> Params)
    
            {
    
                SqlCommand comm = CreateCommand(procedureName);
    
                comm.Parameters.AddRange(CreateParameters(Params));
    
                return comm;
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteReader 执行存储过程并返回结果集
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="command">执行 Transact-SQL 语句的对象。</param>
    
            public SqlDataReader ExecuteReader(SqlCommand command)
    
            {
    
                return command.ExecuteReader(CommandBehavior.CloseConnection);
    
            }
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            public SqlDataReader ExecuteReader(string procedureName)
    
            {
    
                return ExecuteReader(CreateCommand(procedureName));
    
            }
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回结果集
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            /// <param name="Params">参数列表</param>
    
            public SqlDataReader ExecuteReader(string procedureName, Dictionary<String, Object> Params)
    
            {
    
                return ExecuteReader(CreateCommand(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteTable
    
    
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="command">T-SQL 语句执行对象</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            public DataTable ExecuteTable(SqlCommand command)
    
            {
    
                SqlDataAdapter adapter = new SqlDataAdapter(command);
    
                DataTable table = new DataTable();
    
                adapter.Fill(table);
    
                return table;
    
            }
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            public DataTable ExecuteTable(string procedureName)
    
            {
    
                return ExecuteTable(CreateCommand(procedureName));
    
            }
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <param name="Params">需要的参数列表</param>
    
            /// <returns>内存中数据的一个表</returns>
    
            public DataTable ExecuteTable(string procedureName, Dictionary<string, object> Params)
    
            {
    
                return ExecuteTable(CreateCommand(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteNonQuery 执行存储过程并返回受影响行数
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="Command">Transact-SQL 语句的对象</param>
    
            public bool ExecuteNonQuery(SqlCommand Command)
    
            {
    
                return Success(Command.ExecuteNonQuery());
    
            }
    
    
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            public bool ExecuteNonQuery(string procedureName)
    
            {
    
                return ExecuteNonQuery(CreateCommand(procedureName));
    
            }
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回操作是否成功执行
    
            /// </summary>
    
            /// <param name="procedureName">将要执行的存储过程名称</param>
    
            /// <param name="Params">参数列表</param>
    
            public bool ExecuteNonQuery(string procedureName, Dictionary<String, Object> Params)
    
            {
    
                return ExecuteNonQuery(CreateCommand(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteScalar 返回第一行第一列
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回第一行第一列
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            public object ExecuteScalar(string procedureName)
    
            {
    
                return CreateCommand(procedureName).ExecuteScalar();
    
            }
    
    
    
            /// <summary>
    
            /// 执行存储过程并返回第一行第一列
    
            /// </summary>
    
            /// <param name="procedureName">存储过程名称</param>
    
            /// <param name="Params">参数值</param>
    
            public object ExecuteScalar(string procedureName, Dictionary<String, Object> Params)
    
            {
    
                return CreateCommand(procedureName, Params).ExecuteScalar();
    
            }
    
    
    
            #endregion
    
        }
    
    }
    
    
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Data.SqlClient;
    
    
    
    namespace SWSoft.MVC.Model
    
    {
    
        /// <summary>
    
        /// 数据库访问器,提供数据访问的基本方法和对象关系映射
    
        /// </summary>
    
        /// <typeparam name="T">对象类型</typeparam>
    
        public partial class SqlServer<T>
    
        {
    
            /// <summary>
    
            /// 提供存储过程的参数列表
    
            /// </summary>
    
            protected Dictionary<String, Object> Params { get; set; }
    
            /// <summary>
    
            /// 默认构造方法
    
            /// </summary>
    
            public SqlServer()
    
            {
    
                Params = new Dictionary<string, object>();
    
            }
    
    
    
            /// <summary>
    
            /// 获得参数的键值对集合
    
            /// </summary>
    
            /// <param name="model">包含参数的实体对象</param>
    
            protected Dictionary<string, object> GetParameters(T model)
    
            {
    
                Dictionary<string, object> Params = new Dictionary<string, object>();
    
                foreach (var item in model.GetType().GetProperties())
    
                {
    
                    Params.Add("@" + item.Name, item.GetValue(item, null));
    
                }
    
                return Params;
    
            }
    
    
    
            /// <summary>
    
            /// 封装为实体对象
    
            /// </summary>
    
            /// <param name="reader">数据库读取器</param>
    
            /// <returns>实体对象的泛型集合</returns>
    
            protected List<T> ToModels(SqlDataReader reader)
    
            {
    
                List<T> list = new List<T>();
    
                while (reader.Read())
    
                {
    
                    T t = Activator.CreateInstance<T>();
    
                    foreach (var item in t.GetType().GetProperties())
    
                    {
    
                        SetValue(t, item.Name, IsNull(item, reader[item.Name]));
    
                    }
    
                    list.Add(t);
    
                }
    
                return list;
    
            }
    
    
    
    
    
    
    
            /// <summary>
    
            /// 封装为实体对象
    
            /// </summary>
    
            /// <param name="reader">数据库读取器</param>
    
            /// <returns>单个实体对象</returns>
    
            protected T ToModel(SqlDataReader reader)
    
            {
    
                List<T> list = ToModels(reader);
    
                return list != null && list.Count > 0 ? list[0] : default(T);
    
            }
    
    
    
            #region Execute
    
    
    
            /// <summary>
    
            /// 执行一个存储过程,返回一个实体对象
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>实体对象集合</returns>
    
            protected List<T> Execute(string procedureName)
    
            {
    
                return ToModels(ExecuteReader(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteModel
    
    
    
            /// <summary>
    
            /// 执行一个存储过程,返回一个实体对象
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>实体对象</returns>
    
            protected T ExecuteModel(string procedureName)
    
            {
    
                return ToModel(ExecuteReader(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteModels
    
    
    
            /// <summary>
    
            /// 执行一个存储过程,返回一个实体对象集合
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>实体对象</returns>
    
            protected List<T> ExecuteModels(string procedureName)
    
            {
    
                return ToModels(ExecuteReader(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExcuteInt32
    
    
    
            /// <summary>
    
            /// 执行一个存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>第一行第一列</returns>
    
            protected int ExecuteInt32(string procedureName)
    
            {
    
                return Convert.ToInt32(ExecuteScalar(procedureName, Params));
    
            }
    
    
    
            #endregion
    
    
    
            #region ExecuteBool
    
    
    
            /// <summary>
    
            /// 执行存储过程
    
            /// </summary>
    
            /// <param name="procedureName">存储过程的名称</param>
    
            /// <returns>是否执行成功</returns>
    
            protected bool ExecuteBool(string procedureName)
    
            {
    
                return ExecuteNonQuery(procedureName, Params);
    
            }
    
    
    
            #endregion
    
        }
    
    }
    
    
    2010年4月13日 8:03

答案

全部回复