C#.NET: Getting query results with SqlDataAdapter

September 19th, 2008

While the SqlDataSource control is a nifty little thing, oftentimes a developer has to have more control over the page lifecycle (asp.net) or doesn’t want to use a BindingSource (winforms). Instead, you just want to query your database and get a DataSet back. Maybe you’re building a data access layer, or you’re using someone else’s framework, or it’s just biting you in the butt that binding with SqlDataSource fires off events in a completely unintuitive order.

We’ve seen how to execute a query that just returns one thing with ExecuteScalar. Now, though, we need entire results. And for whatever reason, we want a DataSet, not a SqlDataReader.

Luckily for us, querying and getting results into a DataSet using SqlDataAdapter is extremely easy, and works the same both in web forms and winforms. The following code is a full working example for querying Northwind (I just can’t get used to AdventureWorks yet, sorry) and getting a DataSet back. You can Bind a DataGridView to it, or a GridView, or loop the tables, or whatever.

Notes:
1. You have to have a using System.Data and using System.Data.SqlClient.
2. Your connection string should normally be stored in Settings (winforms) or web.config (web forms). In this example, I was playing with winforms, so it’s a Setting. For web forms, you’d use ConfigurationManager.ConnectionStrings.


private void button1_Click(object sender, EventArgs e)
{
    DataSet myDataSet = new DataSet();
    using (SqlConnection sqlconnection = new SqlConnection(Properties.Settings.Default.Northwind))
    {
        SqlCommand command = new SqlCommand("select * from products", sqlconnection);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(myDataSet);
    }
}

It’s really that easy. Happy coding!

If you found this post helpful, please spread the love and share using one of the bookmark links below. Thanks!

Tags: , , , ,
Posted in .NET | 3 Comments »

.NET: Check for exact match with ExecuteScalar

May 12th, 2008

csharp.netYet another quick and dirty example of using ExecuteScalar to check for a value in the database. This time, we’re looking for an exact match, case-sensitive. You might use this to check for a password match, for example. A user enters a value in the textbox, and we query for the value they entered. We borrow String Compare so we can check for a case-sensitive match.

Sometimes you just have an easier time using regular commands. Don’t try to fit a round peg in a square hole. If SqlDataSource works for what you want, which 90% or so of the time it will, then by all means use it! Otherwise, don’t be afraid of your friend SqlCommand!

On to the code. Of course, for production use, add validation and error checking. This is just showing you the basics.

The ASPX source code:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default9.aspx.cs" Inherits="Default9" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Example of checking if a value exists in the database</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Quick and dirty example: ExecuteScalar and exact case matching<br />
        <br />
        Check for product:
        <asp:TextBox ID="txtProduct" runat="server"></asp:TextBox><br />
        <br />
        <asp:Button ID="btnCheckit" runat="server" OnClick="btnCheckit_Click" Text="Check" /><br />
        <br />
        Exists? <asp:Label ID="lblExists" runat="server"></asp:Label></div>
    </form>
</body>
</html>

 

The Code Behind page:


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Default9 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void btnCheckit_Click(object sender, EventArgs e)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.CommandText = "select productname from products where productname=@prodname";
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@prodname", txtProduct.Text);
                connection.Open();
                string p = Convert.ToString(command.ExecuteScalar());
                connection.Close();
                if (String.Compare(txtProduct.Text, p, false) == 0) lblExists.Text = "yep";
                else lblExists.Text = "nope";
            }
        }
    }
}

 

Happy coding!

Tags: , , , ,
Posted in .NET | No Comments »

.NET: Check for existence with ExecuteScalar

May 9th, 2008

csharp.netQuick and dirty sample, kids. Add error checking and validators as appropriate. This is sample code against our friend Northwind to simply take user input and check if a product matching that name exists in the database. Mostly this is meant to showcase setting the parameter from the textbox, using a count(1) in the query, and using ExecuteScalar.

While SqlDataSource is neato keen, sometimes you just complicate your life using it. It’s there for when it’s convenient — don’t think you HAVE to use it.

ASPX source:


<%@ Page Language=C# AutoEventWireup="true" CodeFile="Default8.aspx.cs" Inherits="Default8" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>ExecuteScalar Example</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Quick and dirty example: ExecuteScalar<br />
        <br />
        Check for product:
        <asp:TextBox ID="txtProduct" runat="server"></asp:TextBox><br />
        <br />
        <asp:Button ID="btnCheckit" runat="server" OnClick="btnCheckit_Click" Text="Check" /><br />
        <br />
        Exists? <asp:Label ID="lblExists" runat="server"></asp:Label></div>
    </form>
</body>
</html>

Code Behind page:


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Default8 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void btnCheckit_Click(object sender, EventArgs e)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.CommandText = "select count(1) from products where productname=@prodname";
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@prodname", txtProduct.Text);
                connection.Open();
                int c = Convert.ToInt32(command.ExecuteScalar());
                connection.Close();
                if (c > 0) lblExists.Text = "yep";
                else lblExists.Text = "nope";
            }
        }
    }
 }

 

Happy coding!

Tags: , , , ,
Posted in .NET | 3 Comments »

The Cheater Way To Pass Comma-delimited Text to a Stored Proc

May 1st, 2008

If you’ve been developing with SQL for long, you’ve wanted to have a stored procedure that can take a comma-delimited string of text as a parameter for use in an IN clause.

For example, say we want to query a Products table for a list of products, ‘Chai,Mishi Kobe Niku,Carnarvon Tigers’. We pass that to the stored procedure as @prods and have:

SELECT * FROM Products WHERE ProductName IN (@prods)

We get 0 results! That’s because the query is executed exactly as we sent it – the commas are seen as part of the input, and is the equivalent of running

SELECT * FROM Products WHERE ProductName IN ('Chai,Mishi Kobe Niku,Carnarvon Tigers')

instead of what we intended:

SELECT * FROM Products WHERE ProductName IN ('Chai','Mishi Kobe Niku','Carnarvon Tigers')

The usual remedy for this is to create a temp table. I hate temp tables.

So I decided to cheat. An IN only returns exact matches anyway, and we have control of what is passed into the stored procedure, so we can guarantee that no spaces exist between the commas in the text strings we’re trying to match.

Key words: text strings we’re matching.

You can cheat by matching against the strings delimited by the commas, as long as your table isn’t huge. You put a comma at the beginning and the end of the string, and check for your column value to match any of the values delimited by commas.

Here is how it would work.

CREATE PROCEDURE GetProductsByName
@prods VARCHAR(4000)
AS
BEGIN
SET @prods = ',' + @prods + ','

SELECT * FROM PRODUCTS
WHERE
CHARINDEX(',' + ProductName + ',', @prods) > 0
END

Now, execute it:

exec dbo.GetProductsByName @prods='Chai,Mishi Kobe Niku,Carnarvon Tigers'

Happy coding!

Tags: , , , ,
Posted in SQL | No Comments »

How To Get The Month Name From Date [SQL Server]

April 23rd, 2008

I found myself wanting to pull some data out of the database by month and year using a stored procedure to group things and get counts. Sure, I could use month numbers and make the consuming application display “November”, but that’s just silly, since it was MY application consuming it and all.

To get the month part or year part of a date in SQL Server, you use the DATENAME built-in function like so:

DATENAME(MM, CASE_FILE_RECEIVED) AS MNTH,

DATENAME(YY, CASE_FILE_RECEIVED) AS YR,

Short and sweet.

Got any other short and sweet “tricks”? Share them in the comments!

Tags: , , ,
Posted in SQL | No Comments »

« Older Entries