/// <summary>
/// 执行sql语句或存储过程 返回DataReader
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="obj">object[]参数数组，允许空</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string commandTextOrSpName, CommandType commandType, params object[] obj)
{
//sqlDataReader不能用using 会关闭conn 导致不能获取到返回值。注意：DataReader获取值时必须保持连接状态
SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(Options.Opt_DataBaseType.SqlServer, ConnString_R, ConnString_RW);
SqlCommand cmd = new SqlCommand();
PreparCommand(conn.DbConnection as SqlConnection, cmd, commandTextOrSpName, commandType, obj);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
/// <summary>
/// 执行sql语句或存储过程 返回DataReader
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="dictionary">SqlParameter[]参数数组，允许空</param>
/// <returns></returns>
public static DbDataReader ExecuteReader(string commandTextOrSpName, CommandType commandType, Dictionary <string, object> dictionary)
{
//sqlDataReader不能用using 会关闭conn 导致不能获取到返回值。注意：DataReader获取值时必须保持连接状态
SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW);
DbCommandCommon cmd = new DbCommandCommon(dataBaseType);
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, dictionary);
return(cmd.DbCommand.ExecuteReader(CommandBehavior.CloseConnection));
}
/// <summary>
/// 执行sql语句或存储过程，返回受影响的行数。
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型 t</param>
/// <param name="dictionary">SqlParameter[]参数数组，允许空</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType, Dictionary <string, object> dictionary)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW))
{
using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType))
{
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, dictionary);//参数增加了commandType 可以自己编辑执行方式
return(cmd.DbCommand.ExecuteNonQuery());
}
}
}
/// <summary>
/// 执行sql语句或存储过程，返回受影响的行数,不带参数。
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型 有默认值CommandType.Text</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType = CommandType.Text)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW))
{
using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType))
{
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType);
return(cmd.DbCommand.ExecuteNonQuery());
}
}
}
/// <summary>
/// 执行sql语句或存储过程 返回ExecuteScalar （返回自增的ID）
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="dictionary">SqlParameter[]参数数组，允许空</param>
/// <returns></returns>
public static object ExecuteScalar(string commandTextOrSpName, CommandType commandType, Dictionary <string, object> dictionary)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW))
{
using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType))
{
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, dictionary);
return(cmd.DbCommand.ExecuteScalar());
}
}
}
/// <summary>
/// 执行sql语句或存储过程，返回受影响的行数。
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型 t</param>
/// <param name="parms">SqlParameter[]参数数组，允许空</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType, params SqlParameter[] parms)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(Options.Opt_DataBaseType.SqlServer, ConnString_RW))
{
using (SqlCommand cmd = new SqlCommand())
{
PreparCommand(conn.DbConnection as SqlConnection, cmd, commandTextOrSpName, commandType, parms);//参数增加了commandType 可以自己编辑执行方式
return(cmd.ExecuteNonQuery());
}
}
}
/// <summary>
/// 执行sql语句或存储过程 返回ExecuteScalar （返回自增的ID）
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="obj">object[]参数数组，允许空</param>
/// <returns></returns>
public static object ExecuteScalar(string commandTextOrSpName, CommandType commandType, params object[] obj)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(Options.Opt_DataBaseType.SqlServer, ConnString_RW))
{
using (SqlCommand cmd = new SqlCommand())
{
PreparCommand(conn.DbConnection as SqlConnection, cmd, commandTextOrSpName, commandType, obj);
return(cmd.ExecuteScalar());
}
}
}
/// <summary>
/// 执行sql语句或存储过程，返回DataSet
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="dictionary">SqlParameter[]参数数组，允许空</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType, Dictionary <string, object> dictionary)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW))
{
using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType))
{
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, dictionary);
using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand))
{
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
}
/// <summary>
/// 执行sql语句或存储过程，返回DataSet
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型 </param>
/// <param name="obj">object[]参数数组，允许空</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType, params object[] obj)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(Options.Opt_DataBaseType.SqlServer, ConnString_RW))
{
using (SqlCommand cmd = new SqlCommand())
{
PreparCommand(conn.DbConnection as SqlConnection, cmd, commandTextOrSpName, commandType, obj);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
}
/**
* Update At 2017-3-2 14:58:45
* Add the ExecuteDataTable Method into Sql_Helper_DG
**/
/// <summary>
/// 执行sql语句或存储过程，返回DataTable不带参数
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型 有默认值CommandType.Text</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string commandTextOrSpName, CommandType commandType = CommandType.Text)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW))
{
using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType))
{
PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType);
using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand))
{
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(default(DataTable));
}
}
}
}
/// <summary>
/// 执行sql语句或存储过程，返回DataTable
/// </summary>
/// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param>
/// <param name="commandTextOrSpName">sql语句或存储过程名称</param>
/// <param name="commandType">命令类型</param>
/// <param name="parms">SqlParameter[]参数数组，允许空</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string commandTextOrSpName, CommandType commandType, params SqlParameter[] parms)
{
using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(Options.Opt_DataBaseType.SqlServer, ConnString_RW))
{
using (SqlCommand cmd = new SqlCommand())
{
PreparCommand(conn.DbConnection as SqlConnection, cmd, commandTextOrSpName, commandType, parms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(default(DataTable));
}
}
}
}
