using System;using System.Collections.Generic;using System.Text;using System.Configuration;using System.Data.Common;using System.Data;namespace DiaryDAL{ public static class DBHelper { private static readonly string connectionString = ConfigurationManager.ConnectionStrings["DbHelperConnectionString"].ConnectionString.ToString(); private static readonly string providerName = "System.Data.SqlClient"; /// <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> /// 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; } /// <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; } } }}
