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

观书散遗帙，探古穷至妙。这篇文章主要讲述dapper之连接数据库（Oracle，SQL Server，MySql）相关的知识，希望能为你提供帮助。
因为项目需求，需要项目同时可以访问三个数据库，因此本人经过一番研究，得出以下代码。

1.建立公共连接抽象类(DataBase)

1 public abstract class DataBase 2{ 3/// < summary> 4/// 5/// < /summary> 6public abstract string ConnectionString { get; } 7 8/// < summary> 9/// 10/// < /summary> 11/// < param name="cmd"> < /param> 12/// < param name="pName"> < /param> 13/// < param name="value"> < /param> 14/// < param name="type"> < /param> 15/// < returns> < /returns> 16 17public DbParameter CreateParameter(DbCommand cmd, String pName, Object value, System.Data.DbType type) 18{ 19var p = cmd.CreateParameter(); 20p.ParameterName = pName; 21p.Value = https://www.songbingjia.com/android/(value == null ? DBNull.Value : value); 22p.DbType = type; 23return p; 24} 25/// < summary> 26/// 27/// < /summary> 28/// < returns> < /returns> 29public abstract DbConnection CreateConnection(); 30/// < summary> 31/// 返回List 32/// < /summary> 33/// < typeparam name="T"> < /typeparam> 34/// < param name="sql"> < /param> 35/// < param name="paramObject"> < /param> 36/// < returns> < /returns> 37public List< T> Select< T> (string sql, Object paramObject = null) 38{ 39 40try 41{ 42using (DbConnection conn = CreateConnection()) 43{ 44conn.Open(); 45var list = Dapper.SqlMapper.Query< T> (conn, sql, paramObject); 46return list.ToList< T> (); 47} 48 49} 50catch (Exception ex) 51{ 52Logs.Write(LogType.Error, ex.Message,this.GetType()); 53return null; 54} 55} 56/// < summary> 57/// 返回List 58/// < /summary> 59/// < typeparam name="T"> < /typeparam> 60/// < param name="tabName"> 表名< /param> 61/// < param name="paramObject"> < /param> 62/// < returns> < /returns> 63public List< T> Select< T> () 64{ 65try 66{ 67using (DbConnection conn = CreateConnection()) 68{ 69conn.Open(); 70var list = Dapper.SqlMapper.Query< T> (conn, "SELECT * FROM " + typeof(T).Name, null); 71return list.ToList< T> (); 72} 73} 74catch (Exception ex) 75{ 76Logs.Write(LogType.Error, ex.Message, this.GetType()); 77return null; 78} 79} 80public int Insert< T> (T t) 81{ 82try 83{ 84using (DbConnection conn = CreateConnection()) 85{ 86conn.Open(); 87var id = conn.Insert(t); 88return id ?? 0; 89} 90} 91catch (Exception ex) 92{ 93Logs.Write(LogType.Error, ex.Message, this.GetType()); 94return -1; 95} 96} 97public int Delete< T> (T t) 98{ 99try 100{ 101using (DbConnection conn = CreateConnection()) 102{ 103conn.Open(); 104return conn.Delete(t); 105} 106} 107catch (Exception ex) 108{ 109Logs.Write(LogType.Error, ex.Message, this.GetType()); 110return -1; 111} 112} 113public int Update< T> (T t) 114{ 115try 116{ 117using (DbConnection conn = CreateConnection()) 118{ 119conn.Open(); 120return conn.Update(t); 121} 122} 123catch (Exception ex) 124{ 125Logs.Write(LogType.Error, ex.Message, this.GetType()); 126return -1; 127} 128} 129public string InsertByGuid< T> (T t) 130{ 131try 132{ 133using (DbConnection conn = CreateConnection()) 134{ 135conn.Open(); 136return conn.Insert< string,T> (t); 137} 138} 139catch (Exception ex) 140{ 141Logs.Write(LogType.Error, ex.Message, this.GetType()); 142return ""; 143} 144} 145public List< T> GetList< T> (string sql, Object paramObject = null) 146{ 147try 148{ 149using (DbConnection conn = CreateConnection()) 150{ 151conn.Open(); 152return conn.Query< T> (sql, paramObject).ToList(); 153} 154} 155catch (Exception ex) 156{ 157Logs.Write(LogType.Error, ex.Message, this.GetType()); 158return null; 159} 160} 161public IEnumerable< dynamic> GetList(string sql, Object paramObject = null) 162{ 163try 164{ 165using (DbConnection conn = CreateConnection()) 166{ 167conn.Open(); 168return conn.Query(sql, paramObject); 169} 170} 171catch (Exception ex) 172{ 173Logs.Write(LogType.Error, ex.Message, this.GetType()); 174return null; 175} 176} 177/// < summary> 178/// 179/// < /summary> 180/// < param name="sql"> < /param> 181/// < param name="paramObject"> < /param> 182/// < returns> < /returns> 183public List< dynamic> Select(string sql, Object paramObject = null) 184{ 185DbConnection conn = null; 186try 187{ 188conn = CreateConnection(); 189conn.Open(); 190var list = Dapper.SqlMapper.Query(conn, sql, paramObject); 191return list.ToList< dynamic> (); 192} 193catch (Exception ex) 194{ 195Logs.Write(LogType.Error, ex.Message, this.GetType()); 196return null; 197} 198finally 199{ 200if (conn != null) 201conn.Close(); 202} 203} 204 205/// < summary> 206/// 获取一条数据 207/// < /summary> 208/// < param name="sql"> < /param> 209/// < param name="paramObject"> < /param> 210/// < returns> < /returns> 211public dynamic Single(string sql, Object paramObject = null) 212{ 213DbConnection conn = null; 214try 215{ 216conn = CreateConnection(); 217conn.Open(); 218var list = Dapper.SqlMapper.QuerySingleOrDefault< dynamic> (conn, sql, paramObject); 219return list; 220} 221catch (Exception ex) 222{ 223Logs.Write(LogType.Error, ex.Message, this.GetType()); 224return null; 225} 226finally 227{ 228if (conn != null) 229conn.Close(); 230} 231} 232 233/// < summary> 234/// 获取一条数据 235/// < /summary> 236/// < typeparam name="T"> < /typeparam> 237/// < param name="sql"> < /param> 238/// < param name="paramObject"> < /param> 239/// < returns> < /returns> 240public T Single< T> (string sql, Object paramObject = null) 241{ 242 243DbConnection conn = null; 244try 245{ 246conn = CreateConnection(); 247conn.Open(); 248var list = Dapper.SqlMapper.QuerySingleOrDefault< T> (conn, sql, paramObject); 249return list; 250} 251catch (Exception ex) 252{ 253Logs.Write(LogType.Error, ex.Message, this.GetType()); 254return default(T); 255} 256finally 257{ 258if (conn != null) 259conn.Close(); 260} 261} 262 263/// < summary> 264/// 获取一行一列 265/// < /summary> 266/// < typeparam name="T"> < /typeparam> 267/// < param name="sql"> < /param> 268/// < param name="paramObject"> < /param> 269/// < returns> < /returns> 270public T ExecuteScalar< T> (string sql, Object paramObject = null) 271{ 272 273DbConnection conn = null; 274try 275{ 276conn = CreateConnection(); 277conn.Open(); 278T t = Dapper.SqlMapper.ExecuteScalar< T> (conn, sql, paramObject); 279return t; 280} 281catch (Exception ex) 282{ 283Logs.Write(LogType.Error, ex.Message, this.GetType()); 284return default(T); 285} 286finally 287{ 288if (conn != null) 289conn.Close(); 290} 291} 292 293/// < summary> 294/// 返回受影响行数 295/// < /summary> 296/// < param name="sql"> < /param> 297/// < param name="paramObject"> < /param> 298/// < returns> < /returns> 299public int Execute(string sql, Object paramObject = null) 300{ 301DbConnection conn = null; 302try 303{ 304conn = CreateConnection(); 305conn.Open(); 306int count = Dapper.SqlMapper.Execute(conn, sql, paramObject); 307return count; 308} 309catch (Exception ex) 310{ 311Logs.Write(LogType.Error, ex.Message, this.GetType()); 312return 0; 313} 314finally 315{ 316if (conn != null) 317conn.Close(); 318} 319} 320}

2.建立3个不同数据库连接类（OracleDataBase、SqlDataBase、mysqlDataBase）继承(DataBase)类

1 public class OracleDataBase : DataBase 2{ 3public override string ConnectionString 4{ 5get 6{ 7return System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ToString(); 8} 9} 10/// < summary> 11/// 常用 12/// < /summary> 13/// < returns> < /returns> 14public override DbConnection CreateConnection() 15{ 16Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionString); 17conn.ConnectionString = ConnectionString; 18return null; 19} 20}

1 public class SqlDataBase : DataBase 2{ 3/// < summary> 4/// 5/// < /summary> 6public override string ConnectionString 7{ 8get 9{ 10return System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnection"].ToString(); 11} 12} 13/// < summary> 14/// 15/// < /summary> 16/// < returns> < /returns> 17public override DbConnection CreateConnection() 18{ 19SqlConnection conn = new SqlConnection(ConnectionString); 20conn.ConnectionString = ConnectionString; 21return conn; 22} 23 24}

1 publicclass MySqlDataBase : DataBase 2{ 3/// < summary> 4/// 5/// < /summary> 6public override string ConnectionString 7{ 8get 9{ 10return System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString(); 11} 12} 13/// < summary> 14/// 常用 15/// < /summary> 16/// < returns> < /returns> 17public override DbConnection CreateConnection() 18{ 19MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString); 20return conn; 21} 22}

3.访问不同的连接

1public List< T> GetList< T> () 2{ 3var db = new SqlDataBase(); 4return db.Select< T> (); 5} 6public List< T> GetList< T> () 7{ 8var db = new OracleDataBase(); 9return db.Select< T> (); 10} 11public List< T> GetList< T> () 12{ 13var db = new MySqlDataBase(); 14return db.Select< T> (); 15}

【dapper之连接数据库（Oracle，SQL Server，MySql）】以上代码就是访问不同的数据库的列表信息，其他增删改和上面的差不多写法，如果还有其他问题，请评论！