I’ve used these providers quite a bit over the past few years with good success.  As a simple overview, the Membership provider offers functionality that allows you to authenticate users against a store of users, the Role provider lets you authorize users to perform actions on your site based on roles they have been assigned and the Profile provider allows the saving of additional user details.  By using these providers, you can relieve yourself of having to roll your own functionality for these purposes.  I have found the Scott Mitchell’s tutorial located here to be an invaluable resource, but I have also picked up some tricks along the way.  Below, I’ll explain in a brief way how to set these providers up to use SQL Server as the data store. 

aspnet_regsql

First of all, we’ll need a place to store our information.  A utility is provided with the 2.0 version of the framework called aspnet_regsql.exe that will create all the required database objects for you.  The first decision you need to make is whether this information will live in your application’s database or in a separate database.  If your intention is to use the same user base across several applications, I’d elect to create a separate database for this information, otherwise I’d place the objects along side my application data.

Run the utility and you’ll be presented with the following screen.

Click Next> to continue the wizard.

Select the top radio button since we’re adding the database objects, not removing them.  Click Next>.

Select the SQL Server to install the objects to, credentials to authenticate (NOTE: The credentials used must have rights to create tables, views, stored procedures, etc on the specified database) and database.  If the database already exists, the objects will be created in it.  If the database specified does NOT exist, it will be created and then the objects will be created in it.  Click Next>.

This screen gives you an opportunity to review your choices.  If the information is correct, click Next>, otherwise click <Previous and correct your choices.  Clicking Next, here, creates the objects in the specified database.  Objects for all providers are created in the database.  You select the ones you wish to incorporate into your application by specifying them in the web.config file.  Let’s look at how to set up the providers.

Setting Up The Providers

The first thing we’ll need to do, is tell the providers where to find their data.  We do this by specifying a connection string in web.config.

<connectionStrings>
    <add name="Connection" connectionString="Data Source=ProviderDatabase; 
         initial catalog=ProviderDB; uid=user; password=pass"/>
</connectionStrings>

All the providers we specify in web.config will reference this connection string section.

Now, we need to start configuring our providers.  We so this in the <system.web> section of the web.config file.

<roleManager enabled="true">
  <providers>
    <remove name="AspNetSqlRoleProvider"/>
    <add connectionStringName="Connection" applicationName="/MyWebApp" 
         name="AspNetSqlRoleProvider" 
         type="System.Web.Security.SqlRoleProvider, 
          System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    <remove name="AspNetWindowsTokenRoleProvider"/>
    <add applicationName="/MyWebApp" name="AspNetWindowsTokenRoleProvider" 
         type="System.Web.Security.WindowsTokenRoleProvider, 
         System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</roleManager>
<profile>
  <providers>
    <remove name="AspNetSqlProfileProvider"/>
    <add name="AspNetSqlProfileProvider" connectionStringName="Connection" 
         applicationName="/MyWebApp" 
         type="System.Web.Profile.SqlProfileProvider, System.Web, 
          Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</profile>
<membership defaultProvider="CustomizedProvider">
  <providers>
    <clear/>
    <add name="CustomizedProvider" type="System.Web.Security.SqlMembershipProvider" 
         connectionStringName="Connection" applicationName="/MyWebApp" 
         minRequiredPasswordLength="5" minRequiredNonalphanumericCharacters="0" 
         enablePasswordRetrieval="false" enablePasswordReset="true" 
         requiresUniqueEmail="true" maxInvalidPasswordAttempts="4" 
         passwordAttemptWindow="10" requiresQuestionAndAnswer="false"/>
  </providers>
</membership>
<authentication mode="Forms">
  <forms loginUrl="Login.aspx"/>
</authentication>
<authorization>
  <deny users="?"/>
</authorization>

In the above, I’m specifying that I want to use the Role, Profile and Membership providers.  Notice the use of <remove> and <clear> tags.  The way the config files work is that they start at the machine level (machine.config) and get settings from there.  Then the web.config for the root web site is combined with that and so on down the hierarchy until the level at which your application sits.  There is a chance that a conflicting configuration will exist at some level above your site that will prevent the providers from working properly.  The <remove> and <clear> tags remove any existing settings so our providers remain as the only ones defined.

Next, notice how the each of the providers requires an attribute, connectionstringname.  This should always be the name of the connection string section that points to the database where your provider objects were created.  In our case, above, we created a connection string section named “Connection”, so that’s the section we specify to each of the providers.  Each provider also requires an attribute of applicationName.  Two things to remember, here.  First, this needs to be the actual name of your site and the second is to always preface this name with a forward slash if the application is sitting somewhere below the root of the site.  Since it’s possible for you to use the same provider database for multiple sites, the site name is used by the providers to determine which set of settings apply.  If you forget the forward slash for a site below the root, you’ll find that you get duplicate application records in your database…one with and one without a slash for the application name.

The Membership provider allows quite a lot of configuration.  The example, above, specifies many of the allowable settings but not all.  I highly recommend you look at the documentation to see all that can be specified.

Since we are going to use the Membership provider to log our users into the site, we need to set up forms authentication.  This is being done in the authentication and authorization tags.  Here, we’re saying we’re not allowing anyone to access the site unless they have logged in and if they have not yet logged in, we redirect them to the Login.aspx page.  On this page, you can simply drag on the login control from the toolbox.  No further setup is required.  The control is smart enough to get all it needs from the web.config file.  When the user logs in, the Membership provider will automatically be used and the user will be authenticated against the database.

Since we specified the Login.aspx page as the login page, users automatically get access to it without having to be logged in.  What if we wanted to use some of the other controls provided with ASP.Net to manage our account like password recovery or changing passwords?  We can do this by creating the pages for the purpose at hand and dragging the appropriate control onto it.  All controls of this type will know how to use the provider automatically.  However, if a user should be able to access the page without having logged in, first (password recovery, for example), we’ll need to allow for this.

As we have configured the site, users can only access Login.aspx without having logged in and all other content is inaccessible to non-authenticated users.  We need to make an exception of the pages in question so the rules don’t apply to them.  To do this, we create a <location/> section in web.config. Just below the <system.web/> section, add the following.

<location path="RecoverPassword.aspx">
  <system.web>
    <authorization>
      <allow users="*"/>
    </authorization>
  </system.web>
</location>

This section, in effect, says, “For this one page, allow all users".”

We just found and corrected a problem with printing from the reporting services web interface.  When the user clicked the print button, they received a popup message saying ""Unable to load client print control”.  As we found out, the problem was created when the users’ desktops installed a hotfix from Microsoft, KB956391.  We had two choices upon finding this out; we could remove the patch from every workstation or install a subsequent hotfix to Sql Server.  We opted for the latter and installed the hotfix located here.  Printing seems to work perfectly, now.

Some time ago, I posted a link to an article explaining how to obtain a DataSet from the result set of a Linq query.  I've continued to look for elegant, natural solutions to this problem and I've found one that appeals to me more, here.This solution, similar to the last, uses extension methods to get the job done, but I like the way this one is implemented better.

As noted in a former post, I implemented a change order system using Windows Workflow.  Recently, the system was deployed for User Acceptance Testing and odd things began to happen.  The system is using both tracking and persistence services and what we observed in the Sql Profiler was that the Sql to insert the persistence record was never making it to the database, but the tracking was working fine.

I implemented a handler for the ServicesExceptionNotHandled event on the WorkflowRuntime object, but nothing was being raised.  After a couple of hours poking and prodding, I finally linked the problem to the windows firewall.  If the firewall was turned off, everything worked as expected.  I spent some time researching the problem and found a couple of posts on the Microsoft Forums here and here that discuss the SharedConnectionWorkflowTransactionService.  Apparently, when the persistence and tracking data are in two different databases, the DTC gets involved any updates to the data and the firewall was blocking the DTC traffic. 

As mentioned in one of these posts, I combined the persistence and tracking databases into one, and added the SharedConnectionWorkflowTransactionService to runtime when I started up.  The combination of these actions removed the dependency on the DTC and all is working fine, now. 

Some time ago, I was working on a project that needed to work with users as a hierarchy.  The hierarchy was similar to what you would see in typical organizational chart and the users were organized by who they worked for and who worked for them.  As part of one of the requirements for the system, I needed to be able to retrieve from the database any particular user and all users who were placed beneath them in the chart, from their position in it all the way to the bottom.

To store the relationship between all the users, I created a self referencing table that contained user_id and managing_user_id columns.  The idea was that the record would contain the id of a user and also the id of the user over them.  If the user was at the top of the hierarchy, the managing_user_id column would be null.

Obviously, getting the data out of the table in the way I needed it could best be done with recursion.  I needed to get the user, all records that pointed to that user as the managing user...all the records that pointed to each of those records and so on.  I was wondering how to do this without making multiple calls to the database, however, because I could see the number of calls being astronomical for users near the top of the hierarchy.

Then I ran across a technique whereby I could use a CTE in a  recursive way.  The code looked something like this.

The key to this technique is in the fact that the second query in the CTE references the entire CTE in its FROM clause.  The net effect is that the CTE is executed and for each row in the result, the CTE is executed again and so on until there are no rows in the result set.

In my current contract, it's the typical story with compressed timelines and pulling off miracles, so I haven't had the opportunity to make use of Ruby for a "real" production application.  However, we're in the midst of doing some work in migrating data from a legacy system into a completely new database schema.  All imagined challenges apply, I assure you.  Part of this exercise is repeatedly bouncing the data against the new schema, applying validations to the data to make sure all the new referential integrity holds true in the new system and reporting on data that isn't fitting into the new system properly.  I took this as an opportunity to apply some Ruby scripting as a means of automating this part of the project.

 Having played with the DBI library a bit before starting, I knew that I wanted to abstract some of the interaction with it away so I wrapped it like so.

  ########## Class to abstract the server connection ##########
  class SqlServer
   
    def initialize(connectionString)
        @dbh=DBI.connect("DBI:ADO:Provider=SQLOLEDB;" + connectionString)
        @dbh["AutoCommit"] = true
    end
   
    def execute(sql)
        statement = @dbh.prepare(sql)
        statement.execute
    end
   
    def fetch(sql)
        statement = @dbh.execute(sql)
        statement
    end
   
    def disconnect
        @dbh.disconnect
    end
  end

I needed to be able to pull fresh data from other databases into my conversion database, so I abstracted that task, as well.

In my case, all the columns were varchar, so I didn't have to fuss with the format of the data in the insert statements.  With DBI, however, it's possible to get the meta data about each column to decide whether the data needs to be quoted or not.  Now, I needed to be able to pull data about how the load went and any exceptions that were encountered.  That data needed to be saved into CSV files so they could be attached to emails.  The next class was written to do this.

 ########## Class to pull statistic about the load into csv files ##########
 class StatFile
 
    def initialize(connString)
        @server = SqlServer.new(connString)
    end
 
    def GetStats(filePath, sql)
        statement = @server.fetch(sql)
       
        output = File.open(filePath, "w")
       
        statement.fetch do |row|
            output.puts row.to_a.join(",")
        end
       
        output.close
    end
 end

 With the above classes, I was able to script the tasks at hand something like this.

Pull.new("tableName").CopyTable #Copies the table from one db to another

 conversion = SqlServer.new("Data Source=server;initial catalog=database; trusted_connection=yes")
 conversion.do("EXEC ConversionSproc")

 stat.Getstats("c:\\Stat_Files\\exceptions.csv", "select foo1, foo2 from exception_table where error='bad' group by description")

As I've stated before, I'm not planning to use Ruby to put food on the table at this point, but I would like to get some experience using the language if only to get the chance to think about things differently.

 UPDATE:
After using the above scripts a bit, I found that when executing some rather long running stored procedures, I was receiving a timeout.  It was then that I realized there was not way to set the CommandTimout property on the database connection through DBI.  After hours of searching, I was able to gleen enough information to overcome the problem.  I added the following line to the bottom of the initialize method of the SqlServer class:

  @dbh.handle.instance_variable_get(:@handle).setproperty('CommandTimeout', 7200)

Of course, you can specify whatever value you like for the timeout or even parametize the value.

I recently had the chance to immerse myself in doing CLR development on Sql Server 2005.  The project I'm working on requires some complex pricing algorithms for varying combinations of products and doing the calculations in a "set-based" way just didn't feel right.

I needed to write several closely related stored procedures and did so in a single project.  When you create each procedure, Visual Studio creates a separate file for each procedure, but the files declare the class using the partial keyword which means that each procedure is actually just a static (shared) method on a single class.  As work progressed, I saw a number of opportunities to reuse some of the methods I had written across procedures.  This was easy enough since all the procs were actually in the same class...even private members we accessible from all the procedures. 

At some point, however, I realized that I needed to call one procedure from another.  This presented a problem, though.  I typically started all my procedure like this...

    . . . .

So, at the entry point of the first procedure, I opened a Context Connection and as soon as I called into the second proc, it tried to create another connection.  Since only one context connection is allowed per call, the attempt to open the second one failed.  I could, of course, call into the second procedure by creating a SqlCommand and executing it, but that feels dirty considering that both procs are actually in the same class.

So, after some thought, I determined a pattern that I intend to use from now on in such situations.  For all my CLR procs, the method that is the entry point for the procedure will be structured like this...

Now, I can call the code as a stored procedure (through up_DoWork), which is its primary purpose, and also call into the whole of the proc's functionality (DoWork) while passing in a context connection created in another proc.

I've known about it and handled it well for years.  You know...when you have a string with a single quote embedded in it and you need to insert the string into a database column.  For the longest time, I simply replaced any quotes with two quotes and all was well.  ADO.Net was smarter than I gave it credit for, however, and it apparently hung me up.

 I've been working on a brand new client-server application for the past several months.  I created the dal that was to be used by the entire application and it had been working well.  We just started allowing some user acceptance testing and it happened.  A user keyed in a string with an apostrophe in it and the application thre an exception.  "No problem", I thought, "I'll just replace any quotes with two quotes and I'm on my way".

One of the developers on this project approached me,  shortly after,  and pointed out that quotes I was doubling in the dal were, in fact, being saved in the database as double quotes.  Huh?  After two hours of debugging and tweaking I figured out the problem.  I found a post on Scott Gutherie's blog that pointed out that the SqlParameter class automatically takes care of escaping single quotes.   So why was it seemingly ignoring my quotes?

Turns out that the problem laid with the creation of the actual parameters.  I had created an interface and my Dal class implemented it.  Besides the other benefits to ding this, I wanted to be able to write specific implementations for different databases and be able to switch them out, if needed, without changing any of the application code.  So, necessarily, all the application code was written against the interface and not the specific dal class.  A side effect of this was that the code that called into the dal could not create specific parameter clases such as SqlParameter, but rather it asked the dal to create a parameter specific to the database the class was written for, and the parameter was stored in a variable of type  IDataParameter.

Long story short, the dal contained a method called CreateParameter that looked like this

Note that this method does not specify a DBType for the parameter.  Apparently, the lack of a type confused the SqlParameter class.  I modified the dal to check each parameter being passed in to see if it was a string type.  If it was, I manually set it to a DBType of Varchar.  This worked, in my case, because our standard was to use varchars for all string data types unless a char made more sense.  At any rate, this modification allowed me to remove the code that manually escaped the single quote.  The SqlParameter class now had all it needed to handle the situation on its own.

OK, I admit it.  When the Xml support first started showing up in Sql Server, I was a skeptic.  I wondered why anyone would want to do all that parsing and string manipulation on the database tier.  It made me wince to think about it.  So now, a few years later, and I'm wondering what I ever did without it.  Especially since the Xml data type has been added to Sql Server 2005.

As an example of its usefulness, consider a recent situation I found myself in.  I had a collection of objects that represented changed that needed to be made to the state in the database.  I could have iterated through the collection and called a stored procedure for each object and applied the change sets one at a time, but I tend to try to make any call across the network as chunky as possible and thought how nice it would be if I could send the entire collection of changes to the database at once.  It's entirely possible to do just that with the Xml data type.

First, I serialized each of the objects into Xml and gathered the individually object's state into a single Xml document.  As an illustration, let's suppose the Xml looked like this.  

I can pass this xml into a stored procedure as a string where the proc takes a single parameter of type Xml.

Inside the stored procedure I can parse through the Xml and place the data it contains into a temporary table that I can use any way I like.

Starting with the FROM clause, I've basically specified an Xpath expression to the level within the XmlDocument which corresponds with the level at which my "rows" are defined and I'm saying that each element returned by the Xpath expression is to be treated as a row and each element below that level should be treated as a column in that row.  In the select statement, I'm specifying each column by telling Sql Server the name of the element within the row I want the value from and I'm specifying the data type to cast the data as.

As I said before, I'm pulling the data out of the Xml into a temporary table so that I can run set-based Sql against the data to perform the updates I needed.

Sql Server 2005 brought a couple of great features to the table in regard to paging...CTE's and RowNumber().  Paging, of course, is returning a a resultset in chunks (pages) so that you don't have to return a large number of rows all at once.  For example, in a web application there may be a search page that allows users to search for orders by a date range.  There is always one user who wants to list every order the company has received since the beginning of time.  Without paging, every row returned by the search would be returned to the web server from the database and them passed on to the browser and displayed in a mile-long web page.

It's possible to accomplish paging on the web server (much more easily in .Net 2.0 than in previous versions thanks to the inclusion of paging in several of the bundled web controls).  In this case, the large result set is persisted on the web server and sent to the browser a page at a time.  Using this approach, you take two sizable hits; bringing all the rows across the network from the database and storing them in memory (or possibly on disk) on the web server.

Ideally, we'd like to be able to retrieve just a page at a time from the database and display it to the user.  Sql Server 2005 makes this an easy task.  Here's an example.

SELECT @PageSize = 15, @PageNumber = 2

In this example, we begin by declaring the variables, we'll need.  Note that the page size and page number could and normally would be passed into a stored procedure containing this code.  Next, we calculate the top and bottom rows of the page we're interested in displaying.  These values are indexes into the resultset as a whole that represent where our page starts and where it ends.  Now, we create a CTE named "Orders".  I think of CTE's as temporary views.  We define The CTE and then select from it.  In our CTE definition, we define the select statement which is our view into the data (or in other words the select statement that would return the entire result set we're concerned with) and add a column to the statement based on the Row_Number() function.  Basically, we specify a sort order and the Row_Number() function will assign a row number to each row in the resultset based on the sort order we specify.  With the CTW defined, we can query it and return only the rows with row numbers that fall between the top and bottom row numbers we calculated earlier.