Data Source Controls

Data Source controls are asp .net controls that allow you to connect to a database, retrieve or update data inside the database without writing any code. Thus Data source controls implemented all those downs and ups used in direct data access. More interestingly data source controls work with data oriented list controls (data-bound controls) with ease.
Data source controls handle operations like: 

• Managing connection
• Data retrieval and update
• Data presentation etc

.Net framework includes these data source controls:

• SqlDataSource -used for Sql Server, Oracle, OleDb and ODBC
• AccessDataSource - used for Ms Access
• ObjectDataSource - custom data access class(es)
• XmlDataSource -used for xml files
• SiteMapDataSource - used for sitemap data source

In this note I'll be considering SqlDataSource.To use SqlDataSource controls you need to provide certain values including connectionString, ProviderName and command (SelectCommand, UpdateCommand, DeleteCommand or InsertCommand). All these is usually done inside your aspx markup page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"

            ProviderName="System.Data.SqlClient"

            SelectCommand="SELECT [DName], [Dcode] FROM [Departments]">

</asp:SqlDataSource>

 In the above connectionString you provide the name of your connectionString (probably stored inside web.config) like the one I underlined.

Now after this point what is remaining is how to bind to another control that uses the retrieved data and may provide data to update. Let us use a dropdownlist to display the list of departments:

<asp:DropDownList ID="DropDownList1" runat="server"

      AppendDataBoundItems="True" DataSourceID="SqlDataSource1"

      DataTextField="DName" DataValueField="Dcode">

      <asp:ListItem Selected="True" Value="Select">Select . . .</asp:ListItem>

</asp:DropDownList>

Inside the above markup DataSourceID="SqlDataSource1" is telling the dropdownlist where to find the data while  DataTextField="DName" and DataValueField="Dcode" tells which column to use for display text and which one to use for item's value respectively. The AppendDataBoundItems="True" on the other hand dictates the dropdownlist to not only use data from database but also append another additional data to be added at design time or at runtime. Now we've added an item <asp:ListItem Selected="True" Value="Select">Select . . .</asp:ListItem> at design time.

The result should look like:

 Go to this links to see more: 
 Data Source Controls, Part 1: The Basics

 Data Source Controls Overview 

 

ASP .net Data Binding

Data binding at first is a mechanism  by which you can bind, synchronize or link data with user interface (controls).
In ASP.net there are two types of data binding:

1. Single-Value (Simple) Data Binding
2. Repeated-Value (List) Data Binding

Single-Value Data binding

Allows you to take a variable, a value returned by a method or an expression and place it dynamically inside the user interface of a webform.

Syntax:    <%# your_expression %> 

this example can be a footer on a website 

<div style="text-align:center;">

    CopyRight &copy;<%#DateTime.Now.Year %>. Nuux

</div>

      the output should look like:

                              CopyRight©2011. Nuux

Note that the year is not constant it will change with the server's date.

Data binding to work requires to be activated explicitly. To activate data binding call a method known as Databind() for each control as ctrl.Databind() or once for all controls as Me.Databind() or simply Databind()in which case the owner object is implied as Me (the current page (this in C#)). So call this method in your Page load event like the following example. It is better practice to place the method below your code.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Your code goes here

        Me.DataBind()

    End Sub

 If you have got variables or methods you want to show or use their values on your interface make sure they are declared with either of these access modifiers: Public, Protected or Friend. You cannot access objects with private access in your interface.

Control properties can be set by data binding: Try this:

<asp:TextBox id="txt1" runat="server" />

<asp:Label id="labelOne" Text="<%# txt1.Text %>" runat="server" />

<asp:Label id="labelTwo" Text="<%# DateTime.Now %>" runat="server" />

Repeated-Value Data binding

Repeated-Value data binding works with list controls. By the way, there are two types of list controls in asp.net:

1. Simple List Controls eg: ListBox,DropDownList, RadioButtonList etc
2. Rich Data List Controls eg: GridView,ListView, DetailsView and FormView

These controls expose a number of properties and methods for data binding purpose.You can bind these controls to almost any structure that support the IEnumerable interface. The data sources for this controls include: Collection,array, arraylist, DataTable, DataView,DataReader, HashTable and DataSet.

Data binding can be done at design time or at runtime. 

For a good explanation this might help you: Data Binding - The Concepts

 

ADO.NET II - DIRECT DATA ACCESS

Building SQL commands and using ADO.NET classes to access database without the need of data oriented controls is considered as direct data access.

There are a number of steps you should follow to achieve direct data access:

1. Import relevant namespace
2. create an instance object of SqlConnection class
3. set the ConnectionString property of the connection object
4. Open the connection
5. Create an instance object of SqlCommand
6. set the CommandText property of the command with your sql statement
7. set the Connection property of the command object with the SqlConnection object you’ve created
8. execute the command

If you are retrieving data (i.e: if you are using Select sql statement) you need to use SqlDataReader class to extract the data from the SqlCommand:

9. create a variable object of type SqlDataReader. Note: SqlDataReader does not use the new keyword that means it has no constructor
10. Execute command reader. Command reader (SqlCommand.ExecuteReader() ) is a method that executes the command against the database and at the same time returns a SqlDataReader with the result
11. Traverse the rows of SqlDataReader one after one by using Read() method
12. after you call the Read() method use the data in the reader by column index or name
13. close the reader and the connection if you are done (important)

Example:

Imports System.Data.SqlClient

Partial Class CustDetails

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Dim id As String

        'id = Request.QueryString("id")

        Dim conn As New SqlConnection()

        conn.ConnectionString = "Data source=(local)\sqlexpress; AttachDbFilename=|DataDirectory|\Database.mdf;" _

        & "Integrated Security=true; User Instance=true;"

        conn.Open()

        Dim cmd As New SqlCommand()

        cmd.CommandText = "select * from Customers where custid='12'"

        'cmd.Parameters.AddWithValue("@cust", id)

        cmd.Connection = conn

        Dim drdr As SqlDataReader

        drdr = cmd.ExecuteReader()

        drdr.Read()

        If drdr.HasRows Then

            lblCustID.Text = drdr("custID").ToString()

            lblCustName.Text = drdr("fname").ToString()

            lblCustSurname.Text = drdr("lname").ToString()

        Else

            lblError.Text = "No customer found with the stated ID"

        End If

        drdr.Close()

        conn.Close()

    End Sub

End Class

For how to use Parameters go to my channel http://youtube.com/nuux14

ADO.NET I

ADO.NET is a set of classes designed to access data sources.
We have two categories of these classes:

1. Data Container classes: these classes are merely used for temporary data storage. Data retrieved from database are stored in these classes. Beyond that data container classes can act like your database by maintaining relationships between tables (offline tables not real database tables). This is sometimes referred as Disconnected architecture. These classes include: DataSet
2. Data Connection classes (Data Providers): these classes are the concrete tools to work with data sources. Data providers help us establish connection with our database, retrieve data, update and many related tasks. These classes include: [provider]Connection.

In ADO.NET we have four Data Providers:

1. Sql Data Provider
2. Oracle Data Provider
3. OleDb Data Provider
4. ODBC Data Provider

The main common classes in all data providers are: Connection, Command, DataAdapter, & DataReader.

Sql Data Provider:

• SqlConnection - to establish a connection to our database. This is the first task when we want to deal with database. If the connection is established successfully you have cut a major part of your journey. As a rule every opened connection must be closed after usage. ConnectionString is a property of this class that needs to be set with a number of parameters passed with their values. After this you call Open() method to establish the connection and to disrupt it you call Close() method.
• SqlCommand - to execute sql statements (commands) against the database.This is second task you should do. Two properties need to be set to use this class. The CommandText is set with an sql statement string. The Connection is set with the connection object we have created.
• SqlDataAdapter- it is a bridge between database and dataset.
• SqlDataReader - to read the data returned by SqlCommand

Connection String
To establish a successful connection we need to understand the different ways to construct connection string based on different DBMSs & their different versions. Basically we need to set and pass at least the following parameters in the connection string:

• Data Source - the name or the address where the database is residing.
• Initial Catalog , Database or AttachDbFilename - the name and the path of the database file
• Integrated Security - the type of authentication to use. We have two types of authentication here. Windows Authentication and SQL Server Authentication. If you set this parameter to true you want to use Windows Authentication which is recommended otherwise you are using SQL Server Authentication which requires you to provide two other parameters namely User ID and Password.
• User Instance - you set this to true if your database is not in the master list of sql server. This means roughly your database is not attached to the sql server.

Let us go down: when building connection string you need to consider the version of sql server you are using and whether or not your database is attached to the sql server.

Option 1: You are using Full version of SQL Server

Criteria:  your database must be attached to the sql server
 Example connection string:

Dim conString As String

conString = "Data Source=(local);Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security=true;"

Option 2: You are using SQL Express and Database is attached to server

Example connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;Initial Catalog=yourdatabase.mdf;" _ 

& "Integrated Security=true;"

Option 3: You are using SQL Express and Database is not attached to server

Exmaple connection string

Dim conString As String

conString = "Data Source=(local)\SQLExpress;" _ 

& "AttachDbFilename=|DataDirectory|\yourdatabase.mdf;Integrated Security=true;"_ 

& "User Instance=true;"

Watch a little explanation in Somali

How to Redirect or Send a User from Current Page to Another Page

Of course we can use the traditional way of Html redirection in asp.net web forms but some times you need a more controlled redirection. For instance, you want to validate a user input using the server side code before you send the user to the desired page. At this time you need a kind of, let me say, deferred redirection. There are a number of alternatives to do this and that in asp.net web forms.

We can use the html anchor tag as usual to make unconditional redirection meaning that the user once he/she clicks the link is directly redirected or sent to the navigation url.

    <a href="Order.aspx" target="_blank"> Go to Order</a>

Asp.net has a similar control known as HyperLink

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Order.aspx">Go to Order</asp:HyperLink>

We can also use the PostBackUrl property of button controls ( Button, ImageButton, & LinkButton)

So you can set this property directly in the control at design time or inside code file at runtime.

<asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/Default.aspx">Go to Home</asp:LinkButton>

Or in the LinkButton1 click event or in any other event of the page and its controls just like this:

 

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

 Handles LinkButton1.Click

        ' your other code goes here

        LinkButton1.PostBackUrl = "~/Default.aspx"

    End Sub

Look at the above controls carefully. You’ll notice that the first two controls used the NavigateUrl property while the button controls used the PostBackUrl property. Yes there is a reason why these controls use different properties though they are all meant to send the user to another page. The reason is the above controls (<a> html tag and the HyperLink asp.net control) do not cause the current page to be sent to the server while PostBackUrl, as the name suggests, first send the current page to the server and then, if the server code doesn’t stop the redirection, sends the user to the specified url (page). If you are asking what is the purpose of posting back the current page to the server, the answer can be sometimes you may need to have control over the redirection that is you may have some content to submit first before sending the user to another location. For example, if you are validating form input in server code you may need to stop the redirection and send error messages back to the current page. In other terms, anchor tag and HyperLink control do not cause validation that means the user skips the validation errors and is able to jump to the url specified by these tags while button controls cause validation and will not allow the user to navigate away from the current page (Note: you can stop buttons from causing validation by setting the CausesValidation property to false).

Request.Redirect("url") and Server.Transfer("url") are methods that made possible to redirect users without the need of any tag or asp.net control. Request and Respond are properties of the Page class. These properties will enable us redirect users at any time in our code. For example, we will redirect a user directly to another page before the user can see the page he/she requests.

Put this line of code in one of the pages events that occur before the page is rendered to the browser:

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        Response.Redirect("~/Order.aspx")

End Sub

Or put the statement in the Page.Load event after you perform some operations. Remember in both events you will not be able to see the requested page (i.e: if you request http://localhost/MyWebsite/default.aspx and you place that code in some event of the default page you will never be seeing the default.aspx web form)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim IMG As Image = CType(Master.FindControl("Image1"), Image)

        IMG.ImageUrl = "~/RememberAllah.png"

        Response.Redirect("~/Order.aspx")

End Sub