C#设计模式-单例模式以及应用之ADO.NET的简单封装

最新推荐文章于 2024-07-31 10:46:54 发布

string_user_get_set

最新推荐文章于 2024-07-31 10:46:54 发布

阅读量1.3k

点赞数 1

分类专栏： C# 设计模式 ADO.NET

本文链接：https://blog.youkuaiyun.com/string_user_get_set/article/details/80545048

版权

C# 同时被 3 个专栏收录

8 篇文章

订阅专栏

设计模式

1 篇文章

订阅专栏

ADO.NET

1 篇文章

订阅专栏

什么是单例模式：单例模式也就是保证一个类只有一个实例的一种实现方法罢了(设计模式其实就是帮助我们解决实际开发过程中的方法, 该方法是为了降低对象之间的耦合度,然而解决方法有很多种,所以前人就总结了一些常用的解决方法为书籍,从而把这本书就称为设计模式)，下面给出单例模式的一个官方定义：确保一个类只有一个实例,并提供一个全局访问点。

示例代码：

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

namespace Singleton
{
    class Program
    {
        static void Main(string[] args)
        {
            // 单线程单例模式
            SingletonOneTrh singletonOneTrh = SingletonOneTrh.GetInstance();
            singletonOneTrh.WirteAction();

            // 多线程单例模式
            SingletonManyTrh singletonManyTrh1 = SingletonManyTrh.GetInstance(); // 表示线程1
            SingletonManyTrh singletonManyTrh2 = SingletonManyTrh.GetInstance(); // 表示线程2
            singletonManyTrh1.WirteAction();
            singletonManyTrh2.WirteAction();

            Console.ReadLine();
        }
    }


    /// <summary>
    /// 单线程下实现单例模式
    /// </summary>
    public class SingletonOneTrh
    {
        // 定义静态变量来保存类的实例
        private static SingletonOneTrh uniqueInstance;

        // 私有构造函数，无法在类的外部实例化
        private SingletonOneTrh() {}

        /// <summary>
        /// 定义静态公有方法，提供类的全局访问
        /// </summary>
        /// <returns></returns>
        public static SingletonOneTrh GetInstance() {

            if (uniqueInstance == null)
            {
                uniqueInstance = new SingletonOneTrh();
            }
            return uniqueInstance;
        }

        /// <summary>
        /// 类的行为操作
        /// </summary>
        public void WirteAction() {
            Console.WriteLine("-------单线程下单例模式下对象行为--------");
        }

    }


    /// <summary>
    /// 多线程下实现单例模式
    /// </summary>
    public class SingletonManyTrh
    {
        // 定义一个静态变量来保存类的实例
        private static SingletonManyTrh uniqueInstance;

        // 定义一个标识确保线程同步
        private static readonly object locker = new object();
        // 因为在两个线程同时运行GetInstance方法时，此时两个线程判断(uniqueInstance ==null)这个条件时都返回真，
        // 此时两个线程就都会创建Singleton的实例，这样就违背了我们单例模式初衷了，既然上面的实现会运行多个线程执行，
        // 那我们对于多线程的解决方案自然就是使GetInstance方法在同一时间只运行一个线程运行就好了

        // 定义私有构造函数，使外界不能创建该类实例
        private SingletonManyTrh()
        {
        }
        /// <summary>
        /// 定义静态公有方法，提供类的全局访问
        /// </summary>
        /// <returns></returns>
        public static SingletonManyTrh GetInstance()
        {
            // lock锁定代码的时候会产生很大的开销，所以后面的线程在uniqueInstance不为空的情况下就不需要lock了
            if (uniqueInstance == null)
            {
                lock (locker) // 当一个线程进来的时候锁定，其余线程等待。
                {
                    if (uniqueInstance == null)
                    {
                        Thread.Sleep(5 * 1000); // 直观体现后面的线程不会Sleep
                        uniqueInstance = new SingletonManyTrh();
                    }
                }
            }
            return uniqueInstance;
           
        }


        /// <summary>
        /// 类的行为操作
        /// </summary>
        public void WirteAction()
        {
            Console.WriteLine("-------多线程下单例模式下对象行为--------");
        }
    }



}

运行结果：

会发现在线程2的时候没有在进行sleep，也就是没有在实例化对象。下面就对ADO.NET简单封装应用下单例模式

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DataBase.ADO.NET
{
    /// <summary>
    /// ADO.NET的简单增删改查封装
    /// </summary>
    public class SqlServerHelper
    {
        
        private static SqlServerHelper sqlServer;

        // 定义一个标识确定线程同步，排除多线程下实例化多个数据库服务对象而违背单例模式
        private static readonly object locker = new object();

        public static SqlServerHelper open() {

            if (sqlServer == null)
            {
                lock (locker)
                {
                    if (sqlServer == null)
                    {
                        sqlServer = new SqlServerHelper();
                    }
                }
            }
            return sqlServer;
        }

        // 连接数据库字符串和数据库类型，在实际的项目中需要读取web.config
        // private static readonly string default_DBType = "SqlServer";
        private static readonly string default_ConnectionString = "Server=.;Database=TEST;User ID=sa;Password=123456;Pooling=true;min pool size = 2;max pool size=50;Connection Reset=FALSE";

        /// <summary>
        /// 指定查询返回多条数据
        /// </summary>
        /// <param name="sql">sql text</param>
        /// <param name="commandtype">sql类型,默认执行查询sql</param>
        /// <param name="parameters">sql param</param>
        /// <returns></returns>
        public List<T> QueryMany<T>(string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class,new() {

            // ADO操作数据库第一步：实例化SqlConnection对象，打开一个数据库连接
            using (SqlConnection conn = new SqlConnection(default_ConnectionString))
            {
                // 数据库操作类
                using (SqlCommand comm = new SqlCommand(sql,conn))
                {
                    // 设置执行类型，sql or proc 
                    comm.CommandType = commandtype;
                    // 设置参数
                    if (parameters != null)
                    {
                        foreach (SqlParameter item in parameters)
                        {
                            comm.Parameters.Add(item);
                        }
                    }

                    // 声明数据库适配器，通过comm来实例化
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(comm);
                    
                    // 声明一个DataSet用于装载查询结果且将sqlDataAdapter的数据填充(Fill())到DataSet中
                    DataTable dt = new DataTable();
                    sqlDataAdapter.Fill(dt);
                    comm.Parameters.Clear();
                    // 序列化数据
                    List<T> dataList = SerializeData.ToEntities<T>(dt);
                    return dataList;

                }
            }
        }


        /// <summary>
        /// 指定查询返回首行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql text</param>
        /// <param name="parameters">sql类型,默认执行查询sql</param>
        /// <param name="commandtype">sql param</param>
        /// <returns></returns>
        public T QuerySingle<T>(string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class, new()
        {
            var resultList = QueryMany<T>(sql, parameters);
            return resultList.FirstOrDefault<T>();
        }

        /// <summary>
        /// 数据插入操作
        /// </summary>
        /// <param name="sql">sql text</param>
        /// <param name="parameters">sql param</param>
        /// <returns></returns>
        public int Insert(string sql, SqlParameter[] parameters) {
            return ExecuteNonQuery(sql, parameters);
        }

        public int Insert<T>(string sql, SqlParameter[] parameters) where T : class, new() { return 0; }

        /// <summary>
        /// 数据删除操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Delete(string sql, SqlParameter[] parameters) {
            return ExecuteNonQuery(sql, parameters);
        }

        public int Delete<T>(string sql, SqlParameter[] parameters) where T : class, new() { return 0; }

        /// <summary>
        /// 数据库更新操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Update(string sql, SqlParameter[] parameters) {
            return ExecuteNonQuery(sql, parameters);
        }

        public int Update<T>(string sql, SqlParameter[] parameters) where T : class, new() { return 0; }

        /// <summary>
        /// 数据库增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="commandtype"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) {
            using (SqlConnection conn = new SqlConnection(default_ConnectionString))
            {
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    comm.CommandType = commandtype;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            comm.Parameters.Add(parameter);
                        }
                    }
                    conn.Open();
                    int count = comm.ExecuteNonQuery(); // 返回受影响函数
                    comm.Parameters.Clear();
                    conn.Close();

                    return count;
                }
            }
        }

        public int ExecuteNonQuery<T>(string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class,new()
        {
            return 0;
        }

        /// <summary>
        /// 数据库执行事务操作
        /// </summary>
        /// <param name="list">sql集合</param>
        /// <returns></returns>
        public OptionTranResult ExecuteTran(List<SqlCollection> list) {

            using (SqlConnection conn = new SqlConnection(default_ConnectionString))
            {
                // 打开数据库链接
                conn.Open();
                // 开启事务
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    // 实例化执行对象
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.Transaction = tran;

                        int count = 0;
                        int row = 0;
                        foreach (var item in list)
                        {
                            switch (item.sqlType)
                            {
                                case SqlType.Insert:
                                case SqlType.Delete:
                                case SqlType.Update:
                                    comm.CommandType = CommandType.Text;
                                    // sql text
                                    comm.CommandText = item.sqlText;
                                    break;
                                case SqlType.Procedure:
                                    comm.CommandType = CommandType.StoredProcedure;
                                    // sql proc name
                                    comm.CommandText = item.sqlText;
                                    break;
                                default:
                                    comm.Parameters.Clear();
                                    conn.Close();
                                    return new OptionTranResult(false);
                            }
                            // 设置参数
                            if (item.parameters != null)
                            {
                                foreach (SqlParameter parameter in item.parameters)
                                {
                                    comm.Parameters.Add(parameter);
                                }
                            }
                            

                            row = comm.ExecuteNonQuery(); // 返回受影响行数
                            comm.Parameters.Clear();
                            if (row <= 0)
                            {
                                comm.Parameters.Clear();
                                tran.Rollback();
                                conn.Close();
                                return new OptionTranResult(false);
                            }
                            // 累加受影响行数
                            count += row;
                        }
                       
                        tran.Commit();
                        return new OptionTranResult(true,count);

                    }
                }
            }

        }

        /// <summary>
        /// 执行分页存储过程
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<T> ExexuteProc<T>(string procName, ref SqlParameter[] parameters) where T : class, new()
        {
            return QueryMany<T>(procName, parameters,CommandType.StoredProcedure);
        }
        

    }
    
    
    public struct OptionTranResult
    {
        public bool Status;
        public int Count;

        public OptionTranResult(bool status, int count = 0) {
            this.Status = status;
            this.Count = count;
        }
    }

}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataBase.ADO.NET
{
    public class SqlCollection
    {
        public SqlType sqlType { get; set; }
        public string sqlText { get; set; }
        public SqlParameter[] parameters { get; set; }

        public SqlCollection(SqlType sqlType,string sqlText, SqlParameter[] parameters) {
            this.sqlType = sqlType;
            this.sqlText = sqlText;
            this.parameters = parameters;
        }
    }

    public enum SqlType
    {
        Insert = 1,
        Delete = 2,
        Update = 3,
        Procedure = 4
        
    }

}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DataBase.ADO.NET
{
    /// <summary>
    /// 数据序列化
    /// </summary>
    public static class SerializeData
    {

        internal static DataTable ToDataTable<T>(List<T> entities) /*where T : class,new()*/
        {

            // IsNull 
            if (entities == null || entities.Count == 0)
            {
                return null;
            }
            // 反射机制获取 传入的实体类型的全部公有属性
            PropertyInfo[] pArray = typeof(T).GetProperties();

            try
            {
                DataTable dt = new DataTable();

                // Insert Columns
                Array.ForEach(pArray, p => {
                    dt.Columns.Add(p.Name);
                });

                // 遍历集合插入
                entities.ForEach( p => {
                    // Insert Rows
                    DataRow row = dt.NewRow();
                    int i = 0;
                    Array.ForEach(pArray, m => {
                        if (dt.Columns.Contains(m.Name))
                        {
                            row[i] = m.GetValue(p);
                        }
                    });
                    i++;

                    dt.Rows.Add(row);
                });
                return dt;

            }
            catch (Exception)
            {

                throw;
            }
        }

        internal static List<T> ToEntities<T>(DataTable dt) where T : class, new()
        {
            // IsNull
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }

            List<T> entities = new List<T>();

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    PropertyInfo[] pArray = typeof(T).GetProperties();
                    T entity = new T();
                    Array.ForEach(pArray, p =>
                    {
                        if (row[p.Name] != DBNull.Value)
                            p.SetValue(entity, row[p.Name], null);
                    });
                    entities.Add(entity);
                }
                return entities;
            }
            catch (Exception)
            {
                throw;
            }

        }


    }
}

using DataBase.ADO.NET;

namespace DataBase
{
    public class DbFactory
    {
        public static IDatabase CreateDb()
        {
            SqlServerHelper sqlServerHelper = SqlServerHelper.open();
            return new IDatabase(sqlServerHelper);
        }
    }

    public struct IDatabase
    {
        public SqlServerHelper Context { get; set; }
        public IDatabase(SqlServerHelper context) {
            this.Context = context;
        }

    }

}

运行工程

using DataBase;
using DataBase.ADO.NET;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            IDatabase db = DbFactory.CreateDb();
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@DrivingType","C1")
            };
            // 多条数据查询
            List<Student> list = db.Context.QueryMany<Student>("select * from T_Student where DrivingType = @DrivingType", parameters);
            // 查询第一条满足条件的
            Student listFrist = db.Context.QuerySingle<Student>("select * from T_Student where DrivingType = @DrivingType", parameters);

            // 事务执行
            List<SqlCollection> sqlList = new List<SqlCollection>() {
                new SqlCollection(SqlType.Insert,@"Insert into T_Student values(newId(),@IdCard,@Name,@Phone,@Type,@DriId)",new SqlParameter[] {
                    new SqlParameter("IdCard","420222199506225322"),
                    new SqlParameter("Name","zhangwenchao"),
                    new SqlParameter("Phone","15871492760"),
                    new SqlParameter("Type","C1"),
                    new SqlParameter("DriId","c10cdcc1-4baf-423c-bfc0-deb2b9c06fa3")
                }),
                new SqlCollection(SqlType.Update,"Update T_Student set StuName = @Name where Id = @Id",new SqlParameter[] {
                    new SqlParameter("Name","zhangwenchao"),
                    new SqlParameter("Id","7d24a585-488e-4c08-b3e2-4edbc1f51290")
                })
            };
            OptionTranResult result = db.Context.ExecuteTran(sqlList);

            



        }
    }

    public class Student
    {
        public Guid Id { get; set; }
        public string IDcard { get; set; }
        public string StuName { get; set; }
        public string StuTellPhone { get; set; }
        public string DrivingType { get; set; }
        public Guid DrivingName { get; set; }
    }

}