ADO.NET Notes

ADO.Net (Active Data Objects)



We have 4 Managed Providers in ADO.Net(Managed Provider is nothing but a .Net dll file)



1)System.Data.SqlClient - Managed Provider for SqlServer

2)System.Data.OracleClient - Managed Provider for Oracle

3)System.Data.Oledb - Managed Provider - can be used for any database which has Oledb provider (for COM Based Components).It is Object Oriented.

4)System.Data.Odbc - Managed Provider for Odbc Drivers - This can be used for all databases i.e Generic, but it would be slow compared to a managed provider specific to a given database.(for Win32 based).No Object Orientation .Not recommended.



Important Objects In Managed Provider:



1)Connection- Connection object encapsulates the functioanality of establishing a communication path over the sockets.In .Net, Connections are 'Pooled' we can make by default 100 simultaneous connections max without closing but obviously we have to close every connection opened only then it can be reused 'Pooled' means keeps the connection for the short period of time.The connection Pooling can be customized by setting appropriate parameters in ConectionString of Connection Object.

2)Command- Command Object can be used for execution of any type of Sql Statement.
When "Select" or similar type of sql statement is executed a new cursor is created on the back end.This cursor contains primary key or indexed key of the table on which the statement is executed.There is also a pointer,which is by default positioned at BOF.The cursor and pointers functionality is encapsulated by the "DataReader" object created in the front end.Using this object,we can move the pointer in the forward direction getting one record from backend into the front-end at a time.The cursor managed by Data Reader is read-only and forward-only.

Validator Controls In ASP.Net

In ASP.NET we have 5 validator controls they are:



1)The RequiredFieldValidator Control



2)The CompareValidator Control



3)The RangeValidator Control



4)The RegularExpressionValidator Control



5)The CustomValidator Control

1)RequiredFieldValidator- This is used when the user has to enter compulsarily in the field specified.It should not be left empty.The syntax is

asp:TextBox ID="txtbox1" runat="server"

asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtbox1" ErrorMessage="TextBox cannot be left empty" Display="dynamic"

Here ErrorMessage will be displayed when the user does not enter anything in the requiredfield control, for ControlToValidate we should give the control name we want to validate, Display dynamic means that the validation error message will be displayed dynamically while submitting the form,ID is given for identifying the requiredfieldvalidator control.

2)CompareValidator- This is used when we have a requirement like comparing two textboxes values.The syntax is

asp:Textbox ID="txtbox1" runat="server"

asp:Textbox ID="txtbox2" runat="server"

asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="txtbox1"
ControlToCompare="txtbox2" Operator="LessThan" Type="Integer" ErrorMessage="txtbox1 value should be less than txtbox2 value" Display="dynamic"

Here Errormessage will be displayed dynamically and will be shown to the user when the conditions voilates,ControlToValidate we should give one field and ControlToCompare we should give other field which we want to compare,Operator means on which condition do you want to compare the 2 fields in a page like equal,lessthan,notequal,greaterthan etc., Type specifies the output should be of which type.

3)RangeValidator Control-This is used for restricting the value of a field from minimun value to maximum value.The syntax is

asp:textbox id="txtbx1" runat="server"

asp:button id="btn1" runat="server" text="Submit"

asp:RangeValidator ID="RangeValidator1" runat="server" ControlToValidate="txtbx1" MinimumValue="01-01-2007" MaximumValue="01-01-2005" ErrorMessage="Please enter a date within the 2007"(This message will be displayed if text property is not set) Text="Enter valid date"(Message to displayed when validation fails) Type="Date"

Here If the textbox entered date is valid(between the range of the minimun and maximum value given) then in the button click no errer messasge is displayed.If the value crosses the range mentioned in the validator then error message or the text is diaplayed.acording to the trype mentioned we can do the range validation like in our example it is date but we have many like -"Currency,Double,Integer,String".We dont need to type both error message and text ,either one will be enough.The coolest part of it is that the validator will respond even if the date is invalid like "31-02-2007" (we dont have 31 days in the month of feb)

Note: The blue color is for understanding purpose not there in the code.

4)RegularExpressionValidator Control-The RegularExpressionValidator control is used to ensure that an input value matches a specified pattern,the syntax is as follows

asp:textbox id="txtbox1" runat="server"

asp:RegularExpressionValidator id="Regular Expressionvalidator1" runat="server" ControlToValidate="txtbox1" Display="Dynamic" ErrorMessage="The Zip code must be 5 numeric digits" ValidationExpression="\d{5}"

Here the validator make sure that the user will enter only 5 numeric digits only else the error message is displayed. "\d{5}" means 'd' for digits and 5 means only 5 digits will be entered.

5)CustomValidator Control-The CustomValidator control allows you to write a method to handle the validation of the value entered.The developer will develop this validator control.

In .aspx page-

asp:textbox id="txtName" runat="server"

asp:CustomValidator ID="cvName" ControlToValidate="txtName" runat="server" ErrorMessage="Country with this name already exists.Please choose another name" Display="Dynamic" OnServerValidate="cvName_ServerValidate"

In aspx.cs-

protected void cvName_ServerValidate(object source, ServerValidateEventArgs args)
{
CountryDao cdao = new CountryDao();
if (cdao.IsCountryNameExists(txtCName.Text.Trim(),null))
{
args.IsValid = false;
}
else
{
args.IsValid = true;
}
}

Here IsCountryNameExists() is a method in the CountryDao

In CountryDao-

public bool IsCountryNameExists(string countryName, int? countryId)
{
bool isExists = true;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
DataSet countryDs = new DataSet();
conn.Open();
SqlCommand cmd = new SqlCommand("prcCheckCountryExists", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CountryName", DbType.String));
cmd.Parameters.Add(new SqlParameter("@CountryId", DbType.Int32));
cmd.Parameters["@CountryName"].Value = countryName;
cmd.Parameters["@CountryId"].Value = countryId;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(countryDs);
if (countryDs != null)
{
DataTable countryTb = countryDs.Tables[0];
foreach (DataRow countryRow in countryTb.Rows)
{
isExists = bool.Parse(countryRow["ISExists"].ToString());
}
}
}
catch (SqlException elex)
{
throw elex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return isExists;
}

Here prcCheckCountryExists is a stored procedure in the sqlserver which will check whether the country name added by the user is already existing in the database,if exists then error message will be displayed and if not existing that country name will be added in the database. In the manner we can code according to our requirement using the CustomValidator control.

Loading the DropDownList and putting a default value

Coding using Asp.Net with C#.Net (Web Based)

• The namespace to be used is System.Web.UI.WebControls
• The asp tag to be used is asp:DropdownList id="ddl1" runat="server" DataValueField="Id" DataTextField="Name" AutoPostBack="true" OnSelectedIndexChanged="ddl1_SelectedIndexChanged" , here datatextfield is used for the data to be displayed in the dropdownlist after loading datavaluefield is used for back end identifying the values displayed in the dropdownlist and onselectedindexchanged is an event which will be fired when a user selects an item in the list.
• In Class (aspx.cs)

protected void ddl1_SelectedIndexChanged(object sender, EventArgs e)

{

int ID = Convert.ToInt32(ddlCName.SelectedValue);

CountryDao countryDao = new CountryDao();

lvSearchMints.DataSource = countryDao.GetMint(ID);

lvSearchMints.DataBind();

}

• In .aspx page

protected void Page_Load(object sender, EventArgs e)

{

Page.DataBind();

InitializeData();

}

private void InitializeData()

{

if (!IsPostBack)

{

ddl1.DataSource = mysource;

ddl1.AppendDataBoundItems = true;

ddl1.Items.Add("Default value to be display");

ddl1.DataBind();

}

}