i must know what's the standard/easiest way to do the next:

i've got a form web application in asp.internet and taking advantage of C#

the consumer will enter data in to the form and click on Place and it'll place data into 4 different tables.

the fields are:

primarykey, animal, street, country

the shape enables for multiple creatures, multiple roads and multiple nations per primarykey. then when i've data such as this:

[1],[rhino,cat,dog],[luigi st, paul st], [russia,israel]

i want it placed into tables such as this:


1,luigi st
1, paul st



  1. I am in a total loss regarding how to do that. basically just had one table and a bouquet of data per primary key i'd only use the InsertQuery and get it done by doing this, but as it is multiple tables i'm not sure how to get this done??

  2. what control(s) must i use to be able to allow user to input multiple values? presently i'm simply using textboxes and considering separating the records by semi colons, but that is most likely not the proper way.

I needed to suggest that you make use of the new multirow place statement in SQL 2008 to ensure that you can easily pass a sql statement such as this:

INSERT INTO table1(id,animal_name) values (1,cat),(1,dog),(1,horse)... 

For your SqlCommand but I'm not sure building an argument like this w/o jeopardizing being victim of the SQL Injection Attack.

Another alternative would be to define data table types inside your sql database: enter image description here

enter image description here

After which create a DataTable in C# that suits your datatable type definition:

DataTable t = new DataTable();
foreach(var element in your animals_list)
   DaraRow r = t.NewRow();
   r.ItemArray = new object[] { element.id, element.animal_name };

// Assumes connection is an open SqlConnection.
using (connection)
    // Define the INSERT-SELECT statement.
    string sqlInsert = "INSERT INTO dbo.table1 (id, animal_name) SELECT nc.id, nc.animal_name FROM @animals AS nc;"

    // Configure the command and parameter.
    SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@animals", t);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.AnimalTable";

    // Execute the command.

Read more here.

Or if you're acquainted with Saved Methods, just like previous suggestion but getting the saved procedure get the DataTable t as parameter.

If no above meet your needs, produce a SqlTranscation in the Connection object and iterate through each row of every data set placing the record within the appropriate table and lastly commit the transaction. Example here.

Use Checkboxes around the front-end. Possess a service/repository in order to save the consumer data. Something similar to the next:

public void UpdateUserAnimals(Guid userId, string[] animals)
    using (SqlConnection conn = new SqlConnection("connectionstring..."))
        using (SqlCommand cmd = new SqlCommand("Insert Into UserAnimals(UserId, Animals) values (@UserId, @Animal)"))
            cmd.Parameters.AddWithValue("@UserId", userId);
            foreach(string animal in animals)
                cmd.Parameters.AddWithValue("@Animal", animal);

You will find more complicated solutions, but this can be a simple one.