usersmake.cs

企业内部的短信交流管理平台。 设计详细

using System;
using System.Data;
using System.Data.SqlClient;

namespace Com.SMS.Dal
{
	/// <summary>
	/// UsersMake 的摘要说明。
	/// </summary>
	public class UsersMake
	{
		/// <summary>
		/// 添加用户
		/// </summary>
		/// <param name="us"></param>
		/// <returns></returns>
		public bool Insert(Entity.User us)
		{
			SqlParameter[] parms=
			{
				new SqlParameter("@userName",us.UserName),
				new SqlParameter("@userPwd",us.UserPwd),
				new SqlParameter("@userBir",us.UserBir),
				new SqlParameter("@userTel",us.UserTel),
				new SqlParameter("@userSex",us.UserSex),
				new SqlParameter("@userAge",us.UserAge),
				new SqlParameter("@userEmail",us.UserEmail),
				new SqlParameter("@userTrueName",us.UserTrueName),
			};

			int i=SqlBasic.ExecuteNonQuery(SqlBasic.CONN_STRING,CommandType.Text,SqlCollections.SQL_USERS_INSERT,parms);

			if(i==1)
			{
				return true;
			}
			else
				return false;
		}

		/// <summary>
		/// 删除用户
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		public void Delete(int userId)
		{
			SqlConnection scon=new SqlConnection(SqlBasic.CONN_STRING);
			scon.Open();
			SqlTransaction trans=scon.BeginTransaction();

			//查和删除的发信信息关联的内容表
			SqlDataReader readers=SqlBasic.ExecuteReader(
				SqlBasic.CONN_STRING,
				CommandType.Text,
				SqlCollections.SQL_SENDBOX_SELECTCONTENTBYUSER,
				new SqlParameter("@senderId",userId)
				);
			//查删除收信信息关联的内容表
			SqlDataReader readerr=SqlBasic.ExecuteReader(
				SqlBasic.CONN_STRING,
				CommandType.Text,
				SqlCollections.SQL_RECIVEBOX_SELECTCONTENTBYUSER,
				new SqlParameter("@ReciveuserId",userId)
				);

			try
			{
				//删除照片
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_PHOTOS_DELETEBYUSER,
					new SqlParameter("@userId",userId)
					);
				//删除相册
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_ALBUM_DELETEBYUSER,
					new SqlParameter("@userId",userId)
					);
				//删除地址本联系人中的记录
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_AADDRESSBOOKDETAIL_DELETEBYUSER,
					new SqlParameter("@userId",userId),
					new SqlParameter("@usersId",userId)
					);
				//删除地址本
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_ADDRESSBOOK_DELETEBYUSER,
					new SqlParameter("@userId",userId)
					);
				//删除邮件信息时有点麻烦
				//删除发信箱    用数据库级联删除
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_SENDBOX_DELETEBYUSER,
					new SqlParameter("@senderId",userId)
					);
				while(readers.Read())
				{
					int conId=readers.GetInt32(0);
					int readersr=Convert.ToInt32(
							SqlBasic.ExecuteScalar(
								trans,
								CommandType.Text,
								SqlCollections.SQL_CONTENTS_ISHAVEINRECIVE,
								new SqlParameter("@conId",conId)
							)
						);
					if(readersr==0)
					{
						SqlBasic.ExecuteNonQuery(
							trans,
							CommandType.Text,
							SqlCollections.SQL_CONTENTS_DELETE,
							new SqlParameter("@conId",conId)
							);
					}
				}
				readers.Close();
				//删除收信记录 用数据库级联删除
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_RECIVEBOX_DELETEBYUSER,
					new SqlParameter("@ReciveuserId",userId)
					);
				while(readerr.Read())
				{
					int conId=readerr.GetInt32(0);
					int readerrs=Convert.ToInt32(
							SqlBasic.ExecuteScalar(
							trans,
							CommandType.Text,
							SqlCollections.SQL_CONTENTS_ISHAVEINSEND,
							new SqlParameter("@conId",conId)
							)
						);
					if(readerrs==0)
					{
						SqlBasic.ExecuteNonQuery(
							trans,
							CommandType.Text,
							SqlCollections.SQL_CONTENTS_DELETE,
							new SqlParameter("@conId",conId)
							);
					}
				}
				readerr.Close();
				//删除用户权限关系
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_USERANDPOPEDOM_DELETEBYUSER,
					new SqlParameter("@userId",userId)
					);
				SqlBasic.ExecuteNonQuery(
					trans,
					CommandType.Text,
					SqlCollections.SQL_USERS_DELETE,
					new SqlParameter("@userId",userId)
					);
				trans.Commit();
			}
			catch(Exception ex)
			{
				trans.Rollback();
				throw ex;
			}
			finally
			{
				scon.Close();
			}
		}

		/// <summary>
		/// 修改用户
		/// </summary>
		/// <param name="us"></param>
		/// <param name="userId"></param>
		/// <returns></returns>
		public bool Update(Entity.User us,int userId)
		{
			SqlParameter[] parms=
			{
				new SqlParameter("@userName",us.UserName),
				new SqlParameter("@userPwd",us.UserPwd),
				new SqlParameter("@userBir",us.UserBir),
				new SqlParameter("@userTel",us.UserTel),
				new SqlParameter("@userSex",us.UserSex),
				new SqlParameter("@userAge",us.UserAge),
				new SqlParameter("@userEmail",us.UserEmail),
				new SqlParameter("@userTrueName",us.UserTrueName),
				new SqlParameter("@userId",userId),
			};

			int i=SqlBasic.ExecuteNonQuery(SqlBasic.CONN_STRING,CommandType.Text,SqlCollections.SQL_USERS_UPDATE,parms);

			if(i==1)
			{
				return true;
			}
			else
				return false;
		}

		/// <summary>
		/// 查询用户
		/// </summary>
		/// <returns></returns>
		public DataTable FindAll()
		{
			DataTable dt=new DataTable();

			SqlBasic.FillDatatable(SqlBasic.CONN_STRING,CommandType.Text,SqlCollections.SQL_USERS_SELECT,dt);

			return dt;
		}

		/// <summary>
		/// 用户登录
		/// </summary>
		/// <param name="name"></param>
		/// <param name="pass"></param>
		/// <param name="isa"></param>
		/// <returns></returns>
		public Entity.User Login(string name,string pass)
		{
			SqlParameter[] parms=
			{
				new SqlParameter("@userName",name),
				new SqlParameter("@userPwd",pass),
			};

			SqlDataReader reader=SqlBasic.ExecuteReader(SqlBasic.CONN_STRING,CommandType.Text,SqlCollections.SQL_USERS_LOGIN,parms);

			Entity.User us=new Com.SMS.Entity.User();

			while(reader.Read())
			{
				us.UserId=reader.GetInt32(0);
				us.UserName=reader.GetString(1);
				us.UserPwd=reader.GetString(2);
				us.UserBir=reader.GetDateTime(3);
				us.UserTel=reader.GetString(4);
				us.UserSex=reader.GetString(5);
				us.UserAge=reader.GetInt32(6);
				us.UserEmail=reader.GetString(7);
				us.UserTrueName=reader.GetString(8);
			}

			return us;
		}

		/// <summary>
		/// 按编号查询用户
		/// </summary>
		/// <param name="UserId"></param>
		/// <returns></returns>
		public Entity.User FindById(int UserId)
		{
			SqlDataReader reader=SqlBasic.ExecuteReader(
				SqlBasic.CONN_STRING,
				CommandType.Text,
				SqlCollections.SQL_USERS_SELECTBYID,
				new SqlParameter("@userId",UserId)
				);

			Entity.User us=new Com.SMS.Entity.User();
			while(reader.Read())
			{
				us.UserId=reader.GetInt32(0);
				us.UserName=reader.GetString(1);
				us.UserPwd=reader.GetString(2);
				us.UserBir=reader.GetDateTime(3);
				us.UserTel=reader.GetString(4);
				us.UserSex=reader.GetString(5);
				us.UserAge=reader.GetInt32(6);
				us.UserEmail=reader.GetString(7);
				us.UserTrueName=reader.GetString(8);
			}

			return us;
		}
	}
}