Would you please show me how to retrieve data from SQL Server to C# (Windows Forms application)?
Consider I have a textbox and I need to fill it with data from SQL Server WHERE 'emp_id = something' for example, how can I do it without a DataGridView?
Or take this another example:
SELECT sum(column) FROM table_name
How to get the value of the above command (also without a DataGridView)?
There are multiple ways to achieve this. You can use DataReader or DataSet \ DataTable. These are connected and disconnected architectures respectively. You can also use ExecuteScalar if you want to retrieve just one value.
Recommendations:
SqlConnection (and any other IDisposable object) in using block. My code uses try-catch block.Following is some example code with DataReader in case your query returns multiple rows. The code is copied from here.
//Declare the SqlDataReader
SqlDataReader rdr = null;
//Create connection
SqlConnection conn = new SqlConnection("Your connection string");
//Create command
SqlCommand cmd = new SqlCommand("Your sql statement", conn);
try
{
//Open the connection
conn.Open();
// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
// get the results of each column
string field1 = (string)rdr["YourField1"];
string field2 = (string)rdr["YourField2"];
}
}
finally
{
// 3. close the reader
if(rdr != null)
{
rdr.Close();
}
// close the connection
if(conn != null)
{
conn.Close();
}
}
In case your query returns single value, you can continue with above code except SqlDataReader. Use int count = cmd.ExecuteScalar();. Please note that ExecuteScalar may return null; so you should take additional precautions.
Filling a Textbox:
using (var sqlConnection = new SqlConnection("your_connectionstring"))
{
sqlConnection.Open();
using (var sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = "select sum(field) from your_table";
object result = sqlCommand.ExecuteScalar();
textBox1.Text = result == null ? "0" : result.ToString();
}
sqlConnection.Close();
}
for reading more than one row you can take a look at SqlCommand.ExecuteReader()
You need to use direct database access such as in the System.Data.SqlClient Namespace which is documented here System.Data.SqlClient Namespace.
Basically, look up creating a SQLConnection and SQLCommand and using them to retrieve the data.
