Manage Your Database Connection

This is an Example for how to manage a SQL Server database Connection using Visual Web Developer 2005

Dim _CON As String = "Data Source=MOHAMEDSAMI-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"

Here this is the Connection String and I will tell you in another blog how you can get this String easily, cause it is the most important thing, if you write the right connection you will have a successful connection to the database.

  • Data Source = Your Database Instance, you can type it in many ways like (. /SQLEXPRESS, localhost/SQLEXPRESS, machine name/SQLEXPRESS).
  • Initial Catalog = Your Database Name.

Public newSqlConnection As SqlConnection = New SqlConnection(_CON)

I defined a new Object from SqlConnection so I can manage and establish the Connection.
I have to open the Connection so I can execute any Query I want.

Private Function OpenConnection() As Boolean
Try
               newSqlConnection.Open()
               Return True
Catch ex As Exception
               Return False
Finally
End Try
End Function

You can only just type newSqlConnection.Open() and it will do the function but I would like to make it in Function as a type of Validation.
After executing any query we have to close the connection so I created a Procedure to close the connection with the database.

Private Sub CloseConnection()
Try
                   newSqlConnection.Close()

Catch ex As Exception
Finally
End Try
End Sub

the execute function you will use to select any data from your database

Public Function ExecutemyQuery(ByVal Query As String) As DataTable
         Dim dt_reults As New DataTable
If OpenConnection() Then
       Try
         Dim newSqlCommand As SqlCommand = New SqlCommand(Query, newSqlConnection)
         Dim newSqlAdapt As New SqlDataAdapter(Query, newSqlConnection)
         newSqlCommand.ExecuteNonQuery()
         newSqlAdapt.Fill(dt_reults)
        CloseConnection()
     Catch ex As Exception
    End Try
End If
Return dt_reults
End Function


I defined an object from SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.
SqlDataAdapter: Represents a set of data commands and a database connection that are used to fill the Data table or Dataset and update a SQL Server database.
So this is the function but how we will call it?
In Any Event handler like Button_Click write This Code

Dim dt As New DataTable
dt = _Class.ExecutemyQuery(“Select * from Employee”)
   If dt.Rows.Count > 0 Then
            GridView1.DataSource = dt
            GridView1.DataBind()
  Else
            Lb_MSG.Text = "There is No Data"
 End If

_Class: is an Object from my Class which I Write all my Functions and Procedures on it
Thanks for Reading my Post, I hope this post will help you.
Salam

Comments

Popular posts from this blog

SQL Interview Questions

Number of Days in Current Month