How to debug in SQL 2008

I know there are some nice new features in SQL 2008, a few days ago, finally I got it all tried out and succeeded.

1. Set breakpoint in your Stored Procedure

This feature is only available in SQL 2008, not SQL 2005, and you need Debugger permission to do so. You can conveniently click the left side grey column to set the breakpoint in your stored procedure.

Then, open a new query window, write a statement to execute your stored procedure, provide parameters if necessary.

exec dbo.yourSpName @para1='value1', @para2='value2' .....

highlight the above statement, and click the debug icon next to Execute command at the toolbar, press F11 to step into the body of your stored procedure, also F10 to run the statement, you can check the values of all the variables in the context.

2. Add try-catch block

This is also new in SQL 2008, sample code is as following:

Log the error messages in your logTable, so you can examine them later to see what happened.

declare @ErrorMessage varchar(MAX) begin try            <your sql statements here ....> end try begin catch   select @ErrorMessage = ERROR_MESSAGE()   EXEC [dbo].[log_Error]        @process_name = 'your process name',               @user_message = 'Error occurred …',        @system_message = @ErrorMessage end catch

Auto Increment Number in a single UPDATE statement

SQL Server 2005/2008

Suppose in a table, you want to increase the value of a Field (not identity field), you can write a SQL statement as following:

Update yourTable  set  yourField = max(yourField)+1  where .....

But, if the WHERE clause returns more than one records, then what you get is, for that more than one records, all the yourField will be update with the same value. If you want the yourField all has different value, you can write something like this:

declare @max int

select @max= MAX(yourField)  from yourTable

update yourTable set @max=yourField=@max+1 where ........

Inheritance and Polymorphism tips

The following example is demonstrated in C#

I have classB inherites from classA as following:

class Program     {         static void Main(string[] args)         {             classB b = new classB();             classA a = b as classA;             a.DoSomething();             b.DoSomething();             classB b2 = a as classB;             b2.DoSomethingElse();         }         public class classA         {             protected string s = "hello from classA";             public classA()             {                 s = "hello from classA";             }             public void DoSomething()             {                 Console.WriteLine(s);             }         }         public class classB : classA         {             protected string d = "something else";             public classB()             {                 s = "hello from classB";             }             public void DoSomethingElse()             {                 Console.WriteLine(d);             }         }     }

The derived class - classB gains all the non-private data and behavior of the base class (classA) in addition to any other data or behaviors it defines for itself. classB is effectively both classB and classA (polymorphism).

When the program is run, both a.DoSomething() and b.DoSomething() output "hello from classB" to the Console, because when creating classB object, the Constructor of classA will be called before the Constructor of classB is called, so the value of s is "hello from classB".

When classB is cast to classA, classB is not changed by the cast, but the view of classB becomes restricted to classA's data and behavior. After casting classB to classA, that classA can be cast back to classB without losing the data. So when b2.DoSomethingElse() is invoked, it will output "something else"

Create InfoPath 2007 form and submit to SharePoint 2007

You should have InfoPath 2007 and SharePoint 2007 Standard or Enterprise installed and set up properly

1. Create an InfoPath Form Library in SharePoint 2007

Navigate to your top-level site collection, and then

Site Action | Create | Library | Form Library, name it as "InfoPathFormLibrary", note that

Document Template is Microsoft Office InfoPath form

2. InfoPathFormLibrary Settings | Form Library Settings | Advanced settings | Browser-enabled Documents | Display as a web page | OK

3. Enable InfoPath browser form features in SharePoint 2007

Site Actions | Site Settings | Site Collection Administration | Site Collection features

For SharePoint Enterprise edition, activate Office SharePoint Server Enterprise Site Collection features (Features include business data catalog, forms services, and Excel Services)

For SharePoint Standard edition, activate InfoPath Forms Services support

4. Configure InfoPath Forms Services

Central Administration | Application Management | InfoPath Forms Services | Configure InfoPath Forms Services

In the section of [User Browser-enabled Form Templates], select both Allow users to browser-enabled form templates and Render form  templates that are browser-enabled by users, and click OK to save it.

5. Create a browser-compatible form template in InfoPath and publish to SharePoint 2007

Design a blank Form Template in InfoPath, and enable browser-compatible features

Layout | Table with title

Change the title to Customers, add a 2x2 table under Customers, it has two labels at the left column: Name and Address.  Two TextBox at the right column.

6. Double click Name TextBox, and set Field name as name

Double click Address TextBox, and set Field name as address

7. Set submit options

Tools | Submit Options

Click Add to create data connection for submit. Follow the Data Connection Wizard.

my Document libary is: http://vm2/InfoPathFormLibrary

File name is the result of a function: concat("form",now()), you can click Fx button to build the function

Choose Allow overwrite if file exists

When you have completed the Data connection wizard and returned to the Submit Options window, click Advanced button, and choose Close the form after submit.

8. Set Form Options

Tools | Form Options...

Uncheck Show toolbar at bottom of from and click OK to save

9. Save this template

Click Save icon, name it as CustomerFormTemplate.xsn and save it in My Documents folder

10. Publish the templage

File | Publish, it invokes Publishing Wizard

On the following screen, do not click Add, click Next to go to Next step

Click Publish on the following screen

Close the wizard when success

11. Test the InfoPath form

Close InfoPath 2007.

Return to your site collection, click InfoPathFormLibrary at the quick launch.

New | New Document

you will see the InfoPath form in your browser

Submit the form when you're done.

12. You should be able to see it in the InfoPathFormLibrary

Create and publish Infopath Form to SharePoint 2010

Environment:

SharePoint 2010

InfoPath 2010

1. Navigate to the top level site and create a Custom List

Click 'Lists' from quick launch, then 'Create', name it as 'Customers'

and then go to List Tools | List and create three columns as 'Contact Name', 'Email' and 'Phone Number', make all of the fields 'Single line of text'

2. Open InfoPath Designer 2010, double-click the SharePoint List template. In the Data Connection Wizard, enter the URL of your top level site. For example, the URL I am using is:

http://win-4me5lbbsmh5/SitePages/Home.aspx

If the URL provide is not top level site, my experience is that InfoPath cannot establish the connection.

3. Click 'Next', choose 'Customize an existing SharePoint list', select 'Customers' and then click 'Next'

4. When finish, Infopath generates a form that maps to the fields of Customers list, you can do some customization and preview the changes you made, and then publish it to SharePoint by click the Quick Publish button (next to the Save icon at the top left of InfoPath)

5. To use the form in SharePoint, navigate to your sharepoint site and choose 'Edit Page' from 'Site Actions'

6. The Infopath Form will be display on the sharepoint page as a webpart. Click on the page at where you want the webpart being rendered. Then click Editing Tools | Insert | Web Part, select Forms in the Categories, InfoPath Form Web Part, and then click Add button to add the InfoPath Form webpart.

7. Select a Form. Click here to open the tool pane. In the 'List or Library' dropdown, select 'Customers' and click OK, you will see the InfoPath form on the page.

8. To use the InfoPath Form, click the Forms Edit Tab, fill out the form and click Save.

9. To see the data you just entered and saved. Go to Home, click Customers from quick launch.

CRUD with LINQ

Database: Northwind

Visual Studio 2010

Setup:

1. Create a new Web or WinForm Application

2. Create a new Data Connection in Server Explorer to connect to Northwind database

3. Add LINQ to SQL Classes to your project

4. Drag all the Northwind tables to the designer surface left panel, drag stored procedures CustOrderHist, CustOrdersDetails, CustOrdersOrders to the designer surface right panel. Actually I did not use all of those in the application.

5. Create a new stored procedure in the Northwind, name it as UpdateCustomer. Afterwards, I'll show you how to use LINQ to call this SP to update database and get the returned value. The SP is as following:

ALTER PROCEDURE [dbo].[UpdateCustomer]          @CustomerID nchar(5),       @CompanyName nvarchar(40) AS BEGIN  SET NOCOUNT ON;  update dbo.Customers set CompanyName = @CompanyName where CustomerID = @CustomerID  return 1 -- for demo purpose END

The SP updates the CompanyName of the Customers table based on the CustomerID, it takes  two parameters: CustomerID and CompanyName. And always return value 1 for demo purpose here.

6. Refresh the database in Server Explorer and drag UpdateCustomer SP to the right panel of the Northwind.dbml designer surface.

7. Build the application, make sure it succeeded. The application looks like the following:

8. LINQ has generated the code to model the database, and we can start to enjoy the intellisense and strongly typed database object. The following is the code snippet:

Read Products with category name is "Beverages"

NorthwindDataContext db = new NorthwindDataContext(); var products = from p in db.Products where                    p.Category.CategoryName=="Beverages"                select p; foreach (Product p in products) {    lblResult.Text = lblResult.Text + p.ProductName + " | "; }

Update Products table

NorthwindDataContext db = new NorthwindDataContext(); Product product = db.Products.Single(p => p.ProductName == "Chai"); product.UnitPrice = 99; product.UnitsInStock = 5; db.SubmitChanges();

Insert Records

NorthwindDataContext db = new NorthwindDataContext(); Category cat = new Category(); cat.CategoryName = "TOY"; Product p1 = new Product(); p1.ProductName = "toy 1"; Product p2 = new Product(); p2.ProductName = "toy 2"; cat.Products.Add(p1); cat.Products.Add(p2); db.Categories.InsertOnSubmit(cat); db.SubmitChanges();

Delete

NorthwindDataContext db = new NorthwindDataContext(); var toyProd = from p in db.Products               where p.ProductName.Contains("toy")               select p; db.Products.DeleteAllOnSubmit(toyProd); db.SubmitChanges();

Run SP, return a collection

NorthwindDataContext db = new NorthwindDataContext(); var orderDetail = db.CustOrdersDetail(10248); foreach (CustOrdersDetailResult re in orderDetail) {     Console.WriteLine(re.ProductName); }

Note: When you use the object of CustOrdersDetailResult, you can see the intellisense and can access every Field the Stored Procedure CustOrdersDetailResult returns, that is ProductName, UnitPrice, Quantity, Discount and ExtendedPrice in this case. This SP is to return the order details based on the OrderID, which is passed as a parameter.

Run SP, Update table and return a value

NorthwindDataContext db = new NorthwindDataContext(); var result = db.UpdateCustomer("AAAAA", "BMW"); Console.WriteLine(result.ToString());

Note: UpdateCustomer is the new SP created at step 5. It sets the CompanyName to "BMW" for the CustomerID "AAAAA", Passing the parameter is like to call a regular method, and we do not need SubmitChanges here. In the SP, @CustomerID declared as nchar(5), @CompanyName declared as nvarchar(40), but in the intellisense, I see the signature changed to db.UpdateCustomer(string customerID, string companyName), need to watch not to exceed the width.

Server side Pagination

NorthwindDataContext db = new NorthwindDataContext(); var products = (from p in db.Products                 where p.Category.CategoryName.StartsWith("C")                 select p).Skip(200).Take(10);

Pagination made easy, only returns the collection we are looking for.

9. Save, compile and run the application. The above code has been tested.

Tips of using Trim

Trim() can remove line breaks and space in the same time. For example:

string s = "hello world.\r\n\r\n";

string ret = s.Trim();

Console.WriteLine(ret);

result: "hello world.”  //line breaks removed

string s = "hello world.  \r\n\r\n";

string ret = s.Trim();

Console.WriteLine(ret);

result: "hello world.” //line breaks and trailing space being removed by one Trim()

Sometimes I have my message concatenated in a string:

msg = msg + "; custom message 1";

...

msg = msg + "; custom message 2";

...

msg = msg + "; custom message 3";

In the end, I want to remove the '; ' from the beginning of msg to look better, I can do the following:

string ret = msg.Trim(new char[]{';',' '});

The beauty is: even both custom message 1 and custom message 2 are empty, the msg will look like:

"; ; custom message 3"

After single trimming, you can still get "custom message 3", and you don't have to check if the msg is empty every time.

Host a WCF Service in IIS

When a WCF service runs in IIS, it can take full advantage of IIS features such as process recycling, idle shutdown, process health monitoring, and message-based activation, it will automatically launch the host process when it gets the first client request. This hosting option requires that IIS be properly configured, but you do not need to write hosting code in your application. The disadvantage is that it only supports HTTP.

Development Environment: IIS 6.0, VS2010, C#

1. Create a new web site, select "WCF Service" as Template

Choose location as HTTP, http://localhost/MyCalculatorService and click OK Button

2. Open Service.cs, double click Service to select, and then use "Refactor | Rename" to rename it to MyCalculatorService, rename IService to IMyCalculatorService in the same way. Rename file service.svc to MyCalculatorService.svc, Service.cs to MyCalculatorService.cs, IService.cs to IMyCalculatorService.cs

3. Open IMyCalculatorService.cs, add

public interface IMyCalculatorService {     [OperationContract]     string GetData(int value);     [OperationContract]     CompositeType GetDataUsingDataContract(CompositeType composite);     [OperationContract]     double Add(double n1, double n2);     [OperationContract]     double Subtract(double n1, double n2);     [OperationContract]     double Multiply(double n1, double n2);     [OperationContract]     double Divide(double n1, double n2); }

4. Open MyCalculatorService.cs, add

public double Add(double n1, double n2)     {         return n1 + n2;     }     public double Subtract(double n1, double n2)     {         return n1 - n2;     }     public double Multiply(double n1, double n2)     {         return n1 * n2;     }     public double Divide(double n1, double n2)     {         return n1 / n2;     }

5. Open MyCalculatorService.svc, make sure the name matches the changed name:

<%@ ServiceHost Language="C#" Debug="true" Service="MyCalculatorService" CodeBehind="~/App_Code/MyCalculatorService.cs" %>

6. Open Web.config, modify

<?xml version="1.0"?> <configuration>   <system.web>     <compilation debug="false" targetFramework="4.0" />   </system.web>   <system.serviceModel>     <services>       <service behaviorConfiguration="ServiceBehavior" name="MyCalculatorService">         <endpoint address="http://localhost/MyCalculatorService/MyCalculatorService.svc" binding="wsHttpBinding" contract="IMyCalculatorService">           <identity>             <dns value="localhost"/>           </identity>         </endpoint>         <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>       </service>     </services>     <behaviors>       <serviceBehaviors>         <behavior name="ServiceBehavior">           <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->           <serviceMetadata httpGetEnabled="true"/>           <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->           <serviceDebug includeExceptionDetailInFaults="false"/>         </behavior>       </serviceBehaviors>     </behaviors>       </system.serviceModel>   <system.webServer>     <modules runAllManagedModulesForAllRequests="true"/>   </system.webServer>   </configuration>

I take out <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />

from the web.config, because it causes an error as following:

When 'system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled' is set to true in configuration, the endpoints are required to specify a relative address. If you are specifying a relative listen URI on the endpoint, then the address can be absolute. To fix this problem, specify a relative uri for endpoint 'http://localhost/MyCalculatorService/MyCalculatorService.svc'.

7. Test in IE

Copy and paste 'http://localhost/MyCalculatorService/MyCalculatorService.svc' to IE, you should see something like the following, which means the service is ready to be consumed.

You can also test it using WcfTestClient tool

8. Create a Console application ServiceClient in the same solution or separate solution. Add Reference System.ServiceModel and System.Runtime.Serialization

9. Generate proxy files.

Open Visual Studio command prompt and nagivate to the ServiceClient folder, the same folder as Program.cs

10. Run

svcutil http://localhost/MyCalculatorService/MyCalculatorService.svc

Two files will be generated:

MyCalculatorService.cs and output.config

11. Rename output.config to app.config and include it in the application, also include MyCalculatorService.cs into the application. In the Main method of Program.cs, add

MyCalculatorServiceClient client = new MyCalculatorServiceClient(); Console.WriteLine("calling the service..."); Console.WriteLine("112.78+45.20=" + client.Add(112.78, 45.20).ToString()); Console.Read();

12. Ctrl+F5 to run the Console application, you should see:

13. Yeahhh!!! you have done it, take a break.