Different Forms to Select Data using SQL and .NET

Introduction

In this article, I will show you different development tools that you can use to retrieve data from a database server using SQL and .NET. As it is a very common task for .NET data applications, I think it is good to know the alternatives available to make our job easier.

The examples contained in this article are very simple. I will give you a brief illustration of each development tool. If you want further details, you can go to the respective Web sites. For the following examples, I will assume that you have a table named User with two fields: FirstName and LastName. I assume that you are using Microsoft SQL server.

The code snippets are written in C# but you can use any .NET language.

Selecting Data from Scratch

The first option that you have to retrieve data is to use the data access classes provided by the .NET Framework. The example below shows you how to retrieve a record in the User database table based on the FirstName and LastName.

SqlConnection connection = 
    new SqlConnection(@"Data Source=(local)\sqlexpress;Initial Catalog=MyDb;");
SqlCommand command = 
    new SqlCommand("SELECT * FROM USERACCOUNT 
              WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME", connection);

SqlParameter firstNameParameter = new SqlParameter("@FIRSTNAME", SqlDbType.NVarChar, 50);
firstNameParameter.Value = "Greg";
command.Parameters.Add(firstNameParameter);

SqlParameter lastNameParameter = new SqlParameter("@LASTNAME", SqlDbType.NVarChar, 50);
lastNameParameter.Value = "Gobind";
command.Parameters.Add(lastNameParameter);

connection.Open();

SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

if (reader.Read())
{
    string firstName = reader["FIRSTNAME"].ToString();
    string lastName = reader["LASTNAME"].ToString();
}

reader.Close();

Selecting Data using Microsoft Data Access Application Block

The Microsoft Data Access Application Block contains data access code that helps you call stored procedures and SQL text commands and execute them against a Microsoft SQL server database. For more information, read this.

SqlParameter firstNameParameter = 
    new SqlParameter("@FIRSTNAME", SqlDbType.NVarChar, 50);
firstNameParameter.Value = "Greg";

SqlParameter lastNameParameter = new SqlParameter("@LASTNAME", SqlDbType.NVarChar, 50);
lastNameParameter.Value = "Gobind";

SqlParameter[] parameters = new SqlParameter[] 
    { firstNameParameter, lastNameParameter };

SqlDataReader reader = 
    SqlHelper.ExecuteReader(@"Data Source=(local)\sqlexpress;Initial Catalog=MyDb;", 
    CommandType.Text, 
    "SELECT * FROM USERACCOUNT WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME", 
    parameters);

if (reader.Read())
{
    string firstName = reader["FIRSTNAME"].ToString();
    string lastName = reader["LASTNAME"].ToString();
}

reader.Close();

Selecting Data using Vanilla DAL (Open Source)

The Vanilla Open Source project allows you to reduce the quantity of data access code that you need to write. In the example below, you won't see SQL code because it is stored in an XML file. For more information read this.

IDataSource dataSource = DataSourceFactory.GetDataSource();
IDataCommand command = dataSource.GetCommand("SelectUserAccountByName");
command.Parameters["FIRSTNAME"].Value = "Greg";
command.Parameters["LASTNAME"].Value = "Gobind";

IDataReader reader = command.ExecuteReader();

if (reader.Read())
{
    string firstName = reader["FIRSTNAME"].ToString();
    string lastName = reader["LASTNAME"].ToString();
}

reader.Close();

Selecting Data using the SqlNetFramework

The SqlNetFramework is a software development tool that helps you to develop .NET data applications faster and easier. With a single line of code, you can execute an SQL text command and/or a stored procedure. You can execute them against a Microsoft SQL Server 2000+, Microsoft Access 97+, Oracle 10g+ or any other database that supports the generic Odbc and OleDb data providers. The SQL code is stored in an independent SQL data store (XML file, Microsoft SQL Server database, Oracle database). The SqlNetFramework allows you to implement your own functionality for any database server or SQL data store not included yet within the framework. This tool helps you to reduce the quantity of code that you usually need to codify. For more information read this.

SqlNetFramework.CiOrderedDictionary parameterValues = 
    new SqlNetFramework.CiOrderedDictionary();

parameterValues.Add("FirstName", "Greg");
parameterValues.Add("LastName", "Gobind");

IDataReader reader = SqlNetFramework.Management.DbManager.Instance.ExecuteReader
                            (0, "MyConnection", 
                             parameterValues);

if (reader.Read())
{
    string firstName = reader["FIRSTNAME"].ToString();
    string lastName = reader["LASTNAME"].ToString();
}

reader.Close();

As you can see, there are different options that you have available to select data against a database server. I think that we need to keep our projects as simple as possible. If there are development tools that can help make our job faster and easier, it would be good to use them.

History

• 29^th August, 2007: Initial post