三层结构视频中的DBHelper.cs

发布时间:2020-06-22 20:04:12 作者:275821115
来源:网络 阅读:782

 

 

 lyqConnectionString  要换

  1. using System; 
  2. using System.Collections.Generic; 
  3. using System.Text; 
  4. using System.Configuration; 
  5. using System.Data.Common; 
  6. using System.Data; 
  7. using System.Data.SqlClient; 
  8.  
  9.  
  10. namespace LyqDAL 
  11.     /// <summary> 
  12.     /// 自定义访问通用类 
  13.     /// </summary> 
  14.     public class DBHelper 
  15.     { 
  16.         private static readonly string connectionString = ConfigurationManager.ConnectionStrings["lyqConnectionString"].ConnectionString.ToString(); 
  17.         private static readonly string providerName = "System.Data.SqlClient"
  18.         private static SqlConnection connection = new SqlConnection(connectionString); 
  19.         /// <summary> 
  20.         /// GetConnection 用于获取连接数据库的 connection 对象 
  21.         /// </summary> 
  22.         /// <returns></returns> 
  23.         private static DbConnection GetConnection() 
  24.         { 
  25.             DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 
  26.             DbConnection connection = _factory.CreateConnection(); 
  27.             connection.ConnectionString = connectionString; 
  28.             return connection; 
  29.         } 
  30.         /// <summary> 
  31.         /// GetCommand 获取命令参数 command 对象 
  32.         /// </summary> 
  33.         /// <param name="commandText"></param> 
  34.         /// <param name="commandType"></param> 
  35.         /// <param name="connection"></param> 
  36.         /// <returns></returns> 
  37.         private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection) 
  38.         { 
  39.             DbCommand command = connection.CreateCommand(); 
  40.             command.CommandText = commandText; 
  41.             command.CommandType = commandType; 
  42.             return command; 
  43.  
  44.  
  45.         } 
  46.         /// <summary> 
  47.         /// 读取数据 
  48.         /// </summary> 
  49.         /// <param name="sql">sql语句</param> 
  50.         /// <param name="param">参数列表</param> 
  51.         /// <returns>DataTable</returns> 
  52.         public static DataTable GetDataTable(string sql, params SqlParameter[] param) 
  53.         { 
  54.             DataTable dt = new DataTable(); 
  55.             using (SqlConnection conn = new SqlConnection(connectionString)) 
  56.             { 
  57.                 SqlDataAdapter da = new SqlDataAdapter(sql, conn); 
  58.                 if (param != null
  59.                     da.SelectCommand.Parameters.AddRange(param); 
  60.                 da.Fill(dt); 
  61.             } 
  62.             return dt; 
  63.         } 
  64.         /// <summary> 
  65.         /// GetCommand 方法重载 
  66.         /// </summary> 
  67.         /// <param name="commandText">sql语句</param> 
  68.         /// <param name="connection"></param> 
  69.         /// <returns></returns> 
  70.         private static DbCommand GetCommand(string commandText, DbConnection connection) 
  71.         { 
  72.             DbCommand command = connection.CreateCommand(); 
  73.             command.CommandText = commandText; 
  74.             command.CommandType = CommandType.Text; 
  75.             return command; 
  76.         } 
  77.         public DbCommand GetSqlStringCommond(string sqlQuery) 
  78.         { 
  79.             DbCommand dbCommand = connection.CreateCommand(); 
  80.             dbCommand.CommandText = sqlQuery; 
  81.             dbCommand.CommandType = CommandType.Text; 
  82.             return dbCommand; 
  83.         } 
  84.         /// <summary> 
  85.         /// GetParameter 用于为命令设置参数 
  86.         /// </summary> 
  87.         /// <param name="paramName"></param> 
  88.         /// <param name="paramValue"></param> 
  89.         /// <param name="command"></param> 
  90.         /// <returns></returns> 
  91.         private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command) 
  92.         { 
  93.             DbParameter parameter = command.CreateParameter(); 
  94.             parameter.ParameterName = paramName; 
  95.             parameter.Value = paramValue; 
  96.             return parameter; 
  97.         } 
  98.         /// <summary> 
  99.         /// 执行无参的存储过程 
  100.         /// </summary> 
  101.         /// <param name="sqlCommand">存储过程名</param> 
  102.         /// <returns></returns> 
  103.         public static int ExecuteNonQueryProc(string sqlCommand) 
  104.         { 
  105.             int result = 0; 
  106.             using (DbConnection connection = GetConnection()) 
  107.             { 
  108.                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 
  109.                 connection.Open(); 
  110.                 result = command.ExecuteNonQuery(); 
  111.                 command.Parameters.Clear(); 
  112.             } 
  113.             return result; 
  114.         } 
  115.         /// <summary> 
  116.         /// 执行无返回值有参数的存储过程 
  117.         /// </summary> 
  118.         /// <param name="sqlCommand">存储过程名</param> 
  119.         /// <param name="parameters">参数</param> 
  120.         /// <returns></returns> 
  121.         public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<stringobject> parameters) 
  122.         { 
  123.             int result = 0; 
  124.             using (DbConnection connection = GetConnection()) 
  125.             { 
  126.                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 
  127.                 foreach (KeyValuePair<stringobject> p in parameters) 
  128.                 { 
  129.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  130.                 } 
  131.                 connection.Open(); 
  132.                 result = command.ExecuteNonQuery(); 
  133.                 command.Parameters.Clear(); 
  134.             } 
  135.             return result; 
  136.         } 
  137.  
  138.         /// <summary> 
  139.         /// 执行无返回值的sql语句 
  140.         /// </summary> 
  141.         /// <param name="sqlCommand"></param> 
  142.         /// <param name="parameters"></param> 
  143.         public static int ExecuteNonQuery(string sqlCommand) 
  144.         { 
  145.             int result = 0; 
  146.             using (DbConnection connection = GetConnection()) 
  147.             { 
  148.                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 
  149.                 connection.Open(); 
  150.                 result = command.ExecuteNonQuery(); 
  151.                 command.Parameters.Clear(); 
  152.                 return result; 
  153.             } 
  154.         } 
  155.         /// <summary> 
  156.         /// 执行有参数的sql语句 
  157.         /// </summary> 
  158.         /// <param name="sqlCommand"></param> 
  159.         /// <param name="para"></param> 
  160.         /// <returns></returns> 
  161.         public static int ExecuteNonQuery(string sqlCommand, Dictionary<stringobject> para) 
  162.         { 
  163.             int result = 0; 
  164.             using (DbConnection connection = GetConnection()) 
  165.             { 
  166.                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 
  167.                 foreach (KeyValuePair<stringobject> p in para) 
  168.                 { 
  169.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  170.                 } 
  171.                 connection.Open(); 
  172.                 result = command.ExecuteNonQuery(); 
  173.                 command.Parameters.Clear(); 
  174.                 return result; 
  175.             } 
  176.         } 
  177.         /// <summary> 
  178.         /// 执行有返回值无参数的存储过程 
  179.         /// </summary> 
  180.         /// <param name="cmdText"></param> 
  181.         /// <returns></returns> 
  182.         public static object ExecuteScalarProc(string cmdText) 
  183.         { 
  184.             using (DbConnection connection = GetConnection()) 
  185.             { 
  186.                 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection); 
  187.                 connection.Open(); 
  188.                 object val = command.ExecuteScalar(); 
  189.                 command.Parameters.Clear(); 
  190.                 return val; 
  191.             } 
  192.         } 
  193.         /// <summary> 
  194.         /// 执行有返回值的有参数的存储过程 
  195.         /// </summary> 
  196.         /// <param name="cmdText">存储过程名</param> 
  197.         /// <param name="para">参数</param> 
  198.         /// <returns></returns> 
  199.         public static object ExecuteScalarProc(string cmdText, Dictionary<stringobject> para) 
  200.         { 
  201.             using (DbConnection connection = GetConnection()) 
  202.             { 
  203.                 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection); 
  204.                 foreach (KeyValuePair<stringobject> p in para) 
  205.                 { 
  206.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  207.                 } 
  208.                 connection.Open(); 
  209.                 object val = command.ExecuteScalar(); 
  210.                 command.Parameters.Clear(); 
  211.                 return val; 
  212.             } 
  213.         } 
  214.         /// <summary> 
  215.         /// 执行有返回值的sql语句 
  216.         /// </summary> 
  217.         /// <param name="cmdText"></param> 
  218.         /// <returns></returns> 
  219.         public static object ExecuteScalar(string cmdText) 
  220.         { 
  221.             using (DbConnection connection = GetConnection()) 
  222.             { 
  223.                 DbCommand command = GetCommand(cmdText, CommandType.Text, connection); 
  224.                 connection.Open(); 
  225.                 object val = command.ExecuteScalar(); 
  226.                 command.Parameters.Clear(); 
  227.                 return val; 
  228.             } 
  229.         } 
  230.         /// <summary> 
  231.         /// 执行有返回值有参数的sql语句 
  232.         /// </summary> 
  233.         /// <param name="cmdText"></param> 
  234.         /// <param name="para"></param> 
  235.         /// <returns></returns> 
  236.         public static object ExecuteScalar(string cmdText, Dictionary<stringobject> para) 
  237.         { 
  238.             using (DbConnection connection = GetConnection()) 
  239.             { 
  240.                 DbCommand command = GetCommand(cmdText, CommandType.Text, connection); 
  241.                 foreach (KeyValuePair<stringobject> p in para) 
  242.                 { 
  243.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  244.                 } 
  245.                 connection.Open(); 
  246.                 object val = command.ExecuteScalar(); 
  247.                 command.Parameters.Clear(); 
  248.                 return val; 
  249.             } 
  250.         } 
  251.         /// <summary> 
  252.         /// 执行无参数的存储过程,返回DbDataReader对象 
  253.         /// </summary> 
  254.         /// <param name="sqlCommand">存储过程名</param> 
  255.         /// <returns></returns> 
  256.         public static DbDataReader GetReaderProc(string sqlCommand) 
  257.         { 
  258.             try 
  259.             { 
  260.                 DbConnection connection = GetConnection(); 
  261.                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 
  262.                 connection.Open(); 
  263.                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 
  264.                 return reader; 
  265.             } 
  266.             catch (Exception ex) 
  267.             { 
  268.                 Console.Write("" + ex.Message); 
  269.                 return null
  270.             } 
  271.         } 
  272.         /// <summary> 
  273.         /// 执行有参数的存储过程,返回DbDataReader对象 
  274.         /// </summary> 
  275.         /// <param name="sqlCommand"></param> 
  276.         /// <param name="parameters"></param> 
  277.         /// <returns></returns> 
  278.         public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<stringobject> parameters) 
  279.         { 
  280.             try 
  281.             { 
  282.                 DbConnection connection = GetConnection(); 
  283.                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 
  284.                 foreach (KeyValuePair<stringobject> p in parameters) 
  285.                 { 
  286.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  287.                 } 
  288.                 connection.Open(); 
  289.                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 
  290.                 command.Parameters.Clear(); 
  291.                 return reader; 
  292.             } 
  293.             catch 
  294.             { 
  295.                 return null
  296.             } 
  297.         } 
  298.         #region 
  299.         /// <summary> 
  300.         /// 执行无参数的sql语句,返回DbDataReader对象 
  301.         /// </summary> 
  302.         /// <param name="sqlCommand"></param> 
  303.         /// <returns></returns> 
  304.         public static DbDataReader GetReader(string sqlCommand) 
  305.         { 
  306.             try 
  307.             { 
  308.                 DbConnection connection = GetConnection(); 
  309.                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 
  310.                 connection.Open(); 
  311.                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 
  312.                 command.Parameters.Clear(); 
  313.                 return reader; 
  314.             } 
  315.             catch (Exception ex) 
  316.             { 
  317.                 Console.Write("" + ex.Message); 
  318.                 return null
  319.             } 
  320.         } 
  321.         #endregion 
  322.         /// <summary> 
  323.         /// 执行有参数的sql语句,返回DbDataReader对象 
  324.         /// </summary> 
  325.         /// <param name="sqlCommand"></param> 
  326.         /// <param name="parameters"></param> 
  327.         /// <returns></returns> 
  328.         public static DbDataReader GetReader(string sqlCommand, Dictionary<stringobject> parameters) 
  329.         { 
  330.             try 
  331.             { 
  332.                 DbConnection connection = GetConnection(); 
  333.                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 
  334.                 foreach (KeyValuePair<stringobject> p in parameters) 
  335.                 { 
  336.                     command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 
  337.                 } 
  338.                 connection.Open(); 
  339.                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 
  340.                 command.Parameters.Clear(); 
  341.                 return reader; 
  342.             } 
  343.             catch (Exception ex) 
  344.             { 
  345.                 Console.Write("" + ex.Message); 
  346.                 return null
  347.             } 
  348.         } 
  349.          
  350.         /// <summary> 
  351.         /// 返回DataTable对象 
  352.         /// </summary> 
  353.         /// <param name="safeSql"></param> 
  354.         /// <returns></returns> 
  355.         public static DataTable GetDataSet(string safeSql) 
  356.         { 
  357.             /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 
  358.             DbConnection connection = GetConnection(); 
  359.             connection.Open(); 
  360.             DbDataAdapter da = _factory.CreateDataAdapter(); 
  361.             da.SelectCommand = connection.CreateCommand(); 
  362.             da.SelectCommand.CommandText = safeSql; 
  363.             DataTable dt = new DataTable(); 
  364.             da.Fill(dt); 
  365.             return dt;*/ 
  366.             using (DbConnection connection = GetConnection()) 
  367.             { 
  368.                 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 
  369.                 DbCommand command = GetCommand(safeSql, CommandType.Text, connection); 
  370.                 connection.Open(); 
  371.                 DbDataAdapter da = _factory.CreateDataAdapter(); 
  372.                 da.SelectCommand = command; 
  373.                 DataTable datatable = new DataTable(); 
  374.                 da.Fill(datatable); 
  375.                 return datatable; 
  376.             } 
  377.         } 
  378.     } 

 

推荐阅读:
  1. 在javaee的三层结构中,为什么事物存在于业务层
  2. 干货 | VMAF视频质量评估在视频云转码中的应用

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

视频 结构 he

上一篇:vs2015的部署以及一些其他问题

下一篇:闪亮亮的文本框效果

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》