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>

Renaming a Column in a Temp Table in SQL Server

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

this command is not working in temparary table created through SP

to rename columns in a temp table use following syntax

exec tempdb..sp_rename ‘#TableName.ColumnName’, ‘NewColumnName’, ‘COLUMN’

For Example

Set @Str =’tempdb..sp_RENAME ‘#TempTable.expense’,’expenditure’,’COLUMN’
 

Stateless

Stateful and stateless are adjectives that describe whether a computer or computer program is designed to note and remember one or more preceding events in a given sequence of interactions with a user, another computer or program, a device, or other outside element. Stateful means the computer or program keeps track of the state of interaction, usually by setting values in a storage field designated for that purpose. Stateless means there is no record of previous interactions and each interaction request has to be handled based entirely on information that comes with it. Stateful and stateless are derived from the usage of state as a set of conditions at a moment in time. (Computers are inherently stateful in operation, so these terms are used in the context of a particular set of interactions, not of how computers work in general.)

The Internet’s basic protocol, the Internet Protocol ( IP ), is an example of a stateless interaction. Each packet travels entirely on its own without reference to any other packet. When you request a Web page from a Web site, the request travels in one or more packets, each independent of the other as far as the Internet Protocol program itself is concerned. (The upper layer Transmission Control Protocol – TCP – does relate packets to each other, but uses the information within the packet rather than some external information to do this.) The term connectionless is also used to describe communication in which a connection is made and terminated for each message that is sent. IP is connectionless as well as stateless.

The Web’s Hypertext Transfer Protocol ( HTTP ), an application layer above TCP/IP, is also stateless. Each request from a user for a Web page or URL results in the requested pages being served, but without the Web (HTTP) server remembering the request later. In other words, there is no recorded continuity. Each communication is discrete and unrelated to those that precede or follow. In order to have stateful communication, a site developer must furnish a special program that the server can call that can record and retrieve state information. Web browsers such as Netscape Explorer and Microsoft Internet Explorer provide an area in their subdirectories where state information can be stored and accessed. The area and the information that Web browsers and server applications put in this area is called a cookie .

In formal protocol specifications, a finite state machine is an abstract desciption of how a stateful system works that describes the action that follows each possible state.

Three Layer Architecture in C# .NET

3 Layer Windows Application in C#.

 

Image

 

Example

dbConnection

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ThreeLayerDemo.Core
{
    public class dbConnection
    {
        private SqlDataAdapter myAdapter;
        private SqlConnection conn;

        /// <constructor>
        /// Initialise Connection
        /// </constructor>
        public dbConnection()
        {
            myAdapter = new SqlDataAdapter();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings
					["dbConnectionString"].ConnectionString);
        }

        /// <method>
        /// Open Database Connection if Closed or Broken
        /// </method>
        private SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State == 
						ConnectionState.Broken)
            {
                conn.Open();
            }
            return conn;
        }

        /// <method>
        /// Select Query
        /// </method>
        public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();                
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeSelectQuery - Query: 
			" + _query + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            {

            }
            return dataTable;
        }

        /// <method>
        /// Insert Query
        /// </method>
        public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.InsertCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeInsertQuery - Query: 
			" + _query + " \nException: \n" + e.StackTrace.ToString());
                return false;
            }
            finally
            {
            }
            return true;
        }

        /// <method>
        /// Update Query
        /// </method>
        public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeUpdateQuery - Query: 
			" + _query + " \nException: " + e.StackTrace.ToString());
                return false;
            }
            finally
            {
            }
            return true;
        }
    }
}

Database Access Layer

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ThreeLayerDemo.Core
{
    public class UserDAO
    {
        private dbConnection conn;

        /// <constructor>
        /// Constructor UserDAO
        /// </constructor>
        public UserDAO()
        {
            conn = new dbConnection();
        }

        /// <method>
        /// Get User Email By Firstname or Lastname and return DataTable
        /// </method>
        public DataTable searchByName(string _username)
        {
            string query = string.Format("select * from [t01_user] 
		where t01_firstname like @t01_firstname or t01_lastname 
		like @t01_lastname ");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@t01_firstname", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(_username);
            sqlParameters[1] = new SqlParameter("@t01_lastname", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(_username);
            return conn.executeSelectQuery(query, sqlParameters);
        }

        /// <method>
        /// Get User Email By Id and return DataTable
        /// </method>
        public DataTable searchById(string _id)
        {
            string query = "select * from [t01_id] where t01_id = @t01_id";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@t01_id", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(_id);
            return conn.executeSelectQuery(query, sqlParameters);
        }
    }
}

Value Object

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ThreeLayerDemo.Core
{
    public class UserVO
    {
        private int _idUser;
        private string _firstname;
        private string _lastname;
        private string _email;

        /// <constructor>
        /// Constructor UserVO
        /// </constructor>
        public UserVO()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public int idUser
        {
            get
            { 
                return _idUser;
            }

            set
            {
                _idUser = value;
            }
        }

        public string firstname
        {
            get
            {
                return _firstname;
            }

            set
            {
                _firstname = value;
            }
        }

        public string lastname
        {
            get
            {
                return _lastname;
            }
            set
            {
                _lastname = value;
            }
        }

        public string email
        {
            get
            {
                return _email;
            }

            set
            {
                _email = value;
            }
        }
    }
}

Business Logic Layer

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace ThreeLayerDemo.Core
{
    /// <summary>
    /// Summary description for UserBUS
    /// </summary>
    public class UserBUS
    {
        private UserDAO _userDAO;

        /// <constructor>
        /// Constructor UserBUS
        /// </constructor>
        public UserBUS()
        {
            _userDAO  = new UserDAO();
        }

        /// <method>
        /// Get User Email By Firstname or Lastname and return VO
        /// </method>
        public UserVO getUserEmailByName(string name)
        {
            UserVO userVO = new UserVO();
            DataTable dataTable = new DataTable();

            dataTable = _userDAO.searchByName(name);

            foreach (DataRow dr in dataTable.Rows)
            {
                userVO.idUser = Int32.Parse(dr["t01_id"].ToString());
                userVO.firstname = dr["t01_firstname"].ToString();
                userVO.lastname = dr["t01_lastname"].ToString();
                userVO.email = dr["t01_email"].ToString();
            }
            return userVO;
        }

        /// <method>
        /// Get User Email By Id and return DataTable
        /// </method>
        public UserVO getUserById(string _id)
        {
            UserVO userVO = new UserVO();
            DataTable dataTable = new DataTable();
            dataTable = _userDAO.searchById(_id);

            foreach (DataRow dr in dataTable.Rows)
            {
                userVO.idUser = Int32.Parse(dr["t01_id"].ToString());
                userVO.firstname = dr["t01_firstname"].ToString();
                userVO.lastname = dr["t01_lastname"].ToString();
                userVO.email = dr["t01_email"].ToString();
            }
            return userVO;
        }
    }
}

Presentation Layer

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ThreeLayerDemo.Core;

namespace ThreeLayerDemo
{
    public partial class frmLogin : Form
    {
        private UserBUS _userBUS;

        public frmLogin()
        {
            InitializeComponent();
             _userBUS = new UserBUS();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            UserVO _userVO = new UserVO();
            _userVO = _userBUS.getUserEmailByName(txtUsername.Text);
            if (_userVO.email == null)
                MessageBox.Show("No Match Found!", "Not Found", 
			MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            else
                MessageBox.Show(_userVO.email ,"Result",
			MessageBoxButtons.OK,MessageBoxIcon.Information);       
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}