Giáo trình Lập trình trên nền web (Phần 2)

CHƢƠNG 4: TRUY NHẬP CƠ SỞ DỮ LIỆU

4.1. Giới thiệu chung

Khi phát triển các ứng dụng trên nền Web thì công việc chủ yếu phải giải quyết là

xử lý các nghiệp vụ, trong đó phần lớn là xử lý cơ sở dữ liệu. Trong môi trƣờng phát triển

Microsoft .NET tất cả các ứng dụng Web Form hay Win Form đều thống nhất sử dụng

chung một bộ thƣ viện để truy xuất và thao tác cơ sở dữ liệu gọi là ADO.NET (Active

Data Object).

ADO.NET là một tập các lớp nằm trong bộ thƣ viện lớp cơ sở của .NET

Framework, cho phép các ứng dụng Windows (nhƣ C#, VB.NET) hay ứng dụng Web

(nhƣ ASP.NET) thao tác dễ dàng với các nguồn dữ liệu. Mục tiêu chính của ADO.NET

là:

- Cung cấp các lớp để thao tác cơ sử dữ liệu trong cả hai môi trƣờng là phi kết nối

(Disconected data) và kết nối (Connected data).

- Tích hợp chặt chẽ với XML (Extensible Markup Language).

- Tƣơng tác với nhiều nguồn dữ liệu thông qua mô tả dữ liệu chung.

- Tối ƣu truy cập nguồn dữ liệu (OLE DB & SQL Server).

- Làm việc trên môi trƣờng Internet.

ADO.NET bao gồm hai Provider (hai bộ thƣ viện) để thao tác với các cơ sở dữ liệu

là: OLE DB Provider (nằm trong System.Data.OLEDB) dùng để truy xuất đến cơ sở dữ

liệu nào có hỗ trợ OLEDB; SQL Provider (nằm trong System.Data.SQLClient) chuyên

dùng để truy xuất đến cơ sở dữ liệu SQL Server (không qua OLE DB nên nhanh hơn).

Hiện nay, các hãng thứ ba cung cấp các Provider khác nhƣ : MySQL, Oracle…

provider cho phép ứng dụng .NET truy xuất đến cơ sở dữ liệu không phải của Microsoft.

Hình 4.1. Vị trí của ADO.NET trong kiến trúc của .net Framework

Từ kiến trúc ta thấy rằng: ADO.NET là một thành phần nội tại (Instrict) của .NET

Framework. Do vậy nó có thể đƣợc sử dụng trong tất cả các ngôn ngữ hỗ trợ .NET nhƣ

C#, VB.NET… mà không có sự khác biệt.

4.2. Kiến trúc của ADO.NET

ADO.NET là một phần của .NET Framework, đƣợc xem là “bộ thƣ viện lớp” chịu

trách nhiệm xử lý dữ liệu trong ngôn ngữ MS .NET.

ADO.NET gồm hai thành phần chính cho việc truy xuất và điều khiển dữ liệu đó là

các trình cung cấp dữ liệu .NET Framework (.NET Framework Data Provider) và

DataSet. Mô hình dƣới đây minh họa mối quan hệ giữa trình cung cấp dữ liệu của .NET

Framework và DataSet.

Hình 4.2. Kiến trúc ADO.NET

Việc kết nối giữa ứng dụng và cở sở dữ liệu thông qua ADO.NET theo chế độ

Connected và Disconnected .

Connected: Cơ chế này yêu cầu phải thực hiện kết nối với Database trong khi

đang thực hiện các thao tác với dữ liệu. Các đối tƣợng của cơ chế này là:

+ Connection: Đối tƣợng quản lý đóng/mở kết nối tới Database.

+ Command: Đối tƣợng thực hiện các câu lệnh tƣơng tác truy vấn, rút trích dữ liệu từ

database sau khi đã thiết lập kết nối tới dữ liệu và trả về kết quả.

+ DataReader: Đối tƣợng xử lý đọc dữ liệu từ cơ sở dữ liệu.

+ DataAdapter: Đây là đối tƣợng rất quan trọng của ADO.NET là cầu nối của

Database và Dataset (Dataset là đối tƣợng ngắt kết nối), vì đối tƣợng “ngắt kết nối”

Dataset không thể tƣơng tác trực tiếp với Database nên cần một đối tƣợng trung gian lấy

dữ liệu từ Database đó chính là DataAdapter. DataAdpater khi thao tác với Database vẫn

phải duy trì kết nối nên nó đƣợc liệt kê vào dạng “kết nối” nhƣng bản chất phục vụ cho

việc “ngắt kết nối”.

Disconnected: Chỉ có một đối tƣợng chịu trách nhiệm ngắt kết nối đó chính là

DataSet. Nhiệm vụ của DataSet là nhận dữ liệu về từ DataAdapter và xử lý nó. DataSet có

thể đƣợc xem nhƣ một Database trong bộ nhớ gồm tất cả các bảng, quan hệ….. DataSet

có nhiều đối tƣợng đƣợc xem là “con” nhƣ: DataTable, cấp thấp hơn của DataTable có

các đối tƣợng DataRow, DataColumn, DataRelation. Ngoài ra còn có các đối tƣợng nhóm

nhƣ DataTableCollection, DataRowCollection, DataColumnCollection. Việc sử dụng

DataSet là một tiến bộ lớn của kiến trúc ADO.NET.

Hình 4.3. Cơ chế kết nối ADO.NET

4.3. Tìm hiểu trình cung cấp dữ liệu của ADO.NET

ADO.NET hỗ trợ nhiều trình cung cấp dữ liệu, mỗi trình cung cấp tƣơng tác với

một hệ quản trị cơ sở dữ liệu (DBMS) cụ thể. Lợi ích đầu tiên của phƣơng pháp này là

một trình cung cấp dữ liệu cụ thể có thể đƣợc lập trình để truy xuất đến bất cứ thuộc tính

nào của một DBMS mà đƣợc chỉ định bởi trình cung cấp đó. Lợi ích khác đó là trình cung

cấp dữ liệu có thể kết nối ngay lập tức đến cơ sử dữ liệu mà không thông qua tầng ánh xạ

trung gian giữa các lớp.

Hình 4.4. Các lớp giữa code và nguồn dữ liệu

Một trình cung cấp dữ liệu là tập hợp các kiểu đƣợc định nghĩa trong namespace

System.Data, cho biết cách để giao tiếp với một nguồn dữ liệu cụ thể. Một trình cung cấp

định nghĩa một tập hợp các kiểu lớp mà cung cấp các chức năng cơ bản.

Các đối tƣợng cơ bản của một trình cung cấp dữ liệu ADO.NET.

Đối tƣợng

Connection DbConnection IDbConnection

Command DbCommand IDbCommand

Lớp cơ sở

Giao diện

Ý nghĩa

Cung cấp khả năng kết nối và

ngắt kết nối với nguồn dữ liệu.

Trình bày một truy vấn SQL

hoặc stored procedure.

DataReader DbDataReader IDataReader

IDataRecord

Đọc nguồn dữ liệu theo một

chiều.

DataAdapter DbDataAdapter IDataAdapter

IDbDataAdapter

Chuyển DataSets giữa các đối

tƣợng gọi (caller) và kho dữ liệu.

Miêu tả tên gọi tham số bên

Parameter

DbParameter

IDataParameter

IDbDataParameter trong truy vấn có tham số

(parameterized query).

Transaction DbTransaction IDbTransaction

Đóng gói (Encapsulates) một

database transaction.

Mặc dù tên của các đối tƣợng này sẽ khác nhau tùy thuộc trình cung cấp dữ liệu (ví

dụ: SqlConnection khác với OracleConnection khác với OdbcConnection khác với

MySqlConnection) nhƣng mỗi đối tƣợng đều kế thừa từ một lớp cơ sở (base class)

(DbConnection trong trƣờng hợp các đối tƣợng kết nối) và thi hành interface (nhƣ

IDbConnection).

Các trình cung cấp dữ liệu ADO.NET đƣợc cung cấp bởi Microsoft.

Data Provider

OLE DB

Namespace

Assembly

System.Data.dll

System.Data.OleDb

Microsoft SQL Server System.Data.SqlClient

Microsoft SQL Server System.Data.SqlServerCe

Mobile

System.Data.dll

System.Data.SqlServerCe.dll

ODBC

Oracle

System.Data.Odbc

System.Data.dll

System.Data.OracleClient

System.Data.OracleClient.dll

Trình cung cấp dữ liệu OLE DB và ODBC chỉ hữu ích nếu tƣơng tác với một

DBMS mà không định nghĩa một trình cung cấp dữ liệu .NET cụ thể.

4.4. Các namespace của ADO.NET

.NET cung cấp một số namespace cho ADO.NET, một vài namespace trong đó

đƣợc thể hiện trong bảng sau:

Namespace

Ý nghĩa

Microsoft.SqlServer.Server Namespace này cung cấp các loại phục vụ việc tích hợp

CLR và SQL Server 2005 đƣợc dễ dàng.

System.Data

Namespace này định nghĩa các loại ADO.NET cơ sở

đƣợc sử dụng bởi tất cả trình cung cấp dữ liệu, bao gồm

các Interface phổ biến và các loại thể hiện lớp ngắt kết

nối. (DataSet, DataTable, etc.).

System.Data.Common

System.Data.Sql

Namespace này chứa các loại đƣợc dùng chung trong tất

cả các trình cung cấp của ADO.NET, bao gồm các lớp cơ

sở ảo (abstract base classe) phổ biến.

Namespace này chứa các loại mà cho phép nhận ra các

thể hiện Microsoft SQL Server đƣợc cài đặt trên mạng

cục bộ hiện hành.

System.Data.SqlTypes

Namespace này chứa các loại dữ liệu tự nhiên (native

data types) đƣợc dùng bởi Microsoft SQL Server.

Namespace System.Data: Namespace này chứa các loại đƣợc dùng chung trong

tất cả các trình cung cấp dữ liệu của ADO.NET.

Các thành viên chính của System.Data Namespace.

Namespace

Constraint

Ý nghĩa

Trình bày (Represent) một ràng buộc cho một đối tƣợng

DataColumn đã cho.

DataColumn

DataRelation

Trình bày một column bên trong một đối tƣợng

DataTable.

Trình bày mối quan hệ parent/child giữa hai đối tƣợng

DataTable.

DataRow

DataSet

Trình bày một dòng bên trong một đối tƣợng DataTable.

Trình bày một vùng trong bộ nhớ của dữ liệu bao gồm

mọi thành phần của đối tƣợng DataTable có quan hệ với

nhau.

DataTable

Trình bày bảng trong bộ nhớ

DataTableReader

DataView

Cho phép xem xét một DataTable.

Trình bày một view của một DataTable cho việc sắp xếp,

việc lọc, việc tìm kiếm, chỉnh sửa và điều hƣớng.

Định nghĩa cách hành xử chính (core behavior) của một

đối tƣợng DataAdapter.

IDataAdapter

IDataParameter

IDataReader

Định nghĩa cách hành xử chính (core behavior) của một

đối tƣợng parameter.

Định nghĩa cách hành xử chính (core behavior) của một

đối tƣợng DataReader.

IDbCommand

IDbDataAdapter

IDbTransaction

Định nghĩa cách hành xử chính (core behavior) của một

đối tƣợng command.

Kế thừa IDataAdapter để cung cấp thêm các chức năng

của một đối tƣợng DataAdapter

Định nghĩa cách hành xử cốt lõi (core behavior) của một

đối tƣợng Transaction.

IDbConnection Interface: Đƣợc thi hành bởi đối tƣợng connection của trình cung

cấp dữ liệu. Interface này định nghĩa một tập hợp các thành phần đƣợc dùng để tạo một

kết nối đến kho dữ liệu và nó cũng cho phép lấy đƣợc đối tƣợng Transaction của trình

cung cấp dữ liệu. Dƣới đây định nghĩa của IDbConnection.

public interface IDbConnection : IDisposable

{

string ConnectionString { get; set; }

int ConnectionTimeout { get; }

string Database { get; }

ConnectionState State { get; }

IDbTransaction BeginTransaction();

IDbTransaction BeginTransaction(IsolationLevel il);

void ChangeDatabase(string databaseName);

void Close();

IDbCommand CreateCommand();

void Open();

}

Phƣơng thức Close() là tƣơng đƣơng về mặt chức năng với việc gọi phƣơng thức

Dispose() trực tiếp hoặc gián tiếp bên trong phạm vi using của C#.

IDbTransaction Interface: Việc nạp chồng phƣơng thức BeginTransaction()

(overloaded BeginTransaction() method) đƣợc định nghĩa bởi IDbConnection cung cấp

truy xuất đến đối tƣợng Transaction của trình cung cấp. Việc sử dụng các thành phần

đƣợc định nghĩa bởi IDbTransaction, có thể tƣơng tác với một phiên chuyển giao

(transactionnal session) và kho dữ liệu ở dƣới.

public interface IDbTransaction : IDisposable

{

IDbConnection Connection { get; }

IsolationLevel IsolationLevel { get; }

void Commit();

void Rollback();

}

IDbCommand Interface: IDbCommand Interface đƣợc thi hành bởi đối tƣợng

command của trình cung cấp dữ liệu. Đối tƣợng command cho phép thao tác với các câu

lệnh SQL, Stored Procedures và các truy vấn có tham số (parameterized queries). Trong

phần bổ sung, các đối tƣợng command truy xuất đến DataReader của trình cung cấp dữ

liệu thông qua việc nạp chồng phƣơng thức ExecuteReader() (overloaded

ExecuteReader() method).

public interface IDbCommand : IDisposable

{

string CommandText { get; set; }

int CommandTimeout { get; set; }

CommandType CommandType { get; set; }

IDbConnection Connection { get; set; }

IDataParameterCollection Parameters { get; }

IDbTransaction Transaction { get; set; }

UpdateRowSource UpdatedRowSource { get; set; }

void Cancel();

IDbDataParameter CreateParameter();

int ExecuteNonQuery();

IDataReader ExecuteReader();

IDataReader ExecuteReader(CommandBehavior behavior);

object ExecuteScalar();

void Prepare();

}

Interface IDbDataParameter và IDataParameter: Interface này cung cấp truy

xuất đến một tập hợp các loại lớp tùy ý (compliant) IDbDataParameter (ví dụ: các đối

tƣợng tham số):

public interface IDbDataParameter : IDataParameter

{

byte Precision { get; set; }

byte Scale { get; set; }

int Size { get; set; }

}

IDbDataParameter kế thừa (extend) interface IDataParameter để có đƣợc các hành

xử (behaveors) bổ sung sau đây:

public interface IDataParameter

{

DbType DbType { get; set; }

ParameterDirection Direction { get; set; }

bool IsNullable { get; }

string ParameterName { get; set; }

string SourceColumn { get; set; }

DataRowVersion SourceVersion { get; set; }

object Value { get; set; }

}

Chức năng của các Interface IDbDataParameter và IDataParameter cho phép trình

bày các tham số bên trong một lệnh SQL (bao gồm Stored Procedures) qua các đối tƣợng

parameter của ADO.NET cụ thể hơn là những kí tự chuỗi hard-coded.

IDbDataAdapter and IDataAdapter Interfaces: IDbDataAdapter Interface định

nghĩa một tập hợp các thuộc tính mà đƣợc dùng để duy trì các câu lệnh SQL cho các hoạt

động có liên quan đến select, insert, update và delete.

public interface IDbDataAdapter : IDataAdapter

{

IDbCommand DeleteCommand { get; set; }

IDbCommand InsertCommand { get; set; }

IDbCommand SelectCommand { get; set; }

IDbCommand UpdateCommand { get; set; }

}

Có bốn thuộc tính đƣợc thêm vào DataAdapter cũng đƣợc định nghĩa trong

Interface cơ sở, IDataAdapter. Interface này định nghĩa chức năng chính của một loại

DataAdapter: khả năng chuyển các DataSet giữa đối tƣợng gọi và kho dữ liệu bên dƣới sử

dụng các phƣơng thức Fill() và Update(). IDataAdapter Interface cho phép ánh xạ các tên

cột trong bảng dữ liệu thành các tên hiển thị thân thiện với ngƣời dùng hơn thông qua

thuộc tính TableMappings.

public interface IDataAdapter

{

MissingMappingAction MissingMappingAction { get; set; }

MissingSchemaAction MissingSchemaAction { get; set; }

ITableMappingCollection TableMappings { get; }

int Fill(System.Data.DataSet dataSet);

DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType);

IDataParameter[] GetFillParameters();

int Update(DataSet dataSet);

}

IDataReader and IdataRecord: Interface IDataReader trình bày các hành sử

(behaviors) phổ biến đƣợc hỗ trợ bởi một đối tƣợng DataReader cho trƣớc.

public interface IDataReader : IDisposable, IDataRecord

{

int Depth { get; }

bool IsClosed { get; }

int RecordsAffected { get; }

void Close();

DataTable GetSchemaTable();

bool NextResult();

bool Read();

}

IDataReader kế thừa IDataRecord, định nghĩa một số thành phần cho phép trích

một giá trị đƣợc phân loại một cách rõ ràng. Dƣới đây là danh sách không đầy đủ của các

phƣơng thức GetXXX() khác nhau đƣợc định nghĩa bởi IDataRecord.

public interface IDataRecord

{

int FieldCount { get; }

object this[ string name ] { get; }

object this[ int i ] { get; }

bool GetBoolean(int i);

byte GetByte(int i);

char GetChar(int i);

DateTime GetDateTime(int i);

Decimal GetDecimal(int i);

float GetFloat(int i);

short GetInt16(int i);

int GetInt32(int i);

long GetInt64(int i);

...

bool IsDBNull(int i);

}

Phƣơng thức IDataReader.IsDBNull() thiết lập giá trị cho một trƣờng là null.

4.5. Các lớp thao tác với cơ sở dữ liệu

4.5.1. Lớp Connection

Để kết nối với cơ sở dữ liệu và đọc các mẫu tin sử dụng đối tƣợng DataReader,

thực hiện các bƣớc sau:

- Chỉ định, cấu hình và mở đối tƣợng kết nối.

- Chỉ định và cấu hình đối tƣợng Command, chỉ rõ đối tƣợng Connection nhƣ một

Constructor có đối số hoặc qua thuộc tính Connection.

- Gọi ExecuteReader() trên đối tƣợng Command đã đƣợc cấu hình.

- Xử lý mỗi mẫu tin sử dụng phƣơng thức Read() của DataReader.

Hình 4.5. Kết nối đến cơ sở dữ liệu và đọc dữ liệu

Connection là đối tƣợng có nhiệm vụ thực hiện kết nối đến cơ sở dữ liệu để các đối

tƣợng nhƣ Command thao tác với cơ sở dữ liệu thông qua Connection này.

Các dạng khai báo chuỗi kết nối tuỳ theo loại Database Provider.

[OleDB Provider]

OleDbConnection myConnection = new OleDbConnection();

// Windows Authentication

myConnection.ConnectionString = "Provider=SQLOLEDB.1; Data Source = localhost; "

+"Initial Catalog=Pubs;Integrated Security=SSPI";

// SQL Authentication

myConnection.ConnectionString = "Provider=SQLOLEDB.1; Data Source = localhost;"

+ "Initial Catalog=Pubs;User ID=sa;Password=123456";

[SQLServer Provider]

SqlConnection myConnection = new SqlConnection();

myConnection.ConnectionString = "Data Source=localhost;" + "Initial Catalog =

Pubs;Integrated Security=SSPI";

[AttachDBFile] (tập tin cơ sở dữ liệu)

myConnection.ConnectionString = @"Data Source=localhost\SQLEXPRESS;"

@"AttachDBFilename=|DataDirectory|\Pubs.mdf;Integrated Security=True";

+

Khi làm việc với một trình cung cấp dữ liệu là thiết lập một session với nguồn dữ

liệu sử dụng đối tƣợng connection (kế thừa từ DbConnection). Các đối tƣợng Connection

của .NET đƣợc cung cấp với một chuỗi kết nối đƣợc định dạng, gồm một số cặp

name/value đƣợc phân cách bởi dấu chấm phẩy (;). Thông tin này đƣợc dùng để chỉ định

tên máy sẽ kết nối, các thiết lập bảo mật đƣợc yêu cầu, tên của cơ sở dữ liệu trên máy đó

và thông tin trình cung cấp dữ liệu cụ thể khác.

Tên Initial Catalog xác định tên cơ sở dữ liệu. Data Source xác định tên máy chứa

cơ sở dữ liệu. Ở đây, (local) cho phép định nghĩa một Token riêng biệt (single) để chỉ rõ

máy cục bộ hiện hành, khi token \SQLEXPRESS cho trình cung cấp dữ liệu SQL biết

đang kết nối đến bản cài đặt SQL Server Express edition (nếu đã có Pubs trên SQL Server

2005/2008 hoặc các phiên bản trƣớc đây chỉ rõ Data Source=(local)\SQLEXPRESS).

Integrated Security thiết lập thông tin bảo mật, nếu chọn Integrated Security là

SSPI (tƣơng đƣơng true), để sử dụng tài khoản Windows hiện hành để xác nhận quyền

ngƣời dùng.

User ID và Password thiết lập thông tin bảo mật, nếu chọn để sử dụng tài khoản

SQL Server hiện hành để xác nhận quyền ngƣời dùng.

Sau khi chuỗi kết nối đƣợc thiết lập, gọi Open() mở kết nối với cơ sở dữ liệu.

Ngoài việc bổ sung các thành phần ConnectionString, Open() và Close() một đối tƣợng

kết nối cung cấp một số thành phần mà cho phép thêm vào các thiết lập thuộc về cấu hình

kết nối nhƣ là các thiết lập Timeout thông tin thuộc về giao tác (transactional).

Các thành phần của DbConnection.

Thành phần

Ý nghĩa

BeginTransaction() Phƣơng thức này đƣợc sử dụng để bắt đầu một database

Transaction.

ChangeDatabase() Phƣơng thức này thay đổi cơ sở dữ liệu trên một kết nối mở

(open connection)

Close()

Phƣơng thức này dùng để đóng kết nối

ConnectionString

Thuộc tính này chứa các thông tin để kết nối.

ConnectionTimeout Thuộc tính này trả ra một khoảng thời gian chờ trong khi thiết

lập một kết nối trƣớc khi kết thúc và tạo ra một lỗi (mặc định là

15 giây).

Database

Thuộc tính này lấy ra tên của cơ sở dữ liệu đƣợc duy trì bởi đối

tƣợng connection.

DataSource

GetSchema()

Thuộc tính này lấy vị trí của cơ sở dữ liệu đƣợc duy trì bởi đối

tƣợng connection.

Phƣơng thức này trả ra một DataSet mà chứa thông tin bảng từ

nguồn dữ liệu.

Open()

State

Phƣơng thức này dùng để mở kết nối

Thuộc tính này thiết lập trạng thái hiện hành của việc kết nối,

đƣợc thể hiện bởi việc liệt kê ConnectionState.

Ví dụ: Thực hiện việc tạo kết nối đến cơ sở dữ liệu Pubs trong MS SQL Server sử

dụng đối tƣợng SqlConnection với chuỗi kết nối có dạng sau:

Xác thực với Windows Authenticate

string connectionString = "Data Source=localhost\\SQLEXPRESS;Initial

Catalog=Pubs; Integrated Security=SSPI";

Xác thực với SQL Authenticate

string connectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=Pubs;

User ID=sa;Password=123456";

string connectionString = “Server=localhost; Database= Pubs; Uid=sa; Pwd=123456”;

Tạo đối tƣợng Connection

SqlConnection myConnection = new SqlConnection(connectionString);

Mở kết nối đến cơ sở dữ liệu

myConnection.Open();

Đóng kết nối đến cơ sở dữ liệu

myConnection.Close();

Nội dung thiết kế trang ConnectionTester.aspx

<div>

<asp:RadioButton id="optSQL" runat="Server" Text="Use SQL Authentication

(with sa account)" GroupName="Authentication" Font-Size="Smaller" Font-

Names="Verdana"></asp:RadioButton> <br />

<asp:RadioButton id="optWindows" runat="Server" Text="Use Windows

Integrated Authentication" GroupName="Authentication" Font-Size="Smaller"

Font-Names="Verdana" Checked="True"> </asp:RadioButton> <br /> <br />

<asp:button

id="cmdConnect"

runat="Server"

Text="Connect"

onclick="cmdConnect_Click"></asp:button><br /> <br />

<asp:label id="lblInfo" runat="Server" Height="128px" Width="464px" Font-

Size="Small" Font-Names="Verdana" ForeColor="Maroon"></asp:label>

</div>

</form>

Nội dung trang ConnectionTester.aspx.cs

public partial class ConnectionTester : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void cmdConnect_Click(object sender, EventArgs e)

{

string connectionString = "Data Source=localhost\\SQLEXPRESS; Initial

Catalog=Pubs;";

if (optWindows.Checked)

{

connectionString += "Integrated Security=SSPI";

}

else

{

connectionString += "User ID=sa;Password=123456";

}

SqlConnection myConnection = new SqlConnection(connectionString);

try

{

myConnection.Open();

lblInfo.Text = "<b>Server Version:</b> " + myConnection.ServerVersion;

lblInfo.Text

myConnection.State.ToString(); }

catch (Exception err)

+=

"<br

/><b>Connection

Is:</b>

"

+

{

lblInfo.Text = "Error reading the database. ";

lblInfo.Text += err.Message;

}

finally

{

myConnection.Close();

lblInfo.Text += "<br /><b>Now Connection Is:</b> ";

lblInfo.Text += myConnection.State.ToString();

}

Hình 4.6 Kết quả thực thi trƣờng hợp kết nối cơ sở dữ liệu bị lỗi và thành công

Một cách khác, thực hiện việc khai báo cấu hình ConnectionString trong

Web.config : không cần phải hard-code ở các trang mã lệnh.

<add name="Northwind"

connectionString="Data

Source=

(local)\SQLEXPRESS; Initial Catalog=Northwind;Integrated Security=SSPI"/>

</connectionStrings>

</configuration>

Lấy nội dung ConnectionString trong thẻ <connectionStrings> trong tập tin

Web.config phải khai báo thêm : using System.Web.Configuration;

private string connectionString= WebConfigurationManager.ConnectionStrings["Pubs"].

ConnectionString;

4.5.2. Lớp Command

Sau khi kết nối với cơ sở dữ liệu, sau đó submit các truy vấn SQL đến cơ sở dữ

liệu. Kiểu SqlCommand (kế thừa từ DbCommand). Kiểu của Command theo lý thuyết sử

dụng thuộc tính CommandType, có thể lấy ra bất cứ giá trị nào từ CommandType enum:

public enum CommandType

{

StoredProcedure,

TableDirect,

Text // Default value.

}

Khi tạo một đối tƣợng Command cũng có thể kết hợp truy vấn SQL nhƣ một

Constructor có tham số hoặc trực tiếp thông qua thuộc tính CommandText. Ngoài ra khi

đang tạo một đối tƣợng command, cần chỉ rõ kết nối đƣợc sử dụng. Mặt khác, có thể tạo

một Constructor có đối số hoặc thông qua thuộc tính Connection. Ví dụ

SqlConnection cn = new SqlConnection();

string strSQL = "Select * From Customers";

SqlCommand myCommand = new SqlCommand(strSQL, cn);

SqlCommand testCommand = new SqlCommand();

testCommand.Connection = cn;

testCommand.CommandText = strSQL;

Các thành phần của DbCommand

Thành phần

Ý nghĩa

CommandTimeout

Lấy ra hoặc thiết lập thời gian chờ trong khi đang thực thi lệnh

trƣớc khi kết thúc.

Connection

Lấy ra hoặc thiết lập DbConnection đƣợc dùng bởi thể hiện này

của DbCommand.

Parameters

Lấy ra tập hợp các kiểu của DbParameter đƣợc dùng cho một

truy vấn có tham số.

Cancel()

Hủy bỏ việc thực thi của một lệnh.

ExecuteReader()

Thực hiện câu lệnh trong CommandText. Kết quả trả về là

SqlDataReader của trình cung cấp dữ liệu.

ExecuteNonQuery() Thực hiện câu lệnh trong CommandText và không có kết quả

trả về.

ExecuteScalar()

Thực hiện câu lệnh trong CommandText, kết quả trả về là một

giá trị đơn.

ExecuteXmlReader() Phƣơng thức này trả ra một System.Xml.XmlReader mà cho

phép xử lý luồng đầu vào của XML

Prepare()

Tạo một phiên bản đƣợc chuẩn bị (hoặc đƣợc biên dịch) của

lệnh trên nguồn dữ liệu.

Ví dụ: Tính tổng số bản ghi của bảng Orders trong cơ sở dữ liệu nwind.mdb

SqlConnection cnn = new SqlConnection();

SqlCommand cmd = new SqlCommand();

cnn.ConnectionString = “Server=localhost; database=Northwind; user id=sa;

password=sa”;

cmd.Connection = cnn;

cmd.CommandText = “SELECT COUNT(*) FROM Orders”;

cmd.CommandType = CommandType.Text;

cnn.Open();

int count = (int)cmd.ExecuteScalar();

cnn.Close();

Ví dụ: Thêm một bản ghi vào bảng dtb trong cơ sở dữ liệu QLHS

SqlConnection cnn = new SqlConnection();

SqlCommand cmd = new SqlCommand();

cnn.ConnectionString = “Server=localhost; database=QLHS; user id=sa;

password=sa”;

cmd.Connection = cnn;

cmd.CommandText =“INSERT INTO HocSinh(id_hocsinh, tenhocsinh, dtb)” +

“VALUES(5, „Nguyễn Văn A‟, 8.5)”;

cmd.CommandType = CommandType.Text;

cnn.Open();

cmd.ExecuteNonQuery();

cnn.Close();

Đối tƣợng Command và các truy vấn có tham số.

Các truy vấn có tham số thực thi nhanh hơn một chuỗi SQL bình thƣờng, vì chúng

đƣợc phân tách một cách rõ ràng (chuỗi SQL đƣợc gắn đến thuộc tính CommandText).

Để hỗ trợ các truy vấn có tham số đối tƣợng Command của ADO.NET duy trì một

tập hợp của các đối tƣợng tham số riêng biệt. Mặc định, tập hợp này empty, nhƣng dễ

dàng để thêm bất cứ giá trị nào của đối tƣợng tham số mà ánh xạ đến một “tham số thay

thế” trong truy vấn SQL. Khi muốn kết hợp một tham số bên trong một truy vấn SQL đến

một thành viên trong tập hợp các tham số của đối tƣợng command, thêm tiền tố @ trƣớc

tham số SQL.

Để xây dựng một truy vấn có tham số, chúng ra bắt đầu với DbParameter (là lớp cở

sở cho đối tƣợng tham số cụ thể của một trình cung cấp). Lớp này chứa một số thuộc tính

mà cho phép cấu hình tên, kích cỡ và kiểu dữ liệu của tham số, cũng nhƣ các đặc điểm

khác nhƣ là hƣớng của tham số.

Các thành phần chính của DbParameter

Thuộc tính

DbType

Ý nghĩa

Lấy ra hoặc thiết lập kiểu dữ liệu tự nhiên từ nguồn dữ liệu,

đƣợc trình bày nhƣ kiểu dữ liệu CLR.

Direction

Lấy ra hoặc thiết lập tham số chỉ nhập, chỉ xuất, cả hai, hoặc trả

ra tham số giá trị.

IsNullable

ParameterName

Size

Lấy ra hoặc thiết lập tham số chấp nhận các gía trị Null.

Lấy ra hoặc thiết lập tên của DbParameter.

Lấy ra hoặc thiết lập kích cỡ tham số tối thiểu của dữ liệu (chỉ

hữu ích cho dữ liệu là văn bản).

Value

Lấy ra hoặc thiết lập giá trị cho tham số.

Minh họa tập hợp của các đối tƣợng Command của các đối tƣợng DBParameter

quan sát đoạn code sau :

string insertSQL;

insertSQL = "INSERT INTO Authors (";

insertSQL += "au_id, au_fname, au_lname, ";

insertSQL += "phone, address, city, state, zip, contract) ";

insertSQL += "VALUES (";

insertSQL += "@au_id, @au_fname, @au_lname, ";

insertSQL += "@phone, @address, @city, @state, @zip, @contract)";

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(insertSQL, con);

Đƣa các Parameters với các giá trị lấy từ các điều khiển nhập vào trong đối tƣợng

Command hiện tại cmd

cmd.Parameters.AddWithValue("@au_id", txtID.Text);

cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);

cmd.Parameters.AddWithValue("@au_Lname", txtLastName.Text);

cmd.Parameters.AddWithValue("@phone", txtPhone.Text);

cmd.Parameters.AddWithValue("@address", txtAddress.Text);

cmd.Parameters.AddWithValue("@city", txtCity.Text);

cmd.Parameters.AddWithValue("@state", txtState.Text);

cmd.Parameters.AddWithValue("@zip", txtZip.Text);

cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));

Hoặc khởi tạo đối tƣợng SqlParameter và đặt vào trong phần danh sách tham số

của đối tƣợng

cmd.Parameters.Add(new SqlParameter("@au_id", txtID.Text));

cmd.Parameters.Add(new SqlParameter("@au_fname", txtFirstName.Text));

cmd.Parameters.Add(new SqlParameter("@au_Lname", txtLastName.Text));

cmd.Parameters.Add(new SqlParameter("@phone", txtPhone.Text));

cmd.Parameters.Add(new SqlParameter("@address", txtAddress.Text));

cmd.Parameters.Add(new SqlParameter("@city", txtCity.Text));

cmd.Parameters.Add(new SqlParameter("@state", txtState.Text));

cmd.Parameters.Add(new SqlParameter("@zip", txtZip.Text));

cmd.Parameters.Add(new

SqlParameter("@contract",Convert.ToInt16(chkContract.Checked)));

Ví dụ: Tạo trang AuthorManager.aspx thiết vế với giao diện sau :

Hình 4.7. Giao diện trang AuthorManager.aspx

Trang AuthorManager.aspx.cs

public partial class AuthorManager : System.Web.UI.Page

{

private string connectionString =

WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

FillAuthorList();

}

private void FillAuthorList()

{

lstAuthor.Items.Clear();

string selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(selectSQL, con);

SqlDataReader reader;

try

{

con.Open();

while (reader.Read())

{

ListItem newItem = new ListItem();

newItem.Text = reader["au_lname"] + ", " + reader["au_fname"];

newItem.Value = reader["au_id"].ToString();

lstAuthor.Items.Add(newItem);

}

reader.Close();

}

catch (Exception err)

{

lblResults.Text = "Error reading list of names. ";

lblResults.Text += err.Message;

}

finally

{

con.Close();

}

protected void lstAuthor_SelectedIndexChanged(object sender, EventArgs e)

{

string selectSQL;

selectSQL = "SELECT * FROM Authors ";

selectSQL += "WHERE au_id='" + lstAuthor.SelectedItem.Value + "'";

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(selectSQL, con);

SqlDataReader reader;

try

{

con.Open();

reader = cmd.ExecuteReader();

reader.Read();

txtID.Text = reader["au_id"].ToString();

txtFirstName.Text = reader["au_fname"].ToString();

txtLastName.Text = reader["au_lname"].ToString();

txtPhone.Text = reader["phone"].ToString();

txtAddress.Text = reader["address"].ToString();

txtCity.Text = reader["city"].ToString();

txtState.Text = reader["state"].ToString();

txtZip.Text = reader["zip"].ToString();

chkContract.Checked = (bool)reader["contract"];

reader.Close();

lblResults.Text = "";

}

catch (Exception err)

{

lblResults.Text = "Error getting author. ";

lblResults.Text += err.Message;

}

finally

{

con.Close();

}