db.cs

内容管理 内容管理 内容管理 内容管理 内容管理

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using eInfo.file;  

namespace eInfo.data
{
	/// <summary>
	/// DB 的摘要说明。
	/// </summary>
	public class DB
	{
		
	
		#region 用户管理
		public DataTable  GetUserInfo(string UserId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select name,admin,password from tuser where userid='"+UserId+"'";   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "user");
				return myDS.Tables["user"];
			}
			catch
			{
				return null;
			}
		}
		//检查用户

		public bool checkUserExist(string userid)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select userid from tuser where userid='"+userid+"'";   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "user");
				if((myDS.Tables["user"]!=null)&&(myDS.Tables["user"].Rows.Count!=0))
				{
					return true;
				}
				else return false;
			}
			catch
			{
				return true;
			}
		}

		//添加用户
		public bool InsertUserInfo(string userid,string name,string password,string admin)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd;
			strCmd="insert into tuser(userid,name,admin,password) values(@userid ,@name,@admin,@password )";
			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
			nCommand.Parameters.Add(new SqlParameter("@userid",SqlDbType.VarChar,50)); 
			nCommand.Parameters["@userid"].Value = userid;

			nCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@name"].Value = name;
			

			nCommand.Parameters.Add(new SqlParameter("@admin",SqlDbType.Int,4)); 
			nCommand.Parameters["@admin"].Value = admin;

			nCommand.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@password"].Value = password;

			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
				return true;
			}
			catch
			{
				return false;
			}
		}

		//更新用户信息	
		public bool UpdateUserInfo(string userid,string name,string password,string admin,string olduserid)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);			
			string strCmd="update tuser set userid=@userid,name=@name,admin=@admin,password=@password where userid='"+olduserid+"'";
			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
			nCommand.Parameters.Add(new SqlParameter("@userid",SqlDbType.VarChar,50)); 
			nCommand.Parameters["@userid"].Value = userid;

			nCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@name"].Value = name;			

			nCommand.Parameters.Add(new SqlParameter("@admin",SqlDbType.Int,4)); 
			nCommand.Parameters["@admin"].Value = admin;

			nCommand.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@password"].Value = password;
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
				return true;
			}
			catch(Exception ex)
			{
				return false;
			}
		}

		//删除用户
		public bool DelUserInfo(string userid)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd="delete from tuser where userid='"+userid+"'";			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
		
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch
			{
				return false;
			}
			return true;

		}
		#endregion

		#region 登陆管理
		public String[] Login(String loginid, String password) 
		{    
			String[] CheckLogin;
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Login", myConnection);
			
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterLoginID = new SqlParameter("@loginid", SqlDbType.VarChar,50);
			parameterLoginID.Value = loginid;
			myCommand.Parameters.Add(parameterLoginID);

			SqlParameter parameterPassword = new SqlParameter("@password", SqlDbType.VarChar, 50);
			parameterPassword.Value = password;
			myCommand.Parameters.Add(parameterPassword);

			SqlParameter parameterUSERNAME = new SqlParameter("@name", SqlDbType.VarChar, 100);
			parameterUSERNAME.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterUSERNAME);

			SqlParameter parameterReason = new SqlParameter("@reason", SqlDbType.VarChar,50);
			parameterReason.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterReason);

			SqlParameter parameterADMIN= new SqlParameter("@admin", SqlDbType.Int,4);
			parameterADMIN.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterADMIN);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			string LoginID;
			LoginID=parameterUSERNAME.Value.ToString();
			string LoginADMIN=parameterADMIN.Value.ToString();

			string Result="";
			if(parameterReason.Value.ToString ()=="access")
				Result="成功登录";
			else if(parameterReason.Value.ToString ()=="noUser")
			{
				Result="用户名错误";
				LoginID="usererror";
				
			}
			else if(parameterReason.Value.ToString ()=="pError")
			{
				Result="密码错误";
				LoginID="pwderror";
				
			}
		
			
			CheckLogin=new string []
				  {
					  LoginID,
					  Result,
					  LoginADMIN
				  };
			return CheckLogin;
		}
		#endregion

		#region(模块管理)
		//获取所有菜单节点
		public string getAllNodes()
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select menuId, fatherid ,name from menu  order by fatherid, seq ";
			String result="";
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "menu");
				DataTable myDT=myDS.Tables["menu"];
				for(int i=0;i<myDT.Rows.Count;i++)
				{
					String menuId=myDT.Rows[i][0].ToString();
					String fatherId=myDT.Rows[i][1].ToString();
					String name=myDT.Rows[i][2].ToString();
					result=result+menuId+","+name+","+fatherId+";";
 
				}
				return result ;
			}
			catch(Exception ex)
			{
				string er=ex.Message;
				return result;
			}
		}

　　　　//获取上级菜单名称
		public string  GetFName(string MenuId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select b.name  from menu a,menu b "+
				" where  a.fatherid=b.MenuId  and a.MenuId= "+MenuId;   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "menu");
				return myDS.Tables["menu"].Rows[0][0].ToString() ;
			}
			catch(Exception ex)
			{
				string er=ex.Message;
				return null;
			}
		}

		//获取模块列表名称
		public DataTable  GetModuleInfo(string MenuId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select name,fatherid ,seq,menulink from menu where MenuId="+MenuId;   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "menu");
				return myDS.Tables["menu"];
			}
			catch(Exception ex)
			{
				
				return null;
			}
		}


		//更新菜单
		public bool UpdateModuleInfo(string MenuId,string fatherid,string seq,string name,string menulink)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd;
			strCmd="update menu set name=@name ,fatherid=@fatherid,seq=@seq ,menulink=@menulink"+
				" where MenuId="+MenuId;
			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);

			nCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@name"].Value = name;
				
			nCommand.Parameters.Add(new SqlParameter("@fatherid",SqlDbType.Int,4)); 
			nCommand.Parameters["@fatherid"].Value =Int16.Parse( fatherid);
				
			nCommand.Parameters.Add(new SqlParameter("@seq",SqlDbType.Int,4)); 
			nCommand.Parameters["@seq"].Value = Int16.Parse(seq);

			nCommand.Parameters.Add(new SqlParameter("@menulink",SqlDbType.VarChar,100 )); 
			nCommand.Parameters["@menulink"].Value = menulink;
		
				
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
		}


		//插入菜单
		public bool InSertModuleInfo(string fatherid,string seq,string name,string menulink)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd;
			strCmd="insert menu(name,fatherid,seq,menulink) values(@name ,@fatherid,@seq ,@menulink)";
				
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);

			nCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@name"].Value = name;
				
			nCommand.Parameters.Add(new SqlParameter("@fatherid",SqlDbType.Int,4)); 
			nCommand.Parameters["@fatherid"].Value =Int16.Parse( fatherid);
				
			nCommand.Parameters.Add(new SqlParameter("@seq",SqlDbType.Int,4)); 
			nCommand.Parameters["@seq"].Value = Int16.Parse(seq);

			nCommand.Parameters.Add(new SqlParameter("@menulink",SqlDbType.VarChar,100 )); 
			nCommand.Parameters["@menulink"].Value = menulink;
						
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch(Exception ex)
			{
				return false;
			}
			return true;
		}

		//删除菜单
		public bool DelModuleInfo(string menuId)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd;
			strCmd="delete from  menu where menuId="+menuId;
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
			SqlTransaction myTrans;
			myConnection.Open();		
			myTrans = myConnection.BeginTransaction();
			nCommand.Transaction = myTrans;
			try
			{
				nCommand.ExecuteNonQuery();
					
				strCmd="create table #T(menuid int,fatherID int,Name VARCHAR(100))";
				nCommand.CommandText=strCmd;