Archive

Posts Tagged ‘SQL Server’

.NET Uri, database not found, and encoding

I ran into an interesting issue when trying to debug some code a while back.  I had written a dynamic that would allow a person to authenticate and then pick a database in which to perform their operations.  For the most part, this system worked well and allowed me to keep things simple.  The part that was trick was that I had written my reporting and dashboard engine to use a single Razor .cshtml file to handle all requests.  It in turn pulled the report definition or dashboard definition XML files from the database.  The problem came when one of the SQL Server databases that the system was deployed was not the default instance but a “named instance“.

In my code, I would simply construct an Uri object and pass in the string that represented the parameters that I needed for the given page.  However, since a named instance uses the ‘\‘ character, this caused some problems as my simple string representation was not encoding the character and thus when the server received the request it was not a valid server name.  I spent hours thinking that it was a problem with the configuration with SQL Server.  It wasn’t until I debugged the .cshtml file that I realized that the name of the server was not coming across correctly and that I was not encoding the name properly.  Here is what I finally came up with to correct the issue:

string address = string.Format("../PreviewReturnSetupBillDesigner.cshtml?
Key={0}&ServerName={1}&DatabaseName={2}",
  101,
  HttpUtility.UrlEncode("monticello\test"),
  HttpUtility.UrlEncode("sample"));

Uri uri = new Uri(address, UriKind.RelativeOrAbsolute);

Previously, I was just passing in the server and database names without encoding them. This worked for all default installations of SQL Server but once you had a named instance in place, all bets were off.

Hopefully this will help anyone from spinning their wheels if they are doing anything similar…

Advertisements

Gofer – Silverlight

January 4, 2012 9 comments

In our last post, we used Gofer in a Console application and got data from a SQL Server database as well as creating the database from our domain model. Today, we are going to be doing the same thing but we will be doing this with Silverlight.

First, start by create a new Silverlight 5 application.

Make sure that you do NOT enable WCF RIA Services!

Next, we are going to setup of the web project first and then move over to the Silverlight project once we are done. Let’s start with getting Gofer from NuGet. Right-click on your References folder and select Manage NuGet Packages. Next, type in “Gofer” as your search criteria. Select Gofer.Sample as your choice. This package comes with the Gofer library as well as with some helper files to make testing this easier.

Gofer.Sample has a dependency on SwitchBlade and ValueInjecter.

SwitchBlade is another package that I wrote that allows you to host Razor templates outside of ASP.NET and IIS. I will be covering SwitchBlade in a future post.

ValueInjecter is a package like AutoMapper but much more convention-based and easier.

We are also going to need to use Ninject as our DI/IOC container. We will use NuGet to install this package as well:

Finally, we will need the WCF Web API from NuGet as well:

Moving on from adding all of our packages, you will also notice that you have two new template folders for your CRUD and DDL operations. You can modify these templates to shape how you want your SQL code to look when it is used by Gofer.

You will also notice two new files:

Domain.cs – This file represents a sample domain model. It is very similar to what you would see from a Northwind with some slight modifications.

TestDriver.cs – This file is a test driver class that allows us to test Gofer. We will be using a Silverlight version of this file instead. DELETE this file from the project as we do not need it.

Next, let’s add a Global.asax file to the project and modify as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;

using System.Web.Routing;
using Microsoft.ApplicationServer.Http;
using Ninject;
using Domain;
using Gofer;

namespace GoferSilverlight.Web
{
    public class Global : System.Web.HttpApplication
    {
        public const string NAMESPACE = "Domain";
        public Func<Type,bool> PREDICATE = x => x.Namespace == NAMESPACE;

        protected void Application_Start(object sender, EventArgs e)
        {
            RouteTable.Routes.SetDefaultHttpConfiguration(new WebApiConfiguration()
            {
                CreateInstance = (serviceType, context, request) => CreateInstance(serviceType),
                EnableTestClient = true
            });

            RouteTable.Routes.MapServiceRouteForAssemblyOf<Customers>(PREDICATE);
        }

        private object CreateInstance(Type serviceType)
        {
            object result = null;
            IKernel kernel = new StandardKernel();
            try
            {
                // The following is a sample entry for using the MapServiceRoute method:
                // RouteTable.Routes.MapServiceRoute<GoferService<Customers>>("Customer");
                // Hence the reason we need to pull the generic type from the GoferService.
                var genericType = serviceType.GetGenericArguments().FirstOrDefault();
                SchemaRules rules = GetRules(genericType);
                kernel.Bind(serviceType).ToSelf().WithConstructorArgument("rules", rules);
                result = kernel.Get(serviceType);
            }
            catch { }

            return result;
        }

        #region Rules

        private SchemaRules GetRules(Type type)
        {
            var result = new SchemaRules();
            result.AssemblyOf(type)
                .ShouldMap(PREDICATE)
                .GetSchema();

            result.PerformMigration = true;
            result.ForceNewMigration = false;

            return result;
        }

        #endregion

    };
}

UPDATE: I added a Fun<Type,bool> predicate so you could easily add your own logic for both the “RouteTable.Routes.MapServiceRouteForAssemblyOf<Customers>(PREDICATE)” and the .ShouldMap(PREDICATE) calls.

If you are curious as to what is going on here, please refer to my post Building a Generic Service using WCF Web API – Part II as it walks you through all of these steps.

One thing to point out here is that we are using the same SchemaRules class.

SchemaRules – This tells the Gofer engine what conventions to use for its data access. There is a ton that you can override with this class and we will take a look at that in a later post but this is the bare minimum that you need to get going. Also, you will see two properties that tell the engine whether or not to perform a migration as well as force the migration, meaning that it will drop the database and recreate it if necessary.

There is one last change that you need to put in place before we can test our service. We will need to modify the Web.config. The following is a sample Web.config that you can pattern against for yourself:

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <appSettings>
    <add key="DB_NAME" value="Example" />

    <add key="DDL_ConnectionString" value="Provider=SQLOLEDB;Server=(local);Database=master;Integrated Security=SSPI;" />
    <add key="DDL_DatabaseType" value="4" />

    <add key="ConnectionString" value="Data Source=(local);Initial Catalog=Example;Integrated Security=SSPI;" />
    <add key="DatabaseType" value="3" />

    <add key="TemplatePath" value="C:\Users\Matt\Documents\visual studio 2010\Projects\GoferSilverlight\GoferSilverlight.Web\CRUD_Templates" />
    <add key="DDL_TemplatePath" value="C:\Users\Matt\Documents\visual studio 2010\Projects\GoferSilverlight\GoferSilverlight.Web\DDL_Templates" />
  </appSettings>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <system.serviceModel>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
  </system.serviceModel>
</configuration>

If we run the application, you will see a blank screen but we can still test our service:

I am using the database that I tested with the previous post. Therefore, I knew I had at least one record in the Customers table. Based on the results of the service, I can see that I am getting back a good JSON response.

Ok, our service is ready, now let’s shift gears and see what we must do to test this on the Silverlight side.

We want to share our domain for both the client and server. Right-click your Silverlight project and select “Add | Existing Item…”. Navigate to the web project and select the Domain.cs file and click the down arrow to “Add As Link”. This will give us the domain model definition in our Silverlight project.

Next, let’s add our Gofer.Silverlight package from NuGet. Right-click on the References folder and select Manage NuGet Packages. Next, type in “Gofer” as your search criteria. Select Gofer.Silverlight as your choice.

Gofer.Silverlight has a dependency on Async CTP and HTTP Contrib which are provided as part of the install.

Async CTP is a library that helps make asynchronous programming easier.

Http Contrib is a library that helps make calling the WCF Web API easier from clients such as Silverlight.

You will also notice that a “readme.txt” file added to the project. If you open the file, you will see the you need to add the following code snippet to the constructor of your App.xaml.cs file:

HttpWebRequest.RegisterPrefix("http://", WebRequestCreator.ClientHttp);
HttpWebRequest.RegisterPrefix("https://", WebRequestCreator.ClientHttp);

I wrote a blog post here when I ran into some strange issues trying to test my services for PUT and DELETE behaviors.

Okay, let’s add the following TestDriver.cs class to the Silverlight project:

using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

using Gofer.DataAccess;
using Domain;

namespace GoferSilverlight
{
    public class TestDriver
    {
        public void Run()
        {
            var repo = new SilverlightRepository("Customers");
            var cust = new Customers()
            {
                CompanyName = "Bubba's Repair",
                ContactName = "Billy Bob",
                ContactTitle = "Owner",
                Address = "100 Pecan Street",
                City = "Columbia",
                PostalCode = "29661",
                Country = "USA",
                Phone = "(803) 836-1212",
                Fax = "(803) 836-1213"
            };
            repo.Create<Customers>(cust,
                (item) =>
                {
                    if (item == null)
                    {
                        // Handle data here....
                    }
                },
                (error) =>
                {
                    if (error == null)
                    {
                        // Handle error here....
                    }
                }
            );            
        }
    };
}

As you can see, this is very similar to what we used in the Console application but all calls are asynchronous and I also wanted to have a unique callback for success and errors. If the database did not exist, you could run this just like the Console code and a new database would be created as long as you had the PerformMigration property set to “true” in your Global.asax.cs file.

The final step to get this to work is to add the following code to the constructor of your MainPage.xaml.cs file:

TestDriver td = new TestDriver();
td.Run();

If you add a couple breakpoints as shown below, you should be ready to run the application:

When the debugger hits your breakpoint, you should see something similar to the following:

That’s it! This may seem like a lot of moving pieces to get this working but once you get in the groove, you will see that this is so much easier than dealing with proxies and hidden code generated files from Visual Studio. I personally really like this approach and I look forward to getting your response as well.

In the next couple of posts, I will be digging deeper into to Gofer as a whole to show you everything that you can do.

Gofer – Console

January 3, 2012 1 comment

By now, I am sure that you are tired of reading and just want to play with whatever I have been talking about. Well, that is exactly what we are doing to do.

First, start by creating a new Console Application. Next we are going to access my libraries using NuGet. Right-click on your References folder and select Manage NuGet Packages. Next, type in “Gofer” as your search criteria. Select Gofer.Sample as your choice. This package comes with the Gofer library as well as with some helper files to make testing this easier.

Gofer.Sample has a dependency on SwitchBlade and ValueInjecter.

SwitchBlade is another package that I wrote that allows you to host Razor templates outside of ASP.NET and IIS. I will be covering SwitchBlade in a future post.

ValueInjecter is a package like AutoMapper but much more convention-based and easier.

You will also notice that you have two new template folders for your CRUD and DDL operations. You can modify these templates to shape how you want your SQL code to look when it is used by Gofer.

You will also notice two new files:

Domain.cs – This file represents a sample domain model. It is very similar to what you would see from a Northwind with some slight modifications.

TestDriver.cs – This file is a test driver class that allows us to test Gofer.

In your Program.cs file, add the following code snippet to your Main method:

TestDriver td = new TestDriver();
td.Run();

Here is what the TestDriver class looks like:

public class TestDriver
{
    public void Run()
    {
        SchemaRules rules = new SchemaRules();
        rules.AssemblyOf<Customers>()
            .ShouldMap(x => x.Namespace == "Domain")
            .GetSchema();

        rules.PerformMigration = true;
        rules.ForceNewMigration = true;

        var repo = new Repository<Customers>(rules);
        var cust = new Customers() {
                CompanyName = "Bubba's Repair",
                ContactName = "Billy Bob",
                ContactTitle = "Owner",
                Address = "100 Pecan Street",
                City = "Columbia",
                PostalCode = "29661",
                Country = "USA",
                Phone = "(803) 836-1212",
                Fax = "(803) 836-1213"
        };
        var id = repo.Insert(cust);
        var ds = repo.Get().ToList();

        Console.WriteLine("Press any key to exit...");
        Console.ReadKey();
    }
};

As you can see, we are using two main classes from Gofer: SchemaRules and Repository.

SchemaRules – This tells the Gofer engine what conventions to use for its data access. There is a ton that you can override with this class and we will take a look at that in a later post but this is the bare minimum that you need to get going. Also, you will see two properties that tell the engine whether or not to perform a migration as well as force the migration, meaning that it will drop the database and recreate it if necessary.

Repository – This is our data access class that facilitates getting data from the Gofer engine.

There is one last change that you need to put in place before you continue. The package will have also provided you with an App.config that you will need to complete. The following is a sample App.config that you can pattern against for yourself:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="DB_NAME" value="Example" />

    <add key="DDL_ConnectionString" value="Provider=SQLOLEDB;Server=(local);Database=master;Integrated Security=SSPI;" />
    <add key="DDL_DatabaseType" value="4" />

    <add key="ConnectionString" value="Data Source=(local);Initial Catalog=Example;Integrated Security=SSPI;" />
    <add key="DatabaseType" value="3" />

    <add key="TemplatePath" value="C:\Users\Matt\Documents\Visual Studio 2010\Projects\GoferConsole\GoferConsole\CRUD_Templates" />
    <add key="DDL_TemplatePath" value="C:\Users\Matt\Documents\Visual Studio 2010\Projects\GoferConsole\GoferConsole\DDL_Templates" />
  </appSettings>
</configuration>

I used the name “Example” for the name of the database we will be using for data access. There are two connection strings since we will have one for our data access as well as one for our creation statements. You will see two different DatabaseType values you can leave for now. We will go into how you can go against any back-end system in a future post. Finally, there are two paths for the CRUD and DDL templates. Make sure that you update the paths to the directory where these folders are located on your machine.

If you run your application and you left PerformMigration to true, Gofer will create the database for you. It will then try and insert a new record and the pull all records from the Customers table.

NOTE: I did need to change my project type from the Client Profile to the full .NET 4 Framework.

Here is one last tidbit, if you put a break point after your insert statement, you can access a SqlTrace property on the Repository instance. This will show you what was executed and any error messages coming back from SQL Server. This is really helpful especially when you are migrating changes over to the database. Gofer does this automatically when you have the PerformMigration property set to true.

In my next post, I will be basically doing the same thing but using Gofer over the web for Silverlight without any need for a proxy! My main goal for Gofer is simplicity and allowing us to get back to focusing on our business rules and domain models. Gofer has a lot of extensibility and we will be going into this in future posts as well.

If you start playing with this, remember that it is the tip of the iceberg and I will be going into more depth on multiple levels. Hope you like…

Simple Tabular Report Generation in WPF

May 11, 2011 4 comments

This post is on using the power of the ReportViewer in WPF to build simple tabular reports on the fly. I did a presentation on this a while back and I noticed that there is some interest in building reports and having the ability to query your database on the fly without the need for SQL Server Management Studio or MS Access.

The sample included in this post is a simple mock up that can grow with time. It gives you the ability to connect to any SQL Server backend. You can then write your SQL in the editor. Finally, you can preview your data, make any changes you want to ensure that it is ready for a generating a report. You then click on the Generate Report button and the Preview Report tab becomes selected rendering a dynamic report based on your query. With very little code, you get a simple query editor, code generator, and local report viewer.

In this first post we will just take a look at the user interface but in a following post, we will cover the code necessary to get this working.

My example is using the AdventureWorks database but you can use whatever database you like. When the application first starts, are presented with the following window:

Click on the button as indicated in the following screen shot:

Setting up the Connection

Follow the dialog and enter in your information for creating a connection.

Dialog for creating a database connection.

You can test your connection and verify that it is correct.

Dialog for testing the connection.

Click the Connect button to create the connection. Type in the query you wish to perform:

Write in your SQL query.

You can now either execute the query or parse it. The following is a screen shot of the query executed:

Query and metadata results displayed.

As you can see, we get our results but we also get a grid in the middle that provides us with a bit of metadata for formatting our report. You can also provide a title for the report.

Metadata set for report generation.

Then by clicking the Generate Report button, we see the following:

Report rendered in the ReportViewer control.

By now you can see that it is very easy to create some dynamic reports in very little time. In the next post we will go over the code and architecture required to get this to work. I have included the slides, source code, and a walk-through from my presentation.

Hope this helps….

WCF RIA Services – An entity with the same identity already exists in this EntitySet.

July 16, 2010 3 comments

WCF RIA Services is pretty powerful and does a lot for you when it comes to quickly building business applications. I came across something interesting today when I was working with a client.

For this post, I am going to be focusing on WCF RIA Services in conjunction with the EntityFramework. It turns out that if you have designed your tables with identities and you have set your seed for the table to be zero (0), then you will have problems right after your first record has been created and saved to the database.

The following is a screen shot of a given table in SQL Server with the identity seed set at zero (0):

Identity Seed

The moment you try to create a subsequent record, you will get the following error:

InValidOperationException was unhandled by user code: An entity with the same identity already exists in this EntitySet.

The reason for this is that RIA Services tries to created a new record that has an identity of zero (0) and as soon as you try and add it to the underlying collection it throws an exception since a record with that value already exists.

In WCF RIA Services, I don’t believe there is a way to change the default behavior for when a new record is inserted. It looks like it follows whatever datatype is defined as the identity column that in the table. So if the column is defined as an integer than the default value would be zero (0).

I believe I remember being able to bypass this issue in ADO.NET with DataSets with the ability to define what the default value would be for adding a new record.

More than anything else, I wanted you to be aware of a possible exception that may not intuitively makes sense at first but then becomes clear once you see the data. You may get tricked into thinking that your system is working if you have an empty table and insert your first record. This will work perfectly fine since it will insert a record with the identity value being zero (0). The moment you try to add an additional record you will get the exception mentioned above.

I would like to stress that for most scenarios you should not need be concerned unless your team has designed your tables with the seed of your identity columns to be zero (0) instead of one (1). In SQL Server 200X, the default value for the seed of an integer identity is one (1).

Hope this helps to prevent any confusion. Also, if anyone knows how to rememdy this problem please let me know.

Problem Deploying WCF RIA Services using Entity Framework 4

June 29, 2010 1 comment

So I had my software tested and ready to deploy to the client. I installed the application on a local test server and confirmed that everything was working fine. It is using Silverlight 4, Prism, WCF RIA Services on top of Entity Framework 4. All seemed to be smooth sailing….

However, once I deployed to the client site on their internal servers, everything went wrong. After spending several days and installing Visual Studio on the client’s server to see what was wrong, I finally found the culprit. In hindsight, this may be a simple solution but when you are knee deep looking at code and configuration it is easy to overlook.

The problem turned out that I had developed and tested the application against SQL Server 2008. When I deployed the application to the client, their database instance is SQL Server 2005.

In the Entity Framework 4, it actually targets the database instance for obtaining the correct schema. There is no way to modify this in the Visual Designer or properties pane of Visual Studio. You must open the edmx document using the Xml (Text) Editor.

Here is a sample of what it looked like:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GlobalServices.Web.DataAccess.Store" 
        Alias="Self" Provider="System.Data.SqlClient" 
        ProviderManifestToken="2008">...</Schema>
    </edmx:StorageModels>
  <edmx:Runtime>
</edmx:Edmx>

Here is what it should look like:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GlobalServices.Web.DataAccess.Store" 
        Alias="Self" Provider="System.Data.SqlClient" 
        ProviderManifestToken="2005">...</Schema>
    </edmx:StorageModels>
  <edmx:Runtime>
</edmx:Edmx>

As you can see, the only difference is in the ProviderManifestToken attribute. All I had to do was change the value from 2008 to 2005.

If you have developed an application using Entity Framework and were targeting SQL Server 2008 but then needed to deploy to a client using SQL Server 2005, please be aware of this necessary change for your application to work.

NOTE: Also be aware that you may need to change more than this if you are using SQL Server 2008 specific data types, (e.g. datetime2).

Hope this helps….