Place sql method in seperate class and return multiple strings

Question

I have code that executes on a button click. It connects to a sql database and reads two values. All I want to achieve is to place this code in a separate class called 'DataManager' and then from my button click call a method in this class and get the two strings into my textboxes.

string sReference = txtReference.Text;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_SELECT_CONSHEAD_BY_ENQUIRY_NUMBER";
cmd.Parameters.AddWithValue("@EnquiryNumber", sReference);                        
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())
{
    txtAccount.Text = sdr["Consignee"].ToString();
    txtAccount_Printed.Text = sdr["Consignee_Printed"].ToString();
}

con.Close();
con.Dispose();

I was thinking my method should look something like this

// Select from ConsHead by Reference Number
    public string SelectConsHead(string sReference, out string sAccount, out string sAccount_Printed)
    {
        sAccount_Printed = "";
        sAccount = "";
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ToString());
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_SELECT_CONSHEAD_BY_ENQUIRY_NUMBER";
        cmd.Parameters.AddWithValue("@EnquiryNumber", sReference);
        // Attach connection to command
        cmd.Connection = con;
        con.Open();
        SqlDataReader sdr = cmd.ExecuteReader();

        while (sdr.Read())
        {
            sAccount = sdr["Consignee"].ToString();
            sAccount_Printed = sdr["Consignee_Printed"].ToString();
        }
        con.Close();
        con.Dispose();


        return sAccount + sAccount_Printed;

    }

but I'm not sure on how to call the method and retrieve the corresponding values into the textboxes.

score 3 · Accepted Answer · edited May 23 '17 at 11:56

You ought to avoid the use of out parameters in your method definition. They are not being used as you are returning a concatenation of the account and account_printed strings anyway, so your method signature should look like this:

 public string SelectConsHead(string sReference)

...and therefore you need to declare your local string variables, e.g:

var sAccount_Printed = "";
var sAccount = "";

You should also consider why you are prefixing string variable names with 's', as this is considered by many to be bad practice. For Microsoft's coding guidelines, see this link:

http://msdn.microsoft.com/en-us/library/vstudio/ff926074.aspx

If your class is called DataManager, you need to instantiate the class in order to call your method (as it is not static). For example:

var sReference = // some code here to get sReference.
var dm = new DataManager();
var consHead = dm.SelectConsHead(sReference);

Another good practice is to favour the 'using' statement over manually opening and closing a SqlConnection. See here:

The C# using statement, SQL, and SqlConnection

In order to print the result in a textbox, assuming you are building a windows forms application, you need to know the id of the textbox, and then you can populate it like this (here the id of the textbox is 'myTextBox'):

myTextBox.Text = consHead;

Nice answer. Only thing I would suggest is the use of a DTO as a return value to cater for returning multiple values at the same time. — Terence, Jun 27 '13 at 12:57
Just one question on the very last point you made about printing the result to a text box. I get two values in consHead and you say I should populate the TextBox like 'myTextBox.Text = consHead;', but that dosn't tell the textbox with which one of the two values it should be populated with. — Pieter Du Toit, Jun 27 '13 at 13:13
Your line "return sAccount + sAccount_Printed;" will return a single value (concatenation of the two strings). If you need to return both values separately, then create another class as Terence suggests. This class may be called 'AccountDto' or something similar, and should contain two properties, one for each value. Then rather than returning a string, you need to create an instance of this Dto inside the SelectConsHead method, set its properties, and return it rather than a string (note this will require you to update your method definition). Then access the values via the dto's properties. — Ronnie, Jun 27 '13 at 13:20

score 1 · Answer 2 · answered Jun 27 '13 at 13:00

The answer will vary based on whether you're working with a ASP.NET web app or a Windows forms application.

I can answer based on ASP.NET.

First, either your SelectConsHead method in your DataManager class will need to be static or you will need to create a new instance of your DataManager class in the code-behind of the page with your button and text boxes. If you go the static route, your method heading will look like this:

public static string SelectConsHead

Then in the code-behind of the page with your button and text boxes you will call the SelectConsHead method in the click event handler of your button.

If you go the non-static route, here is how you would instatiate your class and call its method in the codebehind:

DataManager dm = new DataManager();
dm.SelectConsHead(your parameters here);

Also, you should take Ronnie's advice in revising your method.

Place sql method in seperate class and return multiple strings

2 Answers2