C#-继承IDbConnection连接不同数据库,通用的DbHelper（2）

作者：我家小花儿 | 2024-04-07 09:43:59

踩

idbconnection

在《C#-继承IDbConnection连接不同数据库,通用的DbHelper（1）》之后，感觉和原来的DbHelper使用方法不同，为了采用统一的parameter方法，进行了以下修改。

增加以下类

1. Parameters.cs  # 带参数的parameter
2. PubConstant.cs  # 
1
2

1. Parameters.cs # 带参数的parameter

 public struct Parameters
    {
        public string ParamName;
        public object ParamValue;
        public ParameterDirection ParamDirection;

        public Parameters(string Name, object Value, ParameterDirection Direction)
        {
            ParamName = Name;
            ParamValue = Value;
            ParamDirection = Direction;
        }

        public Parameters(string Name, object Value)
        {
            ParamName = Name;
            ParamValue = Value;
            ParamDirection = ParameterDirection.Input;
        }
    }      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2. PubConstant.cs # 数据库常量

/// <summary>
    /// 公共常量
    /// </summary>
    public class PubConstant
    {
        /// <summary>
        /// 获取数据库类型
        /// </summary>
        public static string DbProvider
        {
            get { return ConfigurationManager.AppSettings["DbProvider"]; }
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
                if (ConStringEncrypt == "true")
                {
                    //_connectionString = DESEncrypt.Decrypt(_connectionString);
                    _connectionString = DESEncrypt.Encrypt(_connectionString);
                }
                return _connectionString;
            }
        }
        /// <summary>
        /// 得到web.config里配置项的数据库连接字符串。
        /// </summary>
        /// <param name="configName"></param>
        /// <returns></returns>
        public static string GetConnectionString(string configName)
        {
            string connectionString = ConfigurationManager.AppSettings[configName];
            string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
            if (ConStringEncrypt == "true")
            {
                connectionString = DESEncrypt.Decrypt(connectionString);
            }
            return connectionString;
        }

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3. DESEncrypt.cs # 加密类

/// <summary>
    /// DES加密/解密类。
    /// </summary>
    public class DESEncrypt
    {
        private static string encryptKey = "litianping";
        public DESEncrypt() { }

        #region ========加密========

        /// <summary>
        /// 加密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Encrypt(string Text)
        {
            return Encrypt(Text, encryptKey);
        }
        /// <summary> 
        /// 加密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string Encrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray;
            inputByteArray = Encoding.Default.GetBytes(Text);
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            StringBuilder ret = new StringBuilder();
            foreach (byte b in ms.ToArray())
            {
                ret.AppendFormat("{0:X2}", b);
            }
            return ret.ToString();
        }

        #endregion

        #region ========解密========

        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Decrypt(string Text)
        {
            return Decrypt(Text, encryptKey);
        }
        /// <summary> 
        /// 解密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string Decrypt(string Text, string sKey)
        {
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            int len;
            len = Text.Length / 2;
            byte[] inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
                i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }

        #endregion

        #region ========MD5 32 位 ========
        /// <summary>
        /// MD5 32 位 
        /// </summary>
        /// <param name="convertString"></param>
        /// <returns></returns>
        public static String Encrypt32(String convertString)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] bytes = System.Text.Encoding.UTF8.GetBytes(convertString);
            bytes = md5.ComputeHash(bytes);
            md5.Clear();
            string ret = "";
            for (int i = 0; i < bytes.Length; i++)
            {
                ret += Convert.ToString(bytes[i], 16).PadLeft(2, '0');
            }
            return ret.PadLeft(32, '0').ToLower();
        }
        #endregion

        #region ========MD5 16 位  ========
        /// <summary>
        /// MD5 16 位  
        /// </summary>
        /// <param name="convertString"></param>
        /// <returns></returns>

        public static string Encrypt16(string convertString)
        {
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            string t2 = BitConverter.ToString(md5.ComputeHash(UTF8Encoding.Default.GetBytes(convertString)), 4, 8);
            t2 = t2.Replace("-", "");
            return t2.ToLower();
        }
        #endregion

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

app.config

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
  <appSettings>
    <add key="ConStringEncrypt" value="false"/>
    <add key="DbProvider" value="SqlServer"/>
    <!-- 通过改变value值来更换数据库  
    <add key="DataProvider" value="Oracle"/>  
    <add key="DataProvider" value="SqlServer"/>  
    <add key="DataProvider" value="OleDb"/>  
    <add key="DataProvider" value="Odbc"/>   
    <add key="DataProvider" value="MySql"/>  
    <add key="DataProvider" value="SQLite"/> 
    -->    
  </appSettings>
  <connectionStrings>        


    <add name="ConnString" connectionString="server=.;database=BookManage;Persist Security Info=False;uid=sa;pwd=sa123"/>
    <!-- 通过改变ConnectionString的值来更换数据库连接字符串  
    <add name ="ConnString" connectionString="Data Source=.\data\BookManageDB.db"/>
    <add name="ConnString" connectionString="server=.;database=CourseManageDB;Persist Security Info=False;uid=sa;pwd=sa123"/>
    <add name ="ConnString" connectionString="Server=.;Database=CourseManageDB;Uid=sa;Pwd=sa123"/>
    <add name ="ConnectionStringMssql" connectionString="Server=.;Database=BookManage;Uid=sa;Pwd=sa123"/>
    <add name="ConnString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBDemo.mdb;Jet OLEDB:Database Password=1234"/>  
    <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;Integrated Security=SSPI"/>  
    <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=sa;pwd=1234"/>  
    <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>  
    <add name ="connString" connectionString="Data Source=.\data\BookManageDB.db"/>
    -->
   </connectionStrings>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

修改 IDbManager.cs

void AddParameters(params Parameters[] cmdParms);1

修改 DbManager.cs

public void AddParameters(params Parameters[] cmdParms)
        {
            // 根据数组创建paramter对象列表
            this.CreateParameters(cmdParms.Length);

            for (int index = 0; index < cmdParms.Length; index++)
            {                 
                Parameters[index].ParameterName = cmdParms[index].ParamName;
                Parameters[index].Value = cmdParms[index].ParamValue;
            }
        }1
2
3
4
5
6
7
8
9
10
11

测试

private static void SelectWithParams2(int CourseId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from course");
            strSql.Append(" where ");
            strSql.Append(" CourseId < @CourseId  ");

            //先创建参数，然后才能添加参数   

            //DbHelper.CreateParameters(1);  //参数个数，1个  
            //DbHelper.AddParameters(0, "@CourseId", CourseId);

            Parameters[] parameters = {
                        new Parameters("@CourseId", CourseId)
            };  // 增加带参数的sql对应字段


            DbHelper.AddParameters(parameters);  // 注意增加带参数的sql对应字段
            Console.WriteLine(strSql.ToString());

            int num = Convert.ToInt32(DbHelper.ExecuteScalar(strSql.ToString()));  
            Console.WriteLine("num:" + num.ToString());

            //IDataReader reader = DbHelper.ExecuteReader(strSql.ToString());
            //while (reader.Read())
            //{
            //    Console.WriteLine(reader["CourseId"].ToString());
            //}
            DbHelper.Close();
        }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

下面还希望能做成如下写法：
public static int ExecuteSql(string SQLString, params Parameters[] cmdParms)

DbHelper.ExecuteSql(strSql.ToString(), parameters )
待续…

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/377843