using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DBUtility
{
public class SQLHelper
{
public static string sqlConnString = ConfigurationManager.AppSettings[\"haha\"];
private static void PrepareCommand(
SqlCommand cmd, //SqlCommand对象
SqlConnection conn, //连接对象
SqlTransaction trans, //是否是事务
CommandType cmdType, //判断SqlCommand对象执行的类型(普通的Sql语句,存储过程)
string cmdText, //要执行的Sql语句
SqlParameter[] cmdParms //传递的参数
)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;//赋值连接对象
cmd.CommandText = cmdText;//赋值Sql语句
if (trans != null)//判断是否是事务
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;//赋值操作类型
if (cmdParms != null)//装参数
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
public static SqlDataReader ExecuteReader(
string connectionString,
CommandType cmdType,
string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//如果关闭关联的DataReader对象,则关联的Connection对象也将关闭
cmd.Parameters.Clear();
return rdr;
}
catch (Exception ex)
{
conn.Close();
throw ex;
}
}
public static object ExecuteScalar(
string connectionString,
CommandType cmdType,
string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
}
} |