Proper way to make a connection and query to a SQL Server Express database

Question

I need a sample C# (console application) code witch connects to an SQL Server Express database and inserts a few variables into a table "laptops"

• SQL Server Express is @ localhost
• user name is database
• and password is testdatabase

What is the proper way to do that ?

Solution

Basic ADO.NET 101:

• set up a connection
• set up a command to do something
• execute that command

Step 1: setting up a connection

You need to know the connection string to your database. Check out http://www.connectionstrings.com for a ton of examples.

In your case, you say it's a local SQL Server Express instance - but unfortunately, you didn't mention what your database is called..... your connection string will be something like:

server=(local)\SQLEXPRESS;database=YourDatabaseName;user id=database;pwd=testdatabase

Step 2: setting up a command

You can have various commands - to select data, to delete that, or to insert data. Whatever you do - I would recommend to always use parametrized queries to avoid SQL injection.

So your code here would look something like:

string connectionString = "server=(local)\SQLEXPRESS;database=YourDatabaseName;user id=database;pwd=testdatabase";

string insertStmt = "INSERT INTO dbo.Laptops(Name, Model, ScreenSize) " + 
                    "VALUES(@Name, @Model, @Screensize)";

using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(insertStmt, conn))
{
    // set up the command's parameters
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = "ASUS SX30";
    cmd.Parameters.Add("@Model", SqlDbType.VarChar, 50).Value = "Ultralight";
    cmd.Parameters.Add("@Screensize", SqlDbType.Int).Value = 15;

    // open connection, execute command, close connection
    conn.Open();
    int result = cmd.ExecuteNonQuery();
    conn.Close();
}