c# SQLHelper(for winForm)实现代码

【 tulaoshi.com - 编程语言 】

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
/// returnsCommand对象/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;
}
}
}