摘自《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.
---------------------------------------------------------------------------------------------------------------------------------