Active Directory Using VB.NET

This article discusses working within the Active Directory (AD) using VB.NET, how to query the AD, How to authenticate the user, how to retrieve the user details using Domain User ID, how to retrieve the user details using domain e-mail id etc

The Active Directory is the Windows directory service that provides a unified view of the entire network. Working with the Active Directory is a lot like working with a database, you write queries based on the information you want to retrieve.

Recently, at work, I was tasked with creating a single signon for the application I am currently programming to allow the user to use the application without entering the userid and password details; Also I had to ensure that in the event the application was ever taken off-site, that it couldn't run; This would prevent an individual from taking the application off-site and attempting to use it.

Then I thought of using the Active Directory of the company network to achieve the Single Signon as well as Security.

Active directory seemed to be the most secure way as only a select group of people actually have the permissions to alter the Active Directory in any way.

I built the logic using the System.DirectoryServices namespace.

System.DirectoryServices: The System.DirectoryServices namespace built into the .NET Framework is designed to provide programming access to LDAP directories (Active Directory).

To start querying Active Directory from your VB.NET code, you simply add a reference to the System.DirectoryServices.dll in your project and the following Imports statement to your code:

Imports System.DirectoryServices

When the application is launched, it will check the name the user was logged in as:

Environment.UserName.ToString. It will return the user name in the format: DOMAIN\USERNAME

After getting the user name the application will query the Active Directory to ensure this is a valid network account and they have permissions to be using this application.

The first thing to do when working with the Active Directory is to create a connection to the Active Directory:

dirEntry = New System.DirectoryServices.DirectoryEntry("LDAP://" & DOMAIN_NAME)

You can replace the path with the one specific to your network.

The next thing to search for the provided user to ensure that the login provided is a valid one.

dirSearcher = New

System.DirectoryServices.DirectorySearcher(dirEntry)

dirSearcher.Filter = "(samAccountName=" & m_LoginName & ")"

Dim sr As SearchResult = dirSearcher.FindOne()

If sr Is Nothing Then 'return false if user isn't found
lblStatus.Text = "User authentication failed"
Return False
End If

The condition used here to Search for an entry for the logged in user. The "samAccountName" is the name of the field used to store Domain User ID.

If the user does not exist in the Active Directory, the result will be nothing.

The .FindOne() method will be used to stop searching as soon as the match is found.

 

Sample Code 1:

The IsLogonValid is used to validate the logged in user. It will search for the provided user in the Active Directory. The function will return true or false depending if the login provided is a valid one. With this function, if the selected user is found, and then True is returned, else False is returned, letting the programmer know that this isn't a valid user in the Active Directory.

Private m_ServerName As String
Private m_LoginName As String

Private m_Authenicate As String

Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent() 

' Add any initialization after the InitializeComponent() call.
m_ServerName = DOMAIN_NAME ' Your Domain Name
m_LoginName = Environment.UserName.ToString
m_Authenicate = My.User.Name 
End Sub 
 

Public Function IsLogonValid() As Boolean

Dim m_LoginName As String
Dim dirEntry As System.DirectoryServices.DirectoryEntry
Dim dirSearcher As System.DirectoryServices.DirectorySearcher

lblStatus.Text = "Validating User Account" 

Try 
m_LoginName = Environment.UserName.ToString 'The logged in user ID
dirEntry = New System.DirectoryServices.DirectoryEntry("LDAP://" & DOMAIN_NAME) 
dirSearcher = New System.DirectoryServices.DirectorySearcher(dirEntry) 
dirSearcher.Filter = "(samAccountName=" & m_LoginName & ")"

       'Use the .FindOne() Method to stop as soon as a match is found

Dim sr As SearchResult = dirSearcher.FindOne() 

If sr Is Nothing Then 'return false if user isn't found
lblStatus.Text = "User authentication failed"
Return False
End If

Dim de As System.DirectoryServices.DirectoryEntry = sr.GetDirectoryEntry()

sUserName = de.Properties("GivenName").Value.ToString()
 

lblStatus.Text = "User authentication success"

Return True  'Valid user

Catch ex As Exception ' return false if exception occurs

lblStatus.Text = "User authentication failed"

Return False

End Try

End Function

 

Sample Code 2:

The DisplayActiveDirUserDetails function is used to retrieve the FirstName, Last Name and e-mail adddress of the logged in user.

The PropertiesToLoad property of the DirectorySearcher object is a collection containing attribute names of AD objects that we want the query to return. By analogy with SQL query, the Filter property serves as the WHERE clause and the PropertiesToLoad property works as a list of column names that the query will return.

To retrieve specific properties, we need add them to the Collection before we begin the search. For example, searcher.ProperiesToLoad("GivenName") will add the GivenName property to the list of properties to retrieve in the search.

Some of the directory entry properties are

• SAMAccountName – Users Login Name
  
• Mail –E-Mail
  
• Sn – SurName or Last Name
  
• GivenName – First Name
  
• Title – User Title
  
• Phone – User telephone number
  
• Department – User's Department etc.
  
• Mobile – Mobile Phone number
  
• City – User's City
  

 

In this example I have retrieved only FirstName, LastName and e-mail address.

 

Basically, this is a good practice to limit the amount of returning properties as much as you can. It can reduce execution time of the query significantly.

 

Private Function DisplayActiveDirUserDetails(ByVal USERID As
String) As Boolean

Dim dirEntry As System.DirectoryServices.DirectoryEntry

Dim dirSearcher As System.DirectoryServices.DirectorySearcher

lblStatus.Text = "Validating User Account"

Try dirEntry = New System.DirectoryServices.DirectoryEntry("LDAP://" & DOMAIN_NAME)

dirSearcher = New System.DirectoryServices.DirectorySearcher(dirEntry) 

 dirSearcher.Filter = "(samAccountName=" & USERID & ")"

        'The PropertiesToLoad.Add method will be useful when retrieving only the selected properties.

'In this example I have retrieved only GivenName, Mail and sn

'There are many other properties are available

dirSearcher.PropertiesToLoad.Add("GivenName")
'Users First Name
dirSearcher.PropertiesToLoad.Add("Mail")
'Users e-mail address
dirSearcher.PropertiesToLoad.Add("sn")
'Users last name

Dim sr As SearchResult = dirSearcher.FindOne()

If sr Is Nothing Then 'return false if user isn't found
lblStatus.Text = "Invalid UserID"
Return False
End If

'Retrieve the user's First Name, e-mail and Last Name and assigns them to text boxes

Dim de As System.DirectoryServices.DirectoryEntry = sr.GetDirectoryEntry() 

If Not de.Properties("GivenName").Value Is Nothing Then
txtUserName.Text = de.Properties("GivenName").Value.ToString()
End If 

If Not de.Properties("Mail").Value Is Nothing Then
txtEmail.Text = de.Properties("Mail").Value.ToString()
End If

If Not de.Properties("LastName").Value Is Nothing Then
txtLastName.Text = de.Properties("LastName").Value.ToString()
End If

Return True
'Valid user

Catch e As Exception ' return false if exception occurs

MsgBox("User Authetication Exception: " & e.Message)

Return False

End Try

End Function 
 

Sample Code 3:

The GetUserPropsUsingEmail function is used to retrieve the domain user Id using domain e-mail address.

The userPrincipalName property of Active directory determines the Domain e-mail address of the User.
 

Public Function GetUserPropsUsingEmail() As String

Dim strFullName As String = ""
Dim sPath As String = ""
Dim objDirEnt As New DirectoryEntry("LDAP://" & DOMAIN_NAME)
Dim objSearcher As New DirectorySearcher(objDirEnt)
Dim objSearchRes As SearchResult

' Filter by Lotus Notes internet name

objSearcher.Filter = "(userPrincipalName=" & txtInternetName.Text & ")"

Try
' count should be 1

If objSearcher.FindAll.Count > 0 Then

For Each objSearchRes In objSearcher.FindAll
sPath = objSearchRes.GetDirectoryEntry.Path

Next
objDirEnt.Close()

objDirEnt.Path = sPath

'get domain short name

strFullName = objDirEnt.Invoke("GET", "samAccountName")

End If

Catch ' return nothing if user isn't found

strFullName = ""

End Try

Return strFullName

End Function

 

read more “Active Directory Using VB.NET”

SQL Query: Removing leading zeroes from SQL Server field

The SQL Query for Removing leading zeroes from SQL Server field

The following SQL Query removes the leading zeroes from "Field" field

Select replace(ltrim(replace(Field1, '0', ' ')) , ' ', '0') FROM TestTable

 

read more “SQL Query: Removing leading zeroes from SQL Server field”

ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time xxx exceeds limit yyy

ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time xxx exceeds limit yyy.

 Where xxx and yyy represent the estimated amount of time it will take to process the query and the OS/400 query time limit, respectively.

The error occurs when the estimated SQL Query run time exceeds the system's query processing limit.

Solution:

You can turn off the query timeout limit in the iSeries Access ODBC Driver.

 

IBM now provides you with a mechanism for turning off query timeout value support for applications that use a particular ODBC Data Source Name (DSN).

 

You can turn off query timeout limit processing in a DSN by performing the following steps:

• Open the ODBC Data Source Administrator on the client machine that is experiencing the SQL0666 error
  
• In the Administrator window, highlight the DSN that you want to change and click on the Configure button
  
• Click on the Performance tab in the Windows Setup dialogue
  
• Click on the Advanced button under the Performance options. This will bring up the Advanced performance options window
  
• Turn off the checkmark in the Allow Query Timeout checkbox. Click on OK to exit this screen
  
• In the Windows Setup screen, click on the Apply button followed by the OK button. This allows you to exit the screen and save your changes
  

  
   

•  
   

Once this option is turned off, applications using this particular ODBC DSN will automatically disable support for query timeout value checking.

Note:

But remember that while this option is handy for allowing longer running queries to automatically finish, it also removes a safeguard against run-away queries that will throttle PC and AS/400 performance. So use it only when it's needed and leave it on the rest of the time.

Another Tip:

The ODBC Progress database can store binary or array of data into "CHAR" field type.

The SQLBulkCopy error "The byte array of data cannot be converted to nvarchar/varchar" might throw while migrating the data into SQL Server table's nvarchar/varchar (equivalent SQL data types)field.

Reason: While retrieving the data will be reaching into the destination table as an array of bytes which is supposed to be a text or string of characters.

IBM provides a mechanism to convert the binary data into text. This is achieved by performing the following steps:
 

• Open the ODBC Data Source Administrator on the client machine that is experiencing this error
  
• In the Administrator window, highlight the DSN that you want to change and click on the Configure button
  
• Click on the Translation tab in the Windows Setup dialogue that appears
  
• Turn on the checkmark in the Convert binary data (CCSID 65535) to text checkbox.
  
• Click on the Apply button. This allows you to exit the screen and save your changes
  

  
   
  

read more “ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time xxx exceeds limit yyy”

How to display a PDF document in browser using ASP.NET and VB.NET?

The below example would explain on how to Display a PDF document in browser using ASP.NET/VB.NET

Dim Fs As FileStream
Dim FileSize As Long
Dim filePath As String = "C:\Test.Pdf"

'Open the file.

Fs = New FileStream(filePath, FileMode.Open)
FileSize = Fs.Length

'Convert the file into array of bytes.
Dim Buffer(CInt(FileSize)) As Byte
Fs.Read(Buffer, 0, CInt(FileSize))
Fs.Close()

'Write the binary content directly to the HTTP Output Stream.

Response.ContentType = "application/pdf"
Response.OutputStream.Write(Buffer, 0, FileSize)
Response.Flush()
Response.Close()

read more “How to display a PDF document in browser using ASP.NET and VB.NET?”

XML Error- A semi colon character was expected. Error processing resource –

1) XML Error: A semi colon character was expected. Error processing resource –

Error Description:

While  viewing  the xml file in a web browser, sometimes an error "A semi colon character was expected." Occurs.

Example of a  XML snippet  that throws this error:

<NODES>

<NODE Caption="Ext Link" Link Name="http://google.com/ccc?key=0AEeVE& hl=en" />

</NODES>

Reason:

This occurs when using external links on the XSLT sheet which contains the character “&”.

Solution:

The work around is to simply replace every instance of & in the link with &

<NODES>

<NODE Caption="Ext Link" Link Name="http://google.com/ccc?key=0AEeVE&amp;hl=en" />

</NODES>

read more “XML Error- A semi colon character was expected. Error processing resource –”

.NET Error: Operator '<operatorname>' is not defined for types '<typename1>' and '<typename2>'

Error Description:

The error "Operator '<operatorname>' is not defined for types '<typename1>' and '<typename2>'" occurs When an attempt was made to use an operator in a way that is inappropriate for the specified types.

Reason:

This error can be caused by using the "=" operator instead of using the Is operator to compare two objects.

 

Example:

 

Private Sub toolBar_ButtonClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs)

If e.Button = toolBarButtonNew
Then ' => Error Operator '=' is not defined for types 'System.Windows.Forms.ToolBarButton' and 'System.Windows.Forms.ToolBarButton'

      menuItemNew_Click(Nothing, Nothing)

End If

 

If e.Button <> toolBarButtonSave
Then ' => Error Operator '<>' is not defined for types 'System.Windows.Forms.ToolBarButton' and 'System.Windows.Forms.ToolBarButton'

menuItemNew_Click(Nothing, Nothing)

End
If

 

End Sub

 

Solutions:

1. Use Is operator to compare two reference types.
   
2. Use the Not operator in conjunction with the Is operator to denote inequality. 

Replace

1. 
   "If e.Button = toolBarButtonNew
   Then" with "If e.Button Is toolBarButtonNew
   Then"
   
2. "If e.Button <> toolBarButtonSave
   Then" with "If Not e.Button Is toolBarButtonSave
   Then"
   

read more “.NET Error: Operator '<operatorname>' is not defined for types '<typename1>' and '<typename2>'”

.NET Error: Implementing property must have matching 'Readonly' or 'Writeonly' specifiers

Error Description:

The compiler error Implementing property must have matching 'Readonly' or 'Writeonly' specifiers occurs When implementing property of the interface in the class.

Example:
Interface IComparer 

Public Interface IComparer

#Region "Methods"

Private Function Compare(ByVal x As [Object], ByVal y As [Object]) As
Integer

……

#End Region

End Interface

 

Class Implements the property Compare of IComparer interface:

Public Class DateTimeReverserClass

Implements IComparer

Private Function Compare(ByVal x As [Object], ByVal y As [Object]) As
Integer //=> Error throws here

Dim dx As DateTime = DirectCast(x, DateTime)
Dim dy As DateTime = DirectCast(y, DateTime)
If dx > dy Then
   Return -1
Else
   Return 1
EndIf

End Function

End Class

Reason:
The compiler is not able to find the matching property implementation on interface 'IComparer'
 

Possible Solutions:

The work around is to include the Implements clause to give the fully qualified name of the property (Format: Implements <Interface Name>.<Property Name>)

 

In the above example, replace the line "Private Function Compare(ByVal x As [Object], ByVal y As [Object]) As Integer" with "Private Function Compare(ByVal x As [Object], ByVal y As [Object]) As Integer Implements IComparer.Compare"

 Public Class DateTimeReverserClass

Implements IComparer

Private Function Compare(ByVal x As [Object], ByVal y As [Object]) As
Integer Implements IComparer.Compare

Dim dx As DateTime = DirectCast(x, DateTime)

Dim dy As DateTime = DirectCast(y, DateTime)

If dx > dy Then
Return -1
Else
Return 1

EndIf

End Function

End Class

 

Now the compiler error will not occur.

read more “.NET Error: Implementing property must have matching 'Readonly' or 'Writeonly' specifiers”

SqlBulkCopy Error: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

 

Error Description:

The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

Reason:

The error occurs during sqlbulkcopy when the destination table contains the Decimal column with same precision and scale. (E.g., The  table in SQLServer has column TestColumn  Decimal (3,3) )

SELECT (cast(0.000 as decimal(3,3))) this will run fine in SQL, but will fail in bulk copy.

Possible Solutions:

Increase the precision size.

I had the same problem when I worked on my data migration project.

The work around was to increase the precision size by 1 if both the precision and scale are same for the Decimal Column type.

Example:

TestColumn Decimal (3,3) will fail in sql bulk copy.

But

TestColumn Decimal (3, 4) will work fine in sql bulk copy.

read more “SqlBulkCopy Error: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.”

32-bit Driver Installation for 64‑bit Platforms

How to use a 32-bit driver program in 64‑bit Windows family of operating systems.

This information applies for the following operating systems:

Windows Server 2008 64-Bit Edition

Back Ground

I having been of late developing a Soft ware package in .NET Framework 3.5 for the LegaSync Project, to migrate the data from various legacy ERP systems (SAP,SYTELINE,MACPAC,MANMAN,JDE etc) to SQL server. The ODBC connectivity is used to retrieve the data from many of these legacy systems. Thus it involves various ODBC drivers, which all of them are designed for 32-bit platforms.

The initial development server that was hosted by the client was a 32-bit machine and we didn’t have any driver issues while connecting to the legacy systems.

But when it came to production deployment, the client had hosted a server with the latest configuration ( Windows Server 2008 R2, which was a 64 bit machine.)

It was then we came across this situation that 64-bit drivers need to be installed in the 64-bit machine.

But there are no 64-bit ODBC drivers available for those legacy systems in the market.

So we were in the situation that we need to install the existing 32-bit drivers in the 64-bit platform.

It was not possible to install legacy 32-bit installers to work on 64-bit Windows simply by running the installer package. The installer displays an informative error message.

The below trick cracked the issue of the installation of 32-bit driver in 64-bit platform.

Installation Considerations for 64-bit Platforms

Installation of 32-bit drivers in 64-bit platforms involves the following steps:

1) Creation of DTS Package (DTSPkg1.dtsx) using SQL Server Business Intelligence Development Studio, Which will internally run the required setup exe.

2) The DTS package needs to be run in the 32-bit windows. (default 64-bit)

3) So I used dtexec.exe, an application which can launch the application/DTS package in 32-bit windows and 64 bit windows.

4) The 32-bit support dtexec.exe will be present in” D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn” and the 64-bit support dtexec.exe will be present in “D:\Program Files\Microsoft SQL Server\100\DTS\Binn”.

5) The folder name ends with (x86) contains the 32-bit support files

6) Then I defined a new Sql Server Job in Sql server 2008 management studio

7) In the job step creation I selected the cmdExec as operating system and typed the following command:

"D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /File "C:\DTSPkg\DTSPkg1.dtsx "

Then I ran the SQL Job. The 32-bit driver made a successful transition to the 64-bit operating system.

Viewing installed 32-bit drivers in 64-bit platform:

As the Windows 2008 supports both 32-bit and 64-bit, when we click on Data Sources (ODBC) From Program Menu/Control Panel, we cannot see the installed 32-bit drivers as it will display only the 64-bit drivers.

The reason is that the exe runs from System32 Folder (64-bit drivers)

To view the 32-bit drivers we need to type the following the command prompt

Ø %WINDIR%\SysWow64\odbcad32.exe

In the above picture is highlighted, a MERANT 3.60 32 –BIT Progress SQL92 V91.1 C which is the required ODBC driver for connecting Progress Databases of SYTELINE ERP’s to SQL Server

Migration of Windows 32-bit mode .NET application to 64-bit platform:

After the installation of 32-bit drivers in 64-bit platform, the next problem was the migration of the .Net application which was developed in 32-bit mode to the new 64-bit machine.

When I start running my application to connect with the legacy database, I got the following informative error:

“error[ im014] [Microsoft][ODBC Driver Manager]The specified DSN contains an architecture mismatch between the Driver and Application”

The error clearly indicates the architecture difference between the LegaSync application and New 64-bit Server and the application needs to be run in X86 architecture (32-bit platform)

Solution:

Creating platform specific apps

Most of the language compilers (like C#) now offer a /platform switch. By using this switch, developers can create binaries targeted for a specific platform type or binaries that are platform agnostic. There are four types of binaries that are emitted

· any cpu – platform agnostic

· x86 – 32-bit platform specific

· x64 – x64 platform specific

By default the compilers (like C#) emit anycpu binaries (also called portable assemblies) which are platform agnostic. In case the users want to create binaries specific to a platform, they can use the appropriate switch and be done.

Cross Compilation

The above concept of /platform switch enables cross compilation of binaries. Cross compilation means compiling binaries to a specific platform type (different from the current platform). This is mostly used in terms of compiling and creating 64-bit assemblies from a 32-bit compiler and vice versa. One point to note here is that cross compilation usually refers to compiling to different target types from the compilers shipped with the 64-bit Redist (WoW and 64-bit). The reason for this is that, while trying to compile for a 64-bit platform from a pure 32-bit machine, the 32-bit Redist would not have the components that are 64-bit specific. In such cases the compilation might go through with warning but execution might lead to runtime exceptions. Also, one should note that while cross compiling, users should stick to the platform architecture type of the machine, viz. x64 or IA. Assemblies for IA and x64 are specific to the platform architecture and cross compilation across architectures is not advised.

How to create platform specific apps?

Steps:

1) Go to Project->Properties->Compile

2) Click On Advanced Compile Options button

3) Change the Target CPU to X86 /X64 – default Any CPU

4) Rebuild the application

As I wanted the application to run in 32-bit mode in Compatible with X86 architecture, I Chose X86 CPU Compile Options.

The application started working fine.

read more “32-bit Driver Installation for 64‑bit Platforms”

How to determine First Day of the week For a Week Number and Year in SQL

How to determine First Day of the week For a Week Number and Year in SQL?

 

The following SQL User defined function describes how to determine the first day of the week using Week Number and Year as Input.

 

/*

Function Name: GetFirstDayOfAWeekInYear

Input Parameters: @InputYear – Year Number

@InputWeekNo – Week Number

Output Type: DATETIME – First day of week

Example: GetFirstDayOfAWeekInYear(2010,4) Will return 01/17/2010 as output.

    Created By: Vijayalakshmi Rajkumar

    Created On: 10/5/2009

*/

 

CREATE FUNCTION [dbo].[GetFirstDayOfAWeekInYear]

( @InputYear int,

@InputWeekNo int

)

RETURNS DATETIME

BEGIN

 

declare @firstDayOfYear as datetime;

declare @firstDayOfWeek as datetime, @TempDate as datetime;

declare @firstDayNameOfYear AS varchar(50);

declare @defaultDate as varchar(50);

 

--get the first day of year

 

SET @defaultDate = '1/1/' + Convert(Varchar, @InputYear)

 

--Add the 1/1 to the Input Year to get the first day of year

SET @firstDayOfYear =CONVERT (DATETIME, @defaultDate)

 

--Get the day name of the year - It can be one among 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'

SET @firstDayNameOfYear = DATENAME(dw, @firstDayOfYear)

 

-- Add 7 * ( WeekNo -1) days

SET @TempDate = DateAdd(DD, (7 * (@InputWeekNo - 1)),@firstDayOfYear)

 

--Based on First Day of Year we need to subtract the number of days

SELECT @firstDayOfWeek = CASE @firstDayNameOfYear

 

When 'Monday' Then DateDiff(DD, 1,@TempDate)

 

When 'Tuesday' Then DateDiff(DD, 2,@TempDate)

 

When 'Wednesday' Then DateDiff(DD, 3,@TempDate)

 

When 'Thursday' Then DateDiff(DD, 4,@TempDate)

 

When 'Friday' Then DateDiff(DD, 5,@TempDate)

 

When 'Saturday' Then DateDiff(DD, 6 ,@TempDate)

 

When 'Sunday' Then DateDiff(DD, 7,@TempDate)

End

 

 

Return @firstDayOfWeek

 

END

 

Logical Description:

The logic needs to be separated into three parts:

1. Get the first date of the year by attaching '1/1/' with the Year
   

   Ex: If the year is 2010, the date would be '1/1/2010'
   

   
    

2. Get the First Day of the Year.
   

   The function DateName () is used to get the Day in text format. (Sunday, Monday etc.)
   

   
    

3. Add 7 * (WeekNumber -1) days to the first date of year(In this example, '1/1/2010') – Where WeekNumber is the input.
   

   
    

4. Based on the first day of the year, the number of days needs to be subtracted from the date
   

   For Monday -1 day
   

   Tuesday – 2 days
   

   Wednesday – 3 days
   

   Thursday – 4 days
   

   Friday – 5 days
   

   Saturday – 6 days
   

   Sunday – 7 days
   

How to Call?

The above function can be called as follows:

Select [dbo].[GetFirstDayOfAWeekInYear](2010,4) – Where 2010 is the Year and 4 is the week number

Output:

2010-01-17 00:00:00.000

Formatted Display:

The date display can be formatted to display in various formats.

To display the output in MM/DD/YYYY format, we can call the function as follows:

SELECT Convert (varchar(10), [dbo].[GetFirstDayOfAWeekInYear](2010,4),101) --Format MM/DD/YYYY

Output:

01/17/2010

read more “How to determine First Day of the week For a Week Number and Year in SQL”

Manipulating XML Data Using DataSet Class

This article describes how to use a windows form in VB.NET to manage data in XML files. The code snippets in this article demonstrate inserting, updating, and deleting row entries from an XML data file that has been loaded into the DataSet class using the ReadXML method. This article also demonstrates how to display the data from the XML file.

I Use DataGridView to list the XML File and each row of DataGridView contains two buttons EDIT and DELETE for the purpose of updating and deleting XML File

Sample XML File

For the examples in this article, I use the TestXML.xml.

<?xml version="1.0" standalone="yes" ?>

-
<NODES>

-
<Details>

<ID>1</ID>

<Name>Mr.XX</Name>

<Age>28</Age>

<DOJ>12/29/2006</DOJ>

</Details>

-
<Details>

<ID>2</ID>

<Name>MR.YY</Name>

<Age>27</Age>

<DOJ>9/16/2009</DOJ>

</Details>

-
<Details>

<ID>3</ID>

<Name>Ram</Name>

<Age>29</Age>

<DOJ>10/2/2009</DOJ>

</Details>

</NODES>

You'll need the following namespaces in the VB.NET code for execution:

Imports System.Xml

Reading XML into a DataSet

.NET provides the DataSet class with methods that read and parse an XML file for access within code.

The following code snippet illustrates the XMLRead and XMLWrite methods from the DataSet class

Dim ds As New DataSet

Try

ds.ReadXml("C:\Documents\TestXML.xml")

ds.WriteXml("C:\Documents\TestXML.xml")

Catch ex As Exception

MsgBox(ex.Message)

End Try

The DataSet class contains an array of rows, each row called Details with Four columns called ID , Name, Age and DOJ.

Listing Data

If you want to list the contents of the DataSet in the same order as the XML file, use the following code:

Try

ds.ReadXml("C:\Viji\Documents\TestXML.xml")

Catch ex As Exception

MsgBox(ex.Message)

Exit Sub

End

Try

DataGridView1.Columns.Clear()

DataGridView1.DataSource = Nothing

If Not ds Is Nothing Then

DataGridView1.DataSource = ds.Tables(0)

End If

If DataGridView1.Rows.Count > 0 Then

Dim dgButtoncol As
New DataGridViewButtonColumn

dgButtoncol.UseColumnTextForButtonValue = True

dgButtoncol.HeaderText = "View"

dgButtoncol.Text = "View"

dgButtoncol.Name = "View"

DataGridView1.Columns.Add(dgButtoncol)

dgButtoncol = New DataGridViewButtonColumn

dgButtoncol.UseColumnTextForButtonValue = True

dgButtoncol.HeaderText = "Edit"

dgButtoncol.Text = "Edit"

dgButtoncol.Name = "Edit"

DataGridView1.Columns.Add(dgButtoncol)

dgButtoncol = New DataGridViewButtonColumn

dgButtoncol.UseColumnTextForButtonValue = True

dgButtoncol.HeaderText = "Delete"

dgButtoncol.Text = "Delete"

dgButtoncol.Name = "Delete"

DataGridView1.Columns.Add(dgButtoncol)

End If

Inserting Data

The following code sample shows how to insert a new row into the DataSet:

Try

Dim ID As String

ID = ds.Tables(0).Rows.Count + 1

Dim dr As DataRow = ds.Tables(0).NewRow

dr("ID") = ID

dr("Name") = txtName.Text.Trim

dr("Age") = txtAge.Text.Trim

dr("DOJ") = DateTimePicker1.Value.ToShortDateString

ds.Tables(0).Rows.Add(dr)

ds.WriteXml("C:\Documents\TestXML.xml")

Catch ex As Exception

MsgBox(ex.Message)

End Try

Where txtName, txtAge (text boxes) and DateTimePicker1 controls are used to let the user enter new value.

Updating Data

To update a row in the DataSet, you must locate the row and update the fields within the row. A key field must be selected and searched, using the value of the field prior to the change, to locate the correct row for update. For this example the "ID" field is used as the key field.

If the Name of the ID 1, MR.XX should have been Mr.XXX, here is an example of how you could change it:

Try

If Not ds Is Nothing Then

For
Each dr As DataRow In ds.Tables(0).Rows

Dim ID As String = Convert.ToString(dr("ID"))

If ID = txtID.Text Then

dr("Name") = txtName.Text.Trim

dr("Age") = txtAge.Text.Trim

dr("DOJ") = DateTimePicker1.Value.ToShortDateString

Exit For

End If

Next

ds.WriteXml("C:\Documents\TestXML.xml")

End If

Catch ex As Exception

MsgBox(ex.Message)

End Try

Deleting Data

To delete the Mr.XX Data , a key field must be selected and searched, using the value of the field prior to the delete, to locate the correct row for removal. For this example the "ID" field is used again as the key field. Use the following code:

'I clicked on DataGridView Delete Button

Dim ID As String = sender.Rows(e.RowIndex).Cells("ID").Value.ToString.Trim

For Each dr As DataRow In ds.Tables(0).Rows

Dim rowID As String = Convert.ToString(dr("ID"))

If ID = rowID Then

dr.Delete()

Exit For

End If

Next

ds.WriteXml("C:\Viji\Documents\TestXML.xml")

This article has explained the concepts of editing, updating and deleting records from XML File using Dataset.

read more “Manipulating XML Data Using DataSet Class”

Storing and retrieving images and files from SQL Server using .NET

Summary:

This project is about storing and retrieving images and files (E.g., pdf, xls and txt) from SQL Database in Microsoft .NET Using VB.NET

Technical Features:

• SQL Server 2000
• Microsoft .NET Version 3.5
• VB.NET (Windows Forms based application)

Functional Features:

• Uploading Images/Files into database
• Retrieval of Images/Files from database

Storing Images/Files:

1) Create a table in SQL Server 2000 database which has at least one field of type Image

Here is the Script I used:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[FileStore]

GO

CREATE TABLE [dbo].[FileStore] (

[FileId] [int] IDENTITY (1, 1) NOT NULL ,

[FileName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ImageData] [image] NOT NULL,

[FileType] [varchar](10) NOT NULL,

[Added On] [DateTime] NOT NULL,

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The Image data type is used to store the binary content of the images/Files

2) I am using Open File Dialog to locate the file.

Using OpenFileDialog As OpenFileDialog = Me.GetOpenFileDialog()

If (OpenFileDialog.ShowDialog(Me) = DialogResult.OK) Then

txtFileToUpload.Text = OpenFileDialog.FileName

Else 'Cancel

Exit Sub

End If

End Using

3) I have used two methods , one to upload the image and another one to upload the files.

'Call Upload Images Or File

Dim sFileToUpload As String = ""

sFileToUpload = LTrim(RTrim(txtFileToUpload.Text))

Dim Extension As String = System.IO.Path.GetExtension(sFileToUpload)

upLoadImageOrFile(sFileToUpload, "Image")

upLoadImageOrFile(sFileToUpload, Extension)

4) Convert the file content into array of bytes using FileStream

'Initialize byte array with a null value initially.

Dim data As Byte() = Nothing

'Use FileInfo object to get file size.

Dim fInfo As New FileInfo(sPath)

Dim numBytes As Long = fInfo.Length

'Open FileStream to read file

Dim fStream As New FileStream(sPath, FileMode.Open, FileAccess.Read)

'Use BinaryReader to read file stream into byte array.

Dim br As New BinaryReader(fStream)

'When you use BinaryReader, you need to supply number of bytes to read from file.

'In this case we want to read entire file. So supplying total number of bytes.

data = br.ReadBytes(CInt(numBytes))

5) Saving byte array data to database

a) Create command text to insert record.

qry = "insert into FileStore (FileName,ImageData," & _

"FileType,[Added On]) values(@FileName, @ImageData," & _

"@FileType,@AddedOn)"

b) Create and provide value to the Parameters

'Initialize SqlCommand object for insert.

SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))

SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))

SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))

c) Execute the query to save the byte array to database

SqlCom.ExecuteNonQuery()

lblUploadStatus.Text = "File uploaded successfully"

d) Complete Code to save:

Private Sub upLoadImageOrFile(ByVal sFilePath As String, ByVal sFileType As String)

Dim SqlCom As SqlCommand

Dim imageData As Byte()

Dim sFileName As String

Dim qry As String

Try

'Read Image Bytes into a byte array

'Initialize SQL Server Connection

If connection.State = ConnectionState.Closed Then

connection.Open()

End If

imageData = ReadFile(sFilePath)

sFileName = System.IO.Path.GetFileName(sFilePath)

'Set insert query

qry = "insert into FileStore (FileName,ImageData," & _

"FileType,[Added On]) values(@FileName, @ImageData," & _

"@FileType,@AddedOn)"

'Initialize SqlCommand object for insert.

SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))

SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))

SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))

SqlCom.ExecuteNonQuery()

lblUploadStatus.Text = "File uploaded successfully"

Me.txtFileToUpload.Text = ""

Catch ex As Exception

MessageBox.Show(ex.ToString())

lblUploadStatus.Text = "File could not uploaded"

End Try

End Sub

e)

6)

Retrieving Images/Files:

Retrieving images/files from the SQL database is the exact reverse process of saving the images/files to the SQL database. I have used DataGridView control to list the files/images stored with ViewFile button to view the file/image.

1) Populating the Gridview

Creating the query to list all the rows from FileStore database:

Dim strSql As String = "Select FileId,FileName," & _

"FileType,[Added On] from FileStore"

Fill the Adapter:

'Initialize SQL adapter.

Dim ADAP As New SqlDataAdapter(strSql, connection)

'Initialize Dataset.

Dim DS As New DataSet()

'Fill dataset with FileStore table.

ADAP.Fill(DS, "FileStore")

Assign the dataset to DataGridview:

'Fill Grid with dataset.

dbGridView.DataSource = DS.Tables("FileStore")

Add View File Button to the DataGridView:

Dim dgButtonColumn As New DataGridViewButtonColumn

dgButtonColumn.HeaderText = ""

dgButtonColumn.UseColumnTextForButtonValue = True

dgButtonColumn.Text = "View File"

dgButtonColumn.Name = "ViewFile"

dgButtonColumn.ToolTipText = "View File"

dbGridView.Columns.Add(dgButtonColumn)

2) Viewing the Image

When clicking on the View File button of the DataGridView row, it will display the image/file.

The dbGridView_CellContentClick handler does the trick.

If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then

Select Case dbGridView.Rows(e.RowIndex).Cells("FileType").Value

Case "Image"

...

Case ".txt", ".pdf", ".doc"

...

Creating the query to retrieve the image from FileStore database based on FileId:

'For Image

strSql = "Select ImageData from FileStore WHERE FileId=" & dbGridView.Rows(e.RowIndex).Cells("FileId").Value

Convert the Image content into byte array:

Dim imageData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

Convert the byte array to Image using Memory Stream

Dim newImage As Image = Nothing

Using ms As New MemoryStream(imageData, 0, imageData.Length)

ms.Write(imageData, 0, imageData.Length)

'Set image variable value using memory stream.

newImage = Image.FromStream(ms, True)

End Using

Display the image in picture box:

pictureBox1.Image = newImage

3) Viewing the File

· Creating the query to retrieve the File from FileStore database based on FileId:

strSql = "Select ImageData from FileStore WHERE FileId=" & iFileId

· Convert the Image content into byte array:

Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

· Opening the Tempory File with the Stored File Name

Dim sTempFileName As String = Application.StartupPath & "\" & sFileName

· Convert the byte array to File Using File Stream

Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)

fs.Write(fileData, 0, fileData.Length)

fs.Flush()

fs.Close()

End Using

· Opening the File

System.Diagnostics.Process.Start (sFileName)

read more “Storing and retrieving images and files from SQL Server using .NET”

Cannot access destination table 'table-name' Error – During SqlBulkCopy

Problem Description:

The above error cannot access destination table might occur when bulk copying data to Sql server table using SqlBulkCopy.

Possible Reasons:

1. If the destination table name contains spaces

2. If the destination table name contains - (hyphen) symbol

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "abb po" '- This will Fail –Bcoz the table name contains space.

bulkCopy.DestinationTableName = "abb-po" '- This will Fail –Bcoz the table name contains space.

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using

Solution Description:

The table name should be preceded and followed by "[" and "]" respectively.

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[abb po]" 'Correct Code

bulkCopy.DestinationTableName = "[abb-po]" 'Correct Code

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using

read more “Cannot access destination table 'table-name' Error – During SqlBulkCopy”

SQL BULK COPY error: invalid attempt to call fieldcount when reader is closed Error

Problem Description:

The error " invalid attempt to call fieldcount when reader is closed" can occur when bulk copying data to Sql server table using SqlBulkCopy.

Consider the following example:

The following example throws the error "Invalid attempt to call fieldcount when reader is closed"

Example:

' Retrieval of the data from the source ODBC connection table

Dim sODBCCmd As New OdbcCommand(strODBC, sODBCConn)

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

sODBCConn.Close() ' We are Closing the connection

' SQL Bulk Copy

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Invalid attempt to call fieldcount when reader is closed

End Try

End Using

Possible Reasons:

The data reader is closed.

sODBCConn.Close() (which closes the connection) after ExecuteReader() is called and thus closing the
datareader (the datareader is tied to the connection).

Remarks: The connection.Close() statement closes the data reader as well.

Solution:

We must either keep the connection open and close it and the reader when you are done with it, or make use of a dataset.

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Throws Here

Finally

OdbcDr.Close()

sODBCConn.Close() ' Should be closed Here to avoid the error

End Try

End Using

read more “SQL BULK COPY error: invalid attempt to call fieldcount when reader is closed Error”

Monthly Award Winner from Dot net spider for the Month of July

Following my contributions to DOTNETSPIDER, yet another award in my bag, this time as Monthly award winner, the message from the forum below,

http://www.dotnetspider.com/forum/217934-Monthly-winner-for-month-July.aspx

Hello DNS'ians,

We are glad to announce, the Winners of the month of July, 2009 which goes to the following members.


1. Viji Raj Kumar -- Free License of Aspose.Chart Developer Enterprise for .NET valued $249

Viji Raj Kumar is a Diamond member of DNS who joined DNS in June, 2009. she is an active member of DotNetSpider.


2. Deepika Haridas -- Electronic Gadget,

Deepika Haridas is one of the Most Valuable Member of DNS and is a Diamond member at present. She was recently awarded with MVM Award and many other awards from DNS. Deepika is a very active member of DNS.


3. R.Jaya Kumar -- Electronic Gadget,

R.Jaya Kumar has joined DNS in May, 2009 and is now a Diamond Member of DNS.

Dotnetspider Team congratulates the winners on their achievement.

We have sent a mails to the Winners about their winning the award. We have sent a mail to Mr. Merrit Cooper or www.aspose.com to award the free license to Viji Raj Kumar. The winners are requested to send their shipping address and their contact numbers.

You can catch the other Winners list at :-->> List of DNS Award Winners

Members can join us to Congratulate the winners on this great achievement.

Thank you


Raghav
Web Master

read more “Monthly Award Winner from Dot net spider for the Month of July”

Retrieval of SQL database system Object details

How to get the names of Tables, Views, Stored Procedures and User Defined Functions of SQL Server database Using SQLDMO Class?

The following code VB.NET code snippet explains how to retrieve the names of tables, views, stored procedures and user defined functions of SQL Server database using SQLDMO class.

Private Sub cmdDisplayObjectDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDisplayObjectDetails.Click

'Make the Database connection

Dim strSQL As String = ""

strSQL = "Provider=SQLOLEDB.1;data source=(local);user id='test';Password='test';database='TEST'"

Dim Conn As New OleDbConnection(strSQL)

Try

Conn.Open()

Conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Display the System Object Details

RetrieveAllObjects()

End Sub

Public Sub RetrieveAllObjects()

'Declaration of database

Public MYDatabase As SQLDMO.Database2

Dim MyDBServer As New SQLDMO.SQLServer2

'Connect to the Database –Server Name, User Name and Password

MyDBServer.Connect("(local)", "test", "test")

'Populate all the system object details

MyDatabase = Nothing

Try

'Database Name: TEST

MyDatabase = MyDBServer.Databases.Item("TEST")

MyDatabase.DBOption.SelectIntoBulkCopy = True

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Get Table Details

Dim i As Integer

For i = 1 To MyDatabase.Tables.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Tables.Item(i).Name )

End If

Next

'Get View Details

For i = 1 To MyDatabase.Views.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Views.Item(i).Name )

End If

Next

'Get Stored procedure Details

For i = 1 To MyDatabase.StoredProcedures.Count

If Not MyDatabase.StoredProcedures.Item(i).SystemObject Then

Console.Writeln(MyDatabase.StoredProcedures.Item(i).Name )

End If

Next

'Get User Defined Function Details

For i = 1 To MyDatabase.UserDefinedFunctions.Count

If Not MyDatabase.UserDefinedFunctions.Item(i).SystemObject Then

Console.Writeln(MyDatabase.UserDefinedFunctions.Item(i).Name )

End If

Next

End Function

Code Explanation:

The connection to the SQL Server is made using OLEDB provider. After establishing the connection, the SQLDMO SQLServer2 and SQLDMO Database2 classes are used to retrieve the details of database.

read more “Retrieval of SQL database system Object details”

Restrict .Net Window Forms resizing

How to restrict the user from moving or resizing the .NET Windows
Forms?

There are some situations that we might want to prevent the user from
resizing(Maximizing, minimizing or double clicking the title bar etc) the
VB.Net Windows forms.

For example, we might not want to resize the form when the user double
click on the title bar of the Windows Forms.

This will be accomplished by overriding the color="#008000">WndProc Method.

WndProc Method – A method which handles the
windows messages (Maximizing the window, minimizing the window etc) posted
by the Windows Forms.

Based on the windows messages, we need to write the handler to handle
the messages.

The following VB.NET Code Snippet explains how to prevent

1) Maximization or Restoration of Form (When click on the Title bar of
the Form)

2) Maximize button click event

3) Minimize button click event

4) Moving of Form

5) Resizing the Form (Double click on Title bar of the Window)

Protected Overrides Sub WndProc(ByRef WndMsg As
Message)

'various windows message codes

Const WM_NCLBUTTONDOWN As Integer = 161

Const WM_NCLBUTTONDBLCLK As Int32 = &HA3 'When
User Clicks on the Title Bar

Const WM_SYSCOMMAND As Integer = 274 'Windows
Message

Const HTCAPTION As Integer = 2 'Title bar of
Form

Const SC_MOVE As Integer = 61456 'When the Form is
moved

Const SC_MINIMIZE As System.Int32 =
&HF020& 'When a Form is Minimized

Const SC_MAXIMIZE As System.Int32 =
&HF030& 'When a Form is Maximized

Const SC_RESTORE As System.Int32 = 61728 'When a
Form is Restored

If (WndMsg.Msg = WM_NCLBUTTONDBLCLK) And
(m.WParam.ToInt32() = HTCAPTION) Then 'Handle the Title Bar Click event as
well, this would be either Restore or Maximized

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MAXIMIZE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MINIMIZE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MOVE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_RESTORE) Then

Return

End If

If (WndMsg.Msg = WM_NCLBUTTONDOWN) And
(WndMsg.WParam.ToInt32() = HTCAPTION) Then

Return

End If

MyBase.WndProc(m)

End Sub

color="#400000">USAGE:

Place the above code snippet in the form’s
Coding area where we want to do the above activities.

read more “Restrict .Net Window Forms resizing”

Creating a Unit Test Plan to test the .NET application

Once the coding of the application is complete, the next phase is to ensure that it functions according to specification.

Although the compiler detects syntax errors, run-time errors and logical errors might not be revealed without thoroughly testing our program.

Hence there is a need to design an efficient test plan for testing of the component or application.

The Unit Test Plan

Most code contains errors on the first go. Thus, it is normal to  expect the code to have bugs when first written. However, a final application that is full of bugs or does not function according to specification is useless. We create a final version from a bug-laden first draft through testing.

Testing and debugging are separate but related activities. Debugging refers to the actual finding and correcting of code errors, whereas testing is the process by which errors are found. Testing is usually broken down by method. Individual methods are tested with a variety of inputs and execution parameters. This approach is called Unit Testing.

Designing Test Cases

As a minimal starting point when designing test cases, every line of code must be tested. Thus, if there are any decision structures in our method, we will need to define test cases that follow all possible branches of the code. Consider the following method:

Public Sub TestMethod (Byval bVal1 As Boolean, Byval bVal2 As Boolean)

If bVal1 = True Then

MsgBox (“Val1 is true”)

Else

MsgBox (“Val1 is False”)

End If

If bVal2 = True Then

MsgBox (“Val2 is true”)

Else

MsgBox (“Val2 is False”)

End If

End Sub

 

The number of test cases needed to run every line of code in this method is two: one case where bVal1 and bVal2 are true, and one case where bVal1 and bVal2 are false. Depending on the values of the parameters to be provided to the method in the code, the method execution could take paths not convered by the test case – for example, if bVal1 is True but bVal2 is false, the method execution will follow a path that was not convered in the test plan. To test all possible paths in this method requires two additional test cases: one where bVal 1 is True but bVal2 is false, and one where bVal1 is false but bVal2 is true. Therefore, at minimum our design for a unit test should include testing of all possible data paths.

Testing Data:

Testing the functionality of all possible data points is a good plan, but to make your application robust, you should also test whether it can handle different kinds of data. We want our application to behave normally and give expected results when data within normal parameters is provided, and it should gracefully and appropriately handle data that is outside of the specified bounds as well. Thus, to be thorough, we must test our application with a variety of data inputs ranging from normal to extraordinary. Specific types of data conditions used to create test cases are described as follows:

Normal Data:

It is important to test data that is within the normal bounds of program execution. Although it is usually impossible to test the entire range of normal data, your test cases should contain several examples of data that is normal for the program to process. This data should span the normal range of operation and should include the normal minimum and maximum values.

Boundary conditions:

Special considerations should be given to testing data on the boundaries of normal conditions. This includes the normal minimum and maximum values for program data, as well as values that are “off by one”. For example, to test the maximum boundary, we would include the maximum value, the maximum value minus one, and the maximum value plus one. This approach allows you to correct simple mistakes, such as using a > operator where a >= is required.

Example:

If e.RowIndex >= 0 Then

MsgBox DataGridView1(e.RowIndex).Cells(0).Value

End If

Bad Data

Using a variety of bad data in our test cases evaluates whether our program will crash in response to bad data, or, worse, function normally and return inappropriate results. We should test values that are well outside of the normal scope of operation, including zero for non-zero values, negative numbers for positive data, and so on.

Data Combinations

Our test plan should include a variety of combinations of the types of data. Errors might not be revealed until the correct data combination is used. For example, we might have a method that functions fine when each of its parameters is at its normal maximum, but fails when all of its parameters are at normal maximum. Similarly, combinations of known bad data can yield normal looking results. Consider the following method:

 

Public Function CalculateMySalary (ByVal Hours As Short, ByVal Weeks As Short) As Integer

Dim mySalary As Integer

mySalary = Hours * Weeks * WageConstant

End Function

 

Given that a normal range for Hours might be 0 to 40, and a normal range for weeks might be 0 to 52, this method functions normally with nominal input. However consider the following method call:

Dim result As Integer

Result = CalculateMySalary (-35, -50)

A combination of bad data returns a seemingly normal result. For this reason, our test cases should test a variety of possible data combinations.

To sum up the to do activities for an unit test plan

1. Begin by creating test cases that execute every line in the test unit

2. Add additional test cases until every possible path of data flow through the unit has been tested.

3. Add further cases to test variance in the data the unit will process. In addition to testing nominal data, we should test boundary conditions, bad data and different combinations of good and bad data

4. Determine the expected result of each test case in advance of the actual test.

5. Execute the tests and compare observed results with expected results.

read more “Creating a Unit Test Plan to test the .NET application”

String Functions in .NET

String manipulations are keenly important for many applications – converting strings to display formats, for instance, or extracting substrings from existing strings.

The String class exposes a variety of member methods for the manipulations of strings.

Some useful string methods in .NET Framework are:

Name                    Description

String.Insert             Inserts a specified string into the current instance

String.PadLeft          Adds characters to the left of the string

String.PadRight        Adds characters to the right of the string

String.Remove          Deletes a specified number of characters from the string, beginning at a specified character

String.Replace          Replaces all occurrences of a specified character in the string with another specified character

String.Split               Returns the array of substrings that are delimited by a specified character

String.Substring        Returns a substring from the specified instance

String.ToCharArray    Returns an array of the characters that make up the string

String.ToLower          Returns the string converted to all lower case

String.ToUpper          Returns the string converted to all upper case

String.TrimEnd          Removes trailing spaces from the string

String.TrimStart         Removes leading spaces from the string

String.Trim                Removes both trailing and leading spaces from the string

String.Compare         Compares two specified string objects

String.Concat            Returns a string that is a result of the concatenation of two or more strings

String.Format            Returns a string that has been formatted according to a specified format

String.Join                Returns a string that is the result of the concatenation of a specified array of strings, with a specified   separation string between each member of the array

read more “String Functions in .NET”

Dynamically align the Form in a screen based on users screen resolution

 

The following VB.NET Code Snippet is used to dynamically align the form to the center of the window, based on the the end users screen resolution.

Sub Center_Form()

Dim maxwidth As Integer = Screen.PrimaryScreen.WorkingArea.Size.Width

Dim left As Integer

Dim top As Integer

'Remove 10% top part of window

Dim maxheight As Integer = Screen.PrimaryScreen.WorkingArea.Size.Height - Screen.PrimaryScreen.WorkingArea.Size.Height * 0.1

left = (maxwidth - Me.Width) / 2

top = ((maxheight - Me.Height) / 2)

Me.Location = New Point(left, top)

End Sub

Call the function Center_Form in the Form_Load event of the form.

Private Sub DataForm1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

‘Center display

Center_Form()

End Sub

The following VB.NET Code Snippet is used to maximize the form.

Private Sub Maximize()

Me.WindowState = FormWindowState.Maximized

End Sub

The following VB.NET Code Snippet is used to minimize the form.

Private Sub Minimize()

<B>Me.WindowState = FormWindowState.Minimized</B>

End Sub

read more “Dynamically align the Form in a screen based on users screen resolution”

Import Flat Files into DataSet

In many legacy applications, data is stored in text files, sometimes called flat files. It might be necessary to read data from these files into an ADO.NET Dataset

The System.IO Namespace facilitates file access, and the methods exposed by the System.String class enable parsing the data contained in text files.

When reading data from a text file, you must first determine how the data is stored. Data stored in text files is usually separated by a common character called a delimiter. A delimiter can be a comma(,), a colon(:), a semicolon(;), or any other character. Rows of data are often stored as lines of text with a carriage return signifying the beginning of a new row. The number of entries in each row should correspond to the number of columns in the table.

Once the basic structure of the stored data has been determined, you can begin to construct your Dataset around it. You must create a single DataTable for each table of data you want to represent. Next, you should add the appropriate number of columns to your DataSet. Sometimes, the data in the file’s first row is used for the column names. If this is the case, you should read the first line of the text file and name the columns programmatically.

A text file can be read with the System.IO.StreamReader class. This class can open a file and return the characters represented within it. Once the file has been opened, you can use the method of the System.String class to separate the data entries and add them to the new data rows.

Because every flat file conceivably could have a different format, you must determine the correct procedure for accessing the data on an individual basis. The following code example demonstrates how to access data from a simple, common scenario: data stored in a text file where the rows are represented by lines and the entries are delimited by commas, a common format known as comma-Separated values (CSV).

Imports System.IO

‘Creates a new dataset

Dim myDataSet As New DataSet()

‘Creates a new datatable and adds it to the Tables collection

Dim aTable As New DataTable(“MyTable”)

‘Creates and names seven columns and adds them to MyTable

Dim Counter As Integer

Dim aColumn As DataColumn

For Counter = 0 to 5

aColumn = new DataColumn(“Column “ & Counter.ToString())

myDataSet.Tables(“MyTable”).Columns.Add(aColumn)

Next

‘Creates the StreamReader to read the file and a string variable to hold the output of the StreamReader

Dim myReader As New System.IO.StreamReader(“C:\Viji\MyTestFile.txt”)

Dim myString As String

‘Checks to see if the reader has reached the end of the stream

While myreder.Peek <> -1

‘Reads a line of data from the text file

myString = myReader.ReadLine

‘Uses the String.Split method to create an array of strings that represents each entry in the line.

myDataSet.Tables(“MyTable”).Rows.Add(myString.Split(“,”c))

End While

read more “Import Flat Files into DataSet”

Extender Provider Components in .NET Framework

 

Extender providers are components that impart additional properties to controls.

Take the ToolTipProvider for example.

When you place an instance of a ToolTipProvider on a form, every control on that form receives a new property. This property can be viewed an set in the Properties window, where it appears as ToolTip on MyTooltip, where MyTooltip is the name of the ToolTipProvider.At run time, the value of this property is displayed in a yellow box when the mouse hovers over a control.

Extender providers are usually used to provide information to the users at run time. As we have seen, the ToolTipProvider can be used to provide Tool Tips at run time.

Other extender providers include the

 

a) HelpProvider

b) ErrorProvider

 

To use an extender provider in your project

1. Add a component of the appropriate extender type (such as ErrorProvider) to your form. The component appears in the component tray.

2. In the Properties window, set appropriate values for the properties provided by the extender provider.

The properties provided by extender providers actually reside in the extender providers themselves, not within the controls they extend. Thus they are not true properties of the component and cannot be accessed in code at run time. The extender provider implements methods that can be used to access the properties it provides. By convention, these methods are always called Getprop and Setprop where prop is the name of the property provided.

Thus the ToolTipProvider implements methods named GetToolTip and SetTooltip, which can be used in code to access or dynamically change the value of the Tool Tip stored for a particular control. Both methods take a reference to the appropriate control as an argument, and theSet methods require a value to which the property is to be set.

To access the value of an extender property at run time

Use the Get method implemented for that property. You must supply a reference to the appropriate control.

Example:

Dim myToolTip As String

myToolTip = ToolTip1.GetToolTip(btnData)

To set the value of an extender property at run time

Use the Set method implemented for that property. You must supply a reference to the appropriate control and a new value for that property.

Example:

ToolTip1.SetToolTip(btnData, “Click me to display the data”

read more “Extender Provider Components in .NET Framework”

.NET Interview Questions - Part 9

After two weeks am back to blogging, to start of let me start the Part 9 of the .net Interview question.

1. Briefly describe the major components of the .NET Framework and describe what each component does. The .NET Framework consists of two primary parts: the Common language runtime, which manages application execution, enforces type safety, and manages memory reclamation, and the .NET base class library, which consists of thousands of predeveloped classes that can be used to build applications.

2. Briefly explains what is meant by a reference type and a value type.

A value type holds all of the data represented by the variable within the variable itself. A reference type contains a reference to a memory address that holds the data instead of the actual data itself.

3. How do you enable your application to use .NET base class library members without referencing their fully qualified names?

Use the Imports keyword (VB.NET) or the Using keyword(Visual c#) to make a .NET framework namespace visible to your application.

4. Briefly describe how the garbage collection works.

The garbage collector is a thread that runs in the background of managed .NET applications. It constantly traces the reference tree and attempts to find objects that are no longer referenced. When a non referenced object is found, its memory is reclaimed for later use.

5. Briefly describe what members are, and list the four types of members.

 Members are the part s of a class or a structure that hold data or implement functionality. The primary member types are fields, properties, methods and events.

6. Explain what constructors and destructors are and describe what they are used for.

The constructor is the method that initializes a class or structure and is run when a type is first instantiated. It is used to set default values and perform other tasks required by the class. A destructor is the method that is run as the object is being reclaimed by garbage collection. It contains any code that is required for cleanup of the object

7. Do you need to instantiate a class before accessing a shared (static) member> Why or Why not?

Because a Shared (static) member belongs to the type rather than to any instance of the type, you can access the member without first creating an instance of the type.

8. Briefly describe how a class is similar to a structure. How are they different?

Both classes and structures can have members such as methods, properties and fields both use a constructor for initialization, and both inherit from System.Object. Both classes and structures can be used to model real-world objects. Classes are reference types, and the memory that holds class instances is allocated on the heap. Structures are value types, and the memory that holds structure instances is allocated on the stack

9. You are writing an application that needs to display a common set of controls on several different forms. What is the fastest way to approach the problem?

Create a Single form that incorporates the common controls, and use visual inheritance to create derived forms

10. What is an extender provider, and what does one do?

Extender providers are components that provide additional properties to controls on a form. Examples include the ErrorProvider, HelpProvider and ToolTip components. They can be used to provide additional information about particular controls to the user or the user in the user interface.

read more “.NET Interview Questions - Part 9”

Best forum supporter for the week - 5th July to 11th July, 2009

Following my contribution at dotnetspider, i was awarded the winner of the week award

http://www.dotnetspider.com/forum/214520-Best-forum-supporter-for-week--th-July-th-July.aspx

Hello DNS'ians,

We are glad to announce, the Best Forum Supporter Award for the week 5th July,09 to 11th July, 2009 which goes to the following member.


1. VijayaLakshmi Rajkumar


Vijayalakshmi is a Diamond Member who joined DNS recently in the month of June, 2009, but she has contributed a lot of resources to DNs and has actively participated in the forums, and provided very good answers in the forum. She has contributed actively in the forum and provided very good answers to the questions.

See the sample thread where she clearly explains about Tableless Web Design with an example of how to display tableless html code. Here are some more threads where he has provided very good answers.

Sample Thread1
Sample Thread2
Sample Thread3

Vijaya Lakshmi has provided lots of answer to the questions asked by different members.

These are only a few answers.

I invite all the members to join us and Congratulate VijayaLakshmi Rajkumar on this

great achievement.

We are awarding her with Cash credit of Rs.100 and a

Certificate of appreciation.

The winner is requested to send her shipping address and her contact numbers to send the

certificates.

You can catch the other Winners list at :-->>
href='http://www.dotnetspider.com/credits'>List of DNS Award Winners




Raghav
Web Master

read more “Best forum supporter for the week - 5th July to 11th July, 2009”

What is SQL Injection

 

SQL Injection is an attack of non-valid inputs passed through web application for execution by a backend database , simply It is a trick to inject data to SQL query/command as an input possibly via web pages.

Best example for this, when a user login the web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and grant us.

SQL Query :

sqlquery = “ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ “ + strusername + “ ’ AND PASSWORD = ‘“ + strpwd + “ ’ ”;

sqlqueryresult = GetQueryresult(sqlquery);

if (sqlqueryresult = string.empty)

{

Response.write(“User login failed”);

}

Else

{

Response.redirect(“home.aspx”);

}

User passes ‘VIJI’ and ‘PASS’  as username and password respectively. If the user is a valid by executing the above SQL command, web page redirect to home page.

Look here, if user passes the below inputs Strusername as ‘ OR ‘ ‘ = ‘ and Strpwd as ‘ OR ‘ ‘ = ‘ then dynamic query will be

“ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ OR ‘ ‘ = ‘ AND PASSWORD = ‘ OR ‘ ‘ = ‘ “

Few judgment of this query:

• There is no syntax error
• There is no conflict between the operators.
• Inputs are not valid.

Web application will redirect the home page even input are invalid because result of the query will be true. The query compares the first single quotation and another quotation (means nothing) then OR is an operator. When comparing nothing to =, it returns true. Same execution is applied for password. These kind inputs are called vulnerable inputs to SQL commands.

Disadvantages

SQL injection provides a facility to the net hackers to pull the data from the backend database by supplying the vulnerable inputs.

Will Continue writing on the

Attacks of the SQL Injection

• Select Command.
• Insert Command.
• Using SQL Stored Procedures.

and how to prevent SQL injection in the upcoming articles.

read more “What is SQL Injection”

SQL Editor Shortcut Keys

SQL Editor is a code editor and explorer for SQL Server databases.

The following is a list of Shortcut keys used in the SQL Editor.

Shortcut Key	Function
F1	Windows Help File
F2	Toggle Full screen Editor
F3	Find Next Occurrence
<SHIFT> <F3>	Find Previous Occurrence
F4	Describe Table, View, Procedure, Function, or Package in popup window
F5	Execute as Script
F6	Toggle between SQL Editor and Results panel
F7	Clear All Text
F8	Recall previous SQL statement
F9	Execute statement
<CTRL> F9	Verify statement without execution (parse)
<SHIFT> F9	Execute current statement at cursor
F10	Popup Menu
<CTRL> A	Select All Text
<CTRL> C	Copy
<CTRL> E	Execute Explain Plan on the Current Statement
<CTRL> F	Find Text
<CTRL> G	Goto Line
<CTRL> L	Converts Text to Lowercase
<CTRL> M	Make Code Statement
<CTRL> N	Recall Named SQL Statement
<CTRL> O	Opens a Text File
<CTRL> P	Strip Code Statement
<CTRL> R	Find and Replace
<CTRL> S	Save File
<SHIFT> <CTRL> S	Save File As
<CTRL> T	Columns Dropdown
<CTRL> U	Converts Text to Uppercase
<CTRL> V	Paste
<CTRL> X	Cut
<CTRL> Z	Undo Last Change
<CTRL>.	Display popup list of matching tablenames
<SHIFT> <CTRL> Z	Redo Last Undo
<ALT> <UP>	Display Previous Statement
<ALT> <DOWN>	Display Next Statement (after <ALT> <UP>)
<CTRL><HOME>	In the data grids, goes to the top of the recordset
<CTRL><END>	In the data grids, goes to the end of the recordset
<CTRL><SPACE>	Completely expand dependency tree views
<CTRL><TAB>	Cycles through the collection of MDI Child windows

read more “SQL Editor Shortcut Keys”

DATA VALIDATION

Validation is a most important phase in a  data migration project, to ensure the data integrity between the legacy and the target systems.

Below is one case study  I've been involved to help the business with the validation process.

Client: A technology-based provider of power and automation products, systems, solutions, and services in the united States

History:

The Project is an  ERP(SAP) implementation project, and currently in the Go live phases, with 19 ERP system migrating in 6 phases.

Go live 1 through 3,the Data Migration team performed multiple loads into data migration specific SAP clients/systems in an attempt to have the businesses review, validate and cleanse their data. While the upside of this was that the data was in an SAP system for the businesses to review, the down side was that the turn-around time to correct errors and reload the SAP system was too long thus reducing the productivity of the Data Migration team.  Also no one could visually see the data that was to be uploaded.

 

Prior Model of Operation:

Cransoft is the tool used in tjis

To overcome this challenge, the Raj came along with the  data migration team came up with the new strategy called VLVP (Virtual load and validation process) during Go-Live 4. VLVP means that the legacy data, once extracted and transformed into SAP ready load formats, is exported to logical Excel spreadsheet formats and Access databases, also known as SIM (Simulation Sheets).

VLVP allowed the Data Migration team to provide, to the business, snapshots of the actual data to be loaded to SAP without actually loading the SAP system. It allows the data team to respond quicker to data object load specification changes presented by the businesses and shortens the load turn around time drastically.

 

Version1:

Version 1 was a simulated Excel sheets and access databases that were  interacting with the SQL server

Period of release: Go Live 4, Feb 2009

Designers:

1. RAJKUMAR (Excel,Access)
2. Ernie Blazek of  ABB had designed major of large volume data to be show via, Access

Model Of operation

	Pros of this version
1	Validate the data that is to be loaded into SAP , prior itself,
2	Office Tools part of every PC, so new installation requirement
3	Easier methodology to validate whether proper transformations are in place
4	Improved Data Quality
5	Higher Percentage of Success in load

	Cons of this version
1	Occupies huge space and high network on the Cransoft hosted network
2	Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users
3	Limitation of 65000 record with the Microsoft excel 2003
4	Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.
5	Back up of data not possible
6	Numerous of number of process locks in SQL server

The biggest  cons from the User and Developer point of view was that  SIM sheets consisted of various Excel spreadsheets and Access databases that resided on a network drive and the division teams were provided access to them. It became a strenuous process for the data migration team to educate the division teams on how not only to access the network drive; but which SIM sheets applied to their site; what versions of the SIM sheets were relevant; which SIM sheet naming conventions were applicable; whether a SIM sheet for a particular data object was a Excel spreadsheet or an Access database and why that was.

It caused a lot of coordination at times, where the objective of the SIM sheet was to simplify the validation process.

Some of the positive client Feedbacks(With out any Edition)

• Simulation sheets (SIM) worked well with ability to re-load data when needed.
• Continue with the SIM sheets for future sites, definitely see future sites benefitting.
• The SIM helped identify mapping issues before data was loaded to SAP.
• The error log produced by the SIM helped identify areas of legacy data that needed to be cleansed prior to the actual load of data in SAP.
• This was my first Go-Live on the Power Products (PP) OsA team, and I honestly cannot imagine how difficult the cutover must have been for the previous go-lives.
• Without the SIM and error logs, I do not believe we would have been able to begin cutover 3 days earlier than schedule or for the controllers to sign off 2 days ahead of schedule.
• Excellent tool for the cutover teams to identify data issues. Examples - Do purchase parts have planned delivery time?
• Excellent tool to see if cross reference logic is working as expected.
• Excellent tool to come up with remediation rules.
• Excellent tool to check data counts and see any glaring issues.
• Excellent tool to perform analytical analysis of data and provide recommendations to business on fixing data issues.
• Eliminated the unknown of guessing if our changes are going to work or not.

Version 2:

Period of release: Go Live 4.5, May 2009

Though the Sim sheets received  a positive feedback, Raj heeded the constructive criticism and changed the way the SIM sheets are presented to the division site data validators with a new user Interface application to house all SIM sheets that is far simpler, intuitive and easier to use. There is no switching between Excel and Access. The user is free of the tool is behind the user interface

Designed by:

1. RAJKUMAR

Programmed by

1. VIJI
2. RAJKUMAR

Model Of operation

 

 

Points of comparison	excel/Access	DATA VALIDATION TOOL
Occupies huge space and high network on the Cransoft hosted network	Yes	No
Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users	Yes	Real time
Limitation of 65000 record with the Microsoft excel 2003	Yes	No limitation
Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.	Yes	No. Its on vitual
Back up of data	Not possible	Possible

Result:

We are happy to report that the data migration team has not received one criticism of the new simulation sheet (SIM) application; in fact, numerous compliments; The new  SIM sheet application has allowed the users to focus  on validating the data and eliminated distractions in the validation process.

We heard a very  very positive feedback one of the business user saying, this is beyond the obvious.

 

 

Some of the Screen Shots of DATA VALIDATION Tool

The simple Home Page designed to access GO Live 5( 6 ERP systems ) and Go live 6( 3 ERP system) data

 

The list of  ERP sites sites on the left as a tree view

individual objects can  be displayed choosing  the appropriate node, you have options as filter and sort similar to Excel to work around other options are to view the data on a full screen as below

 

And  can export the data in the grid view to txt,csv or xls format. by clicking on the Export File option on the right hand screen or on the file menu. If you are more comfortable with Excel.

 

This is the xls File saved to my desktop

 

The Additional Feature of this tool is the error summary , which reports, the business object and the volume of records that would fail to make through into SAP when it is uploaded prior to even load, and the reason why they would not make through.This makes the tool a bit of artificial intelligence one too…

 

By Clicking on the Error Summary on the Main Menu, you can view the summary of the errors simulated  site wise with count, to view which all records would fail in detail, you can click on the View report.

Our next exploration  would be on  Extraction tool , Raj has the design  in place on the walls, i ve got to figure out a schedule to support him., more on this experience  i will blog once it comes to ready to use state. One thing is for sure, it is going to an interesting assignment. SAP ---> SQL ,JDE ----> SQL,MAPICS---->SQL, MACPAC---> SQL, SYTELINE ---> SQL, .. Oops there is a  lot

read more “DATA VALIDATION”

Creating Alias name for namespace

The namespaces in .NET can be very long and less obvious.

And to refer the class of the name space we need to include the entire path.

There is a way to simplify this by creating your own alias for the namespace.

How to do?

You can use the Imports statement (VB.NET) or using directive (C#) with an assignment operation.

Syntax:

VB

Imports < AliasName > = < NameSpace >

C#

Using < AliasName > = < NameSpace >


Example - VB


Imports MyaliasName = Microsoft.VisualBasic


Example - C#


Using MyaliasName = Microsoft.VisualBasic;


That way, you can refer to specific class elements within the class referred to using the variable you created in the assignment operation.


'VB

Private Sub TestFunction()

MyaliasName.MsgBox("I am Alias")

End Sub


// C#

void TestFunction()

{

MyaliasName.MsgBox("I am Alias");

}

read more “Creating Alias name for namespace”

Database Exception Handling Best Practices

This article would highlight on how to deal with exceptions in our code and the end result would be better code.

1. Validate data prior passing it to DB at client and server.

2. Left validation to DB and handle DB exceptions properly

3. Validate on both sides

4. Validate some obvious constraints in business logic and left complex
validation to DB

5. Strong type checking and validation are powerful tools to prevent unexpected
exceptions. So be ready to always check types, objects value etc before
passing to database.

6. Don't trust external data -External data is not reliable. It must be extensively checked.

7. Generic exception handling should be done in a central point in your
application. Each thread needs a separate try/catch block, or you'll lose
exceptions and you'll have problems hard to understand. When an application
starts several threads to do some background processing, often you create a
class for storing processing results. Don't forget to add a field for storing an
exception that could happen or you won't be able to communicate it to the
main thread. In "fire and forget" situations, you probably will need to duplicate the main application exception handler on the thread handler.

8. Log Exception.ToString(); never log only Exception.Message!

9. Cleanup code should be put in finally blocks

10. Don't use exception handling as means of returning information from a
method
11. Don't clear the stack trace when re-throwing an exception.

12. When in doubt, don't Assert, throw an Exception Don't forget that Debug.Assert is removed from release code. When checking and doing validation, it's often better to throw an Exception than to put an assertion in your code.
Save assertions for unit tests, for internal loop invariants, and for checks that should never fail due to runtime conditions (a very rare situation, if you think about it).

13. Use "using" everywhere -

Simply calling Dispose () on an object is not enough. The "using" keyword will
prevent resource leaks even on the presence of an exception.


14. Avoiding exception handling inside loops

Check if your code uses exceptions inside loops. This should be avoided. If you
need to catch an exception, place the try/catch block outside the loop for better performance.

read more “Database Exception Handling Best Practices”

DataTypes in C#

What is DataType?

A data type in a programming language is a set of data with values having predefined characteristics. It describes how the value will be stored for the type and how it will be processed.


DataTypes in c#:

The C# programming language is a strongly typed language. All variables must be declared with a valid data type.


Types of DataTypes:


There are two types of datatype:

1. Built In data type

2. User Defined data type


Built In data type:

The built in data types are provided by the c# .NET frameword. The data types are reserved keywords and cannot be used as a variable name.

Built in data types supported in c# are:

byte - 0 to 255 characters

sbyte(short byte) - (-128 to 127)

short - 32,768 to 32,767

ushort(unsigned short) - 0 to 65,535

int -2,147,483,648 to 2,147,483,647

uint(unsigned int) 0 to 4,294,967,295

long -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

ulong 0 to 18,446,744,073,709,551,615

float - (-3.402823e38 to 3.402823e38 )

double - ( -1.79769313486232e308 to 1.79769313486232e308 )

decimal ( -79228162514264337593543950335 to 79228162514264337593543950335 )

char - A Unicode character.

string - A string of Unicode characters.

bool - True or False.

object - An object.


User Defined data type


The user defined data type, will be defined by the user.

Example:

Class or Interface

Enumerations - user defined integer type


The data types can further be classified into

a) Value Types

b) Reference Types


Value Types:

The value type variables store the actual data in the variable. Each variable maintains its own copy of data.


Example:

int x = 10;

double y = 10.12;

char z = 's';


The following value types are supported in c#:

* int
* uint
* bool
* char
* decimal
* float
* double
* sbyte
* byte
* char
* short
* ushort
* ulong
* long
* decimal

* structs
* enumerations


Reference Types


Reference types variables store the reference of the actual data.


The following reference types are supported in c#.

* string - represents a string of unicode characters.

* object - Represents a general purpose type. In C#, all predefined and user-defined types inherit from the object type or System.Object class.

* class

* interface

* delegate


Example:

string s = "TestString";

Although string is a reference type, the equality operators (== and !=) are defined to compare the values of string objects, not references.

public delegate void TestDelegate(string message);

The delegate keyword is used to declare a reference type that can be used to encapsulate a named or an anonymous method


Data Type Conversions:

C# supports two types of conversions.

1. Implicit conversions

2. Explicit conversions.


Implicit conversions are direct conversion. It includes method invoking and assignment.


For example:

int iVal = 34;
long lVal = intValue;

Explicit conversions includes type casting. By using a cast expression for conversion.

For example:

long lVal = 123456;
int iVal = (int) lVal;

read more “DataTypes in C#”