摘自《ASP.NET入门经典——C#编程篇》，清华大学出版社
ADO.NET是由.NET framework为与数据库中的数据进行交互而提供的一组对象类的名称。我们知道，面向对象编程的有关主要优点是可以把各种复杂的功能封装在一个自包含的单元中，接着要处理的就是—个定义好的接口中，它由一些方法和属性组成。
ADO.NET可以与许多类型的对象交互，不仅有存储在数据库中的数据，还有存储在电子邮件服务器、文本文件、应用程序文档(例如Excel电子表格)和XML中的数掂。下面是可以连接的数据源列表：
☉企业级的RDBMS，例如Oracle,Microsoft SQL Server,IBM DB2
☉桌面数据库，例如Access
☉文件和目录系统，例如Windows FAT32
☉用逗号分隔开的文本文件，或长度固定的文本文件
☉非数据库文件，例如Excel电子表格
☉Microsoft Exchange Server 2000数据，例如电子邮件(有一些限制)
☉基于XML的数据
其优点是上面的列表不是固定的。ADO.NET连接的结构是可以为目前还没有想像出来的数据源格式编写供应程序、驱动程序和适配器。ADO.NET允许从源中选择相当抽象的数据。数据库管理员只需授予使用数据的一个视图的权限接口。即使没有访问数据源其它内容的权限，也可以使用ADO.NET把数据放在ASP.NET页面上。
ASP.NET还提供一种比传统ASP更容易的格式化页面上数据的方式。DataGrid(详见本章后面的内容)可以创建大多数HTML标记。而且，ADO.NET还提供了以XML格式处理数据的工具。
在ADO.NET中，我们将处理断开连接的数据集，在网站的访问者请求数据时，就要建立连接，传送数据，之后关闭连接。接着，访问者就可以修改数据，但这些修改不会在数据源中立即更新－－在用访问者所做的修改更新数据库之前，必须重新打开连接。其优点是效率高，可伸缩性好。如果不使用这种断开连接的模型，就意味着必须一直打开连接，直到每个用户的会话结束为止。在Web上，有上千个并行用户，给每个用户打开连接在系统资源上是非常昂贵的。使用断开连接的数据可以使应用程序的效率高得多，并能处理更多的工作负载，即它们的伸缩性更好。
总之，ADO.NET提供的功能可以读取各种数据源中的数据，减少需要编写的代码量。另一方面，仍必须学习如何处理ADO.NET对象和SQL，深刻理解正在使用的数据源。
ADO.NET的Connection对象用于连接数据源。它表示数据源和数据使用者之间的连接。Connection对象有一个Open()方法，可打开连接字符串中指定的连接。连接字符串在3个部分中包含了连接数据仓库需要的信息，但对于不个同的供应程序和驱动程序，这是有区别的：
第一部分指定要使用的供应程序或驱动程序的种类。
第二部分指定要使用的数据库。
最后一部分通常包含安全信息，例如用户名和密码。这些都由网页的访问者提供，或者是表示Web服务器的ID，因此不是针对某个访问者的。
最常见的3个连接字符串是用于Access，SQL Server和Managed SQL-Server直接连接的连接字符串。对于Aceess，可使用Jet供应程序(Jet表示Access中的数据引擎)：
"provider=Microsoft.Jet.OLEDB.4.0; data source=MyDrive:MyPath/MyFile.MDB"
下面是Microsoft SQL Server中数据库的标准OLEDB字符串(放在一行上)：
"provider=SQLOLEDB.1; server=MyServerName; database=MyDatabase; uid=MyUserID; pwd=MyPassword"
用于Microsoft SQL Server的托管供应程序有类似的语法(注意没有指定供应程序)：
"server=MyServerName; database=MyDatabase; uid=MyUserID; pwd=MyPassword"
在ADO.NET中，每个数据库都可以使用那些最能够充分利用数据库特定功能的类来进行访问。在笔者编写本书的时候，有如下的.NET数据提供者可以使用：
* SQL Server。该数据提供者位于System.Data.SqlClient命名空间，并提供了使用SQL Server 7.0以上版本数据库的类。它包含了SqlConnection、SqlCommand、SqlDataReader和SqlDataAdapter类，它是ADO.NET的主要部分。
* OLE DB。该数据提供者位于System.Data.OleDb命名空间，提供了使用(具有0LE DB驱动的)任何数据源的类。它包含了0leDbConnection、OleDbCommand、OleDbDataReader和OleDbDataAdapter类。它也是ADO.NET的主要部分。
* ODBC。该数据提供者位于Microsoft.Data.Odbc命名空间，它提供了使用(安装了ODBC驱动的)任何数据源类。它包含了OdbcConnection、OdbcCommand、OdbcDataReader和OdbcDataAdapter类。安装过程必须以手工方式通过从http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/668/msdncompositedoc.xml中下载程序包来实现。
* Oracle。安装了该数据提供者后，它的类位于System.Data.OracleClient命名空间中，在该命名空间中，您会找到OracleConnection、OracleCommand、OracleDataReader和OracleDataAdapter类。它必须从http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/msdn-files/027/001/940/msdncompositedoc.xml下载，并且和.NET Framework分开。
* MySql。它是一个数据库引擎的数据提供者，但是这次并不是Microsoft获得它。你可以从Core Lab的站点(http://crlab.com/mysqlnet)来购买和下载它。
Table : .NET Framework Data Provider Implementations
|
Data Provider |
Description |
|
.NET Framework Data Provider for ODBC |
Provides connectivity to any data source that implements an ODBC interface; this includes Microsoft SQL Server, Oracle, and Microsoft Access databases. Data provider classes are contained in the System.Data.Odbc namespace and have the prefix Odbc. |
|
.NET Framework Data Provider for OLE DB |
Provides connectivity to any data source that implements an OLE DB interface; this includes Microsoft SQL Server, MSDE, Oracle, and Jet databases. Data provider classes are contained in the System.Data.OleDb namespace and have the prefix OleDb. |
|
.NET Framework Data Provider for Oracle |
Provides optimized connectivity to Oracle databases. Data provider classes are contained in the System.Data.OracleClient namespace and have the prefix Oracle. |
|
.NET Framework Data Provider for SQL Server |
Provides optimized connectivity to Microsoft SQL Server version 7 and later (including MSDE) by communicating directly with the SQL Server without the need to use ODBC or OLE DB. Data provider classes are contained in the System.Data.SqlClient namespace and have the prefix Sql. |
|
.NET Compact Framework Data Provider for SQL Server CE |
Provides connectivity to Microsoft SQL Server CE. Data provider classes are contained in the System.Data.SqlServerCe namespace and have the prefix SqlCe. |
--------------------------------------------------------------------------------------------------------------------------------------
02、利用C#编写Web下的邮件发送程序
创建一个C#项目中的APS.NET Web应用程序，命名为EmailPage；
按下图所示添加各控件，并对各控件进行命名。注意：要将HTML控件FileField设置为服务器控件运行。
查看WebForm1.aspx的HTML源代码，修改标签，添加属性设置encType="multipart/form-data"；
在C#代码中添加对命名空间System.Web.Mail的引用（需要使用该名称空间下的MailMessage类和SmptMail类），MailMessage类对象用来设定要发送邮件的相关信息，如收件人，发件人，主题，内容，优先级，格式以及附件；使用SmptMail类的Send方法将MailMessage对象发送出去。
注：要在C:\Inetpub\wwwroot目录下创建一个temp文件夹，用于将酵文件上传到Web临时目录下，然后将服务器上的文件路径赋给MailMessage对象的Attachments属性即可。
“发送”按钮的事件如下：
private void btnSend_Click(object sender, System.EventArgs e)
{
//创建一个MailMessage对象
MailMessage aMail = new MailMessage();
//收信人地址
aMail.To = txtTo.Text ;
//发信人地址
aMail.From = txtFrom.Text ;
//主题
aMail.Subject = txtSubject.Text;
//正文
aMail.Body = txtBody.Text ;
//优先级
aMail.Priority = (MailPriority)ddPriority.SelectedIndex ;
//正文格式
aMail.BodyFormat = (MailFormat)ddBodyFormat.SelectedIndex ;
ddPriority.SelectedIndex = 1;
ddBodyFormat.SelectedIndex = 0;
if (File1.PostedFile.FileName != "" )
{
//上传文件的文件名（含完整路径）
string fileName = File1.PostedFile.FileName;
fileName = fileName.Substring (fileName.LastIndexOf (@"\"));
//使用SaveAs方法，将文件保存在项目路径\temp目录下，
//需要在项目路径下创建temp目录
fileName = Server.MapPath(@"\temp\" + fileName);
//要在C:\Inetpub\wwwroot文件夹下创建temp文件夹用于存放临时发送的文件
File1.PostedFile.SaveAs(fileName);
//添加附件
aMail.Attachments.Add(new MailAttachment(fileName));
}
string result = "";
try
{
//发送邮件
SmtpMail.Send(aMail);
result = "Email 发送成功！";
}
catch(Exception ex)
{
result = "Email 发送失败。" + ex.Message ;
}
Response.Write ("
");
}
03、C#DOS控制台程序连接SQLServer数据库的方法
①、建立一个C#的DOS控制台应用程序；
②、添加命名空间：using System.Data.SqlClient;；
③、连接到数据源，通过创建一个使用连接字符串的连接对象来完成：
SqlConnection connection = new SqlConnection(@"Data Source=MySER;Integrated Security=SSPI;Initial Catalog=pubs");
其中的MySER是安装了SQLServer的服务器名；pubs是SQLServer上的数据库名。
④、打开连接对象，建立到数据库的连接：
connection.Open();
⑤、创建对象命令，并给它提供SQL命令(从TN_RP表中选取 Ccname, MI_SQL_X,MI_SQL_Y 字段)：
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Ccname, MI_SQL_X,MI_SQL_Y from TN_RP";
⑥、使用DataReader以只读方式获取数据结果：
SqlDataReader reader = command.ExecuteReader();
⑦、从DataReader中获取数据：
while(reader.Read())
{
Console.WriteLine("{0}{1}\t{2}",reader["Ccname"],reader["MI_SQL_X"],reader["MI_SQL_Y"]);
}
⑧、关闭DataReader和SqlConnection。
-------------------------------------------------------------------------------------------------------------------------------------------------------
ADO.NET Terminology and Main Classes
The first new term you'll hear quite often regarding ADO.NET is that of a managed provider. This is simply the .NET equivalent of terminology that was originally introduced with OLEDB (and later used by its COM interface, ADO). In OLEDB, code that provides a generic interface to data is referred to as a provider. Therefore, since code written to run on top of the CLR is called "managed," we are given yet another new database term to remember. As of the time of this writing, the .NET Framework defines five managed providers:
- OLEDB: Supports data stores that have an OLEDB provider.
- ODBC: Supports data stores that have an ODBC driver.
- Oracle: A set of classes optimized for the Oracle database product.
- SQL CE: A .NET Compact Framework managed provider that supports Microsoft SQL Server CE.
- SQL Server: A set of classes that are optimized to support the Microsoft SQL Server database product.
While we're on the topic, I'll also be using the familiar terms data source and data consumer. (Data source is the generic name for the data being provided for consumption by the consumer.) Obviously, the consumer is any code that retrieves, stores, and manipulates data represented by the managed provider.
Like many other frameworks that you've seen throughout this book, ADO.NET is comprised of many classes. However, this chapter will focus on the following classes:
- Connection: Functions much like the ADO object of the same name and represents a connection to a data source.
- Command: Another holdover from ADO, the Command object represents a query or a command that is to be executed by a data source.
- CommandBuilder: Used to automatically generate the insertion, update, and delete commands for the data adapter object based on the select command. It is also used to provide optimistic concurrency for disconnected DataSet objects.
- DataSet: One of the key elements with ADO.NET is the DataSet. A little too involved to be defined with a single sentence, the DataSet represents an in-memory model of disconnected data and has built-in support for XML serialization. That latter capability is covered in Chapter 8, "Combining ADO.NET and XML."
- DbDataAdapter: The abstract base class for all data store.specific classes such as SqlDataAdapter, OracleDataAdapter, OleDbData Adapter, and so on.
- DataAdapter: The base class for the DbDataAdapter class.
- Data adapter: Not really a class, but a generic designation for one of the DbDataAdapter-derived classes.
- DataView: This class is most easily defined to MFC developers as the data equivalent of a CView class for data. For example, in a standard MFC document/view class you can build multiple views that are built on—but work with different parts of—the same data. Likewise, multiple DataView objects represent different views on the same DataSet.
- XmlDataDocument: Enables you to treat DataSet data as XML data in order to support things like XPath search expressions, XSL (eXtensible Stylesheet Language) transformations, and so on.
Now that you've been introduced to the terms, it's easier to define a managed provider as a group of classes that interface to the generic DataSet class to abstract you from the specifics of the data you are reading or modifying. For example, the System::Data::SqlClient namespace defines about 15 classes and several delegates that are optimized for use with the SQL Server database product. Among these classes are derived types of the base classes I mentioned in the previous list: SqlConnection, SqlDataAdapter, SqlCommand, and SqlCommandBuilder.
Let's now look at the DataSet class a bit more closely. The DataSet class is a collection of data structures (other classes) that are used to model relational data. The following list details the main classes that comprise either the DataSet class or one of its member classes:
- DataTable: If you're familiar with ADO, then at first glance you might be tempted to think of a DataSet class as being comparable to souped-up ADO Recordset objects. However, datasets are so encompassing that there is no equivalent in ADO for them. The DataTable class, on the other hand, is a more true ADO.NET equivalent of the ADO Recordset object, as it encapsulates a two-dimensional array (rectangle) of data organized into columns and rows.
- DataColumn: Within the DataTable class are a collection of Data Column definitions. As the DataRow class (described next) defines actual data, the DataColumn class defines the data store column definitions. Example members of this class are ColumnName and DefaultValue as well as Boolean properties such as AllowDBNull, AutoIncrement, and ReadOnly.
- DataRow: The DataRow class encapsulates the data for a given DataTable object in addition to defining many members that support the disconnected capabilities of the DataSet/DataTable. These members include support for tracking the current and original values of each column, the current state of the row (a DataRow State enumeration with such values as Added, Deleted, Detached, Modified, and Unchanged) and a connection to the parent table to support DataRelation via the GetParentRows and GetChildRows methods.
- DataRelation: DataRelation objects are used to define how multiple DataTables are associated. For example, it is quite common to use this feature when dealing with tables that have a parent/child relationship, such as order header and order detail tables. Using this feature, you can more easily navigate the related data of these two tables. This class is covered in more detail in the next chapter.
- Constraint: Each DataTable defines a collection of constraints that specify rules for maintaining data integrity. For example, when you delete a value that is used in one or more related tables, a ForeignKeyConstraint determines whether the values in the related tables are also deleted, set to null values, set to default values, or whether no action occurs.
Constructing and Filling DataSet Objects
Now that that you've been introduced to the main ADO.NET classes that will be used throughout this chapter, let's take a look at a code snippet that illustrates how to connect to and retrieve data from a data source. After the code snippet, I'll provide a walkthrough of the various classes that are being used here as well as a lot of not-so-obvious tasks that are being performed for us in order to facilitate a disconnected dataset.
SqlConnection* conn =
new SqlConnection(S"Server=fantine;"
S"Database=Northwind;"
S"Integrated Security=true;");
SqlDataAdapter* adapter =
new SqlDataAdapter(S"SELECT * FROM Employees", conn);
SqlCommandBuilder* cmd = new SqlCommandBuilder(adapter);
conn->Open();
DataSet* dataset = new DataSet();
adapter->Fill(dataset, S"AllEmployees");
conn->Close();
DataTableCollection* tables = dataset->Tables;
employeesTable = tables->Item[S"AllEmployees"];
While this code looks pretty straightforward, there's much more going on here than meets the eye.
- The first thing the code snippet does is to connect to the SQL Server sample database, Northwind, using the SqlConnection class.
| Specifying Connection Strings for Different Database Products
For this chapter,I chose to use the SQL Server database as it 's the most commonly used database among Visual C++/MFC professionals.In addition,while much of the code that you 'll see in this chapter can easily be massaged to work with any managed provider,the initialization of the Connection object is data source .speci .c.There- fore,if you are using another product,such as Oracle or Microsoft Access,or want to use the OLEDB or ODBC interfaces to these or other databases,the http://www. connectionstrings.com Web site is an invaluable resource,as it contains connection strings for virtually every data store. |
- Once that is done, the code uses a DbDataAdapter-derived class (SqlDataAdapter) designed specifically for SQL Server access. As mentioned in the previous section, the data adapter is what connects a dataset to the underlying data store. However, what's really interesting here is that while I'm passing a "select" value to the SqlData Adapter class' constructor, the various data adapter classes define four distinct commands (in the form of SqlCommand classes): Select Command, InsertCommand, UpdateCommand, and DeleteCommand. (From here on, the latter three commands will be referred to en masse as action commands.) One extremely important note to make here is that the data adapter does not automatically generate commands to reconcile changes made to a dataset based on the select statement used to construct the adapter. You must either set these commands yourself or use a command builder class, which segues nicely into the next items of interest from the code snippet.
- Once the data adapter has been constructed with the desired select command, an SqlCommandBuilder object is instantiated and associated with the data adapter. The command builder automatically generates the appropriate action commands (complete with the underlying SQL code, ADO.NET Command objects, and their associated Parameters collections) based on the adapter's Select Command.
- Next, the connection is opened. One thing to note here is that the data adapter is designed to minimize the time a connection stays open. As you see more code in this chapter, take note that the data adapter's associated connection is never explicitly opened or closed. Instead the adapter knows when it needs to connect and disconnect. For example, when calling the data adapter object's Update method in order to commit changes to the dataset, the data adapter will automatically use an already open connection to the data store or make the necessary connection and automatically disconnect when finished.
- After that, we're finally down to the ttDataSet object itself. To construct and fill the dataset, you can simply use the DataSet class's default constructor and then call the data adapter object's Fill method, passing the constructed DataSet object as the first parameter. The Fill method retrieves data from the underlying data store based on the data adapter's SelectCommand value. (In this example, that value was set when the SqlDataAdapter was constructed.) You'll also notice that I specified a literal value of "AllEmployees" for the second parameter to the Fill method. This value specifies the name that I wish to give the DataTable that will be constructed with the returned data. If I had not named the dataset's data table, it would have been named "Table" automatically. (When more than one data table are generated and not specifically named, they are assigned the names Table1, Table2, and so on.)
| Creating Multiple DataTables in a DataSet
While most of the chapter 's code snippets and demo applications will only read and modify a single table,there might be times when you 'll want a DataSet to contain multiple DataTable objects.The section entitled "Creating Multiple DataTables in a DataSet" will illustrate how to do this both by using multiple data adapters and also by combining multiple SELECT statements in a single data adapter in order to reduce round trips to the server. |
- At this point, the requested data is in the DataRow members of the DataTable members of the DataSet object. Therefore, the code can safely disconnect from the data source and continue working until it wants to commit any changes made to the data!
- The last thing I'll illustrate here before moving on to the next code snippet is how to retrieve the desired DataTable objects from the DataSet object. As you can see from the code, the DataSet class has a public property called Tables that is simply a collection of Data Table objects. As with accessing any other .NET collection with Managed Extensions, you can use one of two overloaded Item indexers—one accepts the relative index and the other the named entity. Therefore, as the data adapter in this code snippet only constructed a single DataTable object that was named "All Employees" in the Fill method, it can be retrieved either by name or by passing its index value of 0.
| Different Ways to Construct Datasets
There are three distinct methods to constructing and filling datasets.One way—used in this chapter—is from a data adapter (which is typically associated with a database). You can also construct a dataset programmatically from any data your application has access to,either read from another source or generated within the application.This technique—while not overly difficult—is not used very often and is beyond the scope of this chapter.Finally, you can also construct a DataSet object from an XML document in situations where you wish to treat XML data as you would any other data format. The topic of mixing ADO.NET and XML is covered in Chapter 8. |
Untyped vs.Typed Datasets
There are two basic ways to use the DataSet objects: untyped and typed. When using untyped datasets, you use the base BCL-provided DataSet objects and pass the relevant information that specifies which table, column, row, and so on that you're working with. For example, let's say that you're working with a row of data (represented by a DataRow object) for a table that contains a column named FirstName. For each row, you could access and modify the FirstName column as follows:
String firstName = row->Item[S"FirstName"]->ToString();
The DataRow—needing to be a generic interface for all data—provides methods for reading and updating column values, respectively, where you're responsible for specifying the column name and—if updating—an Object representing the value. This generic approach, which makes you responsible for the specifics, is used throughout all the DataSet classes. Therefore, the main drawback to untyped datasets is that the code is not type-safe. In other words, mistakes made in your code, such as misspelling the column name or passing an incompatible data type, will only be realized at runtime.
Typed datasets, on the other hand, are classes that are generated from a specified data store. It's important to realize that these classes are still directly derived from the ADO.NET base classes. However, they contain members specific to the data store schema and, as such, allow for compile-time error checking. To continue our Employees table example, a typed DataSet would include a DataRow-derived class called EmployeesRow. This class would then define members for each column in the Employees table, as shown in the following excerpt.
public: EmployeesDataSet::EmployeesRow* AddEmployeesRow
(
System::String* LastName,
System::String* FirstName,
System::DateTime HireDate,
System::Byte Photo[],
System::String* Notes,
System::Int32 ReportsTo
);
Using the typed dataset, our read and update code becomes the following:
String firstName = row->FirstName;
As you can see, the main benefits to typed datasets are better readability and compile-time type checking.as each column is a class member that is associated with its correct type within the class. To draw a parallel between typed datasets and our MFC world, you could say that typed datasets are analogous to using the MFC ODBC Consumer Wizard to generate a CRecordSet class. The main difference is that while the various ADO.NET classes can be bound to .NET Windows Forms controls, they were designed for a managed world; thus, there's nothing akin to RFX that will automatically bind the data to our MFC dialogs/views and controls. That we have to do manually.
I'll get into more of the advantages and disadvantages of using typed datasets in the section entitled "Working with Typed Datasets." However, I at least wanted you to know at this point that they both exist and to understand the main differences between them. Also note that while typed datasets have some obvious advantages, this chapter will use mostly untyped datasets for the following reasons:
- Untyped datasets allow you to see more easily what is really going on in code snippets as the client code explicitly states table and column names, store-procedure parameter names, and so on, as opposed to the actual database entity names being hidden in a class.
- Untyped datasets allow for shorter, more focused code snippets and demos. Otherwise, each demo would require extra steps to create the typed datasets and then would require a lot of cross referencing between the main code and the typed DataSet class code.
Basic Database Operations with ADO.NET
Whether you're working with a connected or disconnected data store, the majority of database operations involve NURD work—New, Update, Read, Delete. However, as this section will illustrate, many of the sometimes very tedious database operations are made much easier with the help of the various ADO.NET classes.
---------------------------------------------------------------------------------------------------------------------------------