c#连接Access(含有插入日期类型)

 

1. using System;
2. using System.Collections.Generic;
3. using System.Text;
4. using System.Data;
5. using System.Data.OleDb; 
7. namespace We_Are_AA_CSharp_ACCESS_
8. {
9.     class BuyThingTable
10.     {
12.         public string mydataSource = "WeAreAA.mdb";
14.         public string connstr = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=WeAreAA.mdb";
16.         //获得购物表
17.         public DataSet getbuyThingTable()
18.         {
19.             DataSet ds = new DataSet();
20.             string cmdStr = "select id,buyUserName,ThingName,moneyTotal,buyDate from buyThingTable order by buyDate desc";
21.             OleDbConnection conn = new OleDbConnection(connstr);
23.             OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdStr, conn);
24.             dataAdapter.Fill(ds, "buyThingTable");
25.             return ds;
26.         }
28.         //获得购物表by日期
29.         public DataSet getbuyThingTableByDate(　DateTime fromDate)
30.         {
31.             DataSet ds = new DataSet();
32.             string cmdStr = "select id,buyUserName,ThingName,moneyTotal,buyDate from buyThingTable where buyDate >= #" + fromDate + "#  order by buyDate desc";
33.             OleDbConnection conn = new OleDbConnection(connstr);
35.             OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdStr, conn);
36.             dataAdapter.Fill(ds, "buyThingTable");
37.             return ds;
38.         }
40.         //人员分组获得本人代付钱数表
41.         public DataSet　getBuyThingTableByDateGroup(DateTime fromDate)
42.         {
43.             DataSet ds = new DataSet();
44.             string cmdStr =  "select  buyUserName,sum(moneyTotal) from buyThingTable 
45.                                          where buyDate >= #" + fromDate + "# group by buyUserName "
46.             OleDbConnection conn = new OleDbConnection(connstr);
48.             OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdStr, conn);
49.             dataAdapter.Fill(ds, "buyThingTable");
50.             return ds;
51.         }
54.         //获得id,以便删除buyThingTable中某记录时,同时删除shareUserTable中的相应项
55.         public Int32 getbuyThingTableId(string buyUserName, string thingName, float moneyTotal, DateTime buyDate)
56.         {
57.             DataSet ds = new DataSet();
58.             string cmdStr = "select id from buyThingTable where buyUserName='" + buyUserName + "'and thingName='" + thingName + "' and moneyTotal=" + moneyTotal + "  and buyDate = #" + buyDate + "#";
59.             OleDbConnection conn = new OleDbConnection(connstr);
61.             OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdStr, conn);
62.             dataAdapter.Fill(ds, "theId");
64.             DataTable dt = ds.Tables["theId"];
65.            
66.            return Int32.Parse(dt.Rows[0].ItemArray[0].ToString());
67.       
68.         }
71.         //删除购物表记录by"ID"
72.         public bool delRecordById(Int32  id)
73.         {
74.             string cmdstr = "delete from buyThingTable where [id] = " + id + "";
76.             using (OleDbConnection conn = new OleDbConnection(connstr))
77.             {
78.                 OleDbCommand ocmd = new OleDbCommand(cmdstr, conn);
79.    
80.                     conn.Open();
81.                     ocmd.ExecuteNonQuery();
82.                     conn.Close();
83.                     return true;
85.             }
86.         }
88.        public void AddbuyThingTable(string buyUserName, string thingName, float  moneyTotal, DateTime buyDate)
89.         {
90.             string cmdstr = "insert into buyThingTable(buyUserName, thingName, moneyTotal, buyDate) values('" + buyUserName + "','" + thingName + "'," + moneyTotal + ",#" + buyDate + "#)";
92.             
93.             using (OleDbConnection conn = new OleDbConnection(connstr))
94.             {
95.                 OleDbCommand ocmd = new OleDbCommand(cmdstr, conn);
96.                 conn.Open();
97.                 ocmd.ExecuteNonQuery();
98.                 conn.Close(); 
99.             }
100.         }
102.     }
103. }