Imports System.Data
Imports System.Data.SqlClient
The above namespaces will allow you to access the SqlConnection, SqlCommand, SqlAdapter classes which provide the functionality to connect the SQL database and fetch the records from the specified table as in the following
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
ViewState("sortOrder") = ""
bindGridView("", "")
End If
End Sub
Public Sub bindGridView(ByVal sortExp As String, ByVal sortDir As String)
' string variable to store the connection string
' defined in appsettings section of web.config file.
Dim connStr As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
' object created for SqlConnection Class.
Dim mySQLconnection As New SqlConnection(connStr)
' if condition that can be used to check the sql connection
' whether it is already open or not.
If mySQLconnection.State = ConnectionState.Closed Then mySQLconnection.Open()
Dim mySqlCommand As New SqlCommand("select * from products", mySQLconnection)
Dim mySqlAdapter As New SqlDataAdapter(mySqlCommand)
Dim myDataSet As New DataSet()
mySqlAdapter.Fill(myDataSet)
Dim myDataView As New DataView()
myDataView = myDataSet.Tables(0).DefaultView
If Not String.IsNullOrEmpty(sortExp) Then
myDataView.Sort = String.Format("{0} {1}", sortExp, sortDir)
End If
GridView1.DataSource = myDataView
GridView1.DataBind()
' if condition that can be used to check the sql connection
' if it is open then close it.
If mySQLconnection.State = ConnectionState.Open Then mySQLconnection.Close()
End Sub
Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)
bindGridView(e.SortExpression, sortOrder)
End Sub
Public Property sortOrder() As String
Get
If ViewState("sortOrder").ToString() = "desc" Then
ViewState("sortOrder") = "asc"
Else
ViewState("sortOrder") = "desc"
End If
Return ViewState("sortOrder").ToString()
End Get
Set(ByVal value As String)
ViewState("sortOrder") = value
End Set
End Property[/code]