dapper mysql数据库_dapper之连接数据库（Oracle，SQL Server，MySql）

1 public abstract classDataBase2 {3 ///

4 ///

5 ///

6 public abstract string ConnectionString { get; }7

8 ///

9 ///

10 ///

11 ///

12 ///

13 ///

14 ///

15 ///

16

17 publicDbParameter CreateParameter(DbCommand cmd, String pName, Object value, System.Data.DbType type)18 {19 var p =cmd.CreateParameter();20 p.ParameterName =pName;21 p.Value = (value == null ?DBNull.Value : value);22 p.DbType =type;23 returnp;24 }25 ///

26 ///

27 ///

28 ///

29 public abstractDbConnection CreateConnection();30 ///

31 ///返回List32 ///

33 ///

34 ///

35 ///

36 ///

37 public List Select(string sql, Object paramObject = null)38 {39

40 try

41 {42 using (DbConnection conn =CreateConnection())43 {44 conn.Open();45 var list = Dapper.SqlMapper.Query(conn, sql, paramObject);46 return list.ToList();47 }48

49 }50 catch(Exception ex)51 {52 Logs.Write(LogType.Error, ex.Message,this.GetType());53 return null;54 }55 }56 ///

57 ///返回List58 ///

59 ///

60 /// 表名

61 ///

62 ///

63 public List Select()64 {65 try

66 {67 using (DbConnection conn =CreateConnection())68 {69 conn.Open();70 var list = Dapper.SqlMapper.Query(conn, "SELECT * FROM" + typeof(T).Name, null);71 return list.ToList();72 }73 }74 catch(Exception ex)75 {76 Logs.Write(LogType.Error, ex.Message, this.GetType());77 return null;78 }79 }80 public int Insert(T t)81 {82 try

83 {84 using (DbConnection conn =CreateConnection())85 {86 conn.Open();87 var id =conn.Insert(t);88 return id ?? 0;89 }90 }91 catch(Exception ex)92 {93 Logs.Write(LogType.Error, ex.Message, this.GetType());94 return -1;95 }96 }97 public int Delete(T t)98 {99 try

100 {101 using (DbConnection conn =CreateConnection())102 {103 conn.Open();104 returnconn.Delete(t);105 }106 }107 catch(Exception ex)108 {109 Logs.Write(LogType.Error, ex.Message, this.GetType());110 return -1;111 }112 }113 public int Update(T t)114 {115 try

116 {117 using (DbConnection conn =CreateConnection())118 {119 conn.Open();120 returnconn.Update(t);121 }122 }123 catch(Exception ex)124 {125 Logs.Write(LogType.Error, ex.Message, this.GetType());126 return -1;127 }128 }129 public string InsertByGuid(T t)130 {131 try

132 {133 using (DbConnection conn =CreateConnection())134 {135 conn.Open();136 return conn.Insert(t);137 }138 }139 catch(Exception ex)140 {141 Logs.Write(LogType.Error, ex.Message, this.GetType());142 return "";143 }144 }145 public List GetList(string sql, Object paramObject = null)146 {147 try

148 {149 using (DbConnection conn =CreateConnection())150 {151 conn.Open();152 return conn.Query(sql, paramObject).ToList();153 }154 }155 catch(Exception ex)156 {157 Logs.Write(LogType.Error, ex.Message, this.GetType());158 return null;159 }160 }161 public IEnumerable GetList(string sql, Object paramObject = null)162 {163 try

164 {165 using (DbConnection conn =CreateConnection())166 {167 conn.Open();168 returnconn.Query(sql, paramObject);169 }170 }171 catch(Exception ex)172 {173 Logs.Write(LogType.Error, ex.Message, this.GetType());174 return null;175 }176 }177 ///

178 ///

179 ///

180 ///

181 ///

182 ///

183 public List Select(string sql, Object paramObject = null)184 {185 DbConnection conn = null;186 try

187 {188 conn =CreateConnection();189 conn.Open();190 var list =Dapper.SqlMapper.Query(conn, sql, paramObject);191 return list.ToList();192 }193 catch(Exception ex)194 {195 Logs.Write(LogType.Error, ex.Message, this.GetType());196 return null;197 }198 finally

199 {200 if (conn != null)201 conn.Close();202 }203 }204

205 ///

206 ///获取一条数据207 ///

208 ///

209 ///

210 ///

211 public dynamic Single(string sql, Object paramObject = null)212 {213 DbConnection conn = null;214 try

215 {216 conn =CreateConnection();217 conn.Open();218 var list = Dapper.SqlMapper.QuerySingleOrDefault(conn, sql, paramObject);219 returnlist;220 }221 catch(Exception ex)222 {223 Logs.Write(LogType.Error, ex.Message, this.GetType());224 return null;225 }226 finally

227 {228 if (conn != null)229 conn.Close();230 }231 }232

233 ///

234 ///获取一条数据235 ///

236 ///

237 ///

238 ///

239 ///

240 public T Single(string sql, Object paramObject = null)241 {242

243 DbConnection conn = null;244 try

245 {246 conn =CreateConnection();247 conn.Open();248 var list = Dapper.SqlMapper.QuerySingleOrDefault(conn, sql, paramObject);249 returnlist;250 }251 catch(Exception ex)252 {253 Logs.Write(LogType.Error, ex.Message, this.GetType());254 return default(T);255 }256 finally

257 {258 if (conn != null)259 conn.Close();260 }261 }262

263 ///

264 ///获取一行一列265 ///

266 ///

267 ///

268 ///

269 ///

270 public T ExecuteScalar(string sql, Object paramObject = null)271 {272

273 DbConnection conn = null;274 try

275 {276 conn =CreateConnection();277 conn.Open();278 T t = Dapper.SqlMapper.ExecuteScalar(conn, sql, paramObject);279 returnt;280 }281 catch(Exception ex)282 {283 Logs.Write(LogType.Error, ex.Message, this.GetType());284 return default(T);285 }286 finally

287 {288 if (conn != null)289 conn.Close();290 }291 }292

293 ///

294 ///返回受影响行数295 ///

296 ///

297 ///

298 ///

299 public int Execute(string sql, Object paramObject = null)300 {301 DbConnection conn = null;302 try

303 {304 conn =CreateConnection();305 conn.Open();306 int count =Dapper.SqlMapper.Execute(conn, sql, paramObject);307 returncount;308 }309 catch(Exception ex)310 {311 Logs.Write(LogType.Error, ex.Message, this.GetType());312 return 0;313 }314 finally

315 {316 if (conn != null)317 conn.Close();318 }319 }320 }