Query Sql Server in String

Haidon Diogenes

10y1.1k

I want to assign the result of a SQL Server query to a variable. This query may or may not have more than one line, but only one column.
Example:
QUERY> SELECT NAME FROM NAMES
OUTPUT

NAME

Michael

Michele

John

James

Variable > String names = ('Michael','Michele','John','James');

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya6y ago
Hello Haidon,

Can you please try to below code and let me know it's working for you or not.

// call the function
GetNames();
// Implement the function and return names in comma seprated string
private string GetNames()
{
   SqlCommand cmd = new SqlCommand("Select Name FROM DB_Names", con);
   SqlDataReader dr = cmd.ExecuteReader();
   var lstName = new List();

   while (dr.Read())
   {
      lstName.Add(dr["Name"].Tostring());
   }

   return string.Join(",", lstName);
}

Thanks
Mageshwaran R6y ago
Hi,
try this code:
SqlCommand cmd = new SqlCommand("Select Name FROM DB_Names", con);
   SqlDataReader dr = cmd.ExecuteReader();

   while (dr.Read())
   {
      string name=dr[0]["Name"].Tostring();
   }
Sujeet Suman10y ago
Haidon you can also store in string type list.

List list = new List();
string allname = "( "

using (SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test"))
{
   con.Open();
   SqlCommand cmd = new SqlCommand("Select Name FROM DB_Names", con);
   SqlDataReader dr = cmd.ExecuteReader();

   while (dr.Read())
   {
      list.Add(dr.GetValue(0).ToString());
   }

   while (dr.Read())
   {
     allname += dr.GetValue(0).ToString() + ", ";
   }
     allname += " )";

   con.Close();
}
Haidon Diogenes10y ago
Sujeet Suman but i not want write the result of query in DataGridView. I want write result in String.
Sujeet Suman10y ago
Haidon please once try below code:

using (SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test"))
{
   con.Open();
   SqlCommand cmd = new SqlCommand("Select Name    FROM DB_Names", con);
   SqlDataReader dr = cmd.ExecuteReader();

// Here you can write code to show selected names with any control. Like below
   dgv.DataSource = dr;
   dgv.DataBind();

   con.Close();
}

If this is helpful for you then please accept my Answer.