ASPDOTNET VBDOTNET ADODOTNET - MANOHAR GOUD

K MANOHAR GOUD

9866550012

Applications communicate with a database, firstly, to retrieve the data stored there and present it in a user-friendly way, and secondly, to update the database by inserting, modifying and deleting data.

Microsoft ActiveX Data Objects.Net (ADO.Net) is a model, a part of the .Net framework that is used by the .Net applications for retrieving, accessing and updating data.

ADO.Net Object Model

ADO.Net object model is nothing but the structured process flow through various components. The object model can be pictorially described as:

The data residing in a data store or database is retrieved through the data provider. Various components of the data provider retrieve data for the application and update data.

An application accesses data either through a dataset or a data reader.

• Datasets store data in a disconnected cache and the application retrieves data from it.
• Data readers provide data to the application in a read-only and forward-only mode.

Data Provider

A data provider is used for connecting to a database, executing commands and retrieving data, storing it in a dataset, reading the retrieved data and updating the database.

The data provider in ADO.Net consists of the following four objects:

S.N	Objects & Description
1	Connection This component is used to set up a connection with a data source.
2	Command A command is a SQL statement or a stored procedure used to retrieve, insert, delete or modify data in a data source.
3	DataReader Data reader is used to retrieve data from a data source in a read-only and forward-only mode.
4	DataAdapter This is integral to the working of ADO.Net since data is transferred to and from a database through a data adapter. It retrieves data from a database into a dataset and updates the database. When changes are made to the dataset, the changes in the database are actually done by the data adapter.

There are following different types of data providers included in ADO.Net

• The .Net Framework data provider for SQL Server - provides access to Microsoft SQL Server.
• The .Net Framework data provider for OLE DB - provides access to data sources exposed by using OLE DB.
• The .Net Framework data provider for ODBC - provides access to data sources exposed by ODBC.
• The .Net Framework data provider for Oracle - provides access to Oracle data source.
• The EntityClient provider - enables accessing data through Entity Data Model (EDM) applications.

DataSet

DataSet is an in-memory representation of data. It is a disconnected, cached set of records that are retrieved from a database. When a connection is established with the database, the data adapter creates a dataset and stores data in it. After the data is retrieved and stored in a dataset, the connection with the database is closed. This is called the 'disconnected architecture'. The dataset works as a virtual database containing tables, rows, and columns.

The following diagram shows the dataset object model:

The DataSet class is present in the System.Data namespace. The following table describes all the components of DataSet:

S.N	Components & Description
1	DataTableCollection It contains all the tables retrieved from the data source.
2	DataRelationCollection It contains relationships and the links between tables in a data set.
3	ExtendedProperties It contains additional information, like the SQL statement for retrieving data, time of retrieval, etc.
4	DataTable It represents a table in the DataTableCollection of a dataset. It consists of the DataRow and DataColumn objects. The DataTable objects are case-sensitive.
5	DataRelation It represents a relationship in the DataRelationshipCollection of the dataset. It is used to relate two DataTable objects to each other through the DataColumn objects.
6	DataRowCollection It contains all the rows in a DataTable.
7	DataView It represents a fixed customized view of a DataTable for sorting, filtering, searching, editing and navigation.
8	PrimaryKey It represents the column that uniquely identifies a row in a DataTable.
9	DataRow It represents a row in the DataTable. The DataRow object and its properties and methods are used to retrieve, evaluate, insert, delete, and update values in the DataTable. The NewRow method is used to create a new row and the Add method adds a row to the table.
10	DataColumnCollection It represents all the columns in a DataTable.
11	DataColumn It consists of the number of columns that comprise a DataTable.

Connecting to a Database

The .Net Framework provides two types of Connection classes:

• SqlConnection - designed for connecting to Microsoft SQL Server.
• OleDbConnection - designed for connecting to a wide range of databases, like Microsoft Access and Oracle.

Example 1

We have a table stored in Microsoft SQL Server, named Customers, in a database named testDB. Please consult 'SQL Server' tutorial for creating databases and database tables in SQL Server.

Let us connect to this database. Take the following steps:

1. Select TOOLS -> Connect to Database
   
2. Select a server name and the database name in the Add Connection dialog box.
   
3. Click on the Test Connection button to check if the connection succeeded.
   
4. Add a DataGridView on the form.
   
5. Click on the Choose Data Source combo box.
6. Click on the Add Project Data Source link.
   
7. This opens the Data Source Configuration Wizard.
8. Select Database as the data source type
   
9. Choose DataSet as the database model.
   
10. Choose the connection already set up.
    
11. Save the connection string.
    
12. Choose the database object, Customers table in our example, and click the Finish button.
    
13. Select the Preview Data link to see the data in the Results grid:
    

When the application is run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window:

Example 2

In this example, let us access data in a DataGridView control using code. Take the following steps:

1. Add a DataGridView control and a button in the form.
2. Change the text of the button control to 'Fill'.
3. Double click the button control to add the required code for the Click event of the button, as shown below:

Imports System.Data.SqlClient
Public Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) _
   Handles MyBase.Load
        'TODO: This line of code loads data into the 'TestDBDataSet.CUSTOMERS' table.   You can move, or remove it, as needed.
      Me.CUSTOMERSTableAdapter.Fill(Me.TestDBDataSet.CUSTOMERS)
      ' Set the caption bar text of the form.   
      Me.Text = "tutorialspoint.com"
   End Sub
   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      Dim connection As SqlConnection = New sqlconnection()
      connection.ConnectionString = "Data Source=KABIR-DESKTOP; _
          Initial Catalog=testDB;Integrated Security=True"
      connection.Open()
      Dim adp As SqlDataAdapter = New SqlDataAdapter _
      ("select * from Customers", connection)
      Dim ds As DataSet = New DataSet()
      adp.Fill(ds)
      DataGridView1.DataSource = ds.Tables(0)
   End Sub
End Class

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window:

Clicking the Fill button displays the table on the data grid view control:

Creating Table, Columns and Rows

We have discussed that the DataSet components like DataTable, DataColumn and DataRow allow us to create tables, columns and rows, respectively.

The following example demonstrates the concept:

Example 3

So far, we have used tables and databases already existing in our computer. In this example, we will create a table, add columns, rows and data into it and display the table using a DataGridView object.

Take the following steps:

• Add a DataGridView control and a button in the form.
• Change the text of the button control to 'Fill'.
• Add the following code in the code editor.

Public Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
      ' Set the caption bar text of the form.   
      Me.Text = "tutorialspont.com"
   End Sub
   Private Function CreateDataSet() As DataSet
      'creating a DataSet object for tables
      Dim dataset As DataSet = New DataSet()
      ' creating the student table
      Dim Students As DataTable = CreateStudentTable()
      dataset.Tables.Add(Students)
      Return dataset
   End Function
   Private Function CreateStudentTable() As DataTable
      Dim Students As DataTable
      Students = New DataTable("Student")
      ' adding columns
      AddNewColumn(Students, "System.Int32", "StudentID")
      AddNewColumn(Students, "System.String", "StudentName")
      AddNewColumn(Students, "System.String", "StudentCity")
      ' adding rows
      AddNewRow(Students, 1, "Zara Ali", "Kolkata")
      AddNewRow(Students, 2, "Shreya Sharma", "Delhi")
      AddNewRow(Students, 3, "Rini Mukherjee", "Hyderabad")
      AddNewRow(Students, 4, "Sunil Dubey", "Bikaner")
      AddNewRow(Students, 5, "Rajat Mishra", "Patna")
      Return Students
   End Function
   Private Sub AddNewColumn(ByRef table As DataTable, _ 
   ByVal columnType As String, ByVal columnName As String)
      Dim column As DataColumn = _ 
       table.Columns.Add(columnName, Type.GetType(columnType))
   End Sub

   'adding data into the table
   Private Sub AddNewRow(ByRef table As DataTable, ByRef id As Integer,_
   ByRef name As String, ByRef city As String)
      Dim newrow As DataRow = table.NewRow()
      newrow("StudentID") = id
      newrow("StudentName") = name
      newrow("StudentCity") = city
      table.Rows.Add(newrow)
   End Sub
   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      Dim ds As New DataSet
      ds = CreateDataSet()
      DataGridView1.DataSource = ds.Tables("Student")
   End Sub
End Class

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window:

Clicking the Fill button displays the table on the data grid view control:

ASPDOTNET WITH VBDOTNET - MASTERPAGE BASICS MANOHAR GOUD

K MANOHAR GOUD

9866550012

ASP.NET master pages enable you to create a page layout (a master page) that you can use with selected or all pages (content pages) in your Web site. Master pages can greatly simplify the task of creating a consistent look for your site.

ASP.NET master pages allow you to create a consistent layout for the pages in your application. A single master page defines the look and feel and standard behavior that you want for all of the pages (or a group of pages) in your application. You can then create individual content pages that contain the content you want to display. When users request the content pages, they merge with the master page to produce output that combines the layout of the master page with the content from the content page.

Master pages actually consist of two pieces, the master page itself and one or more content pages.

A master page is an ASP.NET file with the extension .master (for example, MySite.master) with a predefined layout that can include static text, HTML elements, and server controls. The master page is identified by a special @ Master directive that replaces the @ Page directive that is used for ordinary .aspx pages. 

Master Pages

<%@ Master Language="C#" %>

or

<%@ Page Language="VB" MasterPageFile="~/MasterPages/Master1.master" Title="Content Page" %>

<% @ Master Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 
    1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server" >
    <title>Master page title</title>
</head>
<body>
    <form id="form1" runat="server">
        <table>
            <tr>
                <td><asp:contentplaceholder id="Main" runat="server" /></td>
                <td><asp:contentplaceholder id="Footer" runat="server" /></td>
            </tr>
        </table>
    </form>
</body>
</html>

or

<%@ Master Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 
    1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server" >
    <title>Master page title</title>
</head>
<body>
    <form id="form1" runat="server">
        <table>
           <tr>
               <td><asp:contentplaceholder id="Main" runat="server" /></td>
               <td><asp:contentplaceholder id="Footer" runat="server" /></td>
           </tr>
        </table>
    </form>
</body>
</html>

Content Pages

 

<%@ Page Language="C#" MasterPageFile="~/MasterPages/Master1.master" Title="Content Page"%>

or

<%@ Page Language="VB" MasterPageFile="~/MasterPages/Master1.master" Title="Content Page" %>

Replacing placeholder content

 <% @ Page Language="VB" MasterPageFile="~/Master.master" Title="Content Page 1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Main" Runat="Server">
    Main content.
</asp:Content>
    
<asp:Content ID="Content2" ContentPlaceHolderID="Footer" Runat="Server" >
    Footer content.
</asp:content>

or

<% @ Page Language="C#" MasterPageFile="~/Master.master" Title="Content Page 1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Main" Runat="Server">
    Main content.
</asp:Content>
    
<asp:Content ID="Content2" ContentPlaceHolderID="Footer" Runat="Server" >
    Footer content.
</asp:content>

Advantages of Master Pages

Master pages provide functionality that developers have traditionally created by copying existing code, text, and control elements repeatedly; using framesets; using include files for common elements; using ASP.NET user controls; and so on. Advantages of master pages include the following:

• They allow you to centralize the common functionality of your pages so that you can make updates in just one place.
• They make it easy to create one set of controls and code and apply the results to a set of pages. For example, you can use controls on the master page to create a menu that applies to all pages.
  
• They give you fine-grained control over the layout of the final page by allowing you to control how the placeholder controls are rendered.
  
• They provide an object model that allows you to customize the master page from individual content pages.

Run-time Behavior of Master Pages

At run time, master pages are handled in the following sequence:

1. Users request a page by typing the URL of the content page.
2. When the page is fetched, the @ Page directive is read. If the directive references a master page, the master page is read as well. If this is the first time the pages have been requested, both pages are compiled.
   
3. The master page with the updated content is merged into the control tree of the content page.
   
4. The content of individual Content controls is merged into the corresponding ContentPlaceHolder control in the master page.
   
5. The resulting merged page is rendered to the browser.

The process is illustrated in the following diagram.
Master pages at run time

From the user's perspective, the combined master and content pages are a single, discrete page. The URL of the page is that of the content page.

From a programming perspective, the two pages act as separate containers for their respective controls. The content page acts as a container for the master page. However, you can reference public master-page members from code in the content page, as described in the next section.

Note that the master page becomes a part of the content page. In effect, the master page acts in much the same way a user control acts — as a child of the content page and as a container within that page. In this case, however, the master page is the container for all of the server controls that are rendered to the browser. The control tree for a merged master and content page looks something like this:

Page
    Master Page
        (Master page markup and controls)
        ContentPlaceHolder
            Content page markup and server controls
        (Master page markup and controls)
        ContentPlaceHolder
            Content page markup and server controls
        (Master page markup and controls)

 

 

PROGRAMMING OF MASTER PAGE

 

' Gets a reference to a TextBox control inside a ContentPlaceHolder
Dim mpContentPlaceHolder As ContentPlaceHolder
Dim mpTextBox As TextBox
mpContentPlaceHolder = _
    CType(Master.FindControl("ContentPlaceHolder1"), _
    ContentPlaceHolder)
If Not mpContentPlaceHolder Is Nothing Then
    mpTextBox = CType(mpContentPlaceHolder.FindControl("TextBox1"), _
        TextBox)
    If Not mpTextBox Is Nothing Then
        mpTextBox.Text = "TextBox found!"
    End If
End If

' Gets a reference to a Label control that is not in a 
' ContentPlaceHolder control
Dim mpLabel As Label
mpLabel = CType(Master.FindControl("masterPageLabel"), Label)
If Not mpLabel Is Nothing Then
    Label1.Text = "Master page label = " + mpLabel.Text
End If

Sub Page_PreInit(ByVal sender As Object, ByVal e As EventArgs) _
        Handles Me.PreInit
    Me.MasterPageFile = "~/NewMaster.master"
End Sub

Public Class BaseMaster
    Inherits MasterPage
    Public Overridable ReadOnly Property MyTitle() As String
        Get
            Return "BaseMaster Title"
        End Get
    End Property
End Class

 

<%@ Master Language="VB"  Inherits="BaseMaster" 
    ClassName="MasterBlue" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
    ' No property here that overrrides the MyTitle property of the base master.
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>No title</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="background-color:LightBlue">
        <asp:contentplaceholder id="ContentPlaceHolder1" 
            runat="server">
        Content from MasterBlue.
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>

<%@ Master Language="VB" Inherits="BaseMaster" 
    ClassName="MasterGreen" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">
    Public Overrides ReadOnly Property MyTitle() As String
        Get
            Return "MasterGreen Title"
        End Get
    End Property
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">   
    <title>No title</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="background-color:LightGreen">
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
            Content from MasterGreen.
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>

<%@ Page Language="VB" Title="Content Page" MasterPageFile="~/MasterBlue.master"%>
<%@ MasterType TypeName="BaseMaster" %>
<script runat="server">
    
    Protected Sub Page_PreInit(ByVal sender As Object, 
            ByVal e As System.EventArgs)
        Me.MasterPageFile = "MasterBlue.master"
        If Request.QueryString("color") = "green" Then
            Me.MasterPageFile = "MasterGreen.master"
        End If
        Me.Title = Master.MyTitle
    End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    Content from Content page.
</asp:Content>

or

c#

// Gets a reference to a TextBox control inside a ContentPlaceHolder
ContentPlaceHolder mpContentPlaceHolder;
TextBox mpTextBox;
mpContentPlaceHolder = 
    (ContentPlaceHolder)Master.FindControl("ContentPlaceHolder1");
if(mpContentPlaceHolder != null)
{
    mpTextBox = (TextBox) mpContentPlaceHolder.FindControl("TextBox1");
    if(mpTextBox != null)
    {
        mpTextBox.Text = "TextBox found!";
    }
}

// Gets a reference to a Label control that is not in a 
// ContentPlaceHolder control
Label mpLabel = (Label) Master.FindControl("masterPageLabel");
if(mpLabel != null)
{
    Label1.Text = "Master page label = " + mpLabel.Text;
} 

void Page_PreInit(Object sender, EventArgs e)
{
    this.MasterPageFile = "~/NewMaster.master";
}

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public class BaseMaster : System.Web.UI.MasterPage
{
    public virtual String MyTitle
    {
        get { return "BaseMaster Title"; }
    }
}

<%@ Master Language="C#" Inherits="BaseMaster" 
    ClassName="MasterBlue" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
    // No property here that overrrides the MyTitle property of the base master.
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>No title</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="background-color:LightBlue">
        <asp:contentplaceholder id="ContentPlaceHolder1" 
            runat="server">
        Content from MasterBlue.
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>

<%@ Master Language="C#" Inherits="BaseMaster" 
    ClassName="MasterGreen" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">
    public override String MyTitle
    { 
        get { return "MasterGreen Title"; } 
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">   
    <title>No title</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="background-color:LightGreen">
        <asp:contentplaceholder id="ContentPlaceHolder1" 
            runat="server">
            Content from MasterGreen.
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>

<%@ Page Language="C#" Title="Content Page" MasterPageFile="~/MasterBlue.master"%>
<%@ MasterType TypeName="BaseMaster" %>
<script runat="server">
    protected void Page_PreInit(Object sender, EventArgs e)
    {
        this.MasterPageFile = "MasterBlue.master";
        if(Request.QueryString["color"] == "green")
        {
            this.MasterPageFile = "MasterGreen.master";
        }
        this.Title = Master.MyTitle;
    }
</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" 
        Runat="Server">
    Content from Content page.
</asp:Content>

Both master pages and content pages can contain event handlers for controls. For controls, events are handled locally—a control in a content page raises an event in the content page, and a control in the master page raises an event in the master page. Controls events are not sent from the content page to the master page. Similarly, you cannot handle an event from a master page control in a content page.

In some cases, the same event is raised in both the content and the master page. For example, both pages raise Init and Load events. The general rule for how events are raised is that the initialization events are raised from the innermost control to the outermost one, and all other events are raised from the outermost control to the innermost one. It is helpful to remember that the master page is merged into the content page and treated as a control in the content page.

The following is the sequence in which events occur when a master page is merged with a content page:

1. Master page controls Init event.
   
2. Content controls Init event.
   
3. Master page Init event.
   
4. Content page Init event.
   
5. Content page Load event.
   
6. Master page Load event.
   
7. Content controls Load event.
   
8. Content page PreRender event.
   
9. Master page PreRender event.
   
10. Master page controls PreRender event.
    
11. Content controls PreRender event.
    

The sequence of events in master and content pages rarely is important for you as page developer.

 Sub Page_Load()
    Dim mpContentPlaceHolder As ContentPlaceHolder
    Dim mpTextBox As TextBox
    mpContentPlaceHolder = _
        CType(Master.FindControl("ContentPlaceHolder1"), _
        ContentPlaceHolder)
    If Not mpContentPlaceHolder Is Nothing Then
        mpTextBox = CType(mpContentPlaceHolder. _
            FindControl("TextBox1"), TextBox)
        If Not mpTextBox Is Nothing Then
            mpTextBox.Text = "TextBox found!"
        End If
    End If

    ' Gets a reference to a Label control not in a 
    ' ContentPlaceHolder
    Dim mpLabel As Label
    mpLabel = CType(Master.FindControl("masterPageLabel"), Label)
    If Not mpLabel Is Nothing Then
        Label1.Text = "Master page label = " + mpLabel.Text
    End If
End Sub

or

pContentPlaceHolder = 
      (ContentPlaceHolder)Master.FindControl("ContentPlaceHolder1");
    if(mpContentPlaceHolder != null)
    {
        mpTextBox = 
            (TextBox) mpContentPlaceHolder.FindControl("TextBox1");
        if(mpTextBox != null)
        {
            mpTextBox.Text = "TextBox found!";
        }
    }
    
    // Gets a reference to a Label control that not in 
    // a ContentPlaceHolder
    Label mpLabel = (Label) Master.FindControl("masterPageLabel");
    if(mpLabel != null)
    {
        Label1.Text = "Master page label = " + mpLabel.Text;
    }
}