Part 69 - Merge in SQL Server

Suggested Videos
Part 66 - Writing re-runnable sql server scripts
Part 67 - Alter database table columns without dropping table
Part 68 - Optional parameters in sql server stored procedures



What is the use of MERGE statement in SQL Server
Merge statement introduced in SQL Server 2008 allows us to perform Inserts, Updates and Deletes in one statement. This means we no longer have to use multiple statements for performing Insert, Update and Delete.

With merge statement we require 2 tables
1. Source Table - Contains the changes that needs to be applied to the target table
2. Target Table - The table that require changes (Inserts, Updates and Deletes)



The merge statement joins the target table to the source table by using a common column in both the tables. Based on how the rows match up as a result of the join, we can then perform insert, update, and delete on the target table. 

Merge statement syntax
MERGE [TARGET] AS T
USING [SOURCE] AS S
   ON [JOIN_CONDITIONS]
 WHEN MATCHED THEN 
      [UPDATE STATEMENT]
 WHEN NOT MATCHED BY TARGET THEN
      [INSERT STATEMENT] 
 WHEN NOT MATCHED BY SOURCE THEN
      [DELETE STATEMENT]

Example 1 : In the example below, INSERT, UPDATE and DELETE are all performed in one statement
1. When matching rows are found, StudentTarget table is UPDATED (i.e WHEN MATCHED)

2. When the rows are present in StudentSource table but not in StudentTarget table those rows are INSERTED into StudentTarget table (i.e WHEN NOT MATCHED BY TARGET)

3. When the rows are present in StudentTarget table but not in StudentSource table those rows are DELETED from StudentTarget table (i.e WHEN NOT MATCHED BY SOURCE)

Create table StudentSource

(

     ID int primary key,

     Name nvarchar(20)

)

GO

Insert into StudentSource values (1, 'Mike')

Insert into StudentSource values (2, 'Sara')

GO

Create table StudentTarget

(

     ID int primary key,

     Name nvarchar(20)

)

GO

Insert into StudentTarget values (1, 'Mike M')

Insert into StudentTarget values (3, 'John')

GO

MERGE StudentTarget AS T

USING StudentSource AS S

ON T.ID = S.ID

WHEN MATCHED THEN

     UPDATE SET T.NAME = S.NAME

WHEN NOT MATCHED BY TARGET THEN

     INSERT (ID, NAME) VALUES(S.ID, S.NAME)

WHEN NOT MATCHED BY SOURCE THEN

     DELETE;

Please Note : Merge statement should end with a semicolon, otherwise you would get an error stating - A MERGE statement must be terminated by a semi-colon (;)

In real time we mostly perform INSERTS and UPDATES. The rows that are present in target table but not in source table are usually not deleted from the target table.

Example 2 : In the example below, only INSERT and UPDATE is performed. We are not deleting the rows that are present in the target table but not in the source table.

Truncate table StudentSource

Truncate table StudentTarget

GO

Insert into StudentSource values (1, 'Mike')

Insert into StudentSource values (2, 'Sara')

GO

Insert into StudentTarget values (1, 'Mike M')

Insert into StudentTarget values (3, 'John')

GO

MERGE StudentTarget AS T

USING StudentSource AS S

ON T.ID = S.ID

WHEN MATCHED THEN

     UPDATE SET T.NAME = S.NAME

WHEN NOT MATCHED BY TARGET THEN

     INSERT (ID, NAME) VALUES(S.ID, S.NAME);

Part 69 - Merge in SQL Server

Part 21 - Transactions in ADO.NET

Suggested Videos
Part 18 - Load xml data into sql server table using sqlbulkcopy
Part 19 - Copying data from one table to another table using SqlBulkCopy
Part 20 - SqlBulkCopy NotifyAfter Example



In this video we will discuss how to implement Transactions in ADO.NET

What is a Transaction
A Transaction ensures that either all of the database operations succeed or all of them fail. This means the job is never half done, either all of it is done or nothing is done. Let's understand this with an example.



We will be using the following Accounts table in this demo


The table has got 2 Accounts (A1 and A2). We want to design a web application to transfer $10 from Account A1 to Account A2. The design of the webform should be as shown below.


When we click "Transfer $10 from Account A1 to Account A2" button, we should subtract 10 from A1 account and add 10 to A2 account. So there will be 2 database UPDATE statements. What do you think will happen if only the first update statement is executed successfully and not the second statement. $10 is deducted from the first account, but not added to the second account. This is definitely not desirable. Either both the statements should succeed or both of them should fail. If one succeeds and other fails we should also rollback the changes made by the first statement to maintain the integrity of the data. This can be achieved using transactions in ado.net.

Step 1 : Create the Accounts table using the following SQL script

Create Table Accounts

(

     AccountNumber nvarchar(10) primary key,

     CustomerName nvarchar(50),

     Balance int

)

GO

Insert into Accounts values('A1', 'Mark', 100)

Insert into Accounts values('A2', 'Steve', 100)

GO

Step 2 : Create a new empty asp.net web application. Name it Demo.

Step 3 : Include connection string in web.config file

<connectionStrings>

  <add name="CS"

           connectionString="server=.;database=Sample;integrated security=SSPI"/>

</connectionStrings>

Step 4 : Add a WebForm. Copy and paste the following HTML.

<div style="font-family: Arial">

<table border="1" style="background: brown; color: White">

    <tr>

        <td>

            <b>Account Number </b>

        </td>

        <td>

            <asp:Label ID="lblAccountNumber1" runat="server"></asp:Label>

        </td>

        <td>

            <asp:Label ID="lblAccountNumber2" runat="server"></asp:Label>

        </td>

    </tr>

    <tr>

        <td>

            <b>Customer Name </b>

        </td>

        <td>

            <asp:Label ID="lblName1" runat="server"></asp:Label>

        </td>

        <td>

            <asp:Label ID="lblName2" runat="server"></asp:Label>

        </td>

    </tr>

    <tr>

        <td>

            <b>Balance </b>

        </td>

        <td>

            <asp:Label ID="lblBalance1" runat="server"></asp:Label>

        </td>

        <td>

            <asp:Label ID="lblBalance2" runat="server"></asp:Label>

        </td>

    </tr>

</table>

<br />

<asp:Button ID="btnTransfer" runat="server"

            Text="Transfer $10 from Account A1 to Account A2"

            OnClick="btnTransfer_Click" />

<br />

<br />

<asp:Label ID="lblMessage" runat="server" Font-Bold="true"></asp:Label>

</div>

Step 5 : Copy and paste the following code in the code-behind file.

using System;

using System.Configuration;

using System.Data.SqlClient;

namespace Demo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                GetAccountsData();

            }

        }

        private void GetAccountsData()

        {

            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlCommand cmd = new SqlCommand("Select * from Accounts", con);

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    if (rdr["AccountNumber"].ToString() == "A1")

                    {

                        lblAccountNumber1.Text = "A1";

                        lblName1.Text = rdr["CustomerName"].ToString();

                        lblBalance1.Text = rdr["Balance"].ToString();

                    }

                    else

                    {

                        lblAccountNumber2.Text = "A2";

                        lblName2.Text = rdr["CustomerName"].ToString();

                        lblBalance2.Text = rdr["Balance"].ToString();

                    }

                }

            }

        }

        protected void btnTransfer_Click(object sender, EventArgs e)

        {

            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            {

                con.Open();

                // Begin a transaction. The connection needs to 

                // be open before we begin a transaction

                SqlTransaction transaction = con.BeginTransaction();

                try

                {

                    // Associate the first update command with the transaction

                    SqlCommand cmd = new SqlCommand

                        ("Update Accounts set Balance = Balance - 10 where AccountNumber = 'A1'"

                        , con, transaction);

                    cmd.ExecuteNonQuery();

                    // Associate the second update command with the transaction

                    cmd = new SqlCommand

                        ("Update Accounts set Balance = Balance + 10 where AccountNumber = 'A2'"

                        , con, transaction);

                    cmd.ExecuteNonQuery();

                    // If all goes well commit the transaction

                    transaction.Commit();

                    lblMessage.ForeColor = System.Drawing.Color.Green;

                    lblMessage.Text = "Transaction committed";

                }

                catch

                {

                    // If anything goes wrong, rollback the transaction

                    transaction.Rollback();

                    lblMessage.ForeColor = System.Drawing.Color.Red;

                    lblMessage.Text = "Transaction rolled back";

                }

            }

            GetAccountsData();

        }

    }

}

Testing : Run the apllication and click the "Transfer $10 from Account A1 to Account A2" button. Notice that $10 is deducted from Account A1 and added to Account A2 and the transaction is committed.


Let's now deliberately introduce a change that would crash the application at run time after executing the first update statement. 

CHANGE THE FOLLOWING LINE
cmd = new SqlCommand("Update Accounts set Balance = Balance + 10 where AccountNumber = 'A2'", con, transaction);

TO
cmd = new SqlCommand("Update Accounts1 set Balance = Balance + 10 where AccountNumber = 'A2'", con, transaction);

Run the apllication again and click the "Transfer $10 from Account A1 to Account A2" button. Notice that the transaction is rolled back and the data integrity is not lost.

Part 21 - Transactions in ADO.NET

How to become an asp.net developer

In this video we will discuss all that you need to know to become a successful asp.net web application developer



Essential skills : The following are the essential skills that you need to learn in the order specified to become a web developer




1. Dot Net Basics : Learn what is Dot Net all about and how it works. All dot net developers, irrespective of whether you are a web or windows developer, need to know about these concepts. Click here for 7 videos (1 Hour 55 Minutes) covering the basics of dot net.

2. One Programming language (C# or VB.NET) : You must learn at least on of the programming language (C# or VB.NET) to build dynamic data driven web applications. Click here for 101 videos (22 Hours 14 Minutes) covering C# programming language. These videos start from the basics and covers all the advanced aspects of c# programming language that an asp.net web developer needs to know.

3. SQL Server : SQL Server is a database that stores the web application data. You need to learn the SQL programming language to store and retrieve data from the database server, which helps us build data driven web application. Click here for 67 videos (15 Hours 30 Minutes) covering SQL Server concepts and SQL programming language.

4. ASP.NET : This is the framework that .NET provides to build web applications. Click here for 164 videos (38 Hours 12 Minutes) covering all aspects of ASP.NET.

5. ADO.NET : Enables an asp.net web application to communicate with the database to store and retrieve data. Click here for the ADO.NET Tutorial - 20 videos (5 Hours 40 Minutes).

6. ASP.NET GridView : This is one of the widely used data control in an asp.net web application that enables to display, insert, update and delete data. Click here for the ASP.NET GridView tutorial (61 - Videos - 13 Hours 27 Minutes)

7. Visual Studio : This is the development tool/software that we use to build Dot Net applications. Click here for the Visual Studio tutorial (7 - Videos - 44 Minutes).

In addition to the above 7 essential skills, you also need to know the basics of HTML. HTML is very easy to self learn and there are many free resources on the web already.

Nice to have skills : These days a lot of organisations are also looking for the following skills in addition to the above essential skills. If you want to increase the chances of getting your resume shortlisted for an interview call, then you also need to be familiar (if not proficient) with the following technologies.


1. ASP.NET Web Services : ASP.NET Web Services became a legacy technology with the introduction of WCF. However, there are many organisations that are still using ASP.NET web services, so if you want to increase your chances of an interview call you may want to learn this technology as well. Also, learning asp.net web services, makes it easy to learn WCF. Click here for the video tutorial (7 - Videos - 1 Hour 34 Minutes).

2. WCF : Helps buils service oriented applications. If you are familiar with this and if you can answer the interview questions you almost have the job. Click here for the wcf video tutorial (53 - Videos - 12 Hours 1 Minute).

3. ASP.NET MVC : Gives you a powerful, patterns-based way to build dynamic websites that enables a clean separation of concerns and that gives you full control. Click here for the asp.net mvc video tutorial (53 - Videos - 12 Hours 1 Minute).

4. Entity Framework : This is an ORM framework  that enables .NET developers to work with relational data using domain-specific objects. Another nice to have skill. Click here for the video tutorial (25 - Videos - 4 Hours 59 Minutes)

5. LINQ : Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. The different aspects of LINQ are covered in the following tutorials.
LINQ - (32 - Videos - 4 Hours 53 Minutes).
LINQ to XML - (8 - Videos - 1 Hour 5 Minutes).
LINQ to SQL - (15 - Videos - 1 Hour 43 Minutes).

In addition to the above 4 nice to have skills, you also need to know jQuery. I don't have a video series on this yet, but there are several resources on the web already.

To help you with your interview preparation, I have also started several video series as listed below.
C# Interview Questions & Answers
Dot Net Written Test Questions & Answers
SQL Server Interview Questions & Answers

Slides, code samples and text version of the videos are available on my blog.
http://csharp-video-tutorials.blogspot.com

All these resources are available at no cost. In total there are over 700 videos (150 Hours). This means if you spend 5 hours a day watching these videos in 30 days you have most of the skills required to become a successful asp.net web developer.

All the very best.

If you need a video on a concept that you are struggling with or if you have attended an interview and if you need a question to be answered, please leave it as a comment and I shall record and upload video answering it as soon as I can.

If you need a DVD with all the videos and slides or if you need to download the videos for offline viewing, please visit the following link.
http://pragimtech.com/order.aspx

Finally if you feel these videos could help others like you please share on Facebook, Google+ and any other social media  you use.

Enjoy Coding
Venkat

Part 20 - sqlbulkcopy notifyafter example

Suggested Videos
Part 17 - Strongly typed datasets
Part 18 - Load xml data into sql server table using sqlbulkcopy
Part 19 - Copying data from one table to another table using SqlBulkCopy



In this video we will discuss the use 
1. BatchSize property
2. NotifyAfter property
3. SqlRowsCopied event



BatchSize property - Specifies the number of rows in a batch that will be copied to the destination table. The BatchSize property is very important as the performance of data transfer depends on it. The default batch size is 1. In the example below, BatchSize is set to 10000. This means once the reader has read 10000 rows they will be sent to the database as a single batch to perform the bulk copy operation.

NotifyAfter property - Defines the number of rows to be processed before raising SqlRowsCopied event. In the example below, NotifyAfter property is set to 5000. This means once every 5000 rows are copied to the destination table SqlRowsCopied event is raised.

SqlRowsCopied event - This event is raised every time the number of rows specified by NotifyAfter property are processed. This event is useful for reporting the progress of the data transfer.

Let us now understand these properties with an example.

Step 1 : Execute the following SQL script to create Products_Source table and populate it with test data.

Create Table Products_Source

(

 [Id] int primary key,

 [Name] nvarchar(50),

 [Description] nvarchar(250)

)

GO

Declare @Id int

Set @Id = 1

While(@Id <= 300000)

Begin

 Insert into Products_Source values

 (@Id, 'Product - ' + CAST(@Id as nvarchar(20)),

 'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')

 Print @Id

 Set @Id = @Id + 1

End

GO

Step 2 : Create Products_Destination table

Create Table Products_Destination

(

 [Id] int primary key,

 [Name] nvarchar(50),

 [Description] nvarchar(250)

)

GO

Step 3 : Create a new console application. Name it Demo. Include the database connection string in App.config file

<connectionStrings>

  <add name="CS"

        connectionString="server=.;database=Sample;integrated security=SSPI"/>

</connectionStrings>

Step 4 : Copy and paste the following code in Program.cs file.

using System;

using System.Configuration;

using System.Data.SqlClient;

namespace Demo

{

    class Program

    {

        static void Main()

        {

            string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

            using (SqlConnection sourceCon = new SqlConnection(cs))

            {

                SqlCommand cmd = new 

                                       SqlCommand("Select * from Products_Source", sourceCon);

                sourceCon.Open();

                using (SqlDataReader rdr = cmd.ExecuteReader())

                {

                    using (SqlConnection destinationCon = new SqlConnection(cs))

                    {

                        using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))

                        {

                            bc.BatchSize = 10000;

                            bc.NotifyAfter = 5000;

                            bc.SqlRowsCopied += 

                                     new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);

                            bc.DestinationTableName = "Products_Destination";

                            destinationCon.Open();

                            bc.WriteToServer(rdr);

                        }

                    }

                }

            }

        }

        static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

        {

            Console.WriteLine(e.RowsCopied + " loaded....");

        }

    }

}

Please Note : Instead of creating a separate event handler method for SqlRowsCopied event and then passing the name of the event handler method to SqlRowsCopiedEventHandler delegate, we can reduce the amount of code we have to write with the help of an anonymous function as shown below.

bc.SqlRowsCopied += (sender, eventArgs) =>

{

    Console.WriteLine(eventArgs.RowsCopied + " loaded....");

};

This means the following method is no longer required

static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

{

    Console.WriteLine(e.RowsCopied + " loaded....");

}