.Net, ASP.NET, Data Sources, Database, Programming, Software, SQL, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Tips, Transact-SQL, tSQL, VB.NET, Visual Studio 2010, Web Development, XML

SQL Server – Pass a List from your ASP.NET Code to a Stored Procedure using XML

Sometimes while developing a tool, a developer needs to pass a list of values with a variable size to their database. There are several ways of doing so, one of which involves passing the list in XML format.

The introduction of SQL Server 2005 offered the use of XML datatypes, which had not existed in previous versions of SQL Server. The good thing about the XML datatype is that it is not just good for storing information in XML format, but also has advantages such as being fully query-able similar to a database table.

As you look through this example, please feel free to check out other articles I have written for more SQL Tips

Here are the three main ways of passing lists of parameters to the different versions of SQL Server

A big benefit of the XML datatype is the ability to pass lists of items to stored procedures as XML format parameters. In SQL Server 2000 one would have to pass a list of parameters as a String that would then need to be parsed into its components in the Stored Procedure. This a tedious process that could easily introduce parsing errors. Subsequent versions of SQL Server have added extra ways in which to pass information lists to Stored Procedures:

  1. As a character-separated list contained in a String (VARCHAR) variable – SQL Server 2000 +
  2. As an XML variable – SQL Server 2005 +
  3. As a table variable – SQL Server 2008 +

As you can see from the list above, the methods of passing lists to stored procedures have increased in number over the last few iterations of SQL Server. The catch of course is that as a developer you will need to adapt your code to whichever instance of SQL Server your project is based on.

Continue reading “SQL Server – Pass a List from your ASP.NET Code to a Stored Procedure using XML”

Database, Programming, Software, SQL Server 2000, SQL Server 2005, SQL Server 2008, Windows 2003, Windows 2008, Windows 7

Quick and Easy Windows Shortcuts to Common Server Applications

The other day I was working on a Windows 2003 SP2 server that was no longer displaying anything other than the Windows desktop. I could not bring up the start menu or even see the bottom menu bar.  This naturally was a problem because I wanted to set up a new Web site on the server.

In my experience this problem with Windows is most likely caused by Windows running for too long without a restart, so the standard fix is typically to restart the entire computer.

However, restarting the server was not an option since at that time it was being actively used by other developers for development and testing. So rather than cause trouble for everyone, I decided to look into setting up desktop shortcuts to the programs I wanted to work with.

Continue reading “Quick and Easy Windows Shortcuts to Common Server Applications”

.Net, ADO.NET, ASP.NET, Database, Programming, Security, SQL Server 2000, SQL Server 2005, SQL Server 2008, VB.NET, Web Development

ASP.NET Choosing Parameters.Add or Parameters.AddWithValue

If you are adding parameters to a SqlCommand object then you can use either Parameters.Add or Parameters.AddWithValue. The Parameters.Add syntax requires you to explicitly specify the DataType of the parameter you are passing, while the Parameters.AddWithValue syntax implicitly attempts to convert the parameters you pass in.

When choosing which method you want to use, you should be aware that although there is no difference in the functionality of either method, but Microsoft added Parameters.AddWithValue because overloads for Parameters.Add resulted in possible confusion of which specific overload was being used. I do find myself using Parameters.AddWithValue for its convenience and clarity, but the fact that you are leaving the datatype for the compiler to implicitly convert is not a good thing.

Continue reading “ASP.NET Choosing Parameters.Add or Parameters.AddWithValue”