How can I get SQL result into a STRING variable?
Solution 1
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.
Solution 2
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.,
Solution 3
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();
}
Vantalk
Updated on October 20, 2020Comments
-
Vantalk over 3 years
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).