Archives / 2009 / August
  • Using Parameters with Dynamic SQL

    Some programming situations require you to use Dynamic SQL. Of course the problem with using Dynamic SQL is that this can lead to SQL Injection attacks. However, you can avoid these problems, by just changing how you submit Dynamic SQL to your back end database.

    To illustrate this, consider the sample table of users listed below.

    Login char(16) not null primary key,
    Password varchar(20) not null
    INSERT INTO User values('PSheriff', 'password')
    INSERT INTO User values('Keng', 'password')

    You can copy and paste the above SQL code into your database management system and create this table. After you have created this table, you might create a login screen and use some code like the following to see if the users are in the User table.

    Private Sub BADLoginCode()
     Dim sql As String
     Dim cmd As SqlClient.SqlCommand
     Dim rows As Integer

     sql = " SELECT Count(*) As TotalRows FROM User "
     sql &= " WHERE Login = '" & txtLogin.Text & "'"
     sql &= " AND Password = '" & txtPassword.Text & "'"

     cmd = New SqlClient.SqlCommand(sql)
     cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)

     rows = Convert.ToInt32(cmd.ExecuteScalar())

     If rows > 0 Then
     End If
    End Sub

    The problem with the above code is if a hacker where to type in the following into the Login ID field;

    ' OR 1=1 DELETE FROM Users --

    Then the resulting SQL that is submitted to the back end database is the following:

    SELECT Count(*) As TotalRows
    FROM User
    WHERE Login = '' OR 1=1
    DELETE FROM User --'
    AND Password = ''

    As you can see this will not only allow the user into the application, but will also delete all other users in the system! This is not a good thing.

    However, with just a very minor change to this code, you can protect yourself against this type of attack. Just like when calling a stored procedure you use Parameters on the command object to submit the login id and password to the input parameters, you can do the same type of coding with dynamic SQL.

    Private Sub LoginGood()
     Dim sql As String
     Dim cmd As SqlClient.SqlCommand
     Dim rows As Integer

     sql = " SELECT Count(*) As TotalRows FROM User "
     sql &= " WHERE Login = @sLogin "
     sql &= " AND Password = @sPassword"

     cmd = New SqlClient.SqlCommand(sql)
     cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
     cmd.Parameters.Add(New _
       SqlClient.SqlParameter("@Login", SqlDbType.Char))
     cmd.Parameters.Add(New _
       SqlClient.SqlParameter("@Password", SqlDbType.Char))
     cmd.Parameters.Item("@Login").Value = txtLogin.Text
     cmd.Parameters.Item("@Password").Value = txtPassword.Text

     rows = Convert.ToInt32(cmd.ExecuteScalar())
     If rows > 0 Then
     End If
    End Sub

    Notice the use of the Parameters in the dynamic SQL. When you now run this code, the command object and the parameters ensure that no SQL injection attacks will affect your code.

    Good Luck With Your Coding,
    Paul Sheriff

    Visit for a free eBook on "Fundamentals of N-Tier".



  • Cloning a DataRow

    I can't even tell you how many times over the last few years I have had to clone a row from one DataTable to another DataTable. To make this easier, I created a method that I can call at anytime to create this new DataRow and return a new DataTable back to me. I have another overload of this method that I can also pass in the new DataTable. In ADO.NET there is no easy way to take a single row from an existing DataTable and copy it to another DataTable. The major reason why it is not so easy is you can not add a DataRow that exists in one DataTable to another DataTable. As a result you must create a new DataRow object and copy all of the values from the original DataRow into this new one. You can then create a new DataTable (or use one with the same structure), and add that DataRow to that new DataTable. Below is a method that you can call to accomplish the copying of a single row from one DataTable to a new DataTable.

    C# Code

    private DataTable CloneDataRow(DataTable dtOld, int rowNumber)
      DataRow dr;
      DataTable dtNew;

      dtNew = dtOld.Clone();

      dr = dtNew.NewRow();

      dr.ItemArray = dtOld.Rows[rowNumber].ItemArray;


      return dtNew;

    VB.NET Code

    Private Function CloneDataRow(ByVal dtOld As DataTable, ByVal rowNumber As Integer) As DataTable
      Dim dr As DataRow
      Dim dtNew As DataTable

      dtNew = dtOld.Clone()

      dr = dtNew.NewRow()

      dr.ItemArray = dtOld.Rows(rowNumber).ItemArray


      Return dtNew
    End Function

    I hope you find this method as helpful as I have found it over the years.

    Good Luck With Your Coding,
    Paul Sheriff

    Visit for a free eBook on "Fundamentals of N-Tier".


  • About Nothing

    Sometimes in your code you will need to check to see if a value is nothing/null or not. In .NET there are many different ways to check for this condition. It can also be different depending on the language you use. These little differences can really bite you in the a**, so you need to be aware of the differences.

    I like to use the IsNullOrEmpty() method on the string class, so let's take a look at this method using C#.

    private void TestNullOrEmpty()
      string value;

      //Debug.WriteLine(String.IsNullOrEmpty (value));   // NOT VALID

      Value = null;


      Value = "Hello There";


      if (value != null)
        Debug.WriteLine("NOT null");

    In C# the first check of the Value variable is not valid since the string has not been initialized.

    In VB.NET you can also use IsNullOrEmpty() method, however, the first check is valid since VB.NET treats this type of error as a warning only. You can change this setting in the Project Settings for your project, so this would be a real error. You can also use the new IsNothing statement to check to see if a value is Not Nothing.

    Private Sub TestNullEmpty()
      Dim value As String


      value = Nothing


      value = "Hello There"


      If value IsNot Nothing Then
        Debug.WriteLine("NOT Nothing")
      End If
    End Sub

    I hope this helps someone in the future when you are moving from one language to another.

    Good Luck With Your Coding,
    Paul Sheriff

    Visit for a free eBook on "Fundamentals of N-Tier".


  • To Thread or not to Thread, Is That the Question?

    A lot of developers want to use threads to offload some processing. There are many different ways to use threads. More often than not it is not a question of how to do threading, but whether or not you should. If you are trying to solve performance problems with your application, you might not want to employ the use of threading, at least not until you have exhausted all other methods of enhancing performance.

    Adding more threads to your application can sometimes slow the application down more. You need to be very careful on how you utilize threading. You also need to be careful on what you do on those other threads of execution. If you are performing some method that sucks up a lot of processing on your CPU, then you could be robbing performance from your main thread of execution. This will not help your performance problems, but make them worse.

    An Example: Database Operations

    I have performed many performance reviews on code where programmers loop over 1000's of records. When this took too long, they tried putting this loop on a second thread. Of course, the better approach should have been to move this into a stored procedure, and use set-oriented operations instead of looping. Then if this process still took too long, use asynchronous processing that is built into ADO.NET instead of writing your own threading. I am always of the mind that Microsoft has much smarter people than myself and that they will do the background threading much more efficiently than I could write it.

    Use the BackgroundWorker Class

    If you really must have another thread do some work, you might want to check into the BackgroundWorker class. This class allows you to perform a long-running operation on a separate, dedicated thread. Again, since I am using a class that the smart folks at Microsoft have created, I figure they have put more thought into it and more testing than I have time to do. So, I will frequently use this class as opposed to creating my own thread. It also has a reporting mechanism built in, so I can report on the progress of my operation without having to create and raise my own events.

    Example of the BackgroundWorker Class

    I have created a sample WPF application that shows the updating of a progress bar in WPF. The BackgroundWorker class can raise a Progress event so you can do some UI work. Be careful to not do any UI updates from within the DoWork procedure.

    To try out this sample, you can download it from our website at Click on Tips and Tricks and download the "BackgroundWorker Class Sample".

    So, the next time you are tempted to use a thread, really think about the problem you are trying to solve. You might want to have someone else look over your code prior to doing some complicated threading. Sometimes, someone else will see a better way to do something that just might give you the performance you want without resorting to multiple threads.

    Thank you,
    PDSA, Inc.

    Visit for a free eBook on "Fundamentals of N-Tier" by Paul D. Sheriff


  • PDSA, Inc. Releases Update to Fundamentals of ASP.NET Security eBook

    PDSA, Inc. has just published the update to the popular eBook on ASP.NET Security. Below is a synposis of the book.

    Fundamentals of ASP.NET Security

    Security should be something all developers think about from the very beginning of a new project. This book will teach ASP.NET developers how to deal with security in their web applications. After reading this book you will have learned useful techniques that will allow you to build a good solid security framework for your ASP.NET applications.

    You will learn the following techniques:

    • The basics of cryptography
    • Simplifying .NET cryptography
    • How and where to securely store connection strings
    • How to take advantage of the ASP.NET Membership System
    • How to use the ASP.NET Personalization System
    • The differences between forms-based and windows-based authentication
    • How to securely connect to SQL Server

    This book will give you a huge jumpstart on understanding the security model in ASP.NET. You will have tons of samples to which to refer. This will save you hours of time and wasted development.

    Join Paul D. Sheriff and James H. Byrd as they take you through how to best use the ASP.NET security model. Both authors have a gift for breaking down complex concepts into an easy-to-understand language.

    Purchase a printed version of this book at When you purchase the printed book, you also get the eBook and the samples.

    Or you can purchase the eBook only at

    PDSA, Inc.


  • Why you Should Move to WPF

    If you have not taken a look at WPF yet, you really should. WPF is a great desktop development platform. Granted all of the of the tools are not yet in place, but Microsoft is pouring millions of dollars into developing WPF tools. Windows Forms is now considered a legacy technology and will no longer be updated. These two reasons alone are enough to convince you that you should start taking a little more than a serious look at WPF.

    WPF and XAML = A Great Combination

    WPF and XAML are the way of the future for development. Remember back when you did classic ASP applications? Remember mixing code and HTML together? Then remember when ASP.NET came out and how much easier it was to have a declarative syntax for controls? The same thing holds true to WPF. Instead of the design tools writing code for each control (aka Windows Forms), you now have XAML to define each control. You reserve the code for writing what you want to do with a control. This is a much cleaner way to develop desktop applications. In addition, XAML makes it so much easier to do animation, and to create visually appealing applications compared to Windows Forms.

    XAML and Silverlight

    Another great benefit of learning WPF and XAML is many of the same concepts will apply to Silverlight development. Silverlight uses XAML just like WPF. Sure, it is a subset of the XAML we have in the desktop world, but it is still the same XAML. And, with the release of Silverlight 3 you can do XAML and Silverlight development and create out-of-browser applications!

    Many Reasons For Moving to WPF

    There are many more reasons to take a good look at WPF as your desktop development platform. The following are some of the things I particularly like about WPF:

    • Ability to style or theme your applications without recompiling code
    • Can animate controls with little or no code
    • Separate business logic from UI code easier than Windows Forms
    • Easily create applications that take advantage of different screen resolutions
    • Able to add powerful 3D graphics without 3rd party tools
    • The data binding features are more powerful than Windows Forms
    • A designer can do most of the UI work, the developer can focus on the backend code
    • Much more...

    Yes, there are lots of reasons why WPF just makes sense. I hope you will find your way to WPF in the near future. As always, if you need a helping hand, PDSA will always be here to help.

    Learn WPF from PDSA

    PDSA, Inc. offers a workshop for learning WPF. Please contact me at for more information on having us bring this workshop to you and your team. We can offer this workshop in a hands-on format at your location, or we can present it as a webcast over the Internet.


  • Why Do I Need OOP?

    Most developers have been doing some form of Object Oriented Programming (OOP) for quite awhile now. Sometimes you might not even realize it. If you have been using VB 6 for example, then every control is a class, and you interact with it as an object by setting properties and calling methods on those objects. All you need to do now is to start creating your own classes, properties and methods.

    An easy way to start, is take just a few of your global functions and the properties those functions use and put them into a class. This is a good first start to creating your own classes. As you learn this, you will then start to see other ways to encapsulate your data and functions together.

    What Does OOP Give Me?

    OOP provides many benefits over not using any OOP techniques. You will see the following benefits:

    • Eliminate Global Variables
    • Modularity: Related data and methods together in one place
      • Allows for better unit testing
      • Allows you to focus on a smaller set of data & functionality
    • Better reusability
    • Easier to extend functionality
    • Hide Complexity

    As you can see, these are huge benefits! We all understand that global variables kill our productivity and make tracking down bugs very difficult. The ability to move data and the functions that operate upon that data into one place make reading code easier, and also contributes to better testing. You are better able to reuse code that is in classes.

    If You Don't Use OOP...

    Every new technology that comes out uses OOP techniques. If you don't start using these techniques, you could become a dinosaur and be faced with finding a new career. Ok, this is a little harsh, but it is true. If you want to take advantage of WPF, WCF, SilverLight and other technologies, you will need to understand OOP. I hope you will take sometime to really understand this all-important underlying technology on which everything you do these days is based.

    Need Help?

    Learning OOP is fairly simple. I have written several eBooks on OOP and other .NET topics that illustrate these techniques. We also offer training on many .NET topics, including OOPs. Feel free to email me for more information.


  • What Should I Be Learning? Or, Stop the World I Want To Get Off!

    Everything is Moving too Fast

    Yes, it does feel that way sometimes. Our industry is constantly changing. So much so, that it is difficult to keep up sometimes. Last week at Tech Ed, I heard the same comments from many attendees; "Which technology should I use?" "What should I be learning?" While there is no easy answer to this, and none of us has a crystal ball, there are a few things that are tried and true and have always worked in our industry.

    Stick with the Basics
    Remember that no matter what new tools are introduced, or regardless of which language you use, there are a few core disciplines that you need to know.

    • Structured Programming
    • Object Oriented Programming
    • Avoid copy and paste
    • Security
    • SQL / Set Theory

    This may seem like a lot to know, but these are the basics upon which you should base all your programs. The new tools, languages and technologies that appear will most likely just be syntax that you have to learn about.

    PDSA's Crystal Ball
    OK. So of all of the technologies that are coming out, which ones have we been working with or are looking at? We have found uses for all of the following:

    • ASP.NET
    • WPF (Windows Presentation Foundation)
    • WCF (Windows Communication Foundation)
    • Silverlight
    • Team System

    We have been doing project development in the above technologies for quite awhile, or are starting new projects using them. While, we certainly do not have a crystal ball into the future, these are technologies that we think will be core for quite awhile to come.