How do I write a method which is supposed to get data from data base to show on a form in C#

Question

5.00/5 (1 vote)

See more:

Im a beginner to .net and could you guide me to right direction. My problem is based on the following code. Here I have 4 variations of same method and all 4 variations are working fine. But I just want to know what is the best or standard way of doing this?

Code explanation.
From a windows form Im calling 'viewAccount()' method which is in 'bankAccount' class. Its purpose is to get relivant bank account details of a employee from the data base and then those details should be shown in the text boxes of calling form.

Also please note that I have reduced no of line to make it more readable. Appreciate your any help towards the right direction.

Thank you.

Example 01 - Method will return a 'bankAccount' abject with fields populated with data from the data base

C#

 class bankAccount
{
    //Member fields...
    string acNo;
    string acName;
    string bank;
    string acType;
    frmShowAccount form=new frmShowAccount();


    public bankAccount viewAccount( string acNo )
    {
        this.acNo = acNo;

        using (SqlConnection newCon = new SqlConnection(db.GetConnectionString))
        {
            SqlCommand newCmd = new SqlCommand("SELECT Employee.Name, BankAccount.ac_name, BankAccount.bank_name, BankAccount.ac_type FROM BankAccount INNER JOIN Employee ON BankAccount.emp_id = Employee.Emp_ID WHERE (BankAccount.ac_no = @bankAccount)", newCon);

            newCmd.Parameters.Add("@bankAccount", SqlDbType.Char).Value = acNo;
            newCon.Open();
            SqlDataReader rdr = newCmd.ExecuteReader();
            rdr.Read();

            form.txtEmpName.text = rdr.GetString(0); //EmpName is not a member of bankAccount class
            this.acName = rdr.GetString(1);
            this.bank = rdr.GetString(2);
            this.acType = rdr.GetString(3);

            return this;
        }
    }
}

********
//CALLING THE ABOVE METHOD...

C#

bankAccount newBA=new bankAccount();
newBA=newBA.viewAccount(txtACNo.text);//A reference is set to the instance returned
txtACName.text=newBA.acName; //Get the value of instance field to text box

Example 02 - Method will return a data reader and it will be used by the form to get data

C#

class bankAccount
{

    string acNo;
    string acName;
    string bank;
    string acType;

    public SqlDataReader viewAccount( string acNo )
    {
        this.acNo = acNo;

        using (SqlConnection newCon = new SqlConnection(db.GetConnectionString))
        {
            SqlCommand newCmd = new SqlCommand("Same SELECT …",newCon);

            newCmd.Parameters.Add()…
            newCon.Open();
            SqlDataReader rdr = newCmd.ExecuteReader();
            rdr.Read();

            return rdr;
        }
    }
}

*************
//CALLING THE ABOVE METHOD...

C#

bankAccount newBA=new bankAccount();
SqlDataReader rdr=newBA.viewAccount(txtACNo.text) //A reference to hold the returning reader from the method call
txtACName.text=rdr.getString(1); //Get the value through the reader to text box

Example 03 - This method want return values but explicitly assign values to the text boxes in the form

C#

class bankAccount
  {

      string acNo;
      string acName;
      string bank;
      string acType;
      frmShowAccount form=new frmShowAccount();

      public void viewAccount( string acNo )
      {
          this.acNo = acNo;

          using (SqlConnection newCon = new SqlConnection(db.GetConnectionString))
          {
              SqlCommand newCmd = new SqlCommand("Same SELECT …", newCon);

              newCmd.Parameters.Add()…
              newCon.Open();
              SqlDataReader rdr = newCmd.ExecuteReader();
              rdr.Read();

              // Setting values to the text boxes in the current instance of form
              form.txtName.text=rdr[0];
              form.txtACName.text=rdr[1];
              form.txtBankName.text=rdr[2];
              form.txtACType.text=rdr[3];
          }
      }
  }

************
//CALLING THE ABOVE METHOD

C#

bankAccount newBA=new bankAccount();
newBA.form.this; // reference 'form' which is in the 'bankAccount' class is set to current instance of the form object.

Example 04 - This method want return any value. It will only initialize instance fields of the class with the data

C#

class bankAccount
{

    string acNo;
    string acName;
    string bank;
    string acType;
    frmShowAccount form=new frmShowAccount();

    public void viewAccount( string acNo )
    {
        this.acNo = acNo;

        using (SqlConnection newCon = new SqlConnection(db.GetConnectionString))
        {
            SqlCommand newCmd = new SqlCommand("Same SELECT …)", newCon);

            newCmd.Parameters.Add()…
            newCon.Open();
            SqlDataReader rdr = newCmd.ExecuteReader();
            rdr.Read();

            form.txtName.text=rdr[0];
            this.acName=rdr[1];
            this.bank=rdr[2];
            this.acType=rdr[3];
    }
}

***************
CALLING THE ABOVE METHOD

C#

bankAccount newBA=new bankAccount();
txtACName.text=newBA.acName; // Text boxes get the data from account object's instance fields (probably through a get property)

Posted 10-Apr-14 2:51am

Chathur

Add a Solution

2 solutions

Solution 2

For your specific case the first method is okay as it is separating the data access logic from presentation logic.

Rest of the approaches are bad practices. Do remember that the core principle behind introducing layers (Data Access, Business and Presentation) is that each layer is meant to do specific task. For example data access layer is meant to do only data access activities, nothing more. Business Layer is responsible for coding business logic and do not care about the mechanism behind data fetching, similarly Presentation layer is only meant to view the data processed by business layer. So there is clear separation of responsibilities as you can see.

Posted 10-Apr-14 4:29am

_Asif_

Comments

Chathur 11-Apr-14 23:46pm

Thank you very much for your advice. Im under impression that I should use a separate class for db logic too. In my case I have several entity classes like Employee, Client etc. So should I use
a single class for db logic or a separate one for each entity class. Appreciate if you could you give some code. Chathur

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)

Florian Trück · Accepted Answer · 2014-04-10T03:44:00

I've corrected my solution for separating db code from entity.

I suggest to keep database logic away from the rest of the code:

C#

public class BankAccount
{
    public string acNo { get; set; }
    public string acName { get; set; }
    public string bank { get; set; }
    public string acType { get; set; }
}

public class BankaccountFactory 
{
    private BankaccountFactory() { } // Default constructor private, so use factory method

    public static BankAccount CreateFromDb(string acNo)
    {
        BankAccount account = new BankAccount { acNo = acNo };

        using (SqlConnection newCon = new SqlConnection(db.GetConnectionString))
        {
            SqlCommand newCmd = new SqlCommand("Same SELECT …)", newCon);

            newCmd.Parameters.Add();
            newCon.Open();
            SqlDataReader rdr = newCmd.ExecuteReader();
            rdr.Read();

            account.acName = rdr[1];
            account.bank = rdr[2];
            account.acType = rdr[3];
        }
        return account;
    }
}

Then use it like this in your form:

C#

BankAccount acc = BankAccountFactory.CreateFromDB("1021577552254");

txtACName.text=account.acName;
txtBankName.text=account.bank;
txtACType.text=acType;

According to your question: Yes, it is good practise to do this for all entities in your project. If you are using your entities in more projects it is good advice to place them in a separate class library to reuse them.