续上一节,本节给出SQLHelp的具体实现方法——《YSFSQLHelp》,个人根据自己需要新建适合的类,本节根据参考网上资料,根据自己的需要编写的SQL帮助类。下面直接给出具体实现:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
//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; } #endregionView Code
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//获取配置的数据库连接信息 也可以使用依赖注入调用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }View Code
如有侵权,请联系作者,将进行整改
今日分享结束
能帮朋友解决问题的记得给个关注支持一下,以后将多多分享SQL SERVER 相关知识
标签:SQLHelp,string,C#,List,SOL,dic,int,new,tb From: https://www.cnblogs.com/libo962464/p/16758269.html