DBHelper的一个小例子

跟着教程做网站时的一个DBHelper的小例子。记下来说不定以后用得上。

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Configuration;
  7 
  8 
  9 namespace PracticeMyBookShopDAL
 10 {
 11     public static class DBHelper
 12     {
 13 
 14         private static SqlConnection connection;
 15         public static SqlConnection Connection
 16         {
 17             get
 18             {
 19                 string connectionString = "Data Source=ADMIN-PC;Initial Catalog=MyBookShop;Integrated Security=True";
 20                 if (connection == null)
 21                 {
 22                     connection = new SqlConnection(connectionString);
 23                     connection.Open();
 24                 }
 25                 else if (connection.State == System.Data.ConnectionState.Closed)
 26                 {
 27                     connection.Open();
 28                 }
 29                 else if (connection.State == System.Data.ConnectionState.Broken)
 30                 {
 31                     connection.Close();
 32                     connection.Open();
 33                 }
 34                 return connection;
 35             }
 36         }
 37 
 38         public static int ExecuteCommand(string safeSql)
 39         {
 40             SqlCommand cmd = new SqlCommand(safeSql, Connection);
 41             int result = cmd.ExecuteNonQuery();
 42             return result;
 43         }
 44 
 45         public static int ExecuteCommand(string sql, SqlParameter[] values)
 46         {
 47             SqlCommand cmd = new SqlCommand(sql, Connection);
 48             cmd.Parameters.AddRange(values);
 49             return cmd.ExecuteNonQuery();
 50         }
 51 
 52         public static int ExecuteCommand(string sql, SqlParameter value)
 53         {
 54             SqlCommand cmd = new SqlCommand(sql, Connection);
 55             cmd.Parameters.Add(value);
 56             int result = cmd.ExecuteNonQuery();
 57             return result;
 58         }
 59 
 60 
 61         public static int ExecuteScalar(string safeSql)
 62         {
 63             SqlCommand cmd = new SqlCommand(safeSql, Connection);
 64             int result = (int)cmd.ExecuteScalar();
 65             return result;
 66         }
 67 
 68         public static int ExecuteScalar(string sql, SqlParameter[] values)
 69         {
 70             SqlCommand cmd = new SqlCommand(sql, Connection);
 71             cmd.Parameters.AddRange(values);
 72             int result = (int)cmd.ExecuteScalar();
 73             return result;
 74         }
 75 
 76         public static int ExecuteScalar(string sql, SqlParameter value)
 77         {
 78             SqlCommand cmd = new SqlCommand(sql, Connection);
 79             cmd.Parameters.Add(value);
 80             int result = (int)cmd.ExecuteScalar();
 81             return result;
 82         }
 83 
 84         public static SqlDataReader ExecuteReader(string safeSql)
 85         {
 86             SqlCommand cmd = new SqlCommand(safeSql, Connection);
 87             SqlDataReader reader = cmd.ExecuteReader();
 88             return reader;
 89         }
 90 
 91         public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
 92         {
 93             SqlCommand cmd = new SqlCommand(sql, Connection);
 94             cmd.Parameters.Add(value);
 95             SqlDataReader reader = cmd.ExecuteReader();
 96             return reader;
 97         }
 98 
 99         public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
100         {
101             SqlCommand cmd = new SqlCommand(sql, Connection);
102             cmd.Parameters.AddRange(values);
103             SqlDataReader reader = cmd.ExecuteReader();
104             return reader;
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         public static SqlDataReader GetReader(string safeSql)
115         {
116             SqlCommand cmd = new SqlCommand(safeSql, Connection);
117             SqlDataReader reader = cmd.ExecuteReader();
118             return reader;
119         }
120 
121         public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
122         {
123             SqlCommand cmd = new SqlCommand(sql, Connection);
124             cmd.Parameters.AddRange(values);
125             SqlDataReader reader = cmd.ExecuteReader();
126             return reader;
127         }
128 
129 
130     }
131 }

 

转载于:https://www.cnblogs.com/tangzhengyue/archive/2011/11/27/2265153.html