Here is a sample source code on how to read and load xml file via LINQ, extract the data [using a query] and export it to a CSV file.

The XML file (employee.xml)

<xml>
<Employee id=”1″>
<Name>John Doe</Name>
<Position>President</Position>
<Salary>$500,000</Salary>
</Employee>
<Employee id=”2″>
<Name>Jane Doe</Name>
<Position>Vice President</Position>
<Salary>$250,000</Salary>
</Employee>
<Employee id=”3″>
<Name>Jen Doe</Name>
<Position>Manager</Position>
<Salary>$50,000</Salary>
</Employee>
</xml>

The code

‘Libraries
Imports System
Imports System.IO
Imports System.Text
Imports System.Xml.Linq
Imports Microsoft.VisualBasic.FileIO

‘=====================================

‘Read/Load xml file
Dim employees As XElement = XElement.Load(”employee.xml”)
Dim outputData As New StringBuilder

‘query on loaded xml data and output as String Enumeration
Dim xmlData = _
From emp In employees.<Employee> _
Select String.Format(”"”{0}”",”"{1}”",”"{2}”",”"{3}”"”, _
emp.@id, _
emp.<Name>.Value, _
emp.<Position>.Value, _
emp.<Salary>.Value)

‘Traverse enum and append extracted data
For Each row In xmlData
outputData.AppendLine(row)
Next

‘Output as .txt file
File.WriteAllText(”ParsedData.csv”, outputData.ToString)

The output as CSV file (ParsedData.csv)

“1″,”John Doe”,”President”,”$500,000″
“2″,”Jane Doe”,”Vice President”,”$250,000″
“3″,”Jen Doe”,”Manager”,”$50,000″

• 0 Comments

Tags: asp .net tutorial • vb .net sample code

This is a sample function on how to move a file from one folder/directory to another while considering some factors such as:

• Is the file available? Or is it being used by another process?
• Does the file exists?
• Does a file with the same filename exists in the destination folder?

Move File Function

‘fromFile and toFile parameters consist of directory-path+filename+extension
Public Function MoveFile(ByVal fromFile As String, ByVal toFile As String) As Boolean

‘Check if File do exists
Dim fileExists As Boolean = System.IO.File.Exists(fromFile)

‘Check if File is being use by other process
Dim fileIsLock As Boolean = IsFileLocked(fromFile)

‘If file do exists and is not lock, begin transfer
If fileExists AndAlso Not fileIsLock Then

‘Check if file already exists in the destination folder
If System.IO.File.Exists(toFile) Then

‘delete file from destination folder and move new file [overwrite]
System.IO.File.Delete(toFile)
System.IO.File.Move(fromFile, toFile)
Return True

‘Of course you can always check the destination file information,
‘and maybe decide whether to delete it or not and just rename the new file
‘Example by getting file information
Dim destFile As New FileInfo(toFile)

‘Get size [in bytes]
If destFile.Length > 0 Then
‘decide what to do here if the destination file has some contents
‘you might not want to overwrite it if it contains important data
End If

Else
‘If the file does not exists in the destination folder, just move
System.IO.File.Move(fromFile, toFile)
Return True
End If
Else
Return False
End If

End Function

Simple Function to check if file is locked

Public Function IsFileLocked(ByVal strFullFileName As String) As Boolean
Dim returnVal As Boolean = False
Dim fs As System.IO.FileStream = Nothing

Try
fs = System.IO.File.Open(strFullFileName, IO.FileMode.OpenOrCreate, IO.FileAccess.ReadWrite, IO.FileShare.None)
Catch ex As Exception
returnVal = True
Finally
If fs IsNot Nothing Then
fs.Close()
End If
End Try

Return returnVal
End Function

• 0 Comments

Tags: vb .net sample code

When working on customized Gridview (especially when you’re planning to use it multiple times within your solution), you can always create your own class by extending the default Gridview.  So instead of putting all properties inside <asp:GridView> tag like this:

<asp:GridView ID=”MyOwnGridViewID” runat=”server” CssClass=”MyOwnGridViewClass” AllowPaging=”true” PageSize=”25″>
<!– As well as other properties here –>
</asp:GridView>

You can simply call:

<asp:MyOwnGridView ID=”MyOwnGridViewID” runat=”server” />

Here’s the code for your class (in VB):

Public Class MyOwnGridView Inherits GridView

Public Sub New()
Me.AllowPaging = true
Me.CssClass = “MyOwnGridViewClass”
Me.PageSize = “25″
…etc
End Sub

End Class

The code is neat and easy to debug.

• 0 Comments

Tags: vb .net sample code

Sometimes, we wanted to return our query result as datatable for easy handling of data [probably making it as a datasource of a control].

Here’s a simple function:

Public Function GetDataTable(ByVal conStr As String, ByVal qryStr As String) As DataTable

Dim dt As DataTable = New DataTable()
Dim con As SqlConnection = New SqlConnection(conStr)

Dim adapter As SqlDataAdapter = New SqlDataAdapter(qryStr, con)

Try
‘fill datatable
adapter.Fill(dt)
Catch ex As SqlException
System.Web.HttpContext.Current.Response.Write(”SQL Error!!!”)
Finally
con.Close()
End Try

‘return the data table.
Return dt

End Function

Then, you can use it like this:

myDataGrid.DataSource = GetDataTable(myDbConnection, myQuery)
myDataGrid.DataBind()

• 0 Comments

Tags: .Net asp .net tutorial • mssql sample script • vb .net sample code

I was digging through my files when I found this subroutine and I thought, maybe somebody out there might need it. It’s not my original code though (and I forgot where I got it). Anyhow, I just wanted to share it and here it is:

Public Sub ExportToExcel()

Dim table as Datatable = “Query your datatable here”
Dim name as String  = “filename”

Dim context As HttpContext = HttpContext.Current
context.Response.Clear()
For Each column As DataColumn In table.Columns
context.Response.Write(column.ColumnName & “,”)
Next
context.Response.Write(Environment.NewLine)
For Each row As DataRow In table.Rows
For i As Integer = 0 To table.Columns.Count - 1
context.Response.Write(row(i).ToString().Replace(”,”, String.Empty) & “,”)
Next
context.Response.Write(Environment.NewLine)
Next
context.Response.ContentType = “text/csv”
context.Response.AppendHeader(”Content-Disposition”, “attachment; filename=” & name & “.csv”)
context.Response.[End]()

LoadData_Base()
End Sub

• 0 Comments

Tags: vb .net sample code