connecting to SqlServer using ADO.Net

Submitted on: 1/1/2015 3:56:00 AM
By: Bheemasena Rao.Y (from psc cd)  
Level: Beginner
User Rating: By 14 Users
Compatibility: VB.NET, ASP.NET
Views: 2156
     Using Sqlconnection connecting to SqlServer with easy tips

				Category: DataBase 
Level: Beginnner
Description: Using Sqlconnection connecting to SqlServer with easy tips
Compatibility : VB.Net /ASP.Net

Namespaces to be included
Imports ADODB
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter

Sqlconnection object has to be instantiated .
The parameters are
data source = sql server name
initial catalog = database name
user id and password
workstation id=YBRAO2000
SqlCommand object has to be instantiated to execute the Sql statement.

SqlAdapter object has to be instantiated to fill the data into DataSet.

Data set must be instantiated.
Dim objConn As SqlConnection = New SqlConnection("data source=YBRAO2000;initial catalog=pubs;persist security info=False;user id=sa;workstation id=YBRAO2000;packet size=4096")

Dim become As New SqlCommand()
Dim JobsDA As SqlDataAdapter = New SqlDataAdapter()
Dim JobsDS As DataSet = New DataSet()

Now the commandobject has to be created with CreateCommand method.
objcomm = objConn.CreateCommand()
Now Specify the SQL Statement.
objcomm.CommandText = "SELECT * FROM JOBS"
Now open the connection


Now Assign the command object to Sql Adapter.
JobsDA.SelectCommand = objcomm

Now Fill the Dataset with SqlAdapter
JobsDA.Fill(JobsDS, "jobs")

Now using DataRow we can retrive all the values from the table as given below from the dataset.
Dim pRow As DataRow
For Each pRow In JobsDS.Tables("Jobs").Rows
system.Console.WriteLine (pRow("job_id").ToString())

Now Close the connection
Let me explain about the SqlDataAdapter class .
SqlDataAdapter Class :
Name space Required is System.Data.SqlClient
SqlDataAdapter Class Represents a set of data commands and a database connection that are used
to fill the dataset and update a SQL Server database and this class can not be
The SqlDataAdapter, serves as a bridge between a
dataset and SQL Server for retrieving and saving data.
SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase
performance when connecting to a Microsoft SQL Server database.

The SqlDataAdapter also includes the SelectCommand ,InsertCommand ,DeleteCommand ,UpdateCommand ,
and TableMappings properties to facilitate the loading and updating of data.
Below I am giving some sample examples through which you can understand how exactly this object works .

JobsDA.SelectCommand = New sqlCommand( "SELECT JobID,JobName FROM Jobs" , objConn)
JobsDA.InsertCommand = New sqlCommand( "Insert Into Jobs values (1,'Marketing')" , objConn)
JobsDA.UpdateCommand = New sqlCommand( "Update Jobs Set JobName ='Financial" Where JobID =1 " , objConn)
JobsDA.DeleteCommand = New sqlCommand( "Delete From Jobs Where JobID =1 " , objConn)
I hope now you are able to get the core Concepts of the SqlDataAdapter class and its behaviour.

Bye for Now

Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

 There are no comments on this submission.

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.