如何从 C# 中的 SQL Server 数据库中检索数据？

How to retrieve data from a SQL Server database in C#?

提问by Vivek Dragon

I have a database table with 3 columns firstname, Lastnameand age. In my C# Windows application I have 3 textboxes called textbox1... I made my connectivity to my SQL Server using this code:

我有一个包含 3 列的数据库表firstname，Lastname并且age. 在我的 C# Windows 应用程序中，我有 3 个名为的文本框textbox1......我使用以下代码连接到我的 SQL Server：

SqlConnection con = new SqlConnection("Data Source = .;
                                       Initial Catalog = domain;
                                       Integrated Security = True");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from tablename", con);

I'd like to get values from my database; if I give a value in textbox1it has to match the values in the database and retrieve other details to the corresponding textboxes.

我想从我的数据库中获取值；如果我给textbox1它一个值必须匹配数据库中的值并将其他详细信息检索到相应的文本框。

I tried this method but it's not working:

我试过这个方法，但它不起作用：

cmd.CommandText = "select * from tablename where firstname = '" + textBox1.Text + "' ";

How can I do it to retrieve all the other values to the textboxes?

如何将所有其他值检索到文本框？

回答by Thousand

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

            Person matchingPerson = new Person();
            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())
                    {    
                        matchingPerson.firstName = oReader["FirstName"].ToString();
                        matchingPerson.lastName = oReader["LastName"].ToString();                       
                    }

                    myConnection.Close();
                }               
            }
            return matchingPerson;
        }

Few things to note here: I used a parametrized query, which makes your code safer. The way you are making the select statement with the "where x = "+ Textbox.Text +""part opens you up to SQL injection.

这里需要注意的几件事：我使用了参数化查询，这使您的代码更安全。您使用该"where x = "+ Textbox.Text +""部件制作 select 语句的方式使您容易受到 SQL 注入的影响。

I've changed this to:

我已将其更改为：

  "Select * from Employees where FirstName=@fName"
  oCmd.Parameters.AddWithValue("@fname", fName);  

So what this block of code is going to do is:

所以这段代码要做的是：

Execute an SQL statement against your database, to see if any there are any firstnames matching the one you provided. If that is the case, that person will be stored in a Person object (see below in my answer for the class). If there is no match, the properties of the Person object will be null.

对您的数据库执行 SQL 语句，以查看是否有任何名字与您提供的名字匹配。如果是这种情况，则该人将存储在 Person 对象中（请参阅下面我对该类的回答）。如果没有匹配项，则 Person 对象的属性将为null。

Obviously I don't exactly know what you are trying to do, so there's a few things to pay attention to: When there are more then 1 persons with a matching name, only the last one will be saved and returned to you. If you want to be able to store this data, you can add them to a List<Person>.

显然我不完全知道你要做什么，所以有一些事情需要注意： 当有超过 1 个人的名字匹配时，只有最后一个会被保存并返回给你。如果您希望能够存储这些数据，您可以将它们添加到List<Person>.

Person class to make it cleaner:

Person 类使其更干净：

 public class Person
    {
            public string firstName { get; set; }
            public string lastName { get; set; }
    }

Now to call the method:

现在调用该方法：

Person x = SomeMethod("John");

You can then fill your textboxes with values coming from the Person object like so:

然后，您可以使用来自 Person 对象的值填充文本框，如下所示：

txtLastName.Text = x.LastName;

回答by ABDULKAREEM SHO

create a class called DbManager:

创建一个名为 DbManager 的类：

Class DbManager
{
 SqlConnection connection;
 SqlCommand command;

       public DbManager()
      {
        connection = new SqlConnection();
        connection.ConnectionString = @"Data Source=.     \SQLEXPRESS;AttachDbFilename=|DataDirectory|DatabaseName.mdf;Integrated Security=True;User Instance=True";
        command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.Text;
     } // constructor

 public bool GetUsersData(ref string lastname, ref string firstname, ref string age)
     {
        bool returnvalue = false;
        try
        {
            command.CommandText = "select * from TableName where firstname=@firstname and lastname=@lastname";
            command.Parameters.Add("firstname",SqlDbType.VarChar).Value = firstname;
 command.Parameters.Add("lastname",SqlDbType.VarChar).Value = lastname; 
            connection.Open();
            SqlDataReader reader= command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    lastname = reader.GetString(1);
                    firstname = reader.GetString(2);

                    age = reader.GetString(3);


                }
            }
            returnvalue = true;
        }
        catch
        { }
        finally
        {
            connection.Close();
        }
        return returnvalue;

    }

then double click the retrieve button(e.g btnretrieve) on your form and insert the following code:

然后双击表单上的检索按钮（例如 btnretrieve）并插入以下代码：

 private void btnretrieve_Click(object sender, EventArgs e)
    {
        try
        {
            string lastname = null;
            string firstname = null;
            string age = null;

            DbManager db = new DbManager();

            bool status = db.GetUsersData(ref surname, ref firstname, ref age);
                if (status)
                {
                txtlastname.Text = surname;
                txtfirstname.Text = firstname;
                txtAge.Text = age;       
               }
          }
       catch
          {

          }
   }

回答by Ashraf Abusada

To retrieve data from database:

从数据库中检索数据：

private SqlConnection Conn;
 private void CreateConnection()
 {
    string ConnStr =
    ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
    Conn = new SqlConnection(ConnStr);
 }
 public DataTable getData()
 {
 CreateConnection();
    string SqlString = "SELECT * FROM TableName WHERE SomeID = @SomeID;";
    SqlDataAdapter sda = new SqlDataAdapter(SqlString, Conn);
    DataTable dt = new DataTable();
    try
    {
        Conn.Open();
        sda.Fill(dt);
    }
    catch (SqlException se)
    {
        DBErLog.DbServLog(se, se.ToString());
    }
    finally
    {
        Conn.Close();
    }
    return dt;
}

回答by Mahmoud Ayman

You can use this simple method after setting up your connection:

您可以在设置连接后使用这个简单的方法：

private void getAgentInfo(string key)//"key" is your search paramter inside database
    {
        con.Open();
        string sqlquery = "SELECT * FROM TableName WHERE firstname = @fName";

        SqlCommand command = new SqlCommand(sqlquery, con); 
        SqlDataReader sReader;

        command.Parameters.Clear();
        command.Parameters.AddWithValue("@fName", key);
        sReader = command.ExecuteReader();

        while (sReader.Read())
        {
            textBoxLastName.Text = sReader["Lastname"].ToString(); //SqlDataReader
            //["LastName"] the name of your column you want to retrieve from DB

            textBoxAge.Text = sReader["age"].ToString();
            //["age"] another column you want to retrieve
        }
        con.Close();
    }

Now you can pass the key to this method by your textBoxFirstName like:

现在，您可以通过 textBoxFirstName 将密钥传递给此方法，例如：

getAgentInfo(textBoxFirstName.Text);

回答by shrawan

we can use this type of snippet also we generally use this kind of code for testing and validating data for DB to API fields

我们可以使用这种类型的代码片段，我们通常也使用这种代码来测试和验证 DB 到 API 字段的数据

class Db
{
    private readonly static string ConnectionString =
            ConfigurationManager.ConnectionStrings
                        ["DbConnectionString"].ConnectionString;
    public static List<string> GetValuesFromDB(string LocationCode)
    {
        List<string> ValuesFromDB = new List<string>();
        string LocationqueryString = "select BELocationCode,CityLocation,CityLocationDescription,CountryCode,CountryDescription " +
            $"from [CustomerLocations] where LocationCode='{LocationCode}';";
        using (SqlConnection Locationconnection =
                                 new SqlConnection(ConnectionString))
        {
            SqlCommand command = new SqlCommand(LocationqueryString, Locationconnection);
            try
            {
                Locationconnection.Open();
                SqlDataReader Locationreader = command.ExecuteReader();
                while (Locationreader.Read())
                {
                    for (int i = 0; i <= Locationreader.FieldCount - 1; i++)
                    {
                        ValuesFromDB.Add(Locationreader[i].ToString());
                    }
                }
                Locationreader.Close();
                return ValuesFromDB;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
        }

    }

}

hope this might helpful

希望这可能会有所帮助

Note: you guys need connection string (in our case "DbConnectionString")

注意：你们需要连接字符串（在我们的例子中是“DbConnectionString”）

回答by Daniel Adenew

    DataTable formerSlidesData = new DataTable();
    DformerSlidesData = searchAndFilterService.SearchSlideById(ids[i]);
                if (formerSlidesData.Rows.Count > 0)
                {
                    DataRow rowa = formerSlidesData.Rows[0];

                    cabinet = Convert.ToInt32(rowa["cabinet"]);
                    box = Convert.ToInt32(rowa["box"]);
                    drawer = Convert.ToInt32(rowa["drawer"]);
                }