Introduction
NumericBox Control: This is one of the many custom controls in VB from my tool box that I intend to share with you. It’s actually a text box control that receives only numeric keys from the keyboard and only numeric data from the clipboard.
Well there are many numeric controls that you can find out there, but this particular one has some special features such as controlling the numeric data type you enter, denying the pasting of data that can’t convert to a specified data type and of course it’s in VB. You can as well set visual styles, data formats, minimum and maximum values etc.
Members of interest of NumericBox include the following:-
Properties
This property sets or gets the text message that will be part of error message if control has invalid entry. E.g. if this value is set as Age and MaxValue property of the control is set to 100 and MinValue property set to 0, if you enter a value such as 200, you get a message that reads “Age must be between 0 and 100” a typical value for ControlCaption include the label text besides this control.
This property sets or gets the data format of controls’ value possible values include General, Standard, Currency and Percent. DataFormat is only applicable if DataType is set to Single, Double or Decimal.
This property sets or gets the data type of controls’ value. Possible values include Short (Int16), Integer (Int32), Long (Int64), Single, Double and Decimal. The exact data type is returned by method Get[DataType].
This property sets or gets the number of decimal places for the entered value. This property is only applicable to data types such as Single, Double, Decimal and to specific Data formats.
Some times the user may want to work with none numeric values such as percentages and/or null values. Thus MustEnterNumeric property allows you to work with such values.
This property Mimics the Text property of a textbox. However, it will not allow you to enter non-numeric.
Note
1. Text property is not visible in the properties window.
2. Value Property returns a string value. It’s made so in order to support null values and others such as percentages otherwise, you must use Get[DataType] method to receive the exact data type
This property sets or gets the Visual style of the control possible values include None, Standard, OfficeXP, Office2003. VisualStyle is not yet at the level I’d want it to be, I will be releasing an enhanced version later.
This property sets or gets the Minimum value allowed in control. Applicable only if MaxValue is set greater than MinValue
This property sets or gets the Maximum value allowed in control. Applicable only if MaxValue is set greater than MinValue
Methods
This method returns the controls’ value, and as an object data type, however this value will successfully convert to the data type specified in the DataType property and, if MustEnterNumeric is set to false, this method will return that value as string.
The following methods will return the numeric control’s value as datatype as is in the method’s name
- GetShort()
- GetInteger()
- GetLong()
- GetSingle()
- GetDouble()
- GetDecimal()
Note
Remember to always wrap these methods in the try catch block. They throw exceptions if the entered data can’t be converted to a specified data type.
Other Feature(s) Include
- Denial of pasting of value that can’t convert to the specified data type
- Allowing data only from numeric keys
etc
You can download the source code below
(Usage example in both VB and C# is included)
Using List(of T) Generic in your data layer
List(of T) is basically a better ArrayList. It is optimized for speed, size, and power. Use it for majority of internal implementations whenever you need to store items in a container.
This example shows you how you can use a List(of T) generic in Visual Basic to Save data into the database wrapped in a transaction in your Data access Layer
Explanation
- Create a base object say DBConnect that other object to save, update, delete dataetc. to the database will derive from
- Create a SaveAll method that allow an object of type DBConnect to save data in an optional transactional manner
- Create an object such as Clients that derives from DBConnect
- Define a list of Clients objects and call SaveAll to process them as a transaction
The code behind the form is shown below
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Public Class frmClients
#Region " Fields "
Private clients As New DataTable()
#End Region
Private Sub frmClients_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With clients.Columns
.Add("ClientID", GetType(String))
.Add("FirstName", GetType(String))
.Add("LastName", GetType(String))
End With
With clients.Rows
.Add(New Object() {"001", "Wilson", "Kutegeka"})
.Add(New Object() {"002", "John", "Magezi"})
.Add(New Object() {"003", "Peter", "Okello"})
.Add(New Object() {"004", "Jane", "Miranda"})
End With
Me.dgvClients.DataSource = clients
End Sub
Private Sub btnSaveAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAll.Click
Dim records As Integer
Dim oClients As New Clients()
Dim lClients As New List(Of Clients)
Try
Me.Cursor = Cursors.WaitCursor
For pos As Integer = 0 To clients.Rows.Count - 1
oClients = New Clients()
With clients.Rows(pos)
If IsDBNull(.Item("ClientID")) Then
oClients.ClientID = String.Empty
Else : oClients.ClientID = CStr(.Item("ClientID"))
End If
If IsDBNull(.Item("FirstName")) Then
oClients.FirstName = String.Empty
Else : oClients.FirstName = CStr(.Item("FirstName"))
End If
If IsDBNull(.Item("LastName")) Then
oClients.LastName = String.Empty
Else : oClients.LastName = CStr(.Item("LastName"))
End If
End With
lClients.Add(oClients)
Next
records = SaveAll(lClients, True)
MessageBox.Show(records & " records inserted!")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
End Class
The code below wil be in your Data Access Layer
#Region " Inside your DAL "
Public MustInherit Class DBConnect
#Region " Fields "
#End Region
#Region " Properties "
#End Region
#Region " Methods "
Public Overridable Function SaveCommand() As SqlCommand
Return Nothing
End Function
Public Overridable Function UpdateCommand() As SqlCommand
Return Nothing
End Function
Public Overridable Function DeleteCommand() As SqlCommand
Return Nothing
End Function
#End Region
End Class
Public Class Clients : Inherits DBConnect
#Region " Fields "
Private m_ClientID As String
Private m_FirstName As String
Private m_LastName As String
#End Region
#Region " Properties "
Public Property ClientID() As String
Get
Return m_ClientID
End Get
Set(ByVal Value As String)
m_ClientID = Value
End Set
End Property
Public Property FirstName() As String
Get
Return m_FirstName
End Get
Set(ByVal Value As String)
m_FirstName = Value
End Set
End Property
Public Property LastName() As String
Get
Return m_LastName
End Get
Set(ByVal Value As String)
m_LastName = Value
End Set
End Property
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspInsertClients")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ClientID", Me.ClientID)
.AddWithValue("@FirstName", Me.FirstName)
.AddWithValue("@LastName", Me.LastName)
End With
Return comm
End Using
End Function
#End Region
End Class
Public Module Program
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' </summary>
''' <param name="_ConString"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString(ByVal _ConString As String) As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
If String.IsNullOrEmpty(_ConString) Then
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
Else : Return _ConString
End If
End Function
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' and uses the default connection string
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Return ConString(String.Empty)
End Function
''' <summary>
''' Saves all objects of type DBConnect in the supplied list and returns number of records saved.
''' If IsTransaction is true, all data will save or all will fail. Otherwise only data with no error(s) will save.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <param name="IsTransaction"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function SaveAll(Of T As DBConnect)(ByVal _List As List(Of T), ByVal IsTransaction As Boolean) As Integer
Dim records As Integer
Using conn As New SqlConnection()
Try
If _List Is Nothing OrElse _List.Count < 1 Then Throw New ArgumentException("List is not set or it’s empty!")
conn.ConnectionString = ConString()
conn.Open()
If IsTransaction = True Then
Using tran As SqlTransaction = conn.BeginTransaction()
For Each list As T In _List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while saving, transaction canceled!" & vbCrLf & ex.Message)
End Try
Next
tran.Commit()
End Using
ElseIf IsTransaction = False Then
For Each list As T In _List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Next
End If
Return records
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Function
''' <summary>
''' Saves all objects of type DBConnect in the supplied list and returns number of records saved.
''' Only data with no error(s) will save.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function SaveAll(Of T As DBConnect)(ByVal _List As List(Of T)) As Integer
Return SaveAll(_List, False)
End Function
''' <summary>
''' Function used to fade out a closing form using a user defined number of steps
''' </summary>
''' <param name="_Form"></param>
''' <param name="NumberOfSteps"></param>
''' <remarks></remarks>
Public Sub FadeClosingForm(ByVal _form As Form, ByVal numberOfSteps As Byte)
Dim stepValue As Double = Convert.ToDouble(100.0F / numberOfSteps)
Dim dOpacity As Double = 100.0F
For b As Byte = 0 To numberOfSteps
_form.Opacity = dOpacity / 100
_form.Refresh()
dOpacity -= stepValue
Next
End Sub
End Module
#End Region
Hopefully it helps
Note: Source code attached (WindowsApplication1.zip)
Every table should have a primary key, that’s according to First Normal Form which dictates that all rows in a table should be uniquely identified.
MS SQL Server allows us to define a numeric column as an IDENTITY column, which automatically generates a unique value for each row. Alternatively, one can use NEWID() to generate a random unique value for each row. These types of values, when used as keys, are what are known as Surrogate Keys.
A Surrogate Key in a database is considered to be a unique identifier for a row in the table that is not derived from the data itself. Most times, these keys exist for internal use only as permanent record identifiers and shouldn't be shown to users.
Now consider a situation where by our customers or the end users of our applications are accustomed to referring to Orders by a unique reference number such as ‘1234’. Thus acclimated, they start to expect that the next invoice created will be ‘1235’. If the order jumps to ‘1238’, for example, they start to worry as to what happened to '1236' and ‘1237’. Next thing you know, they're going to ask us to fill in the sequence gaps. Oooch... now they want to append the reference number with the last two digits of year of the order (‘07-1237’) and also suffix it with the customer initials (‘07-1237-KW’). They go on and on. Definitely surrogate key can’t work here.
Perhaps one can think of avoiding surrogate keys by using composite keys (multi column primary keys). Consider a situation where by you have a table Visits that has columns Visit Number and Patient Number as composite keys with many objects referencing it (Visits). Well referencing Visits through Visit Number and Patient Number is just pain for me. The truth is composite keys make any kind of Object/Relational mapping and persistence in general harder. Life is so much easier with surrogate keys compared to composite keys.
The rule of thumb I use is simple, each of your tables should have a natural key that means something to the user, and can uniquely identify each row in the table. At best, the key should be automatically generated and may be replaced by the user. e.g. if Patient Number is ’07-1234-JK’ table Visits could have Visit numbers such as ’07-1234-JK-001’, ’07-1234-JK-002’ and ’07-1234-JK-003’, Visit Number being the only primary key for Visits and automatically generated.
To accomplish the above,
- Add an int column to your table that will store the incremental number.
- Add a Natural Key column, preferably a varchar.
i.e. for Visits, add columns among others the following
- VisitID (preferably of int datatype). I call this a helper column that’s internal
- VisitNo (preferably of varchar(20) datatype). This is the primary key
- PatientNo
- etc
There after determine automatically the next VisitID, generate the VisitNo by padding the VisitID with the padding length of your choice, concatenate it with PatientNo, and present it to the user, and if the user wants, she can replace it.
In my applications, I have a table that manages all Auto Numbers; a couple of functions and at times triggers to ensure that helper’s column sequence is not altered.
Create table code
create table AutoNumbers
(ObjectName varchar(40) not null
constraint fkObjectNameAutoNumbers references AccessObjects(ObjectName),
AutoColumnName varchar(60) not null ,
constraint pkObjectNameAutoColumnName primary key(ObjectName,AutoColumnName),
HelperColumnName varchar(60) not null,
AutoColumnLEN tinyint not null,
PaddingCHAR char(1) not null,
PaddingLEN tinyint not null ,
SeparatorCHAR char(1) not null,
SeparatorPositions varchar(20), -- coma separated
StartValue int not null,
Increment smallint not null constraint ckIncrement check (Increment >= 0),
AllowJumpTo bit not null constraint dfAllowJumpTo default 0,
JumpToValue int not null constraint dfJumpToValue default 0
)
go
Explanation
ObjectName:- referrers to name of table to have the natural key e.g. Visits
AutoColumnName:- the automatically generated natural key e.g VisitNo
HelperColumnName:- the helper column that stores the increment e.g VisitID.
AutoColumnLEN:- key column length e.g. 11 for ’07-1234-JK-003’ (- not stored)
PaddingCHAR:- the padding character e.g 0 as seen in 003 last above
PaddingLEN:- padding length e.g 3 that leads to 003 in our example
SeparatorCHAR:- separator character such as ‘-’ that is inserted for better viewing
SeparatorPositions:- positions where the separator character will be displayed e.g at ‘2,7,10’ in our example
StartValue:- start value say 1 to start at 001
Increment:- The increment value say 1
AllowJumpTo:- If set to true, the Auto Number can jump to the jump to value if greater than Start Value.
JumpToValue:- The value to jump to if allow jump to value is set to true
The following is a SQL function that will help to retrieve the next ID for your Natural key column.
--------Function Get Next AutoNumber--------------------------------------------
if exists (select * from sysobjects where name = 'GetNextAutoNumber')
drop function GetNextAutoNumber
go
create function GetNextAutoNumber(@ObjectName as varchar(40), @AutoColumnName varchar(60), @ID int) returns int
with encryption as
begin
declare @NewID int
declare @SeparatorPositions varchar(20)
declare @StartValue int
declare @Increment smallint
declare @AllowJumpTo bit
declare @JumpToValue int
set @SeparatorPositions = (select SeparatorPositions from AutoNumbers
where ObjectName = @ObjectName and AutoColumnName = @AutoColumnName)
set @StartValue = (select StartValue from AutoNumbers
where ObjectName = @ObjectName and AutoColumnName = @AutoColumnName)
set @Increment = (select Increment from AutoNumbers
where ObjectName = @ObjectName and AutoColumnName = @AutoColumnName)
set @AllowJumpTo = (select AllowJumpTo from AutoNumbers
where ObjectName = @ObjectName and AutoColumnName = @AutoColumnName)
set @JumpToValue = (select JumpToValue from AutoNumbers
where ObjectName = @ObjectName and AutoColumnName = @AutoColumnName)
if (@AllowJumpTo = 1) and (@JumpToValue > @ID) and (@JumpToValue > @StartValue)
begin
set @NewID = @JumpToValue + @Increment
end
else
begin
set @NewID = isnull(@ID, @StartValue) + @Increment
end
return @NewID
end
go
When Inserting Visits, you can have edit your InsertVisits stored procedure to look as follows
create proc uspInsertVisits(
@VisitNo varchar(20),
@PatientNo varchar(20),
….
)as
declare @VisitID int
…
begin
set @VisitID = (select max(VisitID) from Visits where PatientNo = @PatientNo)
set @VisitID = dbo.GetNextAutoNumber('Visits', 'VisitNo', @VisitID)
insert into Visits
(VisitID ,VisitNo ,PatientNo …)
values
(@VisitID ,@VisitNo ,@PatientNo…)
return 0
end
go
The function below will submit the Next Auto Number to your code that you can pad, format, merge with Patient No and display it in the entry box for the user to accept or replace.
create proc uspGetNextVisitID(
@PatientNo varchar(20) ,
@VisitID int = null output
)as
set @VisitID = (select max(VisitID) from Visits where PatientNo = @PatientNo)
set @VisitID = dbo.GetNextAutoNumber('Visits', 'VisitNo', @VisitID)
return 0
Let me know what you think