Personal Mind of ASEP

Sepercik Rasa yang Tercurah

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.

November 11, 2005 - Posted by | Uncategorized

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: