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.
conn.Open();
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";
cmd.Parameters.Add(param);

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

//Process
cmd.ExecuteNonQuery();

}
}
}

Kyoungsu Do
Software Developer
ImageSource, Inc.

Microsoft SharePoint 2013 – Interesting Article for SharePoint Administrators

If you want a high level look at Microsoft SharePoint 2013 and some expected updates and how they will affect various roles within your organization you need to check out this article. It is a 4 part series on Microsoft SharePoint 2013. Part 1 is for Administrators: 35,000 Foot View of SharePoint 2013 for Administrators

Brian Alderman’s previous publications include Windows 2000 Professional and SQL Server 2000 Administration. He is an active speaker at SharePoint Industry conferences including SharePoint Saturday’s and the SharePoint Best Practices Conference.

Al Senzamici, PMP
Program Manager
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

Executing Retry Logic in C#

Sometimes an exception isn’t just an exception. For example, SQL can have connection or timeout issues and file IO can have problems competing for file access. In these situations, sometimes it makes sense to try executing the code again rather than letting it completely error out. Below is some C# sample code for executing some simple retry logic.

for (int retryAttempt = 1; retryAttempt <= MAX_RETRY_ATTEMPTS; retryAttempt++)
{
     try
     {
          // Perform code execution here
          break;
     }
     catch (Exception ex)
     {
          // Perform any logging here          if (retryAttempt < MAX_RETRY_ATTEMPTS)
          {
               Thread.Sleep(1000); // Sleep 1 second before retrying
          }
          else
          {
               throw;
          }
     }
}

As you can see, it’s pretty simple.  Execute the actual code logic within the try block, and perform any necessary exception logging within the catch block.

Richard Franzen
Sr. Developer
ImageSource, Inc.

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))
 {
      conn.Open();

      //Next, create a sql transaction using our current connection.
      using (SqlTransaction transact = conn.BeginTransaction())
      {
           try
           {
                //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))
                {
                     cmd1.ExecuteNonQuery();
                }

                //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))
                {
                     cmd2.ExecuteNonQuery();
                }                //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))
                {
                     cmd3.ExecuteNonQuery();
                }                //Finally, make sure to commit the transaction
                transact.Commit();
           }
           catch (Exception)
           {
                //If there is a sql exception, we need to back out the transaction
                transact.Rollback();
                throw;
           }
      }
  }

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.

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.

Continue reading

Building Out Distributed Apps (Big Data)

Yesterday, I attended a webinar by O’Reilly on how to reduce the pain of building out distributed applications. The focus was on scalability, which makes sense, since this is why you would want to distribute your applications.

Apart from the host’s unfortunate resemblance to Little Lord Fauntleroy, there was some interesting observations to be made. To wit:

Continue reading