using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
public partial class _Default : System.Web.UI.Page
{
public string sVendors = "";
public string sSkill = "";
string con = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection sqlcon;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
showgrid();
}
}
public void showgrid()
{
DataTable dt = new DataTable();
sqlcon = new SqlConnection(con);
sqlcon.Open();
SqlDataAdapter sda = new SqlDataAdapter();
string strQuery = "select * from empdetails ";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlcon;
sda.SelectCommand = cmd;
sda.Fill(dt);
if (dt.Rows.Count > 0) //Check if DataTable returns data
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
ShowNoResultFound(dt,GridView1);
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
showgrid();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
showgrid();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label lb = (Label)GridView1.Rows[e.RowIndex].FindControl("Label6");
DropDownList ddl = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList1");
RadioButtonList rbl = (RadioButtonList)GridView1.Rows[e.RowIndex].FindControl("RadioButtonList1");
CheckBoxList chb = (CheckBoxList)GridView1.Rows[e.RowIndex].FindControl("CheckBoxList2");
TextBox tx1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox1");
TextBox tx2 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");
TextBox tx3 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");
ListBox llb = (ListBox)GridView1.Rows[e.RowIndex].FindControl("ListBox1");
string ski = "";
foreach (ListItem selectedItem in chb.Items)
{
if (selectedItem.Selected)
{
if (ski.Length == 0)
ski = selectedItem.Value;
else
ski = ski + "," + selectedItem.Value;
}
}
string pro = "";
foreach (ListItem selectedItem in llb.Items)
{
if (selectedItem.Selected)
{
if (pro.Length == 0)
pro = selectedItem.Value;
else
pro = pro + "," + selectedItem.Value;
}
}
sqlcon = new SqlConnection(con);
sqlcon.Open();
string sql = "update empdetails set empname='" + tx1.Text + "',empcode='" + tx2.Text + "',department='" + ddl.SelectedValue.ToString() + "',salary='" +
tx3.Text + "',gender='" +
rbl.SelectedValue.ToString() + "',skill='" + ski + "',project='" + pro + "' where empno='" + lb.Text + "'";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlcon;
cmd.ExecuteNonQuery();
GridView1.EditIndex = -1;
showgrid();
}
public DataTable load_department()
{
DataTable dt = new DataTable();
sqlcon = new SqlConnection(con);
sqlcon.Open();
string sql = "select * from department";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlcon;
SqlDataAdapter sd = new SqlDataAdapter(cmd);
sd.Fill(dt);
return dt;
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataRowView drv = e.Row.DataItem as DataRowView;
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList dp = (DropDownList)e.Row.FindControl("DropDownList1");
DataTable dt = load_department();
for (int i = 0; i < dt.Rows.Count; i++)
{
ListItem lt = new ListItem();
lt.Text = dt.Rows[i][0].ToString();
dp.Items.Add(lt);
}
dp.SelectedValue = drv[3].ToString();
RadioButtonList rbtnl = (RadioButtonList)e.Row.FindControl("RadioButtonList1");
rbtnl.SelectedValue = drv[5].ToString();
CheckBoxList chkb = (CheckBoxList)e.Row.FindControl("CheckBoxList2");
string[] strSplitArr = ((DataRowView)e.Row.DataItem)["skill"].ToString().Split(',');
foreach (ListItem oItem in chkb.Items)
{
for (int i = 0; i < strSplitArr.Length; i++)
{
if (oItem.Value == strSplitArr[i].Trim())
{
oItem.Selected = true;
break;
}
}
}
ListBox llb = (ListBox)e.Row.FindControl("ListBox1");
string[] strSplit = ((DataRowView)e.Row.DataItem)["project"].ToString().Split(',');
foreach (ListItem oItem in llb.Items)
{
for (int i = 0; i < strSplit.Length; i++)
{
if (oItem.Value == strSplit[i].Trim())
{
oItem.Selected = true;
break;
}
}
}
}
string empno = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "empno"));
LinkButton lnkbtnresult = (LinkButton)e.Row.FindControl("btndelete");
lnkbtnresult.Attributes.Add("onclick", "javascript:return ConfirmationBox('" + empno + "')");
}
}
protected void save(object sender, EventArgs e)
{
Control control = null;
if (GridView1.FooterRow != null)
{
control = GridView1.FooterRow;
}
else
{
control = GridView1.Controls[0].Controls[0];
}
string empname = (control.FindControl("txtempName") as TextBox).Text;
string empcode = (control.FindControl("txtempcode") as TextBox).Text;
string department = (control.FindControl("DropDownList2") as DropDownList).Text;
string salary = (control.FindControl("txtsalary") as TextBox).Text;
string gender = (control.FindControl("RadioButtonList2") as RadioButtonList).Text;
//string skill = (control.FindControl("CheckBoxList1") as CheckBoxList).Text;
//string project = (control.FindControl("ListBox2") as ListBox);
string strConnString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
StringCollection sc = new StringCollection();
ListBox project = GridView1.FooterRow.FindControl("ListBox2") as ListBox;
CheckBoxList skill = GridView1.FooterRow.FindControl("CheckBoxList1") as CheckBoxList;
foreach (ListItem selectedItem in project.Items)
{
if (selectedItem.Selected)
{
if (sVendors.Length == 0)
sVendors = selectedItem.Value;
else
sVendors = sVendors + "," + selectedItem.Value;
}
}
foreach (ListItem oItem in skill.Items)
{
if (oItem.Selected)
{
if (sSkill.Length == 0)
sSkill = oItem.Value;
else
sSkill = sSkill + "," + oItem.Value;
}
}
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [empdetails] VALUES(@empname, @empcode, @department,@salary, @gender, @sSkill,@sVendors)";
cmd.Parameters.AddWithValue("@empname", empname);
cmd.Parameters.AddWithValue("@empcode", empcode);
cmd.Parameters.AddWithValue("@department", department);
cmd.Parameters.AddWithValue("@salary", salary);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@sSkill", sSkill);
cmd.Parameters.AddWithValue("@sVendors", sVendors);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label lb = (Label)GridView1.Rows[e.RowIndex].FindControl("Label6");
sqlcon = new SqlConnection(con);
sqlcon.Open();
string sql = "delete from empdetails where empno='" + lb.Text + "'";
SqlCommand cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlcon;
cmd.ExecuteNonQuery();
GridView1.EditIndex = -1;
showgrid();
}
public DataSet PopulateControls()
{
SqlConnection myConnection = new SqlConnection(con);
SqlDataAdapter ad = new SqlDataAdapter("SELECT [department] FROM department",
myConnection);
DataSet ds = new DataSet();
ad.Fill(ds, "department");
return ds;
}
public DataSet PopulateControlslist()
{
SqlConnection myConnection = new SqlConnection(con);
SqlDataAdapter ad = new SqlDataAdapter("SELECT [projectname] FROM projects",
myConnection);
DataSet ds = new DataSet();
ad.Fill(ds, "projects");
return ds;
}
private void ShowNoResultFound(DataTable source, GridView gv)
{
source.Rows.Add(source.NewRow()); // create a new blank row to the DataTable
// Bind the DataTable which contain a blank row to the GridView
gv.DataSource = source;
gv.DataBind();
// Get the total number of columns in the GridView to know what the Column Span should be
int columnsCount = gv.Columns.Count;
gv.Rows[0].Cells.Clear();// clear all the cells in the row
gv.Rows[0].Cells.Add(new TableCell()); //add a new blank cell
gv.Rows[0].Cells[0].ColumnSpan = columnsCount; //set the column span to the new added cell
//set No Results found to the new added cell
gv.Rows[0].Cells[0].Text = "NO RESULT FOUND!";
}
}
