SqlCommand

Introduction to SqlCommand

SqlCommand encapsulates the SQL statements you want to execute. Using an established database connection, it sends instructions to the database and retrieves execution results.
The constructor accepts two parameters: SQL text (cmdText) and database connection object (SqlConnection).

Simple Example

Make sure you have SQL Server installed locally and the target table created. This demo uses the Article table with one sample record inserted.

private void button1_Click(object sender, EventArgs e)
{
    // 1. Define connection string
    string connectionString = "User ID=sa;pwd=qweqwe;initial catalog=DB;Data Source=.;";

    // 2. Create connection object
    SqlConnection conn = new SqlConnection(connectionString);

    // 3. Define SQL statement to run
    string cmdText = "SELECT GETDATE(),count(1) FROM [DB].[dbo].[Article]";

    // 4. Create command object, bind SQL and database connection
    SqlCommand command = new SqlCommand(cmdText, conn);

    conn.Open();    // Open database connection
    // ExecuteScalar(): runs SQL and returns data from the [first row, first column] as object type

    object res = command.ExecuteScalar();

    MessageBox.Show(res.ToString());

    conn.Close();   // Close connection
}Code language: C# (cs)

SQL Server Table Script

CREATE TABLE Article(
    id INT PRIMARY KEY IDENTITY(1,1),
    Title NCHAR(10) NULL,
    AddTime NCHAR(10) NULL
);Code language: SQL (Structured Query Language) (sql)

Core Method: ExecuteScalar()

• Get aggregate‑query results: COUNT(), MAX(), MIN(), SUM()
• Fetch single scalar value, e.g. current database time, auto‑increment primary key ID

Notes:

1. Return type is object; manual type casting is required;
2. If your query returns multiple rows and columns, only the first row, first column is returned; remaining data gets discarded;
3. Call conn.Open() to open the connection before execution.

Recommended Coding Pattern

Use the using statement to auto‑release connection resources and avoid connection leaks caused by forgotten close calls:

string connectionString = "User ID=sa;pwd=qweqwe;initial catalog=DB;Data Source=.;";
string cmdText = "SELECT GETDATE(),count(1) FROM [DB].[dbo].[Article]";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(cmdText, conn);
    conn.Open();
    object res = command.ExecuteScalar();
    MessageBox.Show(res.ToString());
}
// Once leaving the using‑block, connection releases automatically. No need for manual conn.Close()Code language: JavaScript (javascript)

Additional Notes

SqlCommand provides three more frequently‑used execution methods:

1. ExecuteNonQuery(): For INSERT/UPDATE/DELETE operations; returns number of affected rows
2. ExecuteReader(): Returns DataReader stream for reading multiple result rows sequentially
3. ExecuteXmlReader(): Reads XML‑formatted data (seldom used)

We will cover these methods in later lessons.

SqlCommand