通过类从SQL服务器检索数据 [英] Retrieve data from a SQL server through class

问题描述

你好亲爱的..

我试图通过类从sql中检索数据但是它出来是空的意味着没有数据返回？



这是检索的好方法吗？

hello dears..
im trying to retrieve data from sql through class but it comes out empty mean no data return?

and is this is good way to retrieve ?

public  class person
        {
            public string name { get; set; }
            public string sex { get; set; }
            public person somemethod(int IDnum)
            {
                string stselect = "select name,sex from tbltxt  where ID=@ID";
                person ps = new person();
                using (SqlConnection cn = new SqlConnection(@"Data Source=~ \\dbtest;Integrated Security=True"))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(stselect,cn))
                    {
                        cmd.Parameters.AddWithValue("@ID", IDnum);
                        //cmd.ExecuteNonQuery();
                        using (SqlDataReader sqld = cmd.ExecuteReader())
                        {
                            while(sqld.Read())
                            {
                                ps.name = sqld.GetString(0);
                                ps.sex = sqld.GetString(1);
                            }
                          }
                      }
                    }
                return ps;
            }
           
        }

在这里我应该得到数据，例如如果我在textbox1中写1我应该在textbox2中得到john名字textbox3中的男性，但没有错误地检索任何数据...

in here i should get data for example if i write 1 in textbox1 i should get john name in textbox2 and male in textbox3 but its not retrieving any data without error...

private void button1_Click(object sender, EventArgs e)
       {
           person x = new person();
           x.somemethod(Convert.ToInt32(textBox1.Text));   //  to select which data you want to retrieve
           textBox2.Text = x.name;//here its not showing data
           textBox3.Text = x.sex;//data not retrieve
       }

我尝试了什么：



问题是它没有给出任何错误！ ！只是没有数据检索.....

What I have tried:

the problem is its not giving any error !!! just no data retrieve.....

推荐答案

嗨...只是一些编码建议



我认为你应该首先更好地组织代码，所以



连接字符串（@Data Source =〜\\ dbtest; Integrated Security = True）应该存储在配置文件中。你也可以为每种方法使用相同的连接字符串，只需从一个地方进行更改

Hi...just some coding suggestions

I think you should first organize the code better, so

The connection string (@"Data Source=~ \\dbtest;Integrated Security=True") should be stored in config file. Also you'd use the same connection string for every method and it's good to change from only one place

public class Person
{
   //think of this as a Model - if you're using MVC
   public string name { get; set; }
   public string sex { get; set; }
}

public class DatabaseQueries
{
   //name your class whatever makes sense 

   public SqlConnection SqlConnection { get; set; }

   public DatabaseQueries()
   { 
       this.SqlConnection = new SqlConnection("get the connection string...");
   }

   public Person GetPerson(int IDNum)
   {
       Person person = null;
       try
       {
           //relevant code from somemethod to get person from database
           SqlConnection.Open();
           SqlCommand cmd = SqlConnection.CreateCommand();
           cmd.CommandText = @"select name,sex from tbltxt where ID = @ID";
           cmd.CommandType = CommandType.Text;
           cmd.Parameters.AddWithValue("@ID", IDnum);
           SqlDataReader sqld = cmd.ExecuteReader();

           //use while when you expect multiple records, in this case only 1 seems expected so if can be used
           if(sqld.Read()) 
           {
              person = new Person();
              //set properties
              person.name = sqld.GetString(0);
              person.sex = sqld.GetString(1);
           }
           return person; //remember to check for null when retrieving person
       }
       catch (SqlException ex)
       {
           throw; //handle any errors
       }
       finally
       {
           SqlConnection.Close();                
       }
   }
}

看起来你打电话给 GetPerson 一个人对象但在代码中你实例化一个新人并填写该人的属性。但是，新实例化的人永远不会被使用。



所以在 GetPerson 方法中，删除 new Person 调用并设置手头对象的属性。换句话说：

It looks like you call the GetPerson of a person object but inside the code you instantiate a new person and fill the properties for that person. However, the newly instantiated person is never used.

So inside the GetPerson method, remove the new Person call and set the properties of the object at hand. In other words:

public  class person
        {
            public string name { get; set; }
            public string sex { get; set; }
            public person somemethod(int IDnum)
            {
                string stselect = "select name,sex from tbltxt  where ID=@ID";
                // person ps = new person(); // this to be removed, you're already having an instance
                using (SqlConnection cn = new SqlConnection(@"Data Source=~ \\dbtest;Integrated Security=True"))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(stselect,cn))
                    {
                        cmd.Parameters.AddWithValue("@ID", IDnum);
                        //cmd.ExecuteNonQuery();
                        using (SqlDataReader sqld = cmd.ExecuteReader())
                        {
                            while(sqld.Read())
                            {
                                this.name = sqld.GetString(0); // use this.
                                this.sex = sqld.GetString(1); // use this.
                            }
                          }
                      }
                    }
                return this; // instead of returning the new instance return this
            }

另一种选择是改变调用方，以便你实际使用返回的对象，比如这个

The other option is to change the calling side so that you actually use the returned object, like this

private void button1_Click(object sender, EventArgs e)
       {
           person x = new person();
           person returnedPerson;
           returnedPerson = x.somemethod(Convert.ToInt32(textBox1.Text));   //  to select which data you want to retrieve
           textBox2.Text = returnedPerson.name;//here its not showing data
           textBox3.Text = returnedPerson.sex;//data not retrieve
       }

但是，首先创建一个再次创建新对象的新对象会有点笨拙。要解决此问题，您可以将 GetPerson 方法定义为静态。在这种情况下， GetPerson 将是

However, that would be a bit clumsy to first create a new object which again creates a new object. To tackle that you could define the GetPerson method as static. In that case the GetPerson would be

public static Person GetPerson(int IDNum)
   {
       Person person = null;
       try
       {
           //relevant code from somemethod to get person from database
           SqlConnection.Open();
           SqlCommand cmd = SqlConnection.CreateCommand();
           cmd.CommandText = @"select name,sex from tbltxt where ID = @ID";
           cmd.CommandType = CommandType.Text;
           cmd.Parameters.AddWithValue("@ID", IDnum);
           SqlDataReader sqld = cmd.ExecuteReader();
 
           //use while when you expect multiple records, in this case only 1 seems expected so if can be used
           if(sqld.Read()) 
           {
              person = new Person();
              //set properties
              person.name = sqld.GetString(0);
              person.sex = sqld.GetString(1);
           }
           return person; //remember to check for null when retrieving person
       }
       catch (SqlException ex)
       {
           throw; //handle any errors
       }
       finally
       {
           SqlConnection.Close();                
       }
   }

现在主叫方将是

and now the calling side would be

private void button1_Click(object sender, EventArgs e)
       {
           person x;
           x = Person.GetPerson(Convert.ToInt32(textBox1.Text));   //  to select which data you want to retrieve
           textBox2.Text = x.name;//here its not showing data
           textBox3.Text = x.sex;//data not retrieve
       }

这篇关于通过类从SQL服务器检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！