Category Archives: .NET

Everything About .NET

.Net Debugging – Conditional Break Points

Often developers need to setup breakpoints for debugging. There are times we need to stop execution under certain condition. I used to add logic in my code using following logic

if (i == 1)

{

    //break here

    i= i;

}

This works well for debugging, however, it’s a pain to keep my code clean. And, sometimes I forget to clean them!

A few years back, I learned Visual Studio had build-in functions for this type of situation call conditional breakpoints. I have showed to a few coworkers how to use it and now I want to blog it for whoever might see it. It’s a very simple technique yet power in debugging.

Below, I’m showing this in VS2008:

To start, make sure Breakpoint window is open in Visual Studio, if not, press Ctrl. D,B to open it.

In source code, press F9 to setup break point at the line. This break point will show in ‘Breakpoints’ window, right click on this breakpoint, there will be a pop-up menu like this:


Select ‘Condition’, conditional break point setup window will appear. All we need to do is to enter the condition

After setup is done, run the code and it will break when condition is met: 

That’s all.

There is another way to break: hit count, you can enter a number and Visual Studio will stop code when this line of code has been hit for xx many times:


 See how easy it is?

Breakpoint doesn’t change much in Visual Studio 2010:


VS2012 just came out, I haven’t got chance to check it out. Will update once I get chance.

SQL Server 2008: SQL Server web service using Service Broker

Important: SQL Server HTTP web service has been removed from SQL Server 2012. Existing implementation will need to recreate web service using other tools like ASP.Net.

Besides using ASP.Net to create a web service and pull out data, we can also take advantage of SQL Server Service Broker to create a web service within SQL Server and expose a stored procedure.

Let’s begin with a test database:

–Setup Test base

create database MyTestDB

go

use MyTestDB

go

create table Test

(Number int)

go

insert into Test

values (3)

go

select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’

go

alter database MyTestDB set ENABLE_BROKER with ROLLBACK IMMEDIATE

go

–Change database owner since it was created under my windows ID

sp_changedbowner ‘sa’

go

OK. Let’s go ahead and create our stored procedure which returns expected data:

create proc NumberService

as

(

select Number from Test

)

Next step is to create end point and expose stored procedure:

–WebService

create endpoint WebServiceEndPoint

state = started

as http

(

    path = ‘/NumberService’,

    authentication = (integrated),

    ports = (clear),

    site = ‘*’

)

for soap

(

    webmethod ‘GetNumberService’

    (

        name = ‘MyTestDb.dbo.NumberService’

    ),

    wsdl = default,

    schema = standard,

    database = ‘MyTestDB’,

    namespace = ‘http://www.test.com’

)

go

Server side work is done, let do a quick test: Open Internet Explorer and enter ‘http://<computer_NetBIOS name>/NumberService?WSDL’, you should be able to see:


Great, we got web service up and running. Let’s create a quick client to consume this service.

In VS2008, create a new console project, add a web reference to the new created web service. Then enter following code:

using System;

using System.Data;

namespace SQLWebService

{

    class Program

    {

        static void Main(string[] args)

        {

            ServiceReference1.WebServiceEndPointSoapClient client = new SQLWebService.ServiceReference1.WebServiceEndPointSoapClient();

            //client.Endpoint.b

            object[] numbers = client.GetNumberService();

            foreach (DataRow num in ((DataSet)numbers[0]).Tables[0].Rows)

            {

                Console.WriteLine(num[0].ToString());

            }

        }

    }

}

Before running this client, go to app.config and update security setting. We had setup Windows authentication on server side and also need to do the same on client side:

Open App.config, locate Security section, update security mode to TransportationCredentialOnly and clientCredentialType to Windows:

                    <security mode=”TransportCredentialOnly”>

                        <transport clientCredentialType=”Windows” proxyCredentialType=”None”

                            realm=”” />

                        <message clientCredentialType=”UserName” algorithmSuite=”Default” />

                    </security>

Run this app and we get the web service consumed:


Data change notification using Service Broker

A quick demo of using query notification to enable application to present data change:

Let’s start with a test database:

–Setup Test base

create database MyTestDB

go

use MyTestDB

go

create table Test

(Number int)

go

insert into Test

values (1)

go

select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’

go

alter database MyTestDB set ENABLE_BROKER with ROLLBACK IMMEDIATE

go

–Change database owner since it was created under my windows ID

sp_changedbowner ‘sa’

go

Here we enable Service Broker and Query Notification to all application to subscribe data changes. Now we need to create an application to be able to see the changes.

The application we are going to build has 2 controls: a lable (lblText) and a button (btnStart).

lblText.Text is set to 0 to start with, here is the source code:

using System;

using System.ComponentModel;

using System.Data;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace QueryNotification

{

    public partial class Form1 : Form

    {

        const string SQL_COMMAND = “SELECT NUMBER FROM TEST”;

        const string CONNECT_STRING = “Data Source=localhost;Initial Catalog=MyTestDB;Integrated Security=SSPI;”;

        private SqlConnection con;

        private SqlCommand cmd;

        private DataSet data;

        public Form1()

        {

            InitializeComponent();

        }

        private void btnStart_Click(object sender, EventArgs e)

        {

            SqlDependency.Stop(CONNECT_STRING);

            SqlDependency.Start(CONNECT_STRING);

            if (con == null) con = new SqlConnection(CONNECT_STRING);

            if (cmd == null) cmd = new SqlCommand(SQL_COMMAND,con);

            if (data == null) data = new DataSet();

            GetData();

        }

        private void GetData()

        {

             data.Clear();

            cmd.Notification = null;

            SqlDependency dependency = new SqlDependency(cmd);

            dependency.OnChange += new OnChangeEventHandler(number_OnChange);

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))

            {

                adapter.Fill(data);

                lblText.Text = data.Tables[0].Rows[0][0].ToString();

            }

        }

        private void number_OnChange(object sender, SqlNotificationEventArgs e)

        {

            ISynchronizeInvoke i = (ISynchronizeInvoke)this;

            if (i.InvokeRequired)

            {

                OnChangeEventHandler deleg = new OnChangeEventHandler(number_OnChange);

                object[] args= {sender, e};

                i.BeginInvoke(deleg,args);

                return;

            }

            SqlDependency dependency = (SqlDependency)sender;

            dependency.OnChange -= number_OnChange;

            GetData();

        }

    }

}

When we run this code, a Windows Form application shows like this:


Let’s click the Start button:


lblText changed to 1, which is the current value in database. What happens if we update the database:

–Update data

update Test

set Number = 2

lblText changes to 2 almost immediately:


Great, without too much of coding, we have successfully setup a data change presenting process with minimum resource!

Note: Query notification does have a performance impact on server side and should be used for less frequently changed data. Microsoft recommends ten concurrent subscribers.

VS2010 failed to deploy SQLCLR on SQL Server 2012

I was experimenting SQL Server 2012 CLR with VS2010 I recently got. However, I ran into this version not supported issue:


Issue continued after I changed framework version.

After some research, I came across this link:

http://msdn.microsoft.com/en-us/data/hh297027

Once installation was completed, I restarted VS 2010. As soon as I open SqlServerProject1, there is a conversion screen poped up:


Of course I chose ‘Yes’ and conversion was done in seconds.

Interestingly, the ‘Deploy’ option doesn’t exist any more and there is a ‘Publish’ instead:


I had to setup database connection again here. Publish started after clicking ‘Publish’ button:


And the test ran successfully: