Exporting to Excel from SQL Server

--

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO


To use OPENROWSET command

1. The Excel file must exist. You can use a file as template.
2. Enable OPENROWSET command from SQL Server 2005 Surface Area Configuration
utility.

How to set the Deafult Button in Web Pages

How to set the Deafult Button in User Control

protected void Page_Load(object sender, EventArgs e)
{
Page.Form.DefaultButton = ButtonId.UniqueID;
}


How to set the Deafult Button in Web Form


form id="thisForm" runat="server" defaultbutton="ButtonId"

10 Tips to Improve your LINQ to SQL Application Performance

I summed up 10 important points for me that needs to be considered during tuning your LINQ to SQL’s data retrieval and data modifying process:

1 – Turn off ObjectTrackingEnabled Property of Data Context If Not Necessary

2 – Do NOT Dump All Your DB Objects into One Single DataContext

3 – Use CompiledQuery Wherever Needed

4 – Filter Data Down to What You Need Using DataLoadOptions.AssociateWith

5 – Turn Optimistic Concurrency Off Unless You Need It

6 – Constantly Monitor Queries Generated by the DataContext and Analyze the Data You Retrieve

7 – Avoid Unnecessary Attaches to Tables in the Context


8 – Be Careful of Entity Identity Management Overhead

9 – Retrieve Only the Number of Records You Need

10 – Don’t Misuse CompiledQuery

Stored Procedure Optional Parameters using LINQ to SQL

By default using the LINQ to SQL DataContext design surface (or the SQL Metal command line tool), all parameters are created for a Stored Procedure signature. This essentially means that when coding against the stored procedure method in code, NO PARAMETER argument is optional.

For example; Consider the following stored procedure signature -


CREATE PROCEDURE [dbo].[ChangingInputParameters]
(
@p1 int, /* mandatory */
@p2 int = 2, /* optional integer */
@p3 nvarchar(15), /* mandatory nvarchar */
@p4 nvarchar(25) output /* input/output nvarchar */
)


Even though @p2 has a default value specified and is optional. The LINQ to SQL tools create a C# signature that always expects a value to be passed in. Whilst the designer tools support only the full signature, LINQ to SQL supports the mapping of any method signature to the Stored Procedure call. You just need to define it yourself.

Steps to update the DataContext within VS 2008 -

1. From within Visual Studio 2008, drag the StoredProcedure onto the DataContext (LINQ to SQL Data Class) designer window. This creates the following function definition in the data context code behind file.



[Function(Name="dbo.ChangingInputParameters")]
public ISingleResult ChangingInputParameters(
[Parameter(DbType="Int")] System.Nullable p1,
[Parameter(DbType="Int")] System.Nullable p2,
[Parameter(DbType="NVarChar(15)")] string p3,
[Parameter(DbType="NVarChar(25)")] ref string p4)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p1, p2, p3, p4);
p4 = ((string)(result.GetParameterValue(3)));
return ((ISingleResult)(result.ReturnValue));
}


2. Create a new partial class for the data context (TIP: Cut and past the using clause, down to the DataContext class definition; Remove the attribute from the class and fix the curly-parenthesis). You should end up with a class looking like this -



namespace [... will vary ...]
{
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
using System.ComponentModel;
using System;

public partial class ChangingInputParametersDataContext : System.Data.Linq.DataContext
{
}


3. Copy the original function definition in the code-behind file (e.g. ChangingInputParameters.designer.cs) to your new partial class file.


4. Remove the input arguments, including the in-line attribute declaring each argument as a Parameter.


5. Return output parameters (those marked INPUT/OUTPUT in SQL, and those marked with a 'ref' prefix in C# are accessed via their index position in this method declaration. Update 'x' in any lines getting the output parameter results, so that the correct parameter is mapped to the correct C# instance variable.


result.GetParameterValue(x)


You should have a method that looks like this -



// Modifying the parameter list by dropping the arguments from this list, and updating any OUT parameters...
[Function(Name = "dbo.ChangingInputParameters")]
public ISingleResult ChangingInputParameters(
[Parameter(DbType = "Int")] System.Nullable p1, // NOTICE: NO P2 Declaration! It is optional in the Stored Proc
[Parameter(DbType = "NVarChar(15)")] string p3,
[Parameter(DbType = "NVarChar(25)")] ref string p4)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p1, p3, p4);
p4 = ((string)(result.GetParameterValue(2))); // NOTICE: We had to update the index position because the methods arguments changed.
return ((ISingleResult)(result.ReturnValue));
}

COALESCE function instead of long CASE WHEN ... ELSE (T-SQL)

Instead of using long "SELECT ... CASE WHEN ... ELSE ..." construction, you can use the COALESCE function when you need to find a value that is not NULL. Lets review the following T-SQL expression, in which we need to select an available "source":

SELECT TheSource =
CASE
WHEN localSource IS NOT NULL THEN localSource
WHEN intranetSource IS NOT NULL THEN intranetSource
WHEN internetSource IS NOT NULL THEN internetSource
ELSE ''
END
FROM ...

Now lets rewrite the code above using COALESCE function:

SELECT TheSource =
COALESCE(localSource, intranetSource, internetSource, '')
FROM ...