SqlHelper类库
来源：互联网 发布：情感读本杂志软件 编辑：程序博客网 时间：2024/06/05 22:31
using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.IO;namespace BFB.DAL{ /// <summary> /// /// </summary> public class SqlHelper { /// <summary> /// /// </summary> private string strConnectionString = ""; /// <summary> /// 连库类 /// </summary> public static SqlConnection cnn; /// <summary> /// 执行类库 /// </summary> public SqlCommand com; /// <summary> /// /// </summary> public SqlHelper() { string server = File.ReadAllText("ServerIP.txt"); strConnectionString = string.Format(@"Data Source={0};uid=aaaa;pwd=123456;database=HundredMIS", server); } /// <summary> ///打开数据库连接 /// </summary> public void Open() { if (cnn == null) { cnn = new SqlConnection(strConnectionString); } if (cnn.State == ConnectionState.Closed) { try { cnn.Open(); } catch (Exception ex) { throw new Exception(ex.Message); } } } /// <summary> /// 关闭数据库连接 /// </summary> public void close() { if (cnn != null) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } } } /// <summary> /// 释放资源 /// </summary> public void Dispose() { // 确认连接是否已经关闭 if (cnn != null) { cnn.Dispose(); cnn = null; } } /// <summary> /// 返回影响行数 /// </summary> /// <param name="query"></param> /// <returns></returns> public int ExecuteNonQuery(string query) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } int retval; try { cnn.Open(); retval = cmd.ExecuteNonQuery(); } catch (Exception exp) { throw exp; } finally { if (cnn.State == ConnectionState.Open) { cnn.Close(); } } return retval; } /// <summary> /// 返回影响行数 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns></returns> public int ExecuteNonQuery(string query, params SqlParameter[] parameters) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } for (int i = 0; i <= parameters.Length - 1; i++) { cmd.Parameters.Add(parameters[i]); } cnn.Open(); int retval = cmd.ExecuteNonQuery(); cnn.Close(); return retval; } /// <summary> /// 查询第一行第一列 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns></returns> public object ExecuteScalar(string query) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } cnn.Open(); object retval = cmd.ExecuteScalar(); cnn.Close(); return retval; } public object ExecuteScalar(string query, params SqlParameter[] parameters) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } for (int i = 0; i <= parameters.Length - 1; i++) { cmd.Parameters.Add(parameters[i]); } cnn.Open(); object retval = cmd.ExecuteScalar(); cnn.Close(); return retval; } /// <summary> /// 使用Select查询，返回SqlDataReader类型 /// </summary> /// <param name="query">查询命令</param> /// <returns></returns> public SqlDataReader ExecuteReader(string query) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; cnn.Open(); } SqlDataReader dr; try { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dr; } catch (Exception ee) { cnn.Close(); throw ee; } } /// <summary> /// 使用存储过程查询，返回SqlDataReader类型 /// </summary> /// <param name="query">存储过程名</param> /// <param name="parameters"></param> /// <returns></returns> public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } for (int i = 0; i <= parameters.Length - 1; i++) { cmd.Parameters.Add(parameters[i]); } cnn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 使用Select查询，返回DataSet类型 /// </summary> /// <param name="query">查询命令</param> /// <returns></returns> public DataSet ExecuteDataSet(string query) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); return ds; } /// <summary> /// 使用存储过程查询，返回DataSet类型 /// </summary> /// <param name="query">存储过程名</param> /// <param name="parameters"></param> /// <returns></returns> public DataSet ExecuteDataSet(string query, params SqlParameter[] parameters) { cnn = new SqlConnection(strConnectionString); SqlCommand cmd = new SqlCommand(query, cnn); if (query.StartsWith("SELECT") | query.StartsWith("select")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } for (int i = 0; i <= parameters.Length - 1; i++) { cmd.Parameters.Add(parameters[i]); } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); return ds; } }}
