document of ado.net

8/14/2019 Document of Ado.net

1/32

Ado.Net Hari K Reddy 1

ASP.NET & Databases : Part 1

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

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 SQLServer (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 domuch 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 -

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()

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 -


2/32


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

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: 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) & "
")

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 currentrecord (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


3/32


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 objectGetFloat(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: 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")


4/32


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

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

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

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 objCmdBld As New OleDbCommandBuilder(objAdapter)


End Sub

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

when you do it.



5/32


Introduction

Welcome to Part 2 of our series into ASP.NET & Databases (that should really be data sources). In this

part we're going to talk about the dataset object. The dataset object is huge and we are not going to cover all of it.

We'll be talking about DataTables, DataRows and all that.

We're going to be using the page that we created in Part 1, that page opened a database and filled a

dataset with data.

What is a DataSet?

Looking back on Classic ASP, we see that the no. 1 thing to store data in from a database is a recordset.

A recordset could hold one table of data and was fairly flexible.

The dataset combines VB.NET's language and an updated version of the recordset to provide you with

much more functionality and flexibility and it can hold many tables of data.

A DataSet is broken down to things like DataRows and DataTables, you can use these to create a dataset

without a connection to an external data source. Also, a dataset is disconnected data, that means that its notconnected to the database, all the data can be used when its offline and the dataset only needs the connection to

update the data. Let's take a closer look at it.

For this Part I'll be taking you through the different objects that make up the DataSet.

In action

We'll create a simple dataset that we can use throughout this part.

Dim ds1 As New DataSet()

Dim dtable As new DataTable("people")

With dtable.Columns.Add("FName", System.Type.GetType("System.String"))

.Add("LName", System.Type.GetType("System.String"))

.Add("UID", System.Type.GetType("System.Int32"))

End With

dtable.Columns("UID").AutoIncrement = True

ds1.Tables.Add(dtable)

dim pkey() as DataColumn = {ds1.Tables("people").Columns("UID")}

ds1.Tables("people").PrimaryKey = pkey

This is a bit complicated so I separated it into two boxes.

Box 1: Ok, we declare a new dataset and a datatable which we call - "people" as you can see in the

parameters.

The dTable.Columns collection has a function - add, that lets you add columns with the syntax - (column

name, type). After adding three columns, we define the "UID" column as AutoIncrement (these properties are

part of the DataColumn object). You can also set things like the Seed, caption, etc.

We then add the DataTable to the DataSet.


6/32


Box 2: This creates a array of DataColumn objects (in this case, only the UID column) and then tell the

dataset that this array has a datacolumn that we want to be a primary key

This is a bit complicated so you don't really need to worry that much about box 2. We'll be going over

the stuff to help you understand this next.

DataTables

A DataTable is simply one table of data (much like the recordset) and you can do things like get data, modify

data and all that kind of stuff. You can either create a DataTable like that or we can create a DataTable from a

dataset.

Dim dtable As DataTable = ds1.Tables("people")

This fills dtable with the table at ds1.Tables("people"). The table structure and data is copied into a

DataTable object. However it's disconnected but it still remains a link to the dataset, so if we add a row and then

apply the changes we can update the dataset.

Dim row as DataRow = dtable.NewRow()

row(0) = "Philip"row(1) = "Quinn"

dtable.Rows.Add(row)

dtable.AcceptChanges

This creates a DataRow. The dtable.NewRow() returns a datarow with the same syntax as the columns, so

row(0) is FName. Then we add the row to the DataTable.

When we use dtable.AcceptChanges, because we got the table from the dataset, it then updates the dataset to

include those changes.


Dim ds1 As New DataSet()

Dim dtable As new DataTable("people")

With dtable.Columns

.Add("FName", System.Type.GetType("System.String"))

.Add("LName", System.Type.GetType("System.String"))

.Add("UID", System.Type.GetType("System.Int32"))

End With

dtable.Columns("UID").AutoIncrement = Trueds1.Tables.Add(dtable)

Dim dtable2 As DataTable = ds1.Tables("people")

Dim row as DataRow = dtable2.NewRow()

row(0) = "Philip"

row(1) = "Quinn"

dtable2.Rows.Add(row)

dtable2.AcceptChanges


7/32


Response.Write(ds1.Tables("people").Rows(0)("FName").ToString)End Sub

This should print "Philip" to the screen. A quick breakdown of this is (incase you haven't been reading):

1. We create a new DataSet and a DataTable called "people"

2. Added three columns and set their properties

3. Added them to the DataSet

4. Created another DataTable. This table was a copy of the one we had just put into the dataset.

5. We created a DataRow using the syntax of the DataTable and put some data into it.

6. We then accepted the changes which then updated the dataset.

7. And printed the First column (which happens to be "FName") of the first row to the screen

There are also other methods that you may want to use with a DataTable.

We'll be covering these as we use them. So far we have seen what a DataTable is and how you can create

them and then use them to edit a dataset (with the help of a DataRow). Next we'll take a look at the DataRow

Anything Else

I've decided not to cover the DataColumn as there isn't really anything in there, we've seen it adding columns

and setting their properties, that's about it.

Dim dcol as DataColumnCollection = ds1.Tables("people").Columns

Dim dcol2 as DataColumn = dcol.Item(2)

dcol2.AutoIncrementStep = 3

The AutoIncrementStep obviously increases the step from 1 (default) to 3.

I know that this article has been a bit messy, but DataSets are a messy. Luckily, it's not too hard to see what

we're doing without much of an explanation. However you should remember that Columns and Rows are made

from Collections and like arrays the collection indexes start at 0. Remember that you must AcceptChanges (in

most cases) to have the data sent back to the dataset.


Introduction

In Part 1 we learnt about connecting, running queries and filling datasets. In Part 2 we talked about different

parts of a dataset. In Part 3 we are actually going to use a dataset.

By now you should know all about how DataSet's work and how to fill them with information. All of the codehere will be relating to the code we made in Part 1 (its at the bottom of the page). You should have a database

with some sample data and used that in the code.

DataSet Data Source

The connection between the DataSet and the data source is usually provided with a DataAdapter. Once a

DataSet has got the data it is disconnected from the data source, whatever changes you make to the data are kept

in the DataSet until you update the data source.


8/32


States:

Original This state is how the data was when it first came into the dataset. This cannot be modified, if

you try to modify it, its put into one of the other two states.

Current This state is the data when you modify it, it holds the modified rows.

Proposed This state is the proposed data to be sent to the data source. The difference between this andthe Current State is that this one is entered by using Edit Mode and imposes the rules of the data source

immediately (the Current only enforces them when it updates the dataset).

You don't really need to worry about these, what we'll be concentrating on is the the current data (with

changes).

Making some changes

We're going to do a simple example of where we take our data, change it and then update the data source.

Here is the table setup (designed for Microsoft Access)

Field Name TypeFName Text

LName TextID AutoNumber

FName LName ID

Philip Quinn 1

Joesph Payne 2

Douglas Adams 3

Michael Okuda 4

This is a pretty simple database, but we will be using it for all of our needs.

Example 1

Ok, I'll give you the code hunks, bit by bit and step you through it, then you can combine them to test it out.


This is just your standard opening the language is VB and we need those two namespaces to do the data stuff.


Source=e:\sff\site\db\test.mdb")

objConn.Open()



Dim objCmdBld as New OleDbCommandBuilder(objAdapter)


9/32



objConn.Close()

Ok, now we open the connection to the database (remember to specify your own database path when you do it).

We then open it (using the Open() method)

We declare a DataSet, a DataAdapter and a CommandBuilder. Then fill the DataSet and close the connectionas we no longer need it.

For those of you who don't pay attention -

* A DataAdapter is an interface between the database and the DataSet, allowing data to be transferred

between both (2-way communication).

* The CommandBuilder builds the SQL commands that you use to execute the SQL statements (we'll talk

about this more soon).

The data is now in the DataSet table called "users"

Dim drow as DataRow

This declares a DataRow, we're going to use this one row to add two rows to the dataset.

drow = ds.Tables("users").NewRow()

drow(0) = "Gene"

drow(1) = "Rodenberry"

ds.Tables("users").Rows.Add(drow)

drow = ds.Tables("users").NewRow()

drow(0) = "Maxwell"

drow(1) = "Stewart"

ds.Tables("users").Rows.Add(drow)

From Part 2 you should be able to gather what this means. We make the DataRow a NewRow() from the

DataTable "users", this means that it has the syntax and format of all the columns -

* drow(0) is FName

* drow(1) is LName

* We don't need to specify drow(2) because it is an AutoNumber

We then add the row to the dataset and then go about creating another one.

objConn.Open()


objConn.Close()

This calls the Update() method of the DataAdapter which then uses the ds dataset's table "users" to update the

database using the CommandBuilder's SQL Statements.

End Sub

Of course.

If you check the database you should have two new rows.


10/32


OleDbCommandBuilder

I didn't cover this earlier because it wasn't relevant until now. The CommandBuilder builds SQL Statements

for a specified DataAdapter. When dimensioning it (when will I use that word again?) you specify the name of

the DataAdapter, when you call the Update() method of the DataAdapter, it checks the CommandBuilder for theSQL statements it needs to update the database.

The alternative is to set the individual commands in the DataAdapter (or use the OleDbCommand to do it),

which is quite inflexible and I don't really see much of a point to it.

Other Stuff

There is other stuff that you can do with datasets, and you have already learnt it. I'll give you the code sample

and you can decode it -




objConn.Open()





Dim drow as DataRow

drow = ds.Tables("users").Rows(1)

drow(0) = "Joseph"

ds.Tables("users").Rows(0).AcceptChanges


objconn.Close()End Sub


Introduction

Welcome to another part in ASP & Databases, this time we are going to be looking at how to display our data

in or on something. We're going to be using the DataSet that we got in Part 3, and for you lazy people who want


11/32


to steal my code, here it is -




objConn.Open()





'add any more code that I show you here. Don't close Tags yet.

Repeater

I'll tell you now that all of these methods of viewing data is tedious and very complicated, but I'm sure that

you can work it out, your relatively smart humans.

The Repeater is a control that loops through the data and displays it on templates that you create.

* ItemTemplate. This is the template where it displays the main body of the data.

* AlternativeItemTemplate. You can have an alternating style for every second item if you want.

* HeaderTemplate. For any header you want.* SeparatorTemplate. For separating items (usually in the form of
, etc.).

* FooterTemplate. For footers!

Before I show you the code there is one thing that I should tell you about.

DataBind()

DataBind() is a method that takes all of the data on the page and binds it to the controls that you havespecified for example.

Repeater1.DataSource = ds.Tables("users").DefaultView

DataBind()

The first line sets the datasource and the second binds the data to the repeater, without binding, the data

wouldn't be shown.

Repeater cont.

Remember that this code just snaps on to the one in the introduction.

Rpt.DataSource = ds.Tables("users").DefaultView

DataBind()

End Sub


12/32


Last Name

First Name

Lets look at this in sections -

1. We first set the repeater's datasource, which is the datatable "users". DefaultView is the view of the table,

we'll look at views later (in another Part).

2. Then we bind the data to the repeater.

3. We start the repeater in the normal way to start any Web Form Control.

4. The HeaderTemplate starts a table with 2 columns, First Name and Last Name.

5. The ItemTemplate fills each column with the appropriate data, 'Container refers to the DataSource, we'lltalk about that later (in another Part).

6. Finally the FooterTemplate closes the table and we then close up the remaining open tags.

I haven't put in any kind of Alternating Item in here, but its simple enough.

That is all a Repeater can do, it presents data and that is all there is to it.

DataList

The DataList is very much like the Repeater but the DataList supports editing of data. You use the templates

like we did with the repeater, but there are two new ones :

* SelectedItemTemplate. This is the template the shows up when you select a row.


13/32


* EditItemTemplate. This is what shows up when you select a row for editing.

Here is a very simple use of it.

dl.DataSource = ds.Tables("users").DefaultView

Databind()

End Sub

Sub dl_ItemCommand(sender as object, e as DataListCommandEventArgs)

dl.SelectedIndex = e.Item.ItemIndex

dl.DataBind()

End Sub

Last Name, click for full name.

This is a rather large piece of code, but here is the explanation....

* After the databinding we put a new procedure (this will be explained soon).

* We enclose the DataList in a form tag because it needs to reload the page to select items.

* We define properties of the DataList -

o HeaderStyle-BackColor The background of the Header (instead of putting it in the table code).

o SelectedItem-BackColor The background of the Selected Item.

o RepeatLayout This is how the DataList should be laid out - either 'table' or 'flow' (flow is just nothing ie.

You define exactly how you want it.)


14/32


o RepeatDirection The direction in which to display the items (horizontal or vertical).

o DataKeyField The primary key to use, this helps when selecting and editing data.o OnItemCommand Explained Later.

* HeaderTemplate This is just a very simple Header column.

* ItemTemplate The link button is a link that serves as a submit type button (but text-only). You display the

Last Name and the CommandName of "Select" sends the command "Select" to the DataList (which thenperforms whatever it needs to do.

* SelectedItemTemplate Shows the First and Last Name.

* Then we close up everything

This is quite complicated and there are many unexplained things, here is an explanation for one of them :

Most of these controls have events that happen when you do something, like when you click on an item or

automatic ones like when an item is created. All of these events can have event handlers. In the above example

we used an event handler for the ItemCommand event. It takes in the usually parameters with one difference - its

DataListCommandEventArgs and not EventArgs, this provides you with more properties that are specialized for

use with the DataList.

dl.SelectedIndex tells the DataList what Item to put the selected template on.

e.Item.ItemIndex returns the index of the Item that was selected.

There are many more events for Data Viewers and I hope to provide them in a future part.

But that wraps up the DataList, don't be afraid to experiment with properties and ways to view data.

DataGrid

This is probably the most powerful control, it can go from simple to complex. I have already written an article

on it but there are a few things that I can show you with it that it didn't cover.

Instead of templates, the DataGrid has types of columns :

* Bound Columns These allow you to specify how a column is displayed, these are the default ones used by

the DataGrid.

* Button Columns These columns have buttons in them that can be customized.

* Edit Command Column This allows you to edit items, when clicked it replaced the items with editable

fields and several options.

* Hyperlink Column A column with a hyperlink.* Templated Column A custom column.

dg.DataSource = ds.tables("users").DefaultView

DataBind()End Sub

This is the most simple form of viewing data in a DataGrid, this creates a table with the column names

equaling the names in the actual DataTable and the information is just listed in rows, no special formatting or

anything.

dg.DataSource = ds.tables("users").DefaultView


15/32


DataBind()

End Sub

This is a bit more complicated, but not as bad as it gets.

* After a Standard Databinding session we set many properties of the DataGrid (self explanatory ones are

left out)

o GridLines If you want to specify a single way that the gridlines go, do it here.

o AutoGenerateColumns If this is true it makes the columns up for you, if its false, it relies on your

Columns that you set up. If it is true and you have defined columns then it will use both your columns and theAuto Generated ones.

* Next we start to put in columns.

* The BoundColumn for the ID Field

* A Template Column (you can see the template that we're using here (the ItemTemplate)) and we then put alabel on it.

* We close tags.

This is no where NEAR the amount of stuff you can do on DataGrids, and because I like them, look out for an

article - Advanced DataGrids.

How to capture the Double Click event in a DataGrid?

Introduction: One of the rich in-built Webserver control is the DataGrid. We can do a lot with a DataGrid. There


16/32


17/32


Case ListItemType.AlternatingItem

lblSID = CType(e.Item.FindControl("hdnStoreID"), Label)

tblStoreName = e.Item.FindControl("lblStoreName").Parent.Parent

tblStoreName.Attributes.Add("OnDblClick", "ShowDetails(" & lblSID & ");")

End Select

End Sub

How it works?

In the above code, we have two variables. An instance of TableRow, and a Label. Then we have a Select

statement, since we need to create attributes for both ItemStyles and AlternatingItemStyles. The statement,

lblSID = CType(e.Item.FindControl("hdnStoreID"), Label)

is responsible for retrieving the value in the Label control "hdnStoreID". This is for retrieving the value for each

row. The next two statements are the most important ones in this article.

tblStoreName = e.Item.FindControl("lblStoreName").Parent.Parent

One of the column is the StoreName. Each column will be inside a element and each will be inside a

element. We know this for true. If we say, e.Item.FindControl("lblStoreName").Parent, then the control

points to the . But we need to get the element. That is why we have Parent of Parent, which is nothing

but parent of each .

Now, we have a TableRow control, which points to the current row in the datagrid. Then our next job is to add

the attribute, "OnDblClick" to each row and this is done using

tblStoreName.Attributes.Add("OnDblClick", "ShowDetails(" & lblSID & ");")

We invoke a client side method called ShowDetails which will be invoked when we double click on any row in

the DataGrid.

Editing a Data Grid Control

Introduction

Modifying / Editing records in a table is as simple as that with the help of editable datagrid. In this article, we

will see, what should we need to create an editable datagrid. Let us start with the datagrid declaration.

Declaring a Datagrid Server Control


18/32


What are OnCancelCommand, OnUpdateCommand, OnDeleteCommand and OnEditCommand

Well, we have a datagrid control and we will go through the above declaration carefully. We have declared four

events in the above datagrid control such as OnCancelCommand, OnUpdateCommand, OnDeleteCommand and

OnEditCommand. You can also see that, we invoke a method for each event. For example, for the event

"OnCancel", we are invoking a server side method called "MyDataGrid_Cancel". We will look into these

methods later.

Creating the Cancel, Edit Update and Delete buttons

Just after the tag, we need to first create the buttons such as, Edit, Cancel, Update and Delete. These

buttons are very common for any database operation. To declare the Edit, Cancel and Update button, you need to

have a EditCommandColumn tag, such as

And for the button Delet, we need to declare a button and use the property commandname to specify that, the

command Delete.

What should be done, when user clicks the Edit button?

When the user clicks the button "Edit", then the corresponding items in the row should show text boxes inorder

to accept input from user. The tag, should be used to declare the control (typically textbox,

listbox, radio button, checkbox etc). We are going to declare a textbox which will be showed while the user

clicks the edit button.


19/32


So, we have a EditItemTemplate tag, which will be showed only, if the user clicks edit button/link. It is to be

noted that, we have a DataBinder for this textbox, which will be filled with the value for the corresponding row

which was selected by the user. It is this EditItemTemplate which is vital for an Editable datagrid.

The Edit Event

Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)

End Sub

This event will be invoked when user clicks the edit button/link. We have only one statement in this event. The

primary goal of this event is to change the selected row to a editable row, which is done by setting the property

EditItemIndex.

The Cancel Event

Public Sub MyDataGrid_Cancel(sender As Object, e As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = -1End Sub

When we click the edit button, all items in the corresponding row will be changed to an editable item. At this

time, the edit button will be replaced with the Cancel and Update button. These are two new buttons which will

be shown when the user clicks the edit button. When the user clicks the cancel button, we want to remove the

editable columns. This is achieved by setting the EditItemIndex as -1.

The Update Event

Public Sub MyDataGrid_Update(sender As Object, e As DataGridCommandEventArgs)

Dim strDestination As String = CType(e.Item.FindControl("txtField1"), TextBox).Text

...................

.........

...

End Sub

Well, when the user clicks the edit button, the selected row will be changed to an editable item. Then we can

enter the new values for the editable columns. The values entered is retrieved using the statement

CType(e.Item.FindControl("txtField1"), TextBox).Text

. Here, we use the FindControl method which retrieves the value for the control name passed. Then we can have

any statement inside this update event. We can invoke a stored procedure or invoke a inline query. It is all up tous.

Inserting Images to SqlServer in ASP .NET

Introduction

There will be many occassion, in which we will be urged to store images in the Database. In some applications

we may have some sensitive information which cannot be stored in a file system, since if anything is in the file

system, then it may be very easy for the users to hack the pictures/images.

In this article, we will discuss about, how we can insert images to a SqlServer 2000.

We will be learning the following aspects in this article.


20/32


1. Prerequistes for inserting an image file

2. Working with the Stream Object3. Finding the Size and Type of the image that is going to be uploaded

4. How to use the InputStream method?

Prerequistes for inserting an image file

Two primary things that we need before the upload begins are

# The property enctype of the Form tag should be set to enctype="multipart/form-data"

# We should have a which allows the user to select the necessary image file (which will be

inserted into the database)

# Also we need to Import the Namespace, System.IO to deal with the Stream object.

The above three points applies to an ASPX page. Also we need to have the following prerequistes in the

SqlServer.

# We should have a Table with atleast one of the field of type Image.# It will be better, if we have another field of type Varchar to hold the image type.

So, we have a Sql Table with the field type of Image and we have a (HTMLFile control). We

also need a Submit button, where user can click after selecting the image. In the OnClick event of the button, we

need to read the content of the image file and finally we insert the image to the table. Let us take a look at the

OnClick event of the button, which reads the image and inserts into the sql table.

Code in the OnClick event of the Submit button.

Dim intImageSize As Int64

Dim strImageType As String

Dim ImageStream As Stream

' Gets the Size of the ImageintImageSize = PersonImage.PostedFile.ContentLength

' Gets the Image Type

strImageType = PersonImage.PostedFile.ContentType

' Reads the Image

ImageStream = PersonImage.PostedFile.InputStream

Dim ImageContent(intImageSize) As Byte

Dim intStatus As Integer

intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

' Create Instance of Connection and Command Object

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim myCommand As New SqlCommand("sp_person_isp", myConnection)

' Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC

Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)


21/32


prmPersonImage.Value = ImageContent

myCommand.Parameters.Add(prmPersonImage)

Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)

prmPersonImageType.Value = strImageType

myCommand.Parameters.Add(prmPersonImageType)

Try

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

Response.Write("New person successfully added!")

Catch SQLexc As SqlException

Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())

End Try

How it works?

The Object, PersonImage is the name of the HTMLInputFile control. First we need to get the size of the image

that is going to be inserted and that is done by

intImageSize = PersonImage.PostedFile.ContentLength

. Then we retrieve the image type using the property ContenType. Then the most important thing is, we need to

get the Image Stream and that is done by

ImageStream = PersonImage.PostedFile.InputStream

. We have an array of Bytes, ImageContent, which is ready to hold the image content. The entire image is read

using the method Read of the Stream Object. The method read takes three arguments, viz;

# Target Location that the Image Content to be copied

# Starting position for the purpose of read

# Number of bytes that needs to be read

. And the Read statement is

intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

. Now, we have read the entire image content. Next we need to insert this into a sql table. We are going to use a

stored procedure which inserts the image type and the image to a sql table. If you go through the above code

listing, then you can see that we use the datatype as SqlDbType.Image. That is it. We have successfully insertedan image to SqlServer.

Retrieving Images from SqlServer in ASP .NET

Introduction

This article is a continuation of my previous article, which talks about Inserting an image to Sql Server. I would

recommend to read the above article before continuing this.


22/32


Compared to inserting an image, retreiving is very simple. The only new thing that we need to write an image is,

we need to use the method BinaryWrite of the Response Object. Also we need to set the appropriate contenttype. In this article, we will discuss about retrieving images from a Sql Server.


1. How to set the Content Type?2. How to use the method, BinaryWrite

Since we already have data in the table, Person, we will add some statements which retrieves all the rows from

the table, person. The following code retrieves all rows from the table, Person.

Code to retrieve image from sql server.

Public Sub Page_Load(sender As Object, e As EventArgs)

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim myCommand As New SqlCommand("Select * from Person", myConnection)

Try

myConnection.Open()Dim myDataReader as SqlDataReader

myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Do While (myDataReader.Read())

Response.ContentType = myDataReader.Item("PersonImageType")

Response.BinaryWrite(myDataReader.Item("PersonImage"))

Loop


Response.Write("Person info successfully retrieved!")


Response.Write("Read Failed : " & SQLexc.ToString())End Try

End Sub

How it works?

The above example is very very simple. All we are doing is executing a sql statement and looping through all the

records. We are displaying only the Image from the table, Person. Before dispalying the image, we first set thecontentType. Then we write the image to' the browser using the method, BinaryWrite

Test this Script

Try ... Catch ... Finally in ASP.NET

Introduction

Error handling in Classic ASP was not the best. We were having only limited options available for error handling

in Classic ASP such as, "On Error Resume Next". In ASP 3.0 we saw the new ASP object called Error Object.

But we were not able to handle all exception/errors efficiently. Now in ASP.NET we have a new error handling

mechanism which was already their in other languages such as C, C++ and JAVA. We can also call the try...catch

mechanism as "Exception Handling"

What is Try...Catch....Finally


23/32


This is a new error handling mechanism in VB.NET, so as in ASP.NET. Well we have three blocks of code, wereeach block has it own functionality. The Try...Catch...Finally block of code surrounds the code where an

exception might occur. The simple Try statement comes before the block of code, the Catch block of code is

where we specify what type of error to look for, and the Finally block of code is always executed and contains

cleanup routines for exception situations. Since the catch block is specific to the type of error we want to catch,we will often use multiple Catch blocks in our Try...Catch...Finally structure.

A simple Database operation

Dim mySqlConnection as New SqlConnection (ConnectionString)

Dim mySqlCommand as SqlCommand

Dim strSql as String

strSql = "insert into yourtable (f1, f2) values ('f1', 'f2')"

mySqlCommand = new SqlCommand(strSql, mySqlConnection)

Try

mySqlConnection.Open()mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

Message.text = "New Forward information added"

Catch SQLexc as sqlexception

Message.text = Message.text + sqlexc.tostring()

Catch exc as exception

if Instr(1, exc.tostring, "duplicate key") > 0 thenMessage.text = Message.text + "Cannot insert duplicate values."

else

Message.text = Message.text + exc.tostring()

end if

Finally

mySqlConnection.Close()End Try

What does the above example exactly do?

Well, in the above example we were trying to insert some values to a database table. The possible chances whileperforming a database operation are invalid connection string, database server too busy resulting in connection

time out, database server not currently running etc etc. We should anticipate all these errors while performing a

database operation. So, we have a Try block, which contains the statements such as opening the connection and

executing the operation. Basically, we have two major statements inside the try block which may result in an

exception/error.

As I said, any exception can occur during a database operation. Catching all these exception is now very easy

with the Catch block. All we need is to have a Catch block. We can have any number of Catch blocks. Each

Catch block may have a different error/exception trapping mechanism. In the above example, we have two catch


24/32


blocks, one which captures a general exception and the other one which traps the SqlException.

When all the statements inside the catch blocks are executed, the finally block comes into the picture. As I said

earlier, finally block contains cleanup routines for exception situations.

Exit Try statement

We can also have the Exit Try statement inside any of the try...catch block. The objective of this statement is to

break out of the Try or Catch block. Once the Exit Try statement is executed, the control goes to the Finally

block. So, Exit Try statement can be best used were we need to execute the cleanup routines.

How about nested Try statments?

We can have nested Try and Catch blocks. Can you imagine, when we should use nested try statements. Well,

errors can occur within the Catch portion of the Try structures, and cause further exception to occur. The ability

to nest try structures is available so that we can use a second Try structure to cover exceptions.

Paging in DataList

Introduction

In my previous article, we discussed how to Sort data in a Datalist. Today, we will learn how we can add thepaging feature for the datalist. It should be noted that, like DataGrid, DataList does not support inbuilt paging

mechanism. The essence of this article is how we make use of the object SqlDataAdapter. This object has a

method called, Fill which is used to add or refresh the rows in a DataSet. Actually the method Fill is overloaded.

We will be mainly concentrating the one which takes four arguments. The four arguments are DataSet,

startRecord, maxRecords and the TableName. Second and third arguments are integer. Where as the TableName

is the table name. So, if we say objDV.Fill(objDS, 0, 5, "sales") The dataset will be filled with 5 records and the

starting position will be from the first record. First, we are bringing the entire records from the table sales, and

then we filter those with the help of startRecord and maxRecords.

For our example, we will consider the table, Sales from the pubs database (SQL Server 2000).Things that we will be learning in this article.

1. How to populate a DataList?

2. How to build the User Interface for Paging in a DataList?

3. How to make user of the Fill method of SqlDataAdapter?

4. An alternate solution for the Fill method!

Populating the DataList

For our example, we will take the table SALES in the PUBS Database. Since stored procedures are very much

better than inline query, we use a stored procedure called, sp_das_sales_sel, which contains a single SQL

statement. The SQL statement would be Select * from pubs.dbo.sales. And finally, we need to bind the DataViewto the DataList web server control.

How to build the User Interface for Paging in a DataList?

Apart from the DataList web server control, we will provide user with four hyperlinks for navigation. When the

user clicks on any othe four navigation links, we will invoke a server side method which will pulls out the proper

records. We will also show the total number of records, total pages and the current page number. < p>

Code for Navigation Links.


25/32



26/32


Private Sub DataBind()Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim objDA As New SqlDataAdapter("exec sp_das_sales_sel", objConn)

Dim objDS As New DataSet()

If Not Page.IsPostBack() Then

objDA.Fill(objDS)

intRecordCount.Text = CStr(objDS.Tables(0).Rows.Count)

objDS = Nothing

objDS = New DataSet()

End If

objDA.Fill (objDS, Cint(intCurrIndex.Text), CInt(intPageSize.Text), "Sales")

dList.DataSource = objDS.Tables(0).DefaultView

dList.DataBind()

objConn.Close()PrintStatus()

End Sub

How it works?

In the ShowFirst method, we are setting the value of intCurrIndex.Text to be zero. intCurrIndex is a hidden label

control which keeps track of the current record number. We also do have two more hidden label controls. They

are intPageSize and intRecordCount.

In the method, DataBind you can see that, we get the total record count. We are storing this count in the hidden

label web server control called, intRecordCount. Then we use the Fill method to retrieve the current page.

An alternate solution for the Fill method!

The major disadvantage of our logic is that, if we have 1000 records in the table, we are bringing all those to our

ASPX pages. All we need is the records for our current page. This can be achived by modifying our stored

procedure. We should have an identity column in our table. Then, we should pass the starting position and the

number of records to be retrieved to the stored procedure. By this way, we will just be bringing in the needed

records, which will decrease the network traffic and throughput time.

Sample output of our scenario

Retrieving Images from SqlServer and displaying in a DataGrid - ASP .NET

Introduction

Before reading this article, I assume that the reader has read my two previous articles which talks about Inserting

Images to Sql Server in ASP .NET and Retreiving Images from Sql Server in ASP .NET.

In this article, we will discuss about populating a datagrid with images. Of course, the images will be coming

from the database.


1. Formatting or populating a URL dynamically

2. How to read images from a sql server


27/32


3. How to display the images in a DataGrid which is retrievied from a SqlServer

We will have two ASPX pages, one for displaying the datagrid and another one for pulling the images from sql

server datbase. The one which displays the datagrid just contains the TemplateColumns, and ItemTemplates. The

DataGrid will have many columns. We will just concentrate on the column which displays the image.

Code to show the image from sql server (DataGrid part).

And the method FormatURL (A Server side Function) is as follows.

Function FormatURL(strArgument) as String

Return ("readrealimage.aspx?id=" & strArgument)End Function

And the method FormatURL (A Server side Function) is as follows.

Function FormatURL(strArgument) as String

Return ("readrealimage.aspx?id=" & strArgument)

End Function

How it works?

In the above code, we have an ItemTemplate column. And we have a


28/32


Try

myConnection.Open()Dim myDataReader as SqlDataReader

myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Do While (myDataReader.Read())

Response.ContentType = myDataReader.Item("PersonImageType")Response.BinaryWrite(myDataReader.Item("PersonImage"))

Loop



End Try

End Sub

How it works?

In the Page_Load event, we first retrieve the querystring (ID of the Person). Then we execute a Sql statementwhich returns the imagetype and the imagecontent from the database. Retrieving the image and writing to the

browser is same as we discussed in Retreiving Images from Sql Server in ASP .NET.Test this Script

Advantages and Disadvantages

The greatest advantage is that, since we store the images in database, more security is their. And the greatest

disadvantage is that, we are making a database call for each image. So, if we have thousands of rows then, this

process of retrieving image every time will result in low response time.

Sorting in DataList

IntroductionWe all know that, the DataList web server control does not support in-built sort option. Still we can provide

users with the sort option for all columns in a DataList. The logic is very simple. The DataView object has a

property called Sort. We are going to make use of this property to sort the rows in a DataList. For our example,

we will consider the table, stores from the database (SQL Server 2000), pubs.

Aspects that we will be learn in this article.

1. How to populate a DataList?

2. How to build the User Interface for Sorting in a DataList?3. How to make user of the Sort property of DataView?

4. An alternate solution for the Sort property!

Populating the DataList

For our example, we will take the table STORES in the PUBS Database. Since stored procedures are very much

better than inline query, we use a stored procedure called, sp_stores_sel, which contains a single SQL statement.

The SQL statement would be Select * from stores. And finally, we need to bind the DataView to the DataList.

How to build the User Interface for Sorting in a DataList?

We are going to provide hyperlinks for all column headings. So, user can click any column they like. The

column clicked will be sorted. As simple as that. When user clicks on any column, we will invoke a Server Side

method. The server side method will bind the datalist and will also sort the datalist. We will also provide the user


29/32


with a DropDownList, in which, they can select the type of sort they need, typically Ascending or Descending.

Let us take a look at the Header template to see how we can provide hyperlinks to column headings. < p>Code for HeaderTemplate of the DataList.

Store

ID

Store

Name

Address

City

State

How it works?

Important Note: The name of the file that I have used in the href property is "datalistsort.aspx". You should

replace this with your aspx filename, unless you keep the filename as myself.

We create a hyperlink for each column names. The target (href) of the hyperlink is set to name of the aspx page

itself. Then, we set the property, OnServerClick. For the first column, StoreID, we invoke a server side method

called, SortStoreID. This method will populate the DataList and will also sort the rows based on the SortID. The

logic is the same for all other columns. Let us take a look at the ServerSide method, SortStoreID.SortStoreID Method.

Public Sub SortStoreID(ByVal s As Object, ByVal e As EventArgs)

SortDataList("stor_id " & cboSortType.SelectedItem.Value)

lblStatus.Text = "Currently Sorted on StoreID: " & cboSortType.SelectedItem.Text

End Sub

Private Sub SortDataList(ByVal strSort As String)

Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))Dim objCmd As New SqlDataAdapter("exec sp_stores_sel", objConn)

Dim objDS As New DataSet()

Dim objDT As New DataTable()

Dim objDV As New DataView()

objCmd.Fill(objDS)

objDT = objDS.Tables(0)

objDV = objDS.Tables(0).DefaultView

objDV.Sort = strSort

dList.DataSource = objDV

dList.DataBind()


30/32


objConn.Close()

End Sub

How it works?

Inside the method, SortStoreID we invoke another private method, SortDataList which accepts a string as theargument. This argument is nothing but the sort criteria for the DataList. The method, SortDataList is the heart

for the sorting. Apart from using the DataSet, we make use of DataTable and DataView. DataView has a property

called, sort. This property takes a sort expression as the input. Sort Expression is made up of the field name and

the sort type. Sort type can be either ASC or DESC, which stands for ascending and descending respectively.

Merging two Datasets into a single Datagrid

Introduction

Merge is one of the method of Dataset. The merge feature is basically used in applications where the concept of

Master and Transaction table exists. In this article we will see, how can we display two datasets in a singledatagrid. For the merge to happen, we need to have the following pre-conditions.

Pre-conditions for displaying two datasets in a single datagrid.

1) All the columns specified in the datagrid must be present in both datasets.

2) The data type of all columns in the datasets must be the same.3) The column names should match.

A Merge walthrough.

Assume that we have two tables with the following structure:

Table1 Table2

Field1 int Field1 int

Field2 varchar(10) Field2 varchar(10)Field3 varchar(20) Field3 varchar(20)

You may be thinking how in the world we will have two table structures with the same structure and same field

names. Yes, in batch processing, we will have two tables--master and transaction. Both of these tables will have

the same number of columns, same data types, and same field names.

Now, we need a datagrid to display records from the above tables. We assume that we have a datagrid that

contains the definition for all columns. If you wish, you can see the example now: Click here to see an aspx page

that uses a datagrid. The data grid that we are talking about is the same as the datagrid that is in the above sample

(editdatagrid.html).

We are mainly going to see the BindGrid method that binds the datasets with the datagrid. We will see how we

can bind two datasets with a single datagrid.

The BindGrid method.

Sub BindGrid()

Dim myConnection as New SqlConnection (strConn)

Dim DS1 As DataSet

Dim DS1 As DataSet


31/32


Dim MyCommand As SqlDataAdapter

MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)

DS1 = new DataSet()

MyCommand.Fill(DS1, "Table1")


DS2 = new DataSet()

MyCommand.Fill(DS2, "Table2")

'This code won't work--the merge will not take place with the above code.

ds1.merge(ds2)

MyDataGrid.DataSource=DS1.tables(0).DefaultView

MyDataGrid.DataBind()

End Sub

The above code will not work. Can you guess the reason? For the merge to take place between the datasets, apart

from the data type and column name, the table name should also be the same.

So what should we do? In order for the merge to take place, we should name both the tables with same name. So

we have to modify the MyCommand.Fill method for both DS1 and DS2 as follows:

The BindGrid method.

Sub BindGrid()

Dim myConnection as New SqlConnection (strConn)

Dim DS1 As DataSetDim DS1 As DataSet

Dim MyCommand As SqlDataAdapter


DS1 = new DataSet()

MyCommand.Fill(DS1, "MyTable")

MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)DS2 = new DataSet()

MyCommand.Fill(DS2, "MyTable")

'Now this code works because the table name for both datasets are the same.'Also the data type and column name for both tables are the same.

ds1.merge(ds2)

MyDataGrid.DataSource=DS1.tables(0).DefaultView

MyDataGrid.DataBind()

End Sub


32/32


What, if the schema of two tables are not the same?

In this example we saw that the table structure of both tables (table1 and table2) are the same. If the datatype of

the columns in table1 and table2 are not the same, then what will happen? It is obvious that the merge will not

take place. Even the compiler will give an error, such as:

.destination and .destination have conflicting properties: DataType property mismatch.

How can we create an editable Datagrid with two datasets?

Well, we very well can have an editable datagrid with two datasets sharing a same datagrid. The only necessity

for this is that we should have some special values in at least one of the fields in each table which depicts that

this data belongs to table1. To be more clear, in our first table, table1, the field3 can be used to store information

that belongs to its own table. E.g.: for table1, the field3 can contain a value called "master" which tells that this

data belongs to the table, master. So the records in the table1 will be as follows:

Field1 Field2 Field3

1 test1 master

2 test2 master3 test3 master

We will also have similar records in table2 except for field3, where the value would be "transaction."

We need to have some hidden columns which store the values of field3, so that in the datagrid update method,

we can know which table that we need to update by retrieving the value of field3. Once we know the value of

field3, we can easily invoke appropriate update statements or stored procedures to update the table.

Summary

Thus, we have gone through how to merge two datasets into a single datagrid. We can also merge between two

datatables or even datarows. To know more about this, read the first link given in the links section.

document of ado.net

Documents