ITDevSpace - Development Tips & Tricks

SQL Server Isolation Levels By Example

-- SOURCE TABLE DECLARE @ Table 1 TABLE ( AsOf DATETIME , Company VARCHAR (50), Name VARCHAR (50) ) INSERT INTO @ Table 1 SELECT ' 20130101 ',' Company1 ',' John ' UNION SELECT ' 20130102 ',' Company2 ',' Tom ' UNION SELECT ' 20130101 ',' Company3 ',' Peter ' UNION SELECT ' 20130102 ',' Company4 ',' Ian ' -- DESTINATION TABLE DECLARE @ Table 2 TABLE ( AsOf DATETIME , Company VARCHAR (50), Name VARCHAR (50) ) -- BATCH TABLE DECLARE @Batches TABLE ( AsOf DATETIME ) INSERT INTO @Batches SELECT DISTINCT AsOf FROM @ Table 1 -- COPY IN BATCHES DECLARE @AsOf DATETIME WHILE ( Exists ( SELECT 1 FROM @Batches)) BEGIN TRY BEGIN TRAN SELECT @AsOf = MIN (AsOf) FROM @Batches PRINT CONVERT ( VARCHAR (20),GETDATE(),20) + ' , ' + ' Copying data for: '+ convert ( VARCHAR (8),@AsOf,112) INSERT INTO @ Table 2 ( ...

[Test] public void FindAndReplace() { var sql = @" SELECT * FROM Table_{@AsOf} WHERE Date='{@AsOf-1}' "; Console.WriteLine(" BEFORE: {0} ", sql); var asOf = DateTime.Today; //(?: )+ means repeat the inner groups var regexExpr = @" (?:{@(?<parameter>\w+)(?<operation>\S)?(?<number>\d+)?})+ "; var regex = new Regex(regexExpr); foreach (Match match in regex.Matches(sql)) { sql = sql.Replace(match.Value, Substitute(match.Value, asOf)); } Console.WriteLine(" AFTER : {0} ", sql); } private string Substitute( string parameterText, DateTime asOf) { var regexExpr = @" {@(?<parameter>\w+)(?<operation>\S)?(?<number>\d+)?} "; var regex = new Regex(regexExpr); var match = regex.Match(parameterText); if (match.Success) { var parameter = match.Groups[" parameter "].Value; if (match.Groups[" operation "].Success &...

PRINT ' CREATE TABLE ' GO IF EXISTS ( SELECT * FROM dbo.sysobjects where id = object_id(N' [Table1] ') and OBJECTPROPERTY(id, N' IsUserTable ') = 1) DROP TABLE [ Table 1] GO CREATE TABLE [dbo].[ Table 1]( [Id] [ uniqueidentifier ] NOT NULL , [Name] [ varchar ](50) NULL , [DateUpdated] [ datetime ] NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Id] ) ) GO GRANT SELECT , INSERT , UPDATE , DELETE ON [ Table 1] TO [SampleUser] GO PRINT ' CREATE STORED PROCEDURE ' GO IF EXISTS ( SELECT * FROM dbo.sysobjects where id = object_id(N' [StoredProcedure1] ') and OBJECTPROPERTY(id, N' IsProcedure ') = 1) DROP PROCEDURE [dbo].[StoredProcedure1] GO CREATE PROCEDURE [dbo].[StoredProcedure1] AS SET NOCOUNT ON UPDATE dbo. Table 1 SET [DateUpdated]=GETDATE() GO GRANT EXECUTE ON [StoredProcedure1] TO [SampleUser] GO PRINT ' CREATE VIEW ' GO ...

This is useful if you want to select a lot of data by key and writing IN statement is not efficient (too much data etc..). You load the keys to temp table and then select data using a join. CREATE TABLE [dbo].[Table_1]( [Id] [ int ] NOT NULL , [Name] [ varchar ](50) NULL ) [TestFixture] public class TestSelectWithTempTableJoin { [Test] public void Test() { const string nbTempCreate = @" CREATE TABLE #Ids( Id INT ) "; const string nbTempDrop = @" DROP TABLE #Ids "; const string query = @" SELECT * FROM Table_1 t JOIN #Ids temp ON t.Id = temp.Id "; var ids = new List< int >( new int []{1,3,4}); var rows = new List<KeyValuePair< int , string >>(); var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(); sqlConnectionStringBuilder.DataSource = " (local) "; sqlConnectionStringBuilder.InitialCatalog = ...

* Windows Authentication " Persist Security Info=False; Integrated Security=SSPI; Server={0}; Database={1}; Connect Timeout={2}; Application Name={3} " * SQL Server Authentication " Persist Security Info=False; Server={0}; Database={1}; Uid={2}; Pwd={3}; Connect Timeout={4}; Application Name={5} "

This is type safe version of ExecuteQuery method using anonymous object as a template public static class DataContextExtensions { public static IEnumerable<T> ExecuteQuery<T>( this DataContext ctx, string query, T template, DbParameter[] parameters = null ) where T : class { using (DbCommand cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = query; if (parameters != null ) cmd.Parameters.AddRange(parameters); ctx.Connection.Open(); using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (rdr.Read()) { object [] values = new object [rdr.FieldCount]; for ( int i = 0; i < rdr.FieldCount; i++) { if (!rdr.IsDBNull(i)) { if (rdr.GetFieldType(i) == typeof (Decimal)) //special case if you need it? { values[i] = Convert.ToDouble(rdr.GetDecimal(i)); } ...