asp.net DBHelper类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ZhiDianDAL
{
public class DBHelper
{
/// <summary>
/// 数据库连接对象
/// </summary>
private SqlConnection con;
public DBHelper()
{
con = CreateConnection();
}
/// <summary>
/// 创建数据库连接对象
/// </summary>
/// <returns></returns>
public SqlConnection CreateConnection()
{
string conStr = ConfigurationManager.ConnectionStrings["sqlconnectionstring"].ToString();
return new SqlConnection(conStr);
}
/// <summary>
/// 创建执行存储过程的command对象
/// </summary>
/// <param name="storedProcedure"></param>
/// <returns></returns>
public SqlCommand GetStoredProcCommand(string procName)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
/// <summary>
/// 创建执行普通sql语句的command对象
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public SqlCommand GetSqlStringCommand(string sqlStr)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
return cmd;
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="dbParameterCollection"></param>
public void AddParameter(SqlCommand cmd, List<SqlParameter> list)
{
foreach (SqlParameter param in list)
{
cmd.Parameters.Add(param);
}
}
/// <summary>
/// 给存储过程增加输入参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
public void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)
{
SqlParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 给存储过程增加返回参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
public void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)
{
SqlParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 取得参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <returns></returns>
public SqlParameter GetParameter(SqlCommand cmd, string parameterName)
{
return cmd.Parameters[parameterName];
}
/// <summary>
/// 执行返回DataTable
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(SqlCommand cmd)
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行返回受影响行数
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public int ExecuteNonQuery(SqlCommand cmd)
{
int count = 0;
try
{
cmd.Connection.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return count;
}
/// <summary>
/// 执行返回首行首列的值
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public object ExecuteScalar(SqlCommand cmd)
{
Object obj = null;
try
{
cmd.Connection.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return obj;
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ZhiDianDAL
{
public class DBHelper
{
/// <summary>
/// 数据库连接对象
/// </summary>
private SqlConnection con;
public DBHelper()
{
con = CreateConnection();
}
/// <summary>
/// 创建数据库连接对象
/// </summary>
/// <returns></returns>
public SqlConnection CreateConnection()
{
string conStr = ConfigurationManager.ConnectionStrings["sqlconnectionstring"].ToString();
return new SqlConnection(conStr);
}
/// <summary>
/// 创建执行存储过程的command对象
/// </summary>
/// <param name="storedProcedure"></param>
/// <returns></returns>
public SqlCommand GetStoredProcCommand(string procName)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
/// <summary>
/// 创建执行普通sql语句的command对象
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public SqlCommand GetSqlStringCommand(string sqlStr)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
return cmd;
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="dbParameterCollection"></param>
public void AddParameter(SqlCommand cmd, List<SqlParameter> list)
{
foreach (SqlParameter param in list)
{
cmd.Parameters.Add(param);
}
}
/// <summary>
/// 给存储过程增加输入参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
public void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)
{
SqlParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 给存储过程增加返回参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
public void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)
{
SqlParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 取得参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameterName"></param>
/// <returns></returns>
public SqlParameter GetParameter(SqlCommand cmd, string parameterName)
{
return cmd.Parameters[parameterName];
}
/// <summary>
/// 执行返回DataTable
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(SqlCommand cmd)
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行返回受影响行数
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public int ExecuteNonQuery(SqlCommand cmd)
{
int count = 0;
try
{
cmd.Connection.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return count;
}
/// <summary>
/// 执行返回首行首列的值
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public object ExecuteScalar(SqlCommand cmd)
{
Object obj = null;
try
{
cmd.Connection.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return obj;
}
}
}