From my last post SQL Parameters in C# I mentioned that I wanted to learn an ORM package.  Well I took a quick glance at NHibernate and got overwhelmed and decided to take a look at SubSonic as I have heard a lot of good things about it.  Well I am happy to say I doubt I will EVER do database access by hand ever again if I can help it.  As such I figured I would make a code post on some SubSonic code.  I am not really going to explain it just want to show you how easy, cool and simple it is.  I'll show the code starting with my .aspx main aspx page code, the code behind then the data access layer code.

I am also including the project I used too.  Be sure you have Northwind database.

I suggest you download, install, and start using SubSonic.  It is a great framework to help generate and create your DAL.  Once you have it I suggest you watch the screencast on the using the Query Tool.  If you would like me to explain this code i'd be more than happy to just let me know and I'll make another post.

SubSonicTest.zip (188.54 kb)

Until I figure out how to use an ORM package, which might be soon, I will continue to do direct database CRUD code.  One thing I have heard which helps make code more secure is using SQL Parameters.  Well this was a good idea until I tried to figure it out.  Over the last couple of years I have just tried to find the code to get it to work.  With a project I just started yesterday it I decided to figure out what was going on and now I want others to know as some stuff on the web, tutorial wise, can be quite confusing.

Getting Started 

I am only going to show you them method that was used to do the database stuff and for the sake of this blog post I am going to assume you know how to use the data reader.  If you don't please let me know and I will make a post on it specifically.  There are basically 3 lines I want to cover with you and explain what they are doing to help you understand what is going on and how to use parameters in your SQL Queries.  First though the code.

Now that you have had a chance to review the code a bit lets take a look out our query.

 string query = "SELECT * FROM TestTable WHERE ID = @id";

This is a normal select statement but notice the WHERE ID = @id this the @id signifies it is a parameter to be used and what ever is passed to it will be like normal query without it.  Think of it as a variable that you would use.

 SqlCommand command = new SqlCommand(query, conn);

This is an object we use that will help us build up our query and execute it against the database.

 command.Parameters.Add("id", SqlDbType.Int).Value = id;

This here is the main part of what we are after.  First, we are using the SqlCommand object and in the object it has a Parameters collection that holds all the parameters a query might have.  Since it is a collection we can just call the Add method and the first parameter that metho takes tells the object the parameter name is id for the @id in our query string.  The second parameter the method takes is they datatype which it uses a Struct called SqlDbType to help you fill that it so it is fairly easy to do.  Next we can give the sql parameter a value in this case the id we passed to the method.

Please take a moment to really compare and look at what is going on by understanding what it is doing you can better know how to use it so you don't need to look it up everytime.

Wrap-Up 

This was meant to be quick so you can get an idea of what is going on.  I am including a sample project with the post please feel free to download it.  I have included sql scripts to create the database, create the table and add data to the table.  Please just run those scripts then edit the connString variable with the write server name for you sql server.  Just to note I did this in .NET 3.5.

DatabaseParameter.zip (7.41 kb)

Seeing a list of SQL Server Reporting Services reports from Report Explorer WebPart is pretty simple.  Set the “Report Manager URL” property to the URL of the report manager.  In my case http://localhost/Reports.  What I couldn’t figure out for a while was viewing a report that I clicked on from the Report Explorer WebPart in the report viewer WebPart.  This is actually simple once I figured it out.  Here are the steps.  This assumes both web parts are added to the same page.  And the Report Explorer is configured.  For help on that click HERE.

1) Click Edit “Site Actions” top right of page
2) Edit Page
3) On the Report Viewer WebPart click edit
4) Go to connections from the popup menu (follow next path in popup menu)
5) Connections -> Get Report From -> Report Explorer
6) Exit Edit Mode, top right of page

There you go you should now be able to click on a report in the Report Explorer and view it in the Report Viewer WebPart.  Hope that helps.

One HUGE problem I have run into while messing with SharePoint Services and Reporting Services is lack of 3.0 information. 

I have been working the last 2 days on getting SSRS and WSS 3.0 both working and getting them to work together.  I am now a bit closer to this with the addition of adding the Reporting Services WebParts to SharePoint then getting a list of reports.  Here are the steps I followed to get it all working. (note: yours could be different)

1) Install SQL Server 2005 and with Reporting Services (Advanced SQL Server Express)
2) Install SQL Server Toolkit ( with Business Intelligence Developer Studio, just a good idea)
3) Run the Report Services Configuration Tool and set the web application to be under the SharePoint.  Pay attention to this post when doing this step.
4) Create a new Reporting Services Database.  (Look at this post)
5) I found the .cab file for the Reporting Services Web Parts to add to SharePoint  on my machine here: “C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint”
6) With the cab file I run this command using stsadm.exe from this folder “C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN”
7) Once that is complete open up SharePoint in your browser and add the Report Explorer WebPart.
Click edit web part -> modify shared web part on the Report Explorer web part.
9) added the following URL to “Report Manager URL” http://localhost/Reports
10) hit ok button. 
11) See a list of available reports.

What I don’t have figured out yet is viewing these reports in the report viewer web part.  I also don’t know if that is even possible. yet.  So stay tuned.

So I just spent quite a bit of time on another issue, that seems like there is no solution to and you can’t seem to find info on anywhere.

Basically, you assume when doing database setup that you just grab the default database you want to run reports off of and click ok.  Well that is what I did, then it came back with an error.  The error said that there wasn’t the correct role permissions for the role “RSExecRole.” Anyway, I finally found something similar here and assigned the role and correct permissions and moved on to the next error.  Well there were 2 errors one was a wrong connection string and the second was incorrect user.  Well neither was coming up in a Google search.  Finally, someone heard me complaining enough to myself to come help.  They helped point out the wrong connection string.  Well when someone is in my office others get curios.  Well one of the guys that seems to have done everything walks by and asks what’s going on we tell him the problem and boom he solves it in 2 minutes.

Here is how and why he solved it.

1) Clicked on the Database Setup tab
2) Clicked New…
3) Clicked Ok
4) Click Apply
5) Click Ok

That was it.

Here is the why.

In order for the report services to work it needs its own database with database with certain things in it.  Without this database it doesn’t work at all.  So you must create this database during the configuration.  The message at the top of that page says this, but is so “Technical”ish that it doesn’t make much sense at all.

I just spent about 4 hours trying to figure out why in the world I couldn’t get the “Report Manager Virtual Directory” which was red to go green.  Well I figured it out.

If you are looking at your “Reporting Services Configuration Manager” and have “Report Server Virtual Directory” with a green check mark automatically then you will not be able to get the rest to work.  You HAVE to configure “Report Server Virtual Directory.”

So basically  to get it to work follow these steps.

1. Click Report Server Virtual Directory tab on the side.
2. Click the New… button.
3. Choose the Web Site.  ( I just chose Default Web Site)
4. Click Apply

After doing that it should auto configure the rest until you hit Database Setup and that is pretty straight forward.  Part of my problem is I went straight to the red without looking at anything else.

Hope this saves someone a couple of hours.

In SQL 2000 and previous there was Northwind which was your dummy database that you could use if you need to do database stuff, but didn’t have a database.  Well with the release of SQL 2005 they came out with a new Database called AdventureWorks.  Problem is it doesn’t come with SQL Express so you have to find it.  Well I finally found the link and am sharing it with you all.

Download: AdventureWorks Database

Edit: I just figured out it got moved yet again to Code Plex

http://www.codeplex.com