DotNet technical blog: October 2009

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>

</Details>

-
<Details>

</Details>

-
<Details>

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

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

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]

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]

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

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)