ADO.Net

Connection Strings

ConnectionStrings.com
SQLServer Express
Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind
SQLServer Express attaching a local database file (in Data Directory)
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True
Oracle XE without tnsnames.ora (User Id is CASE SENSITIVE):
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=MYID;Password=secret;

Tips

Use "using" (calls Dispose) or try/catch/finally (and if (SqlConnection!=null) SqlConnection.Close(). Use parameters, even in raw sql, to stop injection attacks.
Connection pooling is automatic but connections strings must be identical (use System.ConfigurationSettings.AppSettings["ConnStr"], preferably encrypted)
Database Nulls: use System.DBNull.Value (do not use null/Nothing).

if (!rdr.IsDBNull(0)) s = rdr.GetString(0);

//or

if (rdr["myId"] == DBNull.Value) s = "";

else s = (string) rdr["myId"];

Always test with DataRow.IsNull or use strongly typed dataset IsXNull and SetXNull methods.

For inserting/updating nullable fields, I use this helper (here as a 3.5 extension method)

internal static class SqlParameterExtensions

{

//nb instance methods always win over extension methods so you can't add a AddWithValue "overload"

internal static SqlParameter AddWithNullableValue(this SqlParameterCollection col, string parameterName, object value)

{

//everything else

return col.Add(new SqlParameter(parameterName, value ?? DBNull.Value));

}

Concurrency

None: last one wins. Most scalable!
Pessimistic: lock the record. Use a transaction (see below) or manually write to a lock table (with an expiring timestamp).
Optimistic: check whether record changed and report it.
- Select and manually check before update.
- Update ... where... with all prior values (or a subset). NULLable fields need (col is null and origCol is null) or (col = origCol)
- Timestamp/version column (sql timestamps are actually byte[])
You mostly do it manually (apart from SQLDataSource's wizard), unless you use an ORM
- DataAdaptor: Specify the sql manually in the UpdateCommand and in the RowUpdated event check the RecordsAffected
- SQLDataSource: Has a ConflictDetection property (default OverwriteChanges; or CompareAllValues). There is also an OldValuesParameterFormatString. The wizard has an optimistic checkbox. In the RowUpdated event, check AffectedRows. NB: doesn't properly check for NULLable fields
- GridViews: Put the timestamp on the DataKeyNames otherwise it won't get passed.

Paging and Identity

Paging. Use a stored procedure: SET ROWCOUNT @pageSize and pass in the starting point in the unique key(s) (where key > @startKey). Don't use SqlDataAdapter.Fill with start/end as all it does it filter the full query.
Get the identity from the last added row. Add SET @IdentityID = SCOPE_IDENTITY() to the INSERT. Don't use @@IDENTITY (SQLServer 7) because that's the last identity created- a trigger or concurrency could mean it's another table entirely.

Dataset Trivia

Filter and Sort
- DataTables have a .Select(sql) but it returns a DataRow array (no binding!)
- DataTable RowCollections has a Find, but ensure you have set .PrimaryKey= col
- DataViews have properties to .Sort (colname + ASC, DESC) and .RowFilter (sql)
- DataViews also have a .RowStateFilter (default is CurrentRows=Unchanged+Added+ModifiedCurrent; compare ModifiedCurrent with ModifiedOriginal)
DataAdaptor1.MissingSchemaAction= MissingSchemaAction.AddWithKey gets the schema as well (useful to work with primary keys-
eg DataView1.ApplyDefaultSort = true will sort by primary key (make sure DataView1.Sort= "")

Output parameters are only available AFTER the datareader is closed (msdn):

reader.Close(); //must be first
TextBox1.Text = com.Parameters["@myoutput"].Value.ToString()

SQLCommandBuilder generates update/insert/delete sql from your select. It only works for single table with a primary key. Just create it using the dataAdaptor:

SqlConnection cn = new SqlConnection(@"Server=localhost;Initial Catalog=Northwind;Integrated Security=True");

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = new SqlCommand("SELECT * FROM customers", cn);

SqlCommandBuilder cb = new SqlCommandBuilder(da);

cb.ConflictOption = ConflictOption.OverwriteChanges; //only PK

SqlCommand updateCommand = cb.GetUpdateCommand();

Debug.WriteLine(updateCommand.CommandText); //UPDATE [customers] SET [CustomerID] = @p1, [CompanyName] = @p2, ...

UPSERTs

Doing an insert or update in SQL Server (here the update is just a select to get the identity). The lock hints are important to avoid race problems: see this Sam Saffron post from 2007

private static int Upsert(string naturalKey)
{
    const string sql = @"
DECLARE @Id int = null;

IF EXISTS(
    SELECT *
    FROM TestTable WITH (UPDLOCK, SERIALIZABLE)
    WHERE [NaturalKey] = @NaturalKey)
        BEGIN
        SELECT @Id = [Id]
        FROM TestTable
        WHERE [NaturalKey] = @NaturalKey;
        END
ELSE
    BEGIN
        INSERT INTO TestTable
        ([NaturalKey])
        VALUES
            (@NaturalKey);
    SELECT @Id = CAST(SCOPE_IDENTITY() AS int);
    END
SELECT @Id AS Id";
    int id;

    using (var con = new SqlConnection(ConnectionString))
    {
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@NaturalKey", naturalKey);
            con.Open();
            id = (int)cmd.ExecuteScalar();
        }
    }
    return id;
}

From SQLServer 2008 you can use the MERGE command, but you still need the lock hints.

private static int Merge(string naturalKey)
{
    const string sql = @"
DECLARE @Id int = null;

MERGE INTO TestTable WITH ( HOLDLOCK, SERIALIZABLE ) AS target
    --this is the ...WHERE...
    USING (SELECT @NaturalKey) AS source (NaturalKey)
        ON (target.NaturalKey = source.NaturalKey)
    WHEN MATCHED THEN
        UPDATE SET [LastUpdated] = @LastUpdated
                   ,@Id = [Id] -- cleverly set the id here
    WHEN NOT MATCHED THEN
        INSERT ([NaturalKey], [LastUpdated] )
            VALUES (@NaturalKey, @LastUpdated);

--if id is not set in UPDATE, then grab scope identity
SET @Id = ISNULL(@Id, CAST(SCOPE_IDENTITY() AS int));

SELECT @Id AS Id";
    int id;

    using (var con = new SqlConnection(ConnectionString))
    {
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@NaturalKey", naturalKey);
            cmd.Parameters.AddWithValue("@LastUpdated", DateTime.UtcNow);
            con.Open();
            id = (int)cmd.ExecuteScalar();
        }
    }
    return id;
}