C#: help return dataset and assign values to controls

Question

I'm not really used to C# sharp but have used VB.NET before.

I'm needing to set the value of text fields, dropdowns etc from data from a query. To enter the data I have been using a class Computer with the method saveComputer() that takes values from user controls. Now I want an edit page that uses the id from url & uses getComputer(id) from Computer class and returns the values to be set to the user controls. I'm unsure about using this method to set the control values.

Edit.aspx.cs

 protected void btnSave_Click(object sender, EventArgs e)
    {
        int id = 3; //will be replaced to GET value
        Computer comp = new Computer();
        //comp.updateComputer(ref id);

    }

My Computer class

public getComputer(ref int id)
    {
        DataSet data = new DataSet();
        using (SqlConnection conn = new SqlConnection(
               "Server=JURA;Database=ReadyForSeven;User id=;Password="))
        {
            String sql = "SELECT * FROM computers WHERE id=@id";

            //replace contatenation of variable with parameter name


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql.ToString();
            cmd.CommandType = CommandType.Text;

            //Define SqlParameter object and assign value to the parameter

            cmd.Parameters.Add("@id", SqlDbType.Int);
            cmd.Parameters["@id"].Value = id;

            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(data);
                    // return data here
                }
            }
            catch (SqlException ex)
            {
                //send user to error page and log message

            }
        }
    }

So what I'm wanting to achieve is using the getcomputer method of Computer to set the values of the controls on Edit.aspx

Can anyone help me out?

Answer 1

You'll need to modify your getComputer method to return a DataSet like:

public DataSet getComputer(int id) {

Once that's done we can call it and populate the form controls on the page load with something like:

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) {
        int id = 3; // get from querystring
        DataSet ds = getComputer(id);
        DataRow dr = ds.Tables[0].Rows[0]; // get the first row returned

        // populate form controls
        txtFirstName.Text = dr["FirstName"].ToString();
        ddlState.SelectedValue = dr["State"].ToString();
    }
}

Below is an updated version of getComputer that will always return a value and is a little tighter:

public DataSet getComputer(int id) // you don't need to pass int by ref unless you're planning on changing it inside this method
{
    DataSet data = new DataSet();
    using (SqlConnection conn = new SqlConnection("Server=JURA;Database=ReadyForSeven;User id=;Password=")) {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM computers WHERE id = @id", conn)) {
            cmd.Parameters.AddWithValue("id", id);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
                da.Fill(data);
                return data;
            }
        }
    }
}

I had to remove the try/catch blog to ensure the method always returned a value. If you absolutely need the try/catch block you'll need to return an empty DataSet at the end of the method to get to compile correctly.