VB.NET: Connect to SQL Server


Here is the step-by-step procedure to connect to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.


Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
3. Declare and instantiate your SQLConnection object as shown below

Dim con As New SqlConnection

SQLConnection class represents an open connection to a SQL Server database.
4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

The connectionstring value usually contains the following :
Data Source - physical server hostname
Initial Catalog - your database name
User ID - SQL username use to connect to the server
Password - SQL username's password

On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here 

5. Last step is to invoke the Open method of the connection object

con.Open() 

The complete sample sourcecode:

Imports System.Data.SqlClient 
Imports System.Data  
Private Sub ConnectToSQL()  
Dim con As New SqlConnection  
Dim cmd As New SqlCommand   
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security  Info=True;User ID=sa;Password=12345678"  
con.Open()End Sub


To capture if the connection was successful or not, just tweak the above code:


Imports System.Data.SqlClient 
Imports System.Data  
Private Sub ConnectToSQL()  
Dim con As New SqlConnection  
Dim cmd As New SqlCommand Try  
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist   Security Info=True;User ID=sa;Password=12345678"  
con.Open() Catch ex As Exception  MessageBox.Show("Error while connecting to SQL Server." & ex.Message) 
Finally  
con.Close() 'Whether there is error or not. Close the connection. End TryEnd Sub


2 comments:

  1. simply :
    Instructions

    1
    Click the Windows "Start" button and select "All Programs." Click "Microsoft .NET Framework," then click "Visual Studio" to open the software.

    2
    Open your project file to load all of the code. Double-click the form you want to use to connect to the server.

    3
    Add the ADO libraries to the top of the code file. Copy and paste the following library files to your VB editor:

    Imports System.Data

    Imports System.Data.SqlClient

    4
    Create the SQL Server connection using the ADO object. The following code connects to the "myserver" SQL Server:

    Dim connstring As String

    connstring = "server=myserver;uid=username;pwd=password;database=db;"

    Dim conn As New SqlConnection(connstring)

    conn.Open()

    Replace the "myserver," "username," "password," and "db" values with your own server information.

    5
    Query the server. After you connect to the server, you can query for database information. The following code retrieves a list of customers:

    Dim command As New SqlCommand("SELECT * from customers", conn)

    Dim reader As SqlDataReader = command.ExecuteReader()

    6
    Close the connection. After you complete querying the server, close the connection using the following code:

    conn.Close

    ReplyDelete

Dengan mengirim komentar disini, Anda menyetujui bahwa komentar anda tidak mengandung Rasis ataupun konten pornografi