CODEWITHSUNDEEP
c#sql-serverado.net
newbiedevv
newbiedevv

Reputation: 13

How to retrieve data from SQL Server in C# using ADO.NET?

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

Upvotes: 0

Views: 11593

Answers (3)

Amit Joshi
Amit Joshi

Reputation: 16386

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:

  • Enclose SqlConnection (and any other IDisposable object) in using block. My code uses try-catch block.
  • Always use parameterized queries.

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.

Upvotes: 2

Olli
Olli

Reputation: 676

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

Upvotes: 1

spodger
spodger

Reputation: 1679

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.

Upvotes: 0

Related Questions