DBHelper 

  1 public class DBHelper
  2 {
  3     public DBHelper(string conn)
  4     {
  5         ConnectionString = ConfigurationManager.ConnectionStrings[conn].ToString();
  6     }
  7     public string ConnectionString { get; set; }
  8 
  9     public SqlDataReader GetReader(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
 10     {
 11         SqlDataReader reader = null;
 12         SqlConnection conn = new SqlConnection(ConnectionString);
 13         SqlCommand cmd = new SqlCommand(sql, conn);
 14         cmd.CommandType = cmdType;
 15         if (param != null)
 16         {
 17             cmd.Parameters.AddRange(param);
 18         }
 19         conn.Open();
 20         //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
 21         reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 22         return reader;
 23     }
 24     public DataSet GetDataSet(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
 25     {
 26         DataSet dt = new DataSet(); ;
 27         using (SqlConnection conn = new SqlConnection(ConnectionString))
 28         {
 29             using (SqlDataAdapter adapt = new SqlDataAdapter(sql, conn))
 30             {
 31                 adapt.SelectCommand.CommandType = cmdType;
 32                 if (param != null)
 33                 {
 34                     adapt.SelectCommand.Parameters.AddRange(param);
 35                 }
 36                 adapt.Fill(dt);
 37             }
 38             conn.Close();
 39         }
 40         return dt;
 41     }
 42     public DataTable GetTable(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
 43     {
 44         DataTable dt = new DataTable(); ;
 45         using (SqlConnection conn = new SqlConnection(ConnectionString))
 46         {
 47             using (SqlDataAdapter adapt = new SqlDataAdapter(sql, conn))
 48             {
 49                 adapt.SelectCommand.CommandType = cmdType;
 50                 if (param != null)
 51                 {
 52                     adapt.SelectCommand.Parameters.AddRange(param);
 53                 }
 54                 adapt.Fill(dt);
 55             }
 56             conn.Close();
 57         }
 58         return dt;
 59     }
 60     public object GetValue(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
 61     {
 62         object o = null;
 63         using (SqlConnection conn = new SqlConnection(ConnectionString))
 64         {
 65             using (SqlCommand cmd = new SqlCommand(sql, conn))
 66             {
 67                 cmd.CommandType = cmdType;
 68                 if (param != null)
 69                 {
 70                     cmd.Parameters.AddRange(param);
 71                 }
 72                 conn.Open();
 73                 o = cmd.ExecuteScalar();
 74             }
 75             conn.Close();
 76         }
 77         return o;
 78     }
 79     // 返回影响行数
 80     public int ExcuteSQL(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
 81     {
 82         int i = 0;
 83         using (SqlConnection conn = new SqlConnection(ConnectionString))
 84         {
 85             using (SqlCommand cmd = new SqlCommand(sql, conn))
 86             {
 87                 cmd.CommandType = cmdType;
 88                 if (param != null)
 89                 {
 90                     cmd.Parameters.AddRange(param);
 91                 }
 92                 conn.Open();
 93                 i = cmd.ExecuteNonQuery();
 94             }
 95             conn.Close();
 96         }
 97         return i;
 98     }
 99     // 返回第一行第一列
100     public int ExcuteScalarSQL(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
101     {
102         int i = 0;
103         using (SqlConnection conn = new SqlConnection(ConnectionString))
104         {
105             using (SqlCommand cmd = new SqlCommand(sql, conn))
106             {
107                 cmd.CommandType = cmdType;
108                 if (param != null)
109                 {
110                     cmd.Parameters.AddRange(param);
111                 }
112                 conn.Open();
113                 i = Convert.ToInt32(cmd.ExecuteScalar());
114             }
115             conn.Close();
116         }
117         return i;
118     }
119 
120     // 批量更新数据
121     public void BulkToDB(DataTable source, string targetTable)
122     {
123         if (source != null && source.Rows.Count != 0)
124         {
125             using (SqlConnection conn = new SqlConnection(ConnectionString))
126             {
127                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
128                 {
129                     bulkCopy.DestinationTableName = targetTable;
130                     bulkCopy.BatchSize = source.Rows.Count;
131                     for (int i = 0; i < source.Columns.Count; i++)
132                     {
133                         bulkCopy.ColumnMappings.Add(source.Columns[i].ColumnName, source.Columns[i].ColumnName);
134                     }
135                     conn.Open();
136                     bulkCopy.WriteToServer(source);
137                     bulkCopy.Close();
138                 }
139                 conn.Close();
140             }
141         }
142     }
143 }
View Code

 

posted @ 2017-06-09 10:01  iceexx  阅读(160)  评论(0)    收藏  举报