dbfun.cs

一个农村管理系统的程序 数据库那部分可能不全 大家可以

                person.setSex(  sqlReader["sex"].ToString().Trim());
                rtn.Add(person);

            }
			//textBox1.DataBindings.Add ( "Text" , myDataSet , "" ) ; 绑定数据
            sqlReader.Close();
            sqlCMD.Dispose();
			freeConn(myConn);
            return rtn;

		}

        public static ArrayList getPerson(int userID)
		{
            ArrayList rtn = new ArrayList();
            PersonInfo person = null;

			SqlConnection myConn = getNewConn();

			string sqlString = " SELECT * FROM Tab_Staff where id = " + userID +"" ;

            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();
            if ( sqlReader.Read())
            {
                person = new PersonInfo();
                person.setID( Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                person.setName( sqlReader["name"].ToString().Trim());
                person.setAddress(  sqlReader["address"].ToString().Trim());
                person.setAge(  Convert.ToInt32(sqlReader["age"].ToString().Trim()));
                person.setDesc(  sqlReader["desc"].ToString().Trim());
                person.setGroup(  Convert.ToInt32(sqlReader["group_id"].ToString().Trim()));
                person.setPhone(  sqlReader["phone"].ToString().Trim());
                person.setPost(  sqlReader["post"].ToString().Trim());
                person.setSex(  sqlReader["sex"].ToString().Trim());
                rtn.Add(person);

            }

			//textBox1.DataBindings.Add ( "Text" , myDataSet , "" ) ; 绑定数据
            sqlReader.Close();
            sqlCMD.Dispose();
			freeConn(myConn);
            return rtn;

		}
	
		/*public void getInfos()//参数两个日期，需要重新写
		{
			SqlConnection myConn = getNewConn();

			string strCom = " SELECT * FROM Tab_Info where id = " + userID +"" ;
		

			SqlDataAdapter myCommand = new SqlDataAdapter ( strCom , myConn ) ;
		
			myCommand.Fill ( myInfoDataSet , " Info " ) ;	
	
			myConn.Close ( ) ;

			//textBox1.DataBindings.Add ( "Text" , myDataSet , "" ) ; 绑定数据

			freeConn(myConn);
		}
		
		
		
		public void getSysInfos()//参数两个日期，需要重新写
		{
			SqlConnection myConn = getNewConn();

			string strCom = " SELECT * FROM sysinfo where id = " + userID +"" ;
		

			SqlDataAdapter myCommand = new SqlDataAdapter ( strCom , myConn ) ;
		
			myCommand.Fill ( mySysDataSet , " Sys " ) ;	
	
			myConn.Close ( ) ;

			//textBox1.DataBindings.Add ( "Text" , myDataSet , "" ) ; 绑定数据

			freeConn(myConn);
		}*/

        public static void insertMod(ModInfo modinfo)
		{
			SqlConnection myConn = getNewConn();

			string strInsert = " INSERT INTO Tab_Mod ( id , name , [desc] ) VALUES ( " ;
			strInsert += modinfo.getID() + ", '" ;
			strInsert += modinfo.getName() + "', '" ;
			strInsert += modinfo.getDesc() + ")" ;

			SqlCommand sqlCMD = new SqlCommand ( strInsert , myConn ) ;
			sqlCMD.ExecuteNonQuery ( ) ;

            sqlCMD.Dispose();
			freeConn(myConn);
		}


        protected static void updateMod(ModInfo modinfo)
		{
				SqlConnection myConn = getNewConn();
	

				string strUpdt = " UPDATE Tab_Mod SET name = '"
					+ modinfo.getName() + "' , description = '"
					+ modinfo.getDesc() + " WHERE id = " + modinfo.getID(); 
				SqlCommand sqlCMD = new SqlCommand ( strUpdt , myConn ) ;
				sqlCMD.ExecuteNonQuery ( ) ;

                sqlCMD.Dispose();
				freeConn(myConn);
		}

        public static int insertPerson(PersonInfo per)
		{
             int rtn = 0;

			SqlConnection myConn = getNewConn();

			string strInsert = " INSERT INTO Tab_Staff ( id , name , group_id , phone, age, sex, address, post, [desc]) VALUES ( " ;
			strInsert += Util.getId( ) + ", '" ;
			strInsert += per.getName() + "', " ;
			strInsert += per.getGroup() + ", '" ;
			strInsert += per.getPhone() + "', "  ;
			strInsert += per.getAge() + ", '" ;
			strInsert += per.getSex() + "', '" ;
			strInsert += per.getAddress() + "', '" ;
			strInsert += per.getPost() + "', '" ;
			strInsert += per.getDesc() + "')" ;

            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = strInsert;
            rtn = sqlCMD.ExecuteNonQuery();

            sqlCMD.Dispose();
			freeConn(myConn);
            return rtn;
		}


        protected static void updatePerson(PersonInfo per)
		{
				SqlConnection myConn = getNewConn();
	

				string strUpdt = " UPDATE Tab_Staff SET name = '"
					+ per.getName() + "' , group_id = '"
					+ per.getPhone() + "' , phone = '"
					+ per.getPhone() + "' , age = "
					+ per.getAge() + " , sex = "
					+ per.getSex() + "' , address = '"
					+ per.getAddress() + "' , post = '"
					+ per.getPost() + " WHERE id = " + per.getID();
				SqlCommand sqlCMD = new SqlCommand ( strUpdt , myConn ) ;
				sqlCMD.ExecuteNonQuery ( ) ;

                sqlCMD.Dispose();
				freeConn(myConn);
		}

        //这个有问题
        public static void insertGroup(GroupInfo g)
		{
				SqlConnection myConn = getNewConn();
	
				string strInsert = " INSERT INTO Tab_Group ( id , name , groupID , phone, age, sex, address, post ) VALUES ( " ;
				strInsert += g.getID() + ", '" ;
				strInsert += g.getName() + "', '" ;
				strInsert += g.getMod() + ", '" ;
				strInsert += g.getHeading() + ", '" ;
				strInsert += g.getDesc() + ")" ;

				SqlCommand inst = new SqlCommand ( strInsert , myConn ) ;
				inst.ExecuteNonQuery ( ) ;
				freeConn(myConn);
		}


        protected static void updateGroup(GroupInfo g)
		{
				SqlConnection myConn = getNewConn();
	

				string strUpdt = " UPDATE Tab_Group SET name = '"
					+ g.getName() + "' , mod_id = '"
					+ g.getMod() + "' , heading = '"
					+ g.getHeading() + " WHERE id = " + g.getID() ;
				SqlCommand sqlCMD = new SqlCommand ( strUpdt , myConn ) ;
				sqlCMD.ExecuteNonQuery ( ) ;
                sqlCMD.Dispose();
                freeConn(myConn);			
		}

        public static int insertMsg(MsgInfo info)
		{
            int rtn = 0;

			SqlConnection myConn = getNewConn();

			string strInsert = " INSERT INTO Tab_Msg ( id , dateTime , phone , sender, mod, [group], [content] ) VALUES ( " ;
			strInsert += Util.getId( ) + ", '" ;
			strInsert += info.getDate() + "', '" ;
			strInsert += info.getPhone() + "', '" ;
			strInsert += info.getSender() + "', " ;
			strInsert += info.getMod() + ", '" ;
			strInsert += info.getGroup() + "', '" ;
			strInsert += info.getContent() + "')" ;

            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = strInsert;
            rtn = sqlCMD.ExecuteNonQuery();


            sqlCMD.Dispose();
			freeConn(myConn);
            return rtn;
		}


        public static void insertSysMsg(SysInfo s)
		{
			SqlConnection myConn = getNewConn();

			string strInsert = " INSERT INTO Tab_SysMsg ( id , dateTime , [desc] ) VALUES ( " ;
			strInsert += Util.getId() + ", '" ;
			strInsert += s.getDate() + "', '" ;
			strInsert += s.getDesc() + "')" ;

			SqlCommand sqlCMD = new SqlCommand ( strInsert , myConn ) ;
			sqlCMD.ExecuteNonQuery ( ) ;

            sqlCMD.Dispose();
			freeConn(myConn);
		}

        public static void insertSysMsg(String msg)
        {
            SqlConnection myConn = getNewConn();

            string strInsert = " INSERT INTO Tab_SysMsg ( id , dateTime , [desc] ) VALUES ( ";
            strInsert += Util.getId( ) + ", '";
            strInsert += System.DateTime.Now + "', '";
            strInsert += msg + "')";

            SqlCommand sqlCMD = new SqlCommand(strInsert, myConn);
            sqlCMD.ExecuteNonQuery();


            sqlCMD.Dispose();
            freeConn(myConn);
        }

        public static int deleteRecord(string tableName, int id)
		{
            int rtn = 0;
			SqlConnection myConn = getNewConn();

			string strInsert = " DELETE FROM " + tableName +" WHERE id = " + id + "" ;

            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = strInsert;
            rtn = sqlCMD.ExecuteNonQuery();

			//mySysDataSet.Tables [ tableName ] . AcceptChanges ( ) ;

            sqlCMD.Dispose();
			freeConn(myConn);

            return rtn;
		}


	//
	// TODO: 在此处添加构造函数逻辑
	//textBox1.DataBindings.Add ( "Text" , myDataSet , "" ) ; 绑定数据

	}
}