Save sql return in a string

Question

I´m trying to save information from database to a string.

How is this possible with the following code?

using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT something FROM table");
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;
    connection.Open();
    cmd.ExecuteNonQuery();
    connection.Close();
}

Is it possible to use this for getting some value from database saved in a string?

Do you need the value of a single row? Do you need the value of a single column? Your question is a bit vague — Steve
– Steve, Commented Mar 27, 2014 at 15:43

Patrick Hofman · Accepted Answer · 2014-03-27 15:40:44Z

10

Instead of cmd.ExecuteNonQuery(), you need to call cmd.ExecuteScalar().

See MSDN for the documentation.

string result = (string)cmd.ExecuteScalar();

As @ThorstenDittmar pointed out correctly, the casting only works if the value from the database is actually a string (or something that can be mapped to it, like varchar).

You can use this to be sure:

object result = cmd.ExecuteScalar();

string s = result != null? result.ToString() : null;

Or

string s = Convert.ToString(cmd.ExecuteScalar());

answered Mar 27, 2014 at 15:40

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thorsten Dittmar Over a year ago

This works only if the object returned by cmd.ExecuteScalar() actually is a string. To store any object value as string you should use string result = cmd.ExecuteScalar().ToString();

Olivier Jacot-Descombes · Accepted Answer · 2014-03-27 15:54:50Z

2

ExecuteNonQuery is for INSERT, UPDATE, and DELETE statements. Other SQL commands like DDL commands or db administration commands where no result set is returned or the result can be discarded may also be used.

ExecuteScalar can be used for SELECT statements where only the first field of the first row is needed.

When more fields and/or more rows have to be returned, use ExecuteReader.

Example of using ExecuteReader:

using (var conn = new SqlConnection(connectionString)) {
    using (var cmd = new SqlCommand("SELECT name, loc FROM t WHERE id=@id", conn)) {
        cmd.Parameters.AddWithValue("@id", id);
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            int nameOrdinal = reader.GetOrdinal("name");
            int locationOrdinal = reader.GetOrdinal("loc");
            while (reader.Read()) {
                Console.WriteLine("Name = {0}, Location = {1}",
                    reader.GetString(nameOrdinal),
                    reader.GetString(locationOrdinal));
            }
        }
    }
}

edited Mar 27, 2014 at 15:54

answered Mar 27, 2014 at 15:46

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

2 Comments

Marc Gravell Over a year ago

Well, you could use ExecuteNonQuery for something that used return, out or ref, but admittedly it would be pretty unusual to do so ;p

Olivier Jacot-Descombes Over a year ago

@MarcGravell: Added clarification.

Habib · Accepted Answer · 2014-03-27 15:43:07Z

1

You need to fill the records in a SqlDatareader or a DataSet/DataTable using SqlDataAdapter. Currently you are using ExecuteNonQuery which is usually used for INSERT/UPDATE statements.

DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("SELECT something FROM table"))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }
}

This would return your results from your query in DataTable.

If you want to get the results back in a List<string> (since you are requesting a single column from your table and chances are there would be multiple rows) you can do:

List<string> returnedList = new List<string>();
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("SELECT something FROM table"))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        connection.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                returnedList.Add(Convert.ToString(reader["something"]));
            }
        }
    }
}

Then to get a single string you can do:

string singleString = string.Join(Environment.NewLine, returnedList);

answered Mar 27, 2014 at 15:43

Habib

224k30 gold badges420 silver badges446 bronze badges

3 Comments

Marc Gravell Over a year ago

I can't think of any good reason to involve SqlDataAdapter or DataTable just to read a string... actually, I can barely think of any good reason to involve SqlDataAdapter or DataTable at all (caveat: ok, very rare reasons to do with meta-programming and unknown data)

Habib Over a year ago

@MarcGravell, Just trying to show that the records returned would probably be a set. Added an example to get List<string> back as well.

Habib Over a year ago

Also I am not sure if OP is really needing a single string back from his/her query.

Collectives™ on Stack Overflow

Save sql return in a string

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related