static void

ADO.Net

• ASP DataBinding notes
• ADO examples
• Read Excel into DataTables
• Convert List<T> to DataTable and back again
• EntityFiller: ADO DataReader to entities with reflection

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)

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.

Transactions

• For high-load read-write tables, you need transactions on the selects (otherwise they become deadlock victims). Use
  • SELECT * FROM Users WITH (NOLOCK)
  • In sql SET TRANSACTION LEVEL READ UNCOMMITTED;
  • A TransactionScope with IsolationLevel.ReadUncommitted
• If using .Net transactions: check overlap with TSQL BEGIN/END TRANSACTION
• Manual Transactions: SqlConnection.BeginTransaction returns a transaction object which must be attached to the commands. Change Transaction.IsolationLevel when writing data.
• 1.x Automatic Transactions: Uses COM+ DTC. Can use distributed databases. Classes must inherit from System.EnterpriseServices.ServicedComponent and add [Transaction] attributes with the options (TransactionOption, IsolationLevel etc).
  • ContextUtil.SetComplete and .SetAbort static methods (you can .SetAbort at the start, then .SetComplete only when done). Or use [AutoComplete] attribute on methods (but if you catch errors it won't detect the abort).
• 2.0 System.Transactions: wrap ADO in using (TransactionScope tscope = new TransactionScope()) and at the end tscope.Complete= true (an exception leaves it false when it disposes, so aborts).
  As soon as a second transaction enlists, it is promoted to a distributed transaction under DTC
  • Default = new TransactionScope(TransactionScopeOption.Required) (if a higher transaction, joins it)
  • RequiresNew for new transaction that doesn't affect higher level transactions
  • Suppress for non-transactional bits (logging)
  • Set a timeout TimeSpan if could deadlock (or to test failure)
  • Default = TransactionOptions =IsolationLevel.Serializable.
  • You can explicitly create an ICommittableTransaction t= Transaction.Create(), call connection.EnlistTransaction, then t.Commit()

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, ...