.NET Framework ADO.NET Best Practices - Executing Sql Statements

Example

public void SaveNewEmployee(Employee newEmployee)
{
    // best practice - wrap all database connections in a using block so they are always closed & disposed even in the event of an Exception
    // best practice - retrieve the connection string by name from the app.config or web.config (depending on the application type) (note, this requires an assembly reference to System.configuration)
    using(SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionName"].ConnectionString))
    {
        // best practice - use column names in your INSERT statement so you are not dependent on the sql schema column order
        // best practice - always use parameters to avoid sql injection attacks and errors if malformed text is used like including a single quote which is the sql equivalent of escaping or starting a string (varchar/nvarchar)
        // best practice - give your parameters meaningful names just like you do variables in your code
        using(SqlCommand sc = new SqlCommand("INSERT INTO employee (FirstName, LastName, DateOfBirth /*etc*/) VALUES (@firstName, @lastName, @dateOfBirth /*etc*/)", con))
        {
            // best practice - always specify the database data type of the column you are using
            // best practice - check for valid values in your code and/or use a database constraint, if inserting NULL then use System.DbNull.Value
            sc.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 200){Value = newEmployee.FirstName ?? (object) System.DBNull.Value});
            sc.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar, 200){Value = newEmployee.LastName ?? (object) System.DBNull.Value});

            // best practice - always use the correct types when specifying your parameters, Value is assigned to a DateTime instance and not a string representation of a Date
            sc.Parameters.Add(new SqlParameter("@dateOfBirth", SqlDbType.Date){ Value = newEmployee.DateOfBirth });

            // best practice - open your connection as late as possible unless you need to verify that the database connection is valid and wont fail and the proceeding code execution takes a long time (not the case here)
            con.Open();
            sc.ExecuteNonQuery();
        }

        // the end of the using block will close and dispose the SqlConnection
        // best practice - end the using block as soon as possible to release the database connection
    }
}

// supporting class used as parameter for example
public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

---

Best practice for working with ADO.NET

• Rule of thumb is to open connection for minimal time. Close the connection explicitly once your procedure execution is over this will return the connection object back to connection pool. Default connection pool max size = 100. As connection pooling enhances the performance of physical connection to SQL Server.Connection Pooling in SQL Server
• Wrap all database connections in a using block so they are always closed & disposed even in the event of an Exception. See using Statement (C# Reference) for more information on using statements
• Retrieve the connection strings by name from the app.config or web.config (depending on the application type)
  • This requires an assembly reference to System.configuration
  • See Connection Strings and Configuration Files for additional information on how to structure your configuration file
• Always use parameters for incoming values to
  • Avoid sql injection attacks
  • Avoid errors if malformed text is used like including a single quote which is the sql equivalent of escaping or starting a string (varchar/nvarchar)
  • Letting the database provider reuse query plans (not supported by all database providers) which increases efficiency
• When working with parameters
  • Sql parameters type and size mismatch is a common cause of insert/ updated/ select failure
  • Give your Sql parameters meaningful names just like you do variables in your code
  • Specify the database data type of the column you are using, this ensures the wrong parameter types is not used which could lead to unexpected results
  • Validate your incoming parameters before you pass them into the command (as the saying goes, "garbage in, garbage out"). Validate incoming values as early as possible in the stack
  • Use the correct types when assigning your parameter values, example: do not assign the string value of a DateTime, instead assign an actual DateTime instance to the value of the parameter
  • Specify the size of string-type parameters. This is because SQL Server can re-use execution plans if the parameters match in type and size. Use -1 for MAX
  • Do not use the method AddWithValue, the main reason is it is very easy to forget to specify the parameter type or the precision/scale when needed. For additional information see Can we stop using AddWithValue already?
• When using database connections
  • Open the connection as late as possible and close it as soon as possible. This is a general guideline when working with any external resource
  • Never share database connection instances (example: having a singleton host a shared instance of type SqlConnection). Have your code always create a new database connection instance when needed and then have the calling code dispose of it and "throw it away" when it is done. The reason for this is
    • Most database providers have some sort of connection pooling so creating new managed connections is cheap
    • It eliminates any future errors if the code starts working with multiple threads