Removing Unwanted Text from Tab-delimited Files using Regex

Use something like this: Regex.Replace("Tab data here....", "\\t|\\r|\\n|\\v|\\f|\\e|\\x20|\\040\\cC\\u0020", "")

Settings DataSet/TableAdapter Connection Strings at Runtime

The DataSet's TableAdapter generates strongly types classes in VS.NET, with properties to connection strings that are read-only at runtime, unless, of course, you choose the "User" scope. Follow these steps for changing the connection string upon application startup:

1. Open up the Settings.settings file, whatever it might be named in your project. If it's not there, create one by a) adding a new Settings file to your project, b) adding a new (Connection string) setting, c) save it, d) then ensure that each DataSet's TableAdapter's Connection property references this Settings file's (Connection string) setting.
2. Press the F7 key to view its code.
3. Add the following code to the Settings() constructor: this.SettingsLoaded += new System.Configuration.SettingsLoadedEventHandler(Settings_SettingsLoaded);
4. Add the following method to the Settings class: private void Settings_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e) { try { Settings.Default["PrizeWinsConnectionString"] = "PUT YOUR CONNECTION STRING HERE!"; } catch (System.Exception err) { throw; } }
5. Save and recompile your project/solution.

Removing decimal places in VS.NET 2008's Crystal Reports

There are times where the business requires numbers with decimal places to have those decimal places removed if the decimal value is zero. For example, it's better to display 707 than 707.00, but if it's 707.55, it still stays at 707.55. Here's how you can tell Crystal Reports to NOT use decimal places in this case.

1. Open up the Crystal Report.
2. Right-click on the decimal value field, then click on Format Object.
3. Select the Number tab.
4. Choose Custom Style, then click on the Customize button.
5. Click on the X*2 button next to the Decimals drop-down box.
6. Change it from Crystal Syntax to Basic Syntax.
7. Enter something akin to the following code:If ({TABLE.DECIMALFIELD} - Round({TABLE.DECIMALFIELD}, 0)) = 0.0 Then formula = 0 Else formula = 1 End IfWhere 0 and 1 are the number of decimal places.
8. Click on the Save and Close button.
9. Click on OK twice, then save the report.

SQL Server 2005's EXCEPT & INTERSECT Union-like Operators

SELECT name FROM master..sysobjects where name = 'all_columns' UNION SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns routes SELECT name FROM master..sysobjects where name = 'all_columns' EXCEPT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'all_columns'; Results:name ----------- all_columns

SQL Server 2005's Ranking Functions

You can create columns that provide unique ranking/sorting capabilities. For example, ROW_NUMBER() (produces unique row numbers):

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM sysobjects; The preceding query produces the following results:ROWNUM name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 CoOp 2 Country 3 DF__dtpropert__versi__77BFCB91 4 DF_Carriage_CarriageTypeName 5 DF_CoOp_CoOpName 6 DF_Country_CountryName 7 DF_ExceptionLog_Occurred 8 DF_History_ActionDateTime 9 DF_History_ActionTakenByUserID 10 DF_History_Comments 11 DF_History_ETA 12 DF_History_LastNoticeStatusID ..... Or, there's RANK() (produces non-unique sorting numbers with possible gaps/skips in the sequence)...SELECT RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That query produces this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 3 @11data 3 @11data 5 @12data 5 @12data 7 @13data 7 @13data 9 @14data 9 @14data 11 @15data 11 @15data 13 @16data 13 @16data ..... Or, there's DENSE_RANK() (produces non-unique sorting numbers with NO gaps/skips in the sequence)...SELECT DENSE_RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That will produce this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 2 @11data 2 @11data 3 @12data 3 @12data 4 @13data 4 @13data 5 @14data 5 @14data 6 @15data 6 @15data 7 @16data 7 @16data ...... Then there's the interesting NTILE(n) function. This will produce partitioned/divided result sets with equal sequenced numbers, like this:WITH SystemObjects AS ( SELECT TOP 10 name FROM master..sysobjects ) SELECT NTILE(5) OVER (ORDER BY name) AS 'NTILE(5)', name from SystemObjects; That query produces the following output, with the numbers 1 through 5 having a set of 2 for each divided/partitioned section:NTILE(5) name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 all_columns 1 all_objects 2 all_parameters 2 all_sql_modules 3 all_views 3 allocation_units 4 assemblies 4 assembly_files 5 assembly_modules 5 assembly_references

Creating Pivoted Joins in SQL Server 2005

You can take a vertical result set and turn it into a horizontal one, like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT DISTINCT tmp.Make, [380i], [M5], [M3] FROM @TBLTEMP tmp INNER JOIN (SELECT Make, Model FROM @TBLTEMP) PivotTable PIVOT ( COUNT(Model) FOR Model IN ([380i],[M5],[M3]) ) PivotColumns ON tmp.Make = PivotColumns.Make;

The preceding query takes a count of how many licenses belong to which BMW models, and displays them horizontally. The output should be something like this:

Make 380i M5 M3 -------------------------------- ----------- ----------- ----------- BMW 2 1 2

If were were to utilize a simple select statement, it would look something like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT Make, Model, COUNT(*) AS [Count] FROM @TBLTEMP GROUP BY Make, Model; The results being this: Make Model Count -------------------------------- ------------ ----------- BMW 380i 2 BMW M3 2 BMW M5 1

Random Data Sampling Queries in SQL Server 2005

The following query will return a random sampling of data:

SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (10 PERCENT); SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (200 ROWS); Not only will the records returned be random, but so will the number of rows.

New Join Types in SQL Server 2005

CROSS APPLY (equivalent to an inner join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so CROSS APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get no null ColumnName fields because this is equivalent to an inner join, so only objects with matching columns or parameters, in the case of SPROCs and functions, will be returned.

OUTER APPLY (equivalent to a left join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so OUTER APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get any type of object, even non-tables, non-SPROCs, etc, will have their ColumnName fields as null, as this is equivalent to a left join.

Note that any object with no parameter or column will not have an any records in the syscolumns table (eg, SPROCs with no parameters).

Using Common Table Expressions in SQL Server 2005

Pretty cool stuff:

-- Common Table Expressions example: WITH tblDBs AS ( SELECT COUNT(*) AS [Number of Databases] FROM sysdatabases ), tblTables AS ( SELECT COUNT(*) AS [Number of Tables] FROM sysobjects WHERE xtype = 'U' ), tblColumns AS ( SELECT COUNT(*) AS [Number of Columns] FROM syscolumns ) SELECT * FROM tblDBs db, tblTables, tblColumns;

Results from my DB:

Number of Databases Number of Tables Number of Columns ------------------- ---------------- ----------------- 23 6 11258 (1 row(s) affected)

ASP.NET MVC - Validation of viewstate MAC failed.

Yup, View State is not supported in MVC, so beware of this. If you try to use a control that relies on it, you'll get the following error:

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

Note that you can still use ViewState with ASPX/Web Forms as Views in MVC, but that violates the MVC pattern.