You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

373 lines
15 KiB

  1. using System;
  2. using System.Data;
  3. using System.Text;
  4. using System.Data.SqlClient;
  5. namespace Basic.DAL
  6. {
  7. /// <summary>
  8. /// ���ݷ��ʲ�
  9. /// </summary>
  10. public partial class freight
  11. {
  12. #region �˴����ɹ�������
  13. /// <summary>
  14. /// ����һ������
  15. /// </summary>
  16. public int Add(Basic.Model.freight model)
  17. {
  18. StringBuilder strSql = new StringBuilder();
  19. strSql.Append("insert into tb_freight(");
  20. strSql.Append("title,gudingyf,yuanmian,yuanjifei,jianmian,jianjifei,content,paixu,state,add_time)");
  21. strSql.Append(" values (");
  22. strSql.Append("@title,@gudingyf,@yuanmian,@yuanjifei,@jianmian,@jianjifei,@content,@paixu,@state,@add_time)");
  23. strSql.Append(";select @@IDENTITY");
  24. SqlParameter[] parameters = {
  25. new SqlParameter("@title", SqlDbType.NVarChar,100),
  26. new SqlParameter("@gudingyf", SqlDbType.NVarChar,255),
  27. new SqlParameter("@yuanmian", SqlDbType.NVarChar,255),
  28. new SqlParameter("@yuanjifei", SqlDbType.NVarChar,255),
  29. new SqlParameter("@jianmian", SqlDbType.NVarChar,255),
  30. new SqlParameter("@jianjifei", SqlDbType.NVarChar,255),
  31. new SqlParameter("@content", SqlDbType.NVarChar,255),
  32. new SqlParameter("@paixu", SqlDbType.Int,10),
  33. new SqlParameter("@state", SqlDbType.Int,10),
  34. new SqlParameter("@add_time", SqlDbType.DateTime,23)};
  35. parameters[0].Value = model.title;
  36. parameters[1].Value = model.gudingyf;
  37. parameters[2].Value = model.yuanmian;
  38. parameters[3].Value = model.yuanjifei;
  39. parameters[4].Value = model.jianmian;
  40. parameters[5].Value = model.jianjifei;
  41. parameters[6].Value = model.content;
  42. parameters[7].Value = model.paixu;
  43. parameters[8].Value = model.state;
  44. parameters[9].Value = model.add_time;
  45. object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
  46. if (obj == null)
  47. {
  48. return 0;
  49. }
  50. else
  51. {
  52. return Convert.ToInt32(obj);
  53. }
  54. }
  55. /// <summary>
  56. /// ����һ������
  57. /// </summary>
  58. public bool Update(Basic.Model.freight model)
  59. {
  60. StringBuilder strSql = new StringBuilder();
  61. strSql.Append("update tb_freight set ");
  62. strSql.Append("title=@title,");
  63. strSql.Append("gudingyf=@gudingyf,");
  64. strSql.Append("yuanmian=@yuanmian,");
  65. strSql.Append("yuanjifei=@yuanjifei,");
  66. strSql.Append("jianmian=@jianmian,");
  67. strSql.Append("jianjifei=@jianjifei,");
  68. strSql.Append("content=@content,");
  69. strSql.Append("paixu=@paixu,");
  70. strSql.Append("state=@state,");
  71. strSql.Append("add_time=@add_time");
  72. strSql.Append(" where id=@id");
  73. SqlParameter[] parameters = {
  74. new SqlParameter("@title", SqlDbType.NVarChar,100),
  75. new SqlParameter("@gudingyf", SqlDbType.NVarChar,255),
  76. new SqlParameter("@yuanmian", SqlDbType.NVarChar,255),
  77. new SqlParameter("@yuanjifei", SqlDbType.NVarChar,255),
  78. new SqlParameter("@jianmian", SqlDbType.NVarChar,255),
  79. new SqlParameter("@jianjifei", SqlDbType.NVarChar,255),
  80. new SqlParameter("@content", SqlDbType.NVarChar,255),
  81. new SqlParameter("@paixu", SqlDbType.Int,10),
  82. new SqlParameter("@state", SqlDbType.Int,10),
  83. new SqlParameter("@add_time", SqlDbType.DateTime,23),
  84. new SqlParameter("@id", SqlDbType.Int,4)};
  85. parameters[0].Value = model.title;
  86. parameters[1].Value = model.gudingyf;
  87. parameters[2].Value = model.yuanmian;
  88. parameters[3].Value = model.yuanjifei;
  89. parameters[4].Value = model.jianmian;
  90. parameters[5].Value = model.jianjifei;
  91. parameters[6].Value = model.content;
  92. parameters[7].Value = model.paixu;
  93. parameters[8].Value = model.state;
  94. parameters[9].Value = model.add_time;
  95. parameters[10].Value = model.id;
  96. int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
  97. if (rows > 0)
  98. {
  99. return true;
  100. }
  101. else
  102. {
  103. return false;
  104. }
  105. }
  106. /// <summary>
  107. /// ɾ��һ������
  108. /// </summary>
  109. public bool Delete(int id)
  110. {
  111. StringBuilder strSql = new StringBuilder();
  112. strSql.Append("delete from tb_freight");
  113. strSql.Append(" where id=@id");
  114. SqlParameter[] parameters = {
  115. new SqlParameter("@id", SqlDbType.Int,4)};
  116. parameters[0].Value = id;
  117. int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
  118. if (rows > 0)
  119. {
  120. return true;
  121. }
  122. else
  123. {
  124. return false;
  125. }
  126. }
  127. /// <summary>
  128. /// �õ�һ������ʵ��
  129. /// </summary>
  130. public Basic.Model.freight GetModel(int id)
  131. {
  132. StringBuilder strSql=new StringBuilder();
  133. strSql.Append("select top 1 id,title,gudingyf,yuanmian,yuanjifei,jianmian,jianjifei,content,paixu,state,add_time from tb_freight");
  134. strSql.Append(" where id=@id");
  135. SqlParameter[] parameters = {
  136. new SqlParameter("@id", SqlDbType.Int,4)};
  137. parameters[0].Value = id;
  138. Basic.Model.freight model = new Basic.Model.freight();
  139. DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
  140. if (ds.Tables[0].Rows.Count > 0)
  141. {
  142. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["id"].ToString()))
  143. {
  144. model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
  145. }
  146. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["title"].ToString()))
  147. {
  148. model.title = ds.Tables[0].Rows[0]["title"].ToString();
  149. }
  150. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["gudingyf"].ToString()))
  151. {
  152. model.gudingyf = ds.Tables[0].Rows[0]["gudingyf"].ToString();
  153. }
  154. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["yuanmian"].ToString()))
  155. {
  156. model.yuanmian = ds.Tables[0].Rows[0]["yuanmian"].ToString();
  157. }
  158. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["yuanjifei"].ToString()))
  159. {
  160. model.yuanjifei = ds.Tables[0].Rows[0]["yuanjifei"].ToString();
  161. }
  162. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["jianmian"].ToString()))
  163. {
  164. model.jianmian = ds.Tables[0].Rows[0]["jianmian"].ToString();
  165. }
  166. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["jianjifei"].ToString()))
  167. {
  168. model.jianjifei = ds.Tables[0].Rows[0]["jianjifei"].ToString();
  169. }
  170. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["content"].ToString()))
  171. {
  172. model.content = ds.Tables[0].Rows[0]["content"].ToString();
  173. }
  174. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["paixu"].ToString()))
  175. {
  176. model.paixu = int.Parse(ds.Tables[0].Rows[0]["paixu"].ToString());
  177. }
  178. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["state"].ToString()))
  179. {
  180. model.state = int.Parse(ds.Tables[0].Rows[0]["state"].ToString());
  181. }
  182. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["add_time"].ToString()))
  183. {
  184. model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
  185. }
  186. return model;
  187. }
  188. else
  189. {
  190. return null;
  191. }
  192. }
  193. /// <summary>
  194. /// �õ�һ������ʵ��
  195. /// </summary>
  196. public Basic.Model.freight GetModelByState()
  197. {
  198. StringBuilder strSql=new StringBuilder();
  199. strSql.Append("select top 1 id,title,gudingyf,yuanmian,yuanjifei,jianmian,jianjifei,content,paixu,state,add_time from tb_freight");
  200. strSql.Append(" where state=@state");
  201. SqlParameter[] parameters =
  202. {
  203. new SqlParameter("@state", SqlDbType.Int,4)};
  204. parameters[0].Value = 0;
  205. Basic.Model.freight model = new Basic.Model.freight();
  206. DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
  207. if (ds.Tables[0].Rows.Count > 0)
  208. {
  209. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["id"].ToString()))
  210. {
  211. model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
  212. }
  213. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["title"].ToString()))
  214. {
  215. model.title = ds.Tables[0].Rows[0]["title"].ToString();
  216. }
  217. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["gudingyf"].ToString()))
  218. {
  219. model.gudingyf = ds.Tables[0].Rows[0]["gudingyf"].ToString();
  220. }
  221. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["yuanmian"].ToString()))
  222. {
  223. model.yuanmian = ds.Tables[0].Rows[0]["yuanmian"].ToString();
  224. }
  225. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["yuanjifei"].ToString()))
  226. {
  227. model.yuanjifei = ds.Tables[0].Rows[0]["yuanjifei"].ToString();
  228. }
  229. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["jianmian"].ToString()))
  230. {
  231. model.jianmian = ds.Tables[0].Rows[0]["jianmian"].ToString();
  232. }
  233. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["jianjifei"].ToString()))
  234. {
  235. model.jianjifei = ds.Tables[0].Rows[0]["jianjifei"].ToString();
  236. }
  237. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["content"].ToString()))
  238. {
  239. model.content = ds.Tables[0].Rows[0]["content"].ToString();
  240. }
  241. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["paixu"].ToString()))
  242. {
  243. model.paixu = int.Parse(ds.Tables[0].Rows[0]["paixu"].ToString());
  244. }
  245. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["state"].ToString()))
  246. {
  247. model.state = int.Parse(ds.Tables[0].Rows[0]["state"].ToString());
  248. }
  249. if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["add_time"].ToString()))
  250. {
  251. model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
  252. }
  253. return model;
  254. }
  255. else
  256. {
  257. return null;
  258. }
  259. }
  260. /// <summary>
  261. /// ����ǰ��������
  262. /// </summary>
  263. public DataSet GetList(int Top, string strWhere, string filedOrder)
  264. {
  265. StringBuilder strSql = new StringBuilder();
  266. strSql.Append("select ");
  267. if (Top > 0)
  268. {
  269. strSql.Append(" top " + Top.ToString());
  270. }
  271. strSql.Append("id,title,gudingyf,yuanmian,yuanjifei,jianmian,jianjifei,content,paixu,state,add_time");
  272. strSql.Append(" from tb_freight");
  273. if (strWhere.Trim() != "")
  274. {
  275. strSql.Append(" where " + strWhere);
  276. }
  277. strSql.Append(" order by " + filedOrder);
  278. return DbHelperSQL.Query(strSql.ToString());
  279. }
  280. /// <summary>
  281. /// ���ò�ѯ��ҳ����
  282. /// </summary>
  283. public DataSet GetList(string _strWhere, string _orderby, int _pageSize, int _page, out int recordCount)
  284. {
  285. BasicPage bp = new BasicPage();
  286. StringBuilder str_Sql = new StringBuilder();
  287. str_Sql.Append("select top " + _pageSize + " id,title,gudingyf,yuanmian,yuanjifei,jianmian,jianjifei,content,paixu,state,add_time from tb_freight");
  288. str_Sql.Append(" where ");
  289. str_Sql.Append(_strWhere);
  290. str_Sql.Append(" and id not in ");
  291. str_Sql.Append(" ( ");
  292. str_Sql.Append(" select top " + (_page - 1) * _pageSize + " id from tb_freight ");
  293. str_Sql.Append(" where ");
  294. str_Sql.Append(_strWhere);
  295. str_Sql.Append(" order by ");
  296. str_Sql.Append(_orderby);
  297. str_Sql.Append(" ) ");
  298. str_Sql.Append(" order by ");
  299. str_Sql.Append(_orderby);
  300. DataSet dst = bp.SelectDataBase("tb_freight", str_Sql.ToString());
  301. //
  302. recordCount = totlePage(showTotal(_strWhere), _pageSize);
  303. return dst;
  304. }
  305. //��ҳ��
  306. public int totlePage(int Total, int PageSize)
  307. {
  308. if (Total % PageSize == 0)
  309. {
  310. return Total / PageSize;
  311. }
  312. else
  313. {
  314. return Total / PageSize + 1;
  315. }
  316. }
  317. //������
  318. protected int showTotal(string strSql)
  319. {
  320. BasicPage bp = new BasicPage();
  321. int intTotal = 0;
  322. SqlDataReader myread = bp.getRead("select count(id) as CountId from tb_freight where " + strSql);
  323. if (myread.Read())
  324. {
  325. intTotal = Convert.ToInt32(myread["CountId"].ToString());
  326. }
  327. myread.Close();
  328. return intTotal;
  329. }
  330. /// <summary>
  331. /// �Ƿ����ڸü�¼
  332. /// </summary>
  333. public bool Exists(int id)
  334. {
  335. StringBuilder strSql = new StringBuilder();
  336. strSql.Append("select count(1) from tb_freight");
  337. strSql.Append(" where id=@id");
  338. SqlParameter[] parameters = {
  339. new SqlParameter("@id", SqlDbType.Int,4)};
  340. parameters[0].Value = id;
  341. return DbHelperSQL.Exists(strSql.ToString(), parameters);
  342. }
  343. /// <summary>
  344. /// �޸�һ������
  345. /// </summary>
  346. public void UpdateField(int id, string strValue)
  347. {
  348. StringBuilder strSql = new StringBuilder();
  349. strSql.Append("update tb_freight set " + strValue);
  350. strSql.Append(" where id=" + id);
  351. DbHelperSQL.ExecuteSql(strSql.ToString());
  352. }
  353. #endregion
  354. }
  355. }