C# 操作 access 数据库的实例代码

1
private staticstring connStr = @"Provider= Microsoft.Ace.OLEDB.12.0;Data Source = d:login.accdb";

1
2
privatestatic stringconnStr =@"Provider = Microsoft.Jet.OLEDB.4.0;DataSource = d:login.mdb";
          2007版本的Access数据库链接字符串：privatestaticstring connStr =@"Provider= Microsoft.Ace.OLEDB.12.0;Data Source = d:login.accdb";

1
OleDbConnection tempconn =new OleDbConnection(connStr);

1
2
3
OleDbCommand cmd = new OleDbCommand(sql, tempconn); 
      tempconn.Open();
      cmd.ExecuteNonQuery();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;
namespace test
{
  class AccessHelper
  {
    private static string connStr = @"Provider = Microsoft.Ace.OLEDB.12.0;Data Source = d:login.accdb";
    public static OleDbConnection GetConn()
    {
      OleDbConnection tempconn = new OleDbConnection(connStr);
      MessageBox.Show(tempconn.DataSource);
      tempconn.Open();
      MessageBox.Show(tempconn.State.ToString());
      return (tempconn);
    }
    /// <summary>
    /// 执行增加、删除、修改指令
    /// </summary>
    /// <param name="sql">增加、删除、修改的sql语句</param>
    /// <param name="param">sql语句的参数</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sql, params OleDbParameter[] param)
    {
      using (OleDbConnection conn = new OleDbConnection(connStr))
      {
        using (OleDbCommand cmd = new OleDbCommand(sql,conn))
        {
          if (param != null)
          {
            cmd.Parameters.AddRange(param);
          }
          conn.Open();
          return(cmd.ExecuteNonQuery());
        }
      }
    }
    /// <summary>
    /// 执行查询指令，获取返回的首行首列的值
    /// </summary>
    /// <param name="sql">查询sql语句</param>
    /// <param name="param">sql语句的参数</param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql, params OleDbParameter[] param)
    {
      using (OleDbConnection conn = new OleDbConnection(connStr))
      {
        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
        {
          if (param != null)
          {
            cmd.Parameters.AddRange(param);
          }
          conn.Open();
          return (cmd.ExecuteScalar());
        }
      }
    }
    /// <summary>
    /// 执行查询指令，获取返回的datareader
    /// </summary>
    /// <param name="sql">查询sql语句</param>
    /// <param name="param">sql语句的参数</param>
    /// <returns></returns>
    public static OleDbDataReader ExecuteReader(string sql, params OleDbParameter[] param)
    {
      OleDbConnection conn = new OleDbConnection(connStr);
      OleDbCommand cmd = conn.CreateCommand();
      cmd.CommandText = sql;
      cmd.CommandType = CommandType.Text;
      if (param != null)
      {
        cmd.Parameters.AddRange(param);
      }
      conn.Open();
      return (cmd.ExecuteReader(CommandBehavior.CloseConnection));
    }
    /// <summary>
    /// 执行查询指令，获取返回datatable
    /// </summary>
    /// <param name="sql">查询sql语句</param>
    /// <param name="param">sql语句的参数</param>
    /// <returns></returns>
    public static DataTable ExecuteDatable(string sql, params OleDbParameter[] param)
    {
      using (OleDbConnection conn = new OleDbConnection(connStr))
      {
        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
        {
          if (param != null)
          {
            cmd.Parameters.AddRange(param);
          }
          DataTable dt = new DataTable();
          OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
          sda.Fill(dt);
          return (dt);
        }
      }
    }
  }
}