// 返回 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); } }