Object To XML and Vice Versa using C#

Sometimes in web applications we like to save or send our data as XML to the SQL databases.
It’s mostly done when there is a large chunk of data or we like to convert our entity model objects to XML.
It makes it easier to move data from web applications to SQL database and vice versa.
Here I’ll share a code snippet to convert C# object to XML and XML to object C#.

C# Object to XML:

To convert an object to XML, we’ll make use of XmlSerializer to serialize and XmlTextWriter to output the XML string.
Here is how the code looks like :

public static string GetXMLFromObject(object o)
{
    StringWriter sw = new StringWriter();
    XmlTextWriter tw = null;
    try
    {
        XmlSerializer serializer = new XmlSerializer(o.GetType());
        tw = new XmlTextWriter(sw);
        serializer.Serialize(tw, o);
    }
    catch (Exception ex)
    {
        //Handle Exception Code
    }
    finally
    {
        sw.Close();
        if (tw != null)
        {
            tw.Close();
        }
    }
    return sw.ToString();
}

XML to Object C# :

Similarly, to convert an XML string to object we make use of the XmlSerializer to deserialize and XmlTextReader to read the XML string.
Here is how the code to convert XML to objects looks like :

public static Object ObjectToXML(string xml, Type objectType)
{
    StringReader strReader = null;
    XmlSerializer serializer = null;
    XmlTextReader xmlReader = null;
    Object obj = null;
    try
    {
        strReader = new StringReader(xml);
        serializer = new XmlSerializer(objectType);
        xmlReader = new XmlTextReader(strReader);
        obj = serializer.Deserialize(xmlReader);
    }
    catch (Exception exp)
    {
        //Handle Exception Code
    }
    finally
    {
        if (xmlReader != null)
        {
            xmlReader.Close();
        }
        if (strReader != null)
        {
            strReader.Close();
        }
    }
    return obj;
}

Calling :

Employee emp = new Employee();
emp.FirstName = "Ujjwala Datta";
emp.LastName = "Kalluri";

string xml = GetXMLFromObject(emp);
<?xml version="1.0" encoding="utf-16" ?> 
- <Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FirstName>Ujjwala Datta</FirstName> 
  <LastName>Kalluri</LastName> 
  </Employee>

Object obj = ObjectToXML(xml,typeof(Employee));
Advertisements

Handling Errors in SQL Server (Try Catch & Transactions)

During development of any application, one of the most common things we need to take care of is Exception and Error handling.

TRY…CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.

trycatch

TRANSACTIONS group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Example :

CREATE PROCEDURE UpdateSales
  @SalesPersonID INT,
  @SalesAmt MONEY = 0
AS
BEGIN

  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE SalesData
      SET SalesLastYear = SalesLastYear + @SalesAmt
      WHERE SalesPersonID = @SalesPersonID;
    COMMIT TRANSACTION;
  END TRY

  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    -- @@TRANCOUNT returns number of active Transactions,
    -- BEGIN TRANSACTION will increment the transaction count by 1 & 
    -- COMMIT TRANSACTION will decrement the transaction count by 1
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH

END

2015 in Review – Wish You Happy New Year

Wish You Happy Happy Happy New Year,

Time is free, but it’s priceless.
You can’t own it, but you can use it.
You can’t keep it, but you can spend it.
Once you’ve lost it, you can never get it back…

Years, Months, Weeks, Days, Hours , Minutes, Seconds… Its Transforming…

Hope we utilise our Time for well-being in a constructive way & we all have Good Health, Relationships, Prosperity, Contentment & Happiness.

Annual Report – 2015 for this blog

Here’s an excerpt:

In 2015 this blog was viewed about 5,300 times,

there were only 2 new posts,

The busiest day of the year was Apr 29th with 83 views.

Visitors are from 69 countries in all!

Most visitors came from India. The United States & The Srilanka.

DEFAULT Constraint – Adding Column Defaulting in Sql Server Table

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

Rather than providing a value into a column manually during insert operation, by using default option we can auto-populate specific value and update in column.

in the below example, no need to provide CreatedDate and Freezed values during Insertion .

Example :

create table TestTable
(
Id int IDENTITY(1,1) NOT NULL,
UserId int,

—–

—–
CreatedDate datetime default (getdate()),
Freezed int default (0)
)

Bulk Insert into SQL Server using SqlBulkCopy

Transferring data from one source to another is common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to the SQL SERVER database.

Reliable transaction are maintained by using SqlTransaction object to BulkCopy. In-case of error whole transaction will be role-backed.

Example :

SqlTransaction myTransaction;

DataTable FinalTable = (DataTable)Session[“Data”];

using (SqlConnection cn = Get_Connection())

{

myTransaction = cn.BeginTransaction();

try

{

using (SqlBulkCopy copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, myTransaction))

{

copy.ColumnMappings.Add(0, 1); //(SOURCE TABLE,DESTINATION TABLE)

copy.ColumnMappings.Add(2, 2); // (“Product Name”,”Name”)

copy.ColumnMappings.Add(3, 3);

copy.ColumnMappings.Add(4, 4);

copy.ColumnMappings.Add(5, 5);

copy.ColumnMappings.Add(6, 6);

copy.ColumnMappings.Add(7, 7);

copy.ColumnMappings.Add(8, 8);

copy.ColumnMappings.Add(9, 9);

//copy.BatchSize = 1000;

copy.DestinationTableName = “dbo.Products”;

copy.WriteToServer(FinalTable); // writing Source Data to sqlserver table

myTransaction.Commit();

}

}

catch (Exception ex)

{

myTransaction.Rollback();

}

finally

{

cn.Close();

}

}

(or)

public bool BulkInsert(DataTable dataTable)
{

if (dataTable != null && dataTable.Rows.Count > 0)
{
using (SqlConnection sqlConnection = new SqlConnection(Database.Connection_String))
{
sqlConnection.Open();

SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(sqlConnection) { DestinationTableName = “DestinationTableName” };

foreach (DataColumn dataColumn in dataTable.Columns)
objSqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);

objSqlBulkCopy.WriteToServer(dataTable);
return true;
}
}
return false;
}

Know Unknown of our Databases

Some times it is necessary to know database related details, such as Database Status, DataSize, Compatability, Creation Date, Lastbackup Details.

While browsing on internet I got this query, which is useful to know – unknown of our databases.

SELECT database_id[Database ID],

CONVERT(VARCHAR(25), DB.name)AS DatabaseName,

CONVERT(VARCHAR(10),DATABASEPROPERTYEX(name,‘status’))AS [Status],

state_desc[Status Description],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS DataFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS [Data MB],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘log’)AS LogFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘log’)AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model],

CASE compatibility_level

WHEN 60 THEN’60 (SQL Server 6.0)’

WHEN 65 THEN’65 (SQL Server 6.5)’

WHEN 70 THEN’70 (SQL Server 7.0)’

WHEN 80 THEN’80 (SQL Server 2000)’

WHEN 90 THEN’90 (SQL Server 2005)’

WHEN 100 THEN‘100 (SQL Server 2008)’

ENDAS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103)+‘ ‘+CONVERT(VARCHAR(20), create_date, 108)AS [Creation date],

— last backup

ISNULL((SELECTTOP 1

CASETYPEWHEN‘D’THEN‘Full’WHEN‘I’THEN‘Differential’WHEN‘L’THEN‘Transaction log’END++

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY,GETDATE(),Backup_finish_date)))+‘ days ago’,‘NEVER’))++

CONVERT(VARCHAR(20), backup_start_date, 103)+‘ ‘+CONVERT(VARCHAR(20), backup_start_date, 108)++

CONVERT(VARCHAR(20), backup_finish_date, 103)+‘ ‘+CONVERT(VARCHAR(20), backup_finish_date, 108)+

(‘+CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date)ASVARCHAR(4))+‘ ‘+‘seconds)’

FROM msdb..backupset BK WHERE BK.database_name = DB.nameORDERBY backup_set_id DESC),‘-‘)

AS [Last Backup Date],

CASEWHEN is_fulltext_enabled = 1 THEN‘Fulltext enabled’ELSEENDAS [FullText],

CASEWHEN is_auto_close_on = 1 THEN‘autoclose’ELSEENDAS [Autoclose],

page_verify_option_desc AS [page verify option],

CASEWHEN is_read_only = 1 THEN‘read only’ELSEENDAS [Read Only],

CASEWHEN is_auto_shrink_on = 1 THEN‘autoshrink’ELSEENDAS [Auto Shrink],

CASEWHEN is_auto_create_stats_on = 1 THEN‘auto create statistics’ELSEENDAS [auto create statistics],

CASEWHEN is_auto_update_stats_on = 1 THEN‘auto update statistics’ELSEENDAS [auto update statistics],

CASEWHEN is_in_standby = 1 THEN‘standby’ELSEENDAS [Standby],

CASEWHEN is_cleanly_shutdown = 1 THEN‘cleanly shutdown’ELSEENDAS [Cleanly Shutdown]

FROMsys.databases DB

ORDERBY DatabaseName

Enterprise Applications Integration -Overview

E A I – Breaking down information barriers across the Enterprise

Image

Overview :

As enterprises spread their boundaries across different business areas, they acquire disparate technology solutions, technologies and applications. However, these applications are often unable to communicate and share information with each other due to several reasons like lack of interoperability, variable formats and dissimilar standards of operations.

Supply chain management applications (for managing inventory and shipping), customer relationship management applications (for managing current and potential customers), business intelligence applications (for finding patterns from existing data from operations), and other types of applications (for managing data such as human resources data, health care, internal communications, etc.), ERP (For Resource Planning), Portals (for Operations) typically cannot communicate with one another in order to share data or business rules. For this reason, such applications are sometimes referred to as islands of automation or information silos. This lack of communication leads to inefficiencies, wherein identical data are stored in multiple locations, or straightforward processes are unable to be automated.

Even in our enlightened times, great amounts enterprise data lies locked away in monolithic applications. Whether they be purchased, home-grown, or a combination, the realities of business demand that the information contained in those vertical ‘silos’ be used in ways that the original architects never intended.

Enterprises are also increasingly looking forward to reconfigure their business processes through tighter integration of applications via rich frameworks, by opting for Enterprise Application Integration (EAI). This is with the intention to achieve effective communication across various business applications and delivery channels, thereby, ensuring seamless data integration from disparate systems

 Enterprise Application Integration:

 Enterprise application integration is an integration framework composed of a collection of technologies and services which form a middleware (Remote Procedure Call, Object Request Broker, Batch Data Communication, Business Application Programming Interface, Service Oriented Architectures) to enable integration of systems and applications across the enterprise.

Enterprise Application Integration practice expertise focuses on:

Opening up ways for the information to flow from one application to another,

Letting applications to reach other sources of data outside themselves,

and Allowing other programs to peer into the innards of formerly closed programs.

Sharing data between divisions or even between separate corporations

Image

and i wanted to discuss more about this challenge architecture in next couple of articles along with Implementations too which are observed by me since long time.