如何在C＃中从SQL Server数据库检索数据？ [英] How to retrieve data from a SQL Server database in C#?

问题描述

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

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

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

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

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

我尝试了此方法，但是它不起作用：

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?

推荐答案

 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;
        }

这里要注意的几件事：我使用了参数化查询，这使您的代码成为可能更安全。用制作select语句的方式，其中x = + Textbox.Text + 部分使您可以进行SQL注入。

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.

我将其更改为：

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

所以这段代码要做的是：

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

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

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.

很明显，我不完全知道您要做什么，因此需要注意以下几件事：如果有1个以上具有相同名称的人，只有最后一个会被保存并返回给您。
如果您希望能够存储此数据，可以将它们添加到 List< Person> 。

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> .

使人变得更干净的人类：

Person class to make it cleaner:

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

现在调用该方法：

Person x = SomeMethod("John");

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

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

txtLastName.Text = x.LastName;

这篇关于如何在C＃中从SQL Server数据库检索数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！