Sybex -C.Sharp Database Programming

Chia sẻ: Gacon | Ngày: | Loại File: PDF | Số trang:16

Thêm vào BST

Báo xấu

117
lượt xem 25
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Gets or sets the action to take when the incoming table or column doesn't have a matching table or column in the TableMappings collection.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Sybex -C.Sharp Database Programming

ADO.NET Tài liệu tham khảo: Sybex - C.Sharp Database Programming Mục lục MỤC LỤC ............................................................................................................................................................................... 1 TỔNG QUAN VỀ ADO.NET VÀ DATABASE .......................................................................................................... 2 1 Tổng quan về Database ............................................................................................................. 2 1.1 Tổng quan về lập trình dữ liệu và các lớp ADO.Net ................................................................ 2 1.2 CÁC LỚP ADO.NET .................................................................................................................................................... 2 2 Lớp kết nối ................................................................................................................................ 2 2.1 Thuộc tính ......................................................................................................................... 2 2.1.1 Phương thức ...................................................................................................................... 3 2.1.2 Sự kiện .............................................................................................................................. 3 2.1.3 Lớp thi hành các truy vấn ......................................................................................................... 3 2.2 Thuộc tính ......................................................................................................................... 3 2.2.1 Phương thức ...................................................................................................................... 4 2.2.2 Sự kiện .............................................................................................................................. 5 2.2.3 Lớp đọc dữ liệu ......................................................................................................................... 5 2.3 Thuộc tính ......................................................................................................................... 5 2.3.1 Phương thức ...................................................................................................................... 5 2.3.2 Sự kiện .............................................................................................................................. 7 2.3.3 Lớp chuyển dữ liệu sang DataSet ............................................................................................. 7 2.4 Thuộc tính ......................................................................................................................... 7 2.4.1 Phương thức ...................................................................................................................... 9 2.4.2 Sự kiện .............................................................................................................................. 9 2.4.3 Lớp DataSet chứa dữ liệu ....................................................................................................... 10 2.5 Thuộc tính ....................................................................................................................... 10 2.5.1 Phương thức .................................................................................................................... 11 2.5.2 Sự kiện ............................................................................................................................ 12 2.5.3 LẬP TRÌNH CƠ BẢN VỚI ADO.NET ..................................................................................................................... 12 3 Kết nối .................................................................................................................................... 12 3.1 Kết nối dữ liệu Access .................................................................................................... 12 3.1.1 Kết nối dữ liệu Sql Server............................................................................................... 12 3.1.2 Kết nối dữ liệu Oracle ..................................................................................................... 13 3.1.3 Thi hành truy vấn hoặc Procedure .......................................................................................... 13 3.2 Các truy vấn .................................................................................................................... 13 3.2.1
3.2.2 Procedure ........................................................................................................................ 13 Thi hành truy vấn hoặc procedure .................................................................................. 13 3.2.3 Đọc dữ liệu ............................................................................................................................. 13 3.3 Đọc dữ liệu sang DataSet ............................................... Error! Bookmark not defined. 3.3.1 Đọc dữ liệu sang các thành phẩn trên Form ................... Error! Bookmark not defined. 3.3.2 Chuyển dữ liệu sang DataSet .................................................................................................. 14 3.4 Chuyển dữ liệu từ Database sang DataSet ...................................................................... 14 3.4.1 Chuyển dữ liệu từ Form sang DataSet ............................................................................ 15 3.4.2 Làm việc trên DataSet ............................................................................................................ 15 3.5 3.5.1 DataSet ............................................................................................................................ 15 3.5.2 DataTable ........................................................................................................................ 15 3.5.3 DataRow ......................................................................................................................... 15 3.5.4 DataColumn .................................................................................................................... 15 3.5.5 DataView ........................................................................................................................ 15 Làm việc với dữ liệu liên kết .................................................................................................. 15 3.6 Cập nhật dữ liệu ...................................................................................................................... 15 3.7 LẬP TRÌNH NÂNG CAO VỚI ADO.NET ............................................................................................................... 15 4 4.1 Advanced SqlTransaction Control .......................................................................................... 15 4.2 ASP.NET ................................................................................................................................ 15 Làm việc với XML ................................................................................................................. 15 4.3 Dịch vụ Web ........................................................................................................................... 15 4.4 1 TỔNG QUAN VỀ ADO.NET VÀ DATABASE 1.1 Tổng quan về Database 1.2 Tổng quan về lập trình dữ liệu và các lớp ADO.Net 2 CÁC LỚP ADO.NET 2.1 Lớp kết nối 2.1.1 Thuộc tính SqlConnection PROPERTIES PROPERTY TYPE DESCRIPTION ConnectionString string Gets or sets the string used to open a database. ConnectionTimeout int Gets the number of seconds to wait while trying to establish a connection to a database. The default is 15 seconds. Database string Gets the name of the current database (or the database to be used once the connection to the database is made). DataSource string Gets the name of the database server.
SqlConnection PROPERTIES PROPERTY TYPE DESCRIPTION PacketSize int Gets the size (in bytes) of network packets used to communicate with SQL Server. This property applies only to the SqlConnection class. The default is 8,192 bytes. ServerVersion string Gets a string containing the version of SQL Server. State ConnectionState Gets the current state of the connection: Broken, Closed, Connecting, Executing, Fetching, or Open. These states are covered later in the "Getting the State of a Connection" section. WorkstationId string Gets a string that identifies the client computer that is connected to SQL Server. This property applies only to the SqlConnection class. 2.1.2 Phương thức SqlConnection METHODS METHOD RETURN DESCRIPTION TYPE BeginTransaction() SqlTransaction Overloaded. Begins a database transaction. ChangeDatabase() void Changes the current database for an open connection. Close() void Closes the connection to the database. CreateCommand() SqlCommand Creates and returns a command object. Open() void Opens a database connection with the property settings specified by the ConnectionString property. 2.1.3 Sự kiện SqlConnection EVENTS EVENT EVENT HANDLER DESCRIPTION StateChange StateChangeEventHandler Fires when the state of the connection is changed. InfoMessage SqlInfoMessageEventHandler Fires when the database returns a warning or information message. 2.2 Lớp thi hành các truy vấn 2.2.1 Thuộc tính SqlCommand PROPERTIES PROPERTY TYPE DESCRIPTION CommandText string Gets or sets the SQL statement, stored procedure call, or table to retrieve from.
SqlCommand PROPERTIES PROPERTY TYPE DESCRIPTION CommandTimeout int Gets or sets the number of seconds to wait before ending an attempt to execute the command. The default is 30 seconds. CommandType CommandType Gets or sets a value that indicates how the CommandText property is to be interpreted. Valid values are CommandType.Text, CommandType .StoredProcedure, and CommandType .TableDirect. Text indicates the command is a SQL statement. StoredProcedure indicates the command is a stored procedure call. TableDirect indicates the name of a table, for which all rows and columns are to be retrieved. The default is Text. Connection string Gets the name of the database connection. DesignTimeVisible bool Gets or sets a Boolean value that indicates whether the Command object is visible in a Windows Forms Designer control. The default is false. Parameters SqlParameterCollection Gets the parameters (if any) to supply to the command. When using a SqlConnection, the parameters are stored in a SqlParameterCollection object. Transaction SqlTransaction Gets or sets the database transaction for the command. UpdatedRowSource UpdateRowSource Gets or sets how the command results are to be applied to a DataRow object when the Update() method of a DataAdapter object is called. 2.2.2 Phương thức SqlCommand METHODS METHOD RETURN DESCRIPTION TYPE Cancel() void Cancels the execution of the command. CreateParameter() SqlParameter Creates a new parameter for the command. ExecuteNonQuery() int Used to execute SQL statements that don't return a result set. These statements include INSERT, UPDATE, and DELETE statements, Data Definition Language statements, or stored procedure calls that don't return a result set. The int value returned is the number of database rows affected by the command, if any. ExecuteReader() SqlDataReader Used to execute SQL SELECT statements, TableDirect commands, or stored procedures that return a result set. Returns the result set in a DataReader object. ExecuteScalar() object Used to execute SQL SELECT statements that return a
SqlCommand METHODS METHOD RETURN DESCRIPTION TYPE single value (any other values are ignored). Returns the result of the command as an object. ExecuteXmlReader() XmlReader Used to execute SQL SELECT statements that return XML data. Returns the result set in an XmlReader object. Applies only to the SqlCommand class. Prepare() void Creates a prepared version of the command. Sometimes results in faster execution of the command. ResetCommandTimeout() void Resets the CommandTimeout property to its default value. 2.2.3 Sự kiện: Không có! 2.3 Lớp đọc dữ liệu 2.3.1 Thuộc tính SqlDataReader PROPERTIES PROPERTY TYPE DESCRIPTION Depth int Gets a value indicating the depth of nesting for the current row. FieldCount int Gets the number of columns in the current row. IsClosed bool Gets a bool value indicating whether the data reader is closed. RecordsAffected int Gets the number of rows added, modified, or removed by execution of the SQL statement. 2.3.2 Phương thức SqlDataReader METHODS METHOD RETURN DESCRIPTION TYPE GetBoolean() bool Returns the value of the specified column as a bool. GetByte() byte Returns the value of the specified column as a byte. GetBytes() long Reads a stream of byte values from the specified column into a byte array. The long value returned is the number of byte values read from the column. GetChar() char Returns the value of the specified column as a char. GetChars() long Reads a stream of char values from the specified column into a char array. The long value returned is the number of char values read from the column. GetDataTypeName() string Returns the name of the source data type for the specified
SqlDataReader METHODS METHOD RETURN DESCRIPTION TYPE column. GetDateTime() DateTime Returns the value of the specified column as a DateTime. GetDecimal() decimal Returns the value of the specified column as a decimal. GetDouble() double Returns the value of the specified column as a double. GetFieldType() Type Returns the Type of the specified column. GetFloat() float Returns the value of the specified column as a float. GetGuid() Guid Returns the value of the specified column as a globally unique identifier (GUID). GetInt16() short Returns the value of the specified column as a short. GetInt32() int Returns the value of the specified column as an int. GetInt64() long Returns the value of the specified column as a long. GetName() string Returns the name of the specified column. GetOrdinal() int Returns the numeric position, or ordinal, of the specified column (first column has an ordinal of 0). GetSchemaTable() DataTable Returns a DataTable that contains details of the columns stored in the data reader. GetSqlBinary() SqlBinary Returns the value of the specified column as a SqlBinary object. The SqlBinary class is declared in the System.Data.SqlTypes namespace. All the GetSql* methods are specific to the SqlDataReader class. GetSqlBoolean() SqlBoolean Returns the value of the specified column as a SqlBoolean object. GetSqlByte() SqlByte Returns the value of the specified column as a SqlByte object. GetSqlDateTime() SqlDateTime Returns the value of the specified column as a SqlDateTime object. GetSqlDecimal() SqlDecimal Returns the value of the specified column as a SqlDecimal object. GetSqlDouble() SqlDouble Returns the value of the specified column as a SqlDouble object. GetSqlGuid() SqlGuid Returns the value of the specified column as a SqlGuid object. GetSqlInt16() SqlInt16 Returns the value of the specified column as a SqlInt16 object. GetSqlInt32() SqlInt32 Returns the value of the specified column as a SqlInt32 object. GetSqlInt64() SqlInt64 Returns the value of the specified column as a SqlInt64 object. GetSqlMoney() SqlMoney Returns the value of the specified column as a SqlMoney object. GetSqlSingle() SqlSingle Returns the value of the specified column as a SqlSingle object. GetSqlString() SqlString Returns the value of the specified column as a SqlString object.
SqlDataReader METHODS METHOD RETURN DESCRIPTION TYPE GetSqlValue() object Returns the value of the specified column as an object. GetSqlValues() int Copies the value of all the columns in the current row into a specified object array. The int returned by this method is the number of elements in the array. GetString() string Returns the value of the specified column as a string. GetValue() object Returns the value of the specified column as an object. GetValues() int Copies the value of all the columns in the current row into a specified object array. The int returned by this method is the number of elements in the array. IsDBNull() bool Returns a bool that indicates whether the specified column contains a null value. NextResult() bool Moves the data reader to the next row in the result set. The bool returned by this method indicates whether there are more rows in the result set. Read() bool Moves the data reader to the next row in the result set and reads the row. The bool returned by this method indicates whether there are more rows in the result set. 2.3.3 Sự kiện: Không có! 2.4 Lớp chuyển dữ liệu sang DataSet 2.4.1 Thuộc tính SqlDataAdapter PROPERTIES PROPERTY TYPE DESCRIPTION AcceptChangesDuringFill bool Gets or sets a bool that indicates whether the AcceptChanges() method is called after a DataRow object has been added, modified, or removed in a DataTable object. The default is true. ContinueUpdateOnError bool Gets or sets a bool that indicates whether to continue updating the database when an error occurs. When set to true, no exception is thrown when an error occurs during the update of a row. The update of the row is skipped and the error information is placed in the RowError property of the DataRow that
SqlDataAdapter PROPERTIES PROPERTY TYPE DESCRIPTION caused the error. The DataAdapter continues to update subsequent rows. When set to false, an exception is thrown when an error occurs. The default is false. DeleteCommand SqlCommand Gets or sets a command containing a SQL DELETE statement or stored procedure call to remove rows from the database. InsertCommand SqlCommand Gets or sets a command containing a SQL INSERT statement or stored procedure call to add rows to the database. MissingMappingAction MissingMappingAction Gets or sets the action to take when the incoming table or column doesn't have a matching table or column in the TableMappings collection. The values for this action come from the System.Data.MissingMappingAction enumeration with the members Error, Ignore, and Passthrough: Error means a SystemException is thrown. Ignore means the table or column is ignored and not read. Passthrough means the table or column is added to the DataSet with its original name. The default is Passthrough. MissingSchemaAction MissingSchemaAction Gets or sets the action to take when the incoming column doesn't have a matching column in the DataTable object's Column collection. The values for this action come from the System.Data.MissingSchemaAction enumeration with the members Add, AddWithKey, Error, and Ignore: Add means the column is added to the DataTable. AddWithKey means the column and primary key information is added to
SqlDataAdapter PROPERTIES PROPERTY TYPE DESCRIPTION the DataTable. Error means a SystemException is thrown. Ignore means the column is ignored and not read. The default is Add. SelectCommand SqlCommand Gets or sets a command containing a SQL SELECT statement or stored procedure call to retrieve rows from the database. TableMappings DataTableMappingCollection Gets a DataTableMappingCollection that contains the mapping between a database table and a DataTable object in the DataSet. UpdateCommand SqlCommand Gets or sets a command containing a SQL UPDATE statement or stored procedure call to modify rows in the database. 2.4.2 Phương thức SqlDataAdapter METHODS METHOD RETURN TYPE DESCRIPTION Fill() int Overloaded. Synchronizes the rows in the DataSet object to match those in the database. The int returned by this method is the number of rows synchronized in the DataSet with the database. FillSchema() DataTable Overloaded. Adds a DataTable to a DataSet object and DataTable[] configures the schema to match the database. GetFillParameters() IDataParameter[] Returns an array of any parameters set for the SQL SELECT statement. Update() int Overloaded. Calls the respective SQL INSERT, UPDATE, or DELETE statements or stored procedure call (stored in the InsertCommand, UpdateCommand, and DeleteCommand properties, respectively) for each row that has been added, modified, or removed from a DataTable object. The int returned by this method is the number of rows updated. 2.4.3 Sự kiện SqlDataAdapter EVENTS EVENT EVENT HANDLER DESCRIPTION FillError FillErrorEventHandler Fires when an error occurs during a fill operation.
SqlDataAdapter EVENTS EVENT EVENT HANDLER DESCRIPTION RowUpdating RowUpdatingEventHandler Fires before a row is added, modified, or deleted in the database. RowUpdated RowUpdatedEventHandler Fires after a row is added, modified, or deleted in the database. 2.5 Lớp DataSet chứa dữ liệu 2.5.1 Thuộc tính DataSet PROPERTIES PROPERTY TYPE DESCRIPTION CaseSensitive bool Gets or sets a bool value that indicates whether string comparisons within DataTable objects are case- sensitive. DataSetName string Gets or sets the name of the current DataSet object. DefaultViewManager DataViewManager Gets a custom view of the data stored in the DataSet object. You use a view to filter, search, and navigate the DataSet. EnforceConstraints bool Gets or sets a bool value that indicates whether constraint rules are followed when updating information in the DataSet object. ExtendedProperties PropertyCollection Gets a collection (PropertyCollection) of user information. You can use the PropertyCollection to store strings with any additional information you want. You use the Add() method through ExtendedProperties to add a string. HasErrors bool Gets a bool value that indicates whether there are errors in any of the rows in the tables of the DataSet object. Locale CultureInfo Gets or sets a CultureInfo object for the DataSet. A CultureInfo object contains information about a specific culture including its name, writing system, and calendar. Namespace string Gets or sets the namespace for the DataSet object. The namespace is a string that is used when reading and writing an XML document using the ReadXml(), WriteXml(), ReadXmlSchema(), and WriteXmlSchema() methods. The namespace is used to scope the XML attributes and elements. Prefix string Gets or sets the XML prefix for the DataSet namespace. The prefix is used in an XML document to identify the elements that belong to the DataSet object's
DataSet PROPERTIES PROPERTY TYPE DESCRIPTION namespace. Relations DataRelationCollection Gets the collection of relations (DataRelationCollection) that allows navigation from a parent table to a child table. A DataRelationCollection consists of DataRelation objects. Tables DataTableCollection Gets the collection of tables (DataTableCollection) that contains the DataTable objects stored in the DataSet. 2.5.2 Phương thức DataSet METHODS METHOD RETURN DESCRIPTION TYPE AcceptChanges() void Commits all the changes made to the DataSet object since it was loaded or since the last time the AcceptChanges() method was called. BeginInit() void Used by the Visual Studio .NET designer to initialize a DataSet used in a form or component. Clear() void Removes all rows from all tables in the DataSet object. Clone() DataSet Clones the structure of the DataSet object and returns that clone. The clone contains all the schemas, relations, and constraints. Copy() DataSet Copies the structure and data of the DataSet object and returns that copy. The copy contains all the schemas, relations, constraints, and data. EndInit() void Used by the Visual Studio .NET designer to end initialization of a DataSet used in a form or component. GetChanges() DataSet Overloaded. Gets a copy of all the changes made to the DataSet object since it was last loaded or since the last time the AcceptChanges() method was called. GetXml() string Returns the XML representation of the data stored in the DataSet object. GetXmlSchema() string Returns the XML representation of the schema for the DataSet object. HasChanges() bool Overloaded. Returns a bool value that indicates whether the DataSet object has changes that haven't been committed. Merge() void Overloaded. Merges this DataSet with another specified DataSet object. ReadXml() XmlReadMode Overloaded. Loads the data from an XML file into the DataSet object.
DataSet METHODS METHOD RETURN DESCRIPTION TYPE ReadXmlSchema() void Overloaded. Loads a schema from an XML file into the DataSet object. RejectChanges() void Undoes all the changes made to the DataSet object since it was created or since the last time the AcceptChanges() method was called. Reset() void Resets the DataSet object to its original state. WriteXml() void Overloaded. Writes out the data from the DataSet object to an XML file. WriteXmlSchema() void Overloaded. Writes out the schema of the DataSet object to an XML file. 2.5.3 Sự kiện DataSet EVENT EVENT EVENT HANDLER DESCRIPTION MergeFailed MergeFailedEventHandler Fires when an attempt is made add a DataRow to a DataSet when a DataRow with the same primary key value already exists in that DataSet. 3 LẬP TRÌNH CƠ BẢN VỚI ADO.NET 3.1 Kết nối 3.1.1 Kết nối dữ liệu Access SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); Hoặc: string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; SqlConnection mySqlConnection = new SqlConnection(connectionString); 3.1.2 Kết nối dữ liệu Sql Server string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source=F:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb"; OleDbConnection myOleDbConnection = new 01eDbConnection(connectionString);
3.1.3 Kết nối dữ liệu Oracle string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER"; OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); 3.2 Thi hành truy vấn hoặc Procedure 3.2.1 Các truy vấn 3.2.2 Procedure 3.2.3 Thi hành truy vấn hoặc procedure Khởi tạo đối tượng Command: SqlCommand() SqlCommand(string commandText) SqlCommand(string commandText, SqlConnection mySqlConnection) SqlCommand(string commandText, SqlConnection mySqlConnection, SqlTransaction mySqlTransaction) Xác định câu truy vấn: mySqlCommand.CommandText = "SELECT TOP 10 CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "ORDER BY CustomerID"; Hoặc: SqlCommand mySqlCommand = new SqlCommand( "SELECT TOP 5 CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "ORDER BY CustomerID", mySqlConnection); Hoặc: SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); 3.3 Đọc dữ liệu mySqlConnection.Open(); SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(); while (productsSqlDataReader.Read()) { Console.WriteLine(productsSqlDataReader[0]); Console.WriteLine(productsSqlDataReader[1]); Console.WriteLine(productsSqlDataReader[2]); Console.WriteLine(productsSqlDataReader[3]);
} int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID"); int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName"); int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice"); int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued"); 3.4 Chuyển dữ liệu sang DataSet 3.4.1 Chuyển dữ liệu từ Database sang DataSet Khởi tạo: SqlDataAdapter() SqlDataAdapter(SqlCommand mySqlCommand) SqlDataAdapter(string selectCommandString, SqlConnection mySqlConnection) SqlDataAdapter(string selectCommandString, string connectionString) Chuyển dữ liệu sang DataSet: SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); string selectCommandString = "SELECT TOP 10 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(selectCommandString, mySqlConnection); DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet, "Customers"); Update lại dữ liệu trong Database: new SqlCommandBuilder(adapter); adapter.Update(dataset); Các phương thức Fill: int Fill(DataSet myDataSet) int Fill(DataTable myDataTable) int Fill(DataSet myDataSet, string dataTableName) int Fill(DataSet myDataSet, int startRow, int numOfRows, string dataTableName)
3.4.2 Chuyển dữ liệu từ Form sang DataSet 3.5 Làm việc trên DataSet 3.5.1 DataSet 3.5.2 DataTable 3.5.3 DataRow 3.5.4 DataColumn 3.5.5 DataView 3.6 Làm việc với dữ liệu liên kết 3.7 Cập nhật dữ liệu 4 LẬP TRÌNH NÂNG CAO VỚI ADO.NET 4.1 Advanced SqlTransaction Control 4.2 ASP.NET 4.3 Làm việc với XML 4.4 Dịch vụ Web