Execution optimization and parameter passing

1. Check SqlConnection Connection State

Property: conn.State

Enum type ConnectionState

// Open connection if it is not already open
if (conn.State != ConnectionState.Open)
{
    conn.Open();
}Code language: JavaScript (javascript)

Note: When used with using, the state automatically changes to Closed after the connection gets disposed.
Common states: Open / Closed / Connecting, etc.

2. Critical Null‑Check for ExecuteScalar

Validate the return value from ExecuteScalar to prevent exceptions caused by null values

object obj = sqlCom.ExecuteScalar();

// You must check both null and DBNull.Value
if (obj == null || obj == System.DBNull.Value)
{
    MessageBox.Show("Returned result is null");
}
else
{
    MessageBox.Show(obj.ToString());
}Code language: PHP (php)

Distinction:

• null: No rows were returned at all
• DBNull.Value: A row was found, but the target column holds a database NULL value

3. SqlCommand Parameter System (Core Defense Against SQL Injection)

1. Core Object

The SqlCommand.Parameters collection stores multiple SqlParameter instances.Parameterized queries completely eliminate SQL injection risks.

2. Parameter Creation Example

// Parameter name, data type, size
SqlParameter paramSql = new SqlParameter("@Title", SqlDbType.NVarChar, 250);

// Assign value
paramSql.Value = model.Title;

// Attach to command object
sqlCom.Parameters.Add(paramSql);Code language: JavaScript (javascript)

3. ParameterDirection Enum

paramSql.Direction = ParameterDirection.Output;

Enum Member	Value	Description
Input	1	Default, input parameter (pass values into SQL)
Output	2	Output parameter, retrieve values after stored procedure runs
InputOutput	3	Accepts input and returns output after execution

Full Working Sample

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = "Data Source=.;Initial Catalog=db;User ID=sa;Password=xxx";
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        // Safely open connection
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }

        SqlCommand sqlCom = new SqlCommand();
        sqlCom.Connection = conn;
        sqlCom.CommandTimeout = 60;
        // Always use parameterized SQL; never concatenate raw strings!
        sqlCom.CommandText = "SELECT [Title] FROM [dbo].[Article] WHERE Title = @Title";

        // Build parameter
        SqlParameter paramTitle = new SqlParameter("@Title", SqlDbType.NChar, 10);
        paramTitle.Value = "Test Title";
        sqlCom.Parameters.Add(paramTitle);

        object obj = sqlCom.ExecuteScalar();
        if (obj == null || obj == DBNull.Value)
        {
            MessageBox.Show("Returned result is null");
        }
        else
        {
            MessageBox.Show(obj.ToString());
        }
    }
}Code language: JavaScript (javascript)

Key Development Guidelines

1. Never concatenate SQL strings. Always use SqlParameter for parameterization;
2. Wrap all SqlConnection and SqlCommand inside using blocks for automatic resource cleanup;
3. Always perform dual‑check for null + DBNull.Value when calling ExecuteScalar;
4. Tell apart CommandTimeout (SQL statement execution timeout) and Connect Timeout inside connection string (connection‑establishment timeout).

Execution optimization and parameter passing