August 2012: .NET SQL Server Database Code Snippets

How many times have you looked for a piece of code you’ve written in the past? You probably search high and low on your hard drive, scouring through past projects and code files. Or maybe you’ve tried searching your source code control repository with unsuccessful results. How much time do you waste looking for a particular routine only to not find it, which then causes you to rewrite the routine all over again? Let’s face it, as developers we beg, borrow, and steal as much code as we can in order to get our jobs done as quickly as possible. After all, the grand utopian vision of developers is code reuse – the ability to write blocks of code once and then reuse them again and again without having to rewrite them. But how often does that actually happen? Unfortunately, not often enough. Feel free to add the following snippets to your tool belt.

/// <summary>
 /// Checks if a database exists
 /// </summary>
 /// <param name="Database">Name of the database</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 public static bool DoesDatabaseExist(string Database, string ConnectionString)
 {
 return CheckExists("SELECT * FROM Master.sys.Databases WHERE name=@Name", Database, ConnectionString);
 }

/// <summary>
 /// Checks if a table exists
 /// </summary>
 /// <param name="Table">Table name</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 public static bool DoesTableExist(string Table, string ConnectionString)
 {
 return CheckExists("SELECT * FROM sys.Tables WHERE name=@Name", Table, ConnectionString);
 }

/// <summary>
 /// Checks if a view exists
 /// </summary>
 /// <param name="View">View name</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 public static bool DoesViewExist(string View, string ConnectionString)
 {
 return CheckExists("SELECT * FROM sys.views WHERE name=@Name", View, ConnectionString);
 }

/// <summary>
 /// Checks if stored procedure exists
 /// </summary>
 /// <param name="StoredProcedure">Stored procedure's name</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 public static bool DoesStoredProcedureExist(string StoredProcedure, string ConnectionString)
 {
 return CheckExists("SELECT * FROM sys.Procedures WHERE name=@Name", StoredProcedure, ConnectionString);
 }

/// <summary>
 /// Checks if trigger exists
 /// </summary>
 /// <param name="Trigger">Trigger's name</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 public static bool DoesTriggerExist(string Trigger, string ConnectionString)
 {
 return CheckExists("SELECT * FROM sys.triggers WHERE name=@Name", Trigger, ConnectionString);
 }

/// <summary>
 /// Checks if something exists
 /// </summary>
 /// <param name="Command">Command to run</param>
 /// <param name="Name">Name of the item</param>
 /// <param name="ConnectionString">Connection string</param>
 /// <returns>True if it exists, false otherwise</returns>
 private static bool CheckExists(string Command, string Name, string ConnectionString)
 {
 bool Exists = false;
 using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
 {
 try
 {
 Helper.Open();
 Helper.AddParameter("@Name",200, Name);
 Helper.ExecuteReader();
 if (Helper.Read())
 Exists = true;
 }
 catch { }
 finally { Helper.Close(); }
 }
 return Exists;
 }

[yellow_box]Note: Inside the CheckExists method there is a reference to SQLHelper which is nothing more than another class the sets up the database connection, so feel free to use your own class or even create a new one.[/yellow_box]

Series DropDownList: Cascading DropDownList

This is the second article on the subject of DropDownList. If for any reason you missed the earlier post titled Series DropDownList: Binding XML Data to a DropDownList, I would recommend that you take the time and read that post as well.

In part two of this series I will focus on accomplishing cascading selections with your DropDownList. Since the bulk of the work was accomplished in DropDownList: Binding XML Data to a DropDownList we will pick up from there.

dropdown list bound with xml data

Web Form

Here we will incorporate a small change from the previous example. Notice that in this example I have added a new event titled OnSelectedIndexChanged.

<label for="ddlCountry">Country:</label>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged" Width="160px">
</asp:DropDownList>
<label for="ddlRegion">Region:</label>
<asp:DropDownList ID="ddlRegion" runat="server" AutoPostBack="True"
OnSelectedIndexChanged="ddlRegion_SelectedIndexChanged" Width="160px">
</asp:DropDownList>
<label for="ddlCity">City:</label>
<asp:DropDownList ID="ddlCity" runat="server" Width="160px">
</asp:DropDownList>

Code Behind

Each selected index change event fires the appropriate method which in turns makes a call back to the server and reads in the appropriate data to return and bound to out DropDownList.

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
ddlRegion.Items.Clear();
string strCountry = string.Empty;
strCountry = ddlCountry.SelectedValue;
List<string> list = null;

if (ddlCountry.SelectedIndex != 0)
{
list = RetrieveDataFromXml.GetRegionByCountry(strCountry);
if (list != null && list.Count != 0)
{
ddlRegion.Enabled = true;
}

ddlRegion.DataSource = list;
ddlRegion.DataBind();
}
else
{
ddlRegion.Enabled = false;
}

ddlRegion.Items.Insert(0, new ListItem("Select Region", "-1"));

ddlCity.Enabled = false;
ddlCity.Items.Clear();
ddlCity.Items.Insert(0, new ListItem("Select City", "-1"));
}

protected void ddlRegion_SelectedIndexChanged(object sender, EventArgs e)
{
string strRegion = string.Empty;
strRegion = ddlRegion.SelectedValue;
List<string> list = null;
list = RetrieveDataFromXml.GetCityByRegion(strRegion);
ddlCity.Items.Clear();
ddlCity.DataSource = list;
ddlCity.DataBind();
ddlCity.Items.Insert(0, new ListItem("Select City", "-1"));

if (list.Count > 0)
{
ddlCity.Enabled = true;
}
else
{
ddlCity.Enabled = false;
}
}

Conclusion

That is all it takes to produce a cascading DropDownList. In the event that you did not pick up that each DropDownList was posting back to the server revisit the example code in the web form and you will notice AutoPostBack=”True”.

Series DropDownList: Binding XML Data to a DropDownList

Who doesn’t love XML? Extensible Markup Language (XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards.  The design goals of XML emphasize simplicity, generality, and usability over the Internet. It is a textual data format with strong support via Unicode for the languages of the world. Although the design of XML focuses on documents, it is widely used for the representation of arbitrary data structures, for example in web services. Bottom line XML is easily created, consumed, and understood.

In this article we will focus on the basic idea of binding XML data to a DropDownList. When it is all said and done your DropDownList will look similar to the following example.

dropdown list bound with xml data

XML Source

First we need a XML file. In this example we have XML that contains countries, regions, and cities.

<?xml version="1.0" encoding="utf-8" ?>
 <Countries>
 <Country name="Korea">
 <Region name="South Korea">
 <City>
 Seoul
 </City>
 <City>
 Taegu
 </City>
 <City>
 Songtan
 </City>
 </Region>
 </Country>
 <Country name="USA">
 <Region name="California">
 <City>
 Los Angeles
 </City>
 <City>
 Bakersfield
 </City>
 <City>
 Oakland
 </City>
 <City>
 San Diego
 </City>
 </Region>
 <Region name="Missouri">
 <City>
 Kansas City
 </City>
 <City>
 St. Louis
 </City>
 </Region>
 <Region name="Texas">
 <City>
 Dallas
 </City>
 <City>
 Houston
 </City>
 <City>
 San Antonio
 </City>
 </Region>
 </Country>
 </Countries>

Web Form

Next you need to create your form and add the necessary DropDownList that you will be working with, here I have three:

  1. Country
  2. Region
  3. City
<label for="ddlCountry">Country:</label>
 <asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True" Width="160px">
 </asp:DropDownList>
 <label for="ddlRegion">Region:</label>
 <asp:DropDownList ID="ddlRegion" runat="server" AutoPostBack="True" Width="160px">
 </asp:DropDownList>
 <label for="ddlCity">City:</label>
 <asp:DropDownList ID="ddlCity" runat="server" Width="160px"></asp:DropDownList>

Code Behind

So at this point we are ready to begin the process of retrieving and binding the data we will to consume. Before we do this it is important to point out the class will performs the work when it comes to interfacing with the XML file. You will see in the class RetrieveDataFromXML a number of methods which returns a list of countries, regions, and finally cities.

public class RetrieveDataFromXml
 {
 public RetrieveDataFromXml() { }

private static string strXmlPath = HttpContext.Current.Server.MapPath(
 "App_Data") + "\CustomDataSource.xml";

public static List<string> GetAllCountry()
 {
 XmlDocument document = new XmlDocument();
 document.Load(strXmlPath);
 XmlNodeList nodeList = document.SelectNodes("Countries/Country");
 List<string> list = new List<string>();
 foreach (XmlNode node in nodeList)
 {
 list.Add(node.Attributes["name"].Value);
 }
return list;
 }

public static List<string> GetRegionByCountry(string strCountry)
 {
 XmlDocument document = new XmlDocument();
 document.Load(strXmlPath);
 XmlNodeList nodeList = document.SelectNodes(
 "Countries/Country[@name='" + strCountry + "']/Region");
 List<string> list = new List<string>();
 foreach (XmlNode node in nodeList)
 {
 list.Add(node.Attributes["name"].Value);
 }
return list;
 }

public static List<string> GetCityByRegion(string strRegion)
 {
 XmlDocument document = new XmlDocument();
 document.Load(strXmlPath);
 XmlNodeList nodeList = document.SelectNodes(
 "Countries/Country/Region[@name='" + strRegion + "']/City");
 List<string> list = new List<string>();
 foreach (XmlNode node in nodeList)
 {
 list.Add(node.InnerText.ToString());
 }
 return list;
 }
 }

Now all that remains is to begin to populate the DropDownList once the web page is rendered.

protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack)
 {
 // Bind Country dropdownlist
 BindddlCountry();
 ddlRegion.Enabled = false;
 ddlCity.Enabled = false;

// Insert one item to dropdownlist top
 ddlRegion.Items.Insert(0, new ListItem("Select Region", "-1"));
 ddlCity.Items.Insert(0, new ListItem("Select City", "-1"));
 }
 }

public void BindddlCountry()
 {
 List<string> list = RetrieveDataFromXml.GetAllCountry();
 ddlCountry.DataSource = list;
 ddlCountry.DataBind();
 ddlCountry.Items.Insert(0, new ListItem("Select Country", "-1"));
 }

Conclusion

At the end of the day consuming and binding data via XML is really no different that a traditional datasource such as a database. If you’re not familiar with the XmlDocument Class then be sure to head over to MSDN and check out the documentation.

Microsoft Enterprise Library: Caching Application Block

This is a a second article on the topic of the Microsoft Enterprise Library. If you have not read the previous article titled Microsoft Enterprise Library: Data Access Application Block, I recommend you do so.

Introduction to the Caching Application Block

The Enterprise Library Caching Application Block lets developers incorporate a local cache in their applications. It supports both an in-memory cache and, optionally, a backing store that can either be the database store or isolated storage. The Caching Application Block can be used without modification; it provides all the functionality needed to retrieve, add, and remove cached data. Configurable expiration and scavenging policies are also part of the block.

If you have been working with caching outside the Enterprise Library, I believe you will find this application block extremely powerful and easy to use. If you have not taken on the subject of caching before, I believe you also will find this easy to pick up and ultimately boost the performance of your applications. The Enterprise Library Caching Application Block includes the following features:

  • You can use the graphical Enterprise Library configuration tools to manage configuration settings.
  • You can configure a persistent storage location, using either isolated storage or the Enterprise Library Data Access Application Block, whose state is synchronized with the in-memory cache.
  • Administrators can manage the configuration using Group Policy tools.
  • You can extend the block by creating custom expiration policies and storage locations.
  • You are assured that the block performs in a thread-safe manner.

Cache Manager

The cache manager serves the role of managing the cache store exactly as it sounds. It is entirely possible to have multiple cache managers to suit you business requirements. When configuring the cache manager you also have choices when it comes to the backing stores. For example, if you wish to use a database to persist the cache you can do so, but for the purpose of this article I will demonstrate the out of the box configuration which is the server’s memory.

Much like the potential vulnerabilities surrounding the Data Access Block in terms of SQL Injection, the Caching Application Block also has risk that you must understand. For example, do not store sensitive data in the cache and if you must do so, then use encryption.

Once you have the defined your cache manager settings,  you will see similar code withing your web.config:

<configSections>
<section name="cachingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Caching.
Configuration.CacheManagerSettings, Microsoft.Practices.EnterpriseLibrary.Caching,
Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
requirePermission="true" />
</configSections>
<cachingConfiguration defaultCacheManager="RadDev Cache Manager">

Design and Implementation

Turning our attention to the web project, the first step is to add the appropriate references. Add a reference to the Caching Application Block assembly. In Microsoft Visual Studio, right-click your project node in Solution Explorer, and then click Add Reference. Click the Browse tab and find the location of the Microsoft.Practices.EnterpriseLibrary.Caching.dll assembly. Select the assembly, and then click OK to add the reference.

Now that the references are in place, we need the using statements.

//Enterprise Library
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Caching.Expirations;

Now the fun begins. We will query the database and after this first round trip we will cache the data. By caching the data the next call to the database essentially does not occur. Back in your webform add a GridView.

<asp:GridView ID="GridViewContactsNoCache" runat="server"></asp:GridView>

Now that we have the GridView established it is time to wire up the necessary code to communicate with the database and then display this data to the end user.

First step here is to create your reference to the cache manager.

ICacheManager cache = EnterpriseLibraryContainer.Current.GetInstance<ICacheManager>();

Depending upon your application’s design you can add a object to cache in the scenario that works best for you. Here I am using a Generic List to create an object type of Contact. I will not go into the details of the List nor the Contact Class, but I will share the code so you can follow the thought process.

SQL Statement

private const string sqlOne = "SELECT FirstName," + " MiddleName, LastName FROM Person.Contact WHERE (LastName =" + " N'adams') AND (FirstName LIKE N'r%') ORDER BY LastName";

Page Load Event

Inside the page load you will notice an if/else statement. Here is where we check if the Contacts object is in cache and if so, we bypass the database round trip and bind the GridView to this cache object.

protected void Page_Load(object sender, EventArgs e) { if (cache.Contains("ContactsCachKey")) { GridViewContactsNoCache.DataSource = cache.GetData("ContactsCachKey"); } else { GridViewContactsNoCache.DataSource = GetContacts(); } GridViewContactsNoCache.DataBind(); // How many object are in cache? LabelCacheObjects.Text = string.Format("The Cache Manager contains {0} objects.", cache.Count.ToString()); }

Get the Contacts

In this method you will see the use of the Generic List and immediately before exiting the method I am making another method call to populate the Contacts to cache.

private List<Contact> GetContacts() { using (IDataReader rdr = db.ExecuteReader(CommandType.Text, sqlOne)) { lstContact = new List<Contact>(); while (rdr.Read()) { contact = new Contact(); contact.FirstName = rdr.GetString(0); contact.MiddleName = rdr.GetString(1); contact.LastName = rdr.GetString(2); lstContact.Add(contact); } } PopulateCacheManager(lstContact); return lstContact; } private void PopulateCacheManager(List<Contact> lstContact) { cache.Add("ContactsCachKey", lstContact); }

The Result

Contacts GridView

What If The Data Changes

Of course when one displays data, typically the counterpart is to also update the data. You will be pleased to know that in one line of code you can destroy the Contacts cached object and upon the next execution of displaying the data, the process starts all over. Assume for a moment that that Update button click has processed a database transaction.

protected void ButtonUpdate_Click(object sender, EventArgs e) { cache.Remove("ContactsCachKey"); //here we remove the Contacts cache object }

Conclusion

As you can see, using the Caching Application Block component of the Enterprise Library is not complex. Of course, this article has just begun to scratch the surface in the hopes that your interest is perked.

Examples Via MSDN

The following examples are from the Enterprise Library 5.0, Chapter 5 – A Cache Advance for Your Applications. Proactive Cache Loading The example, Load the cache proactively on application startup, provides a simple demonstration of proactive cache loading. In the startup code of your application you add code to load the cache with the items your application will require. The example creates a list of Product items, and then iterates through the list calling the Add method of the cache manager for each one. You would, of course, fetch the items to cache from the location (such as a database) appropriate for your own application. It may be that the items are available as a list, or—for example—by iterating through the rows in a DataSet or a DataReader.

// Create a list of products - may come from a database or other repository
List<Product> products = new List<Product>();
products.Add(new Product(42, "Exciting Thing",
"Something that will change your view of life."));
products.Add(new Product(79, "Useful Thing",
"Something that is useful for everything."));
products.Add(new Product(412, "Fun Thing",
"Something that will keep the grandchildren quiet."));

// Iterate the list loading each one into the cache
for (int i = 0; i < products.Count; i++)
{
theCache.Add(DemoCacheKeys[i], products[i]);
}

Reactive Cache Loading

Reactive cache loading simply means that you check if an item is in the cache when you actually need it, and—if not—fetch it and then cache it for future use. You may decide at this point to fetch several items if the one you want is not in the cache. For example, you may decide to load the complete product list the first time that a price lookup determines that the products are not in the cache.

The example, Load the cache reactively on demand, demonstrates the general pattern for reactive cache loading. After displaying the contents of the cache (to show that it is, in fact, empty) the code attempts to retrieve a cached instance of the Product class. Notice that this is a two-step process in that you must check that the returned value is not null. As we explained in the section “What’s In My Cache?” earlier in this chapter, the Contains method may return true if the item has recently expired or been removed.

If the item is in the cache, the code displays the values of its properties. If it is not in the cache, the code executes a routine to load the cache with all of the products. This routine is the same as you saw in the previous example of loading the cache proactively.

Console.WriteLine("Getting an item from the cache...");
Product theItem = (Product)defaultCache.GetData(DemoCacheKeys[1]);

// You could test for the item in the cache using CacheManager.Contains(key)
// method, but you still must check if the retrieved item is null even
// if the Contains method indicates that the item is in the cache:
if (null != theItem)
{
Console.WriteLine("Cached item values are: ID = {0}, Name = '{1}', "
+ "Description = {2}", theItem.ID, theItem.Name,
theItem.Description);
}
else
{
Console.WriteLine("The item could not be obtained from the cache.");

// Item not found, so reactively load the cache
LoadCacheWithProductList(defaultCache);
Console.WriteLine("Loaded the cache with the list of products.");
ShowCacheContents(defaultCache);
}

Microsoft Enterprise Library: Data Access Application Block

For those of you who have been using the Enterprise Library from Microsoft then I tip my hat to you. I admit that I have not used this library for a number of years and in most cases the reason is because I have honestly not been in a position to do so. It is a long story so don’t ask. There are a number of reason why you should seriously consider the use of the Enterprise Library and I cannot think of any better reason than those provided directly from Microsoft.

The goals of Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together or individually.

Now that the groundwork has been laid let us get started.

Introduction to the Data Access Library

The Data Access Application Block includes a small number of methods that simplify the most common techniques for accessing a database. Each method encapsulates the logic required to retrieve the data and manage the connection to the database. The methods exposed by the block allow you to execute queries, return data in a range of different formats, populate a DataSet, update the database from a DataSet, perform data access asynchronously (against SQL Server databases), and return data as objects in a suitable format for use with client-side query technologies such as LINQ.

As with anything good there is always its counterpart. For example, if you are going to work with data in a a mechanism that is unique to your application or if your are going to work with a particular database that provides unique features then you may not want to use the Data Access Library. It is also important to understand the level of support. For example, if you are targeting an Oracle database you should not use this library since Microsoft has depreciated the Oracle Client. In this case you should turn to Oracle’s ODP.NET which is native to an Oracle Database.

The Database Connection

The beauty of the Data Access Library is the simplicity of its use. For example, to establish a connection to a database, SQL Server in this case all it takes is a single line of code. First things first is to add the appropriate references to the Enterprise Library Assemblies.

//Enterprise Library
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;

Then comes the database object creation.

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>(“MyDatabase”);

The string value MyDatabase represents the connection to a SQL Server. Yes, it actually is that simple. In fact the instance of database could in fact be defined in a single class so your development team would never have to waste any time in defining a database object again. It is also worth saying that this database object is not yet in an open state. This is good news because so many developers often forget to close out their database connections. Another important benefit is the Data Access Library also will log events to the application event log, but this is a subject that I will go into in a future article.

Inline SQL Statement Example

I want to first say that you should exercise extreme caution when using inline SQL because this type of database interaction is vulnerable to SQL Injection. For the purpose of this example, I will utilize inline SQL.

In this example we will employ two SQL statements. One will retrieve the contact details to include first, middle, and last names. The second statement will return the total number of contacts.

private const string sqlOne = "SELECT FirstName," +
" MiddleName, LastName FROM Person.Contact WHERE (LastName = N'adams') AND" +
" (FirstName LIKE N'r%') ORDER BY LastName";

private const string sqlTwo = "SELECT COUNT(*) AS TotalContacts FROM Person.Contact WHERE (LastName = N'adams') AND" +
" (FirstName LIKE N'r%')";

Build the WebForm

Now turning our attention to the web form we will need to server controls. First is a Gridview and second is a Label.

<asp:GridView ID="ContactsGridView" runat="server"></asp:GridView>
<asp:Label ID="TotalContactsLabel" runat="server" Text=""></asp:Label>

At this point if you have been working with ADO.NET you will find the code behind the webform very familiar. The only thing we are doing different in this example is working with the database object that was covered earlier in this article.

/// <summary>
/// Gets the contacts.
/// </summary>
/// <returns>DataSet of contacts</returns>
private DataSet GetContacts()
{
DataSet dsContacts = db.ExecuteDataSet(CommandType.Text, sqlOne);
return dsContacts;
}

/// <summary>
/// Gets the total contacts.
/// </summary>
/// <returns>Total number of contacts</returns>
private int GetTotalContacts()
{
//because ExecuteScalar returns an object we must cast to an int
int totalContacts = (int)db.ExecuteScalar(CommandType.Text, sqlTwo);
return totalContacts;
}
  1. Internal is for assembly scope (i.e. only accessible from code in the same .exe or .dll)
  2. Private is for class scope (i.e. accessible only from code in the same class)

Now that we have the necessary methods established the next step is to bind the data to the server controls back on the webform. We will do this in the page load event.

/// <summary>
/// Handles the Load event of the Page control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
ContactsGridView.DataSource = GetContacts();
ContactsGridView.DataBind();

TotalContactsLabel.Text = string.Format("There are a total of {0} contacts", GetTotalContacts().ToString());
}

Now upon execution of the web application you will be presented the following:

Contacts Data Displayed

Conclusion

As you can see, using the Data Access Library component of the Enterprise Library is not complex. Of course, this article has just begun to scratch the surface in the hopes that your interest is perked.

Examples Via MSDN

The following examples are from the Enterprise Library 5.0, Chapter 2 – Much ADO about Data Access.

Reading Rows Using a Query with No Parameters

Simple queries consisting of an inline SQL statement or a stored procedure, which take no parameters, can be executed using the ExecuteReader method overload that accepts a CommandType value and a SQL statement or stored procedure name as a string.

// Call the ExecuteReader method by specifying just the stored procedure name.
using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))
{
// Use the values in the rows as required.
}

Reading Rows Using an Array of Parameter Values

While you may use simple no-parameter stored procedures and SQL statements in some scenarios, it’s far more common to use queries that accept input parameters that select rows or specify how the query will execute within the database server. If you use only input parameters, you can wrap the values up as an Object array and pass them to the stored procedure or SQL statement. Note that this means you must add them to the array in the same order as they are expected by the query, because you are not using names for these parameters—you are only supplying the actual values. The following code shows how you can execute a stored procedure that takes a single string parameter.

// Call the ExecuteReader method with the stored procedure
// name and an Object array containing the parameter values.
using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState",
new object[] { "Colorado" }))
{
// Use the values in the rows as required - here we are just displaying them.
DisplayRowValues(reader);
}

Retrieving XML Data

The Data Access block provides the ExecuteXmlReader method for querying data as XML. It takes a SQL statement that contains the FOR XML statement and executes it against the database, returning the result as an XmlReader. You can iterate through the resulting XML elements or work with them in any of the ways supported by the XML classes in the .NET Framework. However, as SQLXML is limited to SQL Server (the implementations of this type of query differ in other database systems), it is only available when you specifically use the SqlDatabase class (rather than the Database class).

The following code shows how you can obtain a SqlDatabase instance, specify a suitable SQLXML query, and execute it using the ExecuteXmlReader method.

// Resolve a SqlDatabase object from the container using the default database.
SqlDatabase sqlServerDB
= EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;

// Specify a SQL query that returns XML data.
string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";

// Create a suitable command type and add the required parameter
// NB: ExecuteXmlReader is only available for SQL Server databases
using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))
{
xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado"));
using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))
{
// Iterate through the elements in the XmlReader
while (!reader.EOF)
{
if (reader.IsStartElement())
{
Console.WriteLine(reader.ReadOuterXml());
}
}
}
}

Retrieving Single Scalar Values

A common requirement when working with a database is to extract a single scalar value based on a query that selects either a single row or a single value. This is typically the case when using lookup tables or checking for the presence of a specific entity in the database. The Data Access block provides the ExecuteScalar method to handle this requirement. It executes the query you specify, and then returns the value of the first column of the first row of the result set as an Object type. This means that it provides much better performance than the ExecuteReader method, because there is no need to create a DataReader and stream the results to the client as a row set. To maximize this efficiency, you should aim to use a query that returns a single value or a single row.

The ExecuteScalar method has a set of overloads similar to the ExecuteReader method we used earlier in this chapter. You can specify a CommandType (the default is StoredProcedure) and either a SQL statement or a stored procedure name. You can also pass in an array of Object instances that represent the parameters for the query. Alternatively, you can pass to the method a Command object that contains any parameters you require.

The following code demonstrates passing a Command object to the method to execute both an inline SQL statement and a stored procedure. It obtains a suitable Command instance from the current Database instance using the GetSqlStringCommand and GetStoredProcCommand methods. You can add parameters to the command before calling the ExecuteScalar method if required. However, to demonstrate the way the method works, the code here simply extracts the complete row set. The result is a single Object that you must cast to the appropriate type before displaying or consuming it in your code.

// Create a suitable command type for a SQL statement.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sqlCmd
= defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a SQL statement: {0}",
defaultDB.ExecuteScalar(sqlCmd).ToString());
}

// Create a suitable command type for a stored procedure.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a stored procedure: {0}",
defaultDB.ExecuteScalar(sprocCmd).ToString());
}

Updating Data

The following code from the example application for this chapter shows how you can use the ExecuteNonQuery method to update a row in a table in the database. It updates the Description column of a single row in the Products table, checks that the update succeeded, and then updates it again to return it to the original value (so that you can run the example again). The first step is to create the command and add the required parameters, as you’ve seen in earlier examples, and then call the ExecuteNonQuery method with the command as the single parameter. Next, the code changes the value of the command parameter named description to the original value in the database, and then executes the compensating update.

string oldDescription = "Carries 4 bikes securely; steel construction, fits 2" receiver hitch.";
string newDescription = "Bikes tend to fall off after a few miles.";

// Create command to execute the stored procedure and add the parameters.
DbCommand cmd = defaultDB.GetStoredProcCommand("UpdateProductsTable");
defaultDB.AddInParameter(cmd, "productID", DbType.Int32, 84);
defaultDB.AddInParameter(cmd, "description", DbType.String, newDescription);

// Execute the query and check if one row was updated.
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded.
}
else
{
Console.WriteLine("ERROR: Could not update just one row.");
}

// Change the value of the second parameter
defaultDB.SetParameterValue(cmd, "description", oldDescription);

// Execute query and check if one row was updated
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded.
}
else
{
Console.WriteLine("ERROR: Could not update just one row.");
}

Be sure to check out Chapter 2 – Much ADO about Data Access for much more details and examples.