inserting data into multiple tables using a web form

i would like to know what is the standard/best way of doing the following:

i have a form web app in asp.net and using C#

the user will enter data into the form and click INSERT and it will insert data into 4 different tables.

the fields are:

primarykey, animal, street, country

the form allows for multiple animals, multiple streets and multiple countries per primarykey. so when i have data like this:

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

i need it inserted into tables like this:

table1:
1,rhino
1,cat
1,dog

table2:
1,luigi st
1, paul st

table3:
1,russia
1,israel

questions

1. I'm at a total loss on how to do this. if i just had one table and one set of data per primary key i would just use the InsertQuery and do it this way, but since it is multiple tables i don't know how to开发者_运维技巧 do this??

2. what control(s) should i use in order to allow user to input multiple values? currently i am just using textboxes and thinking of separating the entries by semi colons, but that's probably not the right way.

I wanted to recommend that you take advantage of the new multirow insert statement in SQL 2008 so that you can just pass a sql statement like this:

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

To your SqlCommand but I don't know how to build a statement like that w/o risking being victim of a SQL Injection Attack.

Another alternative is to define data table types in your sql database:

And then construct a DataTable in C# that matches your datatable type definition:

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

// 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.
    insertCommand.ExecuteNonQuery();
}

Read more here.

Or if you are familiar with Stored Procedures, same as previous suggestion but having the stored procedure receive the DataTable t as parameter.

If none of the above work for you, create a SqlTranscation from the Connection object and iterate through each row of each data set inserting the record in the appropriate table and finally commit the transaction. Example here.

Use Checkboxes on the front end. Have a service/repository to save the user data. Something like the following:

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)"))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@UserId", userId);
            foreach(string animal in animals)
            {
                cmd.Parameters.AddWithValue("@Animal", animal);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

There are more complex solutions, but this is a simple one.