续《基于C# 开发的SOL SERVER 操作数据库类（SQLHelp》 ——第二弹

续上一节，本节给出SQLHelp的具体实现方法——《YSFSQLHelp》，个人根据自己需要新建适合的类，本节根据参考网上资料，根据自己的需要编写的SQL帮助类。下面直接给出具体实现：

        //Data Source=.;Initial Catalog=SEFEvaluation;Persist Security Info=True;User ID=sa;Password=***********
        public static string SqlHost = GetConfig.GetConfiguration("ConnectionStrings:SqlHost");
        public static string SqlAdminName = GetConfig.GetConfiguration("ConnectionStrings:SqlAdminName");
        public static string SqlAdminPswd = GetConfig.GetConfiguration("ConnectionStrings:SqlAdminPswd");
        public static string SqlCatalogProFix = GetConfig.GetConfiguration("ConnectionStrings:SqlCatalogProFix");
        public static string strcon = string.Format(@"Data Source={0};User ID={1};Password={2};Initial Catalog={3};Pooling=true", SqlHost, SqlAdminName, SqlAdminPswd, SqlCatalogProFix);

       // public static string strcon = "Server=8.135.110.228;Initial Catalog=TestDB;User ID=sa;Password=QVq4iu=yV$ge7*qQ.6e,yL|hg!8d-79.,@yv?P=hj/JB72sd567";
        //数据库连接字符串(web.config来配置)
        //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />  
        #region 类中的全局变量-数据连接字符串  
        // public static string strcon = "Data Source=127.0.0.1;User ID=sa;Password=family962464QR;Initial Catalog=SEFEvaluation;Pooling=true";//连接字符串，使用Windows登录方式
        // public static string strcon = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString.ToString();//连接字符串，使用Windows登录方式
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数，初始化时连接数据库
        /// </summary>
        public YSFSQLHelp()
            {
                strcon = strcon;// ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString.ToString();
            }
            #endregion
            #region 其他转化与编码
            /// <summary>
            /// MD5加密
            /// </summary>
            /// <param name="strPwd">被加密的字符串</param>
            /// <returns>返回加密后的字符串</returns>
            public string GetMD5(string strPwd)
            {
                string pwd = "";
                //实例化一个md5对象
                MD5 md5 = MD5.Create();
                // 加密后是一个字节类型的数组，这里要注意编码UTF8/Unicode等的选择
                byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(strPwd));
                //翻转生成的MD5码        
                s.Reverse();
                //通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得
                //只取MD5码的一部分，这样恶意访问者无法知道取的是哪几位
                for (int i = 3; i < s.Length - 1; i++)
                {
                    //将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
                    //进一步对生成的MD5码做一些改造
                    pwd = pwd + (s[i] < 198 ? s[i] + 28 : s[i]).ToString("X");
                }
                return pwd;
            }
            /// <summary>
            /// table转化为数组 
            /// </summary>
            /// <param name="table"> datatable对象</param>
            /// <param name="status">M，只查一条数据，否则查多条</param>
            /// <returns></returns>
            public  List<Dictionary<string, object>> QueryData(DataTable table, string status)
            {
                List<Dictionary<string, object>> outdata = new List<Dictionary<string, object>>();
                Dictionary<string, object> dic = new Dictionary<string, object>();
                List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
                try
                {
                    DataTable tb = new DataTable();
                    tb = table;
                    //如果数据库返回的总数据量少于一页显示的量，则循环次数为返回的数据量，反之就是规定的一页数据量
                    //strip 具体拿取 的信息条数          
                    if (tb.Rows.Count > 0)
                    {
                        if (status.Equals("M"))
                        {
                            for (int i = 0; i < tb.Rows.Count; i++)
                            {
                                //重置参数
                                listdata = new List<Dictionary<string, object>>();
                                dic = new Dictionary<string, object>();

                                for (int j = 0; j < tb.Columns.Count; j++)
                                {
                                    dic.Add(tb.Columns[j].ColumnName, tb.Rows[i][j].ToString());

                                }
                                outdata.Add(dic);
                            }
                        }
                        else
                        {
                            //每查询一次就存入一次Outdata中    i是行数，c是列数
                            for (int i = 0; i < 1; i++)
                            {

                                dic = new Dictionary<string, object>();
                                for (int c = 0; c < tb.Columns.Count; c++)
                                {
                                    dic.Add(tb.Columns[c].ColumnName, tb.Rows[i][c].ToString());
                                }
                                outdata.Add(dic);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {

                }
                return outdata;
            }
            /// <summary>
            /// SqlDataReader转化为Table
            /// </summary>
            /// <param name="reader">SqlDataReader对象</param>
            /// <returns></returns>
            public  DataTable ConvertDataReaderToDataTable(SqlDataReader reader)
            {
                try
                {
                    DataTable objDataTable = new DataTable();
                    int intFieldCount = reader.FieldCount;
                    for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                    {
                        objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                    }
                    objDataTable.BeginLoadData();

                    object[] objValues = new object[intFieldCount];
                    while (reader.Read())
                    {
                        reader.GetValues(objValues);
                        objDataTable.LoadDataRow(objValues, true);
                    }
                    reader.Close();
                    objDataTable.EndLoadData();

                    return objDataTable;

                }
                catch (Exception ex)
                {
                    throw new Exception("转换出错!", ex);
                }

            }

            /// <summary>
            /// SQL 语句查询转化为数组 
            /// </summary>
            /// <param name="cmdstr"> SQL语句</param>
            /// <param name="status">M，查多条数据，否则1条</param>
            /// <returns></returns>
            public  List<Dictionary<string, object>> QueryDataSQL(string cmdstr, string status)
            {
                List<Dictionary<string, object>> outdata = new List<Dictionary<string, object>>();
                Dictionary<string, object> dic = new Dictionary<string, object>();
                List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
                SqlConnection con = new SqlConnection(strcon);
                SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
                try
                {
                    con.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    DataTable tb = new DataTable();
                    tb = ds.Tables[0];
                    //如果数据库返回的总数据量少于一页显示的量，则循环次数为返回的数据量，反之就是规定的一页数据量
                    //strip 具体拿取 的信息条数          
                    if (tb.Rows.Count > 0)
                    {
                        if (status.Equals("M"))
                        {
                            for (int i = 0; i < tb.Rows.Count; i++)
                            {
                                //重置参数
                                listdata = new List<Dictionary<string, object>>();
                                dic = new Dictionary<string, object>();

                                for (int j = 0; j < tb.Columns.Count; j++)
                                {
                                    dic.Add(tb.Columns[j].ColumnName, tb.Rows[i][j].ToString());
                                }
                                outdata.Add(dic);
                            }
                        }
                        else
                        {
                            //每查询一次就存入一次Outdata中    i是行数，c是列数
                            for (int i = 0; i < 1; i++)
                            {

                                dic = new Dictionary<string, object>();
                                for (int c = 0; c < tb.Columns.Count; c++)
                                {
                                    dic.Add(tb.Columns[c].ColumnName, tb.Rows[i][c].ToString());
                                }
                                outdata.Add(dic);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    CreateInLog("SQL 语句 " + cmdstr + "执行错误！" + ex.Message);
                }
                finally
                {
                    con.Close();
                }
                return outdata;
            }
            /// <summary>
            /// SQL 语句查询转化为数组 
            /// </summary>
            /// <param name="strconn"> 自定义连接串</param>
            /// <param name="cmdstr"> SQL语句</param>
            /// <param name="status">M，查多条数据，否则1条</param>
            /// <returns></returns>
            public  List<Dictionary<string, object>> QueryDataSQL(string strconn, string cmdstr, string status)
            {
                List<Dictionary<string, object>> outdata = new List<Dictionary<string, object>>();
                Dictionary<string, object> dic = new Dictionary<string, object>();
                List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
                SqlConnection con = new SqlConnection(strconn);
                SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
                try
                {
                    con.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    DataTable tb = new DataTable();
                    tb = ds.Tables[0];
                    //如果数据库返回的总数据量少于一页显示的量，则循环次数为返回的数据量，反之就是规定的一页数据量
                    //strip 具体拿取 的信息条数          
                    if (tb.Rows.Count > 0)
                    {
                        if (status.Equals("M"))
                        {
                            for (int i = 0; i < tb.Rows.Count; i++)
                            {
                                //重置参数
                                listdata = new List<Dictionary<string, object>>();
                                dic = new Dictionary<string, object>();

                                for (int j = 0; j < tb.Columns.Count; j++)
                                {
                                    dic.Add(tb.Columns[j].ColumnName, tb.Rows[i][j].ToString());
                                }
                                outdata.Add(dic);
                            }
                        }
                        else
                        {
                            //每查询一次就存入一次Outdata中    i是行数，c是列数
                            for (int i = 0; i < 1; i++)
                            {

                                dic = new Dictionary<string, object>();
                                for (int c = 0; c < tb.Columns.Count; c++)
                                {
                                    dic.Add(tb.Columns[c].ColumnName, tb.Rows[i][c].ToString());
                                }
                                outdata.Add(dic);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    CreateInLog("SQL 语句 " + cmdstr + "在" + strconn + "执行错误！" + ex.Message);
                }
                finally
                {
                    con.Close();
                }
                return outdata;
            }
            /// <summary>
            /// SQL 执行本地查询存储过程转化为数组 
            /// </summary>
            /// <param name="cmdstr"> SQL语句</param>
            /// <param name="status">M，查多条数据，否则1条</param>
            /// <returns></returns>
            public  List<Dictionary<string, object>> QueryDataParameter(string storedProcName, SqlParameter[] parameters, string status)
            {
                List<Dictionary<string, object>> outdata = new List<Dictionary<string, object>>();
                Dictionary<string, object> dic = new Dictionary<string, object>();
                List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
                //DataTable dataTable2 = new DataTable();

                try
                {
                    DataTable dataTable2 = SqlQueryParameter(storedProcName, parameters);
                    DataTable tb = new DataTable();
                    tb = dataTable2;
                    //如果数据库返回的总数据量少于一页显示的量，则循环次数为返回的数据量，反之就是规定的一页数据量
                    //strip 具体拿取 的信息条数          
                    if (tb.Rows.Count > 0)
                    {
                        if (status.Equals("M"))
                        {
                            for (int i = 0; i < tb.Rows.Count; i++)
                            {
                                //重置参数
                                listdata = new List<Dictionary<string, object>>();
                                dic = new Dictionary<string, object>();

                                for (int j = 0; j < tb.Columns.Count; j++)
                                {
                                 
                                    dic.Add(tb.Columns[j].ColumnName, tb.Rows[i][j].ToString());

                                }
                                outdata.Add(dic);
                            }
                        }
                        else
                        {
                            //每查询一次就存入一次Outdata中    i是行数，c是列数
                            for (int i = 0; i < 1; i++)
                            {

                                dic = new Dictionary<string, object>();
                                for (int c = 0; c < tb.Columns.Count; c++)
                                {
                                    
                                    dic.Add(tb.Columns[c].ColumnName, tb.Rows[i][c].ToString());
                                    

                                }
                                outdata.Add(dic);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    CreateInLog("SQL 存储过程 " + storedProcName + "执行错误！" + ex.Message);
                }
                finally
                {

                }
                return outdata;
            }
            /// <summary>
            /// SQL 执行非本地查询存储过程转化为数组 
            /// </summary>
            /// <param name="strconn"> 自定义连接串</param>
            /// <param name="cmdstr"> SQL语句</param>
            /// <param name="status">M，查多条数据，否则1条</param>
            /// <returns></returns>
            public  List<Dictionary<string, object>> QueryDataParameter(string strconn, string storedProcName, SqlParameter[] parameters, string status)
            {
                List<Dictionary<string, object>> outdata = new List<Dictionary<string, object>>();
                Dictionary<string, object> dic = new Dictionary<string, object>();
                List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
                //DataTable dataTable2 = new DataTable();

                try
                {
                    DataTable dataTable2 = SqlQueryParameter(strconn, storedProcName, parameters);
                    DataTable tb = new DataTable();
                    tb = dataTable2;
                    //如果数据库返回的总数据量少于一页显示的量，则循环次数为返回的数据量，反之就是规定的一页数据量
                    //strip 具体拿取 的信息条数          
                    if (tb.Rows.Count > 0)
                    {
                        if (status.Equals("M"))
                        {
                            for (int i = 0; i < tb.Rows.Count; i++)
                            {
                                //重置参数
                                listdata = new List<Dictionary<string, object>>();
                                dic = new Dictionary<string, object>();

                                for (int j = 0; j < tb.Columns.Count; j++)
                                {
                                    dic.Add(tb.Columns[j].ColumnName, tb.Rows[i][j].ToString());

                                }
                                outdata.Add(dic);
                            }
                        }
                        else
                        {
                            //每查询一次就存入一次Outdata中    i是行数，c是列数
                            for (int i = 0; i < 1; i++)
                            {

                                dic = new Dictionary<string, object>();
                                for (int c = 0; c < tb.Columns.Count; c++)
                                {
                                    
                                    dic.Add(tb.Columns[c].ColumnName, tb.Rows[i][c].ToString());
                                    

                                }
                                outdata.Add(dic);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    CreateInLog("SQL 存储过程 " + storedProcName + "执行错误！" + ex.Message);
                }
                finally
                {

                }
                return outdata;
            }
        #endregion

 

public static string SqlHost = GetConfig.GetConfiguration("ConnectionStrings:SqlHost");//获取配置的服务器
public static string SqlAdminName = GetConfig.GetConfiguration("ConnectionStrings:SqlAdminName");//数据库账号
public static string SqlAdminPswd = GetConfig.GetConfiguration("ConnectionStrings:SqlAdminPswd");//数据库密码
public static string SqlCatalogProFix = GetConfig.GetConfiguration("ConnectionStrings:SqlCatalogProFix");//数据库
public static string strcon = string.Format(@"Data Source={0};User ID={1};Password={2};Initial Catalog={3};Pooling=true", SqlHost, SqlAdminName, SqlAdminPswd, SqlCatalogProFix);

ConnectionStrings appsettings文件配置的数据库连接串

GetConfiguration//获取配置的数据库连接信息

也可以使用依赖注入调用

 

        public static string GetConfiguration(string configKey)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json");
            var config = builder.Build();
            if (configKey.Contains(":"))
            {
                return config.GetSection(configKey).Value;//获取分级参数值
            }
            else
            {
                return config[configKey];//获取直级参数值
            }
            //youdianwenti w xiangxiang
        }

如有侵权，请联系作者，将进行整改

 今日分享结束

能帮朋友解决问题的记得给个关注支持一下，以后将多多分享SQL SERVER 相关知识