如何从c#中检索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:

我有一个数据库表，有3个列的firstname、Lastname和age。在我的c# Windows应用程序中，我有3个名为textbox1的文本框。我使用以下代码连接到我的SQL服务器:

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 textbox1 it 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?

我怎样才能检索到文本框的所有其他值呢?

4 个解决方案

#1

 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创建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语句，看看是否有与所提供的名称相匹配的firstname。如果是这种情况，那么该person将存储在person对象中(请参阅下面我对该类的回答)。如果没有匹配，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> .

显然我不太清楚你想做什么，所以有几件事需要注意:当有超过1个人的名字匹配，只有最后一个会被保存并返回给你。如果希望能够存储这些数据，可以将它们添加到列表 。

Person class to make it cleaner:

个人类使它更简洁:

 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;

#2

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:

然后双击检索按钮(e。g 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
          {

          }
   }

#3

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

#4

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 = oReader["Lastname"].ToString(); 
            //["LastName"] the name of your column you want to retrieve from DB
            textBoxAge.Text = oReader["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);