自己写的SQLHelper类
宁老师版
Code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/**//// <summary>
/// 数据库基本操作类
/// </summary>
public class SQLHelper
{
//数据库连接字符串
private string connSTR;
//数据库连接对象
private SqlConnection myConnection;
//数据库操作命令对象
private SqlCommand myCommand;
/**//// <summary>
/// 构造函数,创建连接
/// </summary>
public SQLHelper()
{
connSTR = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
myConnection = new SqlConnection(connSTR);
}
执行带参数的sql语句(插入、删除、修改),返回-1表示执行失败#region 执行带参数的sql语句(插入、删除、修改),返回-1表示执行失败
public int ExcuSqlWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
//传递参数
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
//定义返回值
int nResult = -1;
try
{
//打开链接
myConnection.Open();
//执行SQL语句
nResult = myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
//抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭链接
myConnection.Close();
}
//返回nResult
return nResult;
}
#endregion
执行带参数的sql语句(select语句),返回数据流#region 执行带参数的sql语句(select语句),返回数据流
public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
/**////定义返回值
SqlDataReader dr = null;
try
{
/**////打开链接
myConnection.Open();
/**////执行SQL语句
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
return dr;
}
#endregion
执行带参数的sql语句(select语句),返回数据表#region 执行带参数的sql语句(select语句),返回数据表
/**//// <summary>
/// 执行带参数的sql语句(select语句),返回数据表
/// </summary>
/// <param name="cmdText">带参数的SQL语句</param>
/// <param name="para">参数列表</param>
public DataTable GetDTWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
/**////打开链接
myConnection.Open();
/**////执行SQL语句
da.SelectCommand = myCommand;
da.Fill(ds);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
//返回dr时不能关闭连接
finally
{ /**////关闭链接
myConnection.Close();
}
//返回nResult
return ds.Tables[0];
}
#endregion
执行不带参数的sql语句(select语句),返回数据表#region 执行不带参数的sql语句(select语句),返回数据表
public DataTable GetDataTable(string cmdText)
{
/**////定义返回值
DataTable dt = null;
try
{
myConnection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "Table1");
dt = ds.Tables["Table1"];
da.Dispose();
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
myConnection.Close();
}
return dt;
}
#endregion
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/**//// <summary>
/// 数据库基本操作类
/// </summary>
public class SQLHelper
{
//数据库连接字符串
private string connSTR;
//数据库连接对象
private SqlConnection myConnection;
//数据库操作命令对象
private SqlCommand myCommand;
/**//// <summary>
/// 构造函数,创建连接
/// </summary>
public SQLHelper()
{
connSTR = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
myConnection = new SqlConnection(connSTR);
}
执行带参数的sql语句(插入、删除、修改),返回-1表示执行失败#region 执行带参数的sql语句(插入、删除、修改),返回-1表示执行失败
public int ExcuSqlWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
//传递参数
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
//定义返回值
int nResult = -1;
try
{
//打开链接
myConnection.Open();
//执行SQL语句
nResult = myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
//抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭链接
myConnection.Close();
}
//返回nResult
return nResult;
}
#endregion
执行带参数的sql语句(select语句),返回数据流#region 执行带参数的sql语句(select语句),返回数据流
public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
/**////定义返回值
SqlDataReader dr = null;
try
{
/**////打开链接
myConnection.Open();
/**////执行SQL语句
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
return dr;
}
#endregion
执行带参数的sql语句(select语句),返回数据表#region 执行带参数的sql语句(select语句),返回数据表
/**//// <summary>
/// 执行带参数的sql语句(select语句),返回数据表
/// </summary>
/// <param name="cmdText">带参数的SQL语句</param>
/// <param name="para">参数列表</param>
public DataTable GetDTWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
myCommand = new SqlCommand(cmdText, myConnection);
for (int i = 0; i < para.Length; i++)
{
myCommand.Parameters.Add(para[i]);
}
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
/**////打开链接
myConnection.Open();
/**////执行SQL语句
da.SelectCommand = myCommand;
da.Fill(ds);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
//返回dr时不能关闭连接
finally
{ /**////关闭链接
myConnection.Close();
}
//返回nResult
return ds.Tables[0];
}
#endregion
执行不带参数的sql语句(select语句),返回数据表#region 执行不带参数的sql语句(select语句),返回数据表
public DataTable GetDataTable(string cmdText)
{
/**////定义返回值
DataTable dt = null;
try
{
myConnection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "Table1");
dt = ds.Tables["Table1"];
da.Dispose();
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
myConnection.Close();
}
return dt;
}
#endregion
}
我的
Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL
{
public class SQLHelp
{
//SqlConnection对象用于建立连接
SqlConnection mycon = new SqlConnection();
//SqlCommand对象用于建立命令
SqlCommand mycom = new SqlCommand();
//用于保存连接字符串
string strConnection = null;
//构造函数,初始化连接
public SQLHelp()
{
strConnection = ConfigurationManager.ConnectionStrings["SqlConnection_Chat"].ConnectionString;
mycon.ConnectionString = strConnection;
//mycon.ConnectionString = "Data Source=.;Initial Catalog=Chat;Integrated Security=True";
}
/**//// <summary>
/// 执行SQL语句或存储过程,返回数据集
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="para">参数数组</param>
/// <param name="type">执行类型</param>
/// <returns></returns>
public DataSet doExceutForDataset(string commandText, SqlParameter[] para, CommandType type)
{
//定义DataSet存储返回数据集
DataSet ds = new DataSet();
//定义command命令
mycom.CommandText = commandText;
//判断是否有参数,有则循环赋值
if (para != null)
{
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
}
//确定执行类型(SQL语句还是存储过程)
mycom.CommandType = type;
try
{
//打开连接
mycon.Open();
//为command命令指定连接
mycom.Connection = mycon;
//执行command命令
SqlDataAdapter da = new SqlDataAdapter(mycom);
//填充数据集
da.Fill(ds, "table1");
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
//关闭连接
mycon.Close();
}
return ds;
}
/**//// <summary>
/// 执行SQL语句或存储过程,返回受影响的行数
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="para">参数数组</param>
/// <param name="type">执行类型</param>
/// <returns></returns>
public int doExceutForRowCount(string commandText, SqlParameter[] para, CommandType type)
{
//定义result用于存储返回值
int result = -1;
//定义command命令
mycom.CommandText = commandText;
//判断是否有参数,有则循环赋值
if (para != null)
{
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
}
//确定执行类型(SQL语句还是存储过程)
mycom.CommandType = type;
try
{
//打开连接
mycon.Open();
//为command命令指定连接
mycom.Connection = mycon;
//执行命令,返回受影响的行数
result = mycom.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
//关闭连接
mycon.Close();
}
return result;
}
/**//// <summary>
/// 用户登录验证 成功返回true
/// </summary>
/// <param name="strUserName">用户名</param>
/// <param name="strPwd">密码</param>
/// <returns></returns>
public bool checkLogin(string strUserName, string strPwd)
{
bool flag = false;
int count = -1;
string strSql = "SELECT COUNT(*) FROM UserInfo WHERE UserName= '" + strUserName + "'and Pwd='" + strPwd + "'";
//Command命令
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
//执行Command命令
try
{
mycon.Open();
count = Convert.ToInt32(mycom.ExecuteScalar());
if (count == 0)
{
flag = false;
}
else
if (count > 0)
{
flag = true;
}
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return flag;
}
/**//// <summary>
/// 执行一条SQL语句,返回第一行第一列的元素
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string doSqlForFirst(string strSql)
{
string result = null;//记录返回元素
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
try
{
mycon.Open();
result = mycom.ExecuteScalar().ToString();
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return result;
}
/**//// <summary>
/// 执行一条SQL语句,返回受影响的行数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public int doSql(string strSql)
{
int result = -1;//记录受影响的行数
//Command命令
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
//执行Command命令
try
{
mycon.Open();
result = Convert.ToInt32(mycom.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return result;
}
执行带参数的sql语句(select语句),返回数据流#region 执行带参数的sql语句(select语句),返回数据流
/**//// <summary>
/// 执行带参数的sql语句(select语句),返回数据流
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="para">参数</param>
/// <returns></returns>
public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
mycom = new SqlCommand(cmdText, mycon);
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
/**////定义返回值
SqlDataReader dr = null;
try
{
/**////打开链接
mycon.Open();
/**////执行SQL语句
dr = mycom.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
return dr;
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL
{
public class SQLHelp
{
//SqlConnection对象用于建立连接
SqlConnection mycon = new SqlConnection();
//SqlCommand对象用于建立命令
SqlCommand mycom = new SqlCommand();
//用于保存连接字符串
string strConnection = null;
//构造函数,初始化连接
public SQLHelp()
{
strConnection = ConfigurationManager.ConnectionStrings["SqlConnection_Chat"].ConnectionString;
mycon.ConnectionString = strConnection;
//mycon.ConnectionString = "Data Source=.;Initial Catalog=Chat;Integrated Security=True";
}
/**//// <summary>
/// 执行SQL语句或存储过程,返回数据集
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="para">参数数组</param>
/// <param name="type">执行类型</param>
/// <returns></returns>
public DataSet doExceutForDataset(string commandText, SqlParameter[] para, CommandType type)
{
//定义DataSet存储返回数据集
DataSet ds = new DataSet();
//定义command命令
mycom.CommandText = commandText;
//判断是否有参数,有则循环赋值
if (para != null)
{
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
}
//确定执行类型(SQL语句还是存储过程)
mycom.CommandType = type;
try
{
//打开连接
mycon.Open();
//为command命令指定连接
mycom.Connection = mycon;
//执行command命令
SqlDataAdapter da = new SqlDataAdapter(mycom);
//填充数据集
da.Fill(ds, "table1");
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
//关闭连接
mycon.Close();
}
return ds;
}
/**//// <summary>
/// 执行SQL语句或存储过程,返回受影响的行数
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="para">参数数组</param>
/// <param name="type">执行类型</param>
/// <returns></returns>
public int doExceutForRowCount(string commandText, SqlParameter[] para, CommandType type)
{
//定义result用于存储返回值
int result = -1;
//定义command命令
mycom.CommandText = commandText;
//判断是否有参数,有则循环赋值
if (para != null)
{
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
}
//确定执行类型(SQL语句还是存储过程)
mycom.CommandType = type;
try
{
//打开连接
mycon.Open();
//为command命令指定连接
mycom.Connection = mycon;
//执行命令,返回受影响的行数
result = mycom.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
//关闭连接
mycon.Close();
}
return result;
}
/**//// <summary>
/// 用户登录验证 成功返回true
/// </summary>
/// <param name="strUserName">用户名</param>
/// <param name="strPwd">密码</param>
/// <returns></returns>
public bool checkLogin(string strUserName, string strPwd)
{
bool flag = false;
int count = -1;
string strSql = "SELECT COUNT(*) FROM UserInfo WHERE UserName= '" + strUserName + "'and Pwd='" + strPwd + "'";
//Command命令
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
//执行Command命令
try
{
mycon.Open();
count = Convert.ToInt32(mycom.ExecuteScalar());
if (count == 0)
{
flag = false;
}
else
if (count > 0)
{
flag = true;
}
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return flag;
}
/**//// <summary>
/// 执行一条SQL语句,返回第一行第一列的元素
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string doSqlForFirst(string strSql)
{
string result = null;//记录返回元素
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
try
{
mycon.Open();
result = mycom.ExecuteScalar().ToString();
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return result;
}
/**//// <summary>
/// 执行一条SQL语句,返回受影响的行数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public int doSql(string strSql)
{
int result = -1;//记录受影响的行数
//Command命令
mycom = new SqlCommand();
mycom.Connection = mycon;
mycom.CommandText = strSql;
//执行Command命令
try
{
mycon.Open();
result = Convert.ToInt32(mycom.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
mycon.Close();
}
return result;
}
执行带参数的sql语句(select语句),返回数据流#region 执行带参数的sql语句(select语句),返回数据流
/**//// <summary>
/// 执行带参数的sql语句(select语句),返回数据流
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="para">参数</param>
/// <returns></returns>
public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)
{
//创建Command
mycom = new SqlCommand(cmdText, mycon);
for (int i = 0; i < para.Length; i++)
{
mycom.Parameters.Add(para[i]);
}
/**////定义返回值
SqlDataReader dr = null;
try
{
/**////打开链接
mycon.Open();
/**////执行SQL语句
dr = mycom.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
/**////抛出异常
throw new Exception(ex.Message, ex);
}
return dr;
}
#endregion
}
}
posted on 2009-11-13 09:46 jay.windows 阅读(5556) 评论(1) 编辑 收藏 举报