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 用于獲取連接數(shù)據(jù)庫的 connection 對象
/// </summary>
/// <returns></returns>
private static DbConnection GetConnection()
{
DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = _factory.CreateConnection();
connection.ConnectionString = connectionString;
return connection;
}
/// <summary>
/// GetCommand 獲取命令參數(shù) 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 用于為命令設(shè)置參數(shù)
/// </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>
/// 執(zhí)行無參的存儲(chǔ)過程
/// </summary>
/// <param name="sqlCommand">存儲(chǔ)過程名</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>
/// 執(zhí)行無返回值有參數(shù)的存儲(chǔ)過程
/// </summary>
/// <param name="sqlCommand">存儲(chǔ)過程名</param>
/// <param name="parameters">參數(shù)</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>
/// 執(zhí)行無返回值的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>
/// 執(zhí)行有參數(shù)的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>
/// 執(zhí)行有返回值無參數(shù)的存儲(chǔ)過程
/// </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>
/// 執(zhí)行有返回值的有參數(shù)的存儲(chǔ)過程
/// </summary>
/// <param name="cmdText">存儲(chǔ)過程名</param>
/// <param name="para">參數(shù)</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>
/// 執(zhí)行有返回值的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>
/// 執(zhí)行有返回值有參數(shù)的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>
/// 執(zhí)行無參數(shù)的存儲(chǔ)過程,返回DbDataReader對象
/// </summary>
/// <param name="sqlCommand">存儲(chǔ)過程名</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>
/// 執(zhí)行有參數(shù)的存儲(chǔ)過程,返回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>
/// 執(zhí)行無參數(shù)的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>
/// 執(zhí)行有參數(shù)的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;
}
}
}
}
