It may sound bit rude and may be disgusting to today’s developers and architects of doing it this way, but yes, it is also an option. And, I agree with them.
If you believe that all the latest mechanisms like the one mentioned above does not suits your requirement, form a query by yourself and execute it. But that too when your project design permits you to execute the query directly from .NET Data Access Layer, one can use Command Object with CommandType as Text.
And to execute multiple insert queries, we can simply append the insert queries separated by semi-colon “;” and use that as the CommandText for your Command Object. Use the ExecuteNonQuery() method of Command Object and observe the resulting number of records affected.
Well, there are couple of ways in which we can form the SQL Statement for our need. Let us see each of them one by one.
Append Insert Statements
In this case we are going to append each of t Insert statement one after the other and execute it as a single command. The syntax that we are trying to achieve here is
INSERT INTO Person VALUES (‘1’, ‘AA’); INSERT INTO Person VALUES (‘2’, ‘BB’);
The .NET 1.1 code for creating sql query having 4 simultaneous insert and executing it with the command object would look like this:
private void btnInsertByJoiningQueries_Click(object sender, System.EventArgs e)
{
string sqlText = "INSERT INTO Person VALUES ( '" + txtPersonId1.Text.Trim() + "',' " + txtPersonName1.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId2.Text.Trim() + "',' " + txtPersonName2.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId3.Text.Trim() + "',' " + txtPersonName3.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId4.Text.Trim() + "',' " + txtPersonName4.Text + "'); ";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlText,connection);
command.CommandType = CommandType.Text;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
In the example above, it returns the message “Number of records affected : 4 “. Multiple records inserted, Mission Accomplished.
Use Select and Union All for Insert
This is quite similar to appending insert statements, but rather than appending each complete Insert statement we are going to first going to Select the values and then pass it to Insert statement to Insert them in the table. The syntax that we are trying to achieve here is
INSERT INTO TableName (Col1, Col2)
SELECT 1, ‘AA’
UNION ALL
SELECT 2, ‘BB’
UNION ALL
SELECT 3, ‘CC’
Rest, the approach is quite similar to what we saw in “Append Insert Statements” section. So the code for it is left to you. Still if you need some assistance feel free to post it.
Pass the values in a string with ColumnSeperator and/or RowSeperator and pass it to Stored proc in SQL Server. Split it in proc and Insert
Here what we can do is, from the front end create a string with values separated by predefined ColumnSeperator and/or RowSeperator and then let Stored Proc parse it, separate the data, bring the data into useful format and then insert it into the respective table. Well, I am not going to go in its details as I also believe that this approach should be used when all your other options are really ruled out. But I can give you tips for it. Create a function which returns you the values after splitting the in the desired format. I found couple of them as mentioned below :
Split functions for SQL Server 2000 at the URLs :
Split functions for Oracle at the URLs :
May be you can take the permission from the Author and modify and use it.
Key Notes:
1. If number of Insert queries are fairly large in number, use StringBuilder rather than simple string object for better performance.
2. Go for clubbing the SQL statements only if you do not have any option left with you. This is surely an option, but not as maintainable and secure as other previously mentioned options.
Other Useful References:
I came across couple of articles which I really found relevant and useful, I advise you to go through them