Insert data into multiple tables using a web form

Question

I would like to know what is the standard / best way to do the following:

I have a form web application in asp.net and using C #

the user enters data into the form and presses INSERT and inserts the data into 4 different tables.

fields:

primarykey, animal, street, country 

The form allows several animals, several streets and several countries to the main key. so when i have this data:

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

I need to insert this into tables as follows:

 table1: 1,rhino 1,cat 1,dog table2: 1,luigi st 1, paul st table3: 1,russia 1,israel 

the questions

• I have a complete loss of how to do this. if I had only one table and one dataset for the primary key, I would just use InsertQuery and do it this way, but since there are several tables, I don’t know how to do it.

• What controls should I use to allow the user to enter multiple values? I'm currently just using text fields and thinking about splitting records in grayscale, but this is probably not the right way.

Answer 1

I would recommend that you use the new Multirow insert statement in SQL 2008 so that you can simply pass the sql statement as follows:

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

In your SqlCommand, but I don’t know how to build such a statement without the risk of becoming a victim of a SQL Injection attack.

Another alternative is to determine the types of data tables in your sql database:

And then build a DataTable in C # that matches the definition of your data type:

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

More details here .

Or, if you are familiar with stored procedures, like the previous sentence, but having a stored procedure, get the DataTable t parameter as the parameter.

If none of the above actions work for you, create an SqlTranscation from the Connection object and iterate through each row of each data set, insert the record in the corresponding table, and finally complete the transaction. An example is here.

Answer 2

Use the flags on the front panel. Have a service / repository to save 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(); } } } } 

More complex solutions exist, but it is simple.