ADO.NET数据访问模板整理

  1     /// <summary>
  2     /// 数据访问类:hi_test
  3     /// </summary>
  4     public partial class TestDA
  5     {
  6         public TestDA()
  7         {}
  8         #region  Method
  9 
 10 
 11 
 12         /// <summary>
 13         /// 增加一条数据
 14         /// </summary>
 15         public bool Add(TestEN model)
 16         {
 17             StringBuilder strSql=new StringBuilder();
 18             strSql.Append("insert into hi_test(");
 19             strSql.Append("name,showpage,status,create_time)");
 20             strSql.Append(" values (");
 21             strSql.Append("?name,?showpage,?status,?create_time)");
 22             MySqlParameter[] parameters = {
 23                     new MySqlParameter("?name", model.Name),
 24                     new MySqlParameter("?showpage", model.ShowPage),
 25                     new MySqlParameter("?status", model.Status),
 26                     new MySqlParameter("?create_time", model.CreateTime)};
 27 
 28             int rows = MySqlHelper.ExecuteNonQuery(ConnStrs.AndroidConnStrForWrite, strSql.ToString(), parameters);
 29             if (rows > 0)
 30             {
 31                 return true;
 32             }
 33             else
 34             {
 35                 return false;
 36             }
 37         }
 38         /// <summary>
 39         /// 更新一条数据
 40         /// </summary>
 41         public bool Update(TestEN model)
 42         {
 43             StringBuilder strSql=new StringBuilder();
 44             strSql.Append("update hi_test set ");
 45             strSql.Append("name=?name,");
 46             strSql.Append("showpage=?showpage,");
 47             strSql.Append("status=?status");
 48             strSql.Append("create_time=?create_time");
 49             strSql.Append(" where id=?id");
 50             MySqlParameter[] parameters = {
 51                     new MySqlParameter("?name", model.Name),
 52                     new MySqlParameter("?showpage",model.ShowPage),
 53                     new MySqlParameter("?status", model.Status),
 54                     new MySqlParameter("?create_time",model.CreateTime),
 55                     new MySqlParameter("?id",model.Id)};
 56 
 57             int rows = MySqlHelper.ExecuteNonQuery(ConnStrs.AndroidConnStrForWrite, strSql.ToString(), parameters);
 58             if (rows > 0)
 59             {
 60                 return true;
 61             }
 62             else
 63             {
 64                 return false;
 65             }
 66         }
 67 
 68         /// <summary>
 69         /// 删除一条数据
 70         /// </summary>
 71         public bool Delete(int Id)
 72         {
 73             
 74             StringBuilder strSql=new StringBuilder();
 75             strSql.Append("delete from hi_test ");
 76             strSql.Append(" where id=?id");
 77             MySqlParameter[] parameters = {
 78                     new MySqlParameter("?id",Id)
 79             };
 80 
 81             int rows = MySqlHelper.ExecuteNonQuery(ConnStrs.AndroidConnStrForWrite, strSql.ToString(), parameters);
 82             if (rows > 0)
 83             {
 84                 return true;
 85             }
 86             else
 87             {
 88                 return false;
 89             }
 90         }
 91 
 92         public IList<TestEN> Find(int pageIndex, int pageSize, TestEN condition, ref int totalCount)
 93         {
 94             string dataTableKey = "hi_test";
 95             string dataCodeKey = "id";
 96             string selectColumn = " id,name,showpage,status,create_time ";
 97 
 98             string sortString = " create_time desc ";
 99 
100             string searchCondition = " 1=1 ";
101             if (condition != null)
102             {
103                  searchCondition += !string.IsNullOrEmpty(condition.Name) ? string.Format(" AND name like ?name escape ‘^‘ ") : string.Empty;//模糊匹配
104                  //searchCondition += !string.IsNullOrEmpty(condition.Name) ? string.Format(" AND name=?name ") : string.Empty;//精确匹配
105                  searchCondition += condition.ShowPage != -1 ? string.Format(" AND showpage={0} ", condition.ShowPage) : string.Empty;
106                  searchCondition += condition.Status != -1 ? string.Format(" AND status={0} ", condition.Status) : string.Empty;
107               //   searchCondition += condition.CreateTime != -1 ? string.Format(" AND create_time={0} ", condition.CreateTime) : string.Empty;
108             }
109             try
110             {
111                 List<MySqlParameter> parameters = new List<MySqlParameter>();
112                 parameters.Add(new MySqlParameter("name", string.IsNullOrEmpty(condition.Name) ? string.Empty : "%" + condition.Name.Replace("%", "^%").Replace("_", "^_") + "%"));
113                 //parameters.Add(new MySqlParameter("name", string.IsNullOrEmpty(condition.Name) ? string.Empty : condition.Name));
114                 parameters.Add(new MySqlParameter("showpage", condition.ShowPage != -1 ? condition.ShowPage.ToString() : string.Empty));
115                 parameters.Add(new MySqlParameter("status", condition.Status != -1 ? condition.Status.ToString() : string.Empty));
116               //  parameters.Add(new MySqlParameter("create_time", condition.CreateTime != -1 ? condition.ShowPage.ToString() : string.Empty));
117                 totalCount = PageAccess.GetItemCount(ConnStrs.AndroidConnStrForWrite, dataTableKey, searchCondition, parameters.ToArray());
118                 if (totalCount > 0)
119                 {
120                     DataTable dt = PageAccess.GetDataSetByPage(ConnStrs.AndroidConnStrForWrite, pageIndex, pageSize, dataTableKey, selectColumn, dataCodeKey, searchCondition, sortString, parameters.ToArray()).Tables[0];
121                     return DataTableToList(dt);
122                 }
123             }
124             catch (Exception e)
125             {
126                 throw e;
127             }
128             return null;
129         }
130         /// <summary>
131         /// 获得数据列表
132         /// </summary>
133         public List<TestEN> DataTableToList(DataTable dt)
134         {
135             List<TestEN> modelList = new List<TestEN>();
136             int rowsCount = dt.Rows.Count;
137             if (rowsCount > 0)
138             {
139                 TestEN model;
140                 for (int n = 0; n < rowsCount; n++)
141                 {
142                     model = BuildEntity(dt.Rows[n]);
143                     modelList.Add(model);
144                 }
145             }
146             return modelList;
147         }
148         public TestEN BuildEntity(DataRow dr)
149         {
150             TestEN model = new TestEN();
151             if (dr["id"] != null && dr["id"].ToString() != "")
152             {
153                 model.Id = int.Parse(dr["id"].ToString());
154             }
155             if (dr["name"] != null && dr["name"].ToString() != "")
156             {
157                 model.Name = dr["name"].ToString();
158             }
159             if (dr["showpage"] != null && dr["showpage"].ToString() != "")
160             {
161                 model.ShowPage = int.Parse(dr["showpage"].ToString());
162             }
163             if (dr["status"] != null && dr["status"].ToString() != "")
164             {
165                 model.Status = int.Parse(dr["status"].ToString());
166             }
167             if (dr["create_time"] != null && dr["create_time"].ToString() != "")
168             {
169                 model.CreateTime = int.Parse(dr["create_time"].ToString());
170             }
171             return model;
172         }
173         /// <summary>
174         /// 得到一个对象实体
175         /// </summary>
176         public TestEN GetModel(int Id)
177         {
178             
179             StringBuilder strSql=new StringBuilder();
180             strSql.Append("select id,name,showpage,status,create_time from hi_test ");
181             strSql.Append(" where id=?id");
182             MySqlParameter[] parameters = {
183                     new MySqlParameter("?id", Id)
184             };
185 
186             TestEN model = new TestEN();
187             DataSet ds = MySqlHelper.ExecuteDataSet(ConnStrs.AndroidConnStrForWrite, strSql.ToString(), parameters);
188             if(ds.Tables[0].Rows.Count>0)
189             {
190                 model = BuildEntity(ds.Tables[0].Rows[0]);
191                 return model;
192             }
193             else
194             {
195                 return null;
196             }
197         }
198         #endregion  Method
199     }