Create a new Class Library Project
Select New Project from File Menu option
Select your preferred project base: e.g. Visual Basic or C#
Select Class Library
Change the name of the application in the Name text box to DataDevEDM
Click OK
Add an Entity Data Model Item
In Solution Explorer, right click the project name.
Click Add on the context menu that opens.
Click New Item.
In the Add New Item dialog, click ADO.NET Entity Data Model.
Change the name from Model1.edmx to AWModel.edmx
addnewitem
Click the Add button
In the Entity Data Model Wizard click Generate from Database, then click the Next button
Create a database connection for the Entity Data Model
If you have a database connection already created in Visual Studio, skip to Define Contents of an Entity Data Model
On the Choose Your Data Connection page of the wizard, click the New Connection button.
At the top of the connection properties window, click the Change button next to the Data Source
clip_image004
Select Microsoft SQL Server Database File from the Data source list, then click OK
Click the Browse button to the right of Database file name
Browse to the folder where you have saved the AdventureWorksLT database*.
Select AdventureWorksLT.mdf then click Open.
clip_image006
At the bottom of the Connection Properties window, click OK
Define the contents of an Entity Data Model
Select the database connection from the drop down.
At the bottom of the Entity Data Model Wizard window, change the value of Entity connection settings to AWEntities then click Next.
clip_image008
On the next page, change the Model Namespace to AWModel.
Check Tables, Views and Stored Procedures.
This will include all of the items. You can expand these nodes to select only the specific tables, views and stored procedures that you want.
clip_image010
Click Finish.
Note that in the solution explorer, a new file, AWModel.edmx has been created.
explorer
View the model
Double click the AWModel.emx file to open the model in the Entity Data Model designer.
model
Create an Entity Data Model using the Entity Data Model Wizard (for assistance with this step, see the Create Entity Data Model Tutorial)
Note: Be sure to include the stored procedures when selecting which database objects to include in the model.
sproc1
Right click the Customer entity and choose "Stored Procedure Mapping" from the menu.
sproc2
Here you will see options for mapping an Insert function, an Update function and a Delete function.
sproc4
(Note: The Stored Procedure Mappings are one of the two pages of the Mapping Details view. The top icon on the left will bring you to the Table Mappings for the entity.)
Use the drop down menu for Select Insert Function, choose the InsertCustomer procedure.
sproc5
After selecting the matching stored procedure, you will have the ability to map the required parameters for the stored procedure to the properties of the entity. By default, names that match will be mapped to each other already. The benefit of mapping the stored procedure parameters to the entity properties is that you will not need to write code to feed the correct pieces of data to the stored procedure; Entity Framework will use these mappings to do that task.
Type NewCustomerID property into the Result Column Bindings and hook it up to the CustomerID property.
The stored procedure returns the new ID for the inserted row. This mapping ensures that the new id is pushed back into the entity that the user is working with.
sproc6
Map the Update and Delete functions. As with the insert function, the tool will automatically map the matching column and property names.
When all of the stored procedures are mapped, the Mapping Details will look like this:
sproc7
Two important notes about using the function mappings.
1) Function mappings require that you map all three of the funcitons, Insert, Update and Delete. If you only map one or two of the functions, the model will not validate.
2) Entities with Navigation Properties that are Entity References (eg SalesOrderHeader.Customer is an Entity Reference, while SalesOrderHeader.SalesOrderDetails is an Entity Collection), will have special requirements for mapping the foreign keys. In an Insert or Update procedure, this is pretty straight forward, but in a Delete procedure, it may require adding parameters to the procedure tha tyou do not use in the procedure itself, but are there only to satisfy the model's rules. See this blog post for more info: Delete Stored Procs And Navigations In the Entity Data Model
Using the new stored procedures
Now that the stored procedures have been mapped, it is not necessary to call them directly in code. Any time SaveChanges is called, Entity Framework will use your mapped stored procedures for any required inserts, updates and deletes.
TSQL for creating the three stored procs in AdventureWorksLT.
INSERT
USE [AdventureWorksLT]
CREATE Procedure [dbo].[InsertCustomer]
@NameStyle bit,
@Title nvarchar(8),
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarchar(50),
@Suffix nvarchar(10),
@CompanyName nvarchar(128),
@SalesPerson nvarchar(256),
@EmailAddress nvarchar(50),
@Phone nvarchar(25),
@PasswordHash varchar(128),
@PasswordSalt varchar(10),
@ModifiedDate datetime,
@Inactive bit
AS
INSERT INTO [AdventureWorksLT].[SalesLT].[Customer]
([NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[CompanyName]
,[SalesPerson]
,[EmailAddress]
,[Phone]
,[PasswordHash]
,[PasswordSalt]
,[ModifiedDate]
,[Inactive])
VALUES
(
@NameStyle,
@Title,
@FirstName ,
@MiddleName,
@LastName ,
@Suffix ,
@CompanyName ,
@SalesPerson ,
@EmailAddress,
@Phone,
@PasswordHash,
@PasswordSalt,
@ModifiedDate,
@Inactive )
select SCOPE_IDENTITY() as NewCustomerID
UPDATE
USE [AdventureWorksLT]
CREATE PROCEDURE [dbo].[UpdateCustomer]
@CustomerID int,
@NameStyle bit,
@Title nvarchar(8),
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarchar(50),
@Suffix nvarchar(10),
@CompanyName nvarchar(128),
@SalesPerson nvarchar(256),
@EmailAddress nvarchar(50),
@Phone nvarchar(25),
@PasswordHash varchar(128),
@PasswordSalt varchar(10),
@ModifiedDate datetime,
@Inactive bit
AS
UPDATE [AdventureWorksLT].[SalesLT].[Customer]
SET [NameStyle] = @NameStyle,
[Title] = @Title,
[FirstName] = @FirstName,
[MiddleName] = @MiddleName,
[LastName] = @LastName,
[Suffix] = @Suffix,
[CompanyName] = @CompanyName,
[SalesPerson] = @SalesPerson,
[EmailAddress] = @EmailAddress,
[Phone] = @Phone,
[PasswordHash] = @PasswordHash,
[PasswordSalt] = @PasswordSalt,
[ModifiedDate] = @ModifiedDate,
[Inactive] = @Inactive
WHERE CustomerID=@CUstomerID
DELETE
USE [AdventureWorksLT]
CREATE PROCEDURE [dbo].[DeleteCustomer]
@CustomerID int
AS
DELETE FROM Customer
WHERE CustomerID=@CUstomerID
Friday 30 December 2011
Thursday 29 December 2011
ASP.NET - Refresh Part of a Page
We often come across a scenario where we need to update part of a page. We can use IFrame to refresh part of a page.
We can achieve this using Ajax also but here I am discussing how we can achieve without using Ajax.
Step 1. Add a page in the project with name “MyPage.aspx”, copy and paste below code in the aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyPage.aspx.cs" Inherits="MyPage" %>Refresh part of page
The below line of code in the head section of the above code will refresh the page after every 5 second.
Step 2: Put below bold and italic lines of code in the Page_Load method of MyPage.aspx page, this line will refresh the time on each refresh of the page.
protected void Page_Load(object sender, EventArgs e) { Label1.Text = "This Page has been refrested at " + DateTime.Now.ToString(); }
Step 3. Now add another page in the project with name “Refresh.aspx”, copy and paste below code in aspx page of Refresh.aspx.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Refresh.aspx.cs" Inherits="Refresh" %>Untitled Page
I have added an IFrame to achieve the goal of part refresh of the page. I have added src property and set it to MyPage.aspx which I have created in step 1.
I have also put a label outside the iframe to show the Date and time when Refresh.aspx page will load.
Step 4: Copy and paste below bold line of code on Page_Load methodprotected void Page_Load(object sender, EventArgs e)
{ Label1.Text = DateTime.Now.ToString(); }
Now run Refresh.aspx page, you will notice that the time on the Refresh.aspx won’t change but the time on MyPage.aspx will change after every 5 seconds
Live Demo Like us if you find this post useful. Thanks!
Download Code
We can achieve this using Ajax also but here I am discussing how we can achieve without using Ajax.
Step 1. Add a page in the project with name “MyPage.aspx”, copy and paste below code in the aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MyPage.aspx.cs" Inherits="MyPage" %>
The below line of code in the head section of the above code will refresh the page after every 5 second.
Step 2: Put below bold and italic lines of code in the Page_Load method of MyPage.aspx page, this line will refresh the time on each refresh of the page.
protected void Page_Load(object sender, EventArgs e) { Label1.Text = "This Page has been refrested at " + DateTime.Now.ToString(); }
Step 3. Now add another page in the project with name “Refresh.aspx”, copy and paste below code in aspx page of Refresh.aspx.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Refresh.aspx.cs" Inherits="Refresh" %>
I have added an IFrame to achieve the goal of part refresh of the page. I have added src property and set it to MyPage.aspx which I have created in step 1.
I have also put a label outside the iframe to show the Date and time when Refresh.aspx page will load.
Step 4: Copy and paste below bold line of code on Page_Load methodprotected void Page_Load(object sender, EventArgs e)
{ Label1.Text = DateTime.Now.ToString(); }
Now run Refresh.aspx page, you will notice that the time on the Refresh.aspx won’t change but the time on MyPage.aspx will change after every 5 seconds
Live Demo Like us if you find this post useful. Thanks!
Download Code
jQuery - Disable right mouse click on the page
In this article we will explore how to disable right click of mouse button when javascript is enabled. We will also find out the way to inform user whether javascript is enabled or disabled. If javascript is disabled right click will work otherwise it won't work.
Let's write some code to achieve it.
Step 1: Include jquery in the head section of aspx page.
Step 2: Place noscript tag along with the content in the body tag. noscript tag runs only when javascript is disabled.
Step 3: Place the below div inside the body tag. It will be hidden and will be displayed only if javascript is enabled.
Step 4: Place below javascript to display above div and disable right click of mouse.
Live Demo Like us if you find this post useful. Thanks!
Let's write some code to achieve it.
Step 1: Include jquery in the head section of aspx page.
Step 2: Place noscript tag along with the content in the body tag. noscript tag runs only when javascript is disabled.
Step 3: Place the below div inside the body tag. It will be hidden and will be displayed only if javascript is enabled.
Step 4: Place below javascript to display above div and disable right click of mouse.
Live Demo Like us if you find this post useful. Thanks!
Get checkboxlist text using javascript
In this article we will explore how to get checkbox text in javascript. CheckBoxList text property lays out the text as html lable in the page.
CheckBox Text
Let's see how we can achieve it:
Step 1: Place below html fragment inside form tag of aspx page.
Selected checkbox value:
Step 2: Place below code snippet in page_load to bind data to checkboxlist.
ListItem lst;
string text;
for (int i = 0; i < 10; i++) { text = i.ToString() + " Checkbox in DDL"; lst = new ListItem(text, i.ToString()); chkList.Items.Add(lst); } Step 3: Now place below javascript in the aspx page.
Live Demo
This ends the article of getting text of checkboxlist using javascript.
Like us if you find this post useful. Thanks!
CheckBox Text
Let's see how we can achieve it:
Step 1: Place below html fragment inside form tag of aspx page.
Selected checkbox value:
Step 2: Place below code snippet in page_load to bind data to checkboxlist.
ListItem lst;
string text;
for (int i = 0; i < 10; i++) { text = i.ToString() + " Checkbox in DDL"; lst = new ListItem(text, i.ToString()); chkList.Items.Add(lst); } Step 3: Now place below javascript in the aspx page.
Live Demo
This ends the article of getting text of checkboxlist using javascript.
Like us if you find this post useful. Thanks!
Inline Editing of GridView Cell like YUI
In this article I will explain how to do inline editing of a cell in gridview like YUI. Onclick of any cell the control associated with that cell will open along with Save and Cancel. I have used textbox, radio button and dropdownlist control.
GridView Inline Editing of Cell
GridView Inline Editing of Cell
GridView Inline Editing of Cell
Let's see how we can achieve this in gridview.
Step 1: Add scriptmanager in the aspx page.
Step 2: Add a gridview in the update panel.
Step 3: Add three divs inside the same update panel which holds gridview, first to place textbox along with save and cancel button, second to place dropdownlist along with save and cancel button and third to place radiobutton along with save and cancel button.
Step 4: Add a hiddenfield in the aspx page which will hold the employeeid of the cell being edited.
Step 5: Add below javascript in the aspx page. EditCell method will be used to get the employeeid of the corresponding cell which has been clicked, celText will be used to populate the corresponding control of the cell, ctrlType will decide which div needs to be visible, rowNo and colNo will be used to position the div.
HideDDLDiv will hide the div containing dropdownlist. HideRdBDiv will hide the div containing radiobutton and HideTextDiv will hide the div containing text box.
Step 6: Create a method GetData() which will return datatable to bind with gridview.
private DataTable GetData(string strQuery)
{
DataTable dtDept = null;
SqlConnection con = GetConnection();
using (con)
{
con.Open();
using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strQuery, con))
{
dtDept = new DataTable();
sqlAdapter.Fill(dtDept);
}
}
return dtDept;
}
private SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=Speaks;Integrated Security=SSPI");
return con;
}
Step 7: Bind the data returned GetData() to gridview on !IsPostBack of page load.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string strQuery = "SELECT * FROM employee";
gvEmployee.DataSource = GetData(strQuery);
gvEmployee.DataBind();
}
Step 8: In RowDataBound event each cell needs to be bind with EditCell javascript method. The kind of control needs to be visible on click of EditCell has to be decided in rowdatabound based on the column.
protected void gvEmployee_RowDataBound(Object sender, GridViewRowEventArgs e)
{
string ctrlType = string.Empty;
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int i = 1; i < gvEmployee.Columns.Count; i++)
{
switch (i)
{
case 1:
ctrlType = "TextBox";
break;
case 2:
ctrlType = "RadioButton";
break;
case 3:
ctrlType = "DropDownList";
break;
}
DataKey dk = gvEmployee.DataKeys[e.Row.DataItemIndex];
e.Row.Cells[i].Attributes.Add("onclick", "EditCell('" + dk.Values["EmployeeId"].ToString() + "','" + e.Row.Cells[i].Text + "','" + ctrlType + "','" + (e.Row.RowIndex + 1) + "','" + i + "');");
}
}
}
Step 9: Now we need to place three button events, first is associated with first column of gridview, second is associated with second coulmn of gridview and thrid is associated with third column of gridview.
protected void btnTextSave_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET employeeName = '" + txtEmployeeName.Text + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
protected void btnRdB_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET Designation = '" + rdDesignation.SelectedValue + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
protected void btnDDLSave_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET Location = '" + ddlLocation.SelectedValue + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
Step 10: At last we need to place UpdateTable method which will be invoked by the above three button click methods.
private void UpdateTable(string strQuery)
{
string m_conString = CryptographyHelper.Decrypt(ConfigurationSettings.AppSettings["DBConnectionString"]);
SqlConnection con = GetConnection(m_conString);
using (con)
{
con.Open();
SqlCommand cmd = new SqlCommand(strQuery, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
Live Demo
This ends the article of inline editing of a cell in gridview like YUI.
Like us if you find this post useful. Thanks!
Download Code
GridView Inline Editing of Cell
GridView Inline Editing of Cell
GridView Inline Editing of Cell
Let's see how we can achieve this in gridview.
Step 1: Add scriptmanager in the aspx page.
Step 2: Add a gridview in the update panel.
Step 3: Add three divs inside the same update panel which holds gridview, first to place textbox along with save and cancel button, second to place dropdownlist along with save and cancel button and third to place radiobutton along with save and cancel button.
Step 4: Add a hiddenfield in the aspx page which will hold the employeeid of the cell being edited.
Step 5: Add below javascript in the aspx page. EditCell method will be used to get the employeeid of the corresponding cell which has been clicked, celText will be used to populate the corresponding control of the cell, ctrlType will decide which div needs to be visible, rowNo and colNo will be used to position the div.
HideDDLDiv will hide the div containing dropdownlist. HideRdBDiv will hide the div containing radiobutton and HideTextDiv will hide the div containing text box.
Step 6: Create a method GetData() which will return datatable to bind with gridview.
private DataTable GetData(string strQuery)
{
DataTable dtDept = null;
SqlConnection con = GetConnection();
using (con)
{
con.Open();
using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strQuery, con))
{
dtDept = new DataTable();
sqlAdapter.Fill(dtDept);
}
}
return dtDept;
}
private SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=Speaks;Integrated Security=SSPI");
return con;
}
Step 7: Bind the data returned GetData() to gridview on !IsPostBack of page load.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string strQuery = "SELECT * FROM employee";
gvEmployee.DataSource = GetData(strQuery);
gvEmployee.DataBind();
}
Step 8: In RowDataBound event each cell needs to be bind with EditCell javascript method. The kind of control needs to be visible on click of EditCell has to be decided in rowdatabound based on the column.
protected void gvEmployee_RowDataBound(Object sender, GridViewRowEventArgs e)
{
string ctrlType = string.Empty;
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int i = 1; i < gvEmployee.Columns.Count; i++)
{
switch (i)
{
case 1:
ctrlType = "TextBox";
break;
case 2:
ctrlType = "RadioButton";
break;
case 3:
ctrlType = "DropDownList";
break;
}
DataKey dk = gvEmployee.DataKeys[e.Row.DataItemIndex];
e.Row.Cells[i].Attributes.Add("onclick", "EditCell('" + dk.Values["EmployeeId"].ToString() + "','" + e.Row.Cells[i].Text + "','" + ctrlType + "','" + (e.Row.RowIndex + 1) + "','" + i + "');");
}
}
}
Step 9: Now we need to place three button events, first is associated with first column of gridview, second is associated with second coulmn of gridview and thrid is associated with third column of gridview.
protected void btnTextSave_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET employeeName = '" + txtEmployeeName.Text + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
protected void btnRdB_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET Designation = '" + rdDesignation.SelectedValue + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
protected void btnDDLSave_Click(object sender, EventArgs e)
{
string empID = hidEmployeeId.Value;
string strQuery = "UPDATE employee SET Location = '" + ddlLocation.SelectedValue + "' WHERE EmployeeID = " + hidEmployeeId.Value;
UpdateTable(strQuery);
BindGrid();
}
Step 10: At last we need to place UpdateTable method which will be invoked by the above three button click methods.
private void UpdateTable(string strQuery)
{
string m_conString = CryptographyHelper.Decrypt(ConfigurationSettings.AppSettings["DBConnectionString"]);
SqlConnection con = GetConnection(m_conString);
using (con)
{
con.Open();
SqlCommand cmd = new SqlCommand(strQuery, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
Live Demo
This ends the article of inline editing of a cell in gridview like YUI.
Like us if you find this post useful. Thanks!
Download Code
Wrapping column in gridview using CSS style
I used ITemplate to wrap column which gives exact word wrap in column on every browser. One can do it with style also but results are not same in all the browsers. If little difference in result variance in different browser is not an issue then wrapping column using style is much easier than using ITemplate.
Output of wrapping column in gridview using CSS in different browser.
IE 9 - Word Break Chrome - Word Break
Firefox 4 - Word Break Safari- Word Break
Let's see how we can do this.
Step 1: Place a gridview inside form tag.
Step 2: Place below lines of code to bind the data to gridview.
protected void Page_Load(object sender, EventArgs e){ gvWrappinColumn.DataSource = GetData(); gvWrappinColumn.DataBind();}
private DataTable GetData()
{ DataTable dt = new DataTable("Data");
dt.Columns.Add(new DataColumn("ID"));
dt.Columns.Add(new DataColumn("Comments"));
dt.Rows.Add(1,"Testing column of GridView Wrapping1");
dt.Rows.Add(2,"http://www.bing.com/search?q=microtsoft+visual+studio+2010&form=QBLH&qs=n&sk=");
dt.Rows.Add(3,"Testing column of GridView Wrapping2"); return dt;
}
Step 3: Apply word-break:break-all and word-wrap:break-word css in the column. Refer Word-Wrap and Word-Break. To set the minimum width of a column using Unit.
protected void gvWrappinColumn_RowDataBound(object sender, GridViewRowEventArgs e)
{ if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Cells[1].Attributes.Add("style", "word-break:break-all;word-wrap:break-word"); }}
protected void gvWrappinColumn_RowCreated(object sender, GridViewRowEventArgs e)
{ this.gvWrappinColumn.Columns[1].ItemStyle.Width = new Unit(200);
}
Live Demo
This ends the article of wrapping column in gridview using css style.
Like us if you find this post useful. Thanks!
Download Code
Output of wrapping column in gridview using CSS in different browser.
IE 9 - Word Break Chrome - Word Break
Firefox 4 - Word Break Safari- Word Break
Let's see how we can do this.
Step 1: Place a gridview inside form tag.
Step 2: Place below lines of code to bind the data to gridview.
protected void Page_Load(object sender, EventArgs e){ gvWrappinColumn.DataSource = GetData(); gvWrappinColumn.DataBind();}
private DataTable GetData()
{ DataTable dt = new DataTable("Data");
dt.Columns.Add(new DataColumn("ID"));
dt.Columns.Add(new DataColumn("Comments"));
dt.Rows.Add(1,"Testing column of GridView Wrapping1");
dt.Rows.Add(2,"http://www.bing.com/search?q=microtsoft+visual+studio+2010&form=QBLH&qs=n&sk=");
dt.Rows.Add(3,"Testing column of GridView Wrapping2"); return dt;
}
Step 3: Apply word-break:break-all and word-wrap:break-word css in the column. Refer Word-Wrap and Word-Break. To set the minimum width of a column using Unit.
protected void gvWrappinColumn_RowDataBound(object sender, GridViewRowEventArgs e)
{ if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Cells[1].Attributes.Add("style", "word-break:break-all;word-wrap:break-word"); }}
protected void gvWrappinColumn_RowCreated(object sender, GridViewRowEventArgs e)
{ this.gvWrappinColumn.Columns[1].ItemStyle.Width = new Unit(200);
}
Live Demo
This ends the article of wrapping column in gridview using css style.
Like us if you find this post useful. Thanks!
Download Code
.NET 4.0 - Set style of all the disable control using aspNetDisabled style
.NET 4.0 has feature of disable state, it is a CSS property which will apply style sheet on all disabled controls. The best thing about this feature is that there is no need to assign style to individual control, It gets applied to all disabled controls.
Step 1: Add a textbox, a button, a dropdownlist which will be enabled and disabled. Two more buttons which will enable and disable the textbox, button and dropdownlist.
Step 2: Add btnDisable_Click and btnDisable_Click method which will disable and enable the controls respectively.
protected void btnDisable_Click(object sender, EventArgs e)
{
TextBox1.Text = "Disabled";
TextBox1.Enabled = false;
Button1.Enabled = false;
DropDownList1.SelectedIndex = 1;
DropDownList1.Enabled = false;
}
protected void btnEnable_Click(object sender, EventArgs e)
{
TextBox1.Text = "Enabled";
TextBox1.Enabled = true;
Button1.Enabled = true;
DropDownList1.SelectedIndex = 0;
DropDownList1.Enabled = true;
}
Step 3: Add aspNetDisabled in Site.css. Make sure it should be aspNetDisabled.
.aspNetDisabled{
color:Yellow;
background-color:Maroon;
font-style:italic;
}
Now run the applicaiton, click on Disable button aspNetDisabled style will be applied to all the disbled controls without writing a single line of code.
Enabled Controls:
Disabled State - Enable
Disabled Controls:
Disabled State - Disable Like us if you find this post useful. Thanks!
Download Code
Step 1: Add a textbox, a button, a dropdownlist which will be enabled and disabled. Two more buttons which will enable and disable the textbox, button and dropdownlist.
Step 2: Add btnDisable_Click and btnDisable_Click method which will disable and enable the controls respectively.
protected void btnDisable_Click(object sender, EventArgs e)
{
TextBox1.Text = "Disabled";
TextBox1.Enabled = false;
Button1.Enabled = false;
DropDownList1.SelectedIndex = 1;
DropDownList1.Enabled = false;
}
protected void btnEnable_Click(object sender, EventArgs e)
{
TextBox1.Text = "Enabled";
TextBox1.Enabled = true;
Button1.Enabled = true;
DropDownList1.SelectedIndex = 0;
DropDownList1.Enabled = true;
}
Step 3: Add aspNetDisabled in Site.css. Make sure it should be aspNetDisabled.
.aspNetDisabled{
color:Yellow;
background-color:Maroon;
font-style:italic;
}
Now run the applicaiton, click on Disable button aspNetDisabled style will be applied to all the disbled controls without writing a single line of code.
Enabled Controls:
Disabled State - Enable
Disabled Controls:
Disabled State - Disable Like us if you find this post useful. Thanks!
Download Code
jQuery - Change blurred image on mouseover
In this article we will see how we can change blurred image on mouse over using jQuery.
jQuery Blurred Image
Let's see how we can do this.
Step 1: Add jQuery jquery-1.4.2.min.js in the header section of the page.
Step 2: Place below HTML in the aspx page.
Step 3: Place below script in the aspx page which will make all the images blurred on the page load. On mouseover the blurred imaged
$(document).ready(function() {
//To make all the images blurred on page load
$('img.imgThNail').css('opacity', 0.4);
//Turn off the blur on mouseover
$("img.imgThNail").mouseover(function(e) {
$(this).css('opacity', 1);
});
//Turn on back the blur on mouseout
$("img.imgThNail").mouseout(function(e) {
$(this).css('opacity', 0.4);
});
document.onclick = check;
function check(e) {
$("#sourceDiv").css({
display: 'none'
});
}
});
Live Demo
jQuery Blurred Image
Let's see how we can do this.
Step 1: Add jQuery jquery-1.4.2.min.js in the header section of the page.
Step 2: Place below HTML in the aspx page.
Step 3: Place below script in the aspx page which will make all the images blurred on the page load. On mouseover the blurred imaged
$(document).ready(function() {
//To make all the images blurred on page load
$('img.imgThNail').css('opacity', 0.4);
//Turn off the blur on mouseover
$("img.imgThNail").mouseover(function(e) {
$(this).css('opacity', 1);
});
//Turn on back the blur on mouseout
$("img.imgThNail").mouseout(function(e) {
$(this).css('opacity', 0.4);
});
document.onclick = check;
function check(e) {
$("#sourceDiv").css({
display: 'none'
});
}
});
Live Demo
jQuery Validator
In this article we will see how to create validation framework in jQuery. I tried to do create validator similar to ASP.NET.
Let's see how we can do this.
Step 1: Place two textboxes (First Name and Last Name) which will accept only alphabet, a dropdownlist, another textbox (Age) whic will accept only integers, another textbox (Address) which will accept alphanumeric dharacters.
All are mandatory except address field. Different type of validation is required in different textboxes.
Step 2: Place below style in the header section of the page. There are few style which doesn't have and style property in it. It will be used to decide which validation need to be performed on any field.
Step 3: Add jquery reference in the header section of the page.
Step 4: Place below javascript in the page to execute validation on the contols.
Live Demo
Like us if you find this post useful. Thanks!
Download Code
Let's see how we can do this.
Step 1: Place two textboxes (First Name and Last Name) which will accept only alphabet, a dropdownlist, another textbox (Age) whic will accept only integers, another textbox (Address) which will accept alphanumeric dharacters.
All are mandatory except address field. Different type of validation is required in different textboxes.
First Name *
Last Name *
Gender *
Age *
Address
Step 2: Place below style in the header section of the page. There are few style which doesn't have and style property in it. It will be used to decide which validation need to be performed on any field.
Step 3: Add jquery reference in the header section of the page.
Step 4: Place below javascript in the page to execute validation on the contols.
Live Demo
Like us if you find this post useful. Thanks!
Download Code
jQuery-Change image on mouseover
In this article we will explore how to change image on mouse over using jQuery
Change Image on Mouse Over
Let's start writing the example.
Step 1: Add jquery-1.4.2.min.js and style in the header section of the aspx page.
Step 2: Add a div with two asp.net image control. First image control will be used to show processing while image is getting loaded. Second image control will be used to load the image.
Step 3: Add a div which will contain all the thumbnails. Reduce the size of thumbnail image to load it faster. I have reduced the size of the thumbnail images and postfixed it by "-s".
Step 4: Add below javascript in the aspx page. Onmouseover i am removing "-s" from the thumbnail image name to load the bigger image which is without "-s"
Live Demo
This ends the article of change image on mouse over.
Like us if you find this post useful. Thanks!
Download Code
Change Image on Mouse Over
Let's start writing the example.
Step 1: Add jquery-1.4.2.min.js and style in the header section of the aspx page.
Step 2: Add a div with two asp.net image control. First image control will be used to show processing while image is getting loaded. Second image control will be used to load the image.
Step 3: Add a div which will contain all the thumbnails. Reduce the size of thumbnail image to load it faster. I have reduced the size of the thumbnail images and postfixed it by "-s".
Step 4: Add below javascript in the aspx page. Onmouseover i am removing "-s" from the thumbnail image name to load the bigger image which is without "-s"
Live Demo
This ends the article of change image on mouse over.
Like us if you find this post useful. Thanks!
Download Code
DropDownList Having CheckBoxes
Step 2: Add below lines of code on page load.
protected void Page_Load(object sender, EventArgs e)
{
DropDownList ddl = new DropDownList();
ddl.ID = "ddlChkList";
ListItem lstItem = new ListItem();
ddl.Items.Insert(0, lstItem);
ddl.Width = new Unit(155);
ddl.Attributes.Add("onmousedown", "showdivonClick()");
CheckBoxList chkBxLst = new CheckBoxList();
chkBxLst.ID = "chkLstItem";
chkBxLst.Attributes.Add("onmouseover", "showdiv()");
DataTable dtListItem = GetListItem();
int rowNo = dtListItem.Rows.Count;
string lstValue = string.Empty;
string lstID = string.Empty;
for (int i = 0; i < rowNo - 1; i++) { lstValue = dtListItem.Rows[i]["Value"].ToString(); lstID = dtListItem.Rows[i]["ID"].ToString(); lstItem = new ListItem("" + lstValue + "", dtListItem.Rows[i]["ID"].ToString());
lstItem.Attributes.Add("onclick", "getSelectedItem('" + lstValue + "','" + i + "','" + lstID + "','listItem');");
chkBxLst.Items.Add(lstItem);
}
System.Web.UI.HtmlControls.HtmlGenericControl div = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
div.ID = "divChkList";
div.Controls.Add(chkBxLst);
div.Style.Add("border", "black 1px solid");
div.Style.Add("width", "160px");
div.Style.Add("height", "180px");
div.Style.Add("overflow", "AUTO");
div.Style.Add("display", "none");
phDDLCHK.Controls.Add(ddl);
phDDLCHK.Controls.Add(div);
}
Step 3:
Place below javascript method in the aspx page or you can place it in .js file and include it in the page.
showdiv method will be called on mouse over of div.
showdivonClick will be invoked on click of dropdownlist.
getSelectedItem will be called on click of checkbox and anchor.
check will be call on any click on the page basically it is used to hide the div on click of any where on the page apart from div.
Step 4: btn_Click method will be used to get to get the selected checkbox status in the dropdownlist.
protected void btn_Click(object sender, EventArgs e)
{
string strSelectedItem = string.Empty;
CheckBoxList chk = (CheckBoxList)phDDLCHK.FindControl("chkLstItem");
DropDownList ddl = (DropDownList)Page.FindControl("ddlChkList");
for (int i = 0; i < chk.Items.Count; i++)
{
if (chk.Items[i].Selected)
{
if (strSelectedItem.Length == 0)
{
strSelectedItem = chk.Items[i].Selected.ToString();
}
else
{
strSelectedItem = strSelectedItem + "," + chk.Items[i].Selected.ToString();
}
}
}
ddl.Items.Clear();
ddl.Items.Add(new ListItem(hidList.Value));
lblSelectedItem.Text = strSelectedItem;
}
Step 5: Now add a method to get datatable which will be bind to checkboxlist.
public DataTable GetListItem()
{
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Value", typeof(string));
table.Rows.Add(1, "ListItem1");
table.Rows.Add(2, "ListItem2");
table.Rows.Add(3, "ListItem3");
table.Rows.Add(4, "My ListItem Wraps also");
table.Rows.Add(5, "My New ListItem5");
table.Rows.Add(6, "ListItem6");
table.Rows.Add(7, "ListItem7");
table.Rows.Add(8, "ListItem8");
return table;
}
Live Demo
How to: Return or Skip Elements in a Sequence (LINQ to SQL)
Use the Take(Of TSource) operator to return a given number of elements in a sequence and then skip over the remainder.
Use the Skip(Of TSource) operator to skip over a given number of elements in a sequence and then return the remainder.
NoteNote
Take(Of TSource) and Skip(Of TSource) have certain limitations when they are used in queries against SQL Server 2000. For more information, see the "Skip and Take Exceptions in SQL Server 2000" entry in Troubleshooting (LINQ to SQL).
LINQ to SQL translates Skip(Of TSource) by using a subquery with the SQL NOT EXISTS clause. This translation has the following limitations:
The argument must be a set. Multisets are not supported, even if ordered.
The generated query can be much more complex than the query generated for the base query on which Skip(OfTSource) is applied. This complexity can cause decrease in performance or even a time-out.
Example
The following example uses Take to select the first five Employees hired. Note that the collection is first sorted byHireDate.
C#
VB
IQueryable firstHiredQuery =
(from emp in db.Employees
orderby emp.HireDate
select emp)
.Take(5);
foreach (Employee empObj in firstHiredQuery)
{
Console.WriteLine("{0}, {1}", empObj.EmployeeID,
empObj.HireDate);
}
The following example uses Skip(Of TSource) to select all except the 10 most expensive Products.
C#
VB
IQueryable lessExpensiveQuery =
(from prod in db.Products
orderby prod.UnitPrice descending
select prod)
.Skip(10);
foreach (Product prodObj in lessExpensiveQuery)
{
Console.WriteLine(prodObj.ProductName);
}
The following example combines the Skip(Of TSource) and Take(Of TSource) methods to skip the first 50 records and then return the next 10.
C#
VB
var custQuery2 =
(from cust in db.Customers
orderby cust.ContactName
select cust)
.Skip(50).Take(10);
foreach (var custRecord in custQuery2)
{
Console.WriteLine(custRecord.ContactName);
}
Take(Of TSource) and Skip(Of TSource) operations are well defined only against ordered sets. The semantics for unordered sets or multisets is undefined.
Because of the limitations on ordering in SQL, LINQ to SQL tries to move the ordering of the argument of the Take(OfTSource) or Skip(Of TSource) operator to the result of the operator.
Note Note
Translation is different for SQL Server 2000 and SQL Server 2005. If you plan to use Skip(Of TSource) with a query of any complexity, use SQL Server 2005.
Consider the following LINQ to SQL query for SQL Server 2000:
C#
VB
IQueryable custQuery3 =
(from custs in db.Customers
where custs.City == "London"
orderby custs.CustomerID
select custs)
.Skip(1).Take(1);
foreach (var custObj in custQuery3)
{
Console.WriteLine(custObj.CustomerID);
}
LINQ to SQL moves the ordering to the end in the SQL code, as follows:
SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName],
FROM [Customers] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 1 [t1].[CustomerID]
FROM [Customers] AS [t1]
WHERE [t1].[City] = @p0
ORDER BY [t1].[CustomerID]
) AS [t2]
WHERE [t0].[CustomerID] = [t2].[CustomerID]
))) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]
When Take(Of TSource) and Skip(Of TSource) are chained together, all the specified ordering must be consistent. Otherwise, the results are undefined.
For non-negative, constant integral arguments based on the SQL specification, both Take(Of TSource) and Skip(OfTSource) are well-defined.
hostgator coupon codes
Use the Skip(Of TSource) operator to skip over a given number of elements in a sequence and then return the remainder.
NoteNote
Take(Of TSource) and Skip(Of TSource) have certain limitations when they are used in queries against SQL Server 2000. For more information, see the "Skip and Take Exceptions in SQL Server 2000" entry in Troubleshooting (LINQ to SQL).
LINQ to SQL translates Skip(Of TSource) by using a subquery with the SQL NOT EXISTS clause. This translation has the following limitations:
The argument must be a set. Multisets are not supported, even if ordered.
The generated query can be much more complex than the query generated for the base query on which Skip(OfTSource) is applied. This complexity can cause decrease in performance or even a time-out.
Example
The following example uses Take to select the first five Employees hired. Note that the collection is first sorted byHireDate.
C#
VB
IQueryable
(from emp in db.Employees
orderby emp.HireDate
select emp)
.Take(5);
foreach (Employee empObj in firstHiredQuery)
{
Console.WriteLine("{0}, {1}", empObj.EmployeeID,
empObj.HireDate);
}
The following example uses Skip(Of TSource) to select all except the 10 most expensive Products.
C#
VB
IQueryable
(from prod in db.Products
orderby prod.UnitPrice descending
select prod)
.Skip(10);
foreach (Product prodObj in lessExpensiveQuery)
{
Console.WriteLine(prodObj.ProductName);
}
The following example combines the Skip(Of TSource) and Take(Of TSource) methods to skip the first 50 records and then return the next 10.
C#
VB
var custQuery2 =
(from cust in db.Customers
orderby cust.ContactName
select cust)
.Skip(50).Take(10);
foreach (var custRecord in custQuery2)
{
Console.WriteLine(custRecord.ContactName);
}
Take(Of TSource) and Skip(Of TSource) operations are well defined only against ordered sets. The semantics for unordered sets or multisets is undefined.
Because of the limitations on ordering in SQL, LINQ to SQL tries to move the ordering of the argument of the Take(OfTSource) or Skip(Of TSource) operator to the result of the operator.
Note Note
Translation is different for SQL Server 2000 and SQL Server 2005. If you plan to use Skip(Of TSource) with a query of any complexity, use SQL Server 2005.
Consider the following LINQ to SQL query for SQL Server 2000:
C#
VB
IQueryable
(from custs in db.Customers
where custs.City == "London"
orderby custs.CustomerID
select custs)
.Skip(1).Take(1);
foreach (var custObj in custQuery3)
{
Console.WriteLine(custObj.CustomerID);
}
LINQ to SQL moves the ordering to the end in the SQL code, as follows:
SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName],
FROM [Customers] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 1 [t1].[CustomerID]
FROM [Customers] AS [t1]
WHERE [t1].[City] = @p0
ORDER BY [t1].[CustomerID]
) AS [t2]
WHERE [t0].[CustomerID] = [t2].[CustomerID]
))) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]
When Take(Of TSource) and Skip(Of TSource) are chained together, all the specified ordering must be consistent. Otherwise, the results are undefined.
For non-negative, constant integral arguments based on the SQL specification, both Take(Of TSource) and Skip(OfTSource) are well-defined.
hostgator coupon codes
How to: Join by Using Composite Keys (C# Programming Guide)
This example shows how to perform join operations in which you want to use more than one key to define a match. This is accomplished by using a composite key. You create a composite key as an anonymous type or named typed with the values that you want to compare. If the query variable will be passed across method boundaries, use a named type that overrides Equals and GetHashCode for the key. The names of the properties, and the order in which they occur, must be identical in each key.
Example
The following example demonstrates how to use a composite key to join data from three tables:
var query = from o in db.Orders
from p in db.Products
join d in db.OrderDetails
on new {o.OrderID, p.ProductID} equals new {d.OrderID,
d.ProductID} into details
from d in details
select new {o.OrderID, p.ProductID, d.UnitPrice};
Type inference on composite keys depends on the names of the properties in the keys, and the order in which they occur. If the properties in the source sequences do not have the same names, you must assign new names in the keys. For example, if the Orders table and OrderDetails table each used different names for their columns, you could create composite keys by assigning identical names in the anonymous types:
join...on new {Name = o.CustomerName, ID = o.CustID} equals
new {Name = d.CustName, ID = d.CustID }
Composite keys can be also used in a group clause.
Example
The following example demonstrates how to use a composite key to join data from three tables:
var query = from o in db.Orders
from p in db.Products
join d in db.OrderDetails
on new {o.OrderID, p.ProductID} equals new {d.OrderID,
d.ProductID} into details
from d in details
select new {o.OrderID, p.ProductID, d.UnitPrice};
Type inference on composite keys depends on the names of the properties in the keys, and the order in which they occur. If the properties in the source sequences do not have the same names, you must assign new names in the keys. For example, if the Orders table and OrderDetails table each used different names for their columns, you could create composite keys by assigning identical names in the anonymous types:
join...on new {Name = o.CustomerName, ID = o.CustID} equals
new {Name = d.CustName, ID = d.CustID }
Composite keys can be also used in a group clause.
Better Linq examples
Here we will see how a linq can be utilized in a company module.
The company class will contain a Collection of employee class and employee will contain a struct for individual addresses.
Hierarchy:
CompanyLINQ.JPG
If we have a project with above hierarchy, let’s see how we can use LINQ to access details from multilevel classes.
After creation of companies we will try to find the details at multilevel and see how conditionally data can be fetched.
1) List details of employees
2) Count no of employees in each company
3) List employees who are staying in Bangalore
4) List employee who is paid highest in each companies
5) Overall Highest paid employee
6) Salary paid by companies in each city
7) Salary paid by each company in each city.
List details of employees:
Now we shall list all the employees with their details and company name.
Collapse | Copy Code
var EmpDetails = from comp in ListCompany
select new {
Emp = (from emp in comp.ListEmp
select new {
Company = comp.Name,
emp
})
};
Here we use sub queries since we need all employee details with their respective company name and both the details are at different levels.
So first we Loops through the companies in the company list
Collapse | Copy Code
from comp in ListCompany
and then within each selected company it loops through each employee
Collapse | Copy Code
from emp in comp.ListEmp
Count no of employees in each company:
Collapse | Copy Code
var LessEmp = from Comp in ListCompany
select new {
Comp.Name,
EmpCount = Comp.ListEmp.Count
};
List employees who are staying in Bangalore:
Here we use compound from clause to retrieve the employees who are staying in any city that contains BAN or ban.
Collapse | Copy Code
var EmpInACity = from comp in ListCompany
from emplist in comp.ListEmp
where emplist.Address.City.ToUpper().Contains("BAN")
select new {
CompName = comp.Name,
EmployeeName = emplist.Name
};
List employee who is paid highest in each companies:
For finding the highest paid employee we first loop through all the companies and employees in each company using compound from clause.
Where condition filters only those employees those have the salary equal to the max salary in current company.
Collapse | Copy Code
var EmpHighSalEachComp = from comp in ListCompany
from empHigh in comp.ListEmp
where empHigh.salary == comp.ListEmp.Max(
HighEmp => HighEmp.salary)
select new {
CompanyName = comp.Name,
EmpHighName = empHigh.Name,
EmpHighSal = empHigh.salary
};
Overall Highest paid employee:
The same above procedure is followed but the only difference comes in the where condition where we match the employee’s salary to the max salary from all the companies.
Collapse | Copy Code
var EmpHighSal = from comp in ListCompany
from emp in comp.ListEmp
where emp.salary == ListCompany.Max(
TComp => TComp.ListEmp.Max(HighEmp => HighEmp.salary))
select new {
CompanyName = comp.Name ,
EmployeeName = emp.Name,
EmpSal = emp.salary
};
Salary paid by companies in each city:
Here we will group by city and sum up all the salaries of the employees who are staying in that city.
Collapse | Copy Code
var CompanyCityWise = from comp in ListCompany
from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
};
Salary paid by each company in each city.
Here for each company a group by clause is applied and from each company employees staying at different locations are fetched.
Collapse | Copy Code
var CityWiseSalary = from comp in ListCompany
select new {
comp.Name,
Emp =(from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
})
};
The company class will contain a Collection of employee class and employee will contain a struct for individual addresses.
Hierarchy:
CompanyLINQ.JPG
If we have a project with above hierarchy, let’s see how we can use LINQ to access details from multilevel classes.
After creation of companies we will try to find the details at multilevel and see how conditionally data can be fetched.
1) List details of employees
2) Count no of employees in each company
3) List employees who are staying in Bangalore
4) List employee who is paid highest in each companies
5) Overall Highest paid employee
6) Salary paid by companies in each city
7) Salary paid by each company in each city.
List details of employees:
Now we shall list all the employees with their details and company name.
Collapse | Copy Code
var EmpDetails = from comp in ListCompany
select new {
Emp = (from emp in comp.ListEmp
select new {
Company = comp.Name,
emp
})
};
Here we use sub queries since we need all employee details with their respective company name and both the details are at different levels.
So first we Loops through the companies in the company list
Collapse | Copy Code
from comp in ListCompany
and then within each selected company it loops through each employee
Collapse | Copy Code
from emp in comp.ListEmp
Count no of employees in each company:
Collapse | Copy Code
var LessEmp = from Comp in ListCompany
select new {
Comp.Name,
EmpCount = Comp.ListEmp.Count
};
List employees who are staying in Bangalore:
Here we use compound from clause to retrieve the employees who are staying in any city that contains BAN or ban.
Collapse | Copy Code
var EmpInACity = from comp in ListCompany
from emplist in comp.ListEmp
where emplist.Address.City.ToUpper().Contains("BAN")
select new {
CompName = comp.Name,
EmployeeName = emplist.Name
};
List employee who is paid highest in each companies:
For finding the highest paid employee we first loop through all the companies and employees in each company using compound from clause.
Where condition filters only those employees those have the salary equal to the max salary in current company.
Collapse | Copy Code
var EmpHighSalEachComp = from comp in ListCompany
from empHigh in comp.ListEmp
where empHigh.salary == comp.ListEmp.Max(
HighEmp => HighEmp.salary)
select new {
CompanyName = comp.Name,
EmpHighName = empHigh.Name,
EmpHighSal = empHigh.salary
};
Overall Highest paid employee:
The same above procedure is followed but the only difference comes in the where condition where we match the employee’s salary to the max salary from all the companies.
Collapse | Copy Code
var EmpHighSal = from comp in ListCompany
from emp in comp.ListEmp
where emp.salary == ListCompany.Max(
TComp => TComp.ListEmp.Max(HighEmp => HighEmp.salary))
select new {
CompanyName = comp.Name ,
EmployeeName = emp.Name,
EmpSal = emp.salary
};
Salary paid by companies in each city:
Here we will group by city and sum up all the salaries of the employees who are staying in that city.
Collapse | Copy Code
var CompanyCityWise = from comp in ListCompany
from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
};
Salary paid by each company in each city.
Here for each company a group by clause is applied and from each company employees staying at different locations are fetched.
Collapse | Copy Code
var CityWiseSalary = from comp in ListCompany
select new {
comp.Name,
Emp =(from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
})
};
Better Linq examples
Here we will see how a linq can be utilized in a company module.
The company class will contain a Collection of employee class and employee will contain a struct for individual addresses.
Hierarchy:
CompanyLINQ.JPG
If we have a project with above hierarchy, let’s see how we can use LINQ to access details from multilevel classes.
After creation of companies we will try to find the details at multilevel and see how conditionally data can be fetched.
1) List details of employees
2) Count no of employees in each company
3) List employees who are staying in Bangalore
4) List employee who is paid highest in each companies
5) Overall Highest paid employee
6) Salary paid by companies in each city
7) Salary paid by each company in each city.
List details of employees:
Now we shall list all the employees with their details and company name.
Collapse | Copy Code
var EmpDetails = from comp in ListCompany
select new {
Emp = (from emp in comp.ListEmp
select new {
Company = comp.Name,
emp
})
};
Here we use sub queries since we need all employee details with their respective company name and both the details are at different levels.
So first we Loops through the companies in the company list
Collapse | Copy Code
from comp in ListCompany
and then within each selected company it loops through each employee
Collapse | Copy Code
from emp in comp.ListEmp
Count no of employees in each company:
Collapse | Copy Code
var LessEmp = from Comp in ListCompany
select new {
Comp.Name,
EmpCount = Comp.ListEmp.Count
};
List employees who are staying in Bangalore:
Here we use compound from clause to retrieve the employees who are staying in any city that contains BAN or ban.
Collapse | Copy Code
var EmpInACity = from comp in ListCompany
from emplist in comp.ListEmp
where emplist.Address.City.ToUpper().Contains("BAN")
select new {
CompName = comp.Name,
EmployeeName = emplist.Name
};
List employee who is paid highest in each companies:
For finding the highest paid employee we first loop through all the companies and employees in each company using compound from clause.
Where condition filters only those employees those have the salary equal to the max salary in current company.
Collapse | Copy Code
var EmpHighSalEachComp = from comp in ListCompany
from empHigh in comp.ListEmp
where empHigh.salary == comp.ListEmp.Max(
HighEmp => HighEmp.salary)
select new {
CompanyName = comp.Name,
EmpHighName = empHigh.Name,
EmpHighSal = empHigh.salary
};
Overall Highest paid employee:
The same above procedure is followed but the only difference comes in the where condition where we match the employee’s salary to the max salary from all the companies.
Collapse | Copy Code
var EmpHighSal = from comp in ListCompany
from emp in comp.ListEmp
where emp.salary == ListCompany.Max(
TComp => TComp.ListEmp.Max(HighEmp => HighEmp.salary))
select new {
CompanyName = comp.Name ,
EmployeeName = emp.Name,
EmpSal = emp.salary
};
Salary paid by companies in each city:
Here we will group by city and sum up all the salaries of the employees who are staying in that city.
Collapse | Copy Code
var CompanyCityWise = from comp in ListCompany
from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
};
Salary paid by each company in each city.
Here for each company a group by clause is applied and from each company employees staying at different locations are fetched.
Collapse | Copy Code
var CityWiseSalary = from comp in ListCompany
select new {
comp.Name,
Emp =(from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
})
};
The company class will contain a Collection of employee class and employee will contain a struct for individual addresses.
Hierarchy:
CompanyLINQ.JPG
If we have a project with above hierarchy, let’s see how we can use LINQ to access details from multilevel classes.
After creation of companies we will try to find the details at multilevel and see how conditionally data can be fetched.
1) List details of employees
2) Count no of employees in each company
3) List employees who are staying in Bangalore
4) List employee who is paid highest in each companies
5) Overall Highest paid employee
6) Salary paid by companies in each city
7) Salary paid by each company in each city.
List details of employees:
Now we shall list all the employees with their details and company name.
Collapse | Copy Code
var EmpDetails = from comp in ListCompany
select new {
Emp = (from emp in comp.ListEmp
select new {
Company = comp.Name,
emp
})
};
Here we use sub queries since we need all employee details with their respective company name and both the details are at different levels.
So first we Loops through the companies in the company list
Collapse | Copy Code
from comp in ListCompany
and then within each selected company it loops through each employee
Collapse | Copy Code
from emp in comp.ListEmp
Count no of employees in each company:
Collapse | Copy Code
var LessEmp = from Comp in ListCompany
select new {
Comp.Name,
EmpCount = Comp.ListEmp.Count
};
List employees who are staying in Bangalore:
Here we use compound from clause to retrieve the employees who are staying in any city that contains BAN or ban.
Collapse | Copy Code
var EmpInACity = from comp in ListCompany
from emplist in comp.ListEmp
where emplist.Address.City.ToUpper().Contains("BAN")
select new {
CompName = comp.Name,
EmployeeName = emplist.Name
};
List employee who is paid highest in each companies:
For finding the highest paid employee we first loop through all the companies and employees in each company using compound from clause.
Where condition filters only those employees those have the salary equal to the max salary in current company.
Collapse | Copy Code
var EmpHighSalEachComp = from comp in ListCompany
from empHigh in comp.ListEmp
where empHigh.salary == comp.ListEmp.Max(
HighEmp => HighEmp.salary)
select new {
CompanyName = comp.Name,
EmpHighName = empHigh.Name,
EmpHighSal = empHigh.salary
};
Overall Highest paid employee:
The same above procedure is followed but the only difference comes in the where condition where we match the employee’s salary to the max salary from all the companies.
Collapse | Copy Code
var EmpHighSal = from comp in ListCompany
from emp in comp.ListEmp
where emp.salary == ListCompany.Max(
TComp => TComp.ListEmp.Max(HighEmp => HighEmp.salary))
select new {
CompanyName = comp.Name ,
EmployeeName = emp.Name,
EmpSal = emp.salary
};
Salary paid by companies in each city:
Here we will group by city and sum up all the salaries of the employees who are staying in that city.
Collapse | Copy Code
var CompanyCityWise = from comp in ListCompany
from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
};
Salary paid by each company in each city.
Here for each company a group by clause is applied and from each company employees staying at different locations are fetched.
Collapse | Copy Code
var CityWiseSalary = from comp in ListCompany
select new {
comp.Name,
Emp =(from emp in comp.ListEmp
group emp by emp.Address.City into CityWiseEmp
select new {
State = CityWiseEmp.Key,
TotalSalary = CityWiseEmp.Sum(emp => emp.salary)
})
};
DataTable - Adding, Modifying, Deleting, Filtering, Sorting rows & Reading/Writing from/to Xml
DataTable - Adding, Modifying, Deleting, Filtering, Sorting rows & Reading/Writing from/to Xml
In this article, I am going to explain how to Add, Modify, Delete, Sort, Filter rows of the DataTable and also loading data from xml and writing data into xml. I will also talk about writing/reading Schema of the DataTable.
Introduction
DataTable is a central object in the ADO.NET library. If you are working with ADO.NET - accessing data from database, you can not escape from DataTable. Other objects that use DataTable are DataSet and DataView. In this tutorials, I will explain how to work with DataTable. I have tried to cover most of the frequently used activity in the DataTable, I hope you will like it.
Creating a DataTable
To create a DataTable, you need to use System.Data namespace, generally when you create a new class or page, it is included by default by the Visual Studio. Lets write following code to create a DataTable object. Here, I have pased a string as the DataTable name while creating DataTable object.
// instantiate DataTableDataTable dTable = new DataTable("Dynamically_Generated");
Creating Columns in the DataTable
To create column in the DataTable, you need to use DataColumn object. Instantiate the DataColumn object and pass column name and its data type as parameter. Then call add method of DataTable column and pass the DataColumn object as parameter.
// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);
Specifying AutoIncrement column in the DataTable
To specify a column as AutoIncrement (naturally it should be an integer type of field only), you need to set some properties of the column like AutoIncrement, AutoIncrementSeed. See the code below, here I am setting the first column "AutoID" as autoincrement field. Whenever a new row will be added its value will automatically increase by 1 as I am specified AutoIncrementSeed value as 1.
// specify it as auto increment field
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
If you want a particular column to be a unique column ie. you don't want duplicate records into that column, then set its Unique property to true like below.
auto.Unique = true;
Specifying Primary Key column in the DataTable
To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.
// create primary key on this fieldDataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
Till now we have created the DataTable, now lets populate the DataTable with some data.
Populating data into DataTable
There are two ways to populate DataTable.
Using DataRow object
Look at the code below, I have created a DataRow object above the loop and I am assiging its value to the dTable.NewRow() inside the loop. After specifying columns value, I am adding that row to the DataTable using dTable.Rows.Add method.
// populate the DataTable using DataRow objectDataRow row = null;
for (int i = 0; i < 5; i++) { row = dTable.NewRow(); row["AutoID"] = i + 1; row["Name"] = i + " - shibashish"; row["Address"] = "Aul,Kendrapara,Odisha, India - " + i; dTable.Rows.Add(row); } Instead of using the column name, you can use ColumnIndex too, however it is not suggested as you might want to add a column in the mid of the table then you will need to change your code wherever you have specified the index of the column. Same applies while reading or writing values into Database column. Asiging the value of column using Arrays In following code, I have specified the values of every column as the array separated by comma (,) in the Add method of the dTable.Rows. // manually adding rows using array of valuesdTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, Odisha"); dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA"); Modifying data into DataTable Modifying Row Data To edit the data of the row, sets its column value using row index or by specifying the column name. In below example, I am updating the 3rd row of the DataTable as I have specified the row index as 2 (dTable.Rows[2]). // modify certain values into the DataTabledTable.Rows[2]["AutoID"] = 20; dTable.Rows[2]["Name"] = "Modified"; dTable.Rows[2]["Address"] = "Modified Address"; dTable.AcceptChanges(); Deleting Row To delete a row into DataTable, call the rows.Delete() method followed by AcceptChanges() method. AcceptChanges() method commits all the changes made by you to the DataTable. Here Row[1] is the index of the row, in this case 2nd row will be deleted as in collection (here rows collection) count start from 0. // Delete rowdTable.Rows[1].Delete(); dTable.AcceptChanges(); Filtering data from DataTable To filter records from the DataTable, use Select method and pass necessary filter expression. In below code, the 1st line will simply filter all rows whose AutoID value is greater than 5. The 2nd line of the code filters the DataTable whose AutoID value is greater than 5 after sorting it. DataRow[] rows = dTable.Select(" AutoID > 5");
DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");
Note that Select method of the DataTable returns the array of rows that matche the filter expression. If you want to loop through all the filtered rows, you can use foreach loop as shown below. In this code, I am adding all the filtered rows into another DataTable.
foreach (DataRow thisRow in rows)
{
// add values into the datatable dTable1.Rows.Add(thisRow.ItemArray);
}
Working with Aggregate functions (Updated on 18-Nov-08)
We can use almost all aggregate functions with DataTable, however the syntax is bit different than standard SQL.
Suppose we need to get the maximum value of a particular column, we can get it in the following way.
DataRow[] rows22 = dTable.Select("AutoID = max(AutoID)");
string str = "MaxAutoID: " + rows22[0]["AutoID"].ToString();
To get the sum of a particular column, we can use Compute method of the DataTable. Compute method of the DataTable takes two argument. The first argument is the expression to compute and second is the filter to limit the rows that evaluate in the expression. If we don't want any filteration (if we need only the sum of the AutoID column for all rows), we can leave the second parameter as blank ("").
object objSum = dTable.Compute("sum(AutoID)", "AutoID > 7");
string sum = "Sum: " + objSum.ToString();
// To get sum of AutoID for all rows of the DataTable
object objSum = dTable.Compute("sum(AutoID)", "");
Sorting data of DataTable
Oops !. There is no direct way of sorting DataTable rows like filtering (Select method to filter DataRows).
There are two ways you can do this.
Using DataView
See the code below. I have created a DataView object by passing my DataTable as parameter, so my DataView will have all the data of the DataTable. Now, simply call the Sort method of the DataView and pass the sort expression. Your DataView object have sorted records now, You can either directly specify the Source of the Data controls object like GridView, DataList to bind the data or if you need to loop through its data you can use ForEach loop as below.
// Sorting DataTableDataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Address"].ToString());
}
Using DataTable.Select() method
Yes, you can sort all the rows using Select method too provided you have not specified any filter expression. If you will specify the filter expression, ofcourse your rows will be sorted but filter will also be applied. A small drawback of this way of sorting is that it will return array of DataRows as descibed earlier so if you are planning to bind it to the Data controls like GridView or DataList you will have for form a DataTable by looping through because directly binding arrays of rows to the Data controls will not give desired results.
DataRow[] rows = dTable.Select("", "AutoID DESC");
Writing and Reading XmlSchema of the DataTable
If you need XmlSchema of the DataTabe, you can use WriteXmlSchema to write and ReadXmlSchema to read it. There are several overloads methods of both methods and you can pass filename, stream, TextReader, XmlReader etc. as the parameter. In this code, the schema will be written to the .xml file and will be read from there.
// creating schema definition of the DataTabledTable.WriteXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
// Reading XmlSchema from the xml file we just created
DataTable dTableXmlSchema = new DataTable();
dTableXmlSchema.ReadXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
Reading/Writing from/to Xml
If you have a scenario, where you need to write the data of the DataTable into xml format, you can use WriteXml method of the DataTable. Note that WriteXml method will not work if you will not specify the name of the DataTable object while creating it. Look at the first code block above, I have passed "Dynamically_Generated" string while creating the instance of the DataTable. If you will not specify the name of the DataTable then you will get error as WriteXml method will not be able to serialize the data without it.
// Note: In order to write the DataTable into XML, // you must define the name of the DataTable while creating it
// Also if you are planning to read back this XML into DataTable, you should define the XmlWriteMode.WriteSchema too
// Otherwise ReadXml method will not understand simple xml file
dTable.WriteXml(Server.MapPath("~/DataTable.xml"), XmlWriteMode.WriteSchema);
// Loading Data from XML into DataTable
DataTable dTableXml = new DataTable();
dTableXml.ReadXml(Server.MapPath("~/DataTable.xml"));
If you are planning to read the xml you have just created into the DataTable sometime later then you need to specify XmlWriteMode.WriteSchema too as the 2nd parameter while calling WriteXml method of the DataTable otherwise normally WriteXml method doesn't write schema of the DataTable. In the abscence of the schema, you will get error (DataTable does not support schema inference from Xml) while calling ReadXml method of the DataTable.
Take one simple Example:
In Source view
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Datatable.aspx.cs" Inherits="Datatable" %>
In Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class Datatable : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dTable = new DataTable("Dynamically_Generated");
// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
// create primary key on this field
DataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
// populate the DataTable using DataRow object
DataRow row = null;
for (int i = 0; i < 5; i++)
{
row = dTable.NewRow();
row["AutoID"] = i + 1;
row["Name"] = i + " - shibashish";
row["Address"] = "Aul,Kendrapara,Odisha, India - " + i;
dTable.Rows.Add(row);
}
dTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, Odisha");
dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");
DataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Name"].ToString());
}
GridView1.DataSource = dTable;
GridView1.DataBind();
}
}
Hope this article will be useful. If you have any question, comments or suggestions, please respond to this article. Thank you very much for reading it and Happy DataTable :)
In this article, I am going to explain how to Add, Modify, Delete, Sort, Filter rows of the DataTable and also loading data from xml and writing data into xml. I will also talk about writing/reading Schema of the DataTable.
Introduction
DataTable is a central object in the ADO.NET library. If you are working with ADO.NET - accessing data from database, you can not escape from DataTable. Other objects that use DataTable are DataSet and DataView. In this tutorials, I will explain how to work with DataTable. I have tried to cover most of the frequently used activity in the DataTable, I hope you will like it.
Creating a DataTable
To create a DataTable, you need to use System.Data namespace, generally when you create a new class or page, it is included by default by the Visual Studio. Lets write following code to create a DataTable object. Here, I have pased a string as the DataTable name while creating DataTable object.
// instantiate DataTableDataTable dTable = new DataTable("Dynamically_Generated");
Creating Columns in the DataTable
To create column in the DataTable, you need to use DataColumn object. Instantiate the DataColumn object and pass column name and its data type as parameter. Then call add method of DataTable column and pass the DataColumn object as parameter.
// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);
Specifying AutoIncrement column in the DataTable
To specify a column as AutoIncrement (naturally it should be an integer type of field only), you need to set some properties of the column like AutoIncrement, AutoIncrementSeed. See the code below, here I am setting the first column "AutoID" as autoincrement field. Whenever a new row will be added its value will automatically increase by 1 as I am specified AutoIncrementSeed value as 1.
// specify it as auto increment field
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
If you want a particular column to be a unique column ie. you don't want duplicate records into that column, then set its Unique property to true like below.
auto.Unique = true;
Specifying Primary Key column in the DataTable
To set the primary key column in the DataTable, you need to create arrays of column and store column you want as primary key for the DataTable and set its PrimaryKey property to the column arrays. See the code below.
// create primary key on this fieldDataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
Till now we have created the DataTable, now lets populate the DataTable with some data.
Populating data into DataTable
There are two ways to populate DataTable.
Using DataRow object
Look at the code below, I have created a DataRow object above the loop and I am assiging its value to the dTable.NewRow() inside the loop. After specifying columns value, I am adding that row to the DataTable using dTable.Rows.Add method.
// populate the DataTable using DataRow objectDataRow row = null;
for (int i = 0; i < 5; i++) { row = dTable.NewRow(); row["AutoID"] = i + 1; row["Name"] = i + " - shibashish"; row["Address"] = "Aul,Kendrapara,Odisha, India - " + i; dTable.Rows.Add(row); } Instead of using the column name, you can use ColumnIndex too, however it is not suggested as you might want to add a column in the mid of the table then you will need to change your code wherever you have specified the index of the column. Same applies while reading or writing values into Database column. Asiging the value of column using Arrays In following code, I have specified the values of every column as the array separated by comma (,) in the Add method of the dTable.Rows. // manually adding rows using array of valuesdTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, Odisha"); dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA"); Modifying data into DataTable Modifying Row Data To edit the data of the row, sets its column value using row index or by specifying the column name. In below example, I am updating the 3rd row of the DataTable as I have specified the row index as 2 (dTable.Rows[2]). // modify certain values into the DataTabledTable.Rows[2]["AutoID"] = 20; dTable.Rows[2]["Name"] = "Modified"; dTable.Rows[2]["Address"] = "Modified Address"; dTable.AcceptChanges(); Deleting Row To delete a row into DataTable, call the rows.Delete() method followed by AcceptChanges() method. AcceptChanges() method commits all the changes made by you to the DataTable. Here Row[1] is the index of the row, in this case 2nd row will be deleted as in collection (here rows collection) count start from 0. // Delete rowdTable.Rows[1].Delete(); dTable.AcceptChanges(); Filtering data from DataTable To filter records from the DataTable, use Select method and pass necessary filter expression. In below code, the 1st line will simply filter all rows whose AutoID value is greater than 5. The 2nd line of the code filters the DataTable whose AutoID value is greater than 5 after sorting it. DataRow[] rows = dTable.Select(" AutoID > 5");
DataRow[] rows1 = dTable.Select(" AutoID > 5", "AuotID ASC");
Note that Select method of the DataTable returns the array of rows that matche the filter expression. If you want to loop through all the filtered rows, you can use foreach loop as shown below. In this code, I am adding all the filtered rows into another DataTable.
foreach (DataRow thisRow in rows)
{
// add values into the datatable dTable1.Rows.Add(thisRow.ItemArray);
}
Working with Aggregate functions (Updated on 18-Nov-08)
We can use almost all aggregate functions with DataTable, however the syntax is bit different than standard SQL.
Suppose we need to get the maximum value of a particular column, we can get it in the following way.
DataRow[] rows22 = dTable.Select("AutoID = max(AutoID)");
string str = "MaxAutoID: " + rows22[0]["AutoID"].ToString();
To get the sum of a particular column, we can use Compute method of the DataTable. Compute method of the DataTable takes two argument. The first argument is the expression to compute and second is the filter to limit the rows that evaluate in the expression. If we don't want any filteration (if we need only the sum of the AutoID column for all rows), we can leave the second parameter as blank ("").
object objSum = dTable.Compute("sum(AutoID)", "AutoID > 7");
string sum = "Sum: " + objSum.ToString();
// To get sum of AutoID for all rows of the DataTable
object objSum = dTable.Compute("sum(AutoID)", "");
Sorting data of DataTable
Oops !. There is no direct way of sorting DataTable rows like filtering (Select method to filter DataRows).
There are two ways you can do this.
Using DataView
See the code below. I have created a DataView object by passing my DataTable as parameter, so my DataView will have all the data of the DataTable. Now, simply call the Sort method of the DataView and pass the sort expression. Your DataView object have sorted records now, You can either directly specify the Source of the Data controls object like GridView, DataList to bind the data or if you need to loop through its data you can use ForEach loop as below.
// Sorting DataTableDataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Address"].ToString());
}
Using DataTable.Select() method
Yes, you can sort all the rows using Select method too provided you have not specified any filter expression. If you will specify the filter expression, ofcourse your rows will be sorted but filter will also be applied. A small drawback of this way of sorting is that it will return array of DataRows as descibed earlier so if you are planning to bind it to the Data controls like GridView or DataList you will have for form a DataTable by looping through because directly binding arrays of rows to the Data controls will not give desired results.
DataRow[] rows = dTable.Select("", "AutoID DESC");
Writing and Reading XmlSchema of the DataTable
If you need XmlSchema of the DataTabe, you can use WriteXmlSchema to write and ReadXmlSchema to read it. There are several overloads methods of both methods and you can pass filename, stream, TextReader, XmlReader etc. as the parameter. In this code, the schema will be written to the .xml file and will be read from there.
// creating schema definition of the DataTabledTable.WriteXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
// Reading XmlSchema from the xml file we just created
DataTable dTableXmlSchema = new DataTable();
dTableXmlSchema.ReadXmlSchema(Server.MapPath("~/DataTableSchema.xml"));
Reading/Writing from/to Xml
If you have a scenario, where you need to write the data of the DataTable into xml format, you can use WriteXml method of the DataTable. Note that WriteXml method will not work if you will not specify the name of the DataTable object while creating it. Look at the first code block above, I have passed "Dynamically_Generated" string while creating the instance of the DataTable. If you will not specify the name of the DataTable then you will get error as WriteXml method will not be able to serialize the data without it.
// Note: In order to write the DataTable into XML, // you must define the name of the DataTable while creating it
// Also if you are planning to read back this XML into DataTable, you should define the XmlWriteMode.WriteSchema too
// Otherwise ReadXml method will not understand simple xml file
dTable.WriteXml(Server.MapPath("~/DataTable.xml"), XmlWriteMode.WriteSchema);
// Loading Data from XML into DataTable
DataTable dTableXml = new DataTable();
dTableXml.ReadXml(Server.MapPath("~/DataTable.xml"));
If you are planning to read the xml you have just created into the DataTable sometime later then you need to specify XmlWriteMode.WriteSchema too as the 2nd parameter while calling WriteXml method of the DataTable otherwise normally WriteXml method doesn't write schema of the DataTable. In the abscence of the schema, you will get error (DataTable does not support schema inference from Xml) while calling ReadXml method of the DataTable.
Take one simple Example:
In Source view
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Datatable.aspx.cs" Inherits="Datatable" %>
In Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class Datatable : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dTable = new DataTable("Dynamically_Generated");
// create columns for the DataTable
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// create another column
DataColumn name = new DataColumn("Name", typeof(string));
dTable.Columns.Add(name);
// create one more column
DataColumn address = new DataColumn("Address", typeof(string));
dTable.Columns.Add(address);
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;
// create primary key on this field
DataColumn[] pK = new DataColumn[1];
pK[0] = auto;
dTable.PrimaryKey = pK;
// populate the DataTable using DataRow object
DataRow row = null;
for (int i = 0; i < 5; i++)
{
row = dTable.NewRow();
row["AutoID"] = i + 1;
row["Name"] = i + " - shibashish";
row["Address"] = "Aul,Kendrapara,Odisha, India - " + i;
dTable.Rows.Add(row);
}
dTable.Rows.Add(6, "Manual Data - 1", "Manual Address - 1, Odisha");
dTable.Rows.Add(7, "Manual Data - 2", "Manual Address - 2, USA");
DataView dataView = new DataView(dTable);
dataView.Sort = " AutoID DESC, Name DESC";
foreach (DataRowView view in dataView)
{
Response.Write(view["Name"].ToString());
}
GridView1.DataSource = dTable;
GridView1.DataBind();
}
}
Hope this article will be useful. If you have any question, comments or suggestions, please respond to this article. Thank you very much for reading it and Happy DataTable :)
Javascript Tutorial for learner
Try it your own
1-->
2-->
alert("sometext");
Example
3-->
confirm("sometext");
Example
4-->
prompt("sometext","defaultvalue");
Example
5-->
JavaScript Function Example
Example
6-->
The example below returns the product of two numbers (a and b):
Example
7-->
Code Sample: JavaScriptBasics/Demos/JavaScript.html
JavaScript Page
1-->
My First Web Page
This is shibashish.
2-->
alert("sometext");
Example
3-->
confirm("sometext");
Example
4-->
prompt("sometext","defaultvalue");
Example
5-->
JavaScript Function Example
Example
6-->
The example below returns the product of two numbers (a and b):
Example
7-->
Code Sample: JavaScriptBasics/Demos/JavaScript.html
Red |
White
Javascript Basics for freshers
JavaScript If...Else Statements
Conditional Statements
Very often when you write code, you want to perform different actions for different decisions. You can use conditional statements in your code to do this.
In JavaScript we have the following conditional statements:
if statement - use this statement to execute some code only if a specified condition is true
if...else statement - use this statement to execute some code if the condition is true and another code if the condition is false
if...else if....else statement - use this statement to select one of many blocks of code to be executed
switch statement - use this statement to select one of many blocks of code to be executed
If Statement
Use the if statement to execute some code only if a specified condition is true.
Syntax
if (condition)
{
code to be executed if condition is true
}
Note that if is written in lowercase letters. Using uppercase letters (IF) will generate a JavaScript error!
Example
Notice that there is no ..else.. in this syntax. You tell the browser to execute some code only if the specified condition is true.
If...else Statement
Use the if....else statement to execute some code if a condition is true and another code if the condition is not true.
Syntax
if (condition)
{
code to be executed if condition is true
}
else
{
code to be executed if condition is not true
}
Example
If...else if...else Statement Use the if....else if...else statement to select one of several blocks of code to be executed. Syntax if (condition1) { code to be executed if condition1 is true } else if (condition2) { code to be executed if condition2 is true } else { code to be executed if neither condition1 nor condition2 is true } Example
The JavaScript Switch Statement
Use the switch statement to select one of many blocks of code to be executed.
Syntax
switch(n)
{
case 1:
execute code block 1
break;
case 2:
execute code block 2
break;
default:
code to be executed if n is different from case 1 and 2
}
This is how it works: First we have a single expression n (most often a variable), that is evaluated once. The value of the expression is then compared with the values for each case in the structure. If there is a match, the block of code associated with that case is executed. Use break to prevent the code from running into the next case automatically.
Example
JavaScript Popup Boxes
Alert Box
An alert box is often used if you want to make sure information comes through to the user.
When an alert box pops up, the user will have to click "OK" to proceed.
Syntax
alert("sometext");
Example
Confirm Box
A confirm box is often used if you want the user to verify or accept something.
When a confirm box pops up, the user will have to click either "OK" or "Cancel" to proceed.
If the user clicks "OK", the box returns true. If the user clicks "Cancel", the box returns false.
Syntax
confirm("sometext");
Example
Prompt Box
A prompt box is often used if you want the user to input a value before entering a page.
When a prompt box pops up, the user will have to click either "OK" or "Cancel" to proceed after entering an input value.
If the user clicks "OK" the box returns the input value. If the user clicks "Cancel" the box returns null.
Syntax
prompt("sometext","defaultvalue");
Example
Alert box with line breaks
How to Define a Function
Syntax
function functionname(var1,var2,...,varX)
{
some code
}
The parameters var1, var2, etc. are variables or values passed into the function. The { and the } defines the start and end of the function.
Note: A function with no parameters must include the parentheses () after the function name.
Note: Do not forget about the importance of capitals in JavaScript! The word function must be written in lowercase letters, otherwise a JavaScript error occurs! Also note that you must call a function with the exact same capitals as in the function name.
JavaScript Function Example
Example
If the line: alert("Hello world!!") in the example above had not been put within a function, it would have been executed as soon as the page was loaded. Now, the script is not executed before a user hits the input button. The function displaymessage() will be executed if the input button is clicked.
You will learn more about JavaScript events in the JS Events chapter.
The return Statement
The return statement is used to specify the value that is returned from the function.
So, functions that are going to return a value must use the return statement.
The example below returns the product of two numbers (a and b):
Example
How to pass a variable to a function, and use the variable in the function.
JavaScript For Loop
Loops execute a block of code a specified number of times, or while a specified condition is true.
JavaScript Loops
Often when you write code, you want the same block of code to run over and over again in a row. Instead of adding several almost equal lines in a script we can use loops to perform a task like this.
In JavaScript, there are two different kind of loops:
for - loops through a block of code a specified number of times
while - loops through a block of code while a specified condition is true
The for Loop
The for loop is used when you know in advance how many times the script should run.
Syntax
for (variable=startvalue;variable<=endvalue;variable=variable+increment) { code to be executed } Example The example below defines a loop that starts with i=0. The loop will continue to run as long as i is less than, or equal to 5. i will increase by 1 each time the loop runs. Note: The increment parameter could also be negative, and the <= could be any comparing statement. Example
Loop through the six different HTML headings
JavaScript While Loop
Loops execute a block of code a specified number of times, or while a specified condition is true.
The while Loop
The while loop loops through a block of code while a specified condition is true.
Syntax
while (variable<=endvalue) { code to be executed } Note: The <= could be any comparing operator. Example The example below defines a loop that starts with i=0. The loop will continue to run as long as i is less than, or equal to 5. i will increase by 1 each time the loop runs: Example
The do...while Loop
The do...while loop is a variant of the while loop. This loop will execute the block of code ONCE, and then it will repeat the loop as long as the specified condition is true.
Syntax
do
{
code to be executed
}
while (variable<=endvalue); Example The example below uses a do...while loop. The do...while loop will always be executed at least once, even if the condition is false, because the statements are executed before the condition is tested: Example
JavaScript Break and Continue Statements The break Statement The break statement will break the loop and continue executing the code that follows after the loop (if any). Example
The continue Statement
The continue statement will break the current loop and continue with the next value.
Example
JavaScript For...In Statement
JavaScript For...In Statement
The for...in statement loops through the properties of an object.
Syntax
for (variable in object)
{
code to be executed
}
Note: The code in the body of the for...in loop is executed once for each property.
Example
Looping through the properties of an object:
Example
var person={fname:"shibashish",lname:"mohanty",age:23};
for (x in person)
{
document.write(person[x] + " ");
}
JavaScript Events
Events are actions that can be detected by JavaScript.
Acting to an Event
The example below displays the date when a button is clicked:
Example
Events
By using JavaScript, we have the ability to create dynamic web pages. Events are actions that can be detected by JavaScript.
Every element on a web page has certain events which can trigger a JavaScript. For example, we can use the onClick event of a button element to indicate that a function will run when a user clicks on the button. We define the events in the HTML tags.
Examples of events:
A mouse click
A web page or an image loading
Mousing over a hot spot on the web page
Selecting an input field in an HTML form
Submitting an HTML form
A keystroke
Note: Events are normally used in combination with functions, and the function will not be executed before the event occurs!
onLoad and onUnload
The onLoad and onUnload events are triggered when the user enters or leaves the page.
The onLoad event is often used to check the visitor's browser type and browser version, and load the proper version of the web page based on the information.
Both the onLoad and onUnload events are also often used to deal with cookies that should be set when a user enters or leaves a page. For example, you could have a popup asking for the user's name upon his first arrival to your page. The name is then stored in a cookie. Next time the visitor arrives at your page, you could have another popup saying something like: "Welcome John Doe!".
onFocus, onBlur and onChange
The onFocus, onBlur and onChange events are often used in combination with validation of form fields.
Below is an example of how to use the onChange event. The checkEmail() function will be called whenever the user changes the content of the field:
onSubmit
The onSubmit event is used to validate ALL form fields before submitting it.
Below is an example of how to use the onSubmit event. The checkForm() function will be called when the user clicks the submit button in the form. If the field values are not accepted, the submit should be cancelled. The function checkForm() returns either true or false. If it returns true the form will be submitted, otherwise the submit will be cancelled:
Conditional Statements
Very often when you write code, you want to perform different actions for different decisions. You can use conditional statements in your code to do this.
In JavaScript we have the following conditional statements:
if statement - use this statement to execute some code only if a specified condition is true
if...else statement - use this statement to execute some code if the condition is true and another code if the condition is false
if...else if....else statement - use this statement to select one of many blocks of code to be executed
switch statement - use this statement to select one of many blocks of code to be executed
If Statement
Use the if statement to execute some code only if a specified condition is true.
Syntax
if (condition)
{
code to be executed if condition is true
}
Note that if is written in lowercase letters. Using uppercase letters (IF) will generate a JavaScript error!
Example
Notice that there is no ..else.. in this syntax. You tell the browser to execute some code only if the specified condition is true.
If...else Statement
Use the if....else statement to execute some code if a condition is true and another code if the condition is not true.
Syntax
if (condition)
{
code to be executed if condition is true
}
else
{
code to be executed if condition is not true
}
Example
If...else if...else Statement Use the if....else if...else statement to select one of several blocks of code to be executed. Syntax if (condition1) { code to be executed if condition1 is true } else if (condition2) { code to be executed if condition2 is true } else { code to be executed if neither condition1 nor condition2 is true } Example
The JavaScript Switch Statement
Use the switch statement to select one of many blocks of code to be executed.
Syntax
switch(n)
{
case 1:
execute code block 1
break;
case 2:
execute code block 2
break;
default:
code to be executed if n is different from case 1 and 2
}
This is how it works: First we have a single expression n (most often a variable), that is evaluated once. The value of the expression is then compared with the values for each case in the structure. If there is a match, the block of code associated with that case is executed. Use break to prevent the code from running into the next case automatically.
Example
JavaScript Popup Boxes
Alert Box
An alert box is often used if you want to make sure information comes through to the user.
When an alert box pops up, the user will have to click "OK" to proceed.
Syntax
alert("sometext");
Example
Confirm Box
A confirm box is often used if you want the user to verify or accept something.
When a confirm box pops up, the user will have to click either "OK" or "Cancel" to proceed.
If the user clicks "OK", the box returns true. If the user clicks "Cancel", the box returns false.
Syntax
confirm("sometext");
Example
Prompt Box
A prompt box is often used if you want the user to input a value before entering a page.
When a prompt box pops up, the user will have to click either "OK" or "Cancel" to proceed after entering an input value.
If the user clicks "OK" the box returns the input value. If the user clicks "Cancel" the box returns null.
Syntax
prompt("sometext","defaultvalue");
Example
Alert box with line breaks
How to Define a Function
Syntax
function functionname(var1,var2,...,varX)
{
some code
}
The parameters var1, var2, etc. are variables or values passed into the function. The { and the } defines the start and end of the function.
Note: A function with no parameters must include the parentheses () after the function name.
Note: Do not forget about the importance of capitals in JavaScript! The word function must be written in lowercase letters, otherwise a JavaScript error occurs! Also note that you must call a function with the exact same capitals as in the function name.
JavaScript Function Example
Example
If the line: alert("Hello world!!") in the example above had not been put within a function, it would have been executed as soon as the page was loaded. Now, the script is not executed before a user hits the input button. The function displaymessage() will be executed if the input button is clicked.
You will learn more about JavaScript events in the JS Events chapter.
The return Statement
The return statement is used to specify the value that is returned from the function.
So, functions that are going to return a value must use the return statement.
The example below returns the product of two numbers (a and b):
Example
How to pass a variable to a function, and use the variable in the function.
By pressing the button above, a function will be called with "Hello" as a parameter. The function will alert the parameter.
JavaScript For Loop
Loops execute a block of code a specified number of times, or while a specified condition is true.
JavaScript Loops
Often when you write code, you want the same block of code to run over and over again in a row. Instead of adding several almost equal lines in a script we can use loops to perform a task like this.
In JavaScript, there are two different kind of loops:
for - loops through a block of code a specified number of times
while - loops through a block of code while a specified condition is true
The for Loop
The for loop is used when you know in advance how many times the script should run.
Syntax
for (variable=startvalue;variable<=endvalue;variable=variable+increment) { code to be executed } Example The example below defines a loop that starts with i=0. The loop will continue to run as long as i is less than, or equal to 5. i will increase by 1 each time the loop runs. Note: The increment parameter could also be negative, and the <= could be any comparing statement. Example
Loop through the six different HTML headings
JavaScript While Loop
Loops execute a block of code a specified number of times, or while a specified condition is true.
The while Loop
The while loop loops through a block of code while a specified condition is true.
Syntax
while (variable<=endvalue) { code to be executed } Note: The <= could be any comparing operator. Example The example below defines a loop that starts with i=0. The loop will continue to run as long as i is less than, or equal to 5. i will increase by 1 each time the loop runs: Example
The do...while Loop
The do...while loop is a variant of the while loop. This loop will execute the block of code ONCE, and then it will repeat the loop as long as the specified condition is true.
Syntax
do
{
code to be executed
}
while (variable<=endvalue); Example The example below uses a do...while loop. The do...while loop will always be executed at least once, even if the condition is false, because the statements are executed before the condition is tested: Example
JavaScript Break and Continue Statements The break Statement The break statement will break the loop and continue executing the code that follows after the loop (if any). Example
The continue Statement
The continue statement will break the current loop and continue with the next value.
Example
JavaScript For...In Statement
JavaScript For...In Statement
The for...in statement loops through the properties of an object.
Syntax
for (variable in object)
{
code to be executed
}
Note: The code in the body of the for...in loop is executed once for each property.
Example
Looping through the properties of an object:
Example
var person={fname:"shibashish",lname:"mohanty",age:23};
for (x in person)
{
document.write(person[x] + " ");
}
JavaScript Events
Events are actions that can be detected by JavaScript.
Acting to an Event
The example below displays the date when a button is clicked:
Example
This is shibashish
Events
By using JavaScript, we have the ability to create dynamic web pages. Events are actions that can be detected by JavaScript.
Every element on a web page has certain events which can trigger a JavaScript. For example, we can use the onClick event of a button element to indicate that a function will run when a user clicks on the button. We define the events in the HTML tags.
Examples of events:
A mouse click
A web page or an image loading
Mousing over a hot spot on the web page
Selecting an input field in an HTML form
Submitting an HTML form
A keystroke
Note: Events are normally used in combination with functions, and the function will not be executed before the event occurs!
onLoad and onUnload
The onLoad and onUnload events are triggered when the user enters or leaves the page.
The onLoad event is often used to check the visitor's browser type and browser version, and load the proper version of the web page based on the information.
Both the onLoad and onUnload events are also often used to deal with cookies that should be set when a user enters or leaves a page. For example, you could have a popup asking for the user's name upon his first arrival to your page. The name is then stored in a cookie. Next time the visitor arrives at your page, you could have another popup saying something like: "Welcome John Doe!".
onFocus, onBlur and onChange
The onFocus, onBlur and onChange events are often used in combination with validation of form fields.
Below is an example of how to use the onChange event. The checkEmail() function will be called whenever the user changes the content of the field:
onSubmit
The onSubmit event is used to validate ALL form fields before submitting it.
Below is an example of how to use the onSubmit event. The checkForm() function will be called when the user clicks the submit button in the form. If the field values are not accepted, the submit should be cancelled. The function checkForm() returns either true or false. If it returns true the form will be submitted, otherwise the submit will be cancelled:
Subscribe to:
Posts (Atom)