Sometimes you need to insert a data to your database. Here is the safer way to insert a row using SQL parameter.
Passing parameters to SQL prevents you build dynamic SQL statement. Building a dynamic SQL statement is a security risk such as SQL injection.
Here is the sample code for insert statement using parameters.
public void InsertRowToDatabaseUsingParams()
//create SQL connection.
using (SqlConnection conn = new SqlConnection(connectionString))
//open sql connection.
string sqlCommand = "Insert into TestTable (column1,column2) Values(@column1,@column2);";using (SqlCommand cmd = new SqlCommand(sqlCommand, conn))
//add column value to parameters of SqlCommand
SqlParameter param = new SqlParameter("@column1", SqlDbType.VarChar, 50);
param =new SqlParameter("@column2", SqlDbType.VarChar, 50);
Doing some testing in SQL Azure, I found that table creation is a slightly different beast. Simply using a SQL table creation script generated by the SQL Server Management Studio does not translate into the Azure environment. I needed to make some modifications first to get the code working correctly, specifically how Indexes are created.
In my last blog post, I talked about performing SQL transactions as part of the SQL statement itself. Now I want to talk about an alternative method for implementing SQL transactions within .NET code. Basically, the SqlTransaction object can perform the function of grouping together the execution of multiple SQL statements within .NET code.
Below is an example of a SqlTransaction object in action:
//Create and open a connection to the dataseusing (SqlConnection conn = newSqlConnection(connStr))
//Next, create a sql transaction using our current connection.using (SqlTransaction transact = conn.BeginTransaction())
//Execute the first sql commandstring sqlStr1 =
"INSERT INTO TestTable1 (Field1) values ('This is a sample value.')";
using (SqlCommand cmd1 = newSqlCommand(sqlStr1, conn, transact))
//Execute the second sql commandstring sqlStr2 =
"INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL')";
using (SqlCommand cmd2 = newSqlCommand(sqlStr2, conn, transact))
} //Execute the third sql commandstring sqlStr3 =
"INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.')";
using (SqlCommand cmd3 = newSqlCommand(sqlStr3, conn, transact))
} //Finally, make sure to commit the transaction
//If there is a sql exception, we need to back out the transaction
As you can see, the SqlTransaction object is created through a SqlConnection object’s BeginTransaction method. It must be included with all of the SqlCommand objects for all of the commands to function as part of the same transaction. The Commit method must be called on the SqlTransaction object in order to complete the transaction. If there is an exception thrown, it must be caught and dealt with using the Rollback method in the SqlTransaction object. I hope that helps with any future SQL coding.
When writing MS SQL statements, sometimes it’s necessary to have several queries that work together in unison. Whether it’s updating, deleting or inserting multiple rows into multiple, different tables, it’s nice to be able to run a series of SQL commands as one single step. This is where running a batch transaction comes in handy, as the following set of sample code demonstrates.
BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('This is a sample value.');
INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL');
INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.');
COMMIT TRANSACTION BigGroupInsert;
As you can see, this transaction will perform all of the Insert statements specified. If there is an error processing one of the statements, like one of the values being to large for the target column, an error will occur for just that statement and all of the rest of the SQL commands will process. That includes commands that were intended to occur after the failed step, not just the ones before.