Connection to a SQL Server database canbe achieved in vb.net quite easily.
The Windows Forms based applications as well as ASP.NET based applications can connect to the SQL Server database in a similar way. But the way the ConnectionStrings are stored is slightly different.
1. Windows Forms Applications – How to make a connection in a Windows Forms based application
I normally use a module in which I have the following two functions – one for opening a connection and one for closing the connection.
Step 1:
At the top of the module write the following line
Imports System.Data.SqlClient
Step 2
Also declare a public string in which you can store the connection string as follows
( well there are different other ways to store the connection string but I will not discuss them here)
Public gsConnString As String = "Data Source=100.200.300.400;Initial Catalog=testdatabase;
User Id=testuser;Password=testpassword;"
Step 3
Function OpenConnection(ByVal cn As SqlConnection) As Boolean
Try
If cn.State <> ConnectionState.Closed Then
cn.Close()
End If
cn.ConnectionString = gsConnString
cn.Open()
Return True ' If the connection is made then it returns true
Catch
MsgBox("Unable to Connect to the database" & Err.Description, vbCritical)
Return False ' If the connection is not made then it returns false
End Try
End Function
Function CloseConnection(ByVal cn As SqlConnection) As Boolean
Try
If cn.State <> ConnectionState.Closed Then
cn.Dispose()
cn.Close()
End If
Return True
Catch
End Try
End Function
Now that you have written the functions and the connection string for your SQL Server . Now you are ready to open the connection in your form or module
Step 1
write the following line at the top of the form or the module
Imports System.Data.SqlClient
Step 2
You can use the openconnection and the close connection in your programs the following way.
This is a useful generic function which you can use to fill and return a datatable. You simply pass the sql statement to it and then you can get a datatable with the results
Once you get the datatable you can use it eg. use it in a datagrid
Function GetDataTable(ByVal sql As String) As DataTable
Dim dt As New DataTable
Dim dc As New SqlCommand
Dim cn As New SqlConnection
dc.CommandTimeout = 0
Dim da As New SqlDataAdapter
OpenConnection(cn)
da.SelectCommand = dc
dc.Connection = cn
dc.CommandText = sql
Try
da.Fill(dt)
Return dt
Catch
MsgBox("Error encountered, please try again:(" & sql & ")" & Err.Description, MsgBoxStyle.Critical)
Finally
dt.Dispose()
dc.Dispose()
CloseConnection(cn)
End Try
End Function
In the following function we are just passing the SQL string to the GetDataTable function which returns the datatable filled with the results. and the Datagrid is being populated with this table.
Private Sub btnFetchData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetchData.Click
Dim sql As String
sql = "SELECT UserID,SectionID, PostID, ThreadID, PostAuthor, PostLevel,PostDate FROM dbo.cs_Posts order by PostID Desc"
Me.DataGridView1.DataSource = GetDataTable(sql)
End Sub
In ASP.NET the Connection string is normally stored in the web.config file
Following example shows a connection string for a sql server 2008 database.
This is how you would read the connection string from the web.config file
Public gsConnString As String = ConfigurationSettings.ConnectionStrings("vbnet_ConnectionString").connectionstring
They you can open the connection using the Openconnection function as described above.
Hits:
298
Current Rating :
No Of Ratings:
416
AuthorName:
admin
Comments