C#,一份超简单的数据库帮助类,SqlHelp
简单,实用,留存。
1 using System; 2 using System.Collections.Generic; 3 using System.Configuration; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 10 namespace CarHailing.Base 11 { 12 /// <summary> 13 /// 数据库帮助类 14 /// </summary> 15 public class SqlHelp 16 { 17 /// <summary> 18 /// 创建一个已打开的连接 19 /// </summary> 20 /// <returns></returns> 21 private static SqlConnection CreateConn() 22 { 23 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString); 24 conn.Open(); 25 return conn; 26 } 27 28 /// <summary> 29 /// 执行sql语句,返回受影响的行数 30 /// </summary> 31 /// <param name="sql"></param> 32 /// <returns></returns> 33 public static int ExecuteNonQuery(string sql) 34 { 35 return ExecuteNonQuery(sql, null); 36 } 37 38 /// <summary> 39 /// 执行sql语句,返回受影响的行数 40 /// </summary> 41 /// <param name="sql"></param> 42 /// <returns></returns> 43 public static int ExecuteNonQuery(string sql, SqlParameter[] ps) 44 { 45 SqlConnection conn = CreateConn(); 46 SqlCommand cmd = new SqlCommand(sql, conn); 47 try 48 { 49 if (ps != null) 50 { 51 cmd.Parameters.AddRange(ps); 52 } 53 return cmd.ExecuteNonQuery(); 54 } 55 catch (Exception ex) 56 { 57 return 0; 58 } 59 finally 60 { 61 conn.Close(); 62 } 63 } 64 65 /// <summary> 66 /// 执行sql语句,返回一个结果表 67 /// </summary> 68 /// <param name="sql"></param> 69 /// <returns></returns> 70 public static DataTable ExecuteSelectDataTable(string sql) 71 { 72 return ExecuteSelectDataTable(sql, null); 73 } 74 75 /// <summary> 76 /// 执行sql语句,返回一个结果表 77 /// </summary> 78 /// <param name="sql"></param> 79 /// <param name="ps"></param> 80 /// <returns></returns> 81 public static DataTable ExecuteSelectDataTable(string sql, SqlParameter[] ps = null) 82 { 83 84 SqlConnection conn = CreateConn(); 85 SqlCommand cmd = new SqlCommand(sql, conn); 86 try 87 { 88 if (ps != null) 89 { 90 cmd.Parameters.AddRange(ps); 91 } 92 //SqlDataAdapter sda = new SqlDataAdapter(cmd); 93 SqlDataReader read = cmd.ExecuteReader(); 94 DataTable table = new DataTable(); 95 table.Load(read); 96 //DataSet ds = new DataSet(); 97 //sda.Fill(ds); 98 return table; 99 } 100 finally 101 { 102 conn.Close(); 103 } 104 } 105 106 /// <summary> 107 /// 108 /// </summary> 109 /// <param name="sql"></param> 110 /// <returns></returns>decimal 111 public static DataTable ExecuteSelectPageDataTable(string sql, ref SqlParameter[] ps) 112 { 113 SqlConnection conn = CreateConn(); 114 SqlCommand cmd = new SqlCommand(sql, conn); 115 cmd.CommandType = CommandType.StoredProcedure; 116 try 117 { 118 cmd.Parameters.AddRange(ps); 119 SqlDataAdapter sda = new SqlDataAdapter(cmd); 120 DataSet ds = new DataSet(); 121 sda.Fill(ds); 122 return ds.Tables[0]; 123 } 124 catch (SqlException ex) 125 { 126 throw ex; 127 } 128 finally 129 { 130 conn.Close(); 131 } 132 } 133 134 //public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms) 135 //{ 136 // SqlCommand cmd = new SqlCommand(); 137 // SqlConnection conn = CreateConn(); 138 139 // try 140 // { 141 // ProCommand(cmd, conn, cmdText, cmdType, cmdParms); 142 // SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 143 // //cmd.Parameters.Clear();//放到这里,返回参数会被清空。 144 // return rdr; 145 // } 146 // catch 147 // { 148 // conn.Close(); 149 // throw; 150 // } 151 //} 152 153 /// <summary> 154 /// 分页读取数据 155 /// </summary> 156 /// <param name="query">select查询语句</param> 157 /// <param name="pageSize">每页显示数据数</param> 158 /// <param name="PageIndex">当前页码</param> 159 /// <param name="dt">存储数据的DataTable实例</param> 160 public static void PageDataTable(string query, int pageSize, int PageIndex, DataTable dt) 161 { 162 //读取数据的开始索引 163 long startIndex = (PageIndex - 1) * pageSize; 164 //读取数据的结束索引 165 long endIndex = PageIndex * pageSize - 1; 166 //DataReader读取的当前数据行的索引 167 long readToIndex = -1; 168 using (SqlConnection conn = CreateConn()) 169 { 170 SqlCommand cmd = new SqlCommand(query, conn); 171 //conn.Open(); 172 SqlDataReader dr = cmd.ExecuteReader(); 173 //数据源中的列数 174 int cols = dr.VisibleFieldCount; 175 //构造DataTable结构 176 for (int i = 0; i < cols; i++) 177 { 178 dt.Columns.Add(new DataColumn(dr.GetName(i), dr.GetFieldType(i))); 179 } 180 //读取数据,将数据一行一行添加到DataTable 181 while (dr.Read()) 182 { 183 readToIndex++; 184 //当DataReader指针在开始索引和结束索引闭区间时才读取数据构造DataRow 185 //并添加到DataTable 186 if (readToIndex >= startIndex && readToIndex <= endIndex) 187 { 188 DataRow row = dt.NewRow(); 189 for (int i = 0; i < cols; i++) 190 { 191 row[i] = dr[i]; 192 } 193 dt.Rows.Add(row); 194 } 195 } 196 dr.Close(); 197 } 198 } 199 } 200 }