1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using System.Data;
  5using Lawyee.DBUtility;
  6using System.Data.SqlClient;
  7using Model;
  8
  9namespace SqlServerDAL
 10...{
 11    public class Users
 12    ...{
 13        用户注册#region 用户注册
 14        /**//// <summary>
 15        /// 用户注册
 16        /// </summary>
 17        /// <param name="sName">用户名称</param>
 18        /// <param name="sPwd">用户密码</param>
 19        /// <returns></returns>
 20        public static bool UserRegister(string sName, string sPwd)
 21        ...{
 22            SqlParameter[] paras = new SqlParameter[2];
 23            paras[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
 24            paras[0].Value = sName;
 25            paras[1] = new SqlParameter("@UserPwd", SqlDbType.NVarChar, 50);
 26            paras[1].Value = sPwd;
 27            int i = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "UserRegister", paras);
 28            if (i > 0)
 29            ...{
 30                return true;
 31            }
 32            else
 33            ...{
 34                return false;
 35            }
 36        }
 37        #endregion
 38
 39        发表文章#region 发表文章
 40        /**//// <summary>
 41        /// 发表文章
 42        /// </summary>
 43        /// <param name="sArticleName">文章标题</param>
 44        /// <param name="iTypeID">文章分类ID</param>
 45        /// <param name="iUserID">用户ID</param>
 46        /// <param name="sSummary">摘要</param>
 47        /// <param name="sContent">内容</param>
 48        /// <returns></returns>
 49        public static bool CreatArticle(string sArticleName, int iTypeID, int iUserID, string sSummary, string sContent, string sSource)
 50        ...{
 51            SqlParameter[] paras = new SqlParameter[6];
 52            paras[0] = new SqlParameter("@ArticleName", SqlDbType.NVarChar, 50);
 53            paras[0].Value = sArticleName;
 54            paras[1] = new SqlParameter("@TypeID", SqlDbType.Int);
 55            paras[1].Value = iTypeID;
 56            paras[2] = new SqlParameter("UserID", SqlDbType.Int);
 57            paras[2].Value = iUserID;
 58            paras[3] = new SqlParameter("@Summary", SqlDbType.NVarChar, 2000);
 59            paras[3].Value = sSummary;
 60            paras[4] = new SqlParameter("@ArticleContent", SqlDbType.Text);
 61            paras[4].Value = sContent;
 62            paras[5] = new SqlParameter("@ArticleDatasoure", SqlDbType.NVarChar, 100);
 63            paras[5].Value = sSource;
 64            int i = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "CreatArticle", paras);
 65            if (i > 0)
 66            ...{
 67                return true;
 68            }
 69            else
 70            ...{
 71                return false;
 72            }
 73        }
 74        #endregion
 75
 76        取得所有分类#region 取得所有分类
 77        /**//// <summary>
 78        /// 取得所有分类
 79        /// </summary>
 80        /// <returns></returns>
 81        public static DataTable GetAllType()
 82        ...{
 83            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetAllType", null);
 84            DataTable table = new DataTable();
 85            table.Load(reader);
 86            reader.Close();
 87            return table;
 88        }
 89        #endregion
 90
 91        取得文章#region 取得文章
 92        /**//// <summary>
 93        /// 取得文章
 94        /// </summary>
 95        /// <param name="iPageIndex">当前页</param>
 96        /// <param name="iPageSize">每页显示的数据</param>
 97        /// <param name="iTypeID">类别ID</param>
 98        /// <param name="iUserID">用户ID</param>
 99        /// <param name="sArticleName">文章标题</param>
100        /// <param name="sSummary">摘要</param>
101        /// <returns></returns>
102        public static DataSet GetArticle(int iPageIndex, int iPageSize, int iTypeID, int iUserID, string sArticleName, string sSummary)
103        ...{
104            //@PageIndex  INT = 0,
105            //@PageSize   INT = 20,
106            //@ArticleName    nVARCHAR(50) = '',        --文章标题
107            //@TypeID    int = 0,                        --分类ID
108            //@UserID int = 0,                        --用户ID
109            //@Summary nvarchar(1000)=''                --摘要
110            SqlParameter[] paras = new SqlParameter[6];
111            paras[0] = new SqlParameter("@PageIndex", SqlDbType.Int);
112            paras[0].Value = iPageIndex;
113            paras[1] = new SqlParameter("@PageSize", SqlDbType.Int);
114            paras[1].Value = iPageSize;
115            paras[2] = new SqlParameter("@ArticleName", SqlDbType.NVarChar, 50);
116            paras[2].Value = sArticleName;
117            paras[3] = new SqlParameter("@TypeID", SqlDbType.Int);
118            paras[3].Value = iTypeID;
119            paras[4] = new SqlParameter("@UserID", SqlDbType.Int);
120            paras[4].Value = iUserID;
121            paras[5] = new SqlParameter("@Summary", SqlDbType.NVarChar, 1000);
122            paras[5].Value = sSummary;
123            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetArticle", paras);
124            DataSet ds = new DataSet();
125            ds.Tables.Add(new DataTable("Article"));
126            ds.Tables.Add(new DataTable("Total"));
127            DataTable[] tables = new DataTable[2] ...{ ds.Tables[0], ds.Tables[1] };
128            ds.Load(reader, LoadOption.Upsert, tables);
129            reader.Close();
130            return ds;
131        }
132        #endregion
133
134        获取文章详细信息#region 获取文章详细信息
135        /**//// <summary>
136        /// 获取文章详细信息
137        /// </summary>
138        /// <param name="iArticleID"></param>
139        /// <returns></returns>
140        public static DataTable GetArticleDetail(int iArticleID)
141        ...{
142            SqlParameter para = new SqlParameter("@ArticleID", SqlDbType.Int);
143            para.Value = iArticleID;
144            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ArticleDetail", para);
145            DataTable table = new DataTable();
146            table.Load(reader);
147            reader.Close();
148            return table;
149        }
150
151        /**//// <summary>
152        /// 获取文章详细信息
153        /// </summary>
154        /// <param name="iArticleID"></param>
155        /// <returns></returns>
156        public static ArticleModel GetArticleDetailForUpdate(int iArticleID)
157        ...{
158            ArticleModel am = new ArticleModel();
159            SqlParameter para = new SqlParameter("@ArticleID", SqlDbType.Int);
160            para.Value = iArticleID;
161            SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ArticleDetail", para);
162            if (sdr.Read())
163            ...{
164                am.ArticleName = Convert.ToString(sdr["ArticleName"]);
165                am.ArticleContent = Convert.ToString(sdr["ArticleContent"]);
166                if (sdr["Summary"] != null)
167                ...{
168                    am.Summary = Convert.ToString(sdr["Summary"]);
169                }
170                else
171                ...{
172                    am.Summary = "";
173                }
174                ArticleTypeModel atm=new ArticleTypeModel();
175                atm.TypeID=Convert.ToInt32(sdr["TypeID"]);
176                am.ArticleType = atm;
177                am.ArticleDatasoure = Convert.ToString(sdr["ArticleDatasoure"]);
178            }
179            sdr.Close();
180            return am;
181        }
182        #endregion
183
184        统计#region 统计
185        /**//// <summary>
186        /// 分类统计
187        /// </summary>
188        /// <param name="iUserID"></param>
189        /// <returns></returns>
190        public static DataTable TypeGroup(int iUserID)
191        ...{
192            SqlParameter para = new SqlParameter("@UserID", SqlDbType.Int);
193            para.Value = iUserID;
194            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "TypeGroup", para);
195            DataTable table = new DataTable();
196            table.Load(reader);
197            reader.Close();
198            return table;
199        }
200
201        /**//// <summary>
202        /// 根据用户编号，获取用户文章个数
203        /// </summary>
204        /// <param name="user"></param>
205        /// <returns></returns>
206        public static int GetUserArticleCount(UsersModel user)
207        ...{
208            SqlParameter sp = new SqlParameter("@UserID",SqlDbType.Int);
209            sp.Value = user.UserID;
210            int count=Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Users_GetUserArticleCount", sp));
211            return count;
212        }
213        #endregion
214
215        用户登录#region 用户登录
216        /**//// <summary>
217        /// 用户登录
218        /// </summary>
219        /// <param name="userName"></param>
220        /// <param name="userPassword"></param>
221        /// <returns></returns>
222        public static UsersModel UserLogin(string userName, string userPassword)
223        ...{
224            UsersModel user = new UsersModel();
225            SqlParameter[] sp = new SqlParameter[2];
226            sp[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
227            sp[1] = new SqlParameter("@UserPwd", SqlDbType.NVarChar, 50);
228            sp[0].Value = userName;
229            sp[1].Value = userPassword;
230            SqlDataReader sdr = (SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "UserLogin", sp));
231            if (sdr.Read())
232            ...{
233                user.UserID = Convert.ToInt32(sdr["UserID"]);
234                user.UserName = Convert.ToString(sdr["UserName"]);
235                user.ArticleCount = Convert.ToInt32(sdr["ArticleCount"]);
236            }
237            sdr.Close();
238            return user;
239        }
240        #endregion
241
242        修改用户密码#region 修改用户密码
243        /**//// <summary>
244        /// 修改用户密码
245        /// </summary>
246        /// <param name="userID">用户ID</param>
247        /// <param name="oldPassword">旧密码</param>
248        /// <param name="newPassword">新密码</param>
249        /// <returns></returns>
250        public static bool ChangePassword(int userID,string oldPassword,string newPassword)
251        ...{
252            SqlParameter[] sp = new SqlParameter[3];
253            sp[0] = new SqlParameter("@UserID",SqlDbType.Int);
254            sp[1] = new SqlParameter("@OldPWD", SqlDbType.NVarChar, 50);
255            sp[2] = new SqlParameter("@NewPWD",SqlDbType.NVarChar,50);
256            sp[0].Value = userID;
257            sp[1].Value = oldPassword;
258            sp[2].Value = newPassword;
259            int i=SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Users_ChangePassword", sp);
260            if (i > 0)
261            ...{
262