您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
lyqConnectionString 要换
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Configuration;
- using System.Data.Common;
- using System.Data;
- using System.Data.SqlClient;
- namespace LyqDAL
- {
- /// <summary>
- /// 自定义访问通用类
- /// </summary>
- public class DBHelper
- {
- private static readonly string connectionString = ConfigurationManager.ConnectionStrings["lyqConnectionString"].ConnectionString.ToString();
- private static readonly string providerName = "System.Data.SqlClient";
- private static SqlConnection connection = new SqlConnection(connectionString);
- /// <summary>
- /// GetConnection 用于获取连接数据库的 connection 对象
- /// </summary>
- /// <returns></returns>
- private static DbConnection GetConnection()
- {
- DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
- DbConnection connection = _factory.CreateConnection();
- connection.ConnectionString = connectionString;
- return connection;
- }
- /// <summary>
- /// GetCommand 获取命令参数 command 对象
- /// </summary>
- /// <param name="commandText"></param>
- /// <param name="commandType"></param>
- /// <param name="connection"></param>
- /// <returns></returns>
- private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection)
- {
- DbCommand command = connection.CreateCommand();
- command.CommandText = commandText;
- command.CommandType = commandType;
- return command;
- }
- /// <summary>
- /// 读取数据
- /// </summary>
- /// <param name="sql">sql语句</param>
- /// <param name="param">参数列表</param>
- /// <returns>DataTable</returns>
- public static DataTable GetDataTable(string sql, params SqlParameter[] param)
- {
- DataTable dt = new DataTable();
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- SqlDataAdapter da = new SqlDataAdapter(sql, conn);
- if (param != null)
- da.SelectCommand.Parameters.AddRange(param);
- da.Fill(dt);
- }
- return dt;
- }
- /// <summary>
- /// GetCommand 方法重载
- /// </summary>
- /// <param name="commandText">sql语句</param>
- /// <param name="connection"></param>
- /// <returns></returns>
- private static DbCommand GetCommand(string commandText, DbConnection connection)
- {
- DbCommand command = connection.CreateCommand();
- command.CommandText = commandText;
- command.CommandType = CommandType.Text;
- return command;
- }
- public DbCommand GetSqlStringCommond(string sqlQuery)
- {
- DbCommand dbCommand = connection.CreateCommand();
- dbCommand.CommandText = sqlQuery;
- dbCommand.CommandType = CommandType.Text;
- return dbCommand;
- }
- /// <summary>
- /// GetParameter 用于为命令设置参数
- /// </summary>
- /// <param name="paramName"></param>
- /// <param name="paramValue"></param>
- /// <param name="command"></param>
- /// <returns></returns>
- private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command)
- {
- DbParameter parameter = command.CreateParameter();
- parameter.ParameterName = paramName;
- parameter.Value = paramValue;
- return parameter;
- }
- /// <summary>
- /// 执行无参的存储过程
- /// </summary>
- /// <param name="sqlCommand">存储过程名</param>
- /// <returns></returns>
- public static int ExecuteNonQueryProc(string sqlCommand)
- {
- int result = 0;
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
- connection.Open();
- result = command.ExecuteNonQuery();
- command.Parameters.Clear();
- }
- return result;
- }
- /// <summary>
- /// 执行无返回值有参数的存储过程
- /// </summary>
- /// <param name="sqlCommand">存储过程名</param>
- /// <param name="parameters">参数</param>
- /// <returns></returns>
- public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<string, object> parameters)
- {
- int result = 0;
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
- foreach (KeyValuePair<string, object> p in parameters)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- result = command.ExecuteNonQuery();
- command.Parameters.Clear();
- }
- return result;
- }
- /// <summary>
- /// 执行无返回值的sql语句
- /// </summary>
- /// <param name="sqlCommand"></param>
- /// <param name="parameters"></param>
- public static int ExecuteNonQuery(string sqlCommand)
- {
- int result = 0;
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
- connection.Open();
- result = command.ExecuteNonQuery();
- command.Parameters.Clear();
- return result;
- }
- }
- /// <summary>
- /// 执行有参数的sql语句
- /// </summary>
- /// <param name="sqlCommand"></param>
- /// <param name="para"></param>
- /// <returns></returns>
- public static int ExecuteNonQuery(string sqlCommand, Dictionary<string, object> para)
- {
- int result = 0;
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
- foreach (KeyValuePair<string, object> p in para)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- result = command.ExecuteNonQuery();
- command.Parameters.Clear();
- return result;
- }
- }
- /// <summary>
- /// 执行有返回值无参数的存储过程
- /// </summary>
- /// <param name="cmdText"></param>
- /// <returns></returns>
- public static object ExecuteScalarProc(string cmdText)
- {
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
- connection.Open();
- object val = command.ExecuteScalar();
- command.Parameters.Clear();
- return val;
- }
- }
- /// <summary>
- /// 执行有返回值的有参数的存储过程
- /// </summary>
- /// <param name="cmdText">存储过程名</param>
- /// <param name="para">参数</param>
- /// <returns></returns>
- public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para)
- {
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
- foreach (KeyValuePair<string, object> p in para)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- object val = command.ExecuteScalar();
- command.Parameters.Clear();
- return val;
- }
- }
- /// <summary>
- /// 执行有返回值的sql语句
- /// </summary>
- /// <param name="cmdText"></param>
- /// <returns></returns>
- public static object ExecuteScalar(string cmdText)
- {
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
- connection.Open();
- object val = command.ExecuteScalar();
- command.Parameters.Clear();
- return val;
- }
- }
- /// <summary>
- /// 执行有返回值有参数的sql语句
- /// </summary>
- /// <param name="cmdText"></param>
- /// <param name="para"></param>
- /// <returns></returns>
- public static object ExecuteScalar(string cmdText, Dictionary<string, object> para)
- {
- using (DbConnection connection = GetConnection())
- {
- DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
- foreach (KeyValuePair<string, object> p in para)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- object val = command.ExecuteScalar();
- command.Parameters.Clear();
- return val;
- }
- }
- /// <summary>
- /// 执行无参数的存储过程,返回DbDataReader对象
- /// </summary>
- /// <param name="sqlCommand">存储过程名</param>
- /// <returns></returns>
- public static DbDataReader GetReaderProc(string sqlCommand)
- {
- try
- {
- DbConnection connection = GetConnection();
- DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
- connection.Open();
- DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
- return reader;
- }
- catch (Exception ex)
- {
- Console.Write("" + ex.Message);
- return null;
- }
- }
- /// <summary>
- /// 执行有参数的存储过程,返回DbDataReader对象
- /// </summary>
- /// <param name="sqlCommand"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters)
- {
- try
- {
- DbConnection connection = GetConnection();
- DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
- foreach (KeyValuePair<string, object> p in parameters)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
- command.Parameters.Clear();
- return reader;
- }
- catch
- {
- return null;
- }
- }
- #region
- /// <summary>
- /// 执行无参数的sql语句,返回DbDataReader对象
- /// </summary>
- /// <param name="sqlCommand"></param>
- /// <returns></returns>
- public static DbDataReader GetReader(string sqlCommand)
- {
- try
- {
- DbConnection connection = GetConnection();
- DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
- connection.Open();
- DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
- command.Parameters.Clear();
- return reader;
- }
- catch (Exception ex)
- {
- Console.Write("" + ex.Message);
- return null;
- }
- }
- #endregion
- /// <summary>
- /// 执行有参数的sql语句,返回DbDataReader对象
- /// </summary>
- /// <param name="sqlCommand"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> parameters)
- {
- try
- {
- DbConnection connection = GetConnection();
- DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
- foreach (KeyValuePair<string, object> p in parameters)
- {
- command.Parameters.Add(GetParameter(p.Key, p.Value, command));
- }
- connection.Open();
- DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
- command.Parameters.Clear();
- return reader;
- }
- catch (Exception ex)
- {
- Console.Write("" + ex.Message);
- return null;
- }
- }
- /// <summary>
- /// 返回DataTable对象
- /// </summary>
- /// <param name="safeSql"></param>
- /// <returns></returns>
- public static DataTable GetDataSet(string safeSql)
- {
- /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
- DbConnection connection = GetConnection();
- connection.Open();
- DbDataAdapter da = _factory.CreateDataAdapter();
- da.SelectCommand = connection.CreateCommand();
- da.SelectCommand.CommandText = safeSql;
- DataTable dt = new DataTable();
- da.Fill(dt);
- return dt;*/
- using (DbConnection connection = GetConnection())
- {
- DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
- DbCommand command = GetCommand(safeSql, CommandType.Text, connection);
- connection.Open();
- DbDataAdapter da = _factory.CreateDataAdapter();
- da.SelectCommand = command;
- DataTable datatable = new DataTable();
- da.Fill(datatable);
- return datatable;
- }
- }
- }
- }
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。