≡ Menu

connecting to SqlServer using ADO.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

objConn.Open()

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())
Next

Now Close the connection
objConn.Close()
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
inherited.
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.

{ 0 comments… add one }

Leave a Comment

Next post:

Previous post: