VB.NET
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
''' <summary>
''' SQLコネクション
''' </summary>
Private _con As SqlConnection = Nothing
''' <summary>
''' トランザクション・オブジェクト
''' </summary>
''' <remarks></remarks>
Private _trn As SqlTransaction = Nothing
''' <summary>
''' DB接続
''' </summary>
''' <param name="svr">サーバー名／IP</param>
''' <param name="dbn">データベース名</param>
''' <param name="uid">ユーザーID</param>
''' <param name="pas">パスワード</param>
''' <param name="tot">タイムアウト値</param>
''' <remarks></remarks>
Public Sub Connect(svr As [String], dbn As [String], uid As [String], pas As [String], tot As Integer)
Try
If _con Is Nothing Then
_con = New SqlConnection()
End If
Dim cst As [String] = ""
cst = cst & "Server=" & Convert.ToString(svr)
cst = cst & ";Database=" & Convert.ToString(dbn)
cst = cst & ";User ID=" & Convert.ToString(uid)
cst = cst & ";Password=" & Convert.ToString(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 SqlCommand(sql, _con, _trn)
If tot > -1 Then
sqlCommand.CommandTimeout = tot
End If
Dim adapter As New SqlDataAdapter(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.SqlClient;
/// <summary>
/// SQLコネクション
/// </summary>
private SqlConnection _con = null;
/// <summary>
/// トランザクション・オブジェクト
/// </summary>
/// <remarks></remarks>
private SqlTransaction _trn = null;
/// <summary>
/// DB接続
/// </summary>
/// <param name="svr">サーバー名／IP</param>
/// <param name="dbn">データベース名</param>
/// <param name="uid">ユーザーID</param>
/// <param name="pas">パスワード</param>
/// <param name="tot">タイムアウト値</param>
/// <remarks></remarks>
public void Connect(
String svr, String dbn, String uid, String pas, int tot)
{
try
{
if (_con == null)
{
_con = new SqlConnection();
}
String cst = "";
cst = cst + "Server=" + svr;
cst = cst + ";Database=" + dbn;
cst = cst + ";User ID=" + uid;
cst = cst + ";Password=" + 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
{
SqlCommand sqlCommand = new SqlCommand(sql, _con, _trn);
if (tot > -1)
{
sqlCommand.CommandTimeout = tot;
}
SqlDataAdapter adapter = new SqlDataAdapter(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>
~SqlDbIf()
{
Disconnect();
}
