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.
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
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