public bool DeleteCourse(Course id)
{
using (SqlConnection con = DBHelp.GetConnection())
{
string sql = "delete from Course where ID=@id";
SqlParameter[] sp = new SqlParameter[]
{
new SqlParameter("@id",id.DepartmentId),
};
string sql2 = "delete from Course_User where CourseID=@id";
SqlParameter[] sp2 = new SqlParameter[]
{
new SqlParameter("@id",id.DepartmentId),
};
if (DBHelp.ExecuteCommand(sql, sp) > 0 && DBHelp.ExecuteCommand(sql2, sp2) > 0)
{
return true;
}
else
{
return false;
}
}
}
public static bool GetDeleteCourse(Course id)
{
if (CourseService.DeleteCourse(id))
{
return true;
}
else
{
return false;
}
}
public static bool courseInsert(Course ci)
{
if (CourseService.insertCourse(ci))
{
return true;
}
else
{
return false;
}
}
public static bool insertCourse(Course ic)
{
string sql = "insert into Course ([Name]) values(@Name)";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Name",ic.DepartmentName)
};
int i = DBHelp.ExecuteCommand(sql, para);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
public static List<Course> SelectCourse()
{
using (SqlConnection con=DBHelp.GetConnection())
{
string sql = "select * from Course";
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
List<Course> list = new List<Course>();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Course c = new Course();
c.DepartmentId = Convert.ToInt32(dr["ID"].ToString());
c.DepartmentName = dr["Name"].ToString();
list.Add(c);
}
dr.Close();
con.Close();
return list;
}
}
public static List<Course> ListCourse()
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Course";
conn.Open();
SqlDataReader DR = cmd.ExecuteReader();
List<Course> list = new List<Course>();
while (DR.Read())
{
Course c = new Course();
c.DepartmentId = (int)DR[0];
c.DepartmentName = DR[1].ToString();
list.Add(c);
}
DR.Close();
conn.Close();
return list;
}
}
public bool insertCourse(Course ic, string userID)
{
string sql = "insert into Course ([Name]) values(@Name)";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Name",ic.DepartmentName)
};
int i = DBHelp.ExecuteCommand(sql, para);
if (i <= 0)
{
return false;
}
sql = "select ID from Course where Name = '" + ic.DepartmentName + "'";
SqlConnection con = DBHelp.GetConnection();
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
int courseID = 0;
while (dr.Read())
{
courseID = Convert.ToInt32(dr["ID"].ToString());
}
dr.Close();
con.Close();
sql = "insert into Course_User (CourseID, UserID) values(@CourseID, @UserID)";
para = new SqlParameter[]
{
new SqlParameter("@CourseID", courseID),
new SqlParameter("@UserID", userID)
};
i = DBHelp.ExecuteCommand(sql, para);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
