using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace LiBangWuLiu.leiFile
{
public class SqlHelper
{
public SqlHelper()
{
}
private static SqlConnection conn = new SqlConnection();
private static SqlCommand cmd = new SqlCommand();
public static string connstr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
/// <summary>
/// 打开连接
/// </summary>
public static void OpenConnection()
{
if (conn.State == ConnectionState.Closed)
{
string connString = connstr;
conn.ConnectionString = connString;
cmd.Connection = conn;
conn.Open();
}
}
/// <summary>
/// 关闭连接
/// </summary>
public static void CloseConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
/// 增,删,改
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static int GetExecuteNonQuery(string sqlStr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
int result = cmd.ExecuteNonQuery();
CloseConnection();
return result;
}
/// <summary>
/// 执行一条语句 返回一条值,用于Insert Update
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetExecuteNonQuery(string sqlStr, params SqlParameter[] values)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
CloseConnection();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 执行一条sql语句,用于执行查询select count(*);
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static int GetExecuteScalar(string sqlstr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
int result=(int)cmd.ExecuteScalar();
CloseConnection();
return result;
}
/// <summary>
/// 遍历数据库中的数据,逐条显示出来 调用形式
/// string sqlStr = "select UserInfo_Password,UserInfo_Sex from UserInfo where UserInfo_Name = '" + Session["name"].ToString() + "'";
///SqlDataReader reader = SqlHelper.GetDataReader(sqlStr);
///if (reader.HasRows)
///{
/// while (reader.Read())
/// {
/// lblUserInfo_Name.Text = Session["name"].ToString();
/// txtNewPassword.Text = (int)reader["UserInfo_Password"].ToString();
/// break;跳出循环,只读取第一条记录,防止可能有多条记录
/// }
///}
///reader.Close();//关闭读取器
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static SqlDataReader GetExecuteReader(string sqlStr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
SqlDataReader reader = cmd.ExecuteReader();
//这里不能关闭连接CloseConnection(),要在调用中关闭
return reader;
}
//返回DataSet
public static DataSet GetDataSet(string sqlStr)
{
SqlDataAdapter da = new SqlDataAdapter();
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
CloseConnection();
return ds;//返回DataSet对象
}
}
}
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace LiBangWuLiu.leiFile
{
public class SqlHelper
{
public SqlHelper()
{
}
private static SqlConnection conn = new SqlConnection();
private static SqlCommand cmd = new SqlCommand();
public static string connstr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
/// <summary>
/// 打开连接
/// </summary>
public static void OpenConnection()
{
if (conn.State == ConnectionState.Closed)
{
string connString = connstr;
conn.ConnectionString = connString;
cmd.Connection = conn;
conn.Open();
}
}
/// <summary>
/// 关闭连接
/// </summary>
public static void CloseConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
/// <summary>
/// 增,删,改
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static int GetExecuteNonQuery(string sqlStr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
int result = cmd.ExecuteNonQuery();
CloseConnection();
return result;
}
/// <summary>
/// 执行一条语句 返回一条值,用于Insert Update
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetExecuteNonQuery(string sqlStr, params SqlParameter[] values)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
CloseConnection();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 执行一条sql语句,用于执行查询select count(*);
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static int GetExecuteScalar(string sqlstr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
int result=(int)cmd.ExecuteScalar();
CloseConnection();
return result;
}
/// <summary>
/// 遍历数据库中的数据,逐条显示出来 调用形式
/// string sqlStr = "select UserInfo_Password,UserInfo_Sex from UserInfo where UserInfo_Name = '" + Session["name"].ToString() + "'";
///SqlDataReader reader = SqlHelper.GetDataReader(sqlStr);
///if (reader.HasRows)
///{
/// while (reader.Read())
/// {
/// lblUserInfo_Name.Text = Session["name"].ToString();
/// txtNewPassword.Text = (int)reader["UserInfo_Password"].ToString();
/// break;跳出循环,只读取第一条记录,防止可能有多条记录
/// }
///}
///reader.Close();//关闭读取器
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static SqlDataReader GetExecuteReader(string sqlStr)
{
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
SqlDataReader reader = cmd.ExecuteReader();
//这里不能关闭连接CloseConnection(),要在调用中关闭
return reader;
}
//返回DataSet
public static DataSet GetDataSet(string sqlStr)
{
SqlDataAdapter da = new SqlDataAdapter();
OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
CloseConnection();
return ds;//返回DataSet对象
}
}
}