CODEWITHSUNDEEP
c#stringclassmethods
Pieter Du Toit
Pieter Du Toit

Reputation: 429

Place sql method in seperate class and return multiple strings

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.

Upvotes: 0

Views: 186

Answers (2)

called2voyage
called2voyage

Reputation: 252

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.

Upvotes: 1

Ronnie
Ronnie

Reputation: 1079

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;

Upvotes: 3

Related Questions