CustomServiceでDBHelperを使用する方法
17903 ワード
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using LyqModels;
- using System.Data.SqlClient;
- using System.Data.Common;
- using System.Data;
-
- namespace LyqDAL
- {
- public class CustomService
- {
- public static bool Add( Custom custom)
- {
- string sql = "INSERT INTO custom (id, cname, departID, age, ename, password) VALUES (@id, @cname, @departID, @age, @ename, @password) ";
- Dictionary<string, object> dic = new Dictionary<string, object>();
- dic.Add("@id", custom.id);
- dic.Add("@cname", custom.cname);
- dic.Add("@departID", custom.departID);
- dic.Add("@age", custom.age);
- dic.Add("@ename", custom.ename);
- dic.Add("@password", custom.password);
- return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false;
-
-
- }
- public static bool Delete(int id)
- {
-
- string sql = " DELETE FROM custom WHERE id = @id ";
- Dictionary<string, object> dic = new Dictionary<string, object>();
- dic.Add("@id", id);
-
- return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false;
- }
- public static bool Modify(Custom custom )
- {
- string sql = "UPDATE custom SET cname = @cname, departID = @departID, age = @age, ename = @ename, password = @password WHERE id = @id; ";
- Dictionary<string, object> dic = new Dictionary<string, object>();
- dic.Add("@id", custom.id);
- dic.Add("@cname", custom.cname);
- dic.Add("@departID", custom.departID);
- dic.Add("@age", custom.age);
- dic.Add("@ename", custom.ename);
- dic.Add("@password", custom.password);
- return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false;
- }
- public static List
GetAllCustom()
- {
- string sql ="select * from custom";
- DataTable dt = DBHelper.GetDataSet(sql);
- List
list = new List();
- foreach (DataRow dr in dt.Rows)
- {
- Custom custom= new Custom();
- custom.id = (int)dr["id"];
- custom.cname = (string)dr["cname"];
- custom.ename = (string)dr["ename"];
- custom.age = (int)dr["age"];
- custom.departID = (int)dr["departID"];
- custom.password = (string)dr["password"];
- list.Add(custom);
-
- }
- return list;
-
-
-
-
- }
- public static List
GetCustom( int id,string cname,string ename,int departID,int age,string password)
- {
-
-
- string sql = "SELECT * FROM custom WHERE 1=1";
-
- if (departID>-1)
- {
- sql += "and departID=@departID";
-
- if (id > 0)
- {
- sql += " or id=@id";
-
- }
-
- else if (!Equals(cname, ""))
- {
- sql += " or cname like '%'+@cname+'%'";
-
- }
- else if (age>0)
- {
- sql += " or age=@age";
- }
-
- else if (!Equals(ename, ""))
- {
- sql += " or ename like '%'+@ename+'%'";
-
- }
- else if (!Equals(password, ""))
- {
- sql += " or password like '%'+@password+'%'";
-
- }
- }
-
-
- SqlParameter[] arr = new SqlParameter[]
- {
- new SqlParameter("@id", id),
- new SqlParameter("@cname", cname),
- new SqlParameter("@departID", departID),
- new SqlParameter("@age", age),
- new SqlParameter("@ename", ename),
- new SqlParameter("@password", password),
-
- };
-
- DataTable dt = DBHelper.GetDataTable(sql, arr);
- List
list = new List();
- foreach (DataRow dr in dt.Rows)
- {
- Custom custom= new Custom();
- custom.id = (int)dr["id"];
- custom.cname = (string)dr["cname"];
- custom.departID = (int)dr["departID"];
- custom.age = (int)dr["age"];
- custom.ename = (string)dr["ename"];
- custom.password = (string)dr["password"];
-
- list.Add(custom);
-
- }
- return list;
-
- }
- }
- }