Saravanan: Source codes

test.aspx

<%@ Page language="c#" Codebehind="test.aspx.cs" AutoEventWireup="false" Inherits="WebApplication1.test" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>test</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:datagrid id="dg" runat="server" width="80%" CellSpacing="1" GridLines="None" CellPadding="3"
BackColor="White" ForeColor="Black" OnPageIndexChanged="dg_Page" PageSize="10" AllowPaging="True"
DataKeyField="eid" Font-Size="X-Small" AutoGenerateColumns="false" AllowSorting="True" Font-Names="Verdana"
OnDeleteCommand="dg_Delete" OnItemCreated="dg_ItemCreated" OnEditCommand="dg_Edit" OnCancelCommand="dg_Cancel"
OnUpdateCommand="dg_Update" OnItemCommand="dg_Item" OnSortCommand="dg_Sort">
<FooterStyle backcolor="#C6C3C6"></FooterStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle>
<PagerStyle horizontalalign="Right" backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
<AlternatingItemStyle backcolor="LightSteelBlue"></AlternatingItemStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
<Columns>
<asp:TemplateColumn headertext="Email Status" SortExpression="EmailStatus" FooterText="Email Status">
<ItemTemplate>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn headertext="Email Type" SortExpression="EmailType" FooterText="Email Type">
<ItemTemplate>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn headertext="Email Content" SortExpression="Emailcontent" FooterText="Email Content">
<ItemTemplate>
</ItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete" DataTextField="eid" DataTextFormatString="Delete"></asp:ButtonColumn>
</Columns>
</asp:datagrid><asp:button id="btnAdd" onclick="dg_Add" runat="server" Font-Size="smaller" Text="Add New"></asp:button>
</form>
<asp:label id="Label1" runat="server" Visible="False"></asp:label><asp:label id="Label2" runat="server" Visible="False"></asp:label><asp:label id="Label3" runat="server" Visible="True"></asp:label><br>
<asp:label id="lblMessage" runat="server" ForeColor="Red" Font-Bold="True">Label</asp:label>
</body>
</HTML>

--------------------------------------------------------------------------------

test.aspx.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace WebApplication1
{
///

/// Summary description for test.
///

public class test : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid dg;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label3;
protected string strEmailContent;
protected string strEmailStatus;
protected string message="";
protected string strSqlConn ="Server=servername;Database=itrfsdev;UID=rfs;PWD=rfs;";
SqlConnection sqlConn = new SqlConnection("Server=servername;Database=itrfsdev;UID=rfs;PWD=rfs;");
protected System.Web.UI.WebControls.Label lblMessage;
bool bEditing =false;
private DataSet ds;
private string action;
protected System.Web.UI.WebControls.Button btnAdd;
private int clickedIndex;
public void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
this.Label1.Text="ASC";
BindGrid();
}
}

private void BindGrid()
{

SqlDataAdapter sda= new SqlDataAdapter("select rfses.eid as eid,rfses.emailstatus as emailstatus,rfses.emailtype as emailtype ,rfset.emailcontent as emailcontent from rfs_emailtemplates rfset " +
" inner join rfs_emailstatus rfses on rfset.eid= rfses.eid ",sqlConn);
try
{
ds = new DataSet();
sda.Fill(ds);
sda.Fill(ds,"MessageNotes");
DataView dv= ds.Tables["MessageNotes"].DefaultView;
if(Label2.Text!="")
{
dv.Sort = this.Label2.Text+ " " + Label1.Text;
}
dg.DataSource= dv;
dg.DataBind();
Session.Add("MessageNotes",ds);
this.lblMessage.Visible=false;
}
catch(Exception e)
{
try
{
this.dg.CurrentPageIndex=0;
this.dg.DataBind();
}
catch
{
this.lblMessage.Text="No data for selection";
this.lblMessage.Visible=true;
}
}
finally
{
sqlConn.Close();
}
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

///

/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

private void InitializeComponent()
{
this.dg.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dg_ItemCommand);
this.dg.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dg_ItemDataBound);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
protected void dg_Page(object sender, DataGridPageChangedEventArgs dpe)
{
if(!bEditing)
{
dg.CurrentPageIndex= dpe.NewPageIndex;
BindGrid();
}
}

protected void dg_Sort(object sender, DataGridSortCommandEventArgs dse)
{
if(!bEditing)
{
if(Label2.Text == dse.SortExpression)
{
if( Label1.Text == "ASC" )
{
this.Label1.Text = "DESC";
}
else
{
this.Label1.Text = "ASC";
}
}
Label2.Text = dse.SortExpression;
BindGrid();
}
}

protected void dg_Edit(object sender, DataGridCommandEventArgs e)
{
if(e.CommandName == "Edit")
{
dg.EditItemIndex = e.Item.ItemIndex;
BindGrid();
SingleColumn(true);
}
}

protected void dg_Cancel(object sender, DataGridCommandEventArgs dce)
{
dg.EditItemIndex = - 1;
BindGrid();
SingleColumn(true);
}

protected void dg_Update(object sender, DataGridCommandEventArgs due)
{
string emailContent="";
dg.EditItemIndex = - 1;
int ctrindex = 3;
int index =0;
sqlConn.Open();
string sTID = dg.DataKeys[(int)due.Item.ItemIndex].ToString();
if(action=="Update" && ViewState["index"]!=null)
{
index = int.Parse(ViewState["index"].ToString())+3;
}
else if(action=="Add")
{
index = ds.Tables[0].Rows.Count;
}
string ClientIDBase = dg.ClientID + ":_ctl" +index+ ":_ctl" +ctrindex;
if( Request.Params[ClientIDBase]!=null)
{
emailContent = Request.Params[ClientIDBase].Trim();
string strcmd= "update rfs_emailTemplates set emailcontent='"+emailContent+"' where eid="+sTID;
SqlCommand sdcmd= new SqlCommand(strcmd,sqlConn);
try
{
sdcmd.ExecuteNonQuery();
}
catch(SqlException se)
{
Label3.Text = se.ToString();
}
finally
{
sqlConn.Close();
}
BindGrid();
SingleColumn(true);
message="Email content updated successfully";
DisplayMessage(message);
}
}

public DataSet BindEmailStatus()
{
string sql= " select distinct EmailStatus,eid from rfs_emailstatus order by eid";
DataSet ds = getDataSet(sql);
return ds;
}

public DataSet BindEmailType()
{
string sql= " select distinct EmailType from rfs_emailstatus";
DataSet ds = getDataSet(sql);
return ds;
}

public DataSet getDataSet(string sql)
{
DataSet dataSet = new DataSet();
SqlDataAdapter DetailsAdapter = new SqlDataAdapter();
SqlCommand myCommand =new SqlCommand();
myCommand.CommandText = sql;
myCommand.CommandType = CommandType.Text;
DetailsAdapter.SelectCommand = myCommand;

try
{
sqlConn.Open();
myCommand.Connection = sqlConn;
DetailsAdapter.Fill(dataSet,"myTable");
}
catch(Exception e)
{
closeOleDbConnection(sqlConn);
throw;
}

closeOleDbConnection(sqlConn);
return dataSet;
}

private void closeOleDbConnection(SqlConnection sqlConn)
{
if( sqlConn != null)
sqlConn.Close();
}

protected void dg_Delete(object sender, DataGridCommandEventArgs dde)
{
if(!bEditing)
{
string sTID = dg.DataKeys[(int)dde.Item.ItemIndex].ToString();
sqlConn.Open();
SqlCommand sdcmd= new SqlCommand("delete from rfs_emailTemplates where eid="+sTID,sqlConn);
sdcmd.ExecuteNonQuery();
sqlConn.Close();
dg.CurrentPageIndex=0;
dg.EditItemIndex=-1;
BindGrid();
message= " Record deleted successfully";
DisplayMessage(message);
}
}

protected void dg_Add(object sender, EventArgs e)
{
CheckEditing("");
int index =0;
action = "Add";
if(Session["MessageNotes"] != null)
{
ds = Session["MessageNotes"] as DataSet;
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
dg.DataSource = ds.Tables[0].DefaultView;
dg.DataBind();
}
if(!bEditing)
{
if(ViewState["index"]!=null)
{
index = int.Parse(ViewState["index"].ToString())+3;
}
}
}

public void InsertEmailContent()
{
int index=0;
string emailContent="";
int eID =0;
int ctrindex=2;
if(action=="Update" && ViewState["index"]!=null)
{
index = int.Parse(ViewState["index"].ToString())+3;
}
else if(action=="Save")
{
if(Session["MessageNotes"] != null)
{
ds = Session["MessageNotes"] as DataSet;
index = ds.Tables[0].Rows.Count+2;
}
}
string emailStatusClientIDBase = dg.ClientID + ":_ctl" +index+ ":_ctl" +ctrindex;
string emailContentClientIDBase = dg.ClientID + ":_ctl" +index+ ":_ctl" +(ctrindex+2);
sqlConn.Open();
try
{
if( Request.Params[emailStatusClientIDBase]!=null)
{
eID = int.Parse (Request.Params[emailStatusClientIDBase].Trim());
}
if( Request.Params[emailContentClientIDBase]!=null)
{
emailContent = Request.Params[emailContentClientIDBase].Trim();
}

SqlCommand sicmd= new SqlCommand("RFS_InsertEmailContent",sqlConn);
sicmd.CommandType= CommandType.StoredProcedure;
sicmd.Parameters.Add( new SqlParameter( "@eID",eID));
sicmd.Parameters["@eID"].Direction = ParameterDirection.Input;
sicmd.Parameters.Add( new SqlParameter("@emailContent",emailContent));
sicmd.Parameters["@emailContent"].Direction = ParameterDirection.Input;
sicmd.Parameters.Add("@displaymessage", SqlDbType.VarChar,3000);
sicmd.Parameters["@displaymessage"].Direction = ParameterDirection.Output;
sicmd.ExecuteNonQuery();
message = (string)sicmd.Parameters["@displaymessage"].Value;
sqlConn.Close();
dg.CurrentPageIndex = dg.PageCount - 1;
SingleInsertColumn(true);
BindGrid();
DisplayMessage(message);
}
catch (System.Data.SqlClient.SqlException ex)
{
Trace.Write(ex.Message);
}
catch (Exception ex) // catch other errors
{
Trace.Write(ex.Message);
}
}
private void DisplayMessage(string message)
{
this.lblMessage.Visible=true;
this.lblMessage.Text =message;
}

protected void dg_ItemCreated(object sender, DataGridItemEventArgs die)
{
if(die.Item.ItemType == ListItemType.Item || die.Item.ItemType == ListItemType.AlternatingItem)
{
WebControl deletebutton = (WebControl) die.Item.Cells[4].Controls[0];
deletebutton.Attributes.Add("onclick","return confirm('Are you sure you want to delete this record?');");
}
}
protected void dg_Item(object sender, DataGridCommandEventArgs di)
{
CheckEditing(di.CommandName);
action = "Save";
if(di.CommandName == "Save")
{
InsertEmailContent();
}
}

private void SingleInsertColumn(bool b)
{
int i;
for(i=2;i<=dg.Columns.Count - 1; i++)
{
dg.Columns[i].Visible = b;
}
Label3.Text="";
}
private void SingleColumn(bool b)
{
int i;
for(i=2;i<=dg.Columns.Count - 1; i++)
{
dg.Columns[i].Visible = b;
}
Label3.Text="";
}

private void CheckEditing(string commandName)
{
if(dg.EditItemIndex != -1)
{
if(commandName!="Cancel" && commandName!="Update")
{
Label3.Text = "Please click update to save your changes, or cancel to discard your changes, before selecting another item.";
bEditing = true;
}
}
}
private void dg_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if(e.Item.DataItem == null)
{
return;
}
DataRowView drV = e.Item.DataItem as DataRowView;
if(action=="Add" && (e.Item.ItemIndex == ds.Tables[0].Rows.Count-1))
{
DropDownList ddlEmailStatus = new DropDownList();
ddlEmailStatus.DataSource=BindEmailStatus();
ddlEmailStatus.DataTextField="EmailStatus";
ddlEmailStatus.DataValueField="eid";
ddlEmailStatus.DataBind();
e.Item.Cells[0].Controls.Add(ddlEmailStatus);
DropDownList ddlEmailType = new DropDownList();
ddlEmailType.DataSource=BindEmailType();
ddlEmailType.DataTextField="EmailType";
ddlEmailType.DataValueField="EmailType";
ddlEmailType.DataBind();
e.Item.Cells[1].Controls.Add(ddlEmailType);
TextBox tbEmailContent=new TextBox();
tbEmailContent.Text="";
tbEmailContent.TextMode = TextBoxMode.MultiLine;
tbEmailContent.Height=150;
tbEmailContent.Width=400;
e.Item.Cells[2].Controls.Add(tbEmailContent);
Button btnAddNew =e.Item.Cells[3].Controls[0] as Button;
btnAddNew.Text = "Save";
btnAddNew.CommandName = "Save";
e.Item.Cells[4].Controls[0].Visible=false;
}
else if(action=="Edit")
{
e.Item.Cells[0].Text=drV["EmailStatus"].ToString() ;
e.Item.Cells[1].Text=drV["EmailType"].ToString() ;
if(e.Item.ItemIndex == clickedIndex)
{
ViewState["index"] = e.Item.ItemIndex;
TextBox tb=new TextBox();
tb.Text = drV["EmailContent"].ToString() ;
e.Item.Cells[2].Controls.Add(tb);
tb.TextMode = TextBoxMode.MultiLine;
tb.Height=150;
tb.Width=400;
e.Item.Cells[4].Controls[0].Visible=false;
}
else
{
e.Item.Cells[2].Text=drV["EmailContent"].ToString() ;
e.Item.Cells[4].Controls[0].Visible=true;
}
}
else
{
e.Item.Cells[0].Text=drV["EmailStatus"].ToString() ;
e.Item.Cells[1].Text=drV["EmailType"].ToString() ;
e.Item.Cells[2].Text=drV["EmailContent"].ToString() ;
}
}
private void dg_ItemCommand(object sender,DataGridCommandEventArgs e)
{
action = e.CommandName;
clickedIndex = e.Item.ItemIndex;
}
}
}

Stored procedure

create procedure RFS_InsertEmailContent @eid int, @emailcontent varchar(3000) ='', @displaymessage varchar(2000) out as
declare @msg varchar(1000)
SET NOCOUNT ON
IF NOT EXISTS(select eid from rfs_emailstatus where eid=@eid)
begin
set @displaymessage= 'This status is not available'

return
end
IF EXISTS(select eid from rfs_emailtemplates where eid=@eid)
begin
set @displaymessage= 'This status is already available'

end
else
begin
begin tran

INSERT INTO rfs_emailtemplates values (@eid,@emailcontent)
set @displaymessage= 'Email content added successfully'

if (@@ERROR <>0) GOTO ERRHANDLER
commit tran
return(1)
ERRHANDLER:
ROLLBACK TRAN
return(0)
end
GO

Table Structure
create table RFS_EmailStatus(eid int primary key not null identity,
EmailStatus varchar(200) unique, EmailType varchar(3))
GO
create table RFS_EmailTemplates(etid int primary key not null identity, eid int ,EmailContent varchar(3000))
GO
ALTER TABLE rfs_emailtemplates ADD UNIQUE(eid)
GO
ALTER TABLE rfs_emailstatus ADD UNIQUE(eid)
GO
alter table RFS_EmailTemplates add constraint fk_RFS_EmailTemplates_RFS_EmailStatus foreign key(eid)
references RFS_EmailStatus(eid) on update cascade
GO
insert into RFS_EmailStatus
select 'RFS_approved email','RFS'
union all
select 'RFS_closedemail email','RFS'
GO
insert into rfs_emailtemplates
select 1,'RFS approved email content'
union all
select 2,'RFS closed email content'
GO

Enjoy :)

Saravanan

Wednesday, August 01, 2007

Source codes

Friday, July 27, 2007

Numeric only textbox

A javascript to search/match any word from the dropdown

A simple datagrid with add, edit, sort, delete features( which uses a stored procedure)

Links I like

Blog Archive