public static bool Delete(Command cmd,string id)
{
string table = Selection.Table;
string sql = string.Format(@"delete from {0} where Id=@Id", table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static bool Insert(Command cmd, IDictionary<string, object> values)
{
string table = Course.Table;
string columns = string.Join(",", values.Keys.Select(p => p == "Teacher" ? p + "_Id" : p).ToArray());
string paras = string.Join(",", values.Keys.Select(p => "@" + (p == "Teacher" ? p + "_Id" : p)).ToArray());
string sql = string.Format(@"insert {1} ({0}) values ({2})", columns, table, paras);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
if (field == "Teacher")
cmd.AddParameter("@" + field + "_Id", values[field]);
else
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static bool Delete(Command cmd, Role role, string id)
{
string table = null;
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"delete from {0} where Id=@Id", table);
cmd.CommandText = sql;
cmd.AddParameter("@Id", id);
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static bool Update(Command cmd, Role role, IDictionary<string, object> values)
{
string table = null;
string cals = string.Join(",", values.Keys.Where( p=>p!="oId").Select(p => p + "=" + "@" + p).ToArray());
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"update {1} set {0} where Id=@oId", cals, table);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static bool Insert(Command cmd, Role role, IDictionary<string, object> values)
{
string table = null;
string columns = string.Join(",", values.Keys.Select(p => p).ToArray());
string paras = string.Join(",", values.Keys.Select(p => "@" + p).ToArray());
switch (role)
{
case Role.Administrator:
table = Administrator.Table;
break;
case Role.Teacher:
table = Teacher.Table;
break;
case Role.Student:
table = Student.Table;
break;
}
string sql = string.Format(@"insert {1} ({0}) values ({2})", columns, table, paras);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}
public static bool Update(Command cmd,IDictionary<string,object> values )
{
string table = Selection.Table;
string cals = string.Join(",",
(from p in values.Keys
where p != "oId"
let n = (p != "Id" && p != "Score" ? p + "_Id" : p)
select n + "=" + "@" + n).ToArray());
string sql = string.Format(@"update {1} set {0} where Id=@oId", cals, table);
cmd.CommandText = sql;
foreach (string field in values.Keys)
{
if (field != "Id" && field!="Score" && field!="oId")
cmd.AddParameter("@" + field + "_Id", values[field]);
else
cmd.AddParameter("@" + field, values[field]);
}
int result = cmd.ExecuteNonQuery();
return result > 0;
}