How use SQL transactions in C#

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

SQL Server operates in the following transaction modes.

Autocommit transactions

Each individual statement is a transaction.

Explicit transactions

Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

Batch-scoped transactions

Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.

 

To apply transactions in C# use the following syntax

 

 

 

 

using (var Conn = new SqlConnection(_ConnectionString))
{
    SqlTransaction trans = null;
    try
    {
        Conn.Open();
        trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn, trans))
        {
            /* DB work */
        }
        trans.Commit();
    }
    catch (Exception Ex)
    {
        if (trans != null) trans.Rollback();
        return -1;
    }
}

 

 

Advertisements

To get result from another stored procedure

In SQL , to execute a SP from another sp and get the resultset as table format use the following code

Stored Procedure 1

Create PROCEDURE  [Sp_Report1]
  @cid bigint=0 
  AS
BEGIN

select Description,CurrentValue from table1

end 

—————————————–

 

Stored Procedure 2

Create PROCEDURE  [Sp_Report2]
  @cid bigint=0 
  AS
BEGIN

 
declare @tableInvestment table (Description nvarchar(50), CurrentValue decimal(18,5) ) 
insert into @tableInvestment 

exec Sp_Report1 2                  — 2 is the parameter value for @CID

select * from  @tableInvestment 

end 

 

 

Convert datatable to XML file

To write contents of a datatable to a XML file use the following method

 

 

public void WriteToXML(DataTable Dt)
{
string result;
Dt.TableName = “mytable”;
using (StringWriter sw = new StringWriter())
{
Dt.WriteXml(sw);
result = sw.ToString();
}
result = result.Replace(“NewDataSet”, “DocumentElement”);
//return result;
try
{
string fileLoc = Application.StartupPath + “\\demo.xml”;
FileStream fs = null;
if (!File.Exists(fileLoc))
{
fs = File.Create(fileLoc);
fs.Close();
}
else
{
File.Delete(Application.StartupPath + “\\demo.xml”);

}
using (StreamWriter sw = new StreamWriter(fileLoc, true))
{
sw.Write(result);
}
}
catch (Exception)
{

}
}

demo.xml will be created in debug folder. 

 

Output:

<DocumentElement>
<mytable>
  <Asset_Type>Salary Income</Asset_Type>
  </mytable>
<mytable>
  <Asset_Type>Bussiness Income</Asset_Type>
  </mytable>
<mytable>
  <Asset_Type>Rental Income</Asset_Type>
  </mytable>
<mytable>
  <Asset_Type>Other Income</Asset_Type>
  </mytable>
  <mytable />
<mytable>
  <Asset_Type>Total</Asset_Type>
  </mytable>
  </DocumentElement>