Example of accessing ADO.Net

[TheWizardofInt]

Does anyone have an example of accessing a SQL database using ADO.Net?

 

I just need a kick start on this.

[Derek Stone]

Dim oConnection As New SqlConnection
Dim oCommand As New SqlCommand
Dim oDataReader As SqlDataReader

oConnection.ConnectionString = "user id=;password=;database=;server=;Connect Timeout=15"
oConnection.Open()

oCommand.Connection = oConnection
oCommand.CommandText = "SELECT * FROM myTable"

oDataReader = oCommand.ExecuteReader()

While oDataReader.Read()
   'Loop though data
End While

oDataReader.Close()
oConnection.Close()

[Guest kdn102]

Does anyone have the same example for C++?

[Vitaly]

Derek, and if you use sqlDataReader, what is the equivalent for

ADO 2.6 EOF, BOF or Field collection?

 

Thanks.

[Derek Stone]

EOF: SqlDataReader.Read = False

BOF: No equivalent and no need for one. It's the beginning if you haven't called .Read yet.

Field collection: Item Collection

[Guest hesaigo999ca]

Is ther any property that allows me to know if a connection object is closed (i.e. VB6 = if recordset.state = adstateopen then

[PlausiblyDamp]

the SQLConnection object has a state property.

 

 

dim conn as System.Data.SqlClient.SQLConnection

if conn.State = ConnectionState.Closed 'Connection closed

end if

[sjchin]

Can we open new reader while i doing loop of 1 reader?

example :

 

oDataReader = oCommand.ExecuteReader()

 

While oDataReader.Read()

'Loop though data

' At there contain read to other sql statement to retrieve info

End While

[Derek Stone]

Yes, but you'll need a second connection. Only one DataReader can be associated with a connection at a time.

[hemenkap]

hi possiblydamp,

 

just a little bit confused as to checking the connection state using

the code

dim conn as System.Data.SqlClient.SQLConnection

 

if conn.State = ConnectionState.Closed 'Connection closed

 

end if

 

 

--------------------------------------------------------------------------------

as you stated above. i think this will check the connection state.

how exactly can i do it for a datset? like we do for recordset. is there any method.

 

also while searching in recordset using find command we can know the cursor position in the recordset.

 

in dataset.table.Select we can search the fields but can we get the location where the search stopped ?

 

help.

[Derek Stone]

A DataSet doesn't have a connection state. It is always disconnected, as that is its nature.

[Roey]

Hi Guys, I'm just doing a bit of research towards moving across to VB.Net and was wondering why you would use this method as opposed to using a SqlDataAdapter, which would utilize a disconnected SqlConnection. What are the advantages / disadvantages of each method.

 

Thanks

[jfackler]

A datareader is a forward only read of data...very fast but only reads doesnt allow for dataset manipulation.

"Adapters are used to exchange data between a data source and a dataset.

In many applications, this means reading data from a database into a dataset,

and then writing changed data from the dataset back to the database. However,

a data adapter can move data between any source and a dataset. For example,

there could be an adapter that moves data between a Microsoft Exchange server and a dataset." (From the search of "Visual Basic and Visual C# Concepts")

[Roey]

Thanks for your help on tha above questions it was really informative. Just one more question. In n-tier applications that I previously developed, I often used recordsets to pass data back and forth across different tiers. How does this work in regards to DataAdapters and DataReaders.

[jfackler]

Depends on how your going to implement the app. If you're serving up data across a large network,

the best way is asp.net. Uses the same disconnected dataset and all the work is done on the server.

No worries about cursors, the adapter takes care of that for you.

That what you're concerned about?