commmonuse.cs

事务提醒软件。自己写的事务提醒软件

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MyNote
{
    public class CommmonUse
    {
        private string strconn;
        private OleDbConnection myConn;
        private OleDbCommand myCmd;
        private OleDbDataAdapter myAdatapter;

        public CommmonUse()
        {
            strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\NoteLib.mdb;Persist Security Info=True";
            myConn = new OleDbConnection(strconn);
            myCmd = new OleDbCommand("", myConn);
            myAdatapter = new OleDbDataAdapter(myCmd);
        }

        public Person[] getAllPersons()
        {
            DataTable pDT = new DataTable();
            myCmd.CommandText = "select * from Persons order by pid";
            myAdatapter.Fill(pDT);
            Person[] p = new Person[pDT.Rows.Count];
            int i;
            for (i = 0; i < pDT.Rows.Count; i++)
            {
                p[i] = new Person((int)pDT.Rows[i]["pid"], (string)pDT.Rows[i]["pname"],(string)pDT.Rows[i]["pass"]);
            }
            
            pDT.Dispose();
            return p;
        }

        public int getMaxEventID()
        {
            int r;
            DataTable pDT = new DataTable();
            myCmd.CommandText = "select max(ntIndex) from Notes";
            myAdatapter.Fill(pDT);
            if (pDT.Rows.Count > 0)
            {
                r = (int)pDT.Rows[0][0];
                pDT.Dispose();
                return r;
            }
            pDT.Dispose();
            return 0;
        }

        public Person getPersonByID(int ID)
        {
            DataTable pDT = new DataTable();
            myCmd.CommandText = "select * from Persons where pID=" + ID.ToString();
            myAdatapter.Fill(pDT);
            int i;
          
            for (i = 0; i < pDT.Rows.Count; i++)
            {
                if ((int)pDT.Rows[i]["pID"] == ID)
                {
                    Person p = new Person(ID, (string)pDT.Rows[i]["pName"],(string)pDT.Rows[i]["pass"]);
                    return p;
                }
            }
            return null;
        }

        public Person getPersonByName(string Name)
        {
            DataTable pDT = new DataTable();
            myCmd.CommandText = "select * from Persons where pName=" + Name;
            myAdatapter.Fill(pDT);
            int i;
            if (pDT.Rows.Count > 0)
            {
                Person p = new Person((int)pDT.Rows[0]["pID"], (string)pDT.Rows[0]["pName"], (string)pDT.Rows[0]["pass"], (string)pDT.Rows[0]["nickname"]);
                return p;
            }
            return null;
        }

        public MyEvent[] getEventByPersonAfterDate(Person p, DateTime fixDateTime)
        {
            DataTable dt = new DataTable();
            //MessageBox.Show(fixDateTime.ToShortDateString());
            myCmd.CommandText = "Select Notes.ntIndex,Notes.Content,Notes.nTime from Notes,Relation where (Notes.ntIndex=Relation.ntIndex) and (Relation.pId=" + p.mIndex.ToString() + ") and (Notes.nTime>=#" + fixDateTime.ToShortDateString() + "#) order by Notes.nTime,Notes.ntIndex";
            myAdatapter.Fill(dt);
            MyEvent[] e = new MyEvent[dt.Rows.Count];
            int i;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                e[i] = new MyEvent((int)dt.Rows[i][0], (string)dt.Rows[i][1], Convert.ToDateTime(dt.Rows[i][2].ToString()));
            }
            dt.Dispose();
            return e;
        }

        public MyEvent[] getAllEvent()
        {
            DataTable nDT = new DataTable();
            myCmd.CommandText = "select * from notes order by nTime";
            myAdatapter.Fill(nDT);
            MyEvent[] e = new MyEvent[nDT.Rows.Count];
            int i;
            for (i = 0; i < nDT.Rows.Count; i++)
            {
                e[i] = new MyEvent((int)nDT.Rows[i]["ntIndex"], (string)nDT.Rows[i]["Content"], Convert.ToDateTime(nDT.Rows[i]["nTime"].ToString()));
            }
            nDT.Dispose();
            return e;
        }

        public Person[] getPersonsByMyEvent(MyEvent e)
        {
            DataTable dt = new DataTable();
            myCmd.CommandText = "Select Persons.Pid,Persons.Pname,Persons.Pass from Persons,Relation where (Persons.Pid=Relation.Pid) and (Relation.ntIndex=" + e.mIndex.ToString() + ") order by Persons.Pid";
            myAdatapter.Fill(dt);
            Person[] p = new Person[dt.Rows.Count];
            int i;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                p[i] = new Person((int)dt.Rows[i][0], (string)dt.Rows[i][1],(string)dt.Rows[i][2]);
            }
            dt.Dispose();
            return p;
        }
        public MyEvent[] getMyEventsByPerson(Person p)
        {
            DataTable dt = new DataTable();
            myCmd.CommandText = "Select Notes.ntIndex,Notes.Content,Notes.nTime,Relation.State from Notes,Relation where (Notes.ntIndex=Relation.ntIndex) and (Relation.pId=" + p.mIndex.ToString() + ") order by Notes.nTime,Notes.ntIndex";
            myAdatapter.Fill(dt);
            MyEvent[] e = new MyEvent[dt.Rows.Count];
            int i;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                e[i] = new MyEvent((int)dt.Rows[i][0], (string)dt.Rows[i][1], Convert.ToDateTime(dt.Rows[i][2].ToString()), Convert.ToBoolean(dt.Rows[i][3].ToString()));
            }
            dt.Dispose();
            return e;
        }

        public void InsertMyNotesToDB(MyNotes mMyNotes)
        {
            int i;
            myConn.Open();
            for (i = 0; i < myEvents.Length; i++)
            {
                myCmd.CommandText = "insert into notes (ntIndex,Content,nTime,Pid) values (" + mMyNotes.mEvents[i].mIndex.ToString() + ",'" + myEvents[i].mContent + @"',#" + myEvents[i].mDatetime.Date.ToShortDateString() + @"#," + person.mIndex.ToString() + ")";
                myCmd.ExecuteNonQuery();
                myCmd.CommandText = "insert into relation(ntIndex,pID,state) values(" + mMyNotes.mEvents[i].mIndex.ToString() + "," + mMyNotes.mPerson.mIndex.ToString() + "," + mMyNotes.mEvents[i].mState.ToString() + ")";
                myCmd.ExecuteNonQuery();
            }
            myConn.Close();
        }

        //public void DeleteAllMyEvents()
        //{
        //    myConn.Open();
        //    myCmd.CommandText = "delete from notes";
        //    myCmd.ExecuteNonQuery();
        //    myCmd.CommandText = "delete from relation";
        //    myCmd.ExecuteNonQuery();
        //    myConn.Close();
        //}

        public void DeleteMyEvents(MyNotes mMyNotes)
        {
            int i;
            myConn.Open();
            for (i = 0; i < mMyNotes.mEvents.Length; i++)
            {
                myCmd.CommandText = "delete from relation where (pID=" + person.mIndex.ToString() + ") and (ntIndex=" + mMyNotes.mEvents[i].mIndex.ToString() + ")";
                myCmd.ExecuteNonQuery();
            }          
            myConn.Close();
        }


    }
}