VB.NET database connectivity tutorials

P

piako

[H]ard|Gawd
Joined
Apr 5, 2006
Messages
1,613
Hi. Are there any good references, examples, or walkthroughs that explain how VB connects to a microsoft database?

There's the simple data wizard, but it ends up creating a few objects called DbDataSet, TableAdapterManager, and then ___TableAdapters and __BindingSource for each table referenced in the form.

I'm moving from web applications to standard windows form applications with db backends. The methodology is similar to ADO rs in asp with similar syntax. The problem is the way microsoft has these intermediate objects; coordinating them to do what I want isn't making sense.
 
Opening and using database connections is actually quite easy.

Put this up at the top of any code file you want to use connections in:
Code: 
Imports System.Data
Imports System.Data.SqlClient

Then, to open a connection, it's very simple (skipping Try-Catch blocks for clarity sake, but always use them when opening a new connection):
Code: 
Dim conn as new SqlConnection()
conn.Open("Your Connection String")

If you want a ridiculously detailed repository of connection string creation information, head over here: http://www.connectionstrings.com/
For example, with SQL 2005, use this: "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

That's it, you've got an open connection. (Verify by checking the conn.State property to see if it's set to ConnectionState.Open.) You use SqlCommand objects to form a command, and then use it to execute a query. I personally prefer using DataReaders when I execute queries that return rows. Here's three examples:

Code: 
// Get a DataReader so you can iterate through the rows in the results.
Dim cmd As New SqlCommand("SELECT name, value FROM TableName", conn)
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
    Dim name As String = CStr(reader("name"))
    Dim value As String = Cstr(reader("value"))
End While
reader.Close()

// Execute a query that returns no rows.
cmd = New SqlCommand("INSERT INTO TableName (name, value) VALUES ('Key1', 'Value1')", conn)
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
// Note: ExecuteNonQuery() returns an integer indicating the number of rows affected.

// Execute a query and grab only the value of the first column of the first row. Handy for queries that you expect exactly one value, saves you from building a DataReader just to get the one value.
cmd = New SqlCommand("SELECT value FROM TableName WHERE name = 'Key1'", conn)
Dim v As String = CStr(cmd.ExecuteScalar())

The other method to collect data is via DataSets. I prefer DataReaders for their performance, but DataSets grab all the data at once as well as allowing you more control over the results including the ability to manipulate them. Hit this URL for a decent tutorial: http://vb.net-informations.com/dataset/vb.net-ado.net-dataset-tutorial.htm

Hope that helps, but if you need more examples, feel free to reply or drop me a PM.
 
Sweet thanks. There are some differences between the .NET frameworks and VB versions. I'll have to sort that out. Hoping to have complex SQL going by Monday! =)
 
piako said:
Sweet thanks. There are some differences between the .NET frameworks and VB versions. I'll have to sort that out. Hoping to have complex SQL going by Monday! =)
Click to expand...

For the scope of this thread so far, there is not anything different between the versions of the .Net framework.
 
PTNL said:
For the scope of this thread so far, there is not anything different between the versions of the .Net framework.
Click to expand...

I think he's talking about differences between vb6 (ado) and .net (ado.net)? Since he talked about asp and ado in the OP.
 
You must log in or register to reply here.
Back
Top