c# SQLHelper(for winForm)实现代码

sqlhelper.cs

using system;
using system.collections.generic;
using system.text;
using system.collections;
using system.data;
using system.data.sqlclient;
using system.configuration;
namespace hellowinform.dbutility
{
class sqlhelper
{
#region 通用方法
// 数据连接池
private sqlconnection con;
/// <summary>
/// 返回数据库连接字符串
/// </summary>
/// <returns></returns>
public static string getsqlconnection()
{
string conn = configurationmanager.appsettings["connectionstring"].tostring();
return conn;
}
#endregion
#region 执行sql字符串
/// <summary>
/// 执行不带参数的sql语句
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static int executesql(string sqlstr)
{
string connstr = getsqlconnection();
using (sqlconnection conn = new sqlconnection(connstr))
{
sqlcommand cmd = new sqlcommand();
cmd.connection = conn;
cmd.commandtext = sqlstr;
conn.open();
cmd.executenonquery();
conn.close();
return 1;
}
}
/// <summary>
/// 执行带参数的sql语句
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <param name="param">参数对象数组</param>
/// <returns></returns>
public static int executesql(string sqlstr, sqlparameter[] param)
{
string connstr = getsqlconnection();
using (sqlconnection conn = new sqlconnection(connstr))
{
sqlcommand cmd = new sqlcommand();
cmd.connection = conn;
cmd.commandtext = sqlstr;
cmd.parameters.addrange(param);
conn.open();
cmd.executenonquery();
conn.close();
return 1;
}
}
/// <summary>
/// 返回datareader
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static sqldatareader executereader(string sqlstr)
{
string connstr = getsqlconnection();
sqlconnection conn = new sqlconnection(connstr);//返回datareader时,是不可以用using()的
try
{
sqlcommand cmd = new sqlcommand();
cmd.connection = conn;
cmd.commandtext = sqlstr;
conn.open();
return cmd.executereader(system.data.commandbehavior.closeconnection);//关闭关联的connection
}
catch //(exception ex)
{
return null;
}
}
/// <summary>
/// 执行sql语句并返回数据表
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns></returns>
public static datatable executedt(string sqlstr)
{
string connstr = getsqlconnection();
using (sqlconnection conn = new sqlconnection(connstr))
{
sqldataadapter da = new sqldataadapter(sqlstr, conn);
datatable dt = new datatable();
conn.open();
da.fill(dt);
conn.close();
return dt;
}
}
/// <summary>
/// 执行sql语句并返回dataset
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns></returns>
public static dataset executeds(string sqlstr)
{
string connstr = getsqlconnection();
using (sqlconnection conn = new sqlconnection(connstr))
{
sqldataadapter da = new sqldataadapter(sqlstr, conn);
dataset ds = new dataset();
conn.open();
da.fill(ds);
conn.close();
return ds;
}
}
#endregion
#region 操作存储过程
/// <summary>
/// 运行存储过程(已重载)
/// </summary>
/// <param name="procname">存储过程的名字</param>
/// <returns>存储过程的返回值</returns>
public int runproc(string procname)
{
sqlcommand cmd = createcommand(procname, null);
cmd.executenonquery();
this.close();
return (int)cmd.parameters["returnvalue"].value;
}
/// <summary>
/// 运行存储过程(已重载)
/// </summary>
/// <param name="procname">存储过程的名字</param>
/// <param name="prams">存储过程的输入参数列表</param>
/// <returns>存储过程的返回值</returns>
public int runproc(string procname, sqlparameter[] prams)
{
sqlcommand cmd = createcommand(procname, prams);
cmd.executenonquery();
this.close();
return (int)cmd.parameters[0].value;
}
/// <summary>
/// 运行存储过程(已重载)
/// </summary>
/// <param name="procname">存储过程的名字</param>
/// <param name="datareader">结果集</param>
public void runproc(string procname, out sqldatareader datareader)
{
sqlcommand cmd = createcommand(procname, null);
datareader = cmd.executereader(system.data.commandbehavior.closeconnection);
}
/// <summary>
/// 运行存储过程(已重载)
/// </summary>
/// <param name="procname">存储过程的名字</param>
/// <param name="prams">存储过程的输入参数列表</param>
/// <param name="datareader">结果集</param>
public void runproc(string procname, sqlparameter[] prams, out sqldatareader datareader)
{
sqlcommand cmd = createcommand(procname, prams);
datareader = cmd.executereader(system.data.commandbehavior.closeconnection);
}
/// <summary>
/// 创建command对象用于访问存储过程
/// </summary>
/// <param name="procname">存储过程的名字</param>
/// <param name="prams">存储过程的输入参数列表</param>
/// <returns>command对象</returns>
private sqlcommand createcommand(string procname, sqlparameter[] prams)
{
// 确定连接是打开的
open();
//command = new sqlcommand( sprocname, new sqlconnection( configmanager.dalconnectionstring ) );
sqlcommand cmd = new sqlcommand(procname, con);
cmd.commandtype = commandtype.storedprocedure;
// 添加存储过程的输入参数列表
if (prams != null)
{
foreach (sqlparameter parameter in prams)
cmd.parameters.add(parameter);
}
// 返回command对象
return cmd;
}
/// <summary>
/// 创建输入参数
/// </summary>
/// <param name="paramname">参数名</param>
/// <param name="dbtype">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="value">参数值</param>
/// <returns>新参数对象</returns>
public sqlparameter makeinparam(string paramname, sqldbtype dbtype, int size, object value)
{
return makeparam(paramname, dbtype, size, parameterdirection.input, value);
}
/// <summary>
/// 创建输出参数
/// </summary>
/// <param name="paramname">参数名</param>
/// <param name="dbtype">参数类型</param>
/// <param name="size">参数大小</param>
/// <returns>新参数对象</returns>
public sqlparameter makeoutparam(string paramname, sqldbtype dbtype, int size)
{
return makeparam(paramname, dbtype, size, parameterdirection.output, null);
}
/// <summary>
/// 创建存储过程参数
/// </summary>
/// <param name="paramname">参数名</param>
/// <param name="dbtype">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="direction">参数的方向(输入/输出)</param>
/// <param name="value">参数值</param>
/// <returns>新参数对象</returns>
public sqlparameter makeparam(string paramname, sqldbtype dbtype, int32 size, parameterdirection direction, object value)
{
sqlparameter param;
if (size > 0)
{
param = new sqlparameter(paramname, dbtype, size);
}
else
{
param = new sqlparameter(paramname, dbtype);
}
param.direction = direction;
if (!(direction == parameterdirection.output && value == null))
{
param.value = value;
}
return param;
}
#endregion
#region 数据库连接和关闭
/// <summary>
/// 打开连接池
/// </summary>
private void open()
{
// 打开连接池
if (con == null)
{
//这里不仅需要using system.configuration;还要在引用目录里添加
con = new sqlconnection(getsqlconnection());
con.open();
}
}
/// <summary>
/// 关闭连接池
/// </summary>
public void close()
{
if (con != null)
con.close();
}
/// <summary>
/// 释放连接池
/// </summary>
public void dispose()
{
// 确定连接已关闭
if (con != null)
{
con.dispose();
con = null;
}
}
#endregion
}
}

简单用一下:

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.data.sqlclient;
using system.collections;
using hellowinform.dbutility;
namespace hellowinform.dal
{
class student
{
public string test()
{
string str = "";
sqldatareader dr = sqlhelper.executereader("select * from student");
while (dr.read())
{
str += dr["studentno"].tostring();
}
dr.close();
return str;
}
}
}