How to Retrieve Data from a SQL Server Database in C#

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

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.

Can't retrieve data from database using c#

There are three big things wrong here.

First, a string inside an SQL statement needs to be enclosed in single quotes, like this:

SqlCommand cmd = new SqlCommand($"select * from Flashcard where English = '{englishName}'", SqlConnect);

This might seem to fix your problem all by itself, but things are still pretty bad. Think about what would happen if you have an English name that itself includes a single-quote character in it? The quote would throw the whole query out of whack. Worse, that issue can be used by hackers to do very bad things in your database.

So the second thing to do is to use query parameters:

SqlCommand cmd = new SqlCommand("select * from Flashcard where English = @EnglishName", SqlConnect);
cmd.Parameters.Add("@EnglishName", SqlDbType.NVarChar, 20).Value = englishName;

This will protect you from mis-placed or malicious single quotes, and notice as well that you no longer need to worry about enclosing the value. There a number of other benefits for doing it this way, as well. Query parameters are important. Use them.

Finally, think about what would happen if an exception is thrown by your query. Execution flow would bubble up out of your method, and the .Close() command would never happen. Do this enough, and it's possible to leave enough connections hanging open that you create a denial of service situation on the database... no one can use it! Protect against that with either a using block or try/finally blocks.

There are some other small things, too, but those are the three that are really important. Put it all together like this:

public string AskBaseForPolishName(string englishName)
{
    //it's best NOT to re-use the same connection object. Only reuse the same connection string
    using (var cn = new SqlConnection("connection string here"))
    using (var cmd = new SqlCommand("select Polish from Flashcard where English = @EnglishName;", cn))
    {
         cmd.Parameters.Add("@EnglishName", SqlDbType.NVarChar, 20).Value = englishName;
         cn.Open();
         return cmd.ExecuteScalar().ToString();
    }
}

Get data from SQL Server C#

You have to call Read() method. Try like:

..
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
   GetNumber(reader[2].ToString());
}
..

How I Retrieve Data from SQL Server Database hosted on a Web Server in my desktop application?

You have 2 options.
1. Write the business logic layer which resides on the web-server and communicates with DB to serve the required data based on requests coming from the desktop client application.
2. Allow direct access to DB over IP address and create your sql connection directly from desktop client app and access the required data.

Option #1 is recommended for scalability and security reasons .

C# - retrieve data from a DB and assign it to a variable

What you are looking for, as a step up from raw querying, is Object-relational mapping (ORM)

In order not to reinvent the wheel, I recommend this answer Best ORM to use with C# 4.0

This answers your question. As for what option suits best, that is a matter of opinion, not to be discussed here.

---

Related Topics