db.cs

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

				nCommand.ExecuteNonQuery();

				strCmd="insert into #T exec sp_getChild  'menu','menuID','fatherid','Name','"+menuId+"'";
				nCommand.CommandText=strCmd;
				nCommand.ExecuteNonQuery();

				strCmd="delete from menu where menuid in (select menuID from #T)"; 
				nCommand.CommandText=strCmd;
				nCommand.ExecuteNonQuery();

				strCmd="drop table #T";
				nCommand.CommandText=strCmd;
				nCommand.ExecuteNonQuery();

				myTrans.Commit();
				myConnection.Close();
			}
			catch(Exception ex)
			{
				string error=ex.Message;
				myTrans.Rollback();
				return false;
			}
			finally 
			{
				myConnection.Close();
			}
			return true;
		}

		#endregion

		#region 资料类型管理
		public DataTable  GetInfoTypeInfo(string Id) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select id,name from InfoType where id="+Id;   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "InfoType");
				return myDS.Tables["InfoType"];
			}
			catch
			{
				return null;
			}
		}
		//添加
		public bool InsertInfoTypeInfo(string name)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd;
			strCmd="insert into InfoType(name) values(@name)";
			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
			
			nCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100)); 
			nCommand.Parameters["@name"].Value = name;
			
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
				return true;
			}
			catch
			{
				return false;
			}
		}

		//更新	
		public bool UpdateInfoTypeInfo(string name,string id)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);			
			string strCmd="update InfoType set name=@name where id="+id;
			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);

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

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

		}
		#endregion

		#region(资料管理) 
		public bool DelInfo(string id)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strCmd="delete from Info where id="+id;			
			SqlCommand nCommand = new SqlCommand(strCmd, myConnection);
		
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch
			{
				return false;
			}
			return true;

		}

		public DataTable  GetInfo(string Id) 
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string strQuery="select a.name,b.name,a.des,dbo.return_ny(a.filedate) as filedate,a.filesize,a.content,a.filename, "+
				" a.filedate as date from info a,infotype b  where a.type=b.id and a.id="+Id;   
			
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "type");
				return myDS.Tables["type"];
			}
			catch
			{
				return null;
			}
		}
		public bool UpdateInfo(string name,string des,string type,string date,string id)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);			
			string strCmd="update Info set name=@name,des=@des,type=@type ,filedate=@filedate where id="+id;
			
			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("@des",SqlDbType.VarChar,500)); 
			nCommand.Parameters["@des"].Value = name;			
		
			nCommand.Parameters.Add(new SqlParameter("@type",SqlDbType.Int,4)); 
			nCommand.Parameters["@type"].Value = name;			
		
			nCommand.Parameters.Add(new SqlParameter("@filedate",SqlDbType.DateTime)); 
			nCommand.Parameters["@filedate"].Value = date;		
			try
			{
				myConnection.Open();
				nCommand.ExecuteNonQuery();
				myConnection.Close();
				return true;
			}
			catch(Exception ex)
			{
				
				return false;
			}
		}
	
		#endregion

		#region 数据备份与恢复
		//备份
		public bool DBback(string path,string DBname)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string backupSql = "use master;";
			backupSql += "backup database @dbname to disk = @path;";
			SqlCommand myCommand = new SqlCommand(backupSql, myConnection);
			myCommand.Parameters.Add("@dbname", SqlDbType.Char);
			myCommand.Parameters["@dbname"].Value = DBname;
			myCommand.Parameters.Add("@path", SqlDbType.Char);
			myCommand.Parameters["@path"].Value = path;

			try
			{
				myCommand.Connection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();				
			}
			catch(Exception ex)
			{
				myConnection.Close();
				return false;
			}
			return true;
		}
		public bool DBRestore(string path,string DBname)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			string restoreSql = "use master;";
			restoreSql += "restore database @dbname from disk = @path;";
			SqlCommand myCommand = new SqlCommand(restoreSql, myConnection);
			myCommand.Parameters.Add("@dbname", SqlDbType.Char);
			myCommand.Parameters["@dbname"].Value = DBname;
			myCommand.Parameters.Add("@path", SqlDbType.Char);
			myCommand.Parameters["@path"].Value = path;

			try
			{
				myCommand.Connection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();				
			}
			catch(Exception ex)
			{
				myConnection.Close();
				return false;
			}
			return true;
		}
		
		
		#endregion


		#region 菜单
		public DataTable getFirstMenu(string admin)
		{
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			
			string strQuery;
			if(admin=="0")
			{
				strQuery="select menuid,name,menulink from menu where fatherid=(select menuid from menu where fatherid=-1) order by seq";   
			}
			else
			{
				strQuery="select menuid,name,menulink from menu where fatherid=(select menuid from menu where fatherid=-1) and name!='系统管理' order by seq";   
			}
			try
			{ 
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "menu");
				return myDS.Tables["menu"];
			}
			catch
			{
				return null;
			}
		}

		public DataTable getChild(string fatherid)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("sp_getChild", myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
				
			SqlParameter parameterMenu = new SqlParameter("@TName", SqlDbType.NVarChar, 40);
			parameterMenu.Value = "menu";
			myCommand.Parameters.Add(parameterMenu);
		
			SqlParameter parameterMenuID = new SqlParameter("@IDName", SqlDbType.NVarChar, 40);
			parameterMenuID.Value = "menuID";
			myCommand.Parameters.Add(parameterMenuID);

			SqlParameter parameterFatherid = new SqlParameter("@PIDName", SqlDbType.NVarChar, 40);
			parameterFatherid.Value = "fatherid";
			myCommand.Parameters.Add(parameterFatherid);
			
			SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 40);
			parameterName.Value = "Name";
			myCommand.Parameters.Add(parameterName);
				
			SqlParameter parameterId = new SqlParameter("@ID", SqlDbType.NVarChar, 20);
			parameterId.Value =fatherid;
			myCommand.Parameters.Add(parameterId);
			DataTable myDataTable;
			try
			{
					
				SqlDataAdapter myDA = new SqlDataAdapter(myCommand);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "menu");
				myDataTable=myDS.Tables["menu"];
					
					
				myConnection.Close();
			}
			catch(Exception ex)
			{
				string error=ex.Message;
				return null;
			}
			finally 
			{
				myConnection.Close();
			}
			return myDataTable;
			

		}
		#endregion


		public DataView SearchDV(string strQuery)
		{
			try
			{ 
				SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "11");
				return myDS.Tables[0].DefaultView;
			}
			catch(Exception ex)
			{
				string error=ex.Message;
				return null;
			}

		}
		public DataTable SearchDT(string strQuery)
		{
			try
			{ 
				SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
				SqlDataAdapter myDA = new SqlDataAdapter(strQuery, myConnection);
				DataSet myDS = new DataSet();
				myDA.Fill(myDS, "11");
				return myDS.Tables[0];
			}
			catch(Exception ex)
			{
				string error=ex.Message;
				return null;
			}

		}

	
	}
}