public bool UpdateWorkshopById(tbl_Workshop Wp, int WorkShopId)
{
try
{
string conStr = "Data Source=rakib;Initial Catalog=WorkShopDB;Integrated Security=True";
string cmdStr = @"Update tbl_WorkShop
Set WorkShopTitle=@WorkShopTitle,WorkShopDate=@WorkShopDate,
WorkShopDuration=@WorkShopDuration,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 tbl_Workshop GetWorkshopById(int WorkShopId)
{
tbl_Workshop Wp = null;
string conStr = "Data Source=rakib;Initial Catalog=WorkShopDB;Integrated Security=True";
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);
}
public tbl_Workshop GetWorkshopById(int WorkShopId)
{
WorkShopDb WD = new WorkShopDb();
tbl_Workshop Wp = WD.GetWorkshopById(WorkShopId);
return(Wp);
}
//Insert new workshop into database
public bool InsertIntoDB(tbl_Workshop workshop)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source = ADMIN-PC\\PritSQLExpress; Initial Catalog = Workshop; Integrated Security = True;";
try
{
//Insert the 'workshop' into DB
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO tbl_Workshop VALUES (@WorkshopTitle, @WorkshopDate, @WorkshopDuration, @WorkshopTopics, NULL, NULL, NULL, NULL);";
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@WorkshopTitle", workshop.WorkshopTitle);
cmd.Parameters.AddWithValue("@WorkshopDate", workshop.WorkshopDate);
cmd.Parameters.AddWithValue("@WorkshopDuration", workshop.WorkshopDuration);
cmd.Parameters.AddWithValue("@WorkshopTopics", workshop.WorkshopTopics);
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(true);
}
//Fetch all workshops to display on screen
public List <tbl_Workshop> GetWorkshops()
{
List <tbl_Workshop> lstWorkshops = new List <tbl_Workshop>();
try
{
using (SqlConnection conn = new SqlConnection("Data Source = ADMIN-PC\\PritSQLExpress; Initial Catalog = Workshop; Integrated Security = True;"))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM tbl_Workshop", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tbl_Workshop workshop = new tbl_Workshop();
workshop.WorkshopId = int.Parse(dr["WorkshopId"].ToString());
workshop.WorkshopTitle = Convert.ToString(dr["WorkshopTitle"]);
workshop.WorkshopDate = DateTime.Parse(dr["WorkshopDate"].ToString());
workshop.WorkshopDuration = Convert.ToString(dr["WorkshopDuration"]);
workshop.WorkshopTopics = Convert.ToString(dr["WorkshopTopics"]);
lstWorkshops.Add(workshop);
}
dr.Close();
}
}
catch
{
}
return(lstWorkshops);
}
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();
WB.InsertWorkshop(Wp, Ls);
GetWorkShops();
}
public List <tbl_Workshop> GetWorkshops()
{
List <tbl_Workshop> Ls;
Ls = new List <tbl_Workshop>();
string conStr = "Data Source=rakib;Initial Catalog=WorkShopDB;Integrated Security=True";
string cmdStr = "select * 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 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 SqTrans = con.BeginTransaction();
try
{
//Inserting Workshop
SqlCommand cmd = new SqlCommand(cmdStr, con, SqTrans);
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 in tbl_Trainer_Workshop_Mapping
//with retrived WorkshopId from Scope_Identity
//And TrainerIds from Ls
if (WorkShopId != 0)
{
foreach (var TrainerId in Ls)
{
SqlCommand cmd2 = new SqlCommand(cmdStr2, con, SqTrans);
cmd2.Parameters.AddWithValue("@TrainerId", TrainerId);
cmd2.Parameters.AddWithValue("@WorkshopId", WorkShopId);
cmd2.ExecuteNonQuery();
}
}
//If Every thing is successful then commiting the transaction
SqTrans.Commit();
con.Close();
return(true);
}
catch
{
//If any thing goes wrong then Rolling back the transaction
SqTrans.Rollback();
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.ToString();
txtWorkShopDuration.Text = Wp.WorkShopDuration;
txtWorkShopTopics.Text = Wp.WorkShopTopics;
}
protected void gvWorkshops_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedRowWorkshopId = int.Parse(gvWorkshops.SelectedValue.ToString());
WorkshopBusiness workshopBusiness = new WorkshopBusiness();
tbl_Workshop workshop = workshopBusiness.GetWorkshopById(selectedRowWorkshopId);
txtWorkshopTitle.Text = workshop.WorkshopTitle;
txtWorkshopDate.Text = workshop.WorkshopDate.ToShortDateString();
txtWorkshopDuration.Text = workshop.WorkshopDuration;
txtWorkshopTopics.Text = workshop.WorkshopTopics;
}
public bool UpdateWorkshopById(tbl_Workshop Wp, int WorkShopId)
{
if (Wp.WorkShopDate > DateTime.Now)
{
WD.UpdateWorkshopById(Wp, WorkShopId);
return(true);
}
else
{
return(false);
}
}
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.WorkShopTopics = txtWorkShopTopics.Text;
Wp.WorkShopDuration = txtWorkShopDuration.Text;
WorkshopBusiness WB = new WorkshopBusiness();
WB.InsertWorkshop(Wp);
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());
WB.UpdateWorkshopById(Wp, id);
GetWorkShops();
}
//Insert record in database.
public bool InsertWorkshop(tbl_Workshop workshop)
{
if (workshop.WorkshopDate > DateTime.Now)
{
WorkshopDB workshopDB = new WorkshopDB();
if (workshopDB.InsertIntoDB(workshop))
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
tbl_Workshop workshop = new tbl_Workshop();
workshop.WorkshopTitle = txtWorkshopTitle.Text;
workshop.WorkshopDate = DateTime.Parse(txtWorkshopDate.Text);
workshop.WorkshopDuration = txtWorkshopDuration.Text;
workshop.WorkshopTopics = txtWorkshopTopics.Text;
WorkshopBusiness workshopBusiness = new WorkshopBusiness();
if (workshopBusiness.InsertWorkshop(workshop))
{
UpdateWorkshopsGrid();
ClearInputs();
}
else
{
}
}
public bool InsertWorkshop(tbl_Workshop Wp)
{
try
{
string conStr = "Data Source=rakib;Initial Catalog=WorkShopDB;Integrated Security=True";
string cmdStr = "insert into tbl_WorkShop values(@WorkShopTitle,@WorkShopDate,@WorkShopDuration,@WorkshopTopics,null,null,null,null) ";
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);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
//Get a perticular workshop by id
public tbl_Workshop GetWorkshopById(int?workshopId)
{
tbl_Workshop workshop = new tbl_Workshop();
using (SqlConnection conn = new SqlConnection("Data Source = ADMIN-PC\\PritSQLExpress; Initial Catalog = Workshop; Integrated Security = True;"))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM tbl_workshop WHERE WorkshopId = @WorkshopId", conn);
cmd.Parameters.AddWithValue("@WorkshopId", workshopId);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
workshop.WorkshopId = int.Parse(dr["WorkshopId"].ToString());
workshop.WorkshopTitle = Convert.ToString(dr["WorkshopTitle"]);
workshop.WorkshopDate = DateTime.Parse(dr["WorkshopDate"].ToString());
workshop.WorkshopDuration = Convert.ToString(dr["WorkshopDuration"]);
workshop.WorkshopTopics = Convert.ToString(dr["WorkshopTopics"]);
}
dr.Close();
}
return(workshop);
}
public tbl_Workshop GetWorkshopById(int WorkShopId)
{
tbl_Workshop Wp = WD.GetWorkshopById(WorkShopId);
return(Wp);
}
