jump to navigation

Create an XML file using LINQ and import XML data into an Excel spreadsheet March 31, 2009

Posted by fofo in LINQ, MS Office, VB 9.0, Visual Studio 2008.
Tags: , , , , ,
6 comments

Hey all,

You must think that this post title is a rather long one. Yes it is, but the concepts are rather easy and I have been posting lots of stuff regarding LINQ to SQL,VB.Net, XML Literals, e.t.c.

A friend of mine had a project which required to get some data from an SQL Server database table, create an XML document from the relational data and import it to an excel file. Another thing to bear in mind here is that we needed to create the excel file programmatically.

I tried to help him out and then thought that it might be a good idea to write a post about the solution I proposed.

So I will try to show all these, by (a hands-on example) building a windows form application using VB 2008(9.0).

I am going to use Visual Studio 2008 professional but Visual studio 2008 Express edition will do just fine.You can download Visual Studio 2008 from here .

Obviously for this example we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.You can download the Pubs database from this link. If you need some help on how to install the database have a look here .

I have installed SQL Server 2008 Standard edition in my PC. SQL Server 2008/2005 Express editions will work just fine.You can download SQL Server 2008 Express edition from this link. I have attached the Pubs database in my local instance of the SQL Server.

In order to follow along you must have all the above running smoothly in your pc.It will be great for you in order to better understand what I am about to show you, If you are familiar with LINQ to SQL and XML Literals.

If you want to read another post of mine regarding XML Literals click here .

If you want to read another post of mine regarding LINQ to SQL click here

So let’s start…

1) Lauch Visual Studio 2008

2) Create a new project. From the available templates choose “Windows form Application”. Make sure VB is selected as your language of choice for this project.

3) Give your project a suitable name, e.g “sqltoxmlandimporttoexcel”

4) We will create the excel file programmatically first.In the form (Form1) just drag and drop a button on it. Name it “btnCreateExcel”.

5) Go to your c:\ drive and create a folder.Name this folder as you want. I have named this new folder after my name, thus nikos. So I have a path of “c:\nikos”. In this folder I will place the excel file that I will create programmatically.

6) Double click on the button. In the event handling routine that is created by VS 2008 for us, paste the code below

Dim xlApp As excel.Application

Dim xlWorkBook As excel.Workbook

Dim xlWorkSheet As excel.Worksheet

xlApp = New excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add

xlWorkSheet = xlWorkBook.Sheets(“sheet1″)

xlWorkSheet.Cells(1, 1) = “XML Code starts here”

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)

xlWorkBook.Close()

xlApp.Quit()

xlApp = Nothing

MsgBox(“Your Excel file has been created successfully”)

As you see I have called my excel workfile, “mydataxml.xls

7) You must do 2 two things in order for this code to run. First you must add a reference to the Microsoft Excel Interop Library which can be found in the COM components tab.

  • Right-click on your Project and select “Add reference” and then from the “Add reference” window, Select the COM tab. Scroll down till you find the Microsoft Excel 11.0 Object Library and click OK.

Have a look at the picture below.

excel-com-library-new

  • Add this line of code just above the “Public Class Form 1″

Imports excel = Microsoft.Office.Interop.Excel

Public Class Form1

8) Run your application by hitting F5. Click on the button and then check on your specified folder to see if the excel file has been created.

9) Now we need to query our database table. We are going to use this by using LINQ to SQL. Go to your Solutions Explorer, select your project and then right click and choose “Add new item” and choose LINQ to SQL classes. Name the .dbml file as Pubs.dbml. If you open the Pubs.dbml file, you will see the O-R designer surface.From your Server Explorer window, click to the Connect to Database button. Select the Sql Server instance name and select the Pubs database and hit the OK button.See the picture below:

add-connection-new

10) After establishing the connection with the Pubs database, expand the Tables node and drag – drop the Authors table in the O-R designer surface. An “Author” data class is automatically created for us.

11) Place another button on your form. Name the button “CreateXML”. Double click on the button. In the event handling routine type the code below. I am trying to get all the rows of the authors table and save them in an .xml file.

Dim mydb As New PubsDataContext

Dim myauthors = <authors>

<%= From author In mydb.authors Select _
<author>
<lname>

<%= author.au_lname %>
</lname>
<fname>

<%= author.au_fname %>
</fname>

<phone>

<%= author.phone %>
</phone>
<address>

<%= author.address %>
</address>
<city>

<%= author.city %>
</city>
<state>

<%= author.state %>
</state>

<zip>

<%= author.zip %>
</zip>

<contract>

<%= author.contract %>
</contract>

</author> %>

</authors>

myauthors.Save(“c:\nikos\myxmldata.xml”)

12) As you see what I am doing is pretty simple. I type XML statements that form the structure of my XML document using LINQ to SQL expressions to query the relational data.If you run your application and click the “CreateXML” button, you will have in your specified folder (c:\nikos\ , in my case) a document called “myxmldata.xml”. Browse to the specified folder and select the “myxmldata.xml”, right-click on this file and Open it with Internet Explorer. You will see all 23 rows of data that resides in the Authors table to be represented as nodes in our newly created xml file.

13) So now we are on the final part of our solution. Yes you guessed it!!! We will place another button on the form. We will call it “SaveXMLtoExcel”. By clicking on this button, all the data from the xml file should be placed in the excel file we created in the first steps of this example.

14) Double click on the “SaveXMLtoExcel” button and in the event handling routine ,and paste the code below. Do no forget to include the Imports System.Xml namespace at the beginning of your Form1.vb file.

In this routine I get an instance of the existing excel file and by using the XMLReader class and the ReadXml to read the xml data into a dataset.Then I use the dataset to populate the rows and columns of the specific worksheet.

Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet

Dim ds As New DataSet
Dim xmlFile As XmlReader
Dim i, j As Integer

xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(“c:\nikos\mydataxml.xls”)
xlWorkSheet = xlWorkBook.Sheets(“sheet1″)

xmlFile = XmlReader.Create(“c:\nikos\myxmldata.xml”, New XmlReaderSettings())
ds.ReadXml(xmlFile)

For i = 0 To ds.Tables(0).Rows.Count – 1
For j = 0 To ds.Tables(0).Columns.Count – 1
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

xlWorkSheet.SaveAs(“C:\nikos\mydataxml.xls”)
xlWorkBook.Close()
xlApp.Quit()
xlWorkBook = Nothing
xlApp = Nothing

GC.Collect()

15) Run your application. Hit the “SaveXMLtoExcel” button. Open the excel file and hopefully you will see 23 rows of data that represent authors data

I am sure you can realise that you could combine the whole functionality in one button or routine, but I broke the code into various parts so it is easier to read. I think we have covered lots of things in this post. We talked about XML,XML literals,LINQ to SQL and how to create excel file from XML data.

Hope it helps!!!

Happy reading!!!

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

LINQ TO SQL and Stored procedures December 13, 2008

Posted by fofo in c# 3.0, LINQ, SQL Server 2008, Visual Studio 2008.
Tags: , , ,
8 comments

In this post, I will go back to the issue of LINQ. I am going to show with an example how to use LINQ and stored procedures to insert,update and delete records from a particular table.

I will use visual studio 2008 to create an asp.net application (c# version) that demonstrates how to use stored procs with LINQ.

If you want to see how LINQ treats stored procedures , have a look here

I am going to use the Pubs datatabase and in particular the Authors table.

I am pretty sure, because I have used the Pubs database in almost all my examples that you have installed it and attached it in your local instance of Sql Server.

You can use Visual Studio 2008 express edition and Sql Server express edition for this example.

We often need to use stored procs to talk with our database because there have many benefits

for example

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.

First we need to create the 3 stored procedures that will insert,update and delete records from the Authors table

This is not a post on how to create stored procedures, so i am just going to paste here the complete stored procs.

  • DeleteAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteAuthor]

@AuthorID nvarchar(20)

AS
BEGIN

DELETE FROM authors
WHERE au_id = @AuthorID

END
GO

  • UpdateAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateAuthor]

@authorID varchar(11),
@lname nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

UPDATE authors
SET

au_lname=@lname,
au_fname=@fname,
phone=@phone,
address=@address,
city=@city,
state=@state,
zip=@zip,
contract=@contract
WHERE au_id  = @authorID

END
GO

  • InsertAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertAuthor]
@id varchar(11),
@lName nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

INSERT INTO pubs.dbo.authors(
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract)
VALUES (
@id,
@lname,
@fname,
@phone,
@address,
@city,
@state,
@zip,
@contract)

END

GO

1) Launch Visual Studio 2008

2) Create an ASP.net web application. Use C# a your language of development

3) Name your project – solution as you want.

4) Open the Server Explorer and connect to the Pubs database.

5) Add a new item in your project, a Linq to SQL classes, a .dbml file. name it authors.dbml

6) Drag and drop from the Server explorer window the authors table into the designer area (on the .dbml file)

7) Right-Click on the designer area and show the “Show methods Pane”

8) Drag and drop the stored procedures from the Server explorer to the designer area

9) Select the author entity from the deisgner area and select the Properties window. In the Default methods we need to assign the correct stored procs and not to leave the default option which is “use Runtime”. So please assign the different methods to their respective stored procs. Have a look at the picture below

sql-linq

10) We will use these stored procs that are methods now as far as LINQ is concerned to update,insert and delete records from the database.

11) Add a button in the Default.aspx file. Name it “Update”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766″
select a).Single();
Response.Write(author.au_fname);
author.au_fname = “nikolaos”;

authdata.SubmitChanges();

}

12) Add another button in the default.aspx page. Name it “After Update”.In this routine we try to see the updated value in the database.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766″
select a).Single();
Response.Write(author.au_fname);

}
12) Add another button in the default.aspx page and name it “Insert”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{

authdata.InsertAuthor(“216-49-8915″, “Jones”, “Michael”, “432423424”, “james street 123″, “New york”, “NY”, “94618”, true);

authdata.SubmitChanges();
}

13) Add another button in the default.aspx page and name it “Delete”. Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “216-49-8915″
select a).Single();
authdata.DeleteAuthor(author.au_id);
authdata.SubmitChanges();
}

if you named you .dbml file “authors”, then there is a file “authors.designer.cs”. Inside there you will find this code

private void Insertauthor(author obj)
{
this.InsertAuthor(default(string), default(string), default(string), obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Updateauthor(author obj)
{
this.UpdateAuthor(obj.au_id, obj.au_lname, obj.au_fname, obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Deleteauthor(author obj)
{
this.DeleteAuthor(default(string));
}

The methods above, are our stored procedures , which are called whenever we insert,update,delete

Hope it helps!!!

If you need the source code just email me.

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

How to monitor and debug LINQ to SQL queries November 19, 2008

Posted by fofo in c# 3.0, LINQ, Visual Studio 2008.
Tags:
1 comment so far

In one of my previous posts where I tried to explain how to query an Sql Server database with LINQ to SQL, I said that LINQ to SQL queries get translated into commands that SQL Server understands. The LINQ runtime knows how to translate LINQ to SQL queries to T-SQL, the only language SQL Server knows!

We can see the communication between the Runtime and SQL Server using several tools.

Some tools are built into the Visual Studio 2008 sp1 for looking into the results of a LINQ query.

If you follow the exact same steps of my previous post until step 8 (the first example), then

you can put a breakpoint in this line

var tauthors = from ta in db.titleauthors
select ta;

run your application now and then press F11 to move one step forward from the above statement

Then place your cursor in the tauthors variable and select the Results View.

It has all the instances of author entity that has been retrieved from the database and populated in the titleauthors class.

You can go throuch each one of them and their respective private fields.

see the picture below and consider it as a visual aid

resultsview

That is one approach viewing-debugging the results before they actually executes

There is another tool available.

Fire the browser of your choice and type the following url from Scott’s Gu website

http://www.scottgu.com/blogposts/linqquery/sqlserverqueryvisualiser.zip

Scott includes this tool as an add-on on his website.

Download this tool to your desktop and then unzip it.

Go then to the Debug/bin folder , copy the SqlServerQueryVisualizer.dll and place it in the following folder

C:\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers

Save all your work. Close your project and exit Visual Studio

Fire up Visual Studio 2008 again and open your project again.

If you put a breakpoint in the same line,  run your application, press F11 and place your cursor above the

tauthors variable. You will see a magnifier icon. Click on it. You will see a screen that looks like the one below

scottvisualiser1

When you click on the magnifier icon, you will see the SQL Server Query Visualiser which shows the query that is going to be executed.

You can hit the “Execute” button to preview the results before they executed.If you want to see when commands are executed you can use a different tool called LINQ to SQL Debug Writer.

LINQ to SQL Debug Writer is basically just a class that you add to your applications that outputs the generated T-SQL to the Debug window.

So you need to add another class to your project. Name it “class1.cs”

clear all the code in the class file and go to the following url

http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?List=4a6d97a9-b3b0-45a7-88a0-1a7f4819a678&ID=11&Source=http%3A%2F%2Fwww.u2u.info%2FBlogs%2FKris%2FLists%2FPosts%2FAllPosts.aspx

You will need to copy all the contents from this line

Here’s the code:

until the end…

and place it in the class1.cs file.

make sure you change this line inside the above file

namespace Vandermotten.Diagnostics

with whatever you named your project.

Then you need to add this line

db.Log = new DebuggerWriter();

immediately after this line

pubsDataContext db = new pubsDataContext();

Save and run your application by placing the breakpoint in the same place.

Clear all windows (like watch,locals) and just make sure you have the “Output” window open.

Step into your code. You can see in the output window, LINQ to SQL runtime, generates the T-SQL and sends the statement to the SQL SERVER.With this tool we can see both what is executed and what is executed.

We can also use the SQL Server Profiler to watch the traffic between LINQ to SQL and SQL Server.

Select Sql Profiler from the Performance Tools, start a new trace,connect to your SQL SERVER ,accept all the defaults and hit the Run button.

If you run the application again and have this break point and run the code step by step,you can see the entire T-SQL statement that has been executed in the SQL profiler window.

Hope it helps…..

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Follow

Get every new post delivered to your Inbox.

Join 1,792 other followers