ASP.NET

Monday, 7 January 2013

Using AJAX,JSON,JQUERY,JAVASCRIPT and Web Services.

Sample Application : Using AJAX,JSON,JQUERY,JAVASCRIPT and Web Services.

DEFINITION:

Operations...

* indicates are required field.
- Insert
- Update
- Active / In Active
- Delete On confirmation
- List all With (Search,Paging,sorting)

-----------------------------
=> Table Employee Master
-----------------------------
- EmplId
*- FirstName
*- LastName
*- UserName
*- Password
- ConfirmPassword      (this is not database field.. use it to compare with Password)
*- Email
*- Phone
*- birthDate
*- Gender
*- Resume            (only .doc and .pdf file format allow)
- IsActive
- Remarks

Output:

Employee Management System

CODING PART.

Default.aspx

<HTML>

<HEAD>

</HEAD>

<BODY>

   <input type="hidden" id="txtId" />
   <table border="0" cellpadding="5" cellspacing="2" id="tblMain">
    <tr>
        <td>First Name</td>
        <td><input type="text" id="txtFname" size="15"/></td>
    </tr>
    <tr>
        <td>Last Name</td>
        <td><input type="text" id="txtLname" size="15"/></td>
    </tr>
    <tr>
        <td>User Name</td>
        <td><input type="text" id="txtUserName" size="20"/></td>
    </tr>
    <tr>
        <td>Password</td>
        <td><input type="password" id="txtPwd" size="20"/></td>
    </tr>
    <tr>
        <td>Confirm Password</td>
        <td><input type="password" id="txtConfirmPwd" onblur="matchPassword()" size="20"/></td>
    </tr>
    <tr>
        <td>Email</td>
        <td><input type="text" id="txtEmail" size="40" onblur="checkEmail();" /></td>
    </tr>
    <tr>
        <td>Phone</td>
        <td><input type="text" id="txtPhone" size="10" onblur="checkPhoneNo();" /></td>
    </tr>
    <tr>
        <td>BirthDate</td>
        <td>
            <input type="text" id="datepicker" size="10" />
        </td>
    </tr>
    <tr>
        <td valign="top">Gender</td>
        <td>
            <input type="radio" name="radioBtnGender" value="Male" />Male<br />
            <input type="radio" name="radioBtnGender" value="Female" />Female
        </td>
    </tr>
    <tr>
        <td>Resume</td>
        <td>
            <asp:FileUpload ID="resumeUploader" runat="server" />
        </td>
    </tr>
    <tr>
        <td>Remarks</td>
        <td><input type="text" id="txtRemarks" maxlength="50" size="30"/></td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <input type="button" value="Update" id="btnUpdate" onclick="updateData()" />
            <input type="button" value="Cancle" id="btnCancle" onclick="cancleUpdate()" />
            <input type="button" value="Insert" id="btnInsert" onclick="insertData()"/>
        </td>
    </tr>
   </table>
   <br />
    <table border="0" cellpadding="0" cellspacing="0" width="100%">
        <tr>
            <td align="right">
                Search  
                    <input type="text" id="txtSearch" onkeyup="searchedRecords();"/>
            </td>
        </tr>
    </table>

   <table border="1" cellpadding="1" cellspacing="0" id="tblDynamic" width="100%">
        <thead>
            <tr>
                <th>
                    Activate/Deactivate
                </th>
                <th>
                    Name
                </th>
                <th>
                    Email
                </th>
                <th>
                    Phone
                </th>
                <th>
                    Birthdate
                </th>
                <th>
                    Gender
                </th>
                <th>
                    Remarks
                </th>
                <th colspan="2">
                    Actions
                </th>
            </tr>
        </thead>
        <tbody id="TableData">


        </tbody>
        <tr>
            <td colspan="9" align="center">
                <input type="button" id="btnFirst" value="FIRST" onclick="displayData(0, 9);"/>    
                <input type="hidden" id="hdnStrtIndex" />  
                <input type="button" id="btnPrev" value="PREVIOUS" onclick="prev10();" />  
                <input type="button" id="btnNext" value="NEXT" onclick="next10();" />  
                <input type="hidden" id="hdnEndIndex" />    
                <input type="button" id="btnLast" value="LAST" onclick="goToLastRecord();"/>
                <div align="right">Page <span id="curPage"></span> Of <span id="totalPage"></span></div>
            </td>
        </tr>
   </table>

</BODY>

</HTML>

DatabaseService.asmx

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class DatabaseService : System.Web.Services.WebService {

    SqlConnection objCon = new SqlConnection(@"Data Source=DS-1\SQL2008;Initial Catalog=EmployeeDb;User ID=training;Password=training");
    SqlCommand objCmd = null;
    /// <summary>
    /// Web method to insert record in database.
    /// </summary>
    [WebMethod]
    public void InsertRecord(string Fname, string Lname, string Uname, string Pwd, string Email, string Phone, string BDate, string Gender, string Resume, string Remarks)
    {
        int isActive = 1;
        string insertQuery = "insert into empdetails_master(emp_fn,emp_ln,emp_uname,emp_pwd,emp_email,emp_phone,emp_birthdate,emp_gender,emp_resume,emp_isactive,emp_remarks) values('" + Fname + "','" + Lname + "','" + Uname + "','" + Pwd + "','" + Email + "','" + Phone + "','" + BDate + "','" + Gender + "','" + Resume + "','" + isActive + "','" + Remarks + "')";

        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = insertQuery;

        objCon.Open();
        objCmd.ExecuteNonQuery();
        objCon.Close();
        objCmd.Dispose();
    }

    [WebMethod]
    public List<Properties> DisplayRecords()
    {
        List<Properties> objListProperties = new List<Properties>();
        string selectQuery = "select (emp_fn+' '+emp_ln) as name,emp_email,emp_phone,emp_id,CONVERT(varchar(10),emp_birthdate,105) as bdate,emp_gender,emp_remarks,case emp_isactive when 1 then 'Images/yes.png' else 'Images/no.png' end as isActive from empdetails_master order by emp_fn";
        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = selectQuery;

        objCon.Open();
        SqlDataReader objDr = objCmd.ExecuteReader();

        while (objDr.Read())
        {
            Properties objProperty = new Properties();
            objProperty._Id = objDr["emp_id"].ToString();
            objProperty._Name = objDr["name"].ToString();
            objProperty._Email = objDr["emp_email"].ToString();
            objProperty._Phone = objDr["emp_phone"].ToString();
            objProperty._Birthdate = objDr["bdate"].ToString();
            objProperty._Gender = objDr["emp_gender"].ToString();
            objProperty._Remarks = objDr["emp_remarks"].ToString();
            objProperty._IsActive = objDr["isActive"].ToString();
            objListProperties.Add(objProperty);
        }
        objCon.Close();
        objDr.Dispose();
        objCmd.Dispose();

        return objListProperties;
    }

    [WebMethod]
    public List<Properties> searchRecords(string Text)
    {
        string args = Text + "%";
        string searchQuery = "select (emp_fn+' '+emp_ln) as name,emp_email,emp_phone,emp_id,CONVERT(varchar(10),emp_birthdate,105) as bdate,emp_gender,emp_remarks,case emp_isactive when 1 then 'Images/yes.png' else 'Images/no.png' end as isActive from empdetails_master where emp_fn LIKE '" + args + "' order by emp_fn";
        List<Properties> objListProperties = new List<Properties>();
        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = searchQuery;

        objCon.Open();
        SqlDataReader objDr = objCmd.ExecuteReader();

        while (objDr.Read())
        {
            Properties objProperty = new Properties();
            objProperty._Id = objDr["emp_id"].ToString();
            objProperty._Name = objDr["name"].ToString();
            objProperty._Email = objDr["emp_email"].ToString();
            objProperty._Phone = objDr["emp_phone"].ToString();
            objProperty._Birthdate = objDr["bdate"].ToString();
            objProperty._Gender = objDr["emp_gender"].ToString();
            objProperty._Remarks = objDr["emp_remarks"].ToString();
            objProperty._IsActive = objDr["isActive"].ToString();
            objListProperties.Add(objProperty);
        }
        objCon.Close();
        objDr.Dispose();
        objCmd.Dispose();

        return objListProperties;
    }

    [WebMethod]
    public int deleteRecord(string Id)
    {
        string deleteQuery = "delete from empdetails_master where emp_id="+Id;
        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = deleteQuery;
        objCon.Open();
        objCmd.ExecuteNonQuery();
        objCon.Close();
        objCmd.Dispose();
        return Convert.ToInt32(Id);
    }

    [WebMethod]
    public void updateRecord(string Id, string Fname, string Lname, string Email, string Phone, string Resume, string Remark)
    {
        string updateQuery = "update empdetails_master set emp_fn='" + Fname + "',emp_ln='" + Lname + "',emp_email='" + Email + "',emp_phone='" + Phone + "',emp_resume='" + Resume + "',emp_remarks='" + Remark + "' where emp_id=" + Id;
        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = updateQuery;
        objCon.Open();
        objCmd.ExecuteNonQuery();
        objCon.Close();
        objCmd.Dispose();
    }

    [WebMethod]
    public void changeStatus(string Id)
    {
        string getStatus = "select emp_isactive from empdetails_master where emp_id=" + Id;
        string updatequery = "";

        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = getStatus;
        objCon.Open();
        SqlDataReader objDr = objCmd.ExecuteReader();
        while (objDr.Read())
        {
            int currentStatus = Convert.ToInt32(objDr["emp_isactive"]);
            if (currentStatus == 0)
            {
                updatequery = "update empdetails_master set emp_isactive=1 where emp_id=" + Id;
            }
            else
            {
                updatequery = "update empdetails_master set emp_isactive=0 where emp_id=" + Id;
            }
        }
        objCon.Close();
        objCmd.Dispose();
        objDr.Dispose();

        objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = updatequery;
        objCon.Open();
        objCmd.ExecuteNonQuery();
        objCon.Close();
        objCmd.Dispose();
    }
}

Properties.cs

[Serializable]
public class Properties
{
    public string _Id { get; set; }
    public string _Name { get; set; }
    public string _Email { get; set; }
    public string _Phone { get; set; }
    public string _Birthdate { get; set; }
    public string _Gender { get; set; }
    public string _Remarks { get; set; }
    public string _IsActive { get; set; }
}

Thursday, 27 December 2012

Insert/Delete/Update/View in Table With Asp.Net C#

Default.aspx

<html>

<head>

<title>This is First Page.. </title>

function form_validate() {

if (document.getElementById("<%=txtFname.ClientID%>").value == "") {

alert("Enter First Name..");

document.getElementById("<%=txtFname.ClientID%>").focus();

return false;

}

if (document.getElementById("<%=txtMname.ClientID%>").value == "") {

alert("Please Enter Middle Name.. ");

document.getElementById("<%=txtMname.ClientID%>").focus();

return false;

}

if (document.getElementById("<%=txtLname.ClientID%>").value == "") {

alert("Please Enter Last Name.. ");

document.getElementById("<%=txtLname.ClientID%>").focus();

return false;

}

if (document.getElementById("<%=txtMob.ClientID%>").value == "") {

alert("Phone Number Can Not Be Blanked.... ");

document.getElementById("<%=txtMob.ClientID%>").focus();

return false;

}

var numpattern = "^[0-9]{10}$";

var phone = document.getElementById("<%=txtMob.ClientID%>").value;

var matchptrn = phone.match(numpattern);

if (matchptrn == null) {

alert("Phone Number Is Incorrect..");

document.getElementById("<%=txtMob.ClientID%>").focus();

return false;

}

if (document.getElementById("<%=txtEmail.ClientID%>").value == "") {

alert("Must Enter Email Address.. ");

document.getElementById("<%=txtEmail.ClientID%>").focus();

return false;

}

var emailptrn = "^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$";

var email = document.getElementById("<%=txtEmail.ClientID%>").value;

var matchemail = email.match(emailptrn);

if (matchemail == null) {

alert("Incorrect Email Address...");

return false;

}

if (document.getElementById("<%=txtAdd1.ClientID%>").value == "") {

alert("Address1 Must be enter.. ");

document.getElementById("<%=txtAdd1.ClientID%>").focus();

return false;

}

if (document.getElementById("<%=txtPincode.ClientID%>").value == "") {

alert("pincode must be enter. ");

document.getElementById("<%=txtPincode.ClientID%>").focus();

return false;

}

var pincodeptrn = "^[0-9]{6}$";

var pincode = document.getElementById("<%=txtPincode.ClientID%>").value;

var matchpincode = pincode.match(pincodeptrn);

if (matchpincode == null) {

alert("Pincode Must Be Of Six Digits..");

document.getElementById("<%=txtPincode.ClientID%>").focus();

return false;

}

function del_confirm() {

var ans = confirm("Are you sure you want to delete this recoed..?");

if (ans == true) {

return true;

}

else {

return false;

}

</script>

</head>

<body>

<tr>

<td>

<table>

<tr>

<td>

<asp:HiddenField ID="hfEmpId" runat="server" />

</td>

</tr>

<tr>

<h2>

Employee Registration Form</h2>

</td>

</tr>

<tr>

<td>

First Name:

</td>

<td>

<asp:TextBox ID="txtFname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Middle Name:

</td>

<td>

<asp:TextBox ID="txtMname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Last Name:

</td>

<td>

<asp:TextBox ID="txtLname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Mobile/Phone:

</td>

<td>

<asp:TextBox ID="txtMob" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Email:

</td>

<td>

<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Address1:

</td>

<td>

<asp:TextBox ID="txtAdd1" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Address2:

</td>

<td>

<asp:TextBox ID="txtAdd2" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

City:

</td>

<td>

<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

State:

</td>

<td>

<asp:TextBox ID="txtState" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Pincode:

</td>

<td>

<asp:TextBox ID="txtPincode" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Resume:

</td>

<td>

<asp:FileUpload ID="FileUploadResume" runat="server" />

<asp:HiddenField ID="hfFileUpload" runat="server" />

</td>

</tr>

<tr>

<asp:Button ID="btnInsert" class="btn_class" runat="server" Text="Submit" OnClick="btnInsert_Click"

OnClientClick="return form_validate('<%=txtFname.ClientID%>');" />

<asp:Button ID="btnUpdate" class="btn_class" runat="server" Text="Update" OnClick="btnUpdate_Click" />

<asp:Button ID="btnClear" class="btn_class" runat="server" Text="Cancel" OnClick="btnClear_Click" />

</td>

</tr>

<tr>

<asp:GridView class="gridview_class" ID="gvDataDisplay" runat="server" AutoGenerateColumnhs="False"

OnRowCommand="gvDataDisplay_RowCommand" AutoGenerateColumns="False" CellPadding="4"

ForeColor="#333333" GridLines="None">

<asp:BoundField DataField="FullName" HeaderText="Full Name" />

<asp:BoundField DataField="Email" HeaderText="Email" />

<asp:BoundField DataField="City" HeaderText="City" />

<asp:BoundField DataField="state" HeaderText="State" />

<asp:TemplateField ShowHeader="False" HeaderText="Delete">

<asp:LinkButton ID="lnkDel" runat="server" CausesValidation="False" CommandName="rowDelete"

CommandArgument='<%# Eval("emp_id") %>' Text="Delete" OnClientClick="return del_confirm();"

OnClick="lnkDel_Click"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit" ShowHeader="False">

<asp:LinkButton ID="lnkEdit" runat="server" CommandName="rowUpdate"CommandArgument='<%#Eval("emp_id")%>'>Edit</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

</table>

</td>

</tr>

</table>

</form>

</body>

</html>

Default.cs

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.SqlClient;

public partial class _Default : System.Web.UI.Page

{

SqlConnection objCon = new SqlConnection(@"Data Source=DS-1\SQL2008;Initial Catalog=EmployeeDb;User ID=training;Password=training");

SqlCommand cmd = null;

int eid = 0;

protected void Page_Load(object sender, EventArgs e)

{

btnUpdate.Visible = false;

btnInsert.Visible = true;

btnClear.Visible = false;

cmd = new SqlCommand("StoredProcedureSelectData", objCon);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

objCon.Open();

sda.Fill(ds, "employee_master");

objCon.Close();

gvDataDisplay.DataSource = ds;

gvDataDisplay.DataBind();

}

protected void btnInsert_Click(object sender, EventArgs e)

{

if (FileUploadResume.HasFile)

{

string fileName = FileUploadResume.FileName.ToString();

string filePath = Server.MapPath("~/Resume/" + fileName);

FileUploadResume.SaveAs(filePath);

hfFileUpload.Value = fileName;

}

cmd = new SqlCommand("StoredProcedureInsertData", objCon);

cmd.CommandType = CommandType.StoredProcedure;

//cmd.Parameters.AddWithValue("@emp_fname", txtFname.Text);

cmd.Parameters.Add("@emp_fn", SqlDbType.NVarChar).Value = txtFname.Text; // instead of this you can write above line

cmd.Parameters.Add("@emp_mn", SqlDbType.NVarChar).Value = txtMname.Text;

cmd.Parameters.Add("@emp_ln", SqlDbType.NVarChar).Value = txtLname.Text;

cmd.Parameters.Add("@emp_mp", SqlDbType.BigInt).Value = txtMob.Text;

cmd.Parameters.Add("@emp_email", SqlDbType.NVarChar).Value = txtEmail.Text;

cmd.Parameters.Add("@emp_address1", SqlDbType.NVarChar).Value = txtAdd1.Text;

cmd.Parameters.Add("@emp_address2", SqlDbType.NVarChar).Value = txtAdd2.Text;

cmd.Parameters.Add("@emp_city", SqlDbType.NVarChar).Value = txtCity.Text;

cmd.Parameters.Add("@emp_state", SqlDbType.NVarChar).Value = txtState.Text;

cmd.Parameters.Add("@emp_pc", SqlDbType.BigInt).Value = txtPincode.Text;

cmd.Parameters.Add("@emp_resume_path", SqlDbType.NVarChar).Value = hfFileUpload.Value;

objCon.Open();

cmd.ExecuteNonQuery();

objCon.Close();

txtFname.Text = "";

txtMname.Text = "";

txtLname.Text = "";

txtMob.Text = "";

txtEmail.Text = "";

txtAdd1.Text = "";

txtAdd2.Text = "";

txtCity.Text = "";

txtState.Text = "";

txtPincode.Text = "";

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Data Inserted Successfully...!!');", true);

Response.Redirect("#");

}

protected void btnView_Click(object sender, EventArgs e)

{

}

protected void gvDataDisplay_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == "rowDelete")

{

int empId = Convert.ToInt32(e.CommandArgument);

cmd = new SqlCommand("StoredProcedureDeleteData", objCon);

cmd.CommandType = CommandType.StoredProcedure;

objCon.Open();

cmd.Parameters.Add("@emp_id", SqlDbType.Int).Value = empId;

cmd.ExecuteNonQuery();

objCon.Close();

Response.Redirect("#");

//gvDataDisplay.DataBind();

}

if (e.CommandName == "rowUpdate")

{

btnInsert.Visible = false;

btnUpdate.Visible = true;

btnClear.Visible = true;

objCon.Open();

SqlCommand cmdEdit = new SqlCommand();

int empId = Convert.ToInt32(e.CommandArgument);

cmdEdit = new SqlCommand("StoredProcedureSelectDataForEdit", objCon);

cmdEdit.CommandType = CommandType.StoredProcedure;

cmdEdit.Parameters.Add("@emp_id", SqlDbType.Int).Value = empId;

SqlDataReader dr;

dr = cmdEdit.ExecuteReader();

while (dr.Read())

{

hfEmpId.Value = dr["emp_id"].ToString();

txtFname.Text = dr["emp_fname"].ToString();

txtMname.Text = dr[2].ToString();

txtLname.Text = dr[3].ToString();

txtMob.Text = dr[4].ToString();

txtEmail.Text = dr[5].ToString();

txtAdd1.Text = dr[6].ToString();

txtAdd2.Text = dr[7].ToString();

txtCity.Text = dr[8].ToString();

txtState.Text = dr[9].ToString();

txtPincode.Text = dr[10].ToString();

}

objCon.Close();

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

cmd = new SqlCommand("StoredProcedureUpdate", objCon);

cmd.CommandType = CommandType.StoredProcedure;

objCon.Open();

cmd.Parameters.Add("@emp_id", SqlDbType.Int).Value = hfEmpId.Value;

cmd.Parameters.Add("@emp_fn", SqlDbType.NVarChar).Value = txtFname.Text;

cmd.Parameters.Add("@emp_mn", SqlDbType.NVarChar).Value = txtMname.Text;

cmd.ExecuteNonQuery();

objCon.Close();

Response.Redirect("#");

}

protected void lnkDel_Click(object sender, EventArgs e)

{

}

protected void btnClear_Click(object sender, EventArgs e)

{

btnClear.Visible = false;

btnInsert.Visible = true;

btnUpdate.Visible = false;

txtFname.Text = "";

txtMname.Text = "";

txtLname.Text = "";

txtMob.Text = "";

txtEmail.Text = "";

txtAdd1.Text = "";

txtAdd2.Text = "";

txtCity.Text = "";

txtState.Text = "";

txtPincode.Text = "";

}