There are many ways to export data from SQL Server to Excel but I think the one way to do it by real simple coding is to export the data as an XML file and then opening it in Excel.

First we fill the data in a DataSet and then export that data to an XML file. Although the file is an XML one, I give it an extension of ‘.xls’ so that Excel opens it by default and then it can be saved it as the Excel format in some other sheet.

Dim con as SqlConnection
con = New SqlConnection(“Data Source=SERVER; Initial Catalog=dbTest;uid=sa;pwd=123″)
Dim cmd As SqlCommand = New SqlCommand
con.Open()
cmd.CommandText = “spExportData”
cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(ds)
Dim savefileName As String = Application.StartupPath & “\ExportedFile.xls”
ds.WriteXml(savefileName)
con.Close()
cmd.Dispose()
da.Dispose()

If in a table, some columns occasionally contain null values, then while trying to retrieve the values, the following error is shown:

Conversion from type ‘DBNull’ to type ‘String’ is not valid.

To solve this problem, we can check if the Datareader has a null value for the column, and if yes, then just getting the TextBox to show an empty string.

If DBNull.Value.Equals(dataReader1(“Column_Name”)) Then

TextBox.Text = “”

Else

TextBox.Text = dataReader1(“Column_Name”)

End If

Microsoft today revealed details of Visual Studio 2010 (code-named “Rosario”) and .NET framework 4.0. This next generation ‘Integrated Development Environment’ focuses on the following five points as given in the Visual Studio 2010 webpage.

• Democratizing Application Lifecycle Management
• Enabling emerging trends
• Inspiring developer delight
• Riding the next generation platform wave
• Breakthrough Departmental Applications

In related news, Scott Guthrie yesterday announced on his blog that from now on  jQuery will be distributed with Visual Studio (including jQuery intellisense, snippets, examples, and documentation). This is a real exciting news since jQuery is a great lightweight library for Javascript, widely used by developers.

Hi everyone

With the release of .NET framework 3.5, Microsoft introduced lots of new features. One of the very important feature introduced is LINQ, which stands for Language Structured Integrated Query. I have been studying LINQ since the last 2 months and I find it an excellent approach to data management. In this post, I will give a brief overview about it.

While writing a software, at some point of time we write code which deals with data. The data can be in any form; files stored locally on a computer, tables in a database or even XML documents. While dealing with data when we are creating applications using .NET, at times we may have to carry objects to a database and then load the results back to the object after querying the database.

The following pseudocode will explain this approach.

Students Stu = New Students();   // A class which represents a table of students
Stu.stuID = 5;  // Setting the row identifier to 5
Stu.Retrieve();   // Retrieving Row’s data where Student ID = 5
Stu.Name = “Pushkar Arora”;   // Changing the column ‘Name’ value to Pushkar Arora
Stu.Update();   // Updating the Database data

A real cool and simple approach, isn’t it? Now to implement this, in ADO.NET we do the following. (more…)