×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
For what its with here is a sample from my application (this is the Business
Object Layer and Database Access Layer that maintains a 'Titles' table e.g.
Mr, Miss, Mrs... and you should note that it caters for both SQL Server and
DB2/400 (which I call iDB2), its also multi lingual (English, French ...
sorry about that)
OK its VB.NET (to be honest I'll probably migrate to C# one day) but it
covers the basics of database maintenance, no complaints please from others,
this code is provided without any kind of warranty :-) It works and it works
well (specifically because of data caching).
One other nice thing about this approach is that whilst the field names in
the iSeries database might be called TI1DSCD the Database Access Layer
returns a cached data table with friendly names e.g. TitleDescription which
are used in the .NET application (so developers need not care or know what
the actual field names are nor which database they come from).
In a nut shell place an object datasource on your ASP.NET page and point it
to the method GetTitles(). Then attach any databound control e.g. GridView,
DataList, Dropdownlist etc to the object data source and of you go (it's a
start).
It may appear a bit lengthy but once you have created one you can clone this
and create another (together with admin program) in about 10 minutes (we
do).
Hope this helps (I know its not training but it is a working example which
you can have a read through)
''' <summary>
''' Sub - New
''' </summary>
Sub New()
End Sub
''' <summary>
''' Sub - New (Overloaded)
''' </summary>
Sub New( _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
With Me
._TitleCode = TitleCode
._TitleActive = TitleActive
._TitleDescription = TitleDescription
End With
End Sub
''' <summary>
''' Property - TitleCode
''' </summary>
Public Property TitleCode() As String
Get
Return _TitleCode
End Get
Set(ByVal Value As String)
_TitleCode = Value
End Set
End Property
''' <summary>
''' Property - TitleActive
''' </summary>
Public Property TitleActive() As String
Get
Return _TitleActive
End Get
Set(ByVal Value As String)
_TitleActive = Value
End Set
End Property
''' <summary>
''' Property - TitleDescription
''' </summary>
Public Property TitleDescription() As String
Get
Return _TitleDescription
End Get
Set(ByVal Value As String)
_TitleDescription = Value
End Set
End Property
'===========================================================================
========================
'
' BUSINESS LOGIC LAYER METHODS
'
''' <summary>
''' a/. ValidateTitle(mode, db, language, record) returns
errorMessage As String
''' </summary>
Public Shared Function ValidateTitle( _
ByVal mode As String, _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
) As String
'----------------------------------------------------------------
' (Validation) In insert mode the record must not already
exist
'----------------------------------------------------------------
If mode = "Insert" Then
Dim exists As Boolean = TitleExists(db, TitleCode)
If exists Then
Return AppErrorMessages.msgRecordAlreadyExists
End If
End If
'----------------------------------------------------------------
' record OK, return nothing
''' <summary>
''' 1/. GetAllTitles(db, language) As DataTable
''' </summary>
Public Shared Function GetAllTitles(ByVal db As String, ByVal
language As String) As DataTable
Dim dt As New DataTable
dt = GetAllTitlesDb(db, language, Nothing)
Return dt
End Function
''' <summary>
''' 1/. GetAllTitles(db, language, searchString) As DataTable
''' </summary>
Public Shared Function GetAllTitles(ByVal db As String, ByVal
language As String, ByVal SearchString As String) As DataTable
Dim dt As New DataTable
dt = GetAllTitlesDb(db, language, SearchString)
Return dt
End Function
''' <summary>
''' 2/. GetTitles(db) As DataTable (Cached)
''' </summary>
Public Shared Function GetTitles(ByVal db As String, ByVal language
As String) As DataTable
Dim dt As New DataTable
dt = GetTitlesDb(db, language)
Return dt
End Function
''' <summary>
''' 3/. GetTitle(db, language, TitleCode) As Title
''' </summary>
Public Function GetTitle(ByVal db As String, ByVal language As
String, ByVal TitleCode As String) As XMLi5.Title
If TitleCode Is Nothing Then
Return Nothing
End If
Dim Title As New XMLi5.Title
Title = GetTitleDb(db, language, TitleCode)
Return Title
End Function
''' <summary>
''' 21/. CreateTitle(db, language, Record)
''' </summary>
Public Shared Sub CreateTitle( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
' convert code to UPPER before inserting
TitleCode = TitleCode.ToUpper()
CreateTitleDb(db, language, TitleCode, TitleActive,
TitleDescription)
End Sub
''' <summary>
''' 41/. UpdateTitle(db, language, Record)
''' </summary>
Public Shared Sub UpdateTitle( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
UpdateTitleDb(db, language, TitleCode, TitleActive,
TitleDescription)
End Sub
''' <summary>
''' 61/. DeleteAllTitles(db)
''' </summary>
Public Shared Sub DeleteAllTitles(ByVal db As String)
DeleteAllTitlesDb(db)
End Sub
''' <summary>
''' 62/. DeleteTitle(db, language, TitleCode)
''' </summary>
Public Shared Sub DeleteTitle( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String _
)
DeleteTitleDb(db, language, TitleCode)
End Sub
''' <summary>
''' 81/. TitleExists(db, TitleCode) As Boolean
''' </summary>
Public Shared Function TitleExists(ByVal db As String, ByVal
TitleCode As String) As Boolean
Dim IsValid As Boolean
IsValid = TitleExistsDb(db, TitleCode)
Return IsValid
End Function
End Class
End Namespace
========================================
Title Database Access Layer - TitleDB.vb
========================================
''' <summary>
''' 0/. CreateTitleTable() As DataTable
''' </summary>
Private Shared Function CreateTitleTable() As DataTable
'create a DataTable and add columns
Dim dt As DataTable = New DataTable("Titles")
dt.Columns.Add(New DataColumn("TitleCode", GetType(String)))
dt.Columns.Add(New DataColumn("TitleActive", GetType(String)))
dt.Columns.Add(New DataColumn("TitleDescription",
GetType(String)))
Return dt
End Function
''' <summary>
''' 1/. GetAllTitlesDb(db, language, searchString) As DataTable
''' </summary>
Public Shared Function GetAllTitlesDb(ByVal db As String, ByVal
language As String, ByVal SearchString As String) As DataTable
Dim dt As New DataTable
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
dt = GetAllTitlesDbSQL(language, SearchString)
Case "iDB2"
dt = GetAllTitlesDbiDB2(language, SearchString)
End Select
Return dt
End Function
''' <summary>
''' 1a/. GetAllTitlesDbSQL(language, searchString) As DataTable
''' </summary>
Private Shared Function GetAllTitlesDbSQL(ByVal language As String,
ByVal searchString As String) As DataTable
' Create connection
Dim con As SqlConnection = New
SqlConnection(AppSettings.SQLConnectionString)
' Build SQL
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100 ")
.Append(" join WPTIP100D on TI1TIT = TI1TITD ")
.Append(" where TI1LIDD=@TI1LIDD ")
' add search filter if specified
If searchString <> String.Empty Then
.Append(" and TI1DSCD like @searchString")
End If
.Append(" order by TI1DSCD ")
End With
' Create command
Dim cmd As SqlCommand = New SqlCommand(sb.ToString(), con)
' Add parameters
cmd.Parameters.Add(New SqlParameter("@TI1LIDD",
SqlDbType.VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
If searchString <> String.Empty Then
cmd.Parameters.Add(New SqlParameter("@searchString",
SqlDbType.VarChar, 40))
cmd.Parameters("@searchString").Value = searchString.Trim &
"%"
End If
Dim dt As DataTable
Dim dr As DataRow
'create a DataTable
dt = CreateTitleTable()
' Execute Reader
Using con
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
''' <summary>
''' 1b/. GetAllTitlesDbiDB2(language, searchString) As DataTable
''' </summary>
Private Shared Function GetAllTitlesDbiDB2(ByVal language As String,
ByVal searchString As String) As DataTable
' Create connection
Dim con As iDB2Connection = New
iDB2Connection(AppSettings.iDB2ConnectionString)
' Build SQL
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100 ")
.Append(" join WPTIP100D on TI1TIT = TI1TITD ")
.Append(" where TI1LIDD=? ")
' add search filter if specified
If searchString <> String.Empty Then
.Append(" and UPPER(TI1DSCD) like ?")
End If
.Append(" order by TI1DSCD ")
End With
' Create command
Dim cmd As iDB2Command = New iDB2Command(sb.ToString(), con)
' Add parameters
cmd.Parameters.Add(New iDB2Parameter("@TI1LIDD",
iDB2DbType.iDB2VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
If searchString <> String.Empty Then
cmd.Parameters.Add(New iDB2Parameter("@searchString",
iDB2DbType.iDB2VarChar, 40))
cmd.Parameters("@searchString").Value =
searchString.ToUpper.Trim & "%"
End If
Dim dt As DataTable
Dim dr As DataRow
'create a DataTable
dt = CreateTitleTable()
' Execute Reader
Using con
con.Open()
Dim reader As iDB2DataReader = cmd.ExecuteReader()
While reader.Read()
''' <summary>
''' 2/. GetTitlesDb(db) As DataTable (Cached)
''' </summary>
Public Shared Function GetTitlesDb(ByVal db As String, ByVal
language As String) As DataTable
Dim dt As New DataTable
' Get Data from Cache?
Dim cacheName As String = "cacheTitle"
Dim objCache As String = GetCacheObjectLongName(cacheName,
language)
If HttpContext.Current.Cache(objCache) IsNot Nothing Then
Return CType(HttpContext.Current.Cache(objCache), DataTable)
End If
' Else build list
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
dt = GetTitlesDbSQL(language)
Case "iDB2"
dt = GetTitlesDbiDB2(language)
End Select
' Store Data in Cache
Dim timeInCache As Integer = XMLi5.Cache.GetCacheTime(cacheName)
HttpContext.Current.Cache.Insert(objCache, dt, Nothing,
DateTime.Now.AddSeconds(timeInCache), TimeSpan.Zero)
Return dt
End Function
''' <summary>
''' 2a/. GetTitlesDbSQL() As DataTable (Cached)
''' </summary>
Private Shared Function GetTitlesDbSQL(ByVal language As String) As
DataTable
' Create connection
Dim con As SqlConnection = New
SqlConnection(AppSettings.SQLConnectionString)
' Build SQL
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100D ")
.Append(" join WPTIP100 on TI1TITD = TI1TIT ")
.Append(" where TI1LIDD=@TI1LIDD ")
.Append(" and TI1ACT=@TI1ACT ")
.Append(" order by TI1DSCD ")
End With
' Create command
Dim cmd As SqlCommand = New SqlCommand(sb.ToString(), con)
' Add parameters
cmd.Parameters.Add(New SqlParameter("@TI1LIDD",
SqlDbType.VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
''' <summary>
''' 1b/. GetTitlesDbiDB2() As DataTable (Cached)
''' </summary>
Private Shared Function GetTitlesDbiDB2(ByVal language As String) As
DataTable
' Create connection
Dim con As iDB2Connection = New
iDB2Connection(AppSettings.iDB2ConnectionString)
' Build SQL
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100D ")
.Append(" join WPTIP100 on TI1TITD = TI1TIT ")
.Append(" where TI1LIDD=? ")
.Append(" and TI1ACT=? ")
.Append(" order by TI1DSCD ")
End With
' Create command
Dim cmd As iDB2Command = New iDB2Command(sb.ToString(), con)
' Add parameters
cmd.Parameters.Add(New iDB2Parameter("@TI1LIDD",
iDB2DbType.iDB2VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
''' <summary>
''' 3/. GetTitleDb(db, language, TitleCode) As Title
''' </summary>
Public Shared Function GetTitleDb(ByVal db As String, ByVal language
As String, ByVal TitleCode As String) As XMLi5.Title
If TitleCode Is Nothing Then
Return Nothing
End If
Dim Title As New XMLi5.Title
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
Title = GetTitleDbSQL(language, TitleCode)
Case "iDB2"
Title = GetTitleDbiDB2(language, TitleCode)
End Select
Return Title
End Function
''' <summary>
''' 3a/. GetTitleDbSQL(language, TitleCode) As Title
''' </summary>
Private Shared Function GetTitleDbSQL(ByVal language As String,
ByVal TitleCode As String) As XMLi5.Title
Dim Title As XMLi5.Title = Nothing
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100 ")
.Append(" join WPTIP100D on TI1TIT = TI1TITD ")
.Append(" where TI1TIT=@TI1TIT ")
.Append(" and TI1LIDD=@TI1LIDD ")
End With
con = New SqlConnection(AppSettings.SQLConnectionString)
cmd = New SqlCommand(sb.ToString(), con)
cmd.Parameters.Add(New SqlParameter("@TI1LIDD",
SqlDbType.VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
Using con
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Title = New Title( _
reader("TI1TIT"), _
reader("TI1ACT"), _
reader("TI1DSCD") _
)
End While
reader.Close()
End Using
Return Title
End Function
''' <summary>
''' 3b/. GetTitleDbiDB2(language, TI1TIT) As Title
''' </summary>
Private Shared Function GetTitleDbiDB2(ByVal language As String,
ByVal TitleCode As String) As XMLi5.Title
Dim Title As XMLi5.Title = Nothing
Dim con As iDB2Connection
Dim cmd As iDB2Command
Dim sb As New StringBuilder
With sb
.Append(" select TI1TIT, TI1ACT, TI1DSCD ")
.Append(" from WPTIP100 ")
.Append(" join WPTIP100D on TI1TIT = TI1TITD ")
.Append(" where TI1TIT=? ")
.Append(" and TI1LIDD=? ")
End With
con = New iDB2Connection(AppSettings.iDB2ConnectionString)
cmd = New iDB2Command(sb.ToString(), con)
cmd.Parameters.Add(New iDB2Parameter("@TI1LIDD",
iDB2DbType.iDB2VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
Using con
con.Open()
Dim reader As iDB2DataReader = cmd.ExecuteReader()
While reader.Read()
Title = New Title( _
reader("TI1TIT"), _
reader("TI1ACT"), _
reader("TI1DSCD") _
)
End While
reader.Close()
End Using
''' <summary>
''' 21/. CreateTitleDb(db, language, Record)
''' </summary>
Public Shared Sub CreateTitleDb( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
CreateTitleDbSQL(language, TitleCode, TitleActive,
TitleDescription)
Case "iDB2"
CreateTitleDbiDB2(language, TitleCode, TitleActive,
TitleDescription)
End Select
End Sub
''' <summary>
''' 21a/. CreateTitleDbSQL(language, Record)
''' </summary>>
Private Shared Sub CreateTitleDbSQL( _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
'------------------------------------
' INSERT - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("INSERT into WPTIP100 ")
.Append("(")
.Append("TI1TIT,TI1ACT")
.Append(") VALUES (")
.Append("@TI1TIT,@TI1ACT")
.Append(")")
End With
Dim connectionString As String = AppSettings.SQLConnectionString
Using con1 As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb1.ToString(), con1)
con1.Open()
'-------------------------------------------
' INSERT - WPTIP100D (One for each language)
'-------------------------------------------
' loop through each language
Dim sb2 As New StringBuilder
With sb2
.Append("Select LM1LID from WPLMP100 ")
End With
Dim cmd2 As SqlCommand
Dim rdr2 As SqlDataReader
Using con2 As New SqlConnection(connectionString)
con2.Open()
cmd2 = New SqlCommand(sb2.ToString, con2)
rdr2 = cmd2.ExecuteReader()
While rdr2.Read()
Dim sb3 As New StringBuilder
With sb3
.Append("INSERT into WPTIP100D ")
.Append("(")
.Append("TI1TITD,TI1LIDD,TI1DSCD")
.Append(") VALUES (")
.Append("@TI1TITD,@TI1LIDD,@TI1DSCD")
.Append(")")
End With
Using con3 As New SqlConnection(connectionString)
Dim cmd3 As New SqlCommand(sb3.ToString(), con3)
con3.Open()
rdr2.Close()
rdr2 = Nothing
con2.Close()
End Using
End Sub
''' <summary>
''' 21b/. CreateTitleDbiDB2(language, Record)
''' </summary>
Private Shared Sub CreateTitleDbiDB2( _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
'------------------------------------
' INSERT - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("INSERT into WPTIP100 ")
.Append("(")
.Append("TI1TIT,TI1ACT")
.Append(") VALUES (")
.Append("?,?")
.Append(")")
End With
Dim connectionString As String =
AppSettings.iDB2ConnectionString
Using con1 As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb1.ToString(), con1)
con1.Open()
'-------------------------------------------
' INSERT - WPTIP100D (One for each language)
'-------------------------------------------
' loop through each language
Dim sb2 As New StringBuilder
With sb2
.Append("Select LM1LID from WPLMP100 ")
End With
Dim cmd2 As iDB2Command
Dim rdr2 As iDB2DataReader
Using con2 As New iDB2Connection(connectionString)
con2.Open()
cmd2 = New iDB2Command(sb2.ToString, con2)
rdr2 = cmd2.ExecuteReader()
While rdr2.Read()
Dim sb3 As New StringBuilder
With sb3
.Append("INSERT into WPTIP100D ")
.Append("(")
.Append("TI1TITD,TI1LIDD,TI1DSCD")
.Append(") VALUES (")
.Append("?,?,?")
.Append(")")
End With
Using con3 As New iDB2Connection(connectionString)
Dim cmd3 As New iDB2Command(sb3.ToString(), con3)
con3.Open()
''' <summary>
''' 41/. UpdateTitleDb(db, language, Record)
''' </summary>
Public Shared Sub UpdateTitleDb( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
UpdateTitleDbSQL(language, TitleCode, TitleActive,
TitleDescription)
Case "iDB2"
UpdateTitleDbiDB2(language, TitleCode, TitleActive,
TitleDescription)
End Select
End Sub
''' <summary>
''' 41a/. UpdateTitleDbSQL(language, Record)
''' </summary>
Private Shared Sub UpdateTitleDbSQL( _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
'------------------------------------
' UPDATE - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("UPDATE WPTIP100 Set ")
.Append("TI1ACT = @TI1ACT ")
.Append("where TI1TIT = @TI1TIT ")
End With
Dim connectionString As String = AppSettings.SQLConnectionString
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb1.ToString(), con)
con.Open()
'------------------------------------
' UPDATE - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("UPDATE WPTIP100D Set ")
.Append("TI1DSCD = @TI1DSCD ")
.Append("where TI1TITD = @TI1TIT ")
.Append("and TI1LIDD = @TI1LIDD ")
End With
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb2.ToString(), con)
con.Open()
cmd.Parameters.Add(New SqlParameter("@TI1LIDD",
SqlDbType.VarChar, 3))
cmd.Parameters("@TI1LIDD").Value = language
cmd.ExecuteNonQuery()
con.Close()
End Using
End Sub
''' <summary>
''' 41b/. UpdateTitleDbiDB2(language, Record)
''' </summary>
Private Shared Sub UpdateTitleDbiDB2( _
ByVal language As String, _
ByVal TitleCode As String, _
ByVal TitleActive As String, _
ByVal TitleDescription As String _
)
'------------------------------------
' UPDATE - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("UPDATE WPTIP100 Set ")
.Append("TI1ACT = ? ")
.Append("where TI1TIT = ? ")
End With
Dim connectionString As String =
AppSettings.iDB2ConnectionString
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb1.ToString(), con)
con.Open()
'------------------------------------
' UPDATE - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("UPDATE WPTIP100D Set ")
.Append("TI1DSCD = ? ")
.Append("where TI1TITD = ? ")
.Append("and TI1LIDD = ? ")
End With
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb2.ToString(), con)
con.Open()
''' <summary>
''' 61/. DeleteAllTitlesDb(db)
''' </summary>
Public Shared Sub DeleteAllTitlesDb(ByVal db As String)
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
DeleteAllTitlesDbSQL()
Case "iDB2"
DeleteAllTitlesDbiDB2()
End Select
End Sub
'------------------------------------
' DELETE ALL - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("Delete from WPTIP100 ")
End With
Dim connectionString As String = AppSettings.SQLConnectionString
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb1.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
'------------------------------------
' DELETE ALL - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("delete from WPTIP100D ")
End With
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb2.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
'------------------------------------
' DELETE ALL - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("Delete from WPTIP100 ")
End With
Dim connectionString As String =
AppSettings.iDB2ConnectionString
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb1.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
'------------------------------------
' DELETE - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("delete from WPTIP100D ")
End With
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb2.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Sub
''' <summary>
''' 62/. DeleteTitleDb(db, language, TitleCode)
''' </summary>
Public Shared Sub DeleteTitleDb( _
ByVal db As String, _
ByVal language As String, _
ByVal TitleCode As String _
)
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
DeleteTitleDbSQL(language, TitleCode)
Case "iDB2"
DeleteTitleDbiDB2(language, TitleCode)
End Select
End Sub
''' <summary>
''' 62a/. DeleteTitleDbSQL(language, TitleCode)
''' </summary>
Private Shared Sub DeleteTitleDbSQL( _
ByVal language As String, _
ByVal TitleCode As String _
)
'------------------------------------
' DELETE - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("Delete from WPTIP100 ")
.Append("where TI1TIT = @TI1TIT ")
End With
Dim connectionString As String = AppSettings.SQLConnectionString
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb1.ToString(), con)
con.Open()
'------------------------------------
' DELETE - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("delete from WPTIP100D ")
.Append("where TI1TITD = @TI1TIT ")
End With
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sb2.ToString(), con)
con.Open()
''' <summary>
''' 62b/. DeleteTitleDbiDB2(language, TitleCode)
''' </summary>
Private Shared Sub DeleteTitleDbiDB2( _
ByVal language As String, _
ByVal TitleCode As String _
)
'------------------------------------
' DELETE - WPTIP100
'------------------------------------
Dim sb1 As New StringBuilder
With sb1
.Append("Delete from WPTIP100 ")
.Append("where TI1TIT = ? ")
End With
Dim connectionString As String =
AppSettings.iDB2ConnectionString
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb1.ToString(), con)
con.Open()
'------------------------------------
' DELETE - WPTIP100D
'------------------------------------
Dim sb2 As New StringBuilder
With sb2
.Append("delete from WPTIP100D ")
.Append("where TI1TITD = ? ")
End With
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command(sb2.ToString(), con)
con.Open()
''' <summary>
''' 81/. TitleExistsDb(db, TitleCode) As Boolean
''' </summary>
Public Shared Function TitleExistsDb(ByVal db As String, ByVal
TitleCode As String) As Boolean
Dim IsValid As Boolean
db = XMLi5.Database.GetDb(db, "TitleDB")
Select Case db
Case "SQL"
IsValid = TitleExistsDbSQL(TitleCode)
Case "iDB2"
IsValid = TitleExistsDbiDB2(TitleCode)
End Select
Return IsValid
End Function
''' <summary>
''' 81a/. TitleExistsDbSQL(TitleCode) As Boolean
''' </summary>
Private Shared Function TitleExistsDbSQL(ByVal TitleCode As String)
As Boolean
Dim IsValid As Boolean = False
Dim count As Integer = 0
Try
Dim connectionString As String =
AppSettings.SQLConnectionString
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("Select count(TI1TIT) from
WPTIP100 where TI1TIT = @TI1TIT", con)
cmd.Parameters.Add(New SqlParameter("@TI1TIT",
SqlDbType.VarChar, 10))
cmd.Parameters("@TI1TIT").Value = TitleCode
con.Open()
count = cmd.ExecuteScalar()
End Using
Catch Ex As Exception
WriteLog("Error - IsValidTitleSQL", Ex)
Finally
If count = 0 Then
IsValid = False
Else
IsValid = True
End If
End Try
Return IsValid
End Function
''' <summary>
''' 81b/. TitleExistsDbiDB2(TitleCode) As Boolean
''' </summary>
Private Shared Function TitleExistsDbiDB2(ByVal TitleCode As String)
As Boolean
Dim IsValid As Boolean = False
Dim count As Integer = 0
Try
Dim connectionString As String =
AppSettings.iDB2ConnectionString
Using con As New iDB2Connection(connectionString)
Dim cmd As New iDB2Command("Select count(TI1TIT) from
WPTIP100 where TI1TIT = ?", con)
cmd.Parameters.Add(New iDB2Parameter("@TI1TIT",
iDB2DbType.iDB2VarChar, 10))
cmd.Parameters("@TI1TIT").Value = TitleCode
con.Open()
count = cmd.ExecuteScalar()
End Using
Catch Ex As Exception
WriteLog("Error - IsValidTitleiDB2", Ex)
Finally
If count = 0 Then
IsValid = False
Else
IsValid = True
End If
End Try
Return IsValid
End Function
End Class
End Namespace
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx
[mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Sent: 03 September 2008 19:08
To: .net use with the System i
Subject: [SystemiDotNet] Any Good Training?
Boy this list get quiet quick! Here is a good question, is there any
training that you would recommend for someone looking at doing more with
.NET? Does IBM have any good trainings?
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
