using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
public class DBHelper
{
static SqlConnection conn;
public DBHelper()
{
conn = new SqlConnection("");
}
public static int ExecuteSqlReturnInt(string cmdText, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(conn, cmd, cmdText, cmdParms);
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
cmd.Parameters.Clear();
return result;
}
public static SqlDataReader getDataReader(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(conn, cmd, cmdText, cmdParms);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch
{
conn.Close();
throw;
}
}
public static DataSet getDataSetPro(string cmdStr, params SqlParameter[] cmdParms)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
PrepareCommandPro(con, cmd, cmdStr, cmdParms);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
cmd.Parameters.Clear();
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
//判断连接的状态。如果是关闭状态,则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
//添加cmd需要的存储过程参数
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
public class DBHelper
{
static SqlConnection conn;
public DBHelper()
{
conn = new SqlConnection("");
}
public static int ExecuteSqlReturnInt(string cmdText, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(conn, cmd, cmdText, cmdParms);
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
cmd.Parameters.Clear();
return result;
}
public static SqlDataReader getDataReader(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(conn, cmd, cmdText, cmdParms);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch
{
conn.Close();
throw;
}
}
public static DataSet getDataSetPro(string cmdStr, params SqlParameter[] cmdParms)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
PrepareCommandPro(con, cmd, cmdStr, cmdParms);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
cmd.Parameters.Clear();
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
//判断连接的状态。如果是关闭状态,则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
//添加cmd需要的存储过程参数
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
}
}