VS 2005 ADO.Net Basics

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I'm a VB6 developer who has recently installed Visual Studio 2005 and I want
to get my hands dirty with VB.NET 2005! I'm struggling with database
connections, and I mean floundering!

Most of the examples I've found have been using forms and controls but I
would like to do it all in code. You could set up a connection and recordset
very easily in classic ADO so I don't suppose it's that hard but I'm stuck.
All I want to do is create a connection to a SQL database, query a table (any
table) and then set the value of a string variable with the value of a field
in my returned records.

Can anyone give me a basic example to set me on my way?

I would really appreciate it.

Thanks in advance for any help
Paul
 
Hi everyone,

I'm a VB6 developer who has recently installed Visual Studio 2005 and I want
to get my hands dirty with VB.NET 2005! I'm struggling with database
connections, and I mean floundering!

Most of the examples I've found have been using forms and controls but I
would like to do it all in code. You could set up a connection and recordset
very easily in classic ADO so I don't suppose it's that hard but I'm stuck.
All I want to do is create a connection to a SQL database, query a table (any
table) and then set the value of a string variable with the value of a field
in my returned records.

Can anyone give me a basic example to set me on my way?

I would really appreciate it.

Thanks in advance for any help
Paul
Click to expand...

System.Data.SQLClient.SQLConnection = ADODB.Connection
System.Data.SQLClient.SQLCommand = ADODB.Command
System.Data.SqlClient.SqlDataReader = ADODB.Recordset (almost)

The datareader doesn't contain the AddNew() or Update() these things
need to be done manually or look into using SqlDataAdapters (I'm not
too familiar with them but I know you can shortcut a lot of work with
them).
 
If you like "code based", then check out a few of these samples:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

The 1.1 is a letter better put together, but the 2.0 version works good as
well.

I would look up the EnterpriseLibrary.Data (and if you get my samples above,
you should replace SqlHelper calls with EnterpriseLibrary.Data calls )

This is a pretty good sample
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!158.entry
of using the EnterpriseLibrary, you just need to whack out the WCF stuff
probably.

When you use the EnterpriseLibrary, you start seeing VERY clean code like
this:



private readonly string PROC_ZEBRA_GET_ALL =
"[dbo].[uspZebraGetAll]";
public IDataReader ZebraGetAll()
{
IDataReader idr = null;
try
{
Database db = base.GetDatabase();//base code is in the
download
DbCommand dbc =
db.GetStoredProcCommand(this.PROC_ZEBRA_GET_ALL);
idr = db.ExecuteReader (dbc);
return idr;
}
finally
{
}
}


All url's above have downloadable code.....you just gotta find the link.


Rule #1
ADO.Net is not ADO.
#2 Do not create ADODB objects in dotnet code. (If you use an upgrade
wizard, you might get this).

Google
IDataReader LoadDataSet
and you'll find alot of info.


The code is in C#, but easily translatable. Also, if you translate, you'll
really see whats going on as your replicate the project into another dotNet
language.
 
I would definately READ the previously listed article(s);
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

But I'd look at the EnterpriseLibrary stuff .... to implement it.

You can avoid alot of building-block code that way... and still get what
you're after...

DataSets (usually strong typed ones)
IDataReaders
scalars (single values)
void / (subs in vb.net) for just executing a stored procedure.




sloan said:
If you like "code based", then check out a few of these samples:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

The 1.1 is a letter better put together, but the 2.0 version works good as
well.

I would look up the EnterpriseLibrary.Data (and if you get my samples
above, you should replace SqlHelper calls with EnterpriseLibrary.Data
calls )

This is a pretty good sample
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!158.entry
of using the EnterpriseLibrary, you just need to whack out the WCF stuff
probably.

When you use the EnterpriseLibrary, you start seeing VERY clean code like
this:



private readonly string PROC_ZEBRA_GET_ALL =
"[dbo].[uspZebraGetAll]";
public IDataReader ZebraGetAll()
{
IDataReader idr = null;
try
{
Database db = base.GetDatabase();//base code is in the
download
DbCommand dbc =
db.GetStoredProcCommand(this.PROC_ZEBRA_GET_ALL);
idr = db.ExecuteReader (dbc);
return idr;
}
finally
{
}
}


All url's above have downloadable code.....you just gotta find the link.


Rule #1
ADO.Net is not ADO.
#2 Do not create ADODB objects in dotnet code. (If you use an upgrade
wizard, you might get this).

Google
IDataReader LoadDataSet
and you'll find alot of info.


The code is in C#, but easily translatable. Also, if you translate,
you'll really see whats going on as your replicate the project into
another dotNet language.




Paul said:
Hi everyone,

I'm a VB6 developer who has recently installed Visual Studio 2005 and I
want
to get my hands dirty with VB.NET 2005! I'm struggling with database
connections, and I mean floundering!

Most of the examples I've found have been using forms and controls but I
would like to do it all in code. You could set up a connection and
recordset
very easily in classic ADO so I don't suppose it's that hard but I'm
stuck.
All I want to do is create a connection to a SQL database, query a table
(any
table) and then set the value of a string variable with the value of a
field
in my returned records.

Can anyone give me a basic example to set me on my way?

I would really appreciate it.

Thanks in advance for any help
Paul
Click to expand...
Click to expand...
 
Paul,
Try something like

Public Shared Function GetDataTable(ByVal conn As SqlConnection, ByVal
tablename As String) As DataTable

Dim _dt As New DataTable
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Dim _cmd As New SqlCommand
_cmd.Connection = conn
_cmd.CommandType = CommandType.Text
_cmd.CommandText = "select * from " & tablename
Dim da As New SqlDataAdapter(_cmd)
da.Fill(_dt)
Finally
conn.Close()
End Try
Return _dt
End Function

this will give you a DataTable full of data, you can then iterate through
its Rows collection to inspect the data (use its item(ColumnNumber) or
ItemArray properties)When you are happy with this have alook at
theSQLDataReader, the ADO.NET equivalent of a 'firehose'

Guy

Guy
 
Dear All,

Thank you very much for all your help. That's exactly the kind of help I was
after.

Many thanks
Paul
 
Back
Top