ADO.NET 连接Access2007和Access2003

精选转载

sellm 2013-01-04 00:42:49

文章标签 .NET C# 文章分类 数据库

1、连接access2007

using System;

using System.Data.OleDb;

class AccessTest{
    static void Main(){
        string strConnection = "Provider=MicroSoft.ACE.OLEDB.12.0;";
        strConnection += @"Data Source=D:\study\work\db\ITalkUsers.accdb;Persist Security Info=False";

        OleDbConnection connection = new OleDbConnection(strConnection);
        OleDbCommand cmd = new OleDbCommand("select userid, username, userpasswd from users", connection);
        connection.Open();
        OleDbDataReader cusReader = cmd.ExecuteReader();
        Console.WriteLine("{0} Field.", cusReader.FieldCount);
        while(cusReader.Read()){
            Console.WriteLine(cusReader.GetInt32(0) + "    " + cusReader.GetString(1) + "    " + cusReader.GetString(2));
        }

        cusReader.Close();
        connection.Close();

}

2、连接access2003

ADO.NET连接ACCESS数据库

Imports System.Data.OleDb ——引用命名空间。

1. OleDbConnection 类(连接),所有对数据库的操作都必需先与数据库连接

用法例1:

Dim _connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\mymoney.mdb")

数据库有密码则在连接字符串改为:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mymoney.mdb;Jet OLEDB:Database Password=12345a

用法例2:

Dim _connection As OleDbConnection = New System.Data.OleDb.OleDbConnection

_connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\shidata.mdb;Jet OLEDB:Database Password=12345a"
_connection的其它属性可以使用其默认值

打开连接:

_connection.Open()'打开连接
为了减少错误,可以用下面的方法打开:
If _connection.State = ConnectionState.Closed Then _connection.Open()'如果是关闭则打开
同样关闭连接时也可以做一个判断

2. OleDbCommand 类(操作数据库)
Dim _OleDbCommand As OleDbCommand = New OleDbCommand("SQL语句", 数据连接)
例:

Dim _OleDbCommand As System.Data.OleDb.OleDbCommand

_OleDbCommand = New System.Data.OleDb.OleDbCommand()

_OleDbCommand.CommandText = "SQL语句"

_OleDbCommand.Connection = _connection

_OleDbCommand.ExecuteNonQuery() '执行SQL语句,返回受影响的行数

_OleDbCommand.ExecuteScalar() '执行SQL语句,返回单个值(结果结果集中第一行第一列的值)

_OleDbCommand.ExecuteReader() '执行SQL语句,返回行,可使用其循环方问行集.此方法的更多内容可参看MSDN.

用数据适配器(OleDbDataAdapter)及数据集(DataSet)方法例(个人理财汇总模块):

Module 汇总

Function pd(ByVal kk As String) As String '为空则赋值为0

If kk = "" Then

Return 0

Else

Return kk

End If

End Function

Sub huizong()

Dim OleDbConn As OleDbConnection = New OleDbConnection(My.MySettings.Default.mymoneyConnectionString) 'My.MySettings.Default.mymoneyConnectionString 是在程序配制文件中记录的连接字符串

OleDbConn.Open()

Dim mydataset As DataSet = New DataSet

Dim MyOleDataAdapter As OleDbDataAdapter = New OleDbDataAdapter()

MyOleDataAdapter.SelectCommand = New OleDbCommand("SELECT SUM(支出金额) AS ZONGHE FROM 支出信息表", OleDbConn)

MyOleDataAdapter.Fill(mydataset)

MyOleDataAdapter.SelectCommand = New OleDbCommand("SELECT SUM(收入金额) AS ZONGHEE FROM 收入信息表", OleDbConn)

MyOleDataAdapter.Fill(mydataset)

MyOleDataAdapter.SelectCommand = New OleDbCommand("SELECT SUM(支出金额) AS Expr2 FROM 支出信息表 WHERE (datediff('M', 支出日期, NOW()) = 0)", OleDbConn)

MyOleDataAdapter.Fill(mydataset)

MyOleDataAdapter.SelectCommand = New OleDbCommand("SELECT SUM(收入金额) AS Expr1 FROM 收入信息表 WHERE (datediff('M', 收入日期, NOW()) = 0)", OleDbConn)

MyOleDataAdapter.Fill(mydataset)

zhu_form.Label2.Text = pd(mydataset.Tables(0).Rows(0).Item("ZONGHE").ToString())

zhu_form.Label5.Text = pd(mydataset.Tables(0).Rows(1).Item("ZONGHEE").ToString())

zhu_form.Label9.Text = pd(mydataset.Tables(0).Rows(2).Item("Expr2").ToString())

zhu_form.Label8.Text = pd(mydataset.Tables(0).Rows(3).Item("Expr1").ToString())

zhu_form.Label12.Text = (pd(mydataset.Tables(0).Rows(2).Item("Expr2").ToString()) / Date.Today.Day).ToString(".##") '留两位小数

zhu_form.Label13.Text = (pd(mydataset.Tables(0).Rows(2).Item("Expr2").ToString()) / Date.Today.Day * Date.DaysInMonth(Now.Year, Now.Month)).ToString(".##")

MyOleDataAdapter.Dispose()

OleDbConn.Dispose()

End Sub

}