SqlParameter paraRoleID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			paraRoleID.Value = roleid;
			myCommand.Parameters.Add(paraRoleID);

			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		public void AddUserRole(int EmpId,int RoleId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysAddEmpRole", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterEmpID = new SqlParameter("@empid", SqlDbType.Int, 4);
			parameterEmpID.Value = EmpId;
			myCommand.Parameters.Add(parameterEmpID);
			SqlParameter parameterRoleID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			parameterRoleID.Value = RoleId;
			myCommand.Parameters.Add(parameterRoleID);
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
		}

		public void AddFuncRole(int funcid, int roleid) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysAddFuncRole", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterFuncID = new SqlParameter("@funcid", SqlDbType.Int, 4);
			parameterFuncID.Value = funcid;
			myCommand.Parameters.Add(parameterFuncID);
			SqlParameter parameterRoleID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			parameterRoleID.Value = roleid;
			myCommand.Parameters.Add(parameterRoleID);

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

		public bool DeleteRoleUser(int EmpId,int RoleId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysDeleteRoleUser", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterEmpId = new SqlParameter("@empid", SqlDbType.Int, 4);
			parameterEmpId.Value = EmpId;
			myCommand.Parameters.Add(parameterEmpId);
			SqlParameter parameterRoleID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			parameterRoleID.Value = RoleId;
			myCommand.Parameters.Add(parameterRoleID);

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

		public bool DeleteFuncRole(int FuncID,int RoleId) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysDeleteFuncRole", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter parameterFuncID = new SqlParameter("@funcid", SqlDbType.Int, 4);
			parameterFuncID.Value = FuncID;
			myCommand.Parameters.Add(parameterFuncID);
			SqlParameter parameterRoleID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			parameterRoleID.Value = RoleId;
			myCommand.Parameters.Add(parameterRoleID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch
			{
					return false;
			}
			return true;
		}
       
		public SqlDataReader GetEmpInfo(int pkid ,String type) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetEmpByType", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter paraID = new SqlParameter("@pkid", SqlDbType.Int, 4);
			paraID.Value = pkid;
			myCommand.Parameters.Add( paraID);
			SqlParameter paraType = new SqlParameter("@type", SqlDbType.VarChar,10);
			paraType.Value = type;
			myCommand.Parameters.Add( paraType);
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public bool DeleteFuncRoleUser(int pkid,String type) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysDeleteFuncRoleUser", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter paraID = new SqlParameter("@pkid", SqlDbType.Int, 4);
			paraID.Value = pkid;
			myCommand.Parameters.Add(paraID);
			SqlParameter paraType = new SqlParameter("@type", SqlDbType.VarChar,10);
			paraType.Value = type;
			myCommand.Parameters.Add( paraType);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch
			{
				return false;
			}
			return true;
		}
  
		public SqlDataReader GetFuncInfo(int funcid) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetFuncInfo", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter paraID = new SqlParameter("@funcid", SqlDbType.Int, 4);
			paraID.Value = funcid;
			myCommand.Parameters.Add( paraID);
			
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public SqlDataReader GetRoleInfo(int roleid) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetRoleInfo", myConnection);

			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter paraID = new SqlParameter("@roleid", SqlDbType.Int, 4);
			paraID.Value = roleid;
			myCommand.Parameters.Add( paraID);
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public SqlDataReader GetAllFuncs() 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetAllFuncs", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public SqlDataReader GetAllRoles() 
		{   
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetAllRoles", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public SqlDataReader GetAllBranch() 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetAllBranch", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public SqlDataReader GetDepByBranch(int BranchID) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sysGetDepByBranch", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			SqlParameter paraBranchID = new SqlParameter("@branchid", SqlDbType.Int, 4);
			 paraBranchID.Value = BranchID;
			myCommand.Parameters.Add( paraBranchID);
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}
	}
}