ConnectionTimeout vs. CommandTimeout

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/connectiontimeout_vs_commandtimeout.htm]

SQL timeouts can be very annoying, especially for internal development tools where performance isn't critical.

A lot of developers will try fixing this by modifying the connection string by appending "Connect Timeout=300". Normally this is easy because the connection string is stored in some config file.

However, it still usually fails. This is because there's a big difference between SqlConnection.ConnectionTimeout and SqlCommand.CommandTimeout.

If you're running a command, like a snippet of SQL or a stored proc, then your code needs to set the CommandTimeout. Something like so:

SqlConnection con = new SqlConnection(strDbCon);
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strText;
cmd.CommandTimeout = 10000; //no relation to con.ConnectionTimeout

Obviously, that's very minimalist code, but that's the general idea. For a robust data access layer, you'd make the timeout configurable.

Tool: Microsoft SQL Server Database Publishing Wizard 1.1

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/tool_microsoft_sql_server_database_publishing_wizard_11.htm]

I was experimenting around with some starter kits, and I came across this good, free tool - Microsoft SQL Server Database Publishing Wizard 1.1. Basically, it can write a database schema, and data, to a sql script. I think it has a command line API too!

 

I found this useful when trying to deal with some database in a starter kit that I couldn't access outside of Visual Studio. I wanted to be able to script and extend it. It was in an ASP.Net 2.0 app_data folder. I'm sure there was a way around it, but this approach seemed so much faster for me. I scripted the database, and then re-ran the script in SQL Studio, and changed the asp.net db connection strings to that new database.

SQL Tip: Concat entire column into a single CSV string

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/sql_tip_concat_entire_column_into_a_single_csv_string.htm]

Sometimes in SQL, you'll want to concat an entire column's values into a single string.

For example, say you have a parent-child relationship (like state-city, or entity-code), and in a parent's list page, you want to display a column with the CSV string of all the children.

State	City
IL	Chicago
IL	Springfield
IL	Rockford
WI	Madison

You can concatenate a list of values into a CSV string like "Chicago, Springfield, Rockford" by continually selecting into the same variable:

        declare @sCsv varchar(1000)
        set @sCsv = ''

        select @sCsv = @sCsv + City + ', '
        from MyTable
        where State = 'IL'; --any filter clause here

        --remove final ","
        if (Len(@sCsv) > 1)
                select @sCsv = substring(@sCsv,1,len(@sCsv)-1)
        return @sCsv

Why use Stored Procedures over direct SQL calls?

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/why_use_stored_procedures_over_direct_sql_calls.htm]

Two main ways to interact with the database: using a stored procedure vs. using a direct SQL statement. Here's a brainstorm of the pros and cons to each:

Technique (called from managed code):	Pro	Con
Stored Procedure	Performance, as SP is already on the database Reusability - multiple sources (including other SP) can call a SP. Security, especially against SQL injection attacks. Maintainability - you can create the SP (and run and test it) in a database editor. Powerful - A SP consists of multiple SQL statements. Deployment - Modifying a database object is often easier than redeploying managed code. Code Generation - You can code generate a data access layer off of stored procs (although most of the time it's code-generated off of table schemas).	Have to install each SP on the database
Sql Statement	Quicker to get started, especially for simple code and a RAD, non-production app. Sql string is stored in managed code, so no need to install on the database. Flexible - you're generating any SQL statement you want.	Lacks most of the benefits of a SP.

In general (of what I've seen), many enterprise apps will use Stored Procs, while sometimes a RAD will use raw SQL statements.

What has your experience been?

Sql Tools - Profiler and SqlCommand

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/sql_tools__profiler_and_sqlcommand.htm]

Everyone knows how to use SQL Management Studio, but there are two other useful tools that SQL Server 2005 has.

• SQL Profiler - This lets you profile the SQL Server calls. It's very effective to determine what page is making which calls (and how long those calls take). It's much easier to just run profiler, as opposed to stepping through all the code to find what SP gets triggered when you click Button X.
• SqlCmd - This is the command line for SQL Server (replaces osql for SQL 2000). It's useful when you need to Automate, or don't want the full SQL GUI installed (such as when you have limited space on a build server).