C#中怎么新建DButility数据库帮助类库

1、在解决方案中新建一个DBUtility类库，作为访问MySQL的通用sql语句存放地点

2、引入MySql.Data.dll

3、配置web.config MySQL数据库连接字符串

在解决方案根目录下打开web.config配置文件，在connectionStrings节点中加入MySQL连接字符串

<connectionStrings>

<add name="DBConnectionString" connectionString="Data Source=127.0.0.1Database=你的数据库名User ID=用户名Password=密码" providerName="MySql.Data.MySqlClient"/>

</connectionStrings>

前一段时间在博客园中找到的一个工厂模式的数据库帮助类,具体是出自哪一位高人记不清楚了,现在Mark一下.

1、数据库类型枚举

复制代码

public enum DbProviderType : byte

{

SqlServer,

MySql,

SQLite,

Oracle,

ODBC,

OleDb,

Firebird,

PostgreSql,

DB2,

Informix,

SqlServerCe

}

复制代码

2、DbProviderFactory工厂类

复制代码

public class ProviderFactory

{

private static Dictionary<DbProviderType, string>providerInvariantNames = new Dictionary<DbProviderType, string>()

private static Dictionary<DbProviderType, DbProviderFactory>providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20)

static ProviderFactory()

{

providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient")

providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb")

providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC")

providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client")

providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient")

providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite")

providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird")

providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql")

providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries")

providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix")

providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe")

}

public static string GetProviderInvariantName(DbProviderType providerType)

{

return providerInvariantNames[providerType]

}

public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)

{

if (!providerFactoies.ContainsKey(providerType))

{

providerFactoies.Add(providerType, ImportDbProviderFactory(providerType))

}

return providerFactoies[providerType]

}

private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)

{

string providerName = providerInvariantNames[providerType]

DbProviderFactory factory = null

try

{

factory = DbProviderFactories.GetFactory(providerName)

}

catch (ArgumentException e)

{

factory = null

}

return factory

}

}

复制代码

3、数据库 *** 作帮助类

复制代码

public sealed class DbHelper

{

public string ConnectionString { getset}

private DbProviderFactory providerFactory

public DbHelper(string connectionString, DbProviderType providerType)

{

ConnectionString = connectionString

providerFactory = ProviderFactory.GetDbProviderFactory(providerType)

if (providerFactory == null)

{

throw new ArgumentException("Can't load DbProviderFactory for given value of providerType")

}

}

********** 具体的ADO.NET *** 作方法略************

public int ExecuteNonQuery(string sql, IList<DbParameter>parameters)

public DbDataReader ExecuteReader(string sql, IList<DbParameter>parameters)

}

复制代码

4、数据库配置文件

复制代码

<connectionStrings>

<add name="DARJUAN_DB" connectionString="data source=DARJUAN-PCdatabase=DARJUAN_DBuser id=***password=***connection reset=falseTimeout=120connection lifetime=120min pool size=20max pool size=50" providerName="System.Data.SqlClient" />

</connectionStrings>

复制代码

<system.data>

<DbProviderFactories>

<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

<add name="Informix Data Provider" invariant="IBM.Data.Informix" description=".Net Framework Data Provider for Informix" type="IBM.Data.Informix.IfxFactory, IBM.Data.Informix" />

<add name="DB2 Data Provider" invariant="IBM.Data.DB2.iSeries" description=".Net Framework Data Provider for DB2 iSeries" type="IBM.Data.DB2.iSeries.DB2Factory, IBM.Data.DB2.iSeries" />

<add name="Firebird Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description="Firebird" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />

<add name="Oracle Data Provider" invariant="Oracle.DataAccess.Client" description=".Net Framework Data Provider for Oracle" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess" />

<add name="PostgreSQL Data Provider Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, System.Data" />

</DbProviderFactories>

</system.data>

---