C#.NET中现在用的SqlHelper操作方法集合【收藏版】

http://www.cnblogs.com/nianyuwen/

  1 http://www.cnblogs.com/nianyuwen/
  2 
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Linq;
  6 using System.Text;
  7 using System.Data;
  8 using System.Data.SqlClient;
  9 using System.Configuration;
 10 namespace MSBDAL
 11 {
 12     /*
 13      * Descrption：数据库常用操作方法合集
 14      * Author：nianyuwen
 15      * Time：2012-03-15
 16      * MSN:nianyuwen@live.com
 17      * WebUrl:http://www.cnblogs.com/nianyuwen/
 18      */
 19    public  class SqlHelper
 20     {
 21        public SqlHelper()
 22        {
 23            //TODO:构造数据库常用操作方法集
 24        }
 25        private static SqlConnection connection;
 26        private static string connectionString;
 27        public static SqlConnection Connection
 28        {
 29            get
 30            {
 31                //string connectionString = ConfigurationManager.ConnectionStrings["stock"].ConnectionString;
 32                //"Data Source=.;Initial Catalog=StockBank;Persist Security Info=True;User ID=sa;pwd=123";
 33                connectionString = ConfigurationSettings.AppSettings["ConstrSQL"].ToString();
 34                
 35                if (connection == null)
 36                {
 37                    connection = new SqlConnection(connectionString);
 38                    connection.Open();
 39                }
 40                else if (connection.State == System.Data.ConnectionState.Closed)
 41                {
 42                    connection.Open();
 43                }
 44                else if (connection.State == System.Data.ConnectionState.Broken)
 45                {
 46                    connection.Close();
 47                    connection.Open();
 48                }
 49                return connection;
 50            }
 51        }
 52 
 53        public static int ExecuteCommand(string safeSql)
 54        {
 55            SqlCommand cmd = new SqlCommand(safeSql, Connection);
 56            int result = cmd.ExecuteNonQuery();
 57            return result;
 58        }
 59       /// <summary>
 60       /// 带参数的执行命令
 61       /// </summary>
 62       /// <param name="sql">SQL命令</param>
 63       /// <param name="values">返回VALUE值</param>
 64       /// <returns></returns>
 65        public static int ExecuteCommand(string sql, params SqlParameter[] values)
 66        {
 67            SqlCommand cmd = new SqlCommand(sql, Connection);
 68            cmd.Parameters.AddRange(values);
 69            return cmd.ExecuteNonQuery();
 70        }
 71        /// <summary>
 72        /// 返回影响记录数
 73        /// </summary>
 74        /// <param name="safeSql"></param>
 75        /// <returns></returns>
 76        public static int GetScalar(string safeSql)
 77        {
 78            SqlCommand cmd = new SqlCommand(safeSql, Connection);
 79            int result = Convert.ToInt32(cmd.ExecuteScalar());
 80            return result;
 81        }
 82 
 83        public static int GetScalar(string sql, params SqlParameter[] values)
 84        {
 85            SqlCommand cmd = new SqlCommand(sql, Connection);
 86            cmd.Parameters.AddRange(values);
 87            int result = Convert.ToInt32(cmd.ExecuteScalar());
 88            return result;
 89        }
 90 
 91        public static SqlDataReader GetReader(string safeSql)
 92        {
 93            SqlCommand cmd = new SqlCommand(safeSql, Connection);
 94            SqlDataReader reader = cmd.ExecuteReader();
 95            return reader;
 96        }
 97 
 98        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
 99        {
100            SqlCommand cmd = new SqlCommand(sql, Connection);
101            cmd.Parameters.AddRange(values);
102            SqlDataReader reader = cmd.ExecuteReader();
103            return reader;
104        }
105 
106        public static DataTable GetDataSet(string safeSql)
107        {
108            DataSet ds = new DataSet();
109            SqlCommand cmd = new SqlCommand(safeSql, Connection);
110            SqlDataAdapter da = new SqlDataAdapter(cmd);
111            da.Fill(ds);
112            return ds.Tables[0];
113        }
114 
115        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
116        {
117            DataSet ds = new DataSet();
118            SqlCommand cmd = new SqlCommand(sql, Connection);
119            cmd.Parameters.AddRange(values);
120            SqlDataAdapter da = new SqlDataAdapter(cmd);
121            da.Fill(ds);
122            return ds.Tables[0];
123        }
124 
125        /// <summary>
126        /// 获得数据集
127        /// </summary>
128        public static DataSet GetDataSet(string sql, string table)
129        {
130            DataSet ds = new DataSet();
131            SqlCommand cmd = new SqlCommand(sql, Connection);
132            SqlDataAdapter da = new SqlDataAdapter(cmd);
133            da.Fill(ds, table);
134            return ds;
135        }
136 
137        public static DataSet GetDataSet(string sql, string table, params SqlParameter[] values)
138        {
139            DataSet ds = new DataSet();
140            SqlCommand cmd = new SqlCommand(sql, Connection);
141            cmd.Parameters.AddRange(values);
142            SqlDataAdapter da = new SqlDataAdapter(cmd);
143            da.Fill(ds, table);
144            return ds;
145        }
146     }
147 }

 

 

posted @ 2012-04-27 14:08  念余温  阅读(7085)  评论(1)  编辑  收藏  举报