WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
dotNet
System_Data

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Get Database Results into a WIL Array and Display

 Keywords:  Database Array Reporview System.Data System.Data.OleDb.OleDbConnection System.Data.OleDb.OleDbCommand System.Data.OleDb.OleDbDataAdapter System.Data.DataTable Microsoft.ACE.OLEDB.12.0 Excel Access Paradox Text DBASE SQL Server SQLServer

;***************************************************************************
;**   Get Database Results into a WIL Array and Display
;**
;** Purpose: Get Database Results into a WIL Array and Display
;** Inputs:  Connection string and SQL query
;** Outputs: Results in an array displayed in Reportview control
;** Reference: 
;**       REQUIRES WinBatch 2013A or newer 
;**
;** Developer: Deana Falk 2013.05.08
;*************************************************************************** 
If Version( )< '2013A' 
   Pause('Notice', 'Need 2013A or Newer Version of WinBatch')
   Exit 
EndIf

#DefineFunction udfQueryDatabase( strConn, strSQLQuery )  
   ;***************************************************************************
   ;**   Query Database
   ;**   strConn        Connection string ( reference http://www.connectionstrings.com )
   ;**   strSQLQuery    SQL Query string
   ;***************************************************************************
   ObjectClrOption("use","System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
   oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection', strConn )
   oCmd = ObjectClrNew( 'System.Data.OleDb.OleDbCommand')
   oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter')
   oTable = ObjectClrNew( 'System.Data.DataTable')

   oConn.Open()
   oCmd.Connection = oConn
   oCmd.CommandText = strSQLQuery
   oAdapter.SelectCommand = oCmd
   rowcount = oAdapter.Fill(oTable)
   colcount = oTable.Columns.Count
   
   ;Read Data into a WIL Array
   arrData = ArrDimension(rowcount,colcount)
   For _row = 0 to rowcount-1
      objRow = oTable.Rows.Item(_row)
      For _col = 0 to colcount-1 
        objColumn = oTable.Columns.Item(_col)
        arrData[_row,_col] = objRow.Item(objColumn)  
      Next 
   Next 
   oConn.Close()
   Return arrData
#EndFunction

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Prompt for input
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
;; Access
strDatabase = "C:\TEMP\Data\Access\Sample.accdb"
strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=' : strDatabase
strTablename = 'Neta'
strSQLQuery = 'SELECT * FROM [' : strTablename :']'

;; Excel XLS
;strDatabase = "C:\TEMP\Data\Excel\Authors.xls"
;strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':strDatabase:';Extended Properties="Excel 8.0;HDR=YES;"'
;strTablename = 'Authors'
;strSQLQuery = 'SELECT * FROM [' : strTablename :']'

;; Excel XLSX
;strDatabase = "C:\TEMP\Data\Excel\Authors.xlsx"
;strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':strDatabase:';Extended Properties="Excel 12.0;HDR=YES;"'
;strTablename = 'Authors'
;strSQLQuery = 'SELECT * FROM [' : strTablename :']'

;; dBASE
;strDatabase = 'C:\TEMP\Data\DBASE\Customer.dbf'
;strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':FilePath(strDatabase):';Extended Properties="DBASE III;"'				
;strSQLQuery = 'Select * From ':FileRoot(strDatabase):'.':FileExtension(strDatabase)
				
;; Paradox
;strDatabase = 'C:\TEMP\Data\PARADOX\PdxFile.db'
;strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':FilePath(strDatabase):';Extended Properties=Paradox 5.x;' 
;strSQLQuery = 'Select * From ':FileRoot(strDatabase):'.':FileExtension(strDatabase)

;; SQL Server 2000
;strConn = 'Provider=SQLOLEDB.1; Network Library=dbmssocn;Password=YOURPWD;User ID=YOURID;Initial Catalog=YOURCATALOG;Data Source=YOURSERVER;'
;strConn = 'Provider=SQLOLEDB.1; Data Source=':serverName:'; Initial Catalog=':dbname:'; Integrated Security=SSPI;' ;Windows authentication instead of a username password
;strSQLQuery = 'SELECT * FROM [dbo].[person]'

;; SQL Server 2005
;Reference: http://www.connectionstrings.com/sql-server-native-client-9-0-oledb-provider/
;strConn = 'Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;'   ; Standard security
;strConn = 'Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;'          ; Trusted connection
;strSQLQuery = 'SELECT * FROM [dbo].[person]'

;; SQL Server 2008
Reference: http://www.connectionstrings.com/sql-server-native-client-10-0-oledb-provider/
;strConn = 'Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;'   ; Standard security
;strConn = 'Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;'          ; Trusted connection
;strSQLQuery = 'SELECT * FROM [dbo].[person]'

;;SQL Server 2012
;Reference: http://www.connectionstrings.com/sql-server-2012 
;strConn = 'Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;' ; Standard security
;strConn = 'Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;'        ; Trusted connection
;strSQLQuery = 'SELECT * FROM [dbo].[person]'

;; Text
;strDatabase = 'C:\TEMP\Data\Customers.txt' 
;strConn = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':FilePath(strDatabase):';Extended Properties="text;HDR=Yes;FMT=Delimited;";'
;strSQLQuery = 'Select * From ':FileRoot(strDatabase):'.':FileExtension(strDatabase)


If !FileExist(strDatabase) Then Terminate(@TRUE,"Cannot Continue",strDatabase:" is missing")

		
arrData = udfQueryDatabase( strConn, strSQLQuery ) 


; Display in WIL Dialogs Reportview Control
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`Database Query Results`
MyDialogX=002
MyDialogY=059
MyDialogWidth=766
MyDialogHeight=353
MyDialogNumControls=003
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0

MyDialog001=`231,333,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
MyDialog002=`499,333,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog003=`001,001,758,322,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,30,2097152,DEFAULT,DEFAULT,"192|192|192"`

ButtonPushed=Dialog("MyDialog")


Exit


Article ID:   W17811

Filename:   Get Database Results into a WIL Array and Display.txt
File Created: 2013:08:29:14:58:16
Last Updated: 2013:08:29:14:58:16