SqlDataReader & Dataset

Các bài viết trong: Quan hệ C# và Database
Phần 1: Kết nối C# với Database
Phần 2: Sql Command
Phần 3: SqlDataReader & Dataset
Phần 4: Stored Procedure
Phần 5: Function và Trigger

Trong bài tiếp theo này sẽ tập trung trình bày về DataReaders và Dataset, ưu và khuyết điềm của chúng.

1.Data Readers

Khi bạn kết nối tới cơ sở dữ liệu và thi hành các query, dữ liệu thu nhận được cần phải có một ai đó xử lý, chứ không lẽ kết nối đến SQL thêm, xóa, sửa.. xong rồi lại không biết được kết quả đúng hay sai, có thực hiện được hay không, ít ra chúng ta phải xuất kết quả ra để theo dõi đúng không. Như vậy cần phải có một đối tượng thu nhận các thông tin, đó chính là DataReaders

Trong bài trước mình có trình bày 1 ví dụ về có liên quan đến DataReaders nội dung ví dụ như sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Database : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strHTML = "";
        strHTML+="<table border=\"0\" width=\"550\" cellspacing=\"1\" cellpadding=\"0\" bgcolor=\"#999966\" id=\"table2\">";
        strHTML+="  <tr>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CustomerID</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">CompanyName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">ContactName</font></td>";
        strHTML += "        <td bgcolor=\"#CC3300\" align=\"center\"><font color=\"#FFFFFF\">Country</font></td>";
        strHTML+="  </tr>";
        // Khai báo chuỗi kết nối
        string connectString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Khai báo câu truy vấn
        string sql = @"SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo một connection tới máy chủ
        SqlConnection conn = new SqlConnection(connectString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                strHTML += "    <tr>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[0]+ "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[1] + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[2] + "</td>";
                strHTML += "        <td bgcolor=\"#FFFFFF\">" + reader[3] + "</td>";
                strHTML += "    </tr>";
            }
            reader.Close();//Đóng SqlDataReader
        }

        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            conn.Close();
        }
        strHTML += "</table>";
        Literal1.Text = strHTML;
    }
}

Kết quả thu được là các thông tin của trong câu lệnh

SELECT TOP 10 CustomerID, CompanyName, ContactName, Country FROM Customers

 Với câu lệnh truy vấn trên nó sẽ trả về 10 bản ghi trong bảng Customers bạn có thể thấy muốn lấy dữ liệu là cột  CustomerID bạn dùng reader[0];  0 ở đây là vị trí Index theo thứ tự (Index được bắt đầu từ 0) trong thứ tự các cột của câu truy vấn, Trong ví dụ của bài trước bạn thấy muốn lấy dữ liệu của cột CustomerID  Mình dùng reader[0].GetValue(0) Ngoài cách dùng index để hiển thị dữ liệu bạn cũng có thể dùng trực tiếp tên cột như sau: reader["CustomerID"]. Tới đây chắc các bạn cũng đã nắm được vị trí index trong reader rồi nhỉ. Bạn sẽ thấy rằng hai cách hiển thị sẽ cho kết qua như nhau nhưng theo mình bạn nên dùng trực tiếp tên cột vì đôi khi trong lập trình bạn thay đổi thứ tự hiển thị trong câu truy vấn SQL.

 Lưu ý:
- Thông thường data reader mà phải xử lý thông qua phương thức ‘ExecuteReader’ của một đối tượng command.
- Một DataReader có thể gọi là một stream đã kết nối tới cơ sở dữ liệu đọc dữ liệu hiệu quả, theo một chiều và thu nhận dữ liệu theo từng dòng (row). Quy tắc chung khi sử dụng data reader dơn giản là chỉ thu nhận và trình bày kết quả thu được. Đây là điểm khác biệt lớn nhất giữa dataReader và DataSet.

2.DataSet và DataAdapter

2.1.Dataset
Trong lúc làm việc ta không thể nào kết nối liên tục đến DataSource việc làm này rất tốn tài nguyên của máy, chính vì lẽ đó mà .Net cung cấp cho bạn DataSet mục đích là lưu trữ dữ liệu và chỉnh sửa cục bộ. Tức là toàn bộ thông tin từ DataSource vẫn còn được lưu trữ trên DataSet khi ta đã ngắt kết nối. Bạn hoàn toàn có thể thao tác trên DataSet như xem, chỉnh sửa sau đó update dữ liệu lại cho DataSource. Nếu khi bạn không update thì việc thao tác trên Dataset sẽ không ảnh hưởng gì đến DataSource cả!
DataSet được lưu trữ dưới dạng tập hợp các Tables và bạn cần xử lý thông qua các lớp của Tables là DataRow, DataColumn.
Có nhiều cách xử lý DataSet nhưng nổi bậc hơn hẳn là 2 cách sử dụng:
- Sử dụng DataAdapter
- Sử dụng XML (cái này sẽ được trình bày trong bài viết sau)

2.2. DataAdapter:
DataAdapter được hiểu nôm na, như là cầu nối và  truyền tải dữ liệu giữa dataSet và dataSource. Mối quan hệ này được biểu diễn như sau: DataSource ~ DataAdapter ~ DataSet

Bạn xem ví dụ sau:
Trong trang aspx bạn cần tạo 1 gridview để hiển thị dữ liệu:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataSetDemo.aspx.cs" Inherits="DataSetDemo" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>hmweb.com.vn</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC"
            BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <RowStyle ForeColor="#000066" />
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Trong CodeFile (Code behind) bạn viết như sau:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class DataSetDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtb = new DataTable();
        dtb = DemoDataSet();
        GridView1.DataSource = dtb;
        GridView1.DataBind();
    }
    private DataTable DemoDataSet()
    {
        DataTable dtbTmp = new DataTable();
        // Tạo connection string
        string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
        // Tạo SQL query
        string sql = @"SELECT TOP 8 CustomerID, CompanyName, ContactName, Country FROM Customers ";
        // Tạo connection
        SqlConnection conn = new SqlConnection(connString);
        try
        {
            // Mở kết nối
            conn.Open();
            // Tạo một Adapter
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            // Tạo DataSet
            DataSet ds = new DataSet();
            // Đổ dữ liệu DataSet
            da.Fill(ds, "Customers");
            // Tạo DataTable từ dataSet
            dtbTmp = ds.Tables[0];
        }
        catch (Exception e)
        {
            // Bắt lỗi
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Đóng kết nối
            conn.Close();
        }
        return dtbTmp;
    }
}

Trong ví dụ trên mình dùng chỉ số index (ds.Tables[0]) để bạn hiểu rằng DataSet là một tập hợp nhiều DataTable. Khi bạn dùng da.Fill(ds, "Customers") Như vậy ngoài cách sử dụng index bạn cũng có thể sử dụng tên đã khai báo như sau: dtbTmp = ds.Tables["Customers"];

Chạy chương trình xong ta thấy, kết quả xuất ra như sau:  (Bạn có thể nhấn vào dưới đây để down mã nguồn ví dụ trên để tham khảo)

 

DataSetDemo.rar (1,93 kb)

Như vậy kết quả không khác mấy khi dùng SqlDataReader, nên nhớ rằng khi bạn thực hiện với mục đích xuất thông tin ra thôi thì nên dùng SqlDataReader để thay thế cho DataSet, vì sẽ tiết kiệm được thời gian và tài nguyên. Ví dụ trên nhằm mình họa một trong những tính năng của DataSet. Như vậy DataSet có điểm gì nổi bậc hơn SqlDataReader, điểm nổi bậc ở đây chính là khả năng tùy biến cao, có thể chỉnh sửa CSDL và lưu lại vào DataSource.
Bạn hãy xem ví dụ sau:

private void DataSetInsertAndView()
{
    // Tạo connection strin
    string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
    // Tạo SQL query        
    string ins = @"INSERT INTO employees (firstname, lastname, titleofcourtesy, city, country) VALUES
                       (@firstname, @lastname, @titleofcourtesy, @city, @country) ";
    // Tạo connection
    SqlConnection conn = new SqlConnection(connString);
    try
    {
        // Tạo Adapter
        SqlDataAdapter da = new SqlDataAdapter(qry, conn);
        // Tạo và lấp đầy DataSet
        DataSet ds = new DataSet();
        da.Fill(ds, "employees");
        // Lấy thông tin Table vào DataTable
        DataTable dt = ds.Tables["employees"];
        // Tạo thêm row mới
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = "Bui";
        newRow["lastname"] = "Hung";
        newRow["titleofcourtesy"] = "AND";
        newRow["city"] = "HaNoi";
        newRow["country"] = "Viet Nam";
        dt.Rows.Add(newRow);
        // Hiển thị thông tin các rows trong DataSet sau khi thêm vào
        foreach (DataRow row in dt.Rows)
        {
            Literal1.Text = row["firstname"].ToString().PadRight(15) + "|";
            Literal1.Text += row["lastname"].ToString().PadLeft(15) + "|";
            Literal1.Text += row["city"];
        }
        // Làm việc với Insert
        SqlCommand cmd = new SqlCommand(ins, conn);
        cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname");
        cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 20, "lastname");
        cmd.Parameters.Add("@titleofcourtesy", SqlDbType.NVarChar, 25, "titleofcourtesy");
        cmd.Parameters.Add("@city", SqlDbType.NVarChar, 15, "city");
        cmd.Parameters.Add("@country", SqlDbType.NVarChar, 15, "country");
        // Tiến hành insert vào database Source
        da.InsertCommand = cmd;
        da.Update(ds, "employees");
    }
    catch (Exception e)
    {
        // Bắt lỗi
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Đóng kết nối
        conn.Close();
    }
}

Qua ví dụ trên bạn có thể nhận thấy DataSet khá linh hoạt, thông qua việc đổ dữ liệu từ dataSet đến các Tables, chúng ta có thể xem thông tin, chỉnh sửa thông tin và khi chỉnh sửa xong có thể update cho DataSource. Nếu các bạn nào tinh ý sẽ thấy quá trình trên thêm vào thực hiện quá dài cứ lập đi lặp lại việc cmd.Parameters.Add,để giải quyết vấn để này .NET cung cấp cho ta SqlCommandBuilder
Bạn xem ví dụ sau:

private void DataSetInsertAndView()
{
    // Tạo connection string
    string connString = @"Server =.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=******";
    // Tạo SQL query        
    string ins = @"INSERT INTO employees (firstname, lastname, titleofcourtesy, city, country) VALUES
                       (@firstname, @lastname, @titleofcourtesy, @city, @country) ";
    // Tạo connection
    SqlConnection conn = new SqlConnection(connString);
    try
    {
        // Tạo Adapter
        SqlDataAdapter da = new SqlDataAdapter(qry, conn);
        // Tạo commandbuider
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        // Tạo và lấp đầy DataSet
        DataSet ds = new DataSet();
        da.Fill(ds, "employees");
        // Lấy thông tin Table vào DataTable
        DataTable dt = ds.Tables["employees"];
        // Tạo thêm row mới
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = "Pi";
        newRow["lastname"] = "Pi_Pi";
        newRow["titleofcourtesy"] = "AND";
        newRow["city"] = "UITS";
        newRow["country"] = "Viet Nam";
        dt.Rows.Add(newRow);
        // Hiển thị thông tin các rows trong DataSet sau khi thêm vào
        foreach (DataRow row in dt.Rows)
        {
            Literal1.Text = row["firstname"].ToString().PadRight(15) + "|";
            Literal1.Text += row["lastname"].ToString().PadLeft(15) + "|";
            Literal1.Text += row["city"];
        }
        //Nhờ sử dụng SqlCommandBuilder mà ta update rất nhanh
        da.Update(ds, "employees");
    }
    catch (Exception e)
    {
        // Bắt lỗi
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Đóng kết nối
        conn.Close();
    }
}

Trong hai ví dụ trên mình có tạo 1 <asp:Literal ID="Literal1" runat="server" /> để khi chạy sẽ hiển thị dữ liệu. Để chạy thử hàm trong ví dụ trên bạn chỉ cần đưa nó lên hàm Page_Load: DataSetInsertAndView();


3.Tổng kết và So sánh giữa DataSet và DataReader

3.1. DataReader:

-Ưu điểm: Thực thi nhanh, ít tốn tài nguyên hơn so với DataSet rất nhiều
-Khuyết Điểm: Tùy biến không cao, thường dùng để lấy dữ liệu và trình bày, hạn chế chỉnh sửa CSDL

3.2. DataSet

- Ưu điểm: Tùy biến cao, có thể chỉnh sửa CSDL rất tiện lợi
-Khuyết điểm: Như đã trình bày, hơi tốn tài nguyên

Lời khuyên: Tùy vào từng trườn hợp, từng mục đích mà ta có thể quyết định chọn cái nào, tránh lạm dụng quá khả năng một cái, phải biết kết hợp hài hòa. Thông thường khi bạn xuất CSDL viết dưới dạng XML, sau đó thao tác trên XML thì bạn nên sử dụng DataReader để phát huy sức mạnh của nó.

 

SQL Data Provider VB.NET Class

Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient

Namespace SqlDataProvider

    ''' <summary>
    ''' This class provides a fast and universal method for accessing SQL Server database.This class cannot be inherited.
    ''' </summary>
    Public NotInheritable Class SqlDatabase

#Region " Local Property Declarations "

        Dim _connectionString As String

#End Region

#Region " Constructor "

        ''' <summary>
        ''' Initializes a new instance of the ADO.SqlDatabase class.
        ''' </summary>
        ''' <param name="connectionString">The connection used to open the SQL Server database.</param>
        Public Sub New(ByVal connectionString As String)
            _connectionString = connectionString
        End Sub

#End Region

#Region " Public Properties "

        ''' <summary>
        ''' Gets or sets the string used to open a SQL Server database.
        ''' </summary>
        ''' <returns>The connection string that includes the source database name, and other parameters needed to establish the initial connection.</returns>
        Public Property ConnectionString() As String
            Get
                Return _connectionString
            End Get
            Set(ByVal value As String)
                _connectionString = value
            End Set
        End Property

#End Region

#Region " Private Methods "

        Private Sub AssignParameters(ByVal cmd As SqlCommand, ByVal cmdParameters() As SqlParameter)
            If (cmdParameters Is Nothing) Then Exit Sub
            For Each p As SqlParameter In cmdParameters
                cmd.Parameters.Add(p)
            Next
        End Sub

        Private Sub AssignParameters(ByVal cmd As SqlCommand, ByVal parameterValues() As Object)
            If Not (cmd.Parameters.Count - 1 = parameterValues.Length) Then Throw New ApplicationException("Stored procedure's parameters and parameter values does not match.")
            Dim i As Integer
            For Each param As SqlParameter In cmd.Parameters
                If Not (param.Direction = ParameterDirection.Output) AndAlso Not (param.Direction = ParameterDirection.ReturnValue) Then
                    param.Value = parameterValues(i)
                    i += 1
                End If
            Next
        End Sub

#End Region

#Region " ExecuteNonQuery "

        ''' <summary>
        ''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
        ''' </summary>
        ''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
        ''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
        ''' <returns>The number of rows affected.</returns>
        Public Function ExecuteNonQuery(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As Integer
            Dim connection As SqlConnection = Nothing
            Dim transaction As SqlTransaction = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As Integer = -1
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(cmd, connection)
                command.CommandType = cmdType
                Me.AssignParameters(command, parameters)
                connection.Open()
                transaction = connection.BeginTransaction()
                command.Transaction = transaction
                res = command.ExecuteNonQuery()
                transaction.Commit()
            Catch ex As Exception
                If Not (transaction Is Nothing) Then
                    transaction.Rollback()
                End If
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (transaction Is Nothing) Then transaction.Dispose()
            End Try
            Return res
        End Function

        ''' <summary>
        ''' Executes a Transact-SQL statement against the connection and returns the number of rows affected.
        ''' </summary>
        ''' <param name="spname">The stored procedure to execute at the data source.</param>
        ''' <param name="returnValue">The returned value from stored procedure.</param>
        ''' <param name="parameterValues">The parameter values of the stored procedure.</param>
        ''' <returns>The number of rows affected.</returns>
        Public Function ExecuteNonQuery(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As Integer
            Dim connection As SqlConnection = Nothing
            Dim transaction As SqlTransaction = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As Integer = -1
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(spname, connection)
                command.CommandType = CommandType.StoredProcedure
                connection.Open()
                SqlCommandBuilder.DeriveParameters(command)
                Me.AssignParameters(command, parameterValues)
                transaction = connection.BeginTransaction()
                command.Transaction = transaction
                res = command.ExecuteNonQuery()
                returnValue = command.Parameters(0).Value
                transaction.Commit()
            Catch ex As Exception
                If Not (transaction Is Nothing) Then
                    transaction.Rollback()
                End If
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (transaction Is Nothing) Then transaction.Dispose()
            End Try
            Return res
        End Function

#End Region

#Region " ExecuteScalar "

        ''' <summary>
        ''' Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        ''' </summary>
        ''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
        ''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
        ''' <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        Public Function ExecuteScalar(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As Object
            Dim connection As SqlConnection = Nothing
            Dim transaction As SqlTransaction = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As Object = Nothing
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(cmd, connection)
                command.CommandType = cmdType
                Me.AssignParameters(command, parameters)
                connection.Open()
                transaction = connection.BeginTransaction()
                command.Transaction = transaction
                res = command.ExecuteScalar()
                transaction.Commit()
            Catch ex As Exception
                If Not (transaction Is Nothing) Then
                    transaction.Rollback()
                End If
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (transaction Is Nothing) Then transaction.Dispose()
            End Try
            Return res
        End Function

        ''' <summary>
        ''' Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
        ''' </summary>
        ''' <param name="spname">The stored procedure to execute at the data source.</param>
        ''' <param name="returnValue">The returned value from stored procedure.</param>
        ''' <param name="parameterValues">The parameter values of the stored procedure.</param>
        ''' <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
        Public Function ExecuteScalar(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As Object
            Dim connection As SqlConnection = Nothing
            Dim transaction As SqlTransaction = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As Object = Nothing
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(spname, connection)
                command.CommandType = CommandType.StoredProcedure
                connection.Open()
                SqlCommandBuilder.DeriveParameters(command)
                Me.AssignParameters(command, parameterValues)
                transaction = connection.BeginTransaction()
                command.Transaction = transaction
                res = command.ExecuteScalar()
                returnValue = command.Parameters(0).Value
                transaction.Commit()
            Catch ex As Exception
                If Not (transaction Is Nothing) Then
                    transaction.Rollback()
                End If
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (transaction Is Nothing) Then transaction.Dispose()
            End Try
            Return res
        End Function

#End Region

#Region " ExecuteReader "

        ''' <summary>
        ''' Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection, and builds a System.Data.SqlClient.SqlDataReader using one of the System.Data.CommandBehavior values.
        ''' </summary>
        ''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
        ''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
        ''' <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        Public Function ExecuteReader(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As IDataReader
            Dim connection As SqlConnection = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As SqlDataReader = Nothing
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(cmd, connection)
                command.CommandType = cmdType
                Me.AssignParameters(command, parameters)
                connection.Open()
                res = command.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            End Try
            Return CType(res, IDataReader)
        End Function

        ''' <summary>
        ''' Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection, and builds a System.Data.SqlClient.SqlDataReader using one of the System.Data.CommandBehavior values.
        ''' </summary>
        ''' <param name="spname">The stored procedure to execute at the data source.</param>
        ''' <param name="returnValue">The returned value from stored procedure.</param>
        ''' <param name="parameterValues">The parameter values of the stored procedure.</param>
        ''' <returns>A System.Data.SqlClient.SqlDataReader object.</returns>
        Public Function ExecuteReader(ByVal spname As String, ByRef returnValue As Integer, ByVal ParamArray parameterValues() As Object) As IDataReader
            Dim connection As SqlConnection = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As SqlDataReader = Nothing
            Try
                connection = New SqlConnection(ConnectionString)
                command = New SqlCommand(spname, connection)
                command.CommandType = CommandType.StoredProcedure
                connection.Open()
                SqlCommandBuilder.DeriveParameters(command)
                Me.AssignParameters(command, parameterValues)
                res = command.ExecuteReader(CommandBehavior.CloseConnection)
                returnValue = command.Parameters(0).Value
            Catch ex As Exception
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            End Try
            Return CType(res, IDataReader)
        End Function

#End Region

#Region " FillDataset "

        ''' <summary>
        ''' Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
        ''' </summary>
        ''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
        ''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
        ''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
        ''' <returns>A System.Data.Dataset object.</returns>
        Public Function FillDataset(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As DataSet
            Dim connection As SqlConnection = Nothing
            Dim command As SqlCommand = Nothing
            Dim sqlda As SqlDataAdapter = Nothing
            Dim res As New DataSet
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(cmd, connection)
                command.CommandType = cmdType
                AssignParameters(command, parameters)
                sqlda = New SqlDataAdapter(command)
                sqlda.Fill(res)
            Catch ex As Exception
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) Then connection.Dispose()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (sqlda Is Nothing) Then sqlda.Dispose()
            End Try
            Return res
        End Function

#End Region

#Region " ExecuteDataset "

        ''' <summary>
        ''' Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the System.Data.DataSet with the specified System.Data.DataTable name.
        ''' </summary>
        ''' <param name="insertCmd">A command used to insert new records into the data source.</param>
        ''' <param name="updateCmd">A command used to update records in the data source.</param>
        ''' <param name="deleteCmd">A command for deleting records from the data set.</param>
        ''' <param name="ds">The System.Data.DataSet to use to update the data source. </param>
        ''' <param name="srcTable">The name of the source table to use for table mapping.</param>
        ''' <returns>The number of rows successfully updated from the System.Data.DataSet.</returns>
        Public Function ExecuteDataset(ByVal insertCmd As SqlCommand, ByVal updateCmd As SqlCommand, ByVal deleteCmd As SqlCommand, ByVal ds As DataSet, ByVal srcTable As String) As Integer
            Dim connection As SqlConnection = Nothing
            Dim sqlda As SqlDataAdapter = Nothing
            Dim res As Integer = 0
            Try
                connection = New SqlConnection(_connectionString)
                sqlda = New SqlDataAdapter
                If Not (insertCmd Is Nothing) Then insertCmd.Connection = connection : sqlda.InsertCommand = insertCmd
                If Not (updateCmd Is Nothing) Then updateCmd.Connection = connection : sqlda.UpdateCommand = updateCmd
                If Not (deleteCmd Is Nothing) Then deleteCmd.Connection = connection : sqlda.DeleteCommand = deleteCmd
                res = sqlda.Update(ds, srcTable)
            Catch ex As Exception
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) Then connection.Dispose()
                If Not (insertCmd Is Nothing) Then insertCmd.Dispose()
                If Not (updateCmd Is Nothing) Then updateCmd.Dispose()
                If Not (deleteCmd Is Nothing) Then deleteCmd.Dispose()
                If Not (sqlda Is Nothing) Then sqlda.Dispose()
            End Try
            Return res
        End Function

#End Region

#Region " ExecuteScript "

        ''' <summary>
        ''' Executes a SQL query file against the connection.
        ''' </summary>
        ''' <param name="filename">SQL query file name.</param>
        ''' <param name="parameters">The parameters of the SQL query file.</param>
        Public Sub ExecuteScript(ByVal filename As String, Optional ByVal parameters() As SqlParameter = Nothing)
            Dim fStream As FileStream = Nothing
            Dim sReader As StreamReader = Nothing
            Dim connection As SqlConnection = Nothing
            Dim command As SqlCommand = Nothing
            Try
                fStream = New FileStream(filename, FileMode.Open, FileAccess.Read)
                sReader = New StreamReader(fStream)
                connection = New SqlConnection(ConnectionString)
                command = connection.CreateCommand()
                connection.Open()
                While (Not sReader.EndOfStream)
                    Dim sb As New StringBuilder
                    While (Not sReader.EndOfStream)
                        Dim s As String = sReader.ReadLine
                        If (Not String.IsNullOrEmpty(s)) AndAlso (s.ToUpper.Trim = "GO") Then
                            Exit While
                        End If
                        sb.AppendLine(s)
                    End While
                    command.CommandText = sb.ToString
                    command.CommandType = CommandType.Text
                    AssignParameters(command, parameters)
                    command.ExecuteNonQuery()
                End While
            Catch ex As Exception
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If (Not IsNothing(connection)) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If (Not IsNothing(command)) Then command.Dispose()
                If (Not IsNothing(sReader)) Then sReader.Close()
                If (Not IsNothing(fStream)) Then fStream.Close()
            End Try
        End Sub

#End Region

    End Class

End Namespace

SqlDatabaseException

Namespace SqlDataProvider

    ''' <summary>
    ''' Represents errors that occur during application execution.
    ''' </summary>
    Public Class SqlDatabaseException
        Inherits Exception

#Region " Constructor "

        ''' <summary>
        ''' Initializes a new instance of the ADO.SqlDatabaseException class.
        ''' </summary>
        Public Sub New()
        End Sub

        ''' <summary>
        ''' Initializes a new instance of the ADO.SqlDatabaseException class with a specified error message.
        ''' </summary>
        ''' <param name="message">The message that describes the error.</param>
        Public Sub New(ByVal message As String)
            MyBase.New(message)
        End Sub

        ''' <summary>
        ''' Initializes a new instance of the ADO.SqlDatabaseException class with a specified error message and a reference to the inner exception that is the cause of this exception.
        ''' </summary>
        ''' <param name="message">The error message that explains the reason for the exception.</param>
        ''' <param name="innerException">The exception that is the cause of the current exception, or a null reference (Nothing in Visual Basic) if no inner exception is specified.</param>
        Public Sub New(ByVal message As String, ByVal innerException As Exception)
            MyBase.New(message, innerException)
        End Sub

#End Region

    End Class

End Namespace

Cách sử dụng

SQLDataProvider Class Documentation
This class provides a fast and universal method for accessing SQL Server database.
Create Instance
At first you create an instance of SqlDatabase class.

 

Dim sqldb As New SqlDatabase("Data Source=(local); Initial Catalog= ; UId = ; Pwd = ;")

For more information about connection strings, visit ConnectionStrings.com.

ExecuteNonQuery Method


Executes a Transact-SQL statement against the connection and returns the number of rows affected.

 

 

Dim params(0 To 1) As SqlParameter
params(0) = New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)
params(0).Value = "Stefan"
params(1) = New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)
params(1).Value = "Cameron"
sqldb.ExecuteNonQuery("Insert Into dbo.Users(Firstname, LastName) Values(@FirstName, @LastName)", CommandType.Text, params)

If you are using stored procedure,you can execute that without declaring parameters such as following code:

sqldb.ExecuteNonQuery("dbo.CreateUser", Nothing, "Stefan", "Cameron")

ExecuteScalar Method

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Dim count As Integer = sqldb.ExecuteScalar("Select Count(*) From dbo.Users", CommandType.Text)
MsgBox("Number of row(s): " & count)


ExecuteReader Method
Sends the CommandText to the Connection and builds a SqlDataReader.

Dim FirstName As String = String.Empty
Dim LastName As String = String.Empty

Dim params(0) As SqlParameter
params(0) = New SqlParameter("@Id", SqlDbType.Int)
params(0).Value = 1

Dim dr As IDataReader = sqldb.ExecuteReader("Select * From dbo.Users Where (Id = @Id)", CommandType.Text, params)
While dr.Read()
    FirstName = dr("Firstname")
    LastName = dr("Lastname")
End While
dr.Close()

MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information)

There is a sample for using stored procedure:

Create Procedure [dbo].[GetUserInfo]
    (
        @Id int
    )
As
Begin
    Select * From dbo.Users Where (Id = @Id)
End
Dim FirstName As String = String.Empty
Dim LastName As String = String.Empty

Dim dr As IDataReader = sqldb.ExecuteReader("dbo.GetUserInfo", Nothing, 1)
While dr.Read()
    FirstName = dr("Firstname")
    LastName = dr("Lastname")
End While
dr.Close()

MsgBox(FirstName & " " & LastName, MsgBoxStyle.Information)

Using Return Value Parameter
If you are using stored procedure,you can get the value of 'return value parameter'.

Create Procedure dbo.UserExists
    (
        @Firstname nvarchar(120),
        @Lastname nvarchar(120)
    )
As
Begin
    If Exists(Select * From dbo.Users Where (Firstname = @Firstname) And (Lastname = @Lastname))
        Return 1
End
Dim retval As Integer
sqldb.ExecuteNonQuery("dbo.UserExists", retval, "Stefan", "Cameron")
MsgBox("User Exists: " & IIf(retval = 1, "Yes", "No"))

FillDataset Method
Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
Binding a DataGridView with FillDataset method.

DataGridView1.DataSource = sqldb.FillDataset("Select * From dbo.Users", CommandType.Text).Tables(0)

ExecuteDataset Method

Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the System.Data.DataSet with the specified System.Data.DataTable name.

' Getting the System.Data.DataSet.
Dim ds As DataSet = CType(DataGridView1.DataSource, DataTable).DataSet

' Declaring insert command object
Dim inscmd As New SqlCommand("Insert Into dbo.Users(Firstname, Lastname) Values(@Firstname, @Lastname)")
With inscmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"
    .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname"
End With

' Declaring update command object
Dim updcmd As New SqlCommand("Update dbo.Users Set Firstname = @Firstname, Lastname = @Lastname Where (Id = @Id)")
With updcmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"
    .Parameters.Add(New SqlParameter("@Firstname", SqlDbType.NVarChar, 120)).SourceColumn = "Firstname"
    .Parameters.Add(New SqlParameter("@Lastname", SqlDbType.NVarChar, 120)).SourceColumn = "Lastname"
End With

' Declaring delete command object
Dim delcmd As New SqlCommand("Delete From dbo.Users Where (Id = @Id)")
With delcmd
    .CommandType = CommandType.Text
    .Parameters.Add(New SqlParameter("@Id", SqlDbType.Int)).SourceColumn = "Id"
End With

' Updating data source
sqldb.ExecuteDataset(inscmd, updcmd, delcmd, ds, ds.Tables(0).TableName)