How to Retrieve Data from a SQL Server Database in C# Using Parameterized Queries?

Retrieving Data from a SQL Server Database in C#

To retrieve data from a SQL Server database in C#, you can use the SqlConnection, SqlCommand, and SqlDataReader objects. Here's how you can achieve this:

1. Establish a Connection to the Database:

   SqlConnection con = new SqlConnection("Data Source=.
   Initial Catalog=domain;
   Integrated Security=True");
   con.Open();

2. Create a SqlCommand Object:

   SqlCommand cmd = new SqlCommand("Select * from tablename", con);

3. Execute the SqlCommand:

   using (SqlDataReader reader = cmd.ExecuteReader())
   {
     // Iterate over the results and retrieve values
     while (reader.Read())
     {
    // Get values from the current row
     }
   }

However, the code you provided in your question is not working because you are not parametrizing your SQL query. This makes your code vulnerable to SQL injection attacks. To fix this, use parametrized queries:

cmd.CommandText = "select * from tablename where firstname = @firstName";
cmd.Parameters.AddWithValue("@firstName", textBox1.Text);

Here's an example of a more complete method that retrieves data from a database and populates user-defined objects:

public Person GetPerson(string firstName)
{
  var con = ConfigurationManager.ConnectionStrings["Yourconnection"].ToString();

  using (SqlConnection myConnection = new SqlConnection(con))
  {
    string oString = "Select * from Employees where FirstName=@fName";
    SqlCommand oCmd = new SqlCommand(oString, myConnection);
    oCmd.Parameters.AddWithValue("@Fname", fName);
    myConnection.Open();

    using (SqlDataReader oReader = oCmd.ExecuteReader())
    {
      while (oReader.Read())
      {
        Person matchingPerson = new Person
        {
          firstName = oReader["FirstName"].ToString(),
          lastName = oReader["LastName"].ToString(),
        };

        return matchingPerson;
      }
    }
  }

  return null; // If no person found
}

To use this method, you can call it with a firstName parameter and populate your textboxes with the properties of the returned Person object.

The above is the detailed content of How to Retrieve Data from a SQL Server Database in C# Using Parameterized Queries?. For more information, please follow other related articles on the PHP Chinese website!