Queries

ADO.NET provides a number of classes for interacting databases such as SQL Server, Access, and Oracle via OLEDB and ODBC. There are classes specifically designed for SQL Server in the System.Data.SqlClient namespace, and classes designed for a broader range of databases in the System.Data.OleDb namespace.

System.Data Namespace

The System.Data Namespace forms the heart of data access in the .NET Framework. Here are the most frequently used classes when accessing and manipulating data from a Microsoft SQL Server database.

SqlConnection Encapsulates a connection to a SQL Server database

SqlCommand Used to issue SQL commands and run Stored Procedures

SqlDataAdapter Fills a DataSet with the results of a SQL Query or Command

SqlReader Forward-only recordset of data

DataSet Disconnected recordset of data

Ad Hoc Queries

The following ASP.NET page shows how to run an ad hoc query against a SQL Server database and bind it to a datagrid control.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>

<script runat="server">

void Page_Load(Object sender, EventArgs e)
{
        SqlConnection myConnection;
        SqlDataAdapter myAdapter;
        string CmdStr;
        string ConnStr;
        DataSet ds = new DataSet();

        // Establish Connection
        ConnStr = "server=localhost;uid=sa;pwd=;database=pubs";
        myConnection = new SqlConnection(ConnStr);

        // Run Query and Fill DataSet
        CmdStr = "select * from authors order by au_lname";
        myAdapter = new SqlDataAdapter(CmdStr, myConnection);
        myAdapter.Fill(ds, "Authors");

        // Bind to Grid
        ArticleGrid.DataSource = ds.Tables["Authors"].DefaultView;
        ArticleGrid.DataBind();
}

</script>
<html>
<body>
    <h1>Authors</h1>
    <asp:DataGrid id="ArticleGrid" runat="server" width="100%" />
</body>
</html>

Stored Procedures

The following code snippet, structured as a console application, shows how to run a stored procedure with input, output, and return parameters.

using System;
using System.Data;
using System.Data.SqlClient;

class Sample
{
    public static void Main()
    {
        //Create Connection
        SqlConnection nwindConn = new SqlConnection("Data Source=localhost;User ID=sa;Password=;Initial Catalog=northwind");
        nwindConn.Open();

        //Create Command w/Parameters
        SqlCommand sc = new SqlCommand("InsertCategory", nwindConn);
        sc.CommandType = CommandType.StoredProcedure;

            //Param: Return Value
            SqlParameter myParm = sc.Parameters.Add("@RowCount", SqlDbType.Int);
            myParm.Direction = ParameterDirection.ReturnValue;

            //Param: Input
            sc.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");
            sc.Parameters["@CategoryName"].Value = "New Category Name";

            //Param: Output
            myParm = sc.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
            myParm.Direction = ParameterDirection.Output;

        sc.ExecuteNonQuery();

        //Get Return Value
        Int32 rowCount = (Int32)sc.Parameters["@RowCount"].Value;

        //Cleanup
        nwindConn.Close();

        //Confirm
        Console.Write("Added " + rowCount + " row.");

    }
}

Additional Resources

Accessing Data with ADO.NET