mobile number expression in regular express

how to capture a image from web cam

PressMe

how to open a panel inside gride view in javascript

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

DID
DNAME
Please Wait....

C# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
DataClassesDataContext o=new DataClassesDataContext(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
fill();
}
void fill()
{
var m = from x in o.EMPs select x;
gv.DataSource = m;
gv.DataBind();
}

protected void btn_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(5000);
}
protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
LinkButton ln1 = (LinkButton)e.Row.FindControl("lbt");
Panel pn1 = (Panel)e.Row.FindControl("pn");
ln1.Attributes.Add("onmouseover","x('"+pn1.ClientID+"')");
}
}
}

Showing Progress Bar

Press me

nested grideview

press here

nested grideview

press me

nested grideview

press me

Ajax ModalPopUpExtender Example to edit the gridview row values in asp.net

press here

Showing Progress Bar Using jQuery and asp.net

Showing Progress Bar Using jQuery and asp.net

how to call a javascript or jquery function in code behind

protected void lbt_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new StringBuilder();
sb.Append("$(document).ready(function(){");
sb.Append("$.showprogress()");
sb.Append("});");
ScriptManager.RegisterStartupScript(up, up.GetType(), "Script", sb.ToString(), true);

}

ADO .NET ENTITY DATA MODEL USING STORE PROCEDURE

Right click ur project add new item

Right clion ur project add new item

Then go to the database

Here i have created three procedures from dml operation that procedure are
PROCEDURE FOR INSERT
ALTER PROCEDURE emp1
(
@NAME VARCHAR(50)
)

AS
BEGIN
SET NOCOUNT ON
INSERT INTO EMP(NAME) VALUES(@NAME)
SET NOCOUNT OFF
END
PROCEDURE FOR UPDATE
CREATE PROCEDURE emp2
(
@ID INT,
@NAME VARCHAR(50)
)
PROCEDURE FOR DELETE
CREATE PROCEDURE emp3
(
@ID INT
)

AS
BEGIN
SET NOCOUNT ON
DELETE FROM EMP WHERE ID=@ID
SET NOCOUNT OFF
END

THEN MAPPING THE PROCEDURE GO TO SOLUTION DOUBLE CLICK ON THE Model.edmx, GO TO model window then right click on the table then select procedure mapping

AS
BEGIN
SET NOCOUNT ON
UPDATE EMP SET NAME=@NAME WHERE ID=@ID
SET NOCOUNT OFF
END
after that u go to solution and double click on model.edmx
then right click on model
select update model from database

then right click on model
select update model from database

after that rihght click on table select store procedure mapping

then select the procedure name from the drop down

right click on the model window select model brower

expand the procedure folder select then right click on procedure select add function import

then new window open after that click on ok button

then clikck on finish buttom
then module window clike this

then go to design view
after that u go to code view
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;



public partial class _Default : System.Web.UI.Page
{
#region
DatabaseModel.DatabaseEntities o = new DatabaseModel.DatabaseEntities();
int i;
string s;
#endregion
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
fill();

}
void fill()
{
var m = from x in o.EMPs select x;
gv.DataSource = m;
gv.DataBind();
}
protected void btn_Click(object sender, EventArgs e)
{
o.emp1(tb1.Text);
o.SaveChanges();
lb.Text = "One record saved.";
fill();
}
//protected void lbt_Click(object sender, EventArgs e)
//{
// LinkButton lk = (LinkButton)sender;
// GridViewRow gr = (GridViewRow)lk.NamingContainer;



//}
protected void lbt1_Click(object sender, EventArgs e)
{

}
protected void gv_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
s = ((LinkButton)gv.Rows[e.NewSelectedIndex].FindControl("lbt")).CommandArgument.ToString();
string[] ss = s.Split('*');
tb.Text = ss[0];
tb1.Text = ss[1];
}
protected void btn1_Click(object sender, EventArgs e)
{
i=Convert.ToInt32(tb.Text);
o.emp2(i, tb1.Text);
o.SaveChanges();
lb.Text = "One record updated.";
fill();
}
protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
i = Convert.ToInt32(((LinkButton)gv.Rows[e.RowIndex].FindControl("lbt1")).CommandArgument);
o.emp3(i);
o.SaveChanges();
lb.Text = "One record deleted";
fill();
}
}

ADO .NET ENTITY DATA MODEL

Right click ur project add new item

Right click ur project add new item

Then go to the database

after that u go to solution and double click on model.edmx
then right click on model
select update model from database

then clikck on finish buttom
then module window like this

then go to design view
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

• EID
• :

• NAME
• :

•  
•  
•  

•  
•  
• 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

then go to code view
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


public partial class _Default : System.Web.UI.Page
{
#region
DatabaseModel.DatabaseEntities o = new DatabaseModel.DatabaseEntities();
int i;
string s;
#endregion
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
fill();

}
void fill()
{
var m = from x in o.EMPs select x;
gv.DataSource = m;
gv.DataBind();
}
protected void btn_Click(object sender, EventArgs e)
{
DatabaseModel.EMP p = new DatabaseModel.EMP();
p.ID = Convert.ToInt32(tb.Text);
p.NAME = tb1.Text;
o.AddToEMPs(p);
o.SaveChanges();
lb.Text = "One record saved.";
fill();
}
//protected void lbt_Click(object sender, EventArgs e)
//{
// LinkButton lk = (LinkButton)sender;
// GridViewRow gr = (GridViewRow)lk.NamingContainer;



//}
protected void lbt1_Click(object sender, EventArgs e)
{

}
protected void gv_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
s = ((LinkButton)gv.Rows[e.NewSelectedIndex].FindControl("lbt")).CommandArgument.ToString();
string[] ss = s.Split('*');
tb.Text = ss[0];
tb1.Text = ss[1];
}
protected void btn1_Click(object sender, EventArgs e)
{
i=Convert.ToInt32(tb.Text);
var n = (from y in o.EMPs where y.ID == i select y).First();
n.NAME = tb1.Text;
o.SaveChanges();
lb.Text = "One record updated.";
fill();
}
protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
i = Convert.ToInt32(((LinkButton)gv.Rows[e.RowIndex].FindControl("lbt1")).CommandArgument);
var p = (from z in o.EMPs where z.ID == i select z).First();
o.EMPs.DeleteObject(p);
o.SaveChanges();
lb.Text = "One record deleted";
fill();
}
}

EXAMPLE OF ADO.NET ENTITY DATA MODEL

Notify icon in web

It's a c# calss.cs file
using System;
using System.Web;
using System.Windows.Forms;
using System.Drawing;

namespace NF
{
public class NotifyClass
{
ToolStripMenuItem[] tsmi;
public NotifyClass()
{

}
public void FunctionNotifyIcon(string message)
{


NotifyIcon ni = new NotifyIcon();
ni.Visible = true;
string path = HttpContext.Current.Server.MapPath("Image/Button-Add.ico");
ni.Icon = new Icon(path, 32, 32);
ni.ShowBalloonTip(3000, "MSG", message, ToolTipIcon.Info);
System.Threading.Thread.Sleep(2000);
ni.Dispose();
}
}
}

EXAMPLE OF INSTEAD OF TRIGGER

I have one table and view that is define below:
CREATE TABLE [B]
(
[ID] [int] ,
[NAME] [varchar](50) ,
[SAL] [decimal](18, 2) ,
[TA] [decimal](18, 2)
)
create view [v] as select id,name,sal from B

TRIGGER:

create trigger tr0 on v instead of insert,delete
as
begin
insert into b select id,name,sal,(sal*.1) from inserted
delete from b where id=(select id from deleted)
end

TRIGGER:
alter trigger tr00 on v instead of update
as
begin
if update(name) or update(sal)
update b set name=(select name from inserted),sal=(select sal from inserted),ta=((select sal from inserted)*.1) where id=(select id from deleted)
end

SQL String Functions

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6


2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR


3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000


4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25


5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000


6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000


7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting


9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA


11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.


13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4


14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2


15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65
16. NCHAR(expression)
Returns a Unicode String with the specified integer code, as defined by the Unicode standard.

Arguments

expression: An Int32.

Return Value

A Unicode String.

Example NCHAR(65)
Result: A
17. QUOTENAME('char_string' [, 'quote_char'])
Returns a Unicode String with the delimiters added to make the input string a valid SQL Server 2005 delimited identifier.

Arguments

char_string: A Unicode String.

quote_char: A one-character string to use as the delimiter are "({[<"". Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_char is not specified, brackets are used.

Return Value

A Unicode String.
Example
select QUOTENAME('siv','"')
Result "siv"
18. REPLICATE(char_expression, int_expression)
Repeats a character expression for a specified number of times.

Arguments

char_expression: A Unicode or ASCII String type.

int_expression: Int64 (not supported in SQL Server 2000) or Int32.

Return Value

A Unicode or ASCII String type.

Example
select REPLICATE('siv ', 2)
Result
siv siv

HOW TO UPLOAD EXCEL FILE AND SAVE IT DATABASE IN DYNAMICLY

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
#region
static OleDbConnection cn = null;
static OleDbConnection cn1 = null;
OleDbCommand cm = null;
OleDbDataAdapter da = null;
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
int i,j,k;
string s, ss = "varchar(50)";
string[] st1;
#endregion
protected void Page_Load(object sender, EventArgs e)
{
cn1 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("swash.accdb") + ";Persist Security Info=False;");
if (!IsPostBack)
{
cn1.Open();
//fill1();
}
}
protected void btn_Click(object sender, EventArgs e)
{
try
{
string s = null;
if (fu.HasFile)
{
string[] st = fu.PostedFile.ContentType.ToString().Split('/');
if (st[0] == "application")
{

st1 = fu.FileName.ToString().Split('.');
s = "test" + "." + st1[1].ToString();
fu.SaveAs(Server.MapPath("~/upload/" + s));
string t = Server.MapPath("~/upload/" + s);
if (st1[1].ToString().Trim() == "xlsx".Trim())
cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + t + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
else
cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + t + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"");
fill();
ViewState["dt"] = st1[0].ToString();
}
else
ClientScript.RegisterStartupScript(GetType(), "x", "");


}
}
catch (Exception ex)
{
Response.Write(""+ex.Message.ToString()+"");
}

}
void fill1(string t)
{
if (cn1.State == ConnectionState.Closed)
cn1.Open();
da = new OleDbDataAdapter("select * from "+t+"", cn1);
dt1.Clear();
da.Fill(dt1);
gv1.DataSource = dt1;
gv1.DataBind();
}
void fill()
{

da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", cn);
dt.Clear();
da.Fill(dt);
gv.DataSource = dt;
gv.DataBind();

}
protected void Button1_Click(object sender, EventArgs e)
{
fill();
try
{
fill();

k = gv.Rows[j].Cells.Count;
ViewState["c"] = k;
s = string.Empty;
if (Convert.ToInt32(tb.Text) > 1)
{
j = (Convert.ToInt32(tb.Text) - 2);
for (i = 0; i < k; i++) { if (gv.Rows[j].Cells[i].Text != " ") { s = s + gv.Rows[j].Cells[i].Text + " " + ss + ","; } } } else { for (i = 0; i < k; i++) { if (gv.HeaderRow.Cells[i].Text != " ") { s = s + gv.HeaderRow.Cells[i].Text + " " + ss + ","; } } } s=s.Substring(0,(s.ToString().Length-1)); if (cn1.State == ConnectionState.Closed) cn1.Open(); cm = new OleDbCommand("create table " + ViewState["dt"].ToString() + " ( " + s + ")", cn1); cm.ExecuteNonQuery(); j = 0; s = string.Empty; k = Convert.ToInt32(ViewState["c"]); if (Convert.ToInt32(tb1.Text) > 1)
{
for (i = (Convert.ToInt32(tb1.Text) - 2); i < (Convert.ToInt32(tb2.Text)-1); i++) { if (cn1.State == ConnectionState.Closed) cn1.Open(); s = string.Empty; for (j = 0; j < k; j++) if (gv.Rows[i].Cells[j].Text != " ") { s = s + "'" + gv.Rows[i].Cells[j].Text + "'" + ","; } s = s.Substring(0, (s.ToString().Length - 1)); cm = new OleDbCommand("insert into " + ViewState["dt"].ToString() + " values(" + s + ")", cn1); cm.ExecuteNonQuery(); } } else { for (i = (Convert.ToInt32(tb1.Text) - 1); i < (Convert.ToInt32(tb2.Text)-1); i++) { if (cn1.State == ConnectionState.Closed) cn1.Open(); s = string.Empty; for (j = 0; j < k; j++) if (gv.Rows[i].Cells[j].Text != " ") { s = s + "'" + gv.Rows[i].Cells[j].Text + "'" + ","; } s = s.Substring(0, (s.ToString().Length - 1)); cm = new OleDbCommand("insert into " + ViewState["dt"].ToString() + " values(" + s + ")", cn1); cm.ExecuteNonQuery(); } } fill1(ViewState["dt"].ToString()); ClientScript.RegisterStartupScript(GetType(), "x", "");
}
catch (DBConcurrencyException ex)
{
Response.Write("" + ex.Message.ToString() + "");
}
}
}

EXAMPLE OF DML TRIGGER

These are the G and G1 tables

create trigger tr123 on G for insert,delete
as
begin
insert into G1 select id,name,getdate(),'INSERT' FROM inserted
insert into G1 select id,name,getdate(),'DELETE' FROM deleted
end

create trigger tr456 on G after update
as
begin
if update(name)
begin
insert into G1 select id,name,getdate(),'NEW' FROM inserted
insert into G1 select id,name,getdate(),'OLD' FROM deleted
end
end

EXAMPLE OF PROCEDURE,CURSOR AND TEMPORARY TABLE

create procedure pp456
AS
create table #tb
(
ID INT,
NAME VARCHAR(50),
SAL MONEY,
TA MONEY,
ROW INT
)
DECLARE @id int,@name varchar(50),@sal money,@c cursor
BEGIN
set nocount on
set @c=cursor for select ID,NAME,SAL FROM B
open @c
fetch next from @c into @id,@name,@sal
while @@fetch_status=0
begin
insert into #tb values(@id,@name,@sal,(@sal*.1),@@CURSOR_ROWS)
fetch next from @c into @id,@name,@sal
end
CLOSE @C
DEALLOCATE @C
select * from #tb
drop table #tb
set nocount off
END
EXECUTE OF THE PROCEDURE
exec pp456

EXAMPLE OF PROCEDURE

create procedure pp123
(
@ID INT,
@NAME varchar(50),
@MARK INT
)
AS
BEGIN
set nocount on
if @MARK=1
INSERT INTO emp VALUES(@ID,@NAME)
ELSE IF @MARK=2
UPDATE emp SET NAME=@NAME WHERE ID=@ID
ELSE IF @MARK=3
DELETE FROM emp WHERE ID=@ID
ELSE IF @MARK=4
SELECT * FROM emp
set nocount off
END
EXECUTE OF THE PROCEDURE
exec pp123(1,'siv')

Table value function

If a function return a table then that function is called table value function
create function fxemp1(@sd datetime,@ed datetime)returns @tb table(cdate datetime,weekdate varchar(50))
as
begin
declare @s varchar(50)
while (@ed>=@sd)
begin
if(datepart(dw,@sd))=1
set @s='S'
else if(datepart(dw,@sd))=2
set @s='M'
else if(datepart(dw,@sd))=3
set @s='T'
else if(datepart(dw,@sd))=4
set @s='W'
else if(datepart(dw,@sd))=5
set @s='Th'
else if(datepart(dw,@sd))=6
set @s='F'
else if(datepart(dw,@sd))=7
set @s='St'
set @s=convert(varchar(2),day(@sd))+' '+@s
insert into @tb values(@sd,@s)
set @sd=dateadd(day,1,@sd)
end
return
end
execute the function
select * from dbo.fxemp1('2012-jan-1','2012-jan-31')

Scalar value function

If a function return a value then that function is called scalar value function
create function fxemp(@cdate datetime)returns varchar(50)
as
begin
return replace(convert(varchar(50),@cdate,106),' ','-')
end

Execute the function
select dbo.fxemp(getdate())

linq commands

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
List x = new List()
{
new emp{ EID=1, NAME="siv", MGR=0, SALARY=5000,DID=1},
new emp{ EID=2, NAME="sankar", MGR=1, SALARY=4000,DID=2},
new emp{ EID=3, NAME="mahadev", MGR=2, SALARY=3000,DID=3},
new emp{ EID=4, NAME="ram", MGR=1, SALARY=2000,DID=5}
};
List y = new List()
{
new dept{ DID=1, DNAME="A"},
new dept{ DID=2, DNAME="B"},
new dept{ DID=3, DNAME="C"},
new dept{ DID=4, DNAME="D"}
};

List z = new List()
{
new salgrade{ LOWSAL=1, HISAL=1000, RANK=1},
new salgrade{ LOWSAL=1001, HISAL=2000, RANK=2},
new salgrade{ LOWSAL=2001, HISAL=3000, RANK=3},
new salgrade{ LOWSAL=3001, HISAL=4000, RANK=4},
new salgrade{ LOWSAL=4001, HISAL=5000, RANK=5}
};
var m = from a in x select a;
var m = from a in x where a.SALARY>=3000 select a;
var m = from a in x where a.SALARY==4000 && a.NAME=="sankar" select a;
var m = from a in x where a.SALARY>=4000 || a.NAME=="sankar" select a;
var m = from a in x where a.NAME!="sankar" select a;
var m = from a in x orderby a.SALARY ascending select a;
var m = from a in x group a by a.DID into b where b.Count() > 1 select new {b.Key,n=b.Count() };
var m = from a in x where a.SALARY < x.Max(b => b.SALARY) select a;
var m = from a in x where a.SALARY > x.Min(b => b.SALARY) select a;
var m = from a in x select new {a.EID,a.NAME,Sumsallary=x.Sum(b=>b.SALARY) };
var m=from a in x
from b in y
where a.DID == b.DID
select new {a.EID,a.NAME,b.DID,b.DNAME};
var m=from a in x
from b in y
from c in z
where a.DID == b.DID && (a.SALARY >= c.LOWSAL && a.SALARY <= c.HISAL) select new {a.EID,a.NAME,a.SALARY,b.DNAME,c.RANK}; var m = from a in x from b in y where 1==1 select new { a.EID, a.NAME, b.DNAME }; var m=from a in x from b in y from c in z where a.DID == b.DID && (a.SALARY >= c.LOWSAL && a.SALARY <= c.HISAL)
select new {a.EID,a.NAME,a.SALARY,b.DNAME,c.RANK};
var m =from a in x
join b in y on a.DID equals b.DID into c
from b in c.DefaultIfEmpty()
select new {a.EID,a.NAME,DNAME=(b==null?null:b.DNAME)};
var m=from a in y
join b in x
on a.DID equals b.DID
into c
from b in c.DefaultIfEmpty()
where b == null
select a;
var m=from a in x
join b in x
on a.MGR equals b.EID
select new { mark = a.NAME + " is working under " + b.NAME };
gv.DataSource = m;
gv.DataBind();
}
class emp
{
public int EID
{
get;
set;
}
public string NAME
{
get;
set;
}
public int MGR
{
get;
set;
}
public decimal SALARY
{
get;
set;
}
public int DID
{
get;
set;
}
}
class dept
{
public int DID
{
get;
set;
}
public string DNAME
{
get;
set;
}
}
class salgrade
{
public decimal LOWSAL
{
get;
set;
}
public decimal HISAL
{
get;
set;
}
public int RANK
{
get;
set;
}
}
}

some sql command

select len('siv')
select upper('siv')
select lower('SIV')
select day(getdate())
select month(getdate())
select year(getdate())
select DATEADD(DAY,1,GETDATE())
select DATEDIFF(DAY,GETDATE(),DATEADD(DAY,1,GETDATE()))
select DATEPART(DW,GETDATE())
select DATEADD(mi,1,GETDATE())
select convert(varchar(50),getdate(),103)
select replace(convert(varchar(50),getdate(),106),' ','-')
select charindex('%','siv%sankar')
select ltrim(' ram')
select rtrim('ram ')
select substring('ram',1,2)
select isnull(mgr,'0') from emp
select * from emp
select * from emp where name='x'
select * from emp where name='x' and eid=2
select * from emp where name='x' or eid=2
select * from emp where name!='x'
select * from emp where name like 'A%'
select * from emp where name like '_i%'
select top 2 * from emp
select * from emp order by sal desc
select distinct sal from emp
select newid(),* from emp
select row_number() over(order by sal desc) RANK,* from emp
select did,count(*) from emp group by did
select did,count(*) from emp group by did having count(*)>1
select *,MARK=case when did=10 then 'siv' when did=20 then 'sankar' else 'mahadev' end from emp
INSERT INTO F SELECT 4,'d' union select 5,'e' union select 6,'f'
select * from emp join dept on emp.did=dept.did
select * from emp left join dept on emp.did=dept.did
select * from emp right join dept on emp.did=dept.did
select * from emp full join dept on emp.did=dept.did
select * from emp cross join dept
select dname from emp join dept on emp.did=dept.did
select dname from emp right join dept on emp.did=dept.did where eid is null
select isnull(m.name,'')+' is working under '+isnull(t.name,'') from emp m join emp t on m.mgr=t.eid
select isnull(m.name,'')+' is working under '+isnull(t.name,'') from emp m gight join emp t on m.mgr=t.eid where m.name is null
select isnull(m.name,'')+' is working under '+isnull(t.name,'') from emp m left join emp t on m.mgr=t.eid where t.name is null
select * from emp where mgr=(select eid from emp where name='x')
select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp))
update f set name=(select name from ff where id=f.id) where id!=2

free asp.ent quick time player control

Saturday, 7 January 2012Source Code of the Free ASP.NET QuickTimePlayer Control
Playing QuickTime movies from an ASP.NET Web form can be a little tricky. At first it may not seem difficult, but there are a lot of little details to worry about — such as browser differences, ActiveX activation, and the list of acceptable parameters. The QuickTimePlayer control detailed here (and shown in Figure 1) takes care of all those issues, reducing the task to drag-and-drop simplicity.


Figure 1: The QuickTimePlayer control eliminates the chores involved with playing QuickTime movies.


How to Use It
The QuickTimePlayer.dll can be added to your Visual Studio toolbox via right-click just like any other control (see end of article for download details). When that task is done, it can be dragged from the toolbox onto any ASP.NET Web form, where a definition similar to this will be rendered to the ASPX:



The source code for this article is available for download here.
If you dont have QuickTimePlayer control you can download it from following url:

https://swdlp.apple.com/cgi-bin/WebObjects/SoftwareDownloadApp.woa/wa/getProductData?localang=en_us&grp_code=quicktime&returnURL=http://www.apple.com/quicktime/download

how to create table in sql server

create table dept
(
DID int identity constraint dept_did_pk primary key,
DNAME varchar(50) constraint dept_dname_nt not null,
LOC varchar(50) constraint dept_loc_ck check(LOC IN('PURI','BBSR','CTC','MUMBAI'))
)

create table EMP
(
EID INT identity constraint emp_eid_pk primary key,
[NAME] varchar(50) constraint emp_name_nt not null,
ADDRESS VARCHAR(50) constraint emp_address_uk unique,
SALARY money constraint emp_sal_nt not null,
DESIGNATION varchar(50) constraint emp_desig_nt not null,
MGR int,
DID int constraint dept_did_fk foreign key references dept(did) on delete cascade on update cascade,
constraint dept_sal_ck check((SALARY=50000 AND DESIGNATION='MANAGER') OR (SALARY=60000 AND DESIGNATION='HR'))
)

Entity Framework - Editing, Updating and Deleting Data in the Form

STEP 1: Setting up the UI

Since this is a continuation of my previous example then I'm jus going to use the same layout and extend it a bit by adding some buttons on the form and a label control for displaying some message. Aside from that I have also moved the form fields inside a Panel control for validation purposes and removed the ReadOnly attribute for each TextBox. Here's the updated mark-up below:




--Select--



First Name:



Last Name:



Contact Number:

















STEP 3: Editing and Updating the Form

Add the following method below in the UserManager class:

public void UpdateUser(UserDetail userDetail) {
var user = (from o in dre.SysUsers
where o.SysUserID == userDetail.SysUserID
select o).First();
user.FirstName = userDetail.FirstName;
user.LastName = userDetail.LastName;
user.ContactNumber = userDetail.ContactNumber;

dre.SaveChanges();
}


The UpdateUser() is a method that handles the updating of data. This method take the UserDetail as the parameter. If you can still remember in my previous example particularly in STEP 2, the UserDatail is a class that holds some properties such as the SysUserID, FirstName, LastName and ContactNumber.

The first line within the method is we queried the SysUser object based on the userID using LINQ syntax and assigned the result into a variable user. The FirstOrDefault function is an eager function which returns the first element of a sequence that satisfies a specified condition. Once the LINQ FirstOrDefault function is invoked then EF will autmatically issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the result to the Entity Model.

After querying the data we then assign a new value to each field and then call SaveChanges() method to update the database with the changes.

Now let's go to the code behind part of the webform and perform the update by calling the method UpdateUser() from the UserManager class. Here are the code blocks below:

private void ToggleButton(bool isEdit){
if (isEdit) {
btnEdit.Visible = false;
btnDelete.Visible = false;
btnUpdate.Visible = true;
btnCancel.Visible = true;
}
else {
btnEdit.Visible = true;
btnDelete.Visible = true;
btnUpdate.Visible = false;
btnCancel.Visible = false;
}

pnlUserDetail.Enabled = isEdit;
}

private void UpdateUserDetail(UserDetail userDetail) {
UserManager userMgr = new UserManager();
userMgr.UpdateUser(userDetail);
}

protected void btnEdit_Click(object sender, EventArgs e) {
if (ddlUser.SelectedItem.Value != "0") {
ToggleButton(true);
lblMessage.Text = string.Empty;
}
else {
lblMessage.Text = "Please select name from the list first.";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}

protected void btnUpdate_Click(object sender, EventArgs e) {

UserDetail userDetail = new UserDetail();
userDetail.SysUserID = Convert.ToInt32(ddlUser.SelectedItem.Value);
userDetail.FirstName = tbFirstName.Text.TrimEnd();
userDetail.LastName = tbLastName.Text.TrimEnd();
userDetail.ContactNumber = tbContactNumber.Text.TrimEnd();

UpdateUserDetail(userDetail);
lblMessage.Text = "Update Successful!";
ToggleButton(false);
}

protected void btnCancel_Click(object sender, EventArgs e) {
ToggleButton(false);
}



The ToggleButton() method is a method that returns a boolean type which is responsible for toggling the visibility of each buttons in the form during Edit,Update,cancel and Delete mode.

The UpdateUserDetail() is a method that takes UserDetail as the parameter. This method is the one who calls the UpdateUser() method from the UserManager class.

At btnUpdate_Click event we create an instance of the UserDetail class and assign the values from the form to the corresponsing fields. After that we call the method UpdateUserDetail() and pass the UserDetail object as the parameter.

Here's the output below when running it on the browser:

On initial load:


After selecting an item from the DropDownList



On Edit Mode (after clicking the edit button)



After Update


STEP 4: Deleting data from the Form

Now lets implement the deletion part of the program. In the UserManager class add this method below:

public void DeleteUser(int userID) {
var user = (from o in dre.SysUsers
where o.SysUserID == userID
select o).First();

dre.DeleteObject(user);
dre.SaveChanges();
}


Just like the UpdateUser() method. We query the SysUser object based on the SysUserID and then assign the result in the variable user. After that we call the DeleteObject() to perform delete and then call the SaveChanges() method to reflect the changes to the database.

Here's the code in the code behind for the deletion:

protected void btnDelete_Click(object sender, EventArgs e) {
if (ddlUser.SelectedItem.Value != "0") {

//Perform the Delete
UserManager userMgr = new UserManager();
userMgr.DeleteUser(Convert.ToInt32(ddlUser.SelectedItem.Value));

//Re-bind the DropDownList
ddlUser.Items.Clear();
BindUserNames();

//Clear the form fields
tbFirstName.Text = string.Empty;
tbLastName.Text = string.Empty;
tbContactNumber.Text = string.Empty;

lblMessage.Text = "Delete Successful!";

}
else {
lblMessage.Text = "Please select name from the list first.";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}


Since we don’t want users to delete the information right away, we need to prompt them a confirmation message if they wish to continue the deletion or not. To do this we could simply hook up the javascript confirm function in the delete button. Take a look at the highlighted code below:




Here's the output below when running the page and perform the delete:

On Deletion:



After Deletion:

DataGrid in WPF using SQL Server Compact 3.5 Sp1

This article will help you to create a simple WPF application to bind data in datagrid using SQL Server Compact 3.5 sp1 as data source. In this article, I would like to show the simple way of data binding to datagrid in WPF.
In this article, I have used Visual Studio 2010 to demonstrate the example. Below find the steps to create new WPF project.
Create New WPF Project
Step 1: Click Start All Program à Microsoft Visual Studio 2010 à Microsoft Visual Studio 2010
Step 2: Click “New Project”
Step 3: Under “Visual C#” à Windows à WPF Application


Give application name for your new WPF applications, mention the location and solution name.
Start Designing and Programming using WPF
Designing windows or web application is quite easy and interesting also. Let’s start designing our first application using WPF.


WPF will generate XAML tags similar to HTML. Here I have used the below mentioned tools for design:

fName_lbl -> “First Name” Label
fName_Txt -> Textbox
lName_lbl -> “Last Name” Label
lName_Txt -> Textbox
DOB_lbl -> “Date of Birth” Label
DOB_Txt -> Date Picker
City_lbl -> “City” Label
City_Txt -> Combo Box
New_Btn -> “New” Button
Add_Btn -> “Add” Button
Del_Btn -> “Delete” Button
Update_Btn -> “Update” Button

Datagrid1
Databinding to “Datagrid1” in WPF is simple, in this example, I have used ADO.NET for binding.







< /datagridtextcolumn>


We can bind the data to datagrid by assigning it to datagrid’s “ItemsSource” and mention the data path which will connect to database to get the data. This is an add on feature in WPF.
App.config is a configuration file which will have the setting and other stuff for an application. Here, I have used this to store the database connectionstring and it is as follows:







Here, I have used SQL Server Compact 3.5 sp1 as my data source so we have to give the exact path of the database file where it is stored. (Note: You need to give exact path of the database file to make this code work).
Whenever we add, delete, update datagrid has to be changed accordingly, so I created a public method named “BindGrid()”.

// Establishing Connection String from Configuration File
string _ConnectionString = ConfigurationManager.ConnectionStrings
["ConnectionString1"].ConnectionString;

public void BindGrid()
{
SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);

// Open the Database Connection
_Conn.Open();

SqlCeDataAdapter _Adapter = new SqlCeDataAdapter("Select * from Details", _Conn);

DataSet _Bind = new DataSet();
_Adapter.Fill(_Bind, "MyDataBinding");

dataGrid1.DataContext = _Bind;

// Close the Database Connection
_Conn.Close();
}

In the above, I have shown a very simple way to get the SQL connection string which will connect to database and used dataset to bind the data to datagrid. The below code will add new record using the values from the textbox.
Add New Record
To add new record on database, I had used simple insert query and it will be populated in the datagrid. The below will do the trick:

private void Add_Btn_Click(object sender, RoutedEventArgs e)
{
try
{
SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);

// Open the Database Connection
_Conn.Open();

string _Date = DOB_Txt.DisplayDate.ToShortDateString();

// Command String
string _Insert = @"insert into Details
(fName,lName,DOB,City)
Values('" + fName_Txt.Text + "','" + lName_Txt.Text + "','" +
_Date.ToString() + "','" + City_Txt.Text + "')";

// Initialize the command query and connection
SqlCeCommand _cmd = new SqlCeCommand(_Insert, _Conn);

// Execute the command
_cmd.ExecuteNonQuery();

MessageBox.Show("One Record Inserted");
fName_Txt.Text = string.Empty;
lName_Txt.Text = string.Empty;
DOB_Txt.Text = string.Empty;
City_Txt.Text = string.Empty;

this.BindGrid();

}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

To store only date into the database, here I have used “ToShortDateString” which gives trim the time.

string _Date = DOB_Txt.DisplayDate.ToShortDateString();



To confirm that a record has been added, I have a messagebox with the message “One Record Inserted” and once you click “Ok”, you should see the added record to be listed in datagrid (refer to the below image).


private void Del_Btn_Click(object sender, RoutedEventArgs e)
{
try
{
SqlCeConnection _conn = new SqlCeConnection(_ConnectionString);

// Open Database Connection
_conn.Open();

// Command String
string _DelCmd = @"Delete from Details
Where fName='" + fName_Txt.Text + "'";

// Initialize the command query and connection
SqlCeCommand _CmdDelete = new SqlCeCommand(_DelCmd, _conn);

// Execute the command
_CmdDelete.ExecuteNonQuery();

MessageBox.Show("One Record Deleted");
fName_Txt.Text = string.Empty;
lName_Txt.Text = string.Empty;
DOB_Txt.Text = string.Empty;
City_Txt.Text = string.Empty;

this.BindGrid();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

To update the existing data, just double click on the record on the datagrid and the values will be edited, will be listed in their respective textboxes as shown in the below image:


The below code will perform editing the datagrid value:

DataRowView _DataView = dataGrid1.CurrentCell.Item as DataRowView;

if (_DataView != null)
{
fName_Txt.Text = _DataView.Row[0].ToString();
fName_Txt.IsEnabled = false;
lName_Txt.Text = _DataView.Row[1].ToString();
DOB_Txt.Text = _DataView.Row[2].ToString();
City_Txt.Text = _DataView.Row[3].ToString();
}

Here I have used “DataRowView” to read the currentcell of the datagrid and fetch each cell value and have assigned it to a textbox.
You can’t change the first name as we are using its value as primary key in the database, so you can change on the other available fields. In this example, I have changed the last name to “Sellamuthu”.


Update Code

private void Update_Btn_Click(object sender, RoutedEventArgs e)
{
try
{
SqlCeConnection _Conn = new SqlCeConnection(_ConnectionString);

// Open Database Connection
_Conn.Open();

string _Date = DOB_Txt.DisplayDate.ToShortDateString();

// Command String
string _UpdateCmd = @"Update Details Set
lName = '" + lName_Txt.Text + "',
DOB = '" + _Date.ToString() + "',
City = '" + City_Txt.Text + "'
where fName = '" + fName_Txt.Text + "'";

// Initialize the command query and connection
SqlCeCommand _CmdUpdate = new SqlCeCommand(_UpdateCmd,_Conn);

// Execute the command
_CmdUpdate.ExecuteNonQuery();

MessageBox.Show("One Record Updated");
fName_Txt.Text = string.Empty;
lName_Txt.Text = string.Empty;
DOB_Txt.Text = string.Empty;
City_Txt.Text = string.Empty;

this.BindGrid();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

I have used Update query to update the record on the database by referring to the primary key “First Name”.



Here is the screenshots of all refrences and namespace i have used


Download source code here:
Download source - 308.08 KB

for some more Effective try this:
Source code:




































code behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Data;




namespace ShibashishWpfApp
{
///

/// Interaction logic for MainWindow.xaml
///

public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
string _ConnectionString = ConfigurationManager.ConnectionStrings["Connectionstring"].ConnectionString;
string Query;
SqlCeDataAdapter da;
SqlCeConnection con;
DataSet ds;
SqlCeCommand cmd;
private void Add_Btn_Click(object sender, RoutedEventArgs e)
{

insertData();
clearData();
}

public void common()
{
con = new SqlCeConnection(_ConnectionString);
con.Open();

}
public void ExecuteAndBindData()
{
cmd = new SqlCeCommand(Query, con);
cmd.ExecuteNonQuery();
BindGrid();
}
public void insertData()
{
common();
Query = "insert into Details(fName,lName,Dob,City)" + "values('" + fName_Txt.Text + "','" + lName_Txt.Text + "','" + DOB_Txt.ToString() + "','" + City_Txt .Text+ "')";
ExecuteAndBindData();

}
public void upadateData()
{

common();
Query = "update Details set lName='" + lName_Txt.Text + "',Dob='" + DOB_Txt.ToString() + "',City='" + City_Txt.Text + "' where fName='" + fName_Txt.Text + "'";
ExecuteAndBindData();
}
public void DeleteData()
{
common();
Query = "delete from Details where fName='" + fName_Txt.Text + "'";
ExecuteAndBindData();
}
public void BindGrid()
{
try
{

Query = "select * from Details";
common();
da = new SqlCeDataAdapter(Query, con);
ds = new DataSet();
da.Fill(ds, "Details");
GridView1.DataContext = ds;

con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public void clearData()
{
fName_Txt.Text = lName_Txt.Text = DOB_Txt.Text = City_Txt.Text = "";
}


private void Window_Loaded(object sender, RoutedEventArgs e)
{
BindGrid();

}

private void Update_Click(object sender, RoutedEventArgs e)
{
upadateData();
clearData();
}

private void Delete_Click(object sender, RoutedEventArgs e)
{
DeleteData();
clearData();
}

private void New_Click(object sender, RoutedEventArgs e)
{
if (fName_Txt.IsEnabled != true)
{
fName_Txt.IsEnabled = true;
}
clearData();
}

private void GridView1_SelectedCellsChanged(object sender, SelectedCellsChangedEventArgs e)
{
DataRowView RowChk = (DataRowView)GridView1.CurrentCell.Item;
if (RowChk != null)
{
fName_Txt.Text = RowChk.Row[0].ToString();
fName_Txt.IsEnabled = false;
lName_Txt.Text = RowChk.Row[1].ToString();
DOB_Txt.Text = RowChk.Row[2].ToString();
City_Txt.Text = RowChk.Row[3].ToString();
}
}


}
}

App.config:








Add database:
Right click on project -->Add New Item -->Select Local Database

open a new page in linq

Page1 p = new Page1();
NavigationWindow navWindow = new NavigationWindow();
navWindow.Content = p;
navWindow.Show();

wpf

xml:




























































































code:
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;


namespace WpfApplication2
{
///

/// Interaction logic for MainWindow.xaml
///

public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
#region
ArrayList al = new ArrayList();
Database1Entities o = new Database1Entities();
int i,j;
string s,ss;
#endregion
private void Window_Loaded(object sender, RoutedEventArgs e)
{
view();
al.Add("--------Select--------");
al.Add("What is ur name?");
al.Add("What is ur vabority movie?");
al.Add("What is ur vabority game?");
al.Add("What is ur vabority place?");
for (i = 0; i < al.Count; i++)
comboBox1.Items.Add(al[i].ToString());
comboBox1.SelectedIndex = 0;

listBox1.Items.Add("Cricket");
listBox1.Items.Add("Hockey");
listBox1.Items.Add("Football");
listBox1.Items.Add("Baseball");
}
void view()
{
var m = from x in o.EMP1 select new {x.EID,x.NAME,x.ADDRESS,x.SEX,x.DOB,x.HOBBY,x.INTEREST,x.QUESTION,x.PASSWORD};
dataGrid1.ItemsSource = m;
}
private void button1_Click(object sender, RoutedEventArgs e)
{
s=string.Empty;
ss=string.Empty;
if (radioButton1.IsChecked==true)
s = radioButton1.Content.ToString();
else
s = radioButton2.Content.ToString();
if (checkBox1.IsChecked==true)
ss =ss+ checkBox1.Content.ToString()+" ";
if(checkBox2.IsChecked==true)
ss = ss + checkBox2.Content.ToString() + " ";
if (checkBox3.IsChecked == true)
ss = ss + checkBox3.Content.ToString() + " ";
if (checkBox4.IsChecked == true)
ss = ss + checkBox4.Content.ToString() + " ";
o.EMP2(textBox1.Text, textBox2.Text, s, Convert.ToDateTime(datePicker1.SelectedDate), ss, listBox1.SelectedItem.ToString(), comboBox1.SelectedItem.ToString(), passwordBox1.Password.ToString());
o.SaveChanges();
view();
}
}
}

Nasted GridView using Entity DataModel

Linq to SQL Screen Shot

STEP 1: Creating a new Website in Visual Studio

To get started then lets go ahead and fire up Visual Studio 2008 and create a new WebSite by selecting File > New WebSite.

STEP 2: Adding a DBML file

Since we are going to use L2S then we need to add .dbml file. To do this, just right click on the application root and select Add New Item. On the template select LINQ to SQL Classes file. See below screen shot:


Now rename your dbml file the way you want it and then click OK. Note that I’m using the Northwind database for this demo and on that case I renamed the dbml file to Northwind to make it friendlier.

Now open up server explorer in Visual Studio and browse the database that you wan’t to work on (in this case the Northwind database). Just for the purpose of this example I’m going to use the Customers table from the northwind database and drag it to the Northwind.dbml design surface. See the screen shot below:


That’s simple! Isn’t it?

What happens there is that by time you drag a table in the design surface, L2S will automatically generates the Business object for you within the DataContext and let you query against it.The DataContext is the main gateway by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select

STEP 3: Setting up the GUI

Now let’s go ahead and create our form for data entry. For the simplicity of this demo, I just set up the form like below:

Company ID
Company Name
Contact Name
Contact Title
Address
City
Region
Postal Code
Country

STEP 4: Creating the SaveCustomerInfo() method

After setting up our GUI then let’s go ahead and create the method for inserting the data to the database using L2S. Here are the code blocks below:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SaveCustomerInfo();
}

private void SaveCustomerInfo()
{
using (NorthwindDataContext context = new NorthwindDataContext())
{
//Create a new instance of the Customer object
Customer cust = new Customer();
//Add new values to each fields
cust.CustomerID = TextBoxID.Text;
cust.CompanyName = TextBoxCompanyName.Text;
cust.ContactName = TextBoxContactName.Text;
cust.ContactTitle = TextBoxContactTitle.Text;
cust.Address = TextBoxAddress.Text;
cust.City = TextBoxCity.Text;
cust.Region = TextBoxRegion.Text;
cust.PostalCode = TextBoxPostalCode.Text;
cust.Country = TextBoxCountry.Text;

//Insert the new Customer object
context.Customers.InsertOnSubmit(cust);
//Sumbit changes to the database
context.SubmitChanges();

//Display Message for successful operation
LiteralMessage.Text = "

Information Successfully saved!

";
}
}
}


As you can see, the code above was very straight forward. First we have created a new instance of the DataContext which we had created on STEP 2 and wrapped it inside the “using” block; this is to ensure that the DataContext will be disposed after its processing. Second we created a new instance of the Customer object that was defined within the DataContext, this object has properties which will be filled with values that comes from the user inputs. Third we inserted a new Customer object to the Customers set and then call the context.SubmitChanges to update our database. Lastly, L2S will do the rest for you ;).

Note: The Customer and Customers set objects are automatically created once you’ve added the Customer table in the .dmbl design surface.

STEP 5: Run the code

Running the code above will look something like below on the browser:


From there we can fill in those fields with values we want. Just for this demo, notice that I have filled in those fields with a sample data. Hitting the save button will invoke the method SaveCustomerInfo() which is responsible for doing the insert operation. Now if we look at the database we can see that the data we entered was successfully being saved to the database. See the screen shot below:


Cool right? ;)

Okay, I know that you have few questions that pops in your mind now and these are:

· What happened behind the scene? How does it actually save the data to the database?
· How does the query being constructed? Does it handle SQL Injection?
· How does the connection string being set up? What If I want to set the connection string manually?
· Does L2S always open the connection to the database once we created a new instance of the DataContext?

To answer the questions that you have in mind then I would suggest you to give this FAQ a read: http://msdn.microsoft.com/en-us/library/bb386929.aspx
I hope someone find this useful!


Fetching Data from Database and Populating fields in the Form using LINQ to SQL

In my previous example I have demonstrated how to create a simple form that would allow users to insert data to the database using L2S. In this example I’m going to demonstrate how to fetch and filter data from database and fill the fields in the form using L2S. This example is basically a continuation of my previous example here. So I would suggest you to take a look at my previous example first before you proceed reading this example.

STEP 1: Setting up the GUI

To get started, add a new WebForm to your application and set up the GUI. Again just for the simplicity of this demo,I just set up the GUI like this:

Company Name
Contact Name
Contact Title
Address
City
Region
Postal Code
Country

If you notice, I set the ReadOnly attribute of each TextBox to True; this is because we don’t need users to edit the fields in the form once the TextBox is filled with data.

STEP 2: Populating the DropDownList with the list of Customers.

Now on our code behind page let’s go ahead and create the method for fetching the list of customers. Here’s the code block below:

private List GetCustomers(){
using (NorthwindDataContext context = new NorthwindDataContext()){
return (from c in context.Customers select c).ToList();
}
}

The code above is the LINQ syntax for querying data. It basically query the Customers object that is generated from the DataContext and then fetch the results.

Since we are done creating the method for fetching the list of customers then we can simply call that method and populate the DropDownList with the results. Typically we do this at Page_Load event within Not IsPostBack block like below:

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) {
DropDownListCustomerID.DataSource = GetCustomers();
DropDownListCustomerID.DataTextField = "ContactName";
DropDownListCustomerID.DataValueField = "CustomerID";
DropDownListCustomerID.DataBind();
}
}

As you can see the code above is very straight forward and self explanatory. Running the code above will display something like below:



Let’s proceed and continue with the next Step.

STEP 3: Populating the form with Customer’s Information

The next step is we are going to populate the form with the customer information based on the CustomerID selected from the DropDownList.

Note: Since the form will be populated based on the selected item from the DropDownList then you’ll need to set up the AutoPostBack attribute to TRUE in the DropDownList so that the SelectedIndexChanged event will fire up.

Here’s the code block below for fetching the customer information based on customer ID:
private List GetCustomerInfo(string customerID) {
using (NorthwindDataContext context = new NorthwindDataContext()){
return(from c in context.Customers
where c.CustomerID == customerID
select c).ToList();
}
}

The code above is the LINQ syntax for querying data. As you can see we created a new instance of the DataContext and query the Customers object based on the parameter we passed on the GetCustomerInfo() method. Once we invoke the LINQ ToList() function, this LINQ query will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the DataContext.

One of the cool things about L2S is we don’t need to worry about how the query is being constructed because L2S will take care of that for you including mapping of the data types from your table columns, mapping relationships between tables, etcetera, etcetera and etcetera. Always keep in mind that L2S is an ORM (Object Relational Mapper) and so we don’t need to deal directly with databases, tables and columns but instead we deal with the objects that is in the DataContext and query the data against it using LINQ syntax.

STEP 4: Populating the Forms with Data

The next step is to populate our form with data based on the selected value from the DropDownList. To do this, we can simply call the method GetCustomerInfo() at the SelectedIndexChanged event of DropDownList like below:

protected void DropDownListCustomerID_SelectedIndexChanged(object sender, EventArgs e) {
var customerInfo = GetCustomerInfo(DropDownListCustomerID.SelectedValue);
TextBoxCompanyName.Text = customerInfo[0].CompanyName;
TextBoxContactName.Text = customerInfo[0].ContactName;
TextBoxContactTitle.Text = customerInfo[0].ContactTitle;
TextBoxAddress.Text = customerInfo[0].Address;
TextBoxCity.Text = customerInfo[0].City;
TextBoxRegion.Text = customerInfo[0].Region;
TextBoxPostalCode.Text = customerInfo[0].PostalCode;
TextBoxCountry.Text = customerInfo[0].Country;
}

The code above calls the method GetCustomerInfo() and pass the selected value of the DropDownList as a parameter to that method. We then store it a customerInfo implicit typed variable and assign each TextBox with the corresponding data returned from the query.

When you run the code above and select an item in the DropDownList, you will see that the textbox fields will be populated with data based from what you have selected in the DropDownList.



That's it! I Hope someone find this post useful!

Editing, Updating and Deleting Data in the Form using LINQ to SQL
In my previous example we talked about how to fetch data from database and how to populate the form with data using L2S. In this example I’m going to extend a little bit of what I have demonstrated in my previous example. Basically I’m going to show you the basic way on how to edit and delete data from the form and update the database using L2S technology. Since this is a continuation of my previous example so I would suggest you to refer that first before you go any further.

STEP 1: Setting up the GUI

Just for the purpose of this demo, I set up the GUI like below:

Company Name
Contact Name
Contact Title
Address
City
Region
Postal Code
Country

If you look at my previous example you will notice that the html markup above is a bit similar. The things that are added above are Buttons for Edit, Delete, Update and Cancel which is by default set their enable property to false, this is to prevent users from doing certain actions when the page is loaded in the browser. Aside from that I have also move the form fields inside a Panel control for validation purposes and removed the ReadOnly attribute for each TextBox.

STEP 2: Populating the DropDownList with the list of Customers and populate the form with the Customers information.
Now the next step if we are going to populate the DropDownList with the list of customers and populate the form with the customers information based on the customer ID selected from the DropDownList. Here are the code blocks below:

private List GetCustomers(){
using (NorthwindDataContext context = new NorthwindDataContext()){
return (from c in context.Customers select c).ToList();
}
}

private List GetCustomerInfo(string customerID) {
using (NorthwindDataContext context = new NorthwindDataContext()){
return(from c in context.Customers
where c.CustomerID == customerID
select c).ToList();
}
}
private void BindCustomersToList(){
DropDownListCustomerID.DataSource = GetCustomers();
DropDownListCustomerID.DataTextField = "ContactName";
DropDownListCustomerID.DataValueField = "CustomerID";
DropDownListCustomerID.DataBind();
}
protected void Page_Load(object sender, EventArgs e){
if (!Page.IsPostBack) {
BindCustomersToList();
}
}
protected void DropDownListCustomerID_SelectedIndexChanged(object sender, EventArgs e) {
var customerInfo = GetCustomerInfo(DropDownListCustomerID.SelectedValue);
TextBoxCompanyName.Text = customerInfo[0].CompanyName;
TextBoxContactName.Text = customerInfo[0].ContactName;
TextBoxContactTitle.Text = customerInfo[0].ContactTitle;
TextBoxAddress.Text = customerInfo[0].Address;
TextBoxCity.Text = customerInfo[0].City;
TextBoxRegion.Text = customerInfo[0].Region;
TextBoxPostalCode.Text = customerInfo[0].PostalCode;
TextBoxCountry.Text = customerInfo[0].Country;

ButtonEdit.Enabled = true;
ButtonDelete.Enabled = true;
}

In this step I will not elaborate more on details because I have already demonstrated this in my previous example here. The only thing that’s added in the code above is we are setting the Enabled attribute of the Edit and Delete Button to true so that by the time users select certain Customer from the DropDownList then that’s the time that they can do certain operations like editing and deleting.

Running the code above will show something like this in the browser:


Selecting customers from the DropDownList



After selecting customers from the DropDownList







STEP 3: Editing the Form

Here’s the code for the Edit Button

protected void ButtonEdit_Click(object sender, EventArgs e)
{
PanelCustomerInfo.Enabled = true;
DropDownListCustomerID.Enabled = false;
ButtonEdit.Enabled = false;
ButtonDelete.Enabled = false;
ButtonUpdate.Enabled = true;
ButtonCancel.Enabled = true;
LiteralMessage.Text = string.Empty;
}

As you can see there’s nothing special about the codes above. It just basically does some basic validations when you hit the Edit button on the form.

STEP 4: Updating the Form
Here’s the code for the Update method

private void UpdateCustomerInfo(string ID)
{
using (NorthwindDataContext context = new NorthwindDataContext())
{
var customer = (from c in context.Customers
where c.CustomerID == ID
select c).Single();

customer.CompanyName = TextBoxCompanyName.Text;
customer.ContactName = TextBoxContactName.Text;
customer.ContactTitle = TextBoxContactTitle.Text;
customer.Address = TextBoxAddress.Text;
customer.City = TextBoxCity.Text;
customer.Region = TextBoxRegion.Text;
customer.PostalCode = TextBoxPostalCode.Text;
customer.Country = TextBoxCountry.Text;

context.SubmitChanges();

LiteralMessage.Text = "

Information Updated!

";
}
}

As you can see, the code above is very straight forward and self explanatory. What happened there is we created a new instance of the DataContext and then we query the Customer object based on the ID using the LINQ syntax and passed it in a variable customer. The Single function is an eager function which returns the only element of a sequence that satisfies a specified condition. Once the LINQ Single function is invoked then DataContext will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the DataContext.


From there, we can then assign the customer fields based on the TextBox values and then call the context.SubmitChanges() method to update the database with the changes we made.

Now let’s try to run the code and see what happens:

Invoking the Edit Button





On Editing




After Invoking the Update Button


STEP 5: Deleting Customer
Here’s the code for the Delete method

private void DeleteCustomerInfo(string ID)
{
using (NorthwindDataContext context = new NorthwindDataContext())
{
var customer =(from c in context.Customers
where c.CustomerID == ID
select c).First();

context.Customers.DeleteOnSubmit(customer);
context.SubmitChanges();
LiteralMessage.Text = "

Information Deleted!

";
}
}

Just like in the update method, the code above creates a new instance of the DataContext and then query the customer entity based on the ID. Note that since I am using the northwind database in this demo, then deleting of customer data directly will throw an exception because this table is being referenced to other table like Orders . So in order for the code above to work and just for the simplicity of this demo, I remove the relationships to the table that referenced it. If you wan't to implement cascade delete then you have to delete the related information to the other table. So for example if you have customer and this customer has orders then you'll have to perform delete in both tables to avoid exceptions. Here's an article that you can refer:Cascading Deletes in LINQ to SQL
Since we don’t want users to delete the information right away, we need to prompt them a confirmation message if they wish to continue the deletion or not. To do this we could simply hook up the javascript confirm function in the delete button. Take a look at the highlighted code below:



Now let’s create the method for clearing the text fields and then call the method created above on click on the delete button. Here are the code blocks below:


public static void ClearFormFields(Control Parent)
{
if (Parent is TextBox)
{ (Parent as TextBox).Text = string.Empty; }
else
{
foreach (Control c in Parent.Controls)
ClearFormFields(c);
}
}



protected void ButtonDelete_Click(object sender, EventArgs e)
{
//Call the DELETE Method
DeleteCustomerInfo(DropDownListCustomerID.SelectedValue);
//Rebind the DropDownList to reflect the changes after deletion
BindCustomersToList();
//Clear the fields
ClearFormFields(Page);
}


Here's the output below when running the page and perform the delete:
On Deletion


After Deletion


STEP 6: Cancelling Operation

Here’s the code for the Cancel Button

protected void ButtonCancel_Click(object sender, EventArgs e)
{
PanelCustomerInfo.Enabled = false;
DropDownListCustomerID.Enabled = true;
ButtonEdit.Enabled = true;
ButtonDelete.Enabled = true;
ButtonUpdate.Enabled = false;
ButtonCancel.Enabled = false;
}

There's nothing fancy about the code above, It just basically toggles the enable property of the button when you hit the Cancel button on the form so that it will return to its default state.

SUMMARY:
In this demo we have learned about the basics on how to perform Edit,Update and Delete using the LINQ to SQL technology.
That's it! I hope someone find this useful!