Real Life SQL and .NET : Using SQL with C#: Part IX

Querying Data with ADO.NET

You can also perform a parameterized select using the SqlDataAdapter object. The following sample shows how you can modify the data selected using the value posted from a select HtmlControl. The SqlDataAdapter maintains a Parameters collection that can be used to replace variable identifiers (denoted by a "@" in front of the name) with values. You add a new SqlParameter to this collection that specifies the name, type, and size of the parameter, and then set its Value property to the value of the select.

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar, 2));
myCommand.SelectCommand.Parameters["@State"].Value = MySelect.Value;

Important: Note that the DataGrid's EnableViewState property has been set to false. If the data will be populated in each request, there is no benefit to having the DataGrid store state information to be sent through a round trip with form posts. Because the DataGrid stores all of its data when maintaining state, it is important to turn it off when appropriate to improve the performance of your pages. DataGrid2.aspx statically populates the values of the select box, but this will not work well if those values ever change in the database. Because the select HtmlControl also supports an IEnumerable DataSource property, you can use a select query to dynamically populate the select box instead, which guarantees that the database and user interface are always in sync. The following sample demonstrates this process.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
public void GetAuthors_Click(Object sender, EventArgs E)
{
String selectCmd = "select * from Authors where state = @State";
SqlConnection myConnection = new SqlConnection("server=(local)
\NetSDK;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar, 2));
myCommand.SelectCommand.Parameters["@State"].Value = MySelect.Value;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource= ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();
}
</script>
<body style="font: 10pt verdana">
<form runat="server" ID="Form2">
<h3><font face="Verdana">Parameterized Select to a DataGrid Control</font></h3>
Select a State:
<select id="MySelect" runat="server" NAME="MySelect">
<option>CA</option>
<option>IN</option>
<option>KS</option>
<option>MD</option>
<option>MI</option>
<option>OR</option>
<option>TN</option>
<option>UT</option>
</select>
<input type="submit" OnServerClick="GetAuthors_Click" Value="Get Authors" runat="server" ID="Submit1" NAME="Submit1"/><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
nableViewState="false"/>
</form>
</body>
</html>

or take a look this example:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
SqlConnection myConnection;
protected void Page_Load(Object Src, EventArgs E)
{
myConnection = new SqlConnection("server=(local)
\NetSDK;database=pubs;Trusted_Connection=yes");
if (!IsPostBack)
{
SqlDataAdapter myCommand = new SqlDataAdapter("select distinct State from Authors",
yConnection);
DataSet ds = new DataSet();
yCommand.Fill(ds, "States");
MySelect.DataSource= ds.Tables["States"].DefaultView;
MySelect.DataBind();
}
}
public void GetAuthors_Click(Object sender, EventArgs E)
{
String selectCmd = "select * from Authors where state = @State";
SqlConnection myConnection = new SqlConnection("server=(local)
\NetSDK;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@State",
qlDbType.NVarChar, 2));
myCommand.SelectCommand.Parameters["@State"].Value = MySelect.Value;
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource= ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();
}
</script>
<body style="font: 10pt verdana">
<form runat="server" ID="Form2">
<h3><font face="Verdana">Dynamic Parameterized Select to a DataGrid
ontrol</font></h3>

Select a State:

<select id="MySelect" DataTextField="State" runat="server" NAME="MySelect"/>
<input type="submit" OnServerClick="GetAuthors_Click" Value="Get Authors"
unat="server" ID="Submit1" NAME="Submit1"/><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</form>
</body>
</html>

To summarize, the classes inherit from each other as follows:

DataAdapter
DbDataAdapter
SqlDataAdapter
OleDbDataAdapter
OdbcDataAdapter

Manipulation data using SQL in C#

Let's work on authors table from pubs database. We need import the namespaces that contain the classes to use to connect to the chosen database. The System.Data namespace contains the DataSet class description and System.Data.SqlClient contains the data adapter class's references for MS SQL Server database:

We can provide two parameters in the constructor: a SQL SELECT statement string and a connection string. Internally, the SqlDataAdapter creates a SqlConnection object, passing it the connection string. Moreover, it creates a SqlCommand object that will fill the DataSet object with the SQL statement string provided:

namespace SQLExample1
{
class ClassFirst
{
static void Main(string[] args)
// Create the data afapter object pointing to the authors table
SqlDataAdapter dadp = new SqlDataAdapter("SELECT au_id, au_lname,
au_fname FROM athors", "Server=localhost;database=pubs;uid=sa;pwd=;";
// Create a dataset to contain the records
DataSet dset = new DataSet("Authors");
// Fill the dataset creating an internal table called author
dadp.Fill(dset, "author");
// Loop through the columns created by data adapter printing
// some properties
foreach(DataColumn co in dset.Tables["author"].Columns)
{
Console.WriteLine("Column name: (0)", co.ColumnsName);
Console.WriteLine("DataType: (0)", co.DataType);
Console.WriteLine("MaxLength: (0)", co.MaxLength);
Console.WriteLine("AllowNulls?: (0)", co.AllowNull);Console.WriteLine(" ");
Console.WriteLine(" ");
}
}
}

Now we create the DataSet object that will contain the record retrieved by the execution of the SQL statement. We use SQL in a console application. We can convert it to ASP.NET web forms or Windows forms easily. SELECT and other SQL statements are very important part of Fill method. At the next part we will discuss them together.

continue article