// 返回 Custom 表所有行
public List<Custom> ListCustom()
{
SqlConnection con = new SqlConnection(this.strcon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
string strsql1 = "select * from Custom a, Department b where a.departID=b.id";
cmd.CommandText = strsql1;
SqlDataReader rd1 = cmd.ExecuteReader();
List<Custom> list1 = new List<Custom>();
Custom n = null;
while (rd1.Read())
{
n = new Custom();
n.department = new Department();
n.id = Convert.ToInt32(rd1["id"]);
n.cname = rd1["cname"].ToString();
n.age = Convert.ToInt32(rd1["age"]);
n.ename = rd1["ename"].ToString();
n.password = rd1["password"].ToString();
//n.departID = Convert.ToInt32(rd["departID"]);
n.department.id = Convert.ToInt32(rd1["departID"]);
n.department.departname = rd1["departname"].ToString();
//n.department.description = rd1["description"].ToString();
list1.Add(n);
}
rd1.Close();
con.Close();
return list1;
}
// 返回 Custom 表所有行
public List<Custom> ListCustom()
{
DataSet1.customDataTable t = cta.GetData();
if(t== null|| t.Count== 0)
{
return null;
}
List<Custom> list1 = new List<Custom>();
Custom n = null;
foreach(DataSet1.customRow r in t) {
n = new Custom();
n.department = new Department();
n.id = r.id;
n.cname = r.cname;
n.age = r.age;
n.ename = r.ename;
n.password = r.password;
n.departID = r.departID;
n.department.id = r.departID;
DataSet1.departmentRow r2 = dta.DepartmentFindByIdR(r.departID)[0];
n.department.departname= r2.departname;
n.department.departname = dta.DepartmentFindByIdR(r.departID)[0].departname;
list1.Add(n);
}
return list1;
}
// 查询 cname
public void CnameQuery(string cname)
{
if(cname== null || cname.Equals("")) return;
SqlConnection con = new SqlConnection(this.strcon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
/*
create proc CnameQuery
@cname nvarchar(50)
as
select * from Custom a, Department b where cname=@cname and a.departID=b.id
*/
//string strsql = "select * from Custom a, Department b where cname=@cname and a.departID=b.id";
string strsql = "CnameQuery";
cmd.CommandText = strsql;
cmd.CommandType= CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@cname", cname));
List<Custom> list = new List<Custom>();
Custom n = null;
SqlDataReader rd= cmd.ExecuteReader();
while (rd.Read())
{
n = new Custom();
n.department = new Department();
n.id = Convert.ToInt32(rd["id"]);
n.cname = rd["cname"].ToString();
n.age = Convert.ToInt32(rd["age"]);
n.ename = rd["ename"].ToString();
n.password = rd["password"].ToString();
//n.departID = Convert.ToInt32(rd["departID"]);
n.department.id = Convert.ToInt32(rd["departID"]);
n.department.departname = rd["departname"].ToString();
//n.department.description = rd1["description"].ToString();
list.Add(n);
}
this.TakeJson(list);
rd.Close();
con.Close();
}
// 插入或更新一条 Custom 记录
public void CreateCustom(Custom f_custom)
{
if (f_custom != null)
{
SqlConnection con = new SqlConnection(this.strcon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
int id = f_custom.id;
// insert check by the departID
/*
同 CreateDepartmentView
*/
//string strsql1 = "select * from Department where id=@id";
string strsql1 = "CreateDepartmentView";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strsql1;
cmd.Parameters.Add(new SqlParameter("id", f_custom.departID));
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
rd.Close();
if (id < 1)
{
/*
create proc InsertCustom
@cname nvarchar(50),
@departID int,
@age int,
@ename nvarchar(50),
@password nvarchar(50)
as
insert into Custom values(@cname, @departID, @age, @ename, @password);select @@IDENTITY as id
*/
// insert custom
//string strsql2 = "insert into Custom values(@cname, @departID, @age, @ename, @password);select @@IDENTITY as id";
string strsql2 = "InsertCustom";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strsql2;
cmd.Parameters.Clear();
}
else
{
/*
create proc UpdateCustom
@id int,
@cname nvarchar(50),
@departID int,
@age int,
@ename nvarchar(50),
@password nvarchar(50)
as
update Custom set cname=@cname, departID=@departID, age=@age, ename=@ename, password=@password where id=@id
*/
// update department
//string strsql3 = "update Custom set cname=@cname, departID=@departID, age=@age, ename=@ename, password=@password where id=@id";
string strsql3 = "UpdateCustom";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strsql3;
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("id", id));
}
cmd.Parameters.Add(new SqlParameter("cname", f_custom.cname));
cmd.Parameters.Add(new SqlParameter("departID", f_custom.departID));
cmd.Parameters.Add(new SqlParameter("age", f_custom.age));
cmd.Parameters.Add(new SqlParameter("ename", f_custom.ename));
cmd.Parameters.Add(new SqlParameter("password", f_custom.password));
rd = cmd.ExecuteReader();
if (rd.Read())
{
// insert return the id
var re = new { id = Convert.ToInt32(rd["id"]), message = "success" };
this.TakeJson(re);
}
else
{
// update success
var re = new { id = 0, message = "success" };
this.TakeJson(re);
}
rd.Close();
con.Close();
}
else
{
// the departID is illegal
var re = new { id = 0, message = "error" };
this.TakeJson(re);
}
}
else
{
// the f_custom data is empty
var re = new { id = 0, message = "error" };
this.TakeJson(re);
}
}
// 根据 id 获取 Custom 一条记录
public void CreateCustomView(int id)
{
/*
create proc CreateCustomView
@id int
as
select * from Custom a, Department b where a.id=@id and a.departID=b.id
*/
SqlConnection con = new SqlConnection(this.strcon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
//string strsql = "select * from Custom a, Department b where a.id=@id and a.departID=b.id";
string strsql = "CreateCustomView";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strsql;
cmd.Parameters.Add(new SqlParameter("id", id));
SqlDataReader rd = cmd.ExecuteReader();
// custom class
Custom n = null;
if (rd.Read())
{
n = new Custom();
n.id = Convert.ToInt32(rd["id"]);
n.cname = rd["cname"].ToString();
n.department = new Department();
//n.department.id = Convert.ToInt32(rd["departID"]);
n.department.departname = rd["departname"].ToString();
n.age = Convert.ToInt32(rd["age"]);
n.ename = rd["ename"].ToString();
n.password = rd["password"].ToString();
}
rd.Close();
con.Close();
if (n != null)
{
this.TakeJson(n);
}
}
// 查询 cname
public void CnameQuery(string cname)
{
if(cname== null || cname.Equals("")) return;
DataSet1.customDataTable t = cta.CustomFindByCname(cname);
List<Custom> list = new List<Custom>();
Custom n = null;
foreach(DataSet1.customRow r in t)
{
n = new Custom();
n.department = new Department();
n.id = r.id;
n.cname = r.cname;
n.age = r.age;
n.ename = r.ename;
n.password = r.password;
n.department.id = r.departID;
n.department.departname = dta.DepartmentFindByIdR(r.departID)[0].departname;
list.Add(n);
}
this.TakeJson(list);
}
// 插入或更新一条 Custom 记录
public void CreateCustom(Custom f_custom)
{
string message = "error";
if (f_custom != null)
{
int id = f_custom.id;
// insert check by the departID
DataSet1.departmentDataTable t = dta.DepartmentFindByIdR(f_custom.departID);
if (t!= null && t.Count> 0)
{
message = "success";
if (id < 1)
{
// insert custom
id= cta.CustomInsertQuery(f_custom.cname, f_custom.departID, f_custom.age, f_custom.ename, f_custom.password);
// insert return the id
var re = new { id = id, message = message };
this.TakeJson(re);
}
else
{
cta.CustomUpdateQuery(f_custom.cname, f_custom.departID, f_custom.age, f_custom.ename, f_custom.password, id);
// update success
var re = new { id = 0, message = message };
this.TakeJson(re);
}
}
else
{
// the departID is illegal
var re = new { id = 0, message = message };
this.TakeJson(re);
}
}
else
{
// the f_custom data is empty
var re = new { id = 0, message = message };
this.TakeJson(re);
}
}
// 根据 id 获取 Custom 一条记录
public void CreateCustomView(int id)
{
DataSet1.customDataTable t = cta.CustomFindByIdR(id);
// custom class
Custom n = null;
foreach(DataSet1.customRow r in t)
{
n = new Custom();
n.id = r.id;
n.cname = r.cname;
n.department = new Department();
n.department.departname = dta.DepartmentFindByIdR(r.departID)[0].departname;
n.age = r.age;
n.ename = r.ename;
n.password = r.password;
}
if (n != null)
{
TakeJson(n);
}
}