ADO.NET provides an API for accessing database systems according to the programs. ADO.NET was created for the .NET Framework and it can be said that they are next generation of Active Data Objects (ADO). The .NET Framework contains several namespaces and classes, which are devoted to database accesses. Microsoft has created separate namespaces that are optimized for working with different data providers (different types of databases). Before starting this topic, the user should have good knowledge of Databases such as MS Access and SQL Server or Oracle.

The following data provides specific namespaces that are included with ADO.NET:

System.Data.SqlClient

Contains classes for connecting to Microsoft SQL Server version 7.0 or higher

System.Data.OleDb

Contains classes for connecting to a data source that has an OLE DB provider

System.Data.Odbc

Contains classes for connecting to a data source that has an ODBC driver

System.Data.OracleClient

Contains classes for connecting to an Oracle database server

The System. Data. SqlClient namespace includes the following three classes:

* SqlConnection
* SqlCommand
* SqlDataReader

You will use these classes very often when you plan to build your ASP.NET application with Microsoft SQL Server (version 7.0 or higher). These classes enable you to execute SQL state­ments and retrieve data quickly from a database query.

The SqlConnection class represents an open connection to a Microsoft SQL Server data­base.

The SqlCommand class represents a SQL statement or stored procedure.

Finally, the SqlDataReader class represents the results from a database query.

The next section of this chapter goes into the details of using each of these classes.

The first group of classes, from the System.Data.SqlClient namespace, works only with Microsoft SQL Server. If you want to work with another type of database, such as an Access or Oracle database, you have to use the classes from one of the other data provider-specific namespaces.

For example, if you want to connect to a Microsoft Access database, then you’ll need to use the classes from the System. Data. OleDb namespace. The System.Data.OleDb name­space includes the following classes:

* OleDbConnection
* OleDbCommand
* OleDbDataReader

It has to be noted that these classes have the same names as the ones in the previous group, except that these class names start with OleDb rather than SQL.

The OleDbConnection class represents an open database connection to a database.

The OleDbCommand class represents a SQL statement or stored procedure.

The OleDbReader class represents the results from a database query.

Performing Common Database Tasks

In the following sections, the method of performing common database tasks using ADO.NET will be discussed. First, you will create and open a database connection.

Second, you will retrieve and display database records, add new database records, update existing database records, and delete database records.

Third, you will perform all these tasks using classes from both the System.Data.SqlClient and System.Data.OleDb namespaces.

When you work with SQL Server, you need to import the System.Data.SqlClient namespace by adding the follow­ing page directive at the top of your ASP.NET page:

<%@ Import Namespace=”System.Data.SqlClient” %>

When working with other databases, such as Microsoft Access or Oracle databases, you need to import the System.Data.OleDb namespace by using the following page directive:

<% @ Import Namespace=”System.Data.OleDb” %>
How to Open a Database Connection ?

The following Example would show how to create and open a connection for a Microsoft SQL Server databases.
Example 17 OpenSqlConnection.aspx

<%@ Import Namespace=”System.Data.SqlClient” %>
<Script Runat=”Server”>Sub Page_Load

Dim conNorthwind As SqlConnection

conNorthwind=NewSqlConnection(”Server=localhost;uid=sa;pwd=secret;database= Northwind” )

conPubs. Open ()

End Sub

</Script>
Connection has been opened!

Firstly the System.Data.OleDb namespace is imported at the top of the page rather in the System. Data.SqlClient namespace.

Secondly,Instead of SQL classes OleDb classes are used in all the statements.

After you finish displaying the records from a DataReader, be sure to explicitly close it (using the Close () method).

Retrieving a Single Database Record

The developers generally need a single record from a database. Therefore it is very important to know how to retrieve it. For example, given a username you might have to look up someone’s password or retrieve a phone number for John from a database table.

Another common situation which requires retrieval of single database record is aggre­gate functions like MAX (), MIN (), COUNT () etc.

The previous section dealt with the use of a DataReader representing the results of a database query. But if there is a need, to retrieve a single result from a query, the ExecuteScalar () method is more efficient than the ExecuteReader () method, as it requires less coding.

The following example counts the no. of records from Employees table. We use the ExecuteScalar () method to retrieve and display the value of the SQL Count (*) aggregate function.

Example 21 SqlExecuteScalarCount.aspx

<%@ Import Namespace=”System.Data.SqlClient” %>

<Script Runat=”Server”>

Sub Page_Load

Dim conNorthwind As SqlConnection

Dim cmdSelectCount As SqlCommand

conNorthwind = New SqlConnection( “Server=localhost;UID=sa;PWD=secret;database=Northwind” )

conNorthwind.Open()

cmdSelectCount = New SqlCommand( “Select Count(*) From Employees”, conNorthwind )

lblEmp.Text = cmdSelectCount.ExecuteScalar()

conNorthwind.Close()

End Sub

</Script>

<html>

<body>

There are

<asp:Label

ID=”lblEmp”

Runat=”Server” />

Employees in this Table

</body>

</html>

Tags: ASP Dot Net by Inder Singh
No Comments »

ASP.NET validation controls provide an easy-to-use but powerful mechanism of ensuring that data is entered correctly on the forms. There are 6 validation controls included in the ASP.NET 2.0 and ASP.NET 3.5 versions

<script type=”text/javascript”>
function ValidateCheckBox()
{

}
</script>

<asp:Button ID=”btnPost” runat=”server” Text=”Post”
OnClientClick=”javascript:return ValidateCheckBox();”  OnClick=”btnPost_Click” />

Tags: OUR web by Inder Singh
No Comments »

There are two types of validations like client-side and server-side. While client side validations can be performed using JavaScript and VBScript, server side validations can be achieved via classic ASP. The biggest problem with these languages and technologies is that you have to write lengthy code to perform a simple task. Since, VBScript won’t work with Netscape browser, you cannot perform client-side validations using this language. You have to learn JavaScript or Classic ASP to perform validations.
With ASP.NET, these difficulties are eliminated. ASP.NET provides its own built-in controls for validations. You can easily place controls and write codes by using a text editor like Notepad or an IDE like Visual Studio .NET. Moreover, ASP.NET supports C#, Visual Basic .NET, and any other language in the Microsoft .NET family. ASP.NET provides six types of validation controls. They are

• CompareValidator
• CustomValidator
• RangeValidator
• RegularExpressionValidator
• RequiredFieldValidator
• ValidationSummary

Each of these above controls can be linked to .NET Web Form controls like TextBox, Dropdownbox, ListBoxes etc. Even though there are wide ranges of controls for validation, it is up to you to decide upon which control to use. It also depends up on your project needs. In this first part of this series, you will learn the application of first three controls with code listings using VB .NET and C#.

CompareValidator

As the name suggests, this control is used to compare the values of one control with another control. The comparison is done with the help of textboxes. The CompareValidator control has two important properties namely, Operator and Type. The Operator property is mainly used as a basis of comparison between two values (GreaterThan, LessThan, Equal, NotEqual etc) and Type property indicates the data type of the values like Integer, string etc.

To illustrate, copy or enter the code in Listing 1 and save the file with extension .aspx under the Inetpub/wwwroot directory. Be sure to install .NET Framework SDK or Visual Studio .NET before attempting this work.

CustomValidator

With the help of the Custom Validator control, you can write your own functions and use them for performing validations in Web Forms. These functions are similar to that of JavaScript and VBScript functions.

<script runat=”server”>
Sub ServerValidate (sender As Object, _
value As ServerValidateEventArgs)
Dim num As Int32 = Int32.Parse(value.Value)
If num Mod 2 = 0 Then
value.IsValid = True
Exit Sub
End If
value.IsValid = False
End Sub
</script>

<script runat="server">
void Page_Load() {
lblMessage.Text = "Hello World";
}
</script>

Finally, you should integrate the above function with a WebForm control via ClientValidationFunction and ServerValidationFunction properties as shown in listing shown below:

<asp:TextBox id=”Text1″ runat=”server” />
<asp:CustomValidator id=”CustomValidator1″ runat=”server”
ControlToValidate=”Text1″ OnServerValidate=”ServerValidate”
Display=”Static” Font-Name=”verdana” Font-Size=”8pt” foreColor =”blue”
ErrorMessage = “Not an even number”>
</asp:CustomValidator>

You have to specify a client-side or server-side function, which will contain the logic behind the validation using the OnServerValidate property. If there are any parameters, you should also specify those with this function. You should be aware that validations performed on the client’s computer require no round trip to server and hence reducing the valuable online time of users.

RangeValidator

This control is used to accept a range of values. For instance, you can employ this validation control, if a user should have to input values within a range such as from 1 to 100 or from 50 to 150. It accepts two important properties, MinimumValue and MaximumValue, and also the Type property. The listing shown below illustrates the functionality of this control:

<%@ Page Language=”VB” %>
<script runat=”server”>
</script>
<html>
<head><title> Range Validator - VB.NET</title>
</head>
<body>
<form runat=”server”>
<p>
<asp:TextBox id=”TextBox1″ runat=”server”></asp:TextBox>
</p>
<p>
<asp:RangeValidator id=”RangeValidator1″
runat=”server”
ErrorMessage=”Please enter a number between 1 and 100″
ControlToValidate=”TextBox1″
MinimumValue=”1″ MaximumValue=”100″
Display=”Dynamic” Type=”Integer”>
</asp:RangeValidator>
</p>
</form>
</body>
</html>

Tags: OUR web by Inder Singh
No Comments »