CODEWITHSUNDEEP
c#sql.netsql-serverado.net
Julius
Julius

Reputation: 129

Save sql return in a string

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?

Upvotes: 2

Views: 310

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112362

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));
            }
        }
    }
}

Upvotes: 2

Habib
Habib

Reputation: 223267

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);

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156978

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());

Upvotes: 10

Related Questions