Retrieve data from a SQL server through class

Question

0.00/5 (No votes)

See more:

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 ?

C#

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

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

C#

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

Posted 29-Oct-16 8:20am

jame01

Updated 29-Oct-16 21:37pm

v2

Add a Solution

Comments

[no name] 29-Oct-16 14:30pm

"whats wrong", how would we know? We don't have access to your database. Learn how to debug your code.

jame01 29-Oct-16 14:34pm

thanks for your care, as i mentioned i can't retrieve data , as it showing in button click event i should get data back to textbox2 and textbox3 but not getting it ....
thanks dear

[no name] 29-Oct-16 14:47pm

As I said, learn how to debug your code. We don't have access to your database so we would have no idea why you can't.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 29-Oct-16 14:54pm

You should debug and find out where exactly is the problem.

jame01 29-Oct-16 15:00pm

sir im debugging it but no error shows up , the problem is no data comeback ...
thanks

Afzaal Ahmad Zeeshan 29-Oct-16 15:55pm

Is there any data in your database? And the table that you are using?

jame01 29-Oct-16 15:59pm

yes there is data and i can get data in other way but i wanna use this class it will be useful a lot ..
tahnks

2 solutions

Solution 1

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

C#

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

Posted 29-Oct-16 10:15am

P_Z

Updated 1-Nov-16 8:41am

v3

Comments

jame01 31-Oct-16 17:12pm

thanks for your help...
i know this is good way to use it, but can you tell me what is wrong with this
private void button1_Click(object sender, EventArgs e)
{
DatabaseQueries dbs = new DatabaseQueries();//im calling class
dbs.GetPerson(Convert.ToInt32(textBox1.Text));//gaiving value
Person ps = new Person();
// Person settheperosn;
textBox2.Text =ps.name ;
textBox3.Text = ps.sex;

}

P_Z 1-Nov-16 14:35pm

>> dbs.GetPerson(Convert.ToInt32(textBox1.Text));//gaiving value
>> Person ps = new Person(); //this person does Not have database details...just created a new object

Some minor updates to the button click
private void button1_Click(object sender, EventArgs e)
{
DatabaseQueries dbs = new DatabaseQueries();//im calling class
int personID = -1;
int.TryParse(textBox1.Text, out personID); //text may contain non digit chars
Person ps = dbs.GetPerson(personID); //Now person will be populated with database details
if (ps != null)
{
//null check due to person with personID may not exist
textBox2.Text = ps.name;
textBox3.Text = ps.sex;
}
}

Richard Deeming 1-Nov-16 15:04pm

If you're just going to rethrow the exception, there's no point catching it. You might as well remove the catch (SqlException ex) { throw; } block. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2016-10-29T21:37:00

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:

C#

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

C#

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
       }

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

C#

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

C#

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
       }