.NET框架自带的数据提供器包括SQLServer(System.Data.SqlClient)、Oracle (System.Data.Oracleclient)、OLEDB(System.Data.Oledb)、ODBC (System.Data.Odbc)
ADO.NET2.0提出了全新的System.Data.Common命名空间。使WEB开发与后台数据库无关的数据访问代码，让WEB程序在运行的时候决定使用上述哪个提供器，所以性能上无影响。
1：通常的做法是在Web.Config配置中添加连接字符串。如下
<connectionStrings>
<add name="ConnectionString"
connectionString="server=(local)"SQLEXPRESS;database=Dataname;uid=username;pwd=password" providerName="System.Data.SqlClient"/>
</connectionStrings>--这里使用SQL数据提供器
2：这里将添加静态类WebConfiguration.cs 完成对连接字符串和数据提供器的读取
public static class WebConfiguration
{
//使用readonly的好处是可以在构造器中动态设置字段的值
private readonly static string dbConnectionString;
private readonly static string dbProviderName;
static WebConfiguration()
{
//将连接字符串赋值给只读字段dbConnectionString;
dbConnectionString= ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//将数据提供器赋值给自读字段dbProviderName
dbProviderName= ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
}
public static string DbConnectionString
{
//获取连接字符串
get { return dbConnectionString;}
}
public static string DbProviderName
{
//供应器的类型
get { return dbProviderName; }
}
}
3:创建通用数据访问类GenericDataAccess.cs
Code
1 using System;
2 using System.Data;
3 using System.Data.Common;
4 using System.Configuration;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12 /// <summary>
13 /// 通用数据库访问代码
14 /// </summary>
15 public static class GenericDataAccess
16 {
17 static GenericDataAccess()
18 {
19
20 }
21 /// <summary>
22 /// 创建DbCommand对象
23 /// </summary>
24 /// <returns></returns>
25 public static DbCommand CreateCommand()
26 {
27 string dataProviderName = WebConfiguration.DbProviderName;
28 string connectionString = WebConfiguration.DbConnectionString;
29
30 //开发与数据库无关的代码
31 DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
32 DbConnection conn = factory.CreateConnection();
33 conn.ConnectionString = connectionString;
34 DbCommand comm = conn.CreateCommand();
35 comm.CommandType = CommandType.StoredProcedure;
36 return comm;
37 }
38 /// <summary>
39 /// 执行查询，返回datatable
40 /// </summary>
41 /// <param name="command"></param>
42 /// <returns></returns>
43 public static DataTable ExecuteSelectCommand(DbCommand command)
44 {
45 DataTable table;
46 try
47 {
48 command.Connection.Open();
49 DbDataReader reader = command.ExecuteReader();
50 table = new DataTable();
51 table.Load(reader);
52 reader.Close();
53 }
54 catch (Exception ex)
55 {
56 throw new Exception(ex.Message, ex);
57 }
58 finally
59 {
60 command.Connection.Close();
61 }
62 return table;
63
64 }
65 /// <summary>
66 /// 执行update insert del操作
67 /// </summary>
68 /// <param name="command"></param>
69 /// <returns>返回影响行数</returns>
70 public static int ExecuteNonQuery(DbCommand command)
71 {
72 int affectRows = -1;
73 try
74 {
75 command.Connection.Open();
76 affectRows = command.ExecuteNonQuery();
77 }
78 catch (Exception ex)
79 {
80 throw new Exception(ex.Message, ex);
81 }
82 finally
83 {
84 command.Connection.Close();
85 }
86 return affectRows;
87 }
88 /// <summary>
89 /// 返回第一列第一行
90 /// </summary>
91 /// <param name="command"></param>
92 /// <returns></returns>
93 public static string ExecuteScalar(DbCommand command)
94 {
95 string value = "";
96 try
97 {
98 command.Connection.Open();
99 value = command.ExecuteScalar().ToString();
100
101 }
102 catch (Exception ex)
103 {
104 throw new Exception(ex.Message, ex);
105 }
106 finally
107 {
108 command.Connection.Close();
109 }
110 return value;
111 }
112
113 }
114
好的习惯是将SQL语句写在存储过程中。这个类摘自ASP.NET2.0电子商务开发实战一书。
这个类能够满足基本的数据访问。
