Using SQL Parameters for Insert statement.

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.Value ="value1";

param =new SqlParameter("@column2", SqlDbType.VarChar, 50);
param.Value ="value2";



Kyoungsu Do
Software Developer
ImageSource, Inc.

Index Creation In SQL Azure

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.

Continue reading

SQL Transactions in .NET

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 datase
 using (SqlConnection conn = new SqlConnection(connStr))

      //Next, create a sql transaction using our current connection.
      using (SqlTransaction transact = conn.BeginTransaction())
                //Execute the first sql command
                string sqlStr1 =
                    "INSERT INTO TestTable1 (Field1) values ('This is a sample value.')";
                using (SqlCommand cmd1 = new SqlCommand(sqlStr1, conn, transact))

                //Execute the second sql command
                string sqlStr2 =
                     "INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL')";
                using (SqlCommand cmd2 = new SqlCommand(sqlStr2, conn, transact))
                }                //Execute the third sql command
                string sqlStr3 =
                     "INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.')";
                using (SqlCommand cmd3 = new SqlCommand(sqlStr3, conn, transact))
                }                //Finally, make sure to commit the transaction
           catch (Exception)
                //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.

Richard Franzen
Sr. Developer
ImageSource, Inc. 


Backing Out Of A SQL Transaction

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.

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.');

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.

Continue reading