dbfun.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using DataFieldClass;
using System.Collections;

namespace MsgSystem
{
	/// <summary>
	/// Class1 的摘要说明。
	/// </summary>
	public class DbFun
	{
       
		public DataSet myModDataSet = new DataSet ();
		public DataSet myGroupDataSet = new DataSet ();
		public DataSet myStaffDataSet = new DataSet ();
		public DataSet myMsgDataSet = new DataSet ();
		public DataSet mySysDataSet = new DataSet ();


		private static SqlConnection getNewConn()//获得一个新的数据库连接
		{
			//string strCon = "Provider = SQLOLEDB ; Persist Security Info = False ; User ID = userID ; password = Password; Initial Catalog = DataBase ; Data Source = localhost " ;
			
            SqlConnection myConn = MsgSystem.Program.conns.getConnection( ) ;
			return myConn;//调用佟浩的接口
		}

        private static void freeConn(SqlConnection myconn)
		{
            MsgSystem.Program.conns.releaseConnection(myconn);

			return;//releaseConnection(myconn);//调用佟浩的接口
		}



        public static ArrayList getModsInfo( )
		{
            ArrayList rtn = new ArrayList();
            ModInfo mod = null;

			SqlConnection myConn = getNewConn();

            string sqlString = " SELECT * FROM Tab_Mod";
            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();


            while (sqlReader.Read())
            {
                mod = new ModInfo();
                mod.setName( sqlReader["name"].ToString().Trim());
                mod.setID( Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                mod.setDesc(  sqlReader["desc"].ToString().Trim());
                rtn.Add(mod);

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

        public static ArrayList getGroups(int ModId)
		{
            ArrayList rtn = new ArrayList();
            GroupInfo group = null;

			SqlConnection myConn = getNewConn();

            string sqlString = " SELECT * FROM Tab_Group where mod_ID =" + ModId + "";

            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();

            while (sqlReader.Read())
            {
                group = new GroupInfo();
                group.setID( Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                group.setName( sqlReader["name"].ToString().Trim());
                group.setMod( Convert.ToInt32(sqlReader["mod_id"].ToString().Trim()));
                group.setHeading( sqlReader["heading"].ToString().Trim());
                group.setDesc( sqlReader["desc"].ToString().Trim());
                rtn.Add(group);


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

			freeConn(myConn);
            return rtn;
		}


        public static ArrayList getStaff()
	    {
            ArrayList rtn = new ArrayList();
            PersonInfo person = null;

		    SqlConnection myConn = getNewConn();
		    string sqlString = " SELECT * FROM Tab_Staff" ;


            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();
            while (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 static ArrayList getMsg( )
        {
            ArrayList rtn = new ArrayList();
            MsgInfo info = null;

            SqlConnection myConn = getNewConn();

            string sqlString = " SELECT * FROM Tab_Msg";


            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();

            while (sqlReader.Read())
            {
                info = new MsgInfo();
                info.setID(Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                info.setDate(Convert.ToDateTime(sqlReader["date"].ToString().Trim()));
                info.setPhone(sqlReader["phone"].ToString().Trim());
                info.setSender(sqlReader["sender"].ToString().Trim());
                info.setMod(Convert.ToInt32(sqlReader["mod"].ToString().Trim()));
                info.setGroup(Convert.ToInt32(sqlReader["group"].ToString().Trim()));
                info.setContent(sqlReader["content"].ToString().Trim());
                rtn.Add(info);

            }

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

        public static ArrayList getSysMsg( DateTime from, DateTime to)
        {
            ArrayList rtn = new ArrayList();
            SysInfo sysInfo = null;

            SqlConnection myConn = getNewConn();

            string sqlString = " SELECT * FROM Tab_SysMsg WHERE dateTime BETWEEN '" + from + "' AND '" + to + "'";


            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();

            while ( sqlReader.Read())
            {
                sysInfo = new SysInfo();
                sysInfo.setID( Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                sysInfo.setDate(Convert.ToDateTime(sqlReader["dateTime"].ToString().Trim()));
                sysInfo.setDesc( sqlReader["desc"].ToString().Trim());
                rtn.Add( sysInfo);

            }

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



        public static ArrayList getGroupMsg( DateTime from, DateTime to, int groupId)
        {
            ArrayList rtn = new ArrayList();
            MsgInfo info = null;

            SqlConnection myConn = getNewConn();

            string sqlString = " SELECT * FROM Tab_Msg WHERE [group] = " + groupId + " AND dateTime BETWEEN '" + from + "' AND '" + to + "'";


            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();

            while (sqlReader.Read())
            {
                info = new MsgInfo();
                info.setID(Convert.ToInt32(sqlReader["id"].ToString().Trim()));
                info.setDate(Convert.ToDateTime(sqlReader["dateTime"].ToString().Trim()));
                info.setPhone(sqlReader["phone"].ToString().Trim());
                info.setSender(sqlReader["sender"].ToString().Trim());
                info.setMod(Convert.ToInt32(sqlReader["mod"].ToString().Trim()));
                info.setGroup(Convert.ToInt32(sqlReader["group"].ToString().Trim()));
                info.setContent(sqlReader["content"].ToString().Trim());
                rtn.Add(info);

            }

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


	/*public void getStaff(int modID)
		{
			SqlConnection myConn = getNewConn();

			string strCom = " SELECT * FROM staff where 1" ;//嵌套查询。。。。。。
		

			SqlDataAdapter myCommand = new SqlDataAdapter ( strCom , myConn ) ;
		
			myCommand.Fill ( myGroupDataSet , " staff " ) ;	
	
			myConn.Close ( ) ;

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

			freeConn(myConn);
		}
*/
        public static ArrayList getStaff(int group_id)
		{
            ArrayList rtn = new ArrayList();
            PersonInfo person = null;

			SqlConnection myConn = getNewConn();
			string sqlString = " SELECT * FROM Tab_Staff where group_id = " + group_id +"" ;


            SqlCommand sqlCMD = myConn.CreateCommand();
            sqlCMD.CommandText = sqlString;
            SqlDataReader sqlReader = sqlCMD.ExecuteReader();
            while (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());