none
Trying to use Paramterized SQL in C# with joined tables RRS feed

  • Question

  • Let's say I have two tables, Customer and Customer Type, joined on the customer_type_id.  I have a DataGridView that shows the combined information.  There is a DataGridViewComboBoxColumn that displays a list of the customer types from the proper table.  This works well.

    However, in the InsertCommand.Parameters.Add("?customertype"...., I'm not sure how to reference the source column for the customer type.  In the database, the customer table has an integer to reference the customer type.  The customer type table has 2 columns, id and string for the type.  The combo box displays the string, but in my Insert/Update commands, I can't pass the string back to the customer table, I need to pass the integer.  How can I do this?

    Here's a basic layout of what I have:
    DataSet for DataGridView:
    SELECT C.CustomerId, C.Fname, CT.Type FROM Customer C, CustomerType CT WHERE C.CustomerTypeId = CT.CustomerTypeId;

    DataSet for DataGridViewComboBoxColumn:
    SELECT Type FROM CustomerType;

    DataSet Insert Command:
    INSERT INTO Customer (Fname, CustomerTypeId) VALUES (?Fname, ?CustomerTypeId);

    ...InsertCommand.Parameters.Add("?Fname", "Fname");
    ...InsertCommand.Parameters.Add("?CustomerTypeId", "XXXXXXXXXX");

    What goes in XXXXXXXXXX for this?  The source column for the combo box is going to be text, right?  I could try coordinating the index value perhaps to match the table, but I'm not sure how good that approach would be, or what exactly I need to do it.

    Hope this makes sense.  I've been pounding my head and searching the forums/net, but I can't find a similar situation.

    Thanks,
    Malcolm




    Wednesday, August 12, 2009 6:34 AM

Answers

  • Ok, I have figured it out myself.  I changed from trying to do a combined SQL (multiple insert statements) to just using a CommandType.StoredProcedure instead.  Using the Stored Procedure, I'm able to insert to as many tables as I like and keep foreign key integrity.  Brief example below for anyone who has read this and wanted to know how to do it too.  Btw, I'm using MySQL, but this will work with any ADO.NET source.  I've also left out the UPDATE and DELETE commands, those are more obvious to do.

    The example below is complete.  You just need a database with the appropriate tables, and a stored procedure to match.  In the stored procedure, you can get the last_insert_id() from the first insert into the contacts table, put it in a variable, then use it to insert to child tables (as many as you like).  This works with doing multiple "new" contacts at a time, not just one.  So, for example, I'm in my datagridview, and I add 4 new rows, then call the Update(), this will handle all 4 properly.  You may need to tweak for your environment a little.  My real project is much more complex than this example, and I have it working fine now. finally :)

    Thanks to all who replied and were helpful!

    Malcolm



    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using MySql.Data.MySqlClient;


    namespace ExampleJoin
    {
        public partial class Form1 : Form
        {
            DataSet dataSet = null;
            MySqlDataAdapter da, da1;
            string connStr = "server=localhost;user id=myuser;password=mypassword;database=examplejoin";
            MySqlConnection myConnection;

            public Form1()
            {
                InitializeComponent();
                myConnection = new MySqlConnection(connStr);
                myConnection.Open();
                dataSet = new DataSet("ContactList");
            }

            private void Form1_Load(object sender, EventArgs e)
            {
        // id is an integer primary key, type is a text description
                string sql = @"SELECT id, type FROM type ORDER BY id ASC;";
                da = new MySqlDataAdapter(sql, myConnection);
                da.InsertCommand = new MySqlCommand(sql, myConnection);
                da.Fill(dataSet, "type");

                Combo1.DataSource = dataSet.Tables["type"];
        //The real value we want is id, so we set that with valuemember
                Combo1.ValueMember = "id";
        //However, the user doesn't want to see numbers, they want text, so tie the type to the display
                Combo1.DisplayMember = "type";
                
                // -----

        //contactid=integer primary key, name=text, typeid = foreign key from the type table
                sql = @"SELECT contactid, name, typeid FROM contacts ORDER BY contactid ASC;";
                da1 = new MySqlDataAdapter(sql, myConnection);
                da1.InsertCommand = new MySqlCommand(sql, myConnection);
                da1.Fill(dataSet, "contacts");
                data1.DataSource = dataSet.Tables["contacts"];

                // -----

                setupInsertCommand();
            }

            private void setupInsertCommand()
            {
                da1.InsertCommand.CommandText = "sp_InsertContact";
        da1.InsertCommand.CommandType = CommandType.StoredProcedure;
                da1.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 45, "name");
                da1.InsertCommand.Parameters.Add("?typeid", MySqlDbType.Int32, 4, "typeid");
            }

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable changes = new DataTable();
                changes = dataSet.Tables[1].GetChanges();

                if (changes != null)
                {
                    da1.Update(changes);
                    dataSet.Tables["contacts"].AcceptChanges();
                }
            }
        }
    }

    • Marked as answer by MalcolmT Wednesday, September 2, 2009 3:29 AM
    Wednesday, September 2, 2009 3:29 AM

All replies

  • I believe you would need to select CustomerTypeID as well and keep it in your DataSet. You do not need to display it, but since this is the key value, you need to have it on client side to be able to pass all the required information to INSERT SQL statement
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, August 12, 2009 10:34 AM
    Moderator
  • Hmm.  I'm not sure how that would work.  If you are inserting a new record, the user selects a choice from the combo box.  At that point, you know two things.  The text of the choice, and the index of the choice in the combo box.  You can't know what the CustomerTypeId would be at that point, unless you match up the ComboBox Indexes to match during design.  And even if you do that, how do you add that to a parameterized query?

    ...InsertCommand.Parameters["?CustomerTypeId"].Value = CustomerTypeComboBox.Index

    That doesn't work, treats it as being null....so what would?

    Thanks,
    Malcolm
    Wednesday, August 12, 2009 4:46 PM
  • Hi MalcolmT,

     

    The insertcommand is only called when there are some newly added rows.

    So even you set the parameters to the insertcommand and call dataadapter.update(ds) like this:

     

       conn = new SqlConnection("Data Source=(local)\\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True;");
                da = new SqlDataAdapter();
    
                da.SelectCommand = new SqlCommand("select id, name from Stu", conn);
    
                ds = new DataSet();
    
                da.Fill(ds);
                da.InsertCommand = new SqlCommand("INSERT INTO Stu (id) VALUES (@myId)", conn);
                da.InsertCommand.Parameters.AddWithValue("@myId", 16);
           
                da.Update(ds);



    Because there are no row with the rowstate of added, the insertcommand won’t be called. Thus, something like this will stimulate the insertcommand, although the row added to the database is the one with id = 16:

     

                conn = new SqlConnection("Data Source=(local)\\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True;");
                da = new SqlDataAdapter();
    
                da.SelectCommand = new SqlCommand("select id, name from Stu", conn);
    
                ds = new DataSet();
    
                da.Fill(ds);
                da.InsertCommand = new SqlCommand("INSERT INTO Stu (id) VALUES (@myId)", conn);
                da.InsertCommand.Parameters.AddWithValue("@myId", 16);
           
                ds.Tables[0].Rows.Add(555, "edith");
    da.Update(ds);


     

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     

    • Proposed as answer by Yichun_Feng Wednesday, August 19, 2009 1:31 AM
    • Marked as answer by Yichun_Feng Wednesday, August 19, 2009 8:16 AM
    • Unmarked as answer by MalcolmT Thursday, August 20, 2009 3:21 AM
    • Unproposed as answer by MalcolmT Thursday, August 20, 2009 3:21 AM
    Tuesday, August 18, 2009 9:53 AM
  • Hi Yichun,
       I understand what you are doing, but this is not what I am attempting to do.  Let me try to illustrate it better:

    // Global declaration:
    DataAdapter da1, da2, da3;
    DataSet dataSet = new DataSet("CustomerList");
    
    //Load the contacts:
    private void loadCustomers() {
       string sql = @"SELECT type from customer_type;";
       da1 = new DataAdapter(sql, myConnection);
       da.Fill(dataSet, "customer_type");
       DataTableReader dr = new DataTableReader(dataSet.Tables["customer_type"]);
       dr = dataSet.Tables["customer_type"].CreateDataReader();
       while(dr.read()) {
          CustomerTypeComboBox.Items.Add(dr.GetString(0));
       }
    
       sql = @"SELECT c.name, c.id, ct.type AS ctype FROM customers c
                   INNER JOIN customer_type ct
                      ON c.customerTypeId = ct.customerTypeId;";
       da2 = new DataAdapter(sql, myConnection);
       da2.Fill(dataSet, "customers");
       dataGridViewCustomerList.DataSource = dataSet.Tables["customers"];
    
       setupInsertCommand();
       //setupDeleteCommand();
       //setupUpdateCommand();
    }
    
    //Everything loads fine, I see records in the DataGridView
    //correctly, etc.
    
    private void setupInsertCommand() {
       da2.InsertCommand = new SqlCommand(@"INSERT INTO
          customers (name, type) VALUES (?name, ?type);", myConnection);
    
       da2.InsertCommand.Parameters.Add("?name", VarChar, 50, "name");
       da2.InsertCommand.Parameters.Add("?type, Int32, 4, "XXXX");
    }
    
    
    Then I have a button that, when clicked, calls the methods for GetChanges(), Merge(), etc.

    It works, other than the fact that in the customers table, the customer type is an integer, and in the combo box on the datagridview, it's text.  I'm currently trying to experiment with using Insert() instead of Add() on the ComboBox, and using the index == the customerTypeId, then trying to reference that in the InsertCommand.Parameters.Add()...but if someone has a better answer, I'm all for it.

    Thanks,
    Malcolm

    Thursday, August 20, 2009 3:50 AM
  • Hi Malcolm,

    So your problem is how to find the the customerTypeId while choosing in the combobox?
    That depends on the datasource of your combobox. In general, I think you can use dataview's rowfiler to get the row you want. And then get the customerTypeId column. You can refer to this MSDN document: http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter(VS.71).aspx

    Does this works for you?


    Best Regards

    Yichun Feng

    Friday, August 21, 2009 3:10 AM
  • The datasource for the combobox is set dynamically in the code from my previous reply.  As tot he RowFilter property, I'm not sure how that helps.  I don't need to filter rows.  I just need to know what/how to put the CustomerTypeId in place of the "XXXXX" at the bottom of my previous code posting.

    Thanks,
    Malcolm
    Monday, August 24, 2009 5:09 PM
  • Ok, I have figured it out myself.  I changed from trying to do a combined SQL (multiple insert statements) to just using a CommandType.StoredProcedure instead.  Using the Stored Procedure, I'm able to insert to as many tables as I like and keep foreign key integrity.  Brief example below for anyone who has read this and wanted to know how to do it too.  Btw, I'm using MySQL, but this will work with any ADO.NET source.  I've also left out the UPDATE and DELETE commands, those are more obvious to do.

    The example below is complete.  You just need a database with the appropriate tables, and a stored procedure to match.  In the stored procedure, you can get the last_insert_id() from the first insert into the contacts table, put it in a variable, then use it to insert to child tables (as many as you like).  This works with doing multiple "new" contacts at a time, not just one.  So, for example, I'm in my datagridview, and I add 4 new rows, then call the Update(), this will handle all 4 properly.  You may need to tweak for your environment a little.  My real project is much more complex than this example, and I have it working fine now. finally :)

    Thanks to all who replied and were helpful!

    Malcolm



    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using MySql.Data.MySqlClient;


    namespace ExampleJoin
    {
        public partial class Form1 : Form
        {
            DataSet dataSet = null;
            MySqlDataAdapter da, da1;
            string connStr = "server=localhost;user id=myuser;password=mypassword;database=examplejoin";
            MySqlConnection myConnection;

            public Form1()
            {
                InitializeComponent();
                myConnection = new MySqlConnection(connStr);
                myConnection.Open();
                dataSet = new DataSet("ContactList");
            }

            private void Form1_Load(object sender, EventArgs e)
            {
        // id is an integer primary key, type is a text description
                string sql = @"SELECT id, type FROM type ORDER BY id ASC;";
                da = new MySqlDataAdapter(sql, myConnection);
                da.InsertCommand = new MySqlCommand(sql, myConnection);
                da.Fill(dataSet, "type");

                Combo1.DataSource = dataSet.Tables["type"];
        //The real value we want is id, so we set that with valuemember
                Combo1.ValueMember = "id";
        //However, the user doesn't want to see numbers, they want text, so tie the type to the display
                Combo1.DisplayMember = "type";
                
                // -----

        //contactid=integer primary key, name=text, typeid = foreign key from the type table
                sql = @"SELECT contactid, name, typeid FROM contacts ORDER BY contactid ASC;";
                da1 = new MySqlDataAdapter(sql, myConnection);
                da1.InsertCommand = new MySqlCommand(sql, myConnection);
                da1.Fill(dataSet, "contacts");
                data1.DataSource = dataSet.Tables["contacts"];

                // -----

                setupInsertCommand();
            }

            private void setupInsertCommand()
            {
                da1.InsertCommand.CommandText = "sp_InsertContact";
        da1.InsertCommand.CommandType = CommandType.StoredProcedure;
                da1.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 45, "name");
                da1.InsertCommand.Parameters.Add("?typeid", MySqlDbType.Int32, 4, "typeid");
            }

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable changes = new DataTable();
                changes = dataSet.Tables[1].GetChanges();

                if (changes != null)
                {
                    da1.Update(changes);
                    dataSet.Tables["contacts"].AcceptChanges();
                }
            }
        }
    }

    • Marked as answer by MalcolmT Wednesday, September 2, 2009 3:29 AM
    Wednesday, September 2, 2009 3:29 AM
  • Hi Malcolm,

     

    Congratulations! Also thanks for sharing the solution. Members who have the similar problem will be benefit from this thread.

     

    Best Regards

    Yichun Feng

    Wednesday, September 2, 2009 4:10 AM