How can I get SQL result into a STRING variable?

Question

I'm trying to get the SQL result in a C# string variable or string array. Is it possible? Do I need to use SqlDataReader in some way? I'm very new to C# functions and all, used to work in PHP, so please give a working example if you can (If relevant I can already connect and access the database, insert and select.. I just don't know how to store the result in a string variable).

Is it a `SELECT` statement for example? _please give a working example_ is not a good way to ask by the way. Please read [FAQ] and [ask] — Soner Gönül, Dec 23 '13 at 12:05

score 10 · Accepted Answer · answered Dec 23 '13 at 12:09

This isn't the single greatest example in history, as if you don't return any rows from the database you'll end up with an exception, but if you want to use a stored procedure from the database, rather than running a SELECT statement straight from your code, then this will allow you to return a string:

public string StringFromDatabase()
    {
        SqlConnection connection = null;

        try
        {
            var dataSet = new DataSet();

            connection = new SqlConnection("Your Connection String Goes Here");
            connection.Open();

            var command = new SqlCommand("Your Stored Procedure Name Goes Here", connection)
            {
                CommandType = CommandType.StoredProcedure
            };

            var dataAdapter = new SqlDataAdapter { SelectCommand = command };

            dataAdapter.Fill(dataSet);

            return dataSet.Tables[0].Rows[0]["Item"].ToString();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
    }

It can definitely be improved, but it would give you a starting point to work from if you want to go down a stored procedure route.

This isn't the single greatest comment in history, but to avoid exception throwing when returning no rows - you can add the condition ```dataSet.Tables[0].Rows.Count > 0``` before accessing any rows :) — Albus_Dalbador, Jul 04 '23 at 19:54

Sudhakar Tillapudi · Answer 2 · 2013-12-23T12:36:31.313

4

Try This:

SqlConnection con=new SqlConnection("/*connection string*/");               
SqlCommand SelectCommand = new SqlCommand("SELECT email FROM table1", con);
SqlDataReader myreader;
con.Open();

myreader = SelectCommand.ExecuteReader();

List<String> lstEmails=new List<String>();
while (myreader.Read())
 {
    lstEmails.Add(myreader[0].ToString());
    //strValue=myreader["email"].ToString();
    //strValue=myreader.GetString(0);
 }
con.Close();

accessing the Emails from list
lstEmails[0]->first email
lstEmails[1]->second email

...etc.,

edited Dec 23 '13 at 12:36

answered Dec 23 '13 at 12:10

Sudhakar Tillapudi

25,935
5
37
67

Seems to work ok, but the results are concatenated. (strValue[0] returns first letter not first value) Guess I need a separator or something? – Vantalk Dec 23 '13 at 12:34
@Vantalk: see my edited answer with list `lstEmails` for adding all Emails – Sudhakar Tillapudi Dec 23 '13 at 12:36
I tried the edited version and printing with Console.Write(lstEmails) but it wont return any values and just repeats: System.Collections.Generic.List`1[System.String] – Vantalk Dec 23 '13 at 13:29

score 2 · Answer 3 · answered Dec 23 '13 at 12:10

You could use an SQL Data Reader:

string sql = "SELECT email FROM Table WHERE Field = @Parameter";
string variable;
using (var connection = new SqlConnection("Your Connection String"))
using (var command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Parameter", someValue);
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        //Check the reader has data:
        if (reader.Read())
        {
            variable = reader.GetString(reader.GetOrdinal("Column"));
        }
        // If you need to use all rows returned use a loop:
        while (reader.Read())
        {
            // Do something
        }
    }
}

Or you could use SqlCommand.ExecuteScalar()

string sql = "SELECT email FROM Table WHERE Field = @Parameter";
string variable;
using (var connection = new SqlConnection("Your Connection String"))
using (var command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Parameter", someValue);
    connection.Open();
    variable = (string)command.ExecuteScalar();
}

score 0 · Answer 4 · answered Dec 23 '13 at 12:13

This May help you For MySQL

MySqlDataReader reader = mycommand.ExecuteReader();
while (reader.Read())
{
  TextBox2.Text = reader.ToString();
}

For SQL

using (SqlCommand command = new SqlCommand("*SELECT QUERY HERE*", connection))
{
//
// Invoke ExecuteReader method.
//
SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        TextBox2.Text = reader.GetString(0);
    }
}

score 0 · Answer 5 · edited Oct 19 '18 at 18:21

0

Try this:

public string SaveStringSQL(string pQuery, string ConnectionString)
{
    var connection = new Conexao(ConnectionString);
    connection.Open();

    SqlCommand command = new SqlCommand(pQuery, connection.Connection);
    var SavedString = (string)command.ExecuteScalar();

    connection.Close();

    return SavedString;
}

The ExecuteScalar function saves whatever type of data there is on your database - you just have to specify it.

Keep in mind that it can only save one line at a time.

edited Oct 19 '18 at 18:21

pushkin

9,575
15
51
95

answered Oct 19 '18 at 18:03

Felipe

15
5

@Felipe It may be noted that returned string has max limit of 2033 characters. – nam May 29 '20 at 23:02

How can I get SQL result into a STRING variable?

5 Answers5

Linked