SQLServer助手类

来源：互联网发布：电子处方软件免费版编辑：程序博客网时间：2024/05/01 10:55
<pre name="code" class="csharp">using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace DAL{    /// <summary>    /// Date:    2012/6/10    /// Author: Lin_    /// </summary>    public class SQLHelper    {        private string connStr = null;        private SqlConnection conn = null;        private SqlCommand cmd = null;        private SqlDataReader sdr = null;        public SQLHelper()        {            //connStr=Web.config连接字符串            connStr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Db.mdf;Integrated Security=True;User Instance=True";            /*                 <connectionStrings>                  <add name="DbConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Db.mdf;Integrated Security=True;User Instance=True"                   providerName="System.Data.SqlClient" />                 </connectionStrings>             */            conn = new SqlConnection(connStr);        }        /// <summary>        /// 获取Connection对象        /// </summary>        /// <returns></returns>        private SqlConnection GetConn()        {            if (conn.State == ConnectionState.Closed)            {                conn.Open();            }            return conn;        }        /// <summary>        /// 执行查询        /// </summary>        /// <param name="cmdText">要执行的sql语句、或存储过程名</param>        /// <param name="ct">cmdType类型</param>        /// <returns></returns>        public DataTable ExecuteQuery(string cmdText,CommandType ct)        {            DataTable dt = new DataTable();            cmd = new SqlCommand(cmdText, GetConn());            cmd.CommandType = ct;            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))//关闭cmd同时关闭conn            {                dt.Load(sdr);            }            return dt;        }                /// <summary>        /// 执行带参数的查询        /// </summary>        /// <param name="cmdText">要执行的sql语句、或存储过程名</param>        /// <param name="para">Para参数</param>        /// <param name="ct">cmdType类型</param>        /// <returns></returns>        public DataTable ExecuteQuery(string cmdText, SqlParameter[] para,CommandType ct)        {            DataTable dt = new DataTable();            cmd = new SqlCommand(cmdText, GetConn());            cmd.CommandType = ct;            cmd.Parameters.AddRange(para);            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))//关闭cmd同时关闭conn            {                dt.Load(sdr);            }            return dt;        }                /// <summary>        /// 执行sql增删改        /// </summary>        /// <param name="cmdText">要执行的sql语句、或存储过程名</param>        /// <param name="ct">cmdType类型</param>        /// <returns></returns>        public int ExecuteNoQuery(string cmdText,CommandType ct)        {            int res;            cmd = new SqlCommand(cmdText, GetConn());            cmd.CommandType = ct;            try            {                res = cmd.ExecuteNonQuery();            }            catch (Exception ex)            {                throw ex;            }            finally            {                if (conn.State == ConnectionState.Open)                {                    conn.Close();                }            }            return res;        }        /// <summary>        /// 执行sql增删改        /// </summary>        /// <param name="cmdText">要执行的sql语句、或存储过程名</param>        /// <param name="para">Para参数</param>        /// <param name="ct">cmdType类型</param>        /// <returns></returns>        public int ExecuteNoQuery(string cmdText, SqlParameter[] para,CommandType ct)        {            int res;            cmd = new SqlCommand(cmdText, GetConn());            cmd.CommandType=ct;            cmd.Parameters.AddRange(para);            try            {                res = cmd.ExecuteNonQuery();            }            catch (Exception ex)            {                throw ex;            }            finally            {                if (conn.State == ConnectionState.Open)                {                    conn.Close();                }            }            return res;        }        /// <summary>        /// 执行返回单一数据的sql语句        /// </summary>        /// <param name="sql">要执行的sql语句</param>        /// <returns></returns>   /*    public object ExecuteScalar(string sql)        {            object res;            cmd = new SqlCommand(sql, GetConn());            try            {                res = cmd.ExecuteScalar();            }            catch (Exception ex)            {                throw ex;            }            finally            {                if (conn.State == ConnectionState.Open)                {                    conn.Close();                }            }            return res;        }  */    }}
大嘴巴：http://www.bigzb.com