Professional ASP .Net page 4

· Loading Templates Dynamically At Runtime

You can load a template at runtime rather then hard-coding them. You define the specified template in a file with extension .ascx. It that file you omit the template element (i.e. <ItemTemplate>). Now you can load the template as:

MyDataControl.ItemTemplate = Page.LoadTemplate("Tmp.ascx")

· Multiple Column Layouts With A DataList

MyDataList.RepeatColumns = 3

MyDataList.RepeatDirection = RepeatDirection.Horizontal

· Custom Column Display In DataGrid

To enable customizing the columns you have to set AutoGenerateColumns property to false and have to use <Columns> element and BoundColumn contols.

<ASP:DataGrid id="MyDataGrid" AutoGenerateColumns="False">

<ASP:BoundColumn DataField="ISBN" />

</Columns>

</ASP:DataGrid>

Now only one column from the data source would be visible in the data grid.

· Adding Unbound Columns To DataGrid

You can also add extra columns to DataGrid that are not part of the original dataset using an ASP:TemplateColumn control. For example the following example adds a column heading Information and each row of the column contains a ASP:Button.

<ASP:DataGrid . . . >

. . .

<ASP:TemplateColumn HeaderText="Information">

<ASP:Button id="MyBtn" runat="Server" CommandName="Info" OnItemCommand="ShowInfo"/>

</ItemTemplate>

</ASP:TemplateColumn>

</Columns>

</ASP:DataGrid>

We have set the button CommandName property so that we can identify which button was clicked. When any one button in the rows is pressed the ItemCommand event is raised and handle by ShowInfo handler on the server.

Sub ShowInfo(ObjSender As Object, ObjArgs As

DataGridCommandEventArgs)

If ObjArgs.CommandSource.CommandName = "Info" Then

. . .

'Now you can access the value of button row

Dim strISBN As String =

ObjArgs.Item.Cell(1).Text

Endif

End Sub

· The DataBinding event of the controls occurs after the values for the column have been determined but before they are output to the client. The handler for this event is called for each row in the data source at the time of binding. You can set a event handler in OnItemDataBound property of the control to handle the DataBinding event. You can identify which row (it may be header, item or footer row) is being bind by the second parameter (DataListItemArgs) of the handler as:

Dim ObjType As ListItemType = CType(ObjArgs.Item.ItemType,

ListItemTypes)

To access the values in the row you can do:

Dim ObjRowVals As DataRowView =

CType(ObjArgs.Item.DataItem, DataRowView)

Dim StrTitle As String = ObjRowVals("Title")

To modify the values those are outputted to client use the FindControl method of the row to get a reference to the control with as ID value of TitleLabel. This is the control that was bound to the title column. Once you get the reference of the control you can modify its value.

Dim ObjLabel As Label = CType(ObjArgs.Item.FindControl

("TitleLabel"), Label)

ObjLabel += "Some text append to the existing value"

· Sorting And Filtering The Row In A DataGrid

When AllowSorting property of the grid is set to true, each column heading automatically becomes a hyperlink. When these are clicked, an OnSortCommand event is fired on the server. You can attach an event handler with it. For example:

Sub SortRow(ObjSender As Object, ObjArgs As

DataGridSortCommandEventArgs)

' Get the name of the column heading that was clicked

Dim SortOrder As String =

ObjArgs.SortExpression.ToString()

' Now you can sort the data source based on the sort ' order and rebind the grid.

End Sub

· Automatic Paging In A DataGrid

To turn on the automatic paging feature, you simply need to set the AllowPaging property of the DataGrid to true, and specify the name of an event handler that will run when the OnPageIndexChanged event occurs. Whenever user clicks the paging controls Page_Load event also fires. You can set the number of rows to be displayed per page by PageSize property on the DataGrid. You can set the paging control mode as:

MyDataGrid.PagerStyle.Mode = PagerMode.NumericPages/NextPrev

OnPageIndexChanged event handler:

Sub ChangeGridPage(ObjSender As Object, ObjArgs As

DataGridPageChangedEventArgs)

MyDataGrid.CurrentPageIndex = ObjArgs.NewPageIndex

' Rebind the DataGrid

End Sub

You can also specify custom paging controls by setting AllowCustomPaging property to true. While you can also responding to the OnPageIndexChanged event as same ways. When you are using the automatic paging, you cannot use a DataReader object to bind the grid. However in case of custom paging you can use this.

· Editing Data With A DataGrid Control

When defining the DataGrid with edit feature you have to specify the event handlers for the OnEditCommand, OnUpdateCommand and OnCancelCommand. You have also make the AutoGenerateColumns property to false as you define the columns your own. You can also specify which control to be used at the time of editing (i.e. TextBox or a DropDownList), to do this use a <ItemTemplate> element to be used to display the column values in a normal mode, and an <EditItemTemplate> element that defines the control to be used in edit mode.

<ASP:DataGrid Id="MyGrid" runat="server"

OnEditCommand="DoItemEdit"

OnUpdateCommand="DoItemUpdate"

OnCancelCommand="DoCancelCommand"

AutoGenerateColumns="False">

<ASP:BoundColumn DataField="ISBN" ReadOnly="True"/>

<ASP:TemplateColumn>

<ASP:Label Text='<%# Container.DataItem("Title")

%>' runat="server" />

</ItemTemplate>

<ASP:TextBox id="TxtTitle" Text = '<%#

Container.DataItem("Title") %>' runat="server"/>

</EditItemTemplate>

</ASP:TemplateColumn>

<ASP:BoundColumn DataField="PublicationDate">

<ASP:EditCommandColumn EditText="Edit" UpdateText="Update"

CancelText="Cancel"/>

</Columns>

</ASP:DataGrid>

Event Handler: In case of OnEditCommand event is fired (when user clicks the edit link of a row), you have to handle the event as following:

Sub DoItemEvent(ObjSource As Object, ObjArgs As

DataGridCommandEventArgs)

' Set the EditItemIndex property of the grid to this

' item's index

MyDataGrid.EditItemIndex = ObjArgs.Item.ItemIndex

' Rebind the DataGrid

End Sub

The default value of EditItemIndex property is –1, which indicates that none of the rows is in edit mode. So to cancel the editing you have to set this value to back –1 as:

Sub DoItemCancel (ObjSource As Object, ObjArgs As

DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = -1

' Rebind the DataGrid

End Sub

To handle OnUpdateCommand event, you have to get the values that are edited by the user in the particular row. To do this there is two techniques are available. The first technique is works for the custom columns that are created with the templates. In this technique we first obtain a reference of control that is hosted by the column (in this case it is a TextBox). After that we can get the value it contains. The second technique is works only for the column that are normal BoundColumn or auto-generated column. It does not work for the custom created column. In this technique we access the cell collection for the item contained in the DataGridCommandEventArgs. Then we use the control collection of the particular cell (in this case the third cell of the row) to get a reference of the TextBox it contains.

Sub DoItemUpdate (ObjSource As Object, ObjArgs As

DataGridCommandEventArgs)

Dim TxtValue1, TxtValue2 As TextBox

' First Method

TxtValue1 = CType(ObjArgs.Item.FindControl

("TxtTitle"), TextBox)

'Second Method

TxtValue2 = ObjArgs.Item.Cells(2).Controls(0)

End Sub

· Selecting And Editing Data With DataList Control

You can edit or update a row within a DataList control. Suppose you have a DataList on the page. At it's normal mode each row of the list contains an "Info" button. If any one of the buttons is clicked the row goes into selected mode and contains the more information and an Edit button. When this Edit button is clicked the rows goes into edit mode and contains editing TextBoxes and three more buttons as Update, Delete and Cancel. On clicking one of those, different events are fired and handle by suitable event handlers. Definition of the DataList control is as follows:

<ASP:DataList id="MyDataList" runat="server" />

OnItemCommand = "DoItemSelect"

OnEditCommand = "DoItemEdit"

OnUpdateCommand = "DoItemUpdate"

OnDeleteCommand = "DoItemDelete"

OnCancelCommand = "DoItemCancel">

' Normal mode

<ASP:Button CommandName="Select" Text="Info"

runat="server"/>

<%# Container.DataItem("Title") %>

</ItemTemplate>

' Selection mode

Title: <b><%# Container.DataItem("Title") %> </b>

<ASP:Button CommandName="Edit" runat="server"/>

</SelectedItemTemplate>

' Edit mode

<ASP:Button CommandName="Update" runat="server"/>

<ASP:Button CommandName="Delete" runat="server"/>

<ASP:Button CommandName="Cancel" runat="server"/>

Title:

<ASP:TextBox id="TxtTitle" Text='<%#

Container.DataItem("Title") %>' runat="server" />

</EditItemTemplate>

You can also put the row directly into the edit mode without going into selected mode.

Selecting A Row: To select a row when user clicks on "Info" button ItemCommand event handler DoItemSelect is called. However one thing to note here is that we also defined other buttons as update, delete and cancel. So when events for these buttons are fired, ItemCommand event handler is also called as a part of event execution mechanism. So you must check which control was used to raise the event in the ItemCommand event handler (the DoItemEvent handler).

Sub DoItemSelect (ObjSource As Object, ObjArgs As

DataListCommandEventArgs)

If ObjArgs.CommandName = "Select" Then

MyDataList.SelectedIndex=ObjArgs.Item.ItemIndex

' Rebind the list

End If

End Sub

Editing A Row: When your row is in selected mode and the Edit button of the row is pressed, it will raise the EditCommand event (as well as ItemCommand event).

Sub DoItemEdit (ObjSource As Object, ObjArgs As

DataListCommandEventArgs)

MyDataList.SelectIndex = -1

MyDataList.EditItemIndex = ObjArgs.Item.ItemIndex

' Rebind the list

End Sub

Updating A Row: When the row is in edit mode and the Update button is clicked the event is handled as the update event of the DataGrid was handled previously. For getting the edited values use the reference of the TextBox hosted in the row.

Sub DoItemUpdate (ObjSource As Object, ObjArgs As

DataListCommandEventArgs)

Dim ObjTxtTitle As TextBox

ObjTxtTitle = CType(ObjArgs.Item.FindControl

("Title"), TextBox)

MyDataList.EditItemIndex = -1

' Rebind the list

End Sub

Deleting A Row:

Sub DoItemDelete (ObjSource As Object, ObjArgs As

DataListCommandEventArgs)

Dim DelKey As Integer = ObjArgs.Item.ItemIndex

' Perform the operation to delete the row on the ' index

MyDataList.EditItemIndex = -1

' Rebind the list

End Sub

Canceling Edit Mode: Just set the EditItemIndex property to –1 in the event handler and rebind the list.

· Web services expose their interface and data using an implementation of XML called the SOAP.

· Commonly Used Methods Of The Command Class

All the specified methods execute the command defined in the CommandText property against the connection defined in the connection property of the command class.

1) ExecuteNonQuery: Executes a query that does not return any rows, only returns an Integer indicating the number of rows affected.

2) ExecuteReader: Executes a query that return an instance of a DataReader object.

3) ExecuteScalar: Returns only a single value (effectively the first column of the first row of the rowset, any other returned columns and rows are discarded.

· The DataAdapter Class

It provides the pipeline and logic that fetches the data from the data store and populates the table in the DataSet, or pushes the changes in the DataSet back into the data store. Commonly used properties of DataAdapter are as follows:

1) Fill: Executes the SelectCommand to fill the DataSet from the data store.

2) FillSchema: Uses the SelectCommand to extract just the schema for a table from the data source, and create an empty table in the DataSet with all the corresponding constraints.

3) Update: Calls the respective InsertCommand, UpdateCommand and DeleteCommand for each inserted, updated and deleted row in the DataSet to update the original data store.

· Every table in the DataSet contains a DefaultView and this can be used to create DataView based on the table.

· Commonly Used Method Of DataSet For Updates

1) AcceptChanges: Commits all the changes within the DataSet since it was loaded, or since the last time AcceptChanges was executed.

2) GetChanges: Returns a DataSet containing the changes.

3) HasChanges: Indicates if any changes have been made.

4) RejectChanges: Abandons all the changes and returns the DataSet into its original state.

· The DataTable class also exposes the Clear, AcceptChanges and RejectChanges methods similar to the DataSet, but operates only on the specified DataTable. Another methods are:

1) NewRow: Gives a new instance of a row (Blank). You can fill the values in that instance and add it to the table.

2) Select: Returns the set of rows that match a filter.

· DataRowCollection Class

This is a collection of all rows in DataTable, as referenced by the Rows property of the table. Common methods:

1) Add: Add a new row created with the NewRow method of the DataTable.

2) Remove: Permanently removes the specified DataRow.

3) RemoveAt: Permanently removes a row specified by its index position from the table.

4) Find: Takes an array of primary key values and returns the matching row as a DataRow instance.

· Commonly Used Methods Of The DataRow Class

1) Delete: Marks the row as being deleted, though it is not removed from the table until the Update or AcceptChanges method is executed.

2) GetChildRows: Returns a collection of rows from another table that is related to this row as child rows.

· Commonly Used Methods Of The DataReader Class

1) Read: Advanced the current row pointer to the next row. Returns false when there are no more rows to read.

2) GetValue: Returns one value from the current row in its native format (as the native data type in the data source) by specifying the integer column index.

3) GetValues: Same as GetValue, but returns an array of one or more values from the current row.

4) NextResult: Moves the pointer to the next RowSet, if the DataReader contains more then one.

5) Close: Closed the DataReader

· Commonly Used Properties Of The DataReader Class

1) FieldCount: Returns the number of columns it contains.

2) HasRow: Returns true if the DataReader contains Rows.

3) IsClosed: Returns true if the DataReader has been closed.

· A DataReader Example

Dim ObjConnection As New SQLConnection("ConnectionString")

ObjConnection.Open()

Dim ObjCommand As New SQLCommand("Select_Query",

ObjConnection)

Dim ObjDataReader As SQLDataReder

ObjDataReader = ObjCommand.ExecuteReader()

Do While ObjDataReader.Read()

MsgBox (ObjDataReader("Title")

Loop

ObjDataReader.Close()

ObjConnection.Close()

You can force the connection to be closed automatically as soon as we call the Close method of the DataReader:

ObjDataReader = ObjCommand.ExecuteReader

(CommandBehavior.CloseConnection)

· A DataSet is a disconnected read/write container for the holding one or more tables of data, and the relationships between these tables.

· A DataSet Example

Dim ObjConnection As New SQLConnection("ConnectionString")

Dim ObjDataAdapter As New SQLDataAdapter ("SelectQuery",

ObjConnection)

Dim ObjDataSet As New DataSet()

ObjDataAdapter.Fill(ObjDataSet, "Books")

This technique still creates and uses a command object. When you create a DataAdapter, a suitable command object instance is created automatically behind the scenes, and assigned to the SelectCommand property of your DataAdapter. You can do this yourself also.

The fill method used here does not automatically set the primary keys, unique constraints and other database specific fact in the table created in the DataSet. To do this you can call the FillSchema method first (before you call Fill) to copy these setting from the data source into the table.

You can create a DataView object for a specific table as:

Dim ObjDataView As New DataView

(ObjDataSet.Tables("Books"))

Dim ObjDataView As DataView = ObjDataSet.Tables

("Books").DefaultView

· Adding Relationships To The DataSet

Define a variable to hold a DataRelation object and create a new DataRelation by specifying the name you want for the relation and the name of primary and foreign key. Then add the new relation to the DataSet object's Relation collection. As the relations are added to the DataSet, an integrity check is carried out automatically, and if some flaws are found, an error is raised and the relation is not added to the DataSet.

Dim ObjRelation As DataRelation

ObjRelation = New DataRelation ("BookAuthors",

ObjDataSet.Tables("Books").Columns("ISBN"),

ObjDataSet.Tables("Author").Columns("ISBN"))

ObjDataSet.Relation.Add(ObjRelation)

You can bind a DataGrid with the DataSet object's Relations collection as:

MyDataGrid.DataSource = ObjDataSet.Relations

MyDataGrid.DataBind()

· XML is the standard persistence format for data within the .Net data access classes.

· The W3C standard for the XML is referred to as the XML Document Object Model (DOM), and is supported under .Net by the XmlDocument and XmlDataDocument objects.

· Working With Relationships Between Tables

The technique is used to navigate from a parent table (with primary key) to child table (with foreign key) uses the GetChildRows method that is exposed by the DataRow Object. All you have to do is iterate through the parent table (in this case "Books") one row at a time, calling the GetChildRows method on each DataRow object. This example uses the relationship defined in the previous examples. As a matter of fact you can also traverse in reverse direction, i.e. from child table to parent table using a method called GetParentRow.

Dim StrResult As String

Dim ObjTable As DataTable = ObjDataSet.Tables("Books")

Dim ObjAuthorRelation As DataRelation =

Objtable.ChildRelations("BookAuthors")

Dim ObjRow, ObjChildRow As DataRow

For Each ObjRow In ObjTable.Rows

StrResult += ObjRow("Title")

' Get a collection of all matching Authors table rows ' for this row

Dim ColChildRows() As DataRow =

ObjRow.GetChildRows(ObjAuthorRelation)

For Each ObjChildRow In ColChildRows

StrResult += ObjChildRow("FirstName")

· Using Implicit Inline Parameters With A Stored Procedure

This option is works fine with the MS-SQL Server. But the syntax might not work in all database application, because the in-line syntax for the stored procedures is not always supported by other database system. SQL Server treats this command text as a SQL query (it automatically locates the stored procedure name within the string and parses out the parameter values). Therefore, you cannot set the CommandText property of the command object to CommandType.StoredProcedure. If you do that, you will get en error message.

Dim StrCommandText As String = "SP_Name 'John', 12"

Now you can set this string in command object as you set a normal query string.

· Using Explicit Parameter With A Stored Procedure

The command object exposes a Parameters collection that can contain multiple Parameter objects. You can create a Parameter object by two ways. In the first one you create a Parameter object with New operator and passing the values in it's constructor. Then you pass it to the Add method of the Parameter collection. This technique required to sets all the properties at the time of creation of the Parameter object, however there may be the case when you do not need to set all the values of the properties. The second technique creates a new parameter object through the Add method of the parameter collection and returns a reference of that newly created parameter object. You can use that parameter object to set the properties you required.

Dim ObjParam As SQLParamter

ObjParam = ObjCommand.Parameters.Add ("ISBN")

ObjParam.Direction = ParameterDirection.Input

· In case of OleDb data access classes, parameters are passed by the position, rather by the name. So you can omit @ symbol to prefix the parameter name. But in case of MS-SQL Server classes parameters are passed by the name, so all parameter names must be prefixed by @ symbol.

· To access the value of output parameter after query execution:

StrTitle = ObjCommand.Parameters("Title").Value.ToString()

· Creating And Filling A New DataTable Through Code

Dim ObjTable As New DataTable("NewTable")

ObjTable.Columns.Add("ISBN", System.Type.GetType

("System.String"))

Dim ObjDataRow As DataRow

ObjDataRow = ObjTable.NewRow()

ObjDataRow("ISBN") = "124635200"

ObjTable.Rows.Add(ObjDataRow)

A physical DataTable object cannot exist alone, and must be part of a DataSet. In the example above, however you have created a standalone DataTable, but a DataSet was created automatically behind the scenes and your new table is part of that DataSet. However you can create a DataSet object explicitly and then add you table into its tables collection.

Dim ObjDataSet As New DataSet()

ObjTable = ObjDataSet.Tables.Add("NewTable")

. . .

· Defining Constraints And Default Values In The DataSet

' Add an identity column named kBookKey

Dim ObjColumn As DataColumn

ObjColumn = ObjTable.Columns.Add("kBookKey",

System.Type.GetType("System.Int32"))

ObjColumn.AutoIncrement = True

ObjColumn.AutoIncrementSeed = 1000

ObjColumn.AutoIncrementStep = 10

' Add a unique string column with max length 10 chars

ObjColumn = ObjTable.Columns.Add("ISBN",

System.Type.GetType("System.String"))

ObjColumn.AllowDBNull = False

ObjColumn.Unique = True

ObjColumn.MaxLength = 10

ObjColumn.DefaultValue = "Xyz"

' Add a calculated column containing as expression

ObjColumn = ObjTable.Columns.Add("Quantity",

System.Type.GetType("System.Int32"))

ObjColumn.Expression = "[StockQty] – [OrderedQty]"

Now the value of the Quantity column is automatically generated as subtracting column OrderedQty from the column StockQty.

· Specifying Primary And Foreign Keys

To do this you have to create the primary and foreign key constraint as objects and add them to the Constraints collection of the table object. To apply this technique you have to first create a UniqueConstraint object and specify that this object is a primary key. As a primary key can be a combination of more than one column, so you have to create an array of all those columns participating in primary key formation and then set this array as the table's primary key.

Dim ObjParentTable As DataTable =

ObjDataSet.Tables("Book")

Dim ObjChildTable As DataTable =

ObjDataSet.Tables("Author")

Dim ObjParentColumn As DataColumn =

ObjParentTable.Columns("ISBN")

Dim ObjChildColumn As DataColumn =

ObjChildTable.Columns("ISBN")

Dim ObjUnique As New UniqueConstraint ("Unique_ISBN",

ObjParentColumn)

ObjParentTable.Constraints.Add(ObjUnique)

' Create an array of columns containing this column only

Dim ObjColumnArraY(0) As DataColumn

ObjColumnArray(0) = ObjParentColumn

ObjParentTable.PrimaryKey = ObjColumnArray

' Specifying the foreign key constraint on Authors table

Dim ObjFKey As New ForeignKeyConstraint ("FK_BookAuthor",

ObjParentColumn, ObjChildColumn)

ObjFKey.DeleteRule = Rule.Cascade

ObjFKey.UpdateRule = Rule.Cascade

ObjChildTable.Constraints.Add(ObjFKey)

You can also bind the Constraints collection of each table to a DataGrid for display.

MyDataGrid.DataSource = ObjDataSet.Tables(0).Constraints

· The DeleteRule And UpdateRule Property Values

1) Cascade: Updating and deleting parent table rows affect the child rows as well.

2) SetDefault: Update to the primary key value in the parent table or deletion of a parent row both cause the foreign key in all linked child rows to be set to its default value.

3) SetNull: Same as above except child values are set to Null.

4) None: Parent row updates or deletes have no effect on child rows.

· Adding Rows To Table With An Object Array

Dim ObjValueArray(1) As Object

ObjValueArray(0) = "24421212"

ObjValueArray(1) = "Professional ASP .Net"

ObjTable.Rows.Add(ObjValueArray)

· Editing Values In A DataTable

ObjTable.Rows(0)("Title") = "Amateur Windows 2000"

ObjTable.Rows(3)("ISBN") = "25458756"

· Using The BeginEdit, CancelEdit And EndEdit Methods

In this technique we used the BeginEdit, CancelEdit and EndEdit methods of the DataRow object. The BeginEdit method creates a copy of the row; so all the changes are made to this copy rather then the original row. You can cancel all the changes by calling the CancelEdit method or you can make changes permanent by calling EndEdit method.

Dim ObjRow As DataRow = ObjTable.Rows(0)

ObjRow.BeginEdit()

ObjRow("ISBN") = "24365241"

' To access the values in the copy of row being edited

If ObjRow("ISBN", DataRowVersion.Proposed) > "1" Then

ObjRow.CancelEdit

Else

ObjRow.EndEdit

End If

· The Original, Current And Proposed Column Values

Every column in every row of a table maintains three values for that item:

1) Original: The value that was in the column when the DataTable was created and filled with the data. It is compared to the value in the original database table when an update is performed, to see if another user or process has changed the value since the DataTable data was created.

2) Proposed: The proposed value for this column after changes have been made following BeginEdit, but before EndEdit, CancelEdit, AcceptChanges or RejectChanges has been executed.

3) Current: The Actual column value after changes have been made to it, and after these changes have been accepted (after EndEdit or AcceptChanges has been call)

As far as the DataRow is concerned:

1) After execution of the BeginEdit method, if you change the value in any column, the Current and Proposed values of all the columns become accessible. The Proposed value is the same as the Current value until you edit that particular column.

2) After execution of the EndEdit method, the Current value for each column is replaced by the Proposed value.

3) After execution of the CancelEdit method, the Proposed value is discarded and the Current value is unchanged.

4) After execution of the AcceptChanges method, the Original value for each column is replaced by the Current value.

5) After execution of the RejectChanges, the Current value is discarded and the Original value is unchanged.

After execution of DataTable object's AcceptChanges method, the Original value for every column in all rows in the table is set to the same as the Current value.

· The RowState Property Of The DataRow Object

Each row in the table exposes another useful property named RowState. You can access it via DataRowState enumeration.

1) Unchanged: No changes have been made to the row since it was created or since the last call to the AccecptChanges.

2) Added: The row has been added to the table and AcceptChanges has not yet been executed.

3) Modified: Row has changed since the last call to the AccecpChanges.

4) Deleted: The row has been deleted (marked).

5) Detached: The row has been created with the NewRow method but has not yet been added to the table with the Add method.

· Deleting Rows Form The DataTable

1) You can directly delete a row from the table as:

ObjTable.Rows(2).Delete()

2) If you have a reference (Object) to the row as:

ObjDataRow.Delete()

The deleted rows remain in the table. The Delete method just sets the RowState property to DataRowState.Deleted. When you call the AcceptChanges, then the deleted rows are removed from the table. It means that you can call the RejectChanges method to undelete the rows that are marked as Deleted.

· Removing Row From The DataTable

The Remove method immediately and irretrievably removes the row from the table. It is not marked as deleted. The Delete method is a member of the DataRow object while the Remove method is a member of the Rows collection. When the remove method is called the index of the rows collection is automatically adjust. So if you remove a row at index position 2 the next row's index (3 before removing) becomes the 2 after the execution of the method.

ObjTable.Rows.Remove(2)

· DataTable Events

The ColumnChanging, RowChanging and RowDeleting events are raised when the column or row is being edited or deleting and before the change is applied. ColumnChanged, RowChanged and RowDeleted events raise after the changes are applied.

Each DataRow object has a RowError property, which is basically just a String value. You can write your custom error message in this field.

Handling RowChanged Event:

AddHandler ObjTable.RowChanged New

DataRowChangeEventHandler(AddressOf OnRowChanged)

Sub OnRowChanged(ObjSender As Object, ObjArgs As

DataRowChangeEventArgs)

' Only react if the action is "Change"

If ObjArgs.Action = DataRowAction.Change Then

If ObjArgs.Row("ISBN") <> "2542535" Then

ObjArgs.Row.RowError = "Invalid ISBN"

End If

End Sub

Now you can have a DataView with only modified row

Dim ObjDataView As DataView

ObjDataView = ObjTable.Tables(0).DefaultView

ObjDataView.RowStateFilter = DataRowState.Modified

DataSet, DataTable and DataRow objects have a property HasErrors, which is True if any row has a now-empty value for its RowError property. An alternative approach is to use the GetErrors method of DataTable object to get an array of all the rows that contains an error.

· Using Table And Column Mappings

All the mappings for both table and column names are stored in the DataAdapter object. The DataAdapter uses a TableMappings collection to store mappings for the tables. Also, each TableMapping contains a collection of ColumnMapping instances that define the alias relevant to that table. The names used in the mappings are case-sensitive for the other than SQL objects i.e. OldDb and Odbc. You can create a default table mapping, so that any call to Fill that does not specify the name of the table will create a table named you specify. First declare a variable to hold a DataTableMapping object, and then call the Add method of the DataAdapter object's TableMappings collection. In the Add method the first parameter would be the original name of the table (database end) and the second parameter would be the alias used. To specify a default table name use "Table" name in the first parameter.

Dim ObjDataAdapter As New SQLDataAdapter()

Dim ObjTableMapping As DataTableMapping

ObjTableMapping = ObjDataAdapter.TableMappings.Add

("Table", "DefaultBookList")

Once you created the TableMapping object, its ColumnMapping collection can be accessed to create the column mappings.

With ObjTableMapping.ColumnMappings

.Add ("ISBN","Code")

.Add ("Title","Heading")

End With

· Sorting And Filtering Data

You can use the DataTabel's Select method to filter and sort the rows.

DataTable.Select (Filter-Expression, Sort-Order)

ObjDataTable.Select ("Title LIKE '%ASP%'", "PubDate DESC")

There is also an overloaded version of the Select method that accepts the third parameter DataViewRowState. By using the each row's RowState property you can filter the row by its states.

ObjDataTable.Select ("Title LIKE '*ASP*'", "Title",

DataViewRowState.Unchanged)

The other values are CurrentRows, OriginalRows, ModifiedCurrent, ModifiedOriginal, Unchanged, Added, Deleted, None.

The Select method returns an array of DataRow objects that match the filter and sort criteria.

Dim ObjResults() As DataRow

ObjResults = ObjTable.Select(<Expression>,<Sort-Order>)

You can also filter and sort a DataView. Suppose you have a DataView object, to sort it:

ObjDataView.Sort = "Title"

And to filter it on a expression:

ObjDataView.RowFilter = "Title LIKE '%ASP%'"

· A transaction is a series of events that are all completed, or of which none are completed-there is never an intermediate result where some but not all the events within the transaction occurs.

ASP.NET 3.5 Interview questions

Search This Blog