Click here to Skip to main content
13,150,178 members (57,714 online)
Click here to Skip to main content
Add your own
alternative version

Stats

21.8K views
27 bookmarked
Posted 29 Aug 2007

Different Forms to Select Data using SQL and .NET

, 29 Aug 2007
Rate this:
Please Sign up or sign in to vote.
This article gives a brief introduction to development tools available that help you 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

  • 29th August, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Luis Ramirez
Software Developer (Senior) www.sqlnetframework.com
Mexico Mexico
Luis Ramirez is creator and owner of ADO.NET Accelerator. You can use the FREE ADO.NET Accelerator version to reduce more than 50% ADO.NET code from your data access layer. Luis Ramirez is a Microsoft Certified Professional specialized in .NET development. If you want to contact him to work in your projects or for any other inquiry that you have write him to lramirez [at] sqlnetframework [dot] com.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170924.2 | Last Updated 29 Aug 2007
Article Copyright 2007 by Luis Ramirez
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid