Executing multiple stored procedures within a single transaction context from ADO.Net

This aims to explain how we can execute different stored procedures in ADO.Net with different parameter values with in a single Transaction context, so that if anything gone wrong in any of the stored procedure, all others should be roll backed or else committed.
 Why do I need this? "Practical approach" 
Very often, we might have encountered various scenarios where we need to insert records in a batch.  This can go in case of a finance application where the data is tightly coupled and inter depended as well as inserting records of a grid-view (let us keep aside datasets for a while, because there is no transactions involved) in click of a button.  The handling of data in case of finance application is more complex where the dependency is high.  For example General Ledgers has to be available only when the journal entries are successful.
 
Of course we can handle this very well, with in the stored procedure itself as we have transactions in any RDBMS you name. But what if we need to combine multiple stored procedures together and run it with in a single transaction boundary, especially when I am forced to do it only through ADO.Net?  (Again please keep aside DTC for a while because I feel it is bit pain for initial configurations)
Solution 
I tried to get a work around fix and my objective was to reduce the burden of developer as much as possible. Something needs to be done where things can be handled very effectively with minimum lines of code. And here is what I tried.
 
To get this code up, you need to have Microsoft Applications Data block that can be downloaded either from Microsoft or from my earlier blog http://www.c-sharpcorner.com/UploadFile/vmsanthosh.chn/209062007040459AM/2.aspx?ArticleID=96a34245-427e-4e57-adb0-f75ce28d5b1d

Fine, But how do I use it?
Usage of this wrapper class is quite simple.  

DAC DC = new DAC();

DC.StoredProcedure = "nProc_InsertOrder";

DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );

DC.Params.Add("@CustomerName", SqlDbType.VarChar, "test");

DAC.Commands.Add(DC); 

DC = new DAC();

DC.StoredProcedure = "nProc_InsertOrderLineItems";

DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );

DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A1");

DAC.Commands.Add(DC); 

DC = new DAC();

DC.StoredProcedure = "nProc_InsertOrderLineItems";

DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );

DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A2");

DAC.Commands.Add(DC); 

DC = new DAC();

DC.StoredProcedure = "nProc_CreateBill";

DC.Params.Add("@BillDate", SqlDbType.DateTime, DateTime.Now);

DC.Params.Add("@BillId", SqlDbType.VarChar, "Bill1");

DAC.Commands.Add(DC);DAC.ExecuteBatch(); 
If the order insertion is failed, the bill should not be created. Similarly, if the line items are failed, then the order should not be created. We are achieving this in just a few lines of code through ADO.Net.
 
In this example, till we call ExecuteBatch, we are not actually inserting the records but preparing the object for making batch updations.

Using "OUT" Parameter With Stored Procedure in ASP.NET?

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns UserName, Email and Country. The table looks as below. 

Creating a Stored Procedure with Out parameter

Now create a stored procedure with an out parameter to insert data into the table. We create an error out parameter.

USE [Rohatash]

GO

/****** Object:  StoredProcedure [dbo].[spuserdetail]    Script Date: 01/25/2012 01:37:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spuserdetail]

@UserName varchar(50),

@Password varchar(50),

@Email varchar(50),

@Country varchar(50),

@ERROR VARCHAR(100) OUT

AS

BEGIN   

         

SET NOCOUNT ON;

IF NOT EXISTS(SELECT * FROM UserDetail WHERE UserName=@UserName) //  To Check UserName is exits or not

BEGIN

INSERT INTO UserDetail(

UserName,

[Password],

Email,

Country

)

VALUES

(

@UserName,

@Password,

@Email,

@Country

)

SET @ERROR=@UserName+' Registered Successfully'

END

ELSE

BEGIN

SET @ERROR=@UserName + ' Already Exists'

END

END

In the above stored procedure, error is the out parameter and other are the input parameter. In this stored procedure we check UserName; if the UserName exists in the table then it will return the message as an Output Parameter.

SET @ERROR=@UserName + ' Already Exists'

If the UserName does not exist in the table then it will return the message as an Output Parameter.

SET @ERROR=@UserName+' Registered Successfully'

Executing the stored procedure from C# code

In order to demonstrate the process of executing a stored procedure from a C#, create a new web application project in Visual Studio 2010. Add using statements above the namespace declaration to enable the use of non-fully qualified references to other namespace types.

Now add the following namespace.

using System.Data.SqlClient;

using System.Data;

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;";

Now I need to display that output parameter message during user registration in ASP.NET. How to get that output parameter returned by a SQL query. For a sample design your aspx page might be like this:

aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs"Inherits="WebApplication117.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

 UserName:<asp:TextBox ID="UserNameTextBox" runat="server"></asp:TextBox>

        <br />

        <br />

 Password:<asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"></asp:TextBox>

        <br />

        <br />

        Confirm Password:<asp:TextBox ID="ConfirmPasswordTextBox" runat="server"TextMode="Password"></asp:TextBox>

        <br />

        <br />

  Email:<asp:TextBox ID="EmailTextBox" runat="server"></asp:TextBox>

        <br />

        <br />

 Country:<asp:TextBox ID="CountryTextBox" runat="server"></asp:TextBox>

        <br />

        <br />

      <asp:Button ID="SaveButton" runat="server" Text="Save"

            onclick="SaveButton_Click" />

            <span style= "color:Red; font-weight :bold"> <asp:Label ID="lblErrorMsg" runat="server"></asp:Label></span>

    </div>

    </form>

</body>

</html>

To get output parameters in ASP.NET we need to write statements like this.

cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);

        cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;

        message = (string)cmd.Parameters["@ERROR"].Value;

In Codebehind write the following code in the SaveButton_Click like this. 

Codebehind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

namespace WebApplication117

{

   

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

    {

      private string message = string.Empty;

      protected void Page_Load(object sender, EventArgs e)

        {

        }

protected void SaveButton_Click(object sender, EventArgs e)

{

    if (PasswordTextBox.Text == ConfirmPasswordTextBox.Text)

    {

        string UserName = UserNameTextBox.Text;

        string Password = PasswordTextBox.Text;

        string ConfirmPassword = ConfirmPasswordTextBox.Text;

        string Email = EmailTextBox.Text;

        string Country = CountryTextBox.Text;

        SqlConnection con = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=Rohatash;");

        con.Open();

        SqlCommand cmd = new SqlCommand("spuserdetail", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@UserName", UserName);

        cmd.Parameters.AddWithValue("@Password", Password);

        cmd.Parameters.AddWithValue("@Email", Email);

        cmd.Parameters.AddWithValue("@Country", Country);

        cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);

        cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;

        cmd.ExecuteNonQuery();

        message = (string)cmd.Parameters["@ERROR"].Value;

        con.Close();

    }

    else

    {

        Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");

    }

    lblErrorMsg.Text = message;

}

    }

}

ex:::

alter PROCEDURE [dbo].[Students_sp]
@FirstName nvarchar(200),
@lastname nvarchar(200),
@Email nvarchar(100)=null,

@ERROR VARCHAR(100)=null out
AS
BEGIN   
         
SET NOCOUNT ON;

IF NOT EXISTS(SELECT * FROM tbl_Students WHERE FirstName=@FirstName) -- To Check UserName is exits or not
BEGIN
INSERT INTO tbl_Students(
FirstName,
lastname,
Email
)
VALUES
(
@FirstName,
@lastname,
@Email
)
SET @ERROR=(select top 1 (Studentid) from tbl_Students order by Studentid desc)
select @ERROR 
END

else
begin
set @ERROR='Error'
select @ERROR 
end
end

SET NOCOUNT ON
SELECT * FROM @table
SELECT @@rowcount
SET NOCOUNT OFF

exec Students_sp 'Vivek01','aa','sss'


sp_helptext Students_sp

The Software Development Life Cycle?

The Software Development Life Cycle is a step-by-step process involved in the development of a software product. It is also denoted as Software Development process in certain parts of the world. The whole process is generally classified into a set of steps and a specific operation will be carried out in each of the steps. 

Classification 
The basic classification of the whole process is as follows 
Planning 
Analysis 
Design 
Development 
Implementation 
Testing 
Deployment 
Maintenance 
Each of the steps of the process has its own importance and plays a significant part in the product development. The description of each of the steps can give a better understanding. 

Planning 
This is the first and foremost stage in the development and one of the most important stages. The basic motive is to plan the total project and to estimate the merits and demerits of the project. The Planning phase includes the definition of the intended system, development of the project plan, and Parallel management of the plan throughout the proceedings of the development. 

A good and matured plan can create a very good initiative and can positively affect the complete project. 

Analysis 
The main aim of the analysis phase is to perform statistics and requirements gathering. Based on the analysis of the project and due to the influence of the results of the planning phase, the requirements for the project are decided and gathered. 

Once the requirements for the project are gathered, they are prioritized and made ready for further use. The decisions taken in the analysis phase are out and out due to the requirements analysis. Proceedings after the current phase are defined. 

Design 
Once the analysis is over, the design phase begins. The aim is to create the architecture of the total system. This is one of the important stages of the process and serves to be a benchmark stage since the errors performed until this stage and during this stage can be cleared here. 

Most of the developers have the habit of developing a prototype of the entire software and represent the software as a miniature model. The flaws, both technical and design, can be found and removed and the entire process can be redesigned. 

Development and Implementation 
The development and implementation phase is the most important phase since it is the phase where the main part of the project is done. The basic works include the design of the basic technical architecture and the maintenance of the database records and programs related to the development process. 

One of the main scenarios is the implementation of the prototype model into a full-fledged working environment, which is the final product or software. 

Testing 
The testing phase is one of the final stages of the development process and this is the phase where the final adjustments are made before presenting the completely developed software to the end-user. 

In general, the testers encounter the problem of removing the logical errors and bugs. The test conditions which are decided in the analysis phase are applied to the system and if the output obtained is equal to the intended output, it means that the software is ready to be provided to the user. 

Maintenance 
The toughest job is encountered in the maintenance phase which normally accounts for the highest amount of money. The maintenance team is decided such that they monitor on the change in organization of the software and report to the developers, in case a need arises. 

Working with XML Records and DataGrid with C# and ASP.NET

In this article you will learn how to Read/Write the XML, Add XML Node / Record, Delete XML Node / Record and Display XML Data in DataGrid .

1.Create a xml-file and copy the following data.

<?xml version="1.0" encoding="utf-8"?>
<Movies>
  <Movie>
    <ID>1</ID>
    <Title>The Avengers</Title>
    <Director>Joss Whedon</Director>
    <Year>2012</Year>
    <Rating>8.7</Rating>
  </Movie>
  <Movie>
    <ID>2</ID>
    <Title>Dark Shadows</Title>
    <Director>Tim Burton</Director>
    <Year>2012</Year>
    <Rating>6.6</Rating>
  </Movie>
  <Movie>
    <ID>3</ID>
    <Title>Men in Black III</Title>
    <Director>Barry Sonnenfeld</Director>
    <Year>2012</Year>
    <Rating>NA</Rating>
  </Movie>
  <Movie>
    <ID>4</ID>
    <Title>Looser</Title>
    <Director>Lennes</Director>
    <Year>2014</Year>
    <Rating>7</Rating>
  </Movie>

</Movies>

2.Html Body Tag---including datalist code

<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Movie ID:
                </td>
                <td>
                    <asp:TextBox ID="txtMovieID" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Movie Title:
                </td>
                <td>
                    <asp:TextBox ID="txtMovieTitle" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Movie Director:
                </td>
                <td>
                    <asp:TextBox ID="txtMovieDirector" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Movie Year:
                </td>
                <td>
                    <asp:TextBox ID="txtMovieYear" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Movie Rating:
                </td>
                <td>
                    <asp:TextBox ID="txtMovieRating" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                </td>
            </tr>
        </table>
        <br />
        <asp:Button ID="btnAddMovie" runat="server" Text="Add Movie" OnClick="btnAddMovie_Click" />
        <asp:Button ID="btnRemoveMovie" runat="server" Text="Remove Movie" OnClick="btnRemoveMovie_Click" />
        <br />
        <br />
        <br />
        <asp:DataGrid ID="dgData" runat="server" AutoGenerateColumns="true">
            <HeaderStyle Font-Bold="true" />
        </asp:DataGrid>
        <br />
    </div>
    </form>

</body>

3.Source code :

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DisplayMovies();
        }
    }
    protected void btnAddMovie_Click(object sender, EventArgs e)
    {
        XmlDocument objXMLDoc = ReadXML();

        XmlElement objElement = objXMLDoc.CreateElement("Movie");
        XmlElement ID = objXMLDoc.CreateElement("ID");
        ID.InnerText = txtMovieID.Text;
        XmlElement Title = objXMLDoc.CreateElement("Title");
        Title.InnerText = txtMovieTitle.Text;
        XmlElement Director = objXMLDoc.CreateElement("Director");
        Director.InnerText = txtMovieDirector.Text;
        XmlElement Year = objXMLDoc.CreateElement("Year");
        Year.InnerText = txtMovieYear.Text;
        XmlElement Rating = objXMLDoc.CreateElement("Rating");
        Rating.InnerText = txtMovieRating.Text;

        objElement.AppendChild(ID);
        objElement.AppendChild(Title);
        objElement.AppendChild(Director);
        objElement.AppendChild(Year);
        objElement.AppendChild(Rating);

        objXMLDoc.DocumentElement.AppendChild(objElement);
        //Save XML
        WriteXML(objXMLDoc);

        //Display Movie List again
        DisplayMovies();
    }
    private void DisplayMovies()
    {
        DataSet oDs = GetXMLDataSet();
        if (oDs != null && oDs.Tables.Count > 0 && oDs.Tables[0].Rows.Count > 0)
        {
            dgData.DataSource = oDs;
            dgData.DataBind();
        }
    }

    protected void btnRemoveMovie_Click(object sender, EventArgs e)
    {
         bool blnFlag = false;
        XmlDocument objXMLDoc = ReadXML();

        XmlNodeList lstMovies = objXMLDoc.GetElementsByTagName("Movie");

        foreach (XmlNode objNode in lstMovies)
        {
            XmlNodeList objNodeList = objNode.ChildNodes;

            foreach (XmlNode objInnerNode in objNodeList)
            {                
                if (objInnerNode.InnerText == txtMovieTitle.Text.ToString())
                {
                    objXMLDoc.DocumentElement.RemoveChild(objNode);

                    blnFlag = true;
                    break;
                }
            }
            if (blnFlag)
                break;
        }
        //Save XML
        WriteXML(objXMLDoc);

        //Display Movie List again
        DisplayMovies();

    }  
    
    private DataSet GetXMLDataSet()
    {
        DataSet objDS = new DataSet();

        objDS.ReadXml(Server.MapPath("~/XMLFile.xml"));
        return objDS;
    }
    private XmlDocument ReadXML()
    {
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(Server.MapPath("~/XMLFile.xml"));
        return xmlDoc;
    }
    private void WriteXML(XmlDocument objDoc)
    {
        objDoc.Save(Server.MapPath("~/XMLFile.xml"));

    }


Source:http://mrbool.com/working-with-xml-records-and-datagrid-with-c-and-asp-net/24610