【DB】ODBCで接続してデータベースを操作する方法(検索、削除、更新)
VB.NET
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.Odbc
''' <summary>
''' SQLコネクション
''' </summary>
Private _con As OdbcConnection = Nothing
''' <summary>
''' トランザクション・オブジェクト
''' </summary>
''' <remarks></remarks>
Private _trn As OdbcTransaction = Nothing
''' <summary>
''' DB接続
''' </summary>
''' <param name="dsn">データソース名</param>
''' <param name="dbn">データベース名</param>
''' <param name="uid">ユーザーID</param>
''' <param name="pas">パスワード</param>
''' <param name="tot">タイムアウト値</param>
''' <remarks></remarks>
Public Sub Connect(dsn As [String], dbn As [String], uid As [String], pas As [String], tot As Integer)
Try
If _con Is Nothing Then
_con = New OdbcConnection()
End If
Dim cst As [String] = ""
cst = cst & ";DSN=" & dsn
cst = cst & ";Database=" & dbn
cst = cst & ";UID=" & uid
cst = cst & ";PWD=" & pas
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(sql As [String], tot As Integer) As DataTable
Dim dt As New DataTable()
Try
Dim sqlCommand As New OdbcCommand(sql, _con, _trn)
If tot > -1 Then
sqlCommand.CommandTimeout = tot
End If
Dim adapter As New OdbcDataAdapter(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 IsNot Nothing 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 IsNot Nothing 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()
Try
Disconnect()
Finally
MyBase.Finalize()
End Try
End Sub
C#
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;
/// <summary>
/// SQLコネクション
/// </summary>
private OdbcConnection _con = null;
/// <summary>
/// トランザクション・オブジェクト
/// </summary>
/// <remarks></remarks>
private OdbcTransaction _trn = null;
/// <summary>
/// DB接続
/// </summary>
/// <param name="dsn">データソース名</param>
/// <param name="dbn">データベース名</param>
/// <param name="uid">ユーザーID</param>
/// <param name="pas">パスワード</param>
/// <param name="tot">タイムアウト値</param>
/// <remarks></remarks>
public void Connect(
String dsn, String dbn, String uid, String pas, int tot)
{
try
{
if (_con == null)
{
_con = new OdbcConnection();
}
String cst = "";
cst = cst + ";DSN=" + dsn;
cst = cst + ";Database=" + dbn;
cst = cst + ";UID=" + uid;
cst = cst + ";PWD=" + pas;
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
{
OdbcCommand sqlCommand = new OdbcCommand(sql, _con, _trn);
if (tot > -1)
{
sqlCommand.CommandTimeout = tot;
}
OdbcDataAdapter adapter = new OdbcDataAdapter(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>
~OdbcDbIf()
{
Disconnect();
}
