|
Imports SQLclass = System.Data.SqlClient
Imports VBClass = Microsoft.VisualBasic
Imports System.Windows.Forms
Module 共通モジュール
Public SQL_Cnn As New SQLclass.SqlConnection(My.MySettings.Default.SSIS_TESTConnectionString)
'Public SQL_TRAN As SQLclass.SqlTransaction = SQL_Cnn.BeginTransaction
Public SQL_Cmd As SQLclass.SqlCommand = SQL_Cnn.CreateCommand()
Public Dr As SQLclass.SqlDataReader
Class UsrERR
'エラーハンドリング system.Exception をフックして加工した結果を上位へを返す
Inherits Exception
Public Sub New(ByVal msg As String, ByVal InnerException As Exception)
MyBase.New(msg, InnerException)
End Sub
End Class
Function GetSqlQuery(ByVal QueryStr As String) As DataTable
'指定されたＳＱＬクエリーを実行し、結果をDatatableへ返す
Dim ErMsg As String = ""
Dim I As Integer
Dim dt As New DataTable
GetSqlQuery = New DataTable
Try
SQL_Cnn.Open()
SQL_Cmd.CommandText = QueryStr
Dr = SQL_Cmd.ExecuteReader
'dt = Dr.GetSchemaTable
For I = 0 To Dr.VisibleFieldCount - 1
dt.Columns.Add(Dr.GetName(I)) '項目名を作成する。
Next I
Dim WROW As DataRow
Do While Dr.Read
WROW = dt.NewRow
For I = 0 To Dr.VisibleFieldCount - 1
WROW.Item(Dr.GetName(I)) = Dr.Item(I) '1行分のデータを取得する
Next I
WROW.EndEdit()
dt.Rows.Add(WROW) 'Datatableへ行を追加する。
Loop
GetSqlQuery = dt
Catch except As System.Exception
ErMsg = "GetSqlQuery関数にてSQL(" & QueryStr & ")の実行に失敗しました。"
Throw New UsrERR(ErMsg, except)
Finally
If Not (Dr Is Nothing) Then
Dr.Close()
End If
SQL_Cnn.Close()
End Try
End Function
End Module
| 
