AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=101&pId=-1
ASP.NET & Databases Part 1
page
by . .
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 33427/ 30

Introduction

ASP.NET & Databases : Part 1

Published 10/16/01

Introduction

No matter what I try, getting your head around a dataset it not easy, there are so many methods and properties! This series will hopefully provide you with some knowledge of ASP.NET's workings with databases. I'm not going to cover everything as I could probably do a book on the subject, but I'll get you into it. This series will span several parts that I will release over a period of days, It will cover -

·         Managed Providers

·         Datasets

·         Viewing data

·         XML

So lets get started.
 

Managed Providers (?)
 

If your new, your probably asking - What's a 'Managed Provider'?
Well they are simply the a way to connect and retrieve data, like the ADODB Connection, Command and all that, but a lot more. Managed Providers come in two flavors - Vanilla and Chocolate (OLEDB and SQL Server (version 7 and above)), I like chocolate the best.
The reason behind having two separate providers is that Microsoft thinks that its SQL Server can do much better with its own provider and connection (the provider uses the tabular-data format which SQL Server uses), there has been hinting of other companies developing their own for ASP.NET but I haven't seen it, so their stuck with OLEDB (which is better than I give it credit for.

For the purpose of these demo's I'll do it in OLEDB (it's not that different, and I've already got a SQL one somewhere), but in real life, I'd be using SQL.
 

Namespaces
 

For all the demo's you'll need the following namespaces -

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>

Connection


Connection
 

To connect to a database, you now use the OleDbConnection like this -

Dim objConn as New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\sff\site\db\users.mdb")

As you can see it takes the connection string as a parameter, this is quite simple. Of course, you then have to open it with the open() method.

objConn.Open()

That is all.
In case you're wondering, you use the Close method to close it.


Command


Command
 

Once you've got a connection to the database, you can then send commands to it. The OleDbCommand object allows you to send commands to the database. You enter a SQL statement and depending on how you execute it (it has several execute commands) it can do almost anything with the data.

Dim objCmd as New OleDbCommand("SELECT * From users", objConn)

As you can see, its simple, it takes in a SQL String and the name of a connection object to use. You can also do it this way -

Dim objCmd as New OleDbCommand()
'Later on
objCmd.Connection = objConn
objCmd.CommandText = "SELECT * FROM users"

'You could even do it like this -
Dim objCmd as New OleDbCommand(SQL String, connection string)

We won't worry about these, they do exactly the same thing with the last one you provide a connection string instead of a connection object.
At this stage, we haven't executed the statement yet and there are numerous ways to do this.

·         ExecuteNonQuery

This is the way you execute when the string isn't going to return any data, like an INSERT or UPDATE SQL string.

objCmd.ExecuteNoQuery

                                    It does its job and that's it.

·         ExecuteReader

If you've got a data reader (explained later, then you can use this to put the data into a data reader -

Dim objRd as OleDbDataReader
objRd = objCmd.ExeuteReader

 

·         ExecuteScalar

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database.
I've been using the OleDbCommand to insert things into a database, it's quite easy actually.

Data Reader

Data Reader
 

The OleDbDataReader is a way to read data, not write or update, but read. It streams the data (unlike a dataset) so is a bit limited to use, but for very simple data reading (i.e. display of data) its perfect.
 

Dim objReader as OleDbDataReader
objReader = objCmd.ExecuteReader

 

While objReader.Read
      Response.Write(objReader.GetString(0) & "<br>")
End While


The top one shows you what we've already done before - Using the Command to fill the Data Reader.
The bottom one displays the data for us. The Read method keeps on reading each line of the results and stops when there are no more. objReader.GetString(0) tells it to get the string version of the first column of the current record (columns start a 0).

Here is a list of the Get[thing](index).

·         GetBoolean(x)

·         GetByte(x)              - Returns it as a Byte value

·         GetBytes(x)            - Returns it as a Byte array

·         GetChar(x)              - Returns char value

·         GetChars(x)            - Returns char array

·         GetDataTypeName(x) - Returns the data type of the specified column

·         GetDateTime(x)

·         GetDecimal(x)

·         GetDefaultStream(x) - Returns a Stream object

·         GetDouble(x)

·         GetFieldType(x)       - Get the Type that is the data type of the object

·         GetFloat(x)

·         GetGuid(x)               - Retunrs the value as a Globally Unique Idetifier Value (GUID)

·         GetInt16(x)

·         GetInt32(x)

·         GetInt64(x)

·         GetName(x)              - Returns the name of the column

·         GetOrdinal(name)      - Returns the column index as given by the column name

·         GetString(x)

·         GetTimeSpan(x)

·         GetValue(x)               - Returns the data in its native format

·         GetValues(values())    - Returns all attributes for the column and places them in values()

Now that is a lot of methods just for getting a value, they take up about 90% of all of the Data Reader's methods. I've put a small explanation on the ones that aren't that obvious.
 

Data Adapter

Data Adapter
 

The OleDbDataAdapter is the thing that takes your data and provides an interface between the data and the dataset. You create it like all of the others -
 

Dim objAdapter as New OleDbDataAdapter("SELECT * FROM users", objConn)


Seems like the OleDbCommand but its not. You can use this Data Adapter for things like Filling a dataset, Updating the data source with data from the dataset and using SQL commands on the dataset. I'll take you through some of these now.
 

Dim ds as Dataset = New DataSet()
objAdapter.Fill(ds, "users")


This method (fill) takes in the name of the dataset and the table in the dataset to put it into. The table is created if it doesn't exist and then populates it with the data that it got from the database using the query it sent.

Mappings

Mapping allows you to create an alias for the names of columns in the dataset. The names don't affect the data source and when sending through the DataAdapter are automatically converted.
 

objAdapter.TableMappings.Add("adbtable", "users")
With objAdapter.TableMappings(0).ColumnMappings
     .Add("PID", "ID")
     .Add("LastName", "LName")
     .Add("StreetAddress", "Addy")
End With
objAdapter.Fill(ds)


Now when you call the dataset you don't have to use
 

ds.Tables("adbtable")

 

You can use
 

ds.Tables("users")


When we talk about datasets in Part 2, you'll see how this can come in useful sometimes.
 

Command Builder

Command Builder


When we view the dataset in Part 2, you'll see why this is useful (and I recommend it for all of your dataset updating needs). Basically when you make a change in the dataset and want to update the data source you can use one of these to generate the SQL all for you.
 

Dim objCmdBld As New OleDbCommandBuilder(objAdapter)


From there on its automatic, you can update a data source with this command alone
 

objAdapter.Update(ds, "users")


and it will update fine.

You'll find out more about this in Part 2.

What we haven't gone over here is the use of the very few others and we haven't gone over the SQL Server ones. The others are for more advanced functions and can be left till later and then SQL Server ones are basically the same (Replace OleDb with SQL and the namespaces are a bit different).

Exercises and Summary

Exercises

If you create a database and use the following source (and test it out) you'll be all ready for tomorrow -
 

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<script language="VB" runat="server">
Sub Page_Load(sender as object, e as eventargs)

Dim objConn as New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\sff\site\db\users.mdb")
objConn.Open()

Dim ds as Dataset = New DataSet()
Dim objAdapter as New OleDbDataAdapter("SELECT * FROM users", objConn)
Dim objCmdBld As New OleDbCommandBuilder(objAdapter)

objAdapter.Fill(ds, "users")

End Sub
</script>


That is using exactly the same stuff we have been doing here. Just remember to change the Data Source when you do it.

Summary

In this part we have learnt about most of the OleDb variety of managed providers and seen how useful they are.
In part two we will examine the dataset and how you will be blown away with its complexity.

See ya there! wisemonk@aspalliance.com

 


Product Spotlight
Product Spotlight 

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 9:44:08 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search