- 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.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;
- }
- }
- }