下面就是详细的sqlhelper的代码了
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data; namespace hr_DAL
{
public class SqlHelper
{
//连接对象和命令对象
private static SqlConnection conn;
private static SqlCommand cmd;
private static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
/// <summary>
/// 构造函数,实例化一个空的SqlHelper对象
/// </summary>
public SqlHelper()
{
}
#region 共有方法
/// <summary>
///方法:实例化连接对象
/// </summary>
private static void con()
{
conn = new SqlConnection(constr);
}
/// <summary>
/// 方法:打开连接,实例化命令对象
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private static SqlCommand ExtCommand(string sql)
{
con();
//创建一个connection对象打开时,
//就会创建一个连接池,连接池和连接字符串完全匹配.
//如果一个连接对象的连接字符串和连接池完全匹配,就会把该连接对象放到匹配的池中,
//如果不完全匹配,则再创建一个连接池
conn.Open(); cmd = conn.CreateCommand();//将连接给命令对象
cmd.CommandText = sql;
return cmd; }
/// <summary>
/// 方法:关闭连接,释放资源
/// </summary>
private static void CloseConn()
{
if(conn!=null)//连接池是否为null,是否占用连接
{
if (conn.State == ConnectionState.Open)
{
conn.Close();//关闭了连接,
cmd.Dispose();//清空了连接字符串,即连接池
conn.Dispose();
}
} }
#endregion
/// <summary>
/// 方法:得到执行结果的首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static string GetOne(string sql)
{
ExtCommand(sql);
string result= (string) cmd.ExecuteScalar();
CloseConn();
return result;
} /// <summary>
/// 方法:用SqlDataAdapter得到一个表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetTable(string sql)
{
ExtCommand(sql);
SqlDataAdapter sda = new SqlDataAdapter();
//将cmd命令对象交给适配器对象
sda.SelectCommand = ExtCommand(sql);
DataSet ds = new DataSet();
sda.Fill(ds,"table");
CloseConn();
return ds.Tables["table"]; #region SqlDataAdapter的使用方法
//SqlConnection conn = new SqlConnection(constr);
//SqlCommand cmd = new SqlCommand("select * from product", conn); //SqlDataAdapter sdat = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet(); //sdat.Fill(ds, "p");
//dataGridView1.DataSource = ds.Tables["p"];
#endregion }/// <summary>
/// 方法:根据条件返回一个数据流
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
ExtCommand(sql);
//CommandBehavior.CloseConnection表示数据流关闭,连接也关闭
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr; }
}
}