JLH/JLHHJSvr/LJFrameWork/LJLib.SQLEX/SqlConnectionStringEx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Security.Cryptography;

namespace LJLib.SQLEX
{
    internal static class SqlConnectionStringEx
    {
        public static SqlConnection NewSqlConnection(this string connectionString, bool pooling = false)
        {
            var builder = new SqlConnectionStringBuilder(connectionString);
            builder.Pooling = pooling;
            builder.PersistSecurityInfo = true;
            if (pooling)
            {
                builder.MaxPoolSize = 200;
                builder.MinPoolSize = 20;
                if (builder.ConnectTimeout < 30)
                {
                    builder.ConnectTimeout = 30;
                }
            }
            var rslt = new SqlConnection(builder.ConnectionString);
            rslt.StateChange += (sender, args) =>
            {
                if (args.OriginalState == ConnectionState.Closed && args.CurrentState == ConnectionState.Open)
                {
                    var cmd = (sender as SqlConnection).CreateCommand();
                    cmd.CommandText = "set transaction isolation level READ UNCOMMITTED";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SET DEADLOCK_PRIORITY low";
                    cmd.ExecuteNonQuery();
                }
            };
            return rslt;
        }
        public static SqlConnection NewSqlConnectionReadCommitted(this string connectionString, bool pooling = false)
        {
            var builder = new SqlConnectionStringBuilder(connectionString);
            builder.Pooling = pooling;
            builder.PersistSecurityInfo = true;
            var rslt = new SqlConnection(builder.ConnectionString);
            rslt.StateChange += (sender, args) =>
            {
                if (args.OriginalState == ConnectionState.Closed && args.CurrentState == ConnectionState.Open)
                {
                    var cmd = (sender as SqlConnection).CreateCommand();
                    cmd.CommandText = "set transaction isolation level READ COMMITTED";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SET DEADLOCK_PRIORITY low";
                    cmd.ExecuteNonQuery();
                }
            };
            return rslt;
        }
    }
}