Chris Pietschmann

husband, father, hacker, entrepreneur, futurist, innovator, autodidact

NAVIGATION - SEARCH

Using MiniProfiler with SqlDataSource ASP.NET WebForms Control

I recently implemented MiniProfiler into an existing ASP.NET WebForms application that makes use of databinding to the SqlDataSource control. Since the SqlDataSource uses a DbProviderFactory internally, it is fairly simple to exend the control to utilize MiniProfiler through inheritance and overriding a single method of the SqlDataSource. Here’s a very simple class that inherits from the SqlDataSource control and injects MiniProfiler support to be able to profile the SQL query used by the contr... [More]

SQL Azure: REBUILD All Indexes in Database – Alternative to DBCC DBREINDEX

Unfortunately there are some differences between SQL Server and SQL Azure. One of these differences is that SQL Azure does not support the “DBCC DBREINDEX” command. Thankfully there is an alternative you can use to rebuild the indexes within your SQL Azure databases. The alternative is to use “ALTER INDEX” instead. ALTER INDEX ALL ON TableName REBUILD Here’s an example that loops through all the tables in a database and rebuilds all their indexes: DECLARE @TableName... [More]

Entity Framework Tips and Tricks: Use GetObjectByKey When Querying a Single Entity

I’ve been using Entity Framework a lot lately and it makes data access really nice. It’s fairly simple to use and maps all your tables to a .NET object model. However, just like any other library, it can be misused and have it’s own issues. I’ve been figuring some things out as I’ve been using it, so I thought I’d share a few tip and tricks that I’ve learned. Here’s the first one: Use “GetObjectByKey” when Querying for a Single data entity Take the following query: var person = (from p in con... [More]

Software Basics: What are Databases and Data Access Layers, and How do they relate to Web Services?

In my own personal discussions with other developers and computer users I've seen a fair amount of confusion as to what a database is. Since the purpose of almost all applications is to Create/Read/Update/Delete data, they need to connect to a database, since data persistence storage need to be provided for the application to function as intended. If you want to stick to the simplest definition below, then a file cabinet is technically a database, but now that we're in the information age we don... [More]

Silverlight: Client-Side Database via LINQ and Isolated Storage

When I did a search on "Silverlight Database" there weren't many results, and none of them actually mentioned a small database that you could embed into your Silverlight application. So, I thought I'd share a solution that I've used to store application data on the client using a combination of LINQ and Isolated Storage. This actually gives you a small "SQL"-like database since you can use LINQ to interact with it. Basically, this technique constist of: ... [More]

CouchDB on FLOSS Weekly Episode 36 - distributed, document-oriented, RESTful database

I just listened to the latest episode of FLOSS Weekly; episode 36 on CouchDB. This episode is an interview with Jan Lehnardt about CouchBD. Here's a description of what CouchDB is from their website: Apache CouchDB is a distributed, fault-tolerant and schema-free document-oriented database accessible via a RESTful HTTP/JSON API. Among other features, it provides robust, incremental replication with bi-directional conflict detection and resolution, and is queryable and indexable ... [More]

T-SQL: Parse a delimited string

Parsing a delimeted string in T-SQL is fairly simple to do, but it does take a nice little chunk of code. I most commonly use this chunk of code when I need to pass an array from an application into a SQL Stored Procedure. The best way to implement this code into an application would be to create a SQL Function that you pass in a delimted string and it returns a table of values. This way you don't have to duplicate the code everytime you want to use it. The following example parses the delimete... [More]

ASP.NET: How to get a specific ConnectionString from the Web.Config by name

You have your ConnectionString for your ASP.NET web app stored in the Web.Config file. Now how exactly how do you get that ConnectionString out of there from within your code? Sample Web.Config section with a ConnectionString: <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Data Source=myDBServer;database=myDB;Integrated Security=True;" providerName="System.Data.SqlCl... [More]

T-SQL: Join Tables by a Field that contains a delimited string

This is the first time I ran into a situation where I needed to Join two tables (one with a varchar field and one with a varchar field that contains pipe delimited data) so I decided to post it for other who may not know you can do this. Below is a simplified example with solution of getting all rows of the first table whos values are contained in the pipe delimited field of the second table. This is really simple to do and you don't even have to use any User-Defined Functions or Cursors to... [More]

Use ASP.NET Web Parts and Membership stuff with SQL Server 2000

By default ASP.NET 2.0 Web Parts uses SQL Express. To use this stuff with SQL Server 2000 you must configure the aspnetdb database on your SQL Server 2000 database server. Fortunately for us, Microsoft has created a utility to automate the process. Follow these steps to setup the aspnetdb database: 1) run the utility - C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\aspnet_regsql.exe (this was written with ASP.NET 2.0 Beta 2 (v2.0.50215) as a reference, so with the final rele... [More]

I found a bug in SQL Server 2000...

Well, I didn't find a new bug, but it is the first time I discovered a bug in SQL Server by just doing my everyday job. I'd post the code for the SQL Server user defined function that I wrote if I could, but I can't (confidential). Its nice to have a reminder once in awhile that even Microsoft's software has bugs. Server: Msg 8624, Level 16, State 8, Line 1 Internal SQL Server error. http://support.microsoft.com/kb/290817

SQL Script: Search all tables for a specific field

DECLARE @NAME_TO_SEARCH_FOR nvarchar(50) SET @NAME_TO_SEARCH_FOR = '%photo%' SELECT sysobjects.name AS Table_Name, syscolumns.name AS Column_Name FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.idWHERE syscolumns.name LIKE @NAME_TO_SEARCH_FOR AND sysobjects.Type Like 'U%'