AccessアクセスMDBに接続して操作する方法
VB.NET
'Imports System.Data.OleDb
''' <summary>
''' SQLコネクション
''' </summary>
Private _con As OleDbConnection = Nothing
''' <summary>
''' トランザクション・オブジェクト
''' </summary>
''' <remarks></remarks>
Private _trn As OleDbTransaction = Nothing
''' <summary>
''' DB接続
''' </summary>
''' <param name="dsn">データソース名</param>
''' <param name="tot">タイムアウト値</param>
''' <remarks></remarks>
Public Sub Connect(Optional ByVal dsn As String = "db1.mdb", _
Optional ByVal tot As Integer = -1)
Try
If _con Is Nothing Then
_con = New OleDbConnection
End If
Dim cst As String = ""
cst = cst & "Provider=Microsoft.Jet.OLEDB.4.0"
cst = cst & ";Data Source=" & dsn
' データベースパスワードが設定されている場合
' cst = cst & ";Jet OLEDB:Database Password=xxxxx"
If tot > -1 Then
'_con.ConnectionTimeout = tot
cst = cst & ";Connect Timeout=" & tot.ToString
End If
_con.ConnectionString = cst
_con.Open()
Catch ex As Exception
Throw New Exception("Connect Error", ex)
End Try
End Sub
''' <summary>
''' DB切断
''' </summary>
Public Sub Disconnect()
Try
_con.Close()
Catch ex As Exception
Throw New Exception("Disconnect Error", ex)
End Try
End Sub
''' <summary>
''' SQLの実行
''' </summary>
''' <param name="sql">SQL文</param>
''' <param name="tot">タイムアウト値</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ExecuteSql(ByVal sql As String, _
Optional ByVal tot As Integer = -1) As DataTable
Dim dt As New DataTable
Try
Dim sqlCommand As New OleDbCommand(sql, _con, _trn)
If tot > -1 Then
sqlCommand.CommandTimeout = tot
End If
Dim adapter As New OleDbDataAdapter(sqlCommand)
adapter.Fill(dt)
adapter.Dispose()
sqlCommand.Dispose()
Catch ex As Exception
Throw New Exception("ExecuteSql Error", ex)
End Try
Return dt
End Function
''' <summary>
''' トランザクション開始
''' </summary>
''' <remarks></remarks>
Public Sub BeginTransaction()
Try
_trn = _con.BeginTransaction()
Catch ex As Exception
Throw New Exception("BeginTransaction Error", ex)
End Try
End Sub
''' <summary>
''' コミット
''' </summary>
''' <remarks></remarks>
Public Sub CommitTransaction()
Try
If _trn Is Nothing = False Then
_trn.Commit()
End If
Catch ex As Exception
Throw New Exception("CommitTransaction Error", ex)
Finally
_trn = Nothing
End Try
End Sub
''' <summary>
''' ロールバック
''' </summary>
''' <remarks></remarks>
Public Sub RollbackTransaction()
Try
If _trn Is Nothing = False Then
_trn.Rollback()
End If
Catch ex As Exception
Throw New Exception("RollbackTransaction Error", ex)
Finally
_trn = Nothing
End Try
End Sub
''' <summary>
''' 解除
''' </summary>
''' <remarks></remarks>
Protected Overrides Sub Finalize()
Disconnect()
MyBase.Finalize()
End Sub
基礎からわかる C#
C#
//using System; //using System.Collections.Generic; //using System.Text; //using System.Data; //using System.Data.OleDb;
/// <summary>
/// SQLコネクション
/// </summary>
private OleDbConnection _con = null;
/// <summary>
/// トランザクション・オブジェクト
/// </summary>
/// <remarks></remarks>
private OleDbTransaction _trn = null;
/// <summary>
/// DB接続
/// </summary>
/// <param name="dsn">データソース名</param>
/// <param name="tot">タイムアウト値</param>
/// <remarks></remarks>
public void Connect(String dsn, int tot)
{
try
{
if (_con == null)
{
_con = new OleDbConnection();
}
String cst = "";
cst = cst + "Provider=Microsoft.Jet.OLEDB.4.0";
cst = cst + ";Data Source=" + dsn;
// データベースパスワードが設定されている場合
// cst = cst + ";Jet OLEDB:Database Password=xxxxx";
if (tot > -1)
{
//_con.ConnectionTimeout = tot;
cst = cst + ";Connect Timeout=" + tot.ToString();
}
_con.ConnectionString = cst;
_con.Open();
}
catch (Exception ex)
{
throw new Exception("Connect Error", ex);
}
}
/// <summary>
/// DB切断
/// </summary>
public void Disconnect()
{
try
{
_con.Close();
}
catch (Exception ex)
{
throw new Exception("Disconnect Error", ex);
}
}
/// <summary>
/// SQLの実行
/// </summary>
/// <param name="sql">SQL文</param>
/// <param name="tot">タイムアウト値</param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable ExecuteSql(String sql, int tot)
{
DataTable dt = new DataTable();
try
{
OleDbCommand sqlCommand = new OleDbCommand(sql, _con, _trn);
if (tot > -1)
{
sqlCommand.CommandTimeout = tot;
}
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlCommand);
adapter.Fill(dt);
adapter.Dispose();
sqlCommand.Dispose();
}
catch (Exception ex)
{
throw new Exception("ExecuteSql Error", ex);
}
return dt;
}
/// <summary>
/// トランザクション開始
/// </summary>
/// <remarks></remarks>
public void BeginTransaction()
{
try
{
_trn = _con.BeginTransaction();
}
catch (Exception ex)
{
throw new Exception("BeginTransaction Error", ex);
}
}
/// <summary>
/// コミット
/// </summary>
/// <remarks></remarks>
public void CommitTransaction()
{
try
{
if (_trn != null)
{
_trn.Commit();
}
}
catch (Exception ex)
{
throw new Exception("CommitTransaction Error", ex);
}
finally
{
_trn = null;
}
}
/// <summary>
/// ロールバック
/// </summary>
/// <remarks></remarks>
public void RollbackTransaction()
{
try
{
if (_trn != null)
{
_trn.Rollback();
}
}
catch (Exception ex)
{
throw new Exception("RollbackTransaction Error", ex);
}
finally
{
_trn = null;
}
}
/// <summary>
/// 接続解除
/// </summary>
/// <remarks></remarks>
~AccessMdb()
{
Disconnect();
}
基礎からわかる C#
