I have a form that has multiple text boxes, check boxes etc...
The question is, instead of writing out:

cmd.Parameters.Add(new SqlParameter("@myparam", (object)textbox.Text));

a million times for each parameter, is it possible to load all of the names of the objects on a form into an array and just do a loop through those names to set the parameters?

I tried using something similar to:

cmd.Parameters.Add(new SqlParameter("@" + array.GetValue(cnt).ToString(), (object)array.GetValue(cnt).Text));

but for obvious reasons...it fails. Any suggestion? Instead of an array that I populate maybe a system array works that would get the object names off of the form? I dunno...

You could use a dictionary to index the controls.

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmAutoPopulate : Form
  {
    Dictionary<Control, string> bindings;

    public frmAutoPopulate()
    {
      InitializeComponent();
    }

    private void frmAutoPopulate_Load(object sender, EventArgs e)
    {
      bindings = new Dictionary<Control, string>();
      bindings.Add(textBox1, "Name");
      bindings.Add(textBox2, "Adddress");
      bindings.Add(textBox3, "Zip");
      bindings.Add(checkBox1, "Deceased");
    }

    private void button1_Click(object sender, EventArgs e)
    {
      System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
      Control[] ctrls = (Control[])FindControls(this, typeof(Control));
      for (int i1 = 0; i1 < ctrls.Length; i1++)
      {
        Control ctrl = ctrls[i1];
        string name;

        if (!bindings.TryGetValue(ctrl, out name))
          continue; //not in our bindings list
        if ((ctrls[i1] as TextBox) != null)
        {
          cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + name, ((TextBox)ctrl).Text));
        }
        else if ((ctrls[i1] as CheckBox) != null)
        {
          cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + name, ((CheckBox)ctrl).Checked));
        }
      }
    }


    private static Array FindControls(Control ctrl, Type t)
    {
      System.Collections.ArrayList result = new System.Collections.ArrayList();
      foreach (Control c in ctrl.Controls)
      {
        if ((c.GetType() == t) || c.GetType().IsSubclassOf(t))
          result.Add(c);
        foreach (Control childCtrl in c.Controls)
          result.AddRange(FindControls(childCtrl, t));
      }
      return result.ToArray(t);
    }
  }
}
Comments
Welcome back.

You could use a dictionary to index the controls.

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmAutoPopulate : Form
  {
    Dictionary<Control, string> bindings;

    public frmAutoPopulate()
    {
      InitializeComponent();
    }

    private void frmAutoPopulate_Load(object sender, EventArgs e)
    {
      bindings = new Dictionary<Control, string>();
      bindings.Add(textBox1, "Name");
      bindings.Add(textBox2, "Adddress");
      bindings.Add(textBox3, "Zip");
      bindings.Add(checkBox1, "Deceased");
    }

    private void button1_Click(object sender, EventArgs e)
    {
      System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
      Control[] ctrls = (Control[])FindControls(this, typeof(Control));
      for (int i1 = 0; i1 < ctrls.Length; i1++)
      {
        Control ctrl = ctrls[i1];
        string name;

        if (!bindings.TryGetValue(ctrl, out name))
          continue; //not in our bindings list
        if ((ctrls[i1] as TextBox) != null)
        {
          cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + name, ((TextBox)ctrl).Text));
        }
        else if ((ctrls[i1] as CheckBox) != null)
        {
          cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + name, ((CheckBox)ctrl).Checked));
        }
      }
    }


    private static Array FindControls(Control ctrl, Type t)
    {
      System.Collections.ArrayList result = new System.Collections.ArrayList();
      foreach (Control c in ctrl.Controls)
      {
        if ((c.GetType() == t) || c.GetType().IsSubclassOf(t))
          result.Add(c);
        foreach (Control childCtrl in c.Controls)
          result.AddRange(FindControls(childCtrl, t));
      }
      return result.ToArray(t);
    }
  }
}

Sknake,

Thanks for your reply! But...you lost me.

Here is a picture of my form and the code behind it...Maybe this might help? Could you possibly expand on your example?

private void Save(object sender, EventArgs e)
        {
            #region Error Handlers

            Messages errors = new Messages();

            #endregion

            #region Connect to Database

            //get default server type
            string ServerType = Properties.Settings.Default.SQLType.ToString();

            //get our connection string
            ConnectionManager ConnectionString = new ConnectionManager();
            string SQLConnectionString = ConnectionString.ConnectionString(ServerType);
            SqlConnection Connection = new SqlConnection();
            Connection.ConnectionString = SQLConnectionString;

            //try opening the connection
            try
            {
                Connection.Open();
                Connection.Close();
            }
            catch
            {
                //connection failed
                //give error message
                errors.ErrorMessages(004);
            }

            #endregion

            #region Save the record

            //retrieve the current data structure
            DataIntegrity di = new DataIntegrity();

            Array structure = di.DataStructure("accounts");

            //build the update string
            string updatestring = "UPDATE accounts SET ";

            //find out how many things need to be updated
            int i = structure.Length;

            //pick our starting point
            int cnt = 1;

            //our string for holding the current column value
            string next = null;

            //add the string variables using the structure array
            while (cnt < i)
            {
                next = structure.GetValue(cnt).ToString();
                if (cnt != i - 1)
                {
                    updatestring += next + "=@" + next + ", ";
                }
                if (cnt == i - 1)
                {
                    updatestring += next + "=@" + next;
                }
                cnt++;
            }

            //create our command
            SqlCommand cmd = new SqlCommand(updatestring, Connection);

            //set our parameters


            #endregion

        }

        #endregion

Line 73 is where I'm lost?

Edited 6 Years Ago by zachattack05: n/a

Attachments Capture.JPG 54.95 KB

>I am thinking of, then I am WAY lost.

I would like to say that keep your program as simple as you can do. It's very difficult to develop simple programs.

>I am thinking of, then I am WAY lost.

I would like to say that keep your program as simple as you can do. It's very difficult to develop simple programs.

I understand that...I'm just wondering if there is a way to make something like this:

SqlCommand cmd = new SqlCommand(updatestring, Connection);

            //set our parameters
            cmd.Parameters.Add(new SqlParameter("@companycode", (object)companycode.Text));
            cmd.Parameters.Add(new SqlParameter("@title", (object)title.Text));
            cmd.Parameters.Add(new SqlParameter("@firstname", (object)firstname.Text));
            cmd.Parameters.Add(new SqlParameter("@lastname", (object)lastname.Text));
            cmd.Parameters.Add(new SqlParameter("@company", (object)company.Text));
            cmd.Parameters.Add(new SqlParameter("@physicaladdress", (object)physicaladdress.Text));
            cmd.Parameters.Add(new SqlParameter("@physicalcity", (object)physicalcity.Text));
            cmd.Parameters.Add(new SqlParameter("@physicalstate", (object)physicalstate.Text));
            cmd.Parameters.Add(new SqlParameter("@physicalzip", (object)physicalzip.Text));
            cmd.Parameters.Add(new SqlParameter("@mailingaddress", (object)mailingaddress.Text));
            cmd.Parameters.Add(new SqlParameter("@mailingcity", (object)mailingcity.Text));
            cmd.Parameters.Add(new SqlParameter("@mailingstate", (object)mailingstate.Text));
            cmd.Parameters.Add(new SqlParameter("@mailingzip", (object)mailingzip.Text));
            cmd.Parameters.Add(new SqlParameter("@billingcontact", (object)billingcontact.Text));
            cmd.Parameters.Add(new SqlParameter("@billingaddress", (object)billingaddress.Text));
            cmd.Parameters.Add(new SqlParameter("@billingcity", (object)billingcity.Text));
            cmd.Parameters.Add(new SqlParameter("@billingstate", (object)billingstate.Text));
            cmd.Parameters.Add(new SqlParameter("@billingzip", (object)billingzip.Text));
            cmd.Parameters.Add(new SqlParameter("@phone", (object)phone.Text));
            cmd.Parameters.Add(new SqlParameter("@fax", (object)fax.Text));
            cmd.Parameters.Add(new SqlParameter("@faxsecure", (object)faxsecure.Checked));
            cmd.Parameters.Add(new SqlParameter("@email", (object)email.Text));
            cmd.Parameters.Add(new SqlParameter("@mro", (object)mro.Text));
            cmd.Parameters.Add(new SqlParameter("@reportingmethod", (object)reportingmethod.Text));
            cmd.Parameters.Add(new SqlParameter("@primarylab", (object)primarylab.Text));
            cmd.Parameters.Add(new SqlParameter("@primarylabaccountnumber", (object)primarylabaccountnumber.Text));
            cmd.Parameters.Add(new SqlParameter("@secondarylab", (object)secondarylab.Text));
            cmd.Parameters.Add(new SqlParameter("@secondarylabaccountnumber", (object)secondarylabaccountnumber.Text));
            cmd.Parameters.Add(new SqlParameter("@filedate", (object)filedate.Text));
            cmd.Parameters.Add(new SqlParameter("@safetydirector", (object)safetydirector.Text));
            cmd.Parameters.Add(new SqlParameter("@alternatecontacts", (object)alternatecontacts.Text));
            cmd.Parameters.Add(new SqlParameter("@agency", (object)agency.Text));
            cmd.Parameters.Add(new SqlParameter("@clinic", (object)clinic.Text));
            cmd.Parameters.Add(new SqlParameter("@filename", (object)filename.Text));
            cmd.Parameters.Add(new SqlParameter("@lastupdated", DateTime.Now));
            cmd.Parameters.Add(new SqlParameter("@randompool", (object)randompool.Text));
            cmd.Parameters.Add(new SqlParameter("@drugrate", (object)drugrate.Text));
            cmd.Parameters.Add(new SqlParameter("@drugdrawtype", (object)drugdrawtype.Text));
            cmd.Parameters.Add(new SqlParameter("@alcoholrate", (object)alcoholrate.Text));
            cmd.Parameters.Add(new SqlParameter("@alcoholdrawtype", (object)alcoholdrawtype.Text));
            cmd.Parameters.Add(new SqlParameter("@notes", (object)notes.Text));
            cmd.Parameters.Add(new SqlParameter("@active", (object)active.Text));
            cmd.Parameters.Add(new SqlParameter("@rlock", (object)rlock.Checked));

            //run the update query
            Connection.Open();
            MessageBox.Show(cmd.ExecuteNonQuery().ToString() + " rows affected");

Into something a little more manageable and easier to maintain?

The articles you posted seemed to not do what I was wanting to do. And the post by sknake was confusing to me because I have never used a dictionary for anything before. I was hoping he could expand on it based on my code example.

I know writing any program is difficult and takes time. But it just seemed like writing out cmd.Parameters.Add etc.... adnauseum for each textbox and checkbox on the form would create a management nightmare. All I would have to do is inadvertently change a control name and the whole code is wrong. If I had some sort of way of telling the program "hey, do this: cmd.Parameters.Add blah blah blah for each of the textboxes and checkboxes on this form."

I opted, for temporary sake, to write out all of the parameters until I can understand a better way to do it.

If I were to keep the program only as simple as my knowledge allows then I wouldn't have gotten as far as I have. It's a learning experience and the articles and examples were just a little over my head...I was hoping someone could explain them as it relates to my situation?

You can use typed datasets generated by the IDE or use an entity framework, ie: nhibernate, developer express' XPress Persistent Objects (XPO), or .. a handful of other libs. Take a look around at entity frameworks. There is a lot to learn.

You can use typed datasets generated by the IDE or use an entity framework, ie: nhibernate, developer express' XPress Persistent Objects (XPO), or .. a handful of other libs. Take a look around at entity frameworks. There is a lot to learn.

Thanks. I'll take a look and see what I can find. Any preferences?

This article has been dead for over six months. Start a new discussion instead.