public List <tbl_WorkShop> GetWorkShops()
{
List <tbl_WorkShop> Ls;
Ls = new List <tbl_WorkShop>();
string cmdStrDr = "select * from tbl_WorkShop";
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmdDr = new SqlCommand(cmdStrDr, con);
con.Open();
SqlDataReader dr = cmdDr.ExecuteReader();
while (dr.Read())
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopId = int.Parse(dr["WorkShopid"].ToString());
Wp.WorkShopTitle = dr["WorkShopTitle"].ToString();
Wp.WorkShopDate = DateTime.Parse(dr["WorkShopDate"].ToString());
Wp.WorkShopDuration = dr["WorkShopDuration"].ToString();
Wp.WorkShopTopics = dr["WorkShopTopics"].ToString();
Ls.Add(Wp);
}
dr.Close();
con.Close();
return(Ls);
}
public tbl_WorkShop GetWorkShopById(int WorkShopId)
{
WorkShopDb WD = new WorkShopDb();
tbl_WorkShop Wp = WD.GetWorkShopById(WorkShopId);
return(Wp);
}
public bool UpdateWorkShopById(tbl_WorkShop Wp, int WorkShopId)
{
try
{
string cmdStr = @"Update tbl_WorkShop
Set WorkShopTitle=@WorkShopTitle, WorkShopDuration=@WorkShopDuration,WorkShopDate=@WorkShopDate,
WorkShopTopics=@WorkShopTopics where WorkShopId=@WorkShopid";
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(cmdStr, con);
cmd.Parameters.AddWithValue("@WorkShopTitle", Wp.WorkShopTitle);
cmd.Parameters.AddWithValue("@WorkShopDate", Wp.WorkShopDate);
cmd.Parameters.AddWithValue("@WorkShopDuration", Wp.WorkShopDuration);
cmd.Parameters.AddWithValue("@WorkShopTopics", Wp.WorkShopTopics);
cmd.Parameters.AddWithValue("@WorkShopid", WorkShopId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public List <tbl_WorkShop> GetWorkShopsForStudent()
{
List <tbl_WorkShop> Ls;
Ls = new List <tbl_WorkShop>();
string CmdStr = "SELECT WorkShopId,WorkShopTitle,WorkShopDuration, CAST(WorkShopDate As date) as WorkShopDate,WorkShopTopics FROM tbl_WorkShop";
SqlConnection Con = new SqlConnection(ConStr);
SqlCommand Cmd = new SqlCommand(CmdStr, Con);
Con.Open();
SqlDataReader dr = Cmd.ExecuteReader();
while (dr.Read())
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopId = int.Parse(dr["WorkShopId"].ToString());
Wp.WorkShopTitle = dr["WorkShopTitle"].ToString();
Wp.WorkShopDate = DateTime.Parse(dr["WorkShopDate"].ToString());
Wp.WorkShopDuration = dr["WorkShopDuration"].ToString();
Wp.WorkShopTopics = dr["WorkShopTopics"].ToString();
Ls.Add(Wp);
}
dr.Close();
Con.Close();
return(Ls);
}
public tbl_WorkShop GetWorkShopById(int WorkShopId)
{
tbl_WorkShop Wp = null;
string CmdStr = "select * from tbl_WorkShop where WorkShopId=@WorkShopId";
SqlConnection Con = new SqlConnection(ConStr);
SqlCommand Cmd = new SqlCommand(CmdStr, Con);
Cmd.Parameters.AddWithValue("@WorkShopId", WorkShopId);
Con.Open();
SqlDataReader dr = Cmd.ExecuteReader();
if (dr.Read())
{
Wp = new tbl_WorkShop();
Wp.WorkShopId = int.Parse(dr["WorkShopId"].ToString());
Wp.WorkShopTitle = dr["WorkShopTitle"].ToString();
Wp.WorkShopDate = DateTime.Parse(dr["WorkShopDate"].ToString());
Wp.WorkShopDuration = dr["WorkShopDuration"].ToString();
Wp.WorkShopTopics = dr["WorkShopTopics"].ToString();
}
dr.Close();
Con.Close();
return(Wp);
}
protected void btnSave_Click(object sender, EventArgs e)
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopTitle = txtWorkShopTitle.Text;
Wp.WorkShopDate = DateTime.Parse(txtWorkShopDate.Text);
Wp.WorkShopTopics = txtWorkShopTopics.Text;
Wp.WorkShopDuration = txtWorkShopDuration.Text;
List <int> Ls = new List <int>();
foreach (ListItem item in ckbLTrainers.Items)
{
int TrainerId;
if (item.Selected)
{
TrainerId = int.Parse(item.Value);
Ls.Add(TrainerId);
}
}
WorkShopBusiness WB = new WorkShopBusiness();
if (WB.InsertWorkShop(Wp, Ls))
{
lblMessage.Text = "Saved Successfully";
ClearData();
}
GetWorkShops();
}
public bool InsertWorkShop(tbl_WorkShop Wp)
{
try
{
string cmdStr = "insert into tbl_WorkShop values(@WorkShopTitle,@WorkShopDate,@WorkShopDuration,@WorkShopTopics)";
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(cmdStr, con))
{
cmd.Parameters.AddWithValue("@WorkShopTitle", Wp.WorkShopTitle);
cmd.Parameters.AddWithValue("@WorkShopDate", Wp.WorkShopDate);
cmd.Parameters.AddWithValue("@WorkShopDuration", Wp.WorkShopDuration);
cmd.Parameters.AddWithValue("@WorkShopTopics", Wp.WorkShopTopics);
con.Open();
cmd.ExecuteNonQuery();
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
protected void gridviewWorkShop_SelectedIndexChanged(object sender, EventArgs e)
{
int id = int.Parse(gridviewWorkShop.SelectedValue.ToString());
WorkShopBusiness WB = new WorkShopBusiness();
tbl_WorkShop Wp = WB.GetWorkShopById(id);
txtWorkShopTitle.Text = Wp.WorkShopTitle;
txtWorkShopDuration.Text = Wp.WorkShopDuration;
txtWorkShopDate.Text = Wp.WorkShopDate.ToString();
txtWorkShopTopics.Text = Wp.WorkShopTopics;
}
public bool UpdateWorkShopById(tbl_WorkShop Wp, int WorkShopId)
{
//if (Wp.WorkShopDate > DateTime.Now)
//{
WD.UpdateWorkShopById(Wp, WorkShopId);
return(true);
//}
//else
//{
// return false;
//}
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
int id = int.Parse(GridView1.SelectedValue.ToString());
WorkShopBusiness WB = new WorkShopBusiness();
tbl_WorkShop Wp = WB.GetWorkShopById(id);
txtWorkShopTitle.Text = Wp.WorkShopTitle;
txtWorkShopDate.Text = Wp.WorkShopDate.Date.ToString();
txtWorkShopDuration.Text = Wp.WorkShopDuration;
txtWorkShopTopics.Text = Wp.WorkShopTopics;
ckbLTrainers.ClearSelection();
}
public bool InsertWorkShop(tbl_WorkShop Wp, List <int> Ls)
{
//WorkShopDate should be greater than Current Date
if (Wp.WorkShopDate > DateTime.Now)
{
WD.InsertWorkShop(Wp, Ls);
return(true);
}
else
{
return(false);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopTitle = txtWorkShopTitle.Text;
Wp.WorkShopDate = DateTime.Parse(txtWorkShopDate.Text);
Wp.WorkShopDuration = txtWorkShopDuration.Text;
Wp.WorkShopTopics = txtWorkShopTopics.Text;
WorkShopBusiness Wb = new WorkShopBusiness();
Wb.InsertWorkShop(Wp);
GetWorkShops();
}
public bool InsertWorkShop(tbl_WorkShop WP, List <int> Ls)
{
SqlConnection Con = new SqlConnection(ConStr);
string CmdStr = "insert into tbl_WorkShop values(@WorkShopTitle,@WorkShopDate,@WorkShopDuration,@WorkShopTopics,null,null,null,null);select Scope_Identity() as Id";
string CmdStr2 = "insert into tbl_Trainer_WorkShop_Mapping values(@TrainerId,@WorkShopId,null,null,null,null)";
Con.Open();
SqlTransaction SqlTrans = Con.BeginTransaction();
try
{
//Inserting WorkShop
SqlCommand Cmd = new SqlCommand(CmdStr, Con, SqlTrans);
Cmd.Parameters.AddWithValue("@WorkShopTitle", WP.WorkShopTitle);
Cmd.Parameters.AddWithValue("@WorkShopDate", WP.WorkShopDate);
Cmd.Parameters.AddWithValue("@WorkShopDuration", WP.WorkShopDuration);
Cmd.Parameters.AddWithValue("@WorkShopTopics", WP.WorkShopTopics);
SqlDataReader dr = Cmd.ExecuteReader();
//Reading WorkShopId Returned from Scope_Identity
int WorkShopId = 0;
if (dr.Read())
{
WorkShopId = int.Parse(dr[0].ToString());
}
dr.Close();
//inserting multiple records to tbl_Trainer_WorkShop_Mapping
if (WorkShopId != 0)
{
foreach (var TrainerId in Ls)
{
SqlCommand Cmd2 = new SqlCommand(CmdStr2, Con, SqlTrans);
Cmd2.Parameters.AddWithValue("@TrainerId", TrainerId);
Cmd2.Parameters.AddWithValue("@WorkShopId", WorkShopId);
Cmd2.ExecuteNonQuery();
}
}
SqlTrans.Commit();
Con.Close();
return(true);
}
catch
{
SqlTrans.Rollback();
return(false);
}
}
public bool UpdateWorkShopById(tbl_WorkShop Wp, int WorkShopId)
{
WorkShopDb WD = new WorkShopDb();
if (Wp.WorkShopDate > DateTime.Now)
{
WD.UpdateWorkShopById(Wp, WorkShopId);
return(true);
}
else
{
return(false);
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopTitle = txtWorkShopTitle.Text;
Wp.WorkShopDate = DateTime.Parse(txtWorkShopDate.Text);
Wp.WorkShopDuration = txtWorkShopDuration.Text;
Wp.WorkShopTopics = txtWorkShopTopics.Text;
WorkShopBusiness Wb = new WorkShopBusiness();
int id = int.Parse(gridviewWorkShop.SelectedValue.ToString());
Wb.UpdateWorkShopById(Wp, id);
GetWorkShops();
}
public bool InsertWorkShop(tbl_WorkShop Wp)
{
//WorkShopDate should be greater than current Date
if (Wp.WorkShopDate > DateTime.Now)
{
WorkShopDb WD = new WorkShopDb();
WD.InsertWorkShop(Wp);
return(true);
}
else
{
return(false);
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
tbl_WorkShop Wp = new tbl_WorkShop();
Wp.WorkShopTitle = txtWorkShopTitle.Text;
Wp.WorkShopDate = DateTime.Parse(txtWorkShopDate.Text);
Wp.WorkShopTopics = txtWorkShopTopics.Text;
Wp.WorkShopDuration = txtWorkShopDuration.Text;
WorkShopBusiness WB = new WorkShopBusiness();
int id = int.Parse(GridView1.SelectedValue.ToString());
if (WB.UpdateWorkShopById(Wp, id))
{
lblMessage.Text = "Updated Successfully";
ClearData();
}
GetWorkShops();
}
public tbl_WorkShop GetWorkShopById(int WorkShopId)
{
tbl_WorkShop Wp = WD.GetWorkShopById(WorkShopId);
return(Wp);
}
