Setting sql Parameters

[zachattack05]

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...

[kvprajapati]

Have a look at these threads :

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.sommarskog.se/arrays-in-sql-2005.html#CSV

[sknake]

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);
    }
  }
}

[zachattack05]

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?

[zachattack05]

Have a look at these threads :

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.sommarskog.se/arrays-in-sql-2005.html#CSV

adatapost,

I think these links are refering to something different? I could be wrong, but if they are talking about the same thing I am thinking of, then I am WAY lost.

[kvprajapati]

>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.

[zachattack05]

>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?

[sknake]

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.

[zachattack05]

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?