What do C# Table Adapters actually return?

Question

Hello, stack overflow !

I'm working on an application that manipulates SQL tables in a windows form application. Up until now, I've only been using the pre-generated Fill queries, and self-made update and delete queries (which return nothing).

I am interested in storing the value of a single value from a single column (an 'nchar(15)' name), and though I have easily written the SQL code to return that value to the application, I have no idea what it will be returned as.

SELECT [Contact First Name] FROM ContactSkillSet
WHERE [Contact ID] = @CurrentID

Can the result be stored directly as a string? Do I need to cast it? Invoke a toString method?

Thanks!

Edit: Interestingly enough, the following works:

String firstName = this.contactSkillSetTableAdapter.GrabFirstName(userKey);

The joys of the Visual Studio data wizard, I suppose.

Answer 1

A SQL statement that returns a single value is called a Scalar query. Normally Statements that use aggregate commands like COUNT, MAX, and MIN fall into this category. Other statements like your example are considered scalar as well.

Basically you can treat it like any other query. Check that there is at least one row, and if there is one, cast row zero column zero to the appropriate data type. If you are using Strongly Typed Data sets there are some features to make this easier, but it is not required to do so.

Answer 2

If you plan to return a single result from your query you can always use the ExecuteScalar method on a SqlCommand class and then cast it to a string (see example in first link, it casts it to an Int32, but same principle for Strings).

Answer 3

A TableAdapter is used to fill a DataTable. The result of your example query will still be a DataTable but with a single column in a single row. You can get the value using:

var adapter = new SomeKindOfTableAdapter();
var dataTable = new DataTable();

adapter.Fill(dataTable);

string contactFirstName = (string)dataTable.Rows[0][0];

There are other ways to get your value without using a Table Adapter though:

string query = @"SELECT [Contact First Name] 
                FROM ContactSkillSet 
                WHERE [Contact ID] = @Current";

string result;

using(SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    using(SqlCommand command = new SqlCommand(query, conn))
    {
        result = (string)command.ExecuteScalar();
    }
}