inserting data into multiple tables using a web form

Question

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.

Answer 1

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.

Answer 2

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.