自己封装的MSSQL的数据库操作类
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
/*
* 作者: 牛腩
* 创建时间: 2010-1-20 10:43:14
* Email: 164423073@qq.com
* 说明: 对MSSQL数据库的增删查改操作的封装类
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace Niunan.Tg029.Hr.Utility
{
public class MSSQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public MSSQLHelper()
{
string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
conn = new SqlConnection(connStr);
}
/// <summary>创建Command对象
/// 默认是SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new SqlCommand(sql, conn);
}
/// <summary>创建存储过程的Command对象
///
/// </summary>
/// <param name="procName">存储过程名称</param>
public void CreateStoredCommand(string procName)
{
conn.Open();
cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>添加参数
/// 默认是输入参数
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
SqlParameter p = new SqlParameter(paramName, value);
cmd.Parameters.Add(p);
}
/// <summary>添加输出参数
/// 用于存储过程
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddOutputParameter(string paramName) {
SqlParameter p = new SqlParameter();
p.ParameterName = paramName;
p.Direction = ParameterDirection.Output;
p.Size = 20;
cmd.Parameters.Add(p);
}
/// <summary>获取输出参数的值
///
/// </summary>
/// <param name="paramName">输出参数名称</param>
/// <returns></returns>
public string GetOutputParameter(string paramName) {
return cmd.Parameters[paramName].Value.ToString();
}
/// <summary>执行增删改SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句或存储过程查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
* 作者: 牛腩
* 创建时间: 2010-1-20 10:43:14
* Email: 164423073@qq.com
* 说明: 对MSSQL数据库的增删查改操作的封装类
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace Niunan.Tg029.Hr.Utility
{
public class MSSQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public MSSQLHelper()
{
string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
conn = new SqlConnection(connStr);
}
/// <summary>创建Command对象
/// 默认是SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new SqlCommand(sql, conn);
}
/// <summary>创建存储过程的Command对象
///
/// </summary>
/// <param name="procName">存储过程名称</param>
public void CreateStoredCommand(string procName)
{
conn.Open();
cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>添加参数
/// 默认是输入参数
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
SqlParameter p = new SqlParameter(paramName, value);
cmd.Parameters.Add(p);
}
/// <summary>添加输出参数
/// 用于存储过程
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddOutputParameter(string paramName) {
SqlParameter p = new SqlParameter();
p.ParameterName = paramName;
p.Direction = ParameterDirection.Output;
p.Size = 20;
cmd.Parameters.Add(p);
}
/// <summary>获取输出参数的值
///
/// </summary>
/// <param name="paramName">输出参数名称</param>
/// <returns></returns>
public string GetOutputParameter(string paramName) {
return cmd.Parameters[paramName].Value.ToString();
}
/// <summary>执行增删改SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句或存储过程查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
先记下来,以备不时之需!!!
撸码:复制、粘贴,拿起键盘就是“干”!!!