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 }