Tuesday, December 1, 2009

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


 

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


 

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


    image 

  • image 
     

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


     image

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()

Thursday, November 19, 2009

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 &amp;

<NODES>

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

</NODES>

.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"

.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.

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.

Tuesday, November 17, 2009

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.

clip_image002

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

clip_image004

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

clip_image006

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.

Monday, October 5, 2009

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. Ex: If the year is 2010, the date would be '1/1/2010'


     

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


     


  3.  

  4. 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

Friday, October 2, 2009

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.




Thursday, October 1, 2009

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)