ADO.NET4從入門到精通/微軟技術叢書
Bear在這裡買的
amazon的參考
-----------------------------------------------------------
作者：(美)帕特里克|譯者:賈洪峰
出版社：清華大學
ISBN：9787302277507
出版日期：2012/01/01
頁數：330
人民幣：RMB 59 元
-----------------------------------------------------------
P.17(原文P.19)
在向一個表的行集合中插入新行時觸發。在調用這個表的NewRow方法時，這一事件不會觸發，而是在向表中潻加新行時才會觸發。
TableNewRow
When a new row is inserted into a table’s collection of rows. This event is not raised when calling the table’s NewRow method, but instead when that new row is added to the table.
P.19(原文P.21)
C#
DataTable customer = new DataTable("Customer");
DataColumn keyField = new DataColumn("ID", typeof(long));
customer.Columns.Add(keyField);
P.20(原文P.23)
C#
DataTable customer = new DataTable("Customer");
customer.Columns.Add("ID", typeof(long));
customer.Columns.Add("FullName", typeof(string));
customer.Columns.Add("LastOrderDate", typeof(DateTime));
P.21(原文P.23)
.NET Framework包括一個 System.DBNull 類，可以用來測試 ADO.NET 字段中的 NULL 值。使用這個對象的 Value.Equals 方法來判斷一個值是否等價於 DBNull。
The .NET Framework includes a System.DBNull class that you can use to test for NULL values in ADO.NET fields. Use this object’s Value. Equals method to test a value for equivalence to DBNull.
C#
if (DBNull.Value.Equals(fieldValue))...
P.22(原文P.24)
C#
DataTable customer = new DataTable("Customer");
customer.Columns.Add("ID", typeof(long));
customer.Columns.Add("FullName", typeof(string));
// ----- Use ID for the primary key.
customer.PrimaryKey = new DataColumn[] {customer.Columns["ID"]};
P.34(原文P.38)
通常，在一個新行中，與每個行相關的數據在始時者是NULL(未賦值字段的數據庫狀態)。但是，如果DataColumn定義中包含DefaultValue設置，在所生成的行中會馬上出現已命名列的初始值。另外，任何一個設置了AutoIncrement及相關字段(通常是一個主鍵字段)的列中都會包含為該列生成的順序值。
Typically, the data associated with each column in the new row is initially NULL, the database state for an unassigned field. However, if a DataColumn definition includes a DefaultValue setting, that initial value will appear immediately in the generated row for the named column. Also, any column that has its AutoIncrement and related fields set (typically a primary key field) will include generated sequential values for that column.
P.34(原文P.38)
C#
oneRow.Item["ID"] = 123; // by column name
oneRow.Item[0] = 123; // by column position
DataColumn whichColumn = someTable.Columns[0];
oneRow.Item[whichColumn] = 123; // by column instance
由於Item是DataRow類的默認成員，所以在引用行值時，可以省略名稱。
Because Item is the default member for the DataRow class, you can omit the name when referencing row values, as shown here:
P.35(原文P.40)
將一個字段由非NULL狀態設置為NULL狀態，可以將.NET的DBNull類的值賦給它。
If for any reason you need to set a field to NULL from a non-NULL state, assign it with the value of .NET’s DBNull class.
可以在C#中使用DBNull.Value.Equals方法
DataRow類包含它自已的IsNull方法
C#
oneRow["Comments"] = System.DBNull.Value;
As mentioned in Chapter 2, “Building Tables of Data,” you can test field values in C# using the DBNull.Value.Equals method or in Visual Basic with the IsDBNull function. The DataRow class includes its own IsNull method; it is functionally equivalent to the methods from Chapter 2. Instead of passing the IsNull method a field value to test, you pass it the column’s name, the column’s position, or an instance of the column.
C#
if (oneRow.IsNull("Comments"))...
P.36(原文P.41)
C#
// ----- Assumes column 0 is numeric, 1 is string.
someTable.Rows.Add(new Object[] {123, "Fred"});
P.43(原文P.48)
ADO.NET仍然允許使用 Remove 和 RemoveAt方法。但是，這些方法不會從數據庫端的副本中清除該行，它們僅刪除這一行的本地DataTable.Rows副本，您必須使用這一行的Delete方法來確保在接受時從數據庫中清除該行。
Note When working with DataTable instances that are connected to true database tables, ADO.NET will still allow you to use the Remove and RemoveAt methods. However, these methods will not remove the row from the database-side copy. They remove only the local DataTable.Rows copy of the row. You must use the row’s Delete method to ensure that the row gets removed from the database upon acceptance.
P.43(原文P.49)
Item屬性返回的默認行版本是Current版本。
The default row version returned by the Item property is the Current version.
P.46(原文P.52)
另一個基本方法ClearErrors會清除一行中的所有先前錯誤通知。
Another essential method, ClearErrors, removes any previous error notices from a row.
P.46~47(原文P.52~53)
C#
// ----- Row-level monitoring.
oneRow.ClearErrors();
oneRow.BeginEdit();
oneRow.FullName = "Tailspin Toys"; // Among other changes
if (oneRow.HasErrors)
{
ShowFirstRowError(oneRow);
oneRow.CancelEdit();
}
else
oneRow.EndEdit();
// ----- Table-level monitoring. Perform row edits, then...
if (someTable.HasErrors)
{
DataRow[] errorRows = someTable.GetErrors();
ShowFirstRowError(errorRows[0]);
someTable.RejectChanges(); // Or, let user make additional corrections
}
else
someTable.AcceptChanges();
// ...later...
public void ShowFirstRowError(DataRow whichRow)
{
// ----- Show first row-level or column-level error.
string errorText = "No error";
DataColumn[] errorColumns = whichRow.GetColumnsInError();
if (errorColumns.Count > 0)
errorText = whichRow.GetColumnError(errorColumns[0]);
else if (whichRow.RowError.Length > 0)
errorText = whichRow.RowError;
if (errorText.Length == 0) errorText = "No error";
MessageBox.Show(errorText);
}
P.54(原文P.60)
C#
// ----- Single-part key.
DataRow matchingRow = someTable.Rows.Find(searchValue);
// ----- Multi-part key.
DataRow matchingRow = someTable.Rows.Find(new Object[]
{keyPart1, keyPart2, keyPart3});
P.54(原文P.60)
如果向一個沒有定義主鍵的表應用此方法，則會引發異常。
If no row matches the provided primary key, Find returns Nothing (in Visual Basic) or null (in C#). The method throws an exception if you apply it to tables with no defined primary key.
通過禁用約束條，可以向DataTable中添加兩個具有相同主鍵的行(在第5章進行討論)。在這些表中，Find方法僅返回第一個具有匹配主鍵值的行。
Note It is possible to add two rows with the same primary key to a DataTable by disabling its constraints (as discussed in Chapter 5, “Bringing Related Data Together”). In such tables, the Find method returns only the first row with a matching primary key value.
P.57(原文P.63~64)
DataColumn.Expression屬性
Table 4-1 Filter Expression Elements
Event Name Triggering Action
Column names
Any of the column names from the DataTable. Surround column names that contain embedded space characters or other nonalphanumeric characters with a set of square brackets, as in [Full Name] for a column named Full Name.
<, >, <=, >=, <>, =
Use the standard comparison operators to compare columns to literal values, to each other, or to more complex expressions.
IN
Match from a collection of comma-delimited elements. BillDenomination IN (5, 10, 20)
LIKE
Match a string pattern. The pattern can include zero or more occurrences of the wildcard character (“*” or “%”), but at the ends of the pattern string only, not in the middle.
ProductClass = 'AA*'
or:
ProductClass = 'AA%'
AND, OR, NOT
Use these Boolean operators to join multiple expressions together.
Parentheses
Force the order of expression evaluation with parentheses.
Literals
Literals include integers, decimals, numbers in scientific notation, strings in single quotes, and dates or times in # marks.
CONVERT
Convert an expression or column from one data type to another.
CONVERT(expression, new-type)
The list of allowed data types is pretty close to those allowed when creating data columns. There are also restrictions on which data types can be coerced into other types. See the Visual Studio online help for full details.
LEN
Returns the length of a string column or expression.
ISNULL
Returns an expression or a default expression if the first argument evaluates to NULL. Useful for ensuring that a NULL value does not appear in a calculation. For example, the following expression compares the FamilyMembers column to the value 2 when FamilyMembers is not NULL. However, if FamilyMembers evaluates to NULL, it defaults to 1 instead.
ISNULL(FamilyMembers, 1) >= 2
IIF
The ternary conditional function, similar to the If and IIf operators in Visual Basic, and to the :? operator in C#. The operator contains three arguments. If the first argument evaluates to true, the function returns the second argument, the “true” part. Otherwise, it returns the third argument, the “false” part.
IIF(Age >= 18, 'Adult', 'Minor')
TRIM
Trims whitespace from the ends of a string column or expression.
SUBSTRING
Returns a portion of a string column or expression, starting from a 1-based position and continuing on for a specific length count.
SUBSTRING(PhoneNumber, 1, 3)
P.60(原文P.67)
在對比字符串值時，Select方法默認悉略字符的大小寫。
The Select method ignores character casing by default when comparing string values.
P.61(原文P.68)
C#
// ----- Syntax using a DataColumn object.
DataColumn orderTotal = new DataColumn();
orderTotal.ColumnName = "Total";
orderTotal.DataType = typeof(decimal);
orderTotal.Expression = "Subtotal + ISNULL(Tax, 0)";
someTable.Columns.Add(orderTotal);
// ----- Syntax using Add arguments only.
someTable.Columns.Add("Total", typeof(decimal),
"Subtotal + ISNULL(Tax, 0)");
P.61(原文P.68)
在嘗試訪問表達式列之前，不會對其進行計算。
After being added to your table, you can query expression columns in Select statements or examine them with standard ADO.NET code just like static columns. Expression columns are not calculated until you attempt to access them. If there is anything wrong with the expression, such as including references to non-existent columns, the code accessing the column will throw an exception.
P.66(原文P.74)
但是，如果一個表名在數據集中是唯一的，那在查詢中就不會對其名稱區分大小寫。
Note A DataSet can contain two tables with the same name as long as their namespace values differ. Chapter 7, “Saving and Restoring Data,” discusses these namespaces. Also, if two tables share a common name (and namespace) but differ in the casing of those names (“CUSTOMERS” versus “customers”), the DataSet will treat them as distinct tables. When querying these tables, you must provide the same casing as the original table names, or else the query will fail. However, if a table name has no duplicate within a DataSet, its name in queries can be caseinsensitive.
P.71(原文P.80)
由於一個父記錄可能有多個子記錄，所以 GetChildRows 方法返回一個由匹配 DataRow 組成的數組。
Getting the child records for a parent row uses nearly identical code. Because a parent can have multiple children, the GetChildRows method returns an array of DataRow matches.
C#
DataRow[] orders = whichCustomer.GetChildRows("CustomerOrder");
P.72(原文P.81)
因此，在創建 DataRelation 實例時，此代碼向構造函數的第四個布林參數傳遞 False 值，告訴它省去這些約束條件。
Because the parent table can include duplicate values in its related column, this relationship doesn’t follow the normal rules for a key-based, one-to-many relationship. It is instead a form of many-to-many cardinality, albeit one that does not involve either table’s primary key. Normally, new DataRelation instances create special “constraint” objects that establish the relationship rules, such as the need for a unique primary key. (This next section, "Defining Table Constraints,” discusses these constraints.) In this many-to-many relationship, such constraints would generate errors. Therefore, when creating the DataRelation instance, the code passed a fourth Boolean argument with a value of False to the constructor, telling it to dispense with the constraints.
P.80~81(原文P.91)
C#
DataTable employees = new DataTable("Employee");
employees.Columns.Add("ID", typeof(int));
employees.Columns.Add("Gender", typeof(string));
employees.Columns.Add("FullName", typeof(string));
employees.Columns.Add("Salary", typeof(decimal));
// ----- Add employee data to table, then...
decimal averageSalary = (decimal)employees.Compute("Avg(Salary)", "");
P.81(原文P.91)
Compute的第二個參數是一個篩選器，它限制了此計算中包含的數據行。它接受一個布林條件表達式，類似於 DataTable.Select 方法調用中使用的表達式。
In the preceding code, the Compute method calculates the average of the values in the Salary column. The second argument to Compute is a filter that limits the rows included in the calculation. It accepts a Boolean criteria expression similar to those used in the DataTable.Select method call.
P.82(原文P.93)
在將這個列名插入表達式之前， Column Name 字段中顯示的"Child"前綴將被剝離。Compute方法不支持列名之前的Parent 和 Child 前綴。
Note The “Child.” prefix shown in the Column Name field is stripped out before the column name is inserted into the expression. The Compute method does not support the Parent and Child prefixes before column names.
P.84(原文P.94)
在沒有篩選表達式時，聚合總是使用表中的所有行計算其總和。
Expression columns typically compute a value based on other columns in the same row. You can also add an expression column to a table that generates an aggregate value. In the absence of a filtering expression, aggregates always compute their totals using all rows in a table. This is also true of aggregate expression columns. When you add such a column to a table, that column will contain the same value in every row, and that value will reflect the aggregation of all rows in the table.
P.87(原文P.98)
儘管ADO.NET查詢表達式支持"Parent"關鍵字，但不能在聚合函數中使用它。但可以向一個子表中添加一個表達式列，用來引用父表中的列數據。
Although ADO.NET query expressions support a “Parent” keyword, it can’t be used with the aggregation functions. Instead you use it to add an expression column to a child table that references column data from the parent table. For instance, if you had Customer (parent) and Order (child) tables linked by a customer ID, and the parent table included the address for the customer, you could include the city name in the child table using an expression column.
P.90(原文P.102)
ToTable接受一個由列名組成的數組，用於構建一個新的 DataTable 實例，它只包含經過篩選的行和指定的數據列。
The DataView.ToTable method provides the most convenient way to generate a subset of table rows while at the same time selecting only a subset of columns. ToTable accepts an array of column names to build a new DataTable instance that includes only the filtered rows and only the specified data columns.
P.113(原文P.128)
調用這個連接的Dispose方法將自動調用Close(如果還沒有關閉的話)。調用Close不會自動調用Dispose。
Note Calling the connection’s Dispose method will automatically call Close (if you haven’t done so already). Calling Close will not automatically call Dispose.
P.114(原文P.129)
為提高連接池的效率(連接池將在本章後面介紹)，最好盡可能晚地打開連接，並在此之後盡可能早地關閉它。
For effective connection pooling (discussed later in this chapter), it is best to open the connection as late as you can, and close it again as soon as you can after that.
P.117(原文P.132)
這些開發人員仍然會偶爾需要使連接在多個查詢間保持開放。例如，如果執行一個創建本地臨時表的查詢(所謂臨時表是指一個"#"符號開頭的SQL SERVER 表)，那就必須維持一個活動連接，以在多次查詢期間使用這些臨時表。
These developers still at times need to keep a connection open through multiple queries. For example, if you execute a query that creates local temporary tables (those SQL Server tables that begin with a single “#” symbol), you must maintain an active connection to use the tables across multiple queries. Also, committable multiupdate database transactions require a consistent connection experience to work properly.
P.117(原文P.132)
SqlConnection類還包括兩個方法: ClearPool 和 ClearAllPools ，分別用來清除相關池或者由提供程序當前管理的所有池。
You can turn off pooling for a specific connection by including the Pooling=false key-value pair in your connection string. The SqlConnection class also includes two methods—ClearPool and ClearAllPools—that let you clear its associated pool or all pools currently managed by the provider within your application respectively.
P.122(原文P.139)
此命令對象還支持非查詢操作的異步處理。它包含一對可以將操作放在其中的方法:BeginExecuteNonQuery 和 EndExecuteNonQuery 。BeginExecuteNonQuery方法用接口 System.IAsyncResult 返回一個對象，在處理結束時將其 IsCompleted 屬性設置為True。屆時，代碼必須調用 EndExecuteNonQuery 方法來完成該過程。
The command object also supports asynchronous processing of nonqueries. It includes a pair of methods—BeginExecuteNonQuery and EndExecuteNonQuery—that bracket the operation. The BeginExecuteNonQuery method returns an object with the interface System.IAsyncResult that sets its IsCompleted property to True when processing ends. At that point, your code must call the EndExecuteNonQuery method to complete the process.
P.123(原文P.139)
可以從回調代碼調用它
A variation of the BeginExecuteNonQuery method lets you specify a callback method and an optional object that will be passed to the callback method when the operation completes. You must still call EndExecuteNonQuery, although you can call it from within the callback code. Passing the SqlCommand object as the optional argument simplifies this process.
P.123(原文P.140)
要知道，Cancel方法也許能級時取消執行，也許不能及時取消，具體取決於處理的狀態。
The connection used by the command must remain open during processing. If you want to halt execution of the command before it completes, call the SqlCommand object’s Cancel method. Be aware that—depending on the state of processing—the Cancel method might or might not cancel the execution in time.
P.124(原文P.141)
因為ExecuteScalar 返回System.Object類型的數據，所以必須將其轉換為所需要的數據類型。以上方法可以為非數據結果返回System.DBNull。
Because ExecuteScalar returns data of type System.Object, you must coerce it into the expected data type. The method can return System.DBNull for nondata results.
P.124(原文P.141)
C#
// ----- Pretend the ...'s represent actual fields, and that
// WorkTable.ID is the name of the primary key.
string sqlText = @"INSERT INTO WorkTable (...)
OUTPUT INSERTED.ID VALUES (...)";
SqlCommand dataAction = new SqlCommand(sqlText, linkToDB);
int newID = (int)dataAction.ExecuteScalar();
P.126(原文P.143)
默認情況下，SQL SERVER 僅允許同時打開一個讀取器。
Always call the reader’s Close or Dispose method when finished. By default, SQL Server will permit only a single reader to be open at once. To open another reader, you must close the previous one. This also applies to other types of queries. Statements issued through the SqlCommand.ExecuteNonQuery method will also fail if a SqlDataReader is open and in use.
P.126(原文P.143)
MultipleActiveRecordSets=True，那就可以一次打開多個讀取器，並在一個讀取器處於打開狀態時處理其它命令。
Note If you include the MultipleActiveRecordSets=True key-value pair in the SQL Server connection string used to access the database, you will be able to open multiple readers at once and process other commands while a reader is open. However, be careful when using this feature because you won’t get a warning if you inadvertently leave a reader open.
P.127(原文P.144)
這些方法僅接受一個序數位置: 如果希望在使用它們時提供字段名，必須使用 GetOrdinal 方法將名字轉換為它的位置。
For strongly typed access to fields, the data reader exposes a seemingly endless number of data-returning methods with names that indicate the format of the resulting value. For example, the SqlDataReader.GetDecimal method returns a System.Decimal value from one of the row’s fields. These methods accept only an ordinal position; if you want to use them with a field name, you must convert the name to its position using the GetOrdinal method.
P.138(原文P.157)
不要忘了在名字的開頭加上@符號。
ParameterName The name of the parameter; that is, the placeholder. Don’t forget to include the @ sign at the start of the name.
P.138(原文P.157)
DbType 和 SqlDbType 是同一屬性
DbType or SqlDbType One of the System.Data.SqlDbType enumeration values, which all parallel the available data types in SQL Server. For example, SqlDbType.VarChar maps to SQL Server’s varchar column type. Both DbType and SqlDbType refer to the same property; update either one as needed.
P.138(原文P.157)
使用Value來處理那些使用標準.NET數據類型定義的數據。而那些在格式上更接近於SQL SERVER數據類型的數據，則使用SqlValue屬產來處理，通過System.Data.SqlTypes命名空間的類來表示
Value and SqlValue The actual value that will replace the placeholder in the SQL statement. Use Value to work with data defined using the standard .NET data types. Use the SqlValue property instead to work with data in a format that more closely resembles SQL Server’s data types, and as expressed through the classes in the System.Data.SqlTypes namespace.
P.141(原文P.160)
每個可替換元素沒有在占位符名稱前面加上@符號前綴，而是在命令文本中顯示為一個無名字的問號(?)
但 所添加的每個OleDbParameter 或 OdbcParameter 實例仍然應當包含以@為前綴的名稱。
The OLE DB and ODBC providers also include support for parameterized queries. However, the definitions of both the command text and the associated parameters vary somewhat from the SQL Server implementation. Instead of including placeholder names prefixed with @ signs, each replaceable element appears as a nameless question mark (?) in the command text. Parameters added to the associated OleDbCommand or OdbcCommand instance must be added in the order indicated by the placeholders. Although the command text does not include parameter names, each added OleDbParameter or OdbcParameter instance should still include @-prefixed names.
P.142(原文P.161)
和標準查詢一樣，每個參數包含一個以 @ 為前綴的名稱和數據類型。
The third difference is in how you name the parameters. As with standard queries, each parameter includes an @-prefixed name and a data type, plus other optional settings you might want to configure. Unlike standard queries, you have no flexibility in how you define the parameter names. They must match precisely the parameter names used when the stored procedure was defined within SQL Server.
P.143(原文P.162)
C#
// ----- Use a stored procedure to add a new building location.
string sqlText = "dbo.AddLocation";
SqlCommand locationCommand = new SqlCommand(sqlText, linkToDB);
locationCommand.CommandType = CommandType.StoredProcedure;
// ----- Add the input parameter: locationName.
SqlParameter workParameter = locationCommand.Parameters.AddWithValue(
"@locationName", LocationNameField.Text.Trim());
workParameter.Size = 50;
// ----- Add the output parameter: newID.
workParameter = locationCommand.Parameters.Add("@newID", SqlDbType.BigInt);
workParameter.Direction = ParameterDirection.Output;
// ----- Add the return value parameter. The name is not important.
workParameter = locationCommand.Parameters.Add("@returnValue", SqlDbType.Int);
workParameter.Direction = ParameterDirection.ReturnValue;
// ----- Add the location.
locationCommand.ExecuteNonQuery();
// ----- Access returned values as:
// locationCommand.Parameters["@newID"].Value
// locationCommand.Parameters["@returnValue"].Value
P.152(原文P.172)
前面的例子都不會顯式打開連接。如果這個命令的連接還沒有打開，Fill方法會為你打開，並在完成操作之後關閉連接。
Neither of the preceding examples opened the connection explicitly. If the command’s connection isn’t open yet, the Fill method opens it for you—and closes it when the operation completes.
P.154(原文P.175)
在檢索多個數據表時，調用SqlDataAdapter.FillSchema只會查看第一個結果集的架構，後續集合的架構只能作為 Fill 方法的副效應導入。
Note When retrieving multiple tables of data, a call to SqlDataAdapter.FillSchema examines only the schema of the first result set. The schemas of subsequent sets can be imported only as a side effect of the Fill method.
P.155(原文P.176)
C#
// ----- Build the selection query.
SqlDataAdapter unitAdapter = new SqlDataAdapter();
SqlCommand unitCommand = new SqlCommand(
"SELECT * FROM UnitOfMeasure", linkToDB);
unitAdapter.SelectCommand = unitCommand;
// ----- Build the insertion query.
unitCommand = new SqlCommand(
@"INSERT INTO UnitOfMeasure (ShortName, FullName)
VALUES (@ShortName, @FullName); SET @ID = @@IDENTITY;", linkToDB);
unitCommand.Parameters.Add("@ShortName", SqlDbType.VarChar, 15, "ShortName");
unitCommand.Parameters.Add("@FullName", SqlDbType.VarChar, 50, "FullName");
SqlParameter param =
unitCommand.Parameters.Add("@ID", SqlDbType.BigInt, 0, "ID");
param.Direction = ParameterDirection.Output;
unitAdapter.InsertCommand = unitCommand;
// ----- Build the revision query.
unitCommand = new SqlCommand(
@"UPDATE UnitOfMeasure SET ShortName = @ShortName,
FullName = @FullName WHERE ID = @ID", linkToDB);
unitCommand.Parameters.Add("@ShortName", SqlDbType.VarChar, 15, "ShortName");
unitCommand.Parameters.Add("@FullName", SqlDbType.VarChar, 50, "FullName");
param = unitCommand.Parameters.Add("@ID", SqlDbType.BigInt, 0, "ID");
param.SourceVersion = DataRowVersion.Original;
unitAdapter.UpdateCommand = unitCommand;
// ----- Build the deletion query.
unitCommand = new SqlCommand(
"DELETE FROM UnitOfMeasure WHERE ID = @ID", linkToDB);
param = unitCommand.Parameters.Add("@ID", SqlDbType.BigInt, 0, "ID");
param.SourceVersion = DataRowVersion.Original;
unitAdapter.DeleteCommand = unitCommand;
P.157(原文P.179)
With unitCommand.Parameters.Add("@ID", SqlDbType.BigInt, 0, "ID")
.SourceVersion = DataRowVersion.Original
End With
P.159(原文P.181)
SqlCommandBuilder只能與單表查詢一起使用。
SqlCommandBuilder can be used only with single-table queries. You should not use it with joined-table queries.
P.159(原文P.181)
所選記錄的架構必須包含一個主鍵或者唯一值列。
The schema of the selected records must include at least one primary key or uniquevalue column. Tables defined without primary keys or unique columns will not work with command builders.
P.159(原文P.181)
如果因為任何原因修改了與數據適配器相關聯的SelectCommand ，必須調用SqlCommandBuilder.RefreshSchema方法來調整所生成的查詢。
If for any reason you modify the SelectCommand associated with the data adapter, you must call the SqlCommandBuilder.RefreshSchema method to adjust the generated queries.
P.164(原文P.186)
要強制數據適配器將輸入記錄移動到正常表中，可以在調用Fill 方法之前，向表映射集合中添加新的 System.Data.Common.DataTableMapping 對象。
To coerce the data adapter into moving the incoming records into the correct table, add new System.Data.Common.DataTableMapping objects to the table mapping collection before calling the Fill method.
P.165(原文P.187)
SqlDataAdapter.MissingMappingAction屬性決定當表和列映射規則沒有包含輸入表或輸入列時，應當做什麼。
You are not required to set up a mapping for every incoming table or column. The data adapter includes two properties that establish the rules for handling missing targets. The SqlDataAdapter.MissingMappingAction property determines what should be done when the table and column mapping rules do not include one of the incoming tables or columns (or any of them). It is set to one of the MissingMappingAction enumerated values.
P.169(原文P.194)
ADO.NET的重點是繼開連接的數據處理，所以採用了樂觀並發。
ADO.NET, with its focus on disconnected data processing, uses optimistic concurrency.
P.171(原文P.196)
事務只能在打開的數據庫連接上工作，所以必須首先調用這個連接對象的 Open 方法。
Instead of creating instances of SqlTransaction directly, you generate connection-specific transactions using the SqlConnection object’s BeginTransaction method. Transactions work only on open database connections, so you must call the connection object’s Open method first.
P.173(原文P.198)
SELECT語句 在默認情況下可能會對返回記錄應用"讀取鎖"。為了避免 這些鎖，需要將SELECT語句排除在事務之外，或者在SQL SERVER SELECT語句中使用 WITH (NOLOCK)提示。
If you include SELECT statements in your transactions, especially on records that will not be modified as part of the transaction, there is a chance that these selected records might become locked during the transaction, preventing other users from making modifications to them, or even reading them. Depending on the configuration of your SQL Server instance, SELECT statements might apply “read locks” on the returned records by default. To avoid such locks, exclude SELECT statements from your transactions or use the WITH (NOLOCK) hint in your SQL Server SELECT statements.
SELECT * FROM OrderEntry WITH (NOLOCK)
WHERE OrderDate >= DATEADD(day, -3, GETDATE())
P.177(原文P.202)
正常情況下，事務是一種要麼全部完成，要麼一點也不完成的操作。但是，SQL SERVER 提供程序還包含了對"保存點"的支持，保存點是一些已命名的部份事務，可以獨立進行回滾。
Normally a transaction is an all-or-nothing operation. However, the SQL Server provider also includes support for savepoints, which are named partial transactions that can be independently rolled back.
為向一個事務中添加保存點，可以調用 SqlTransaction 對象的 Save 方法，將新保存點的名稱傳遞給它。
To add a savepoint to a transaction, call the SqlTransaction object’s Save method, passing it the name of the new savepoint.
P.177(原文P.203)
C#
// ----- Run the pre-savepoint transaction statements.
SqlCommand firstCommand = new SqlCommand(sqlText1, linkToDB, envelope);
firstCommand.ExecuteNonQuery();
// ----- Mark this place for possible partial rollback.
envelope.Save("HalfwayPoint");
// ----- Run the post-savepoint transaction statements.
SqlCommand secondCommand = new SqlCommand(sqlText2, linkToDB, envelope);
secondCommand.ExecuteNonQuery();
P.178(原文P.203)
根據需要，可以在一個事務中準備任意多個保存點。要保存或取消此事務的所有更改，仍然必須對該事務發出最終的Commit 或 Rollback。
You can issue as many savepoints as you need within a transaction. You must still issue a final Commit or Rollback on the transaction to save or cancel the transaction’s overall changes.
P.178(原文P.204)
分布式事務是通過 System.Transactions.TransactionScope 類發生的。
向應用程序中添加對 System. Transactions.dll 庫的引用。
Distributed transactions occur through the System.Transactions.TransactionScope class. This class is not part of ADO.NET, but ADO.NET does include automatic support for it when you use it in your application. To access this class, you must add a reference to the System. Transactions.dll library in your application through the Project | Add Reference menu command
in Visual Studio.
C#
using System.Transactions;
// ----- Later...
using (TransactionScope envelope = new TransactionScope())
{
// ----- Include all relevant ADO.NET commands here.
P.212(原文P.)
實體SQL不支持 SQL中用於指定一個表中所有列的符號。要返回每個匹配行的整體內容，在 SELECT 子句中使用表別名本身，或者逐一列出列和屬性。
Entity SQL does not support the * symbol used in SQL to specify all columns in a table. To return the entire content of each matching row, use the table alias by itself in the SELECT clause, or list the columns and properties individually.
SELECT c FROM Customers AS c
P.213(原文P.)
可以強制查竘將(每個返回記錄中的)結果作為一個值返回，而不是作為包含一個值的行。為達到這一目的，可以在指定投之前使用VALUE關鍵字。
You can force the query to return the result (in each returned record) as a distinct value instead of as a row containing one distinct value. To accomplish this, use the VALUE keyword before the field specification.
P.215(原文P.250)
特殊的IS和IS NOT運算符支持與NULL文本進行對比。
The logical operators AND, OR, and NOT combine different logical expressions, and can be replaced with the C-like synonyms &&, ||, and !, respectively. The special IS and IS NOT operators enable comparisons with the null literal.
P.215(原文P.251)
實體SQL沒有用位運算功能來重載邏輯運算符，而是包含了特有的位運算函數:BitWiseAnd, BitWiseNot, BitWiseOr, and BitWiseXor。
Bitwise functions Instead of overloading the logical operators with bitwise functionality, Entity SQL includes distinct bitwise functions: BitWiseAnd, BitWiseNot, BitWiseOr, and BitWiseXor.
P.217(原文P.253)
-- Calculate last year's small monthly order totals.
SELECT WhichMonth, SUM(o.Total) AS TotalOfAllOrders
FROM OrderEntries AS o
WHERE Year(o.OrderDate) = Year(CurrentDateTime()) - 1
GROUP BY Month(o.OrderDate) AS WhichMonth
HAVING SUM(o.Total) < 1000
ORDER BY WhichMonth
P.217(原文P.253)
-- Return page 2 from a list of matching products, 50 per page.
SELECT p.ID, p.ProductName, p.Description, p.Price
FROM Products AS p
ORDER BY ProductName SKIP 50 LIMIT 50
P.233(原文P.271)
要接收實體框架中的更改，可以調用活動上下文對象的 SaveChanges 方法。
Just as with ADO.NET, you must take one additional step that is needed to accept all pending changes. To accept changes in the Entity Framework, call the SaveChanges method of the active context object. This completes the update process and persists all changes to the underlying data source.
C#
context.SaveChanges();
P.234(原文P.273)
用戶編寫的代碼負責提供所有必要的屬性值(不接收NULL值而且沒有默認值的屬性)。如果概念模型或底層數據庫都沒有提供主鍵值，也必須由用戶編寫的代碼提供他們。
Your code is responsible for supplying all required property values (those that don’t accept NULL values and that don’t have defaults). If neither the conceptual model nor the underlying database provides primary key values, your code must supply them as well.
P.235(原文P.273)
C#
context.Refresh(RefreshMode.StoreWins);
P.250(原文P.290)
LINQ使用可以為NULL的類型來表示那些在查詢結果中包含缺失值的字段。
Nullable types The .NET Framework has always supported nullable reference types, allowing your code to assign a value of null (C#) or Nothing (Visual Basic) to, say, a string instance variable. Nullable types extend this same support to value types, such as System.Int32 and System.Bool. LINQ uses nullable types to represent fields that contain missing values in query results.
P.256(原文P.296)
C#
var results = from tr in transport
orderby tr.SpeedClass descending, tr.Name
select tr;
P.256(原文P.297)
C#
// ----- This sorts by SpeedClass (descending), Name (ascending).
var results = transport.OrderByDescending(
tr => tr.SpeedClass).ThenBy(tr => tr.Name);
P.257(原文P.298)
C#
var results = from tr in transport
from sp in speed
select new { tr.Name, tr.SpeedClass, SpeedName = sp.Name };
P.257(原文P.298)
C#
var results = from tr in transport
join sp in speed on tr.SpeedClass equals sp.ClassID
select new { tr.Name, tr.SpeedClass, SpeedName = sp.Name };
P.258(原文P.299)
請注意，這里使用 Equals 關鍵字而不是等號來連接聯接字段對。
Note that you use the Equals keyword rather than an equals sign to pair the joined fields. For multicolumn relationships, the On clause includes an And keyword that works much like the conditional And clause.
P.258(原文P.299)
另外 添加 Into 子句可以定義下屬集合的列或屬性。
The syntax to produce a group join parallels that of a standard inner join, but you add the Group keyword just before Join (Visual Basic only). An additional Into clause defines the columns or properties of the subordinate collection. Within this clause, the special Group keyword refers to the entire collection (again, Visual Basic only).
P.259(原文P.300)
單獨使用Take字句時，相當於SQL SERVER中TOP關鍵字的功能。
The Skip and Take clauses let you generate paged results, returning a limited number of objects in the output collection. Each keyword is followed by a numeric count that indicates the number of records to skip (Skip) or include (Take). You can use either or both of these clauses in your query. Using the Take clause alone parallels the functionality of the TOP keyword in SQL Server.
P.259(原文P.300)
C#
// ----- Returns just the first result, not a collection.
var result = (from tr in transport select tr).First();
// ----- Counts the returned records.
int result = (from tr in transport select tr).Count();
P.261(原文P.302)
C#
// ----- Vehicles by wheel count.
var results = from tr in transport
group tr by tr.Wheels into g
orderby g.Key
select new { g.Key, HowMany = g.Count(tr => true) };
P.261(原文P.303)
C#
var allTheNames = (from tr in transport
select tr.Name).Union(
from sp in speed
select sp.Name);
P.265(原文P.307)
因為Field的實現使用了泛型，所以還必須使用適當的語言語法附加一個類型名稱，將該列的名稱作為參數傳遞給Field。
To cast a field, add the Field extension method to the end of the range variable (the range variables in the previous code sample are cu and ord). Because the implementation of Field uses generics, you must also attach a type name using the language-appropriate syntax. Pass the name of the column as an argument to Field.
P.274(原文P.318)
var result2 = from cu in result.ToArray()
from sts in statusTable
where cu.StatusCode == sts.Code
select new { cu.CustomerID, cu.CustomerName, cu.OrderID,
OrderStatus = sts.Description, cu.OrderDate, cu.OrderTotal };
P.277(原文P.322)
只有特定的.NET方法才有相應的數據庫級別方法
Only certain .NET methods have database-level equivalents, and it’s not always immediately clear which local methods will be passed to the database without your interaction. Math.Round converts to SQL Server’s ROUND, but Math.Sqrt generates an error, even though Transact-SQL includes a SQRT function.
P.277(原文P.323)
要使用這些規範函數，一定要對 System.Data.Objects 進行using，然後再在查竘中的函數調用前面加上 EntityFunctions 類名前綴。
To use the canonical functions, be sure to have a using (C#) or Imports (Visual Basic) reference to System.Data.Objects and then prefix the function calls in your query with the EntityFunctions class name.
C#
var result = from cu in context.Customers
where EntityFunctions.Left(cu.FullName, 1) == "A"
P.278(原文P.323)
這些數據庫函數的使用和規範函數一樣，首先添加對 System.Data.Objects.SqlClient 的using引用，然後將SqlFunctions類名附加在查詢中所使用的每個數據庫函數的開頭。
The database functions work just like the canonical functions. First include an Imports (Visual Basic) or using (C#) reference to System.Data.Objects.SqlClient and then attach the SqlFunctions class name to the start of each database function used in your query.
P.278(原文P.324)
C#
var result = from ord in context.OrderEntries
select new { ord.ID, ord.OrderCustomer.FullName,
LateDate = SqlFunctions.DateAdd("day", 90, ord.OrderDate) };
P.283(原文P.329)
因為實體和它們的屬性都是強類型化的，所以只需要在LINQ查詢中包含它們即可，不需要添加另外的數據類型轉換代碼。
This chapter introduced LINQ to Entities, a LINQ provider that allows you to run queries against entity types within a generated Entity Framework conceptual model. Because entities and their properties are strongly typed, it’s simple to include them in LINQ queries without additional data type conversion code. Also, the syntax used in the query is nearly identical to the basic LINQ to Objects form.
P.315(原文P.366)
DataKeyNames="CourseID" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display."
P.315(原文P.366)
這個控件還包含了一個 EmptyDataText 特性，如果源中缺少數據記錄，則用這個特性在運行的應用程序中調整網格內容。
The control’s DataSourceID references the SqlDataSource instance that will supply the bound data. Within the control’s Columns tag, a collection of asp:BoundField instances and their DataField attributes identify the path needed to locate the display data within the source. The control also includes an EmptyDataText attribute that adjusts the grid’s content in the running application if the source lacks data records. It’s a surprisingly small amount of code for the functionality it brings to the web page, and it works without adding a single line of custom C# or Visual Basic code.
