Querying a simple XML file using LINQ

The below demo shows how to query a simple xml file using LINQ.


First create an xml file as below and name it as Countries.xml

<?xml version="1.0" encoding="utf-8" ?>
<Countries>
  <Country Id ="1">
    India
  </Country>
  <Country Id ="2">
    U.S.A
  </Country>
  <Country Id ="3">
    China
  </Country>
  <Country Id ="4">
    U.K
  </Country>
</Countries>

Then write the code as below to query xml

using System;
using System.Linq;
using System.Xml.Linq;


public partial class Linq_QueryXMLUsingLINQ : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    XElement xEle = XElement.Load(@"D:\Countries.xml");

    var list = from l in xEle.Elements("Country")
               where ((string)l.Element("Name")).Trim() == "India"
               select l.Element("Name");

    foreach (var item in list)
    {
      Response.Write(item);
    }
  }
}

Hello LINQ

Below is a simple demo of LINQ which prints all the items in the string array.

using System;
using System.Linq;

public partial class Linq_HelloLinq : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    string[] greetings = { "Hello World", "Hello LINQ", "Hello" };

    var greetingsList = from l in greetings select l;

    foreach (var greeting in greetingsList)
    {
      Response.Write(greeting + "<br>");
    }
  }
}

Important things to note are:
1. using System.Linq;
2. var greetingsList = from l in greetings select l;

Rest all is pretty straight forward.

After you are done with doing the above, try adding a where condition to it as below.

using System;
using System.Linq;

public partial class Linq_HelloLinq : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    string[] greetings = { "Hello World", "Hello LINQ", "Hello" };

    var greetingsList = from l in greetings where l.EndsWith("World") select l;

    foreach (var greeting in greetingsList)
    {
      Response.Write(greeting + "<br>");
    }
  }
}

Thats it here ends our simple linq demo.

SqlHelper

//===============================================================================
// Author: Arun Kumar Endapally
// Created on: 14th June 2010
// Description: Util class to perform repetitive database operations
//===============================================================================

using System;

using System.Configuration;

using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace Util
{
    public class SqlHelper
    {

        // Gets the connection string from web.config or app.config settings value.
        private string connectionString = ConfigurationManager.AppSettings["conString"];
        // Property which can be used to get/set the connection string.
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        // Sql connection
        private SqlConnection connection;
        public SqlConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        // Sql transaction
        private SqlCommand command;

        // Sql transaction
        private SqlTransaction transaction;

        #region Private methods

        // Sends an email to admin when any error occurs.
        private static void SendMailOnError(Exception ex)
        {
            //TODO: Write code to send email when exception occurs.
        }

        /// <summary>
        /// Writes the error occured into the log file.
        /// </summary>
        /// <param name="ex">Exception object which has all the information about the exception</param>
        private static void WriteErrorLog(Exception ex)
        {
            try
            {
                // creates a folder with name "ErrorLogs" in the base directory of the application.
                //string folder = AppDomain.CurrentDomain.BaseDirectory + "ErrorLogs\\";

                // creates a folder with name "ErrorLogs" in a location specified as settings value in web.config or app.config.
                string folder = ConfigurationManager.AppSettings["sqlErrorLogLocation"] + "ErrorLogs\\";

                DirectoryInfo dir = new DirectoryInfo(folder);
                // check if the directory exists
                if (!dir.Exists)
                {
                    // creates the directory
                    dir.Create();
                }

                // appending all the error info to error string builder.
                StringBuilder error = new StringBuilder();
                error.Append("Error Time: ").Append(DateTime.Now.Hour).Append(":").Append(DateTime.Now.Minute).Append(Environment.NewLine);
                error.Append("Error Message: ").Append(ex.Message).Append(Environment.NewLine);
                error.Append("Stack Trace: ").Append(Environment.NewLine).Append(ex.StackTrace).Append(Environment.NewLine);
                error.Append("------------------------------------------------------------------------------").Append(Environment.NewLine).Append(Environment.NewLine);

                // Get the current date in dd-MMM-yyyy format and use it as the error log text file name.
                string sErrorTime = DateTime.Now.ToString("dd-MMM-yyyy");

                // intialize stream writer with path and setting not to overwrite.
                StreamWriter sw = new StreamWriter(folder + sErrorTime + ".txt", true);
                sw.WriteLine(error.ToString());
                sw.Flush();
                sw.Close();

            }
            catch (Exception exc)
            {
                throw exc;
            }
            throw ex;
        }

        /// <summary>
        /// Attaches array of SqlParameter to the SqlCommand object
        /// if the value of the SqlParameter is null then it will replace the value with DBNull.Value
        /// </summary>
        /// <param name="command">SqlCommand object to which we attach parameters</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        private void AttachParameters(SqlParameter[] commandParameters)
        {
            try
            {
                // picking each parameter from the parameters array.
                foreach (SqlParameter p in commandParameters)
                {
                    // check if parameter value is null.
          if (p.Value == null || p.Value == "")
                    {
                        // if the parameter value is null then replace it with DBNull.Value.
                        p.Value = DBNull.Value;
                    }
                    // adding the parameters to command object.
                    command.Parameters.Add(p);
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// prepares the command for execution.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        private void PrepareCommand(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            try
            {
                // check if connection exists and opened.
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // create a command object.
                    command = new SqlCommand();
                    // set connection.
                    command.Connection = connection;
                    // set command text.
                    command.CommandText = commandText;
                    // set command type.
                    command.CommandType = cmdType;
                    // check if transaction exists.
                    if (transaction != null)
                    {
                        // set transaction.
                        command.Transaction = transaction;
                    }
                    // check if command parameters exists.
                    if (commandParameters != null)
                    {
                        // attach the parameters to command object.
                        AttachParameters(commandParameters);
                    }
                }
                else
                {
                    // exception is thrown if connection is not created or not open.
                    throw new Exception("Connection is not created/open");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// To open the database connection for performing any database operation.
        /// </summary>
        public void OpenConnection()
        {
            try
            {
                // created new connection object with the connection string retrieved from settings.
                connection = new SqlConnection(connectionString);
                // check if the connection is already open.
                if (connection.State != ConnectionState.Open)
                {
                    // connection is opened
                    connection.Open();
                }
                else
                {
                    // expception is thrown if the connection is already opened.
                    throw new Exception("Connection is already open.");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// To close the database connection after performing the database operations.
        /// </summary>
        public void CloseConnection()
        {
            try
            {
                // check if connection exists and opened.
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // close the connection.
                    connection.Close();
                    // set the connection object to null i.e. clear.
                    connection = null;
                }
                else
                {
                    // exception is thrown if connection is not created or not open.
                    throw new Exception("Connection is not created/open");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// begins a transaction for performing series of database operations.
        /// </summary>
        public void BeginTransaction()
        {
            try
            {
                // check if connection exists and opened.
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // check if transaction does not exists.
                    if (transaction == null)
                    {
                        // begin transaction.
                        transaction = connection.BeginTransaction();
                    }
                    else
                    {
                        // exception is thrown if transaction ha.s already begin/exists.
                        throw new Exception("Transaction has already begin");
                    }
                }
                else
                {
                    // exception is thrown if connection does not exists or not opened.
                    throw new Exception("Connection is not created/open");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// commits a transaction after performing series of database operations.
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                // check if connection exists and opened.
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // check if transaction exists
                    if (transaction != null)
                    {
                        // commiting the transaction
                        transaction.Commit();
                        transaction = null;
                    }
                    else
                    {
                        // exception is thrown if transaction does not exists or not begined.
                        throw new Exception("Transaction has not begin");
                    }
                }
                else
                {
                    // exception is thrown if connection does not exists or not opened.
                    throw new Exception("Connection is not created/open");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// roll backs transaction if any error occurs any where in performing series of database operations.
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                // check if connection exists and opened.
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // check if transaction exists.
                    if (transaction != null)
                    {
                        // roll back the transaction.
                        transaction.Rollback();
                        transaction = null;
                    }
                    else
                    {
                        // exception is thrown if transaction does not exists or not begined.
                        throw new Exception("Transaction has not begin");
                    }
                }
                else
                {
                    // exception is thrown if connection does not exists or not opened.
                    throw new Exception("Connection is not created/open");
                }
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
        }

        /// <summary>
        /// Executes command, takes no SqlParameters and returns the number of rows affected.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <returns>returns number of row(s) affected</returns>
        public int ExecuteNonQuery(string commandText, CommandType cmdType)
        {
            // calling the overloaded method with null in place of SqlParameter array.
            return ExecuteNonQuery(commandText, cmdType, (SqlParameter[])null);
        }

        /// <summary>
        /// Executes command with parameters and returns the number of rows affected.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        /// <returns>returns number of row(s) affected</returns>
        public int ExecuteNonQuery(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            try
            {
                int result = 0;
                // prepare the command for execution
                PrepareCommand(commandText, cmdType, commandParameters);
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
            return 0;
        }

        /// <summary>
        /// Executes command, takes no SqlParameters and returns dataset.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <returns>returns dataset</returns>
        public DataSet ExecuteDataSet(string commandText, CommandType cmdType)
        {
            // calling the overloaded method with null in place of SqlParameter array.
            return ExecuteDataSet(commandText, cmdType, (SqlParameter[])null);
        }

        /// <summary>
        /// Executes command with parameters and returns dataset.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        /// <returns>returns dataset</returns>
        public DataSet ExecuteDataSet(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            try
            {
                // prepare the command for execution
                PrepareCommand(commandText, cmdType, commandParameters);
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
                command.Parameters.Clear();
                return ds;
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
            return null;
        }

        /// <summary>
        /// Executes command, takes no SqlParameters and returns datatable.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <returns>returns datatable</returns>
        public DataTable ExecuteDataTable(string commandText, CommandType cmdType)
        {
            // calling the overloaded method with null in place of SqlParameter array.
            return ExecuteDataTable(commandText, cmdType, (SqlParameter[])null);
        }

        /// <summary>
        /// Executes command with parameters and returns datatable.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        /// <returns>returns datatable</returns>
        public DataTable ExecuteDataTable(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            try
            {
                DataSet ds = new DataSet();
                // get the dataset
                ds = ExecuteDataSet(commandText, cmdType, commandParameters);
                DataTable dt = new DataTable();
                if (ds != null && ds.Tables.Count > 0)
                {
                    // set the datatable with the datatable in the dataset
                    dt = ds.Tables[0];
                }
                command.Parameters.Clear();
                return dt;
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
            return null;
        }

        /// <summary>
        /// Executes command, takes no SqlParameters and returns object.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <returns>returns object</returns>
        public object ExecuteScalar(string commandText, CommandType cmdType)
        {
            // calling the overloaded method with null in place of SqlParameter array.
            return ExecuteScalar(commandText, cmdType, (SqlParameter[])null);
        }

        /// <summary>
        /// Executes command, takes no SqlParameters and returns object.
        /// </summary>
        /// <param name="commandText">command text, can be a text query ex: "SELECT * FROM DemoTable"
        /// or name of the stored procedure ex: "SPGetDetails".</param>
        /// <param name="cmdType">command type, specifying whether command text is text query or stored procedure name
        /// ex: CommandType.Text or CommandType.StoredProcedure</param>
        /// <param name="commandParameters">array of parameters to attach to SqlCommand object</param>
        /// <returns>returns object</returns>
        public object ExecuteScalar(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            try
            {
                object result = null;
                // prepare the command for execution
                PrepareCommand(commandText, cmdType, commandParameters);
                result = command.ExecuteScalar();
                command.Parameters.Clear();
                return result;
            }
            catch (Exception ex)
            {
                // write a log regarding the exception.
                WriteErrorLog(ex);
            }
            return null;
        }

        #endregion
    }
}

 

 

Wiki for SqlHelper

What is SqlHelper?

SqlHelper is a utility class which contains public methods ExecuteNonQuery, ExecuteDataSet, ExecuteDataTable and ExecuteScalar. Using those methods we can perform database operations like insert, delete, update and select.

What is the advantage of using SqlHelper?

By using SqlHelper we can perform a database operation by writing minimum lines of code.

How to set connection string?

You can follow either of the below two steps:

1. You can simply add app setting with key “conString” in web.config

<configuration>

<appSettings>

<add key="conString" value="Data Source=<servername>;Initial Catalog=<databasename>;User Id=<userid>;Password=<password>"/>

</appSettings>

</configuration>

Internally SqlHelper will fetch the connection string from the app settings as the below line of code is written inside it.

private string connectionString = ConfigurationManager.AppSettings["conString"];

2. Or by setting the property ConnectionString.

SqlHelper sqlHelper = new SqlHelper();

sqlHelper.ConnectionString = "Data Source=<servername>;Initial Catalog=<databasename>;User Id=<userid>;Password=<password>";

How to open and close connection?

SqlHelper sqlHelper = new SqlHelper();

sqlHelper.OpenConnection();

// Perform database operations.

sqlHelper.CloseConnection();

About ExecuteNonQuery method

ExecuteNonQuery is used to perform database operations like insert or update or delete. There are two overloaded methods for ExecuteNonQuery, they are:

1. public int ExecuteNonQuery(string commandText, CommandType cmdType)

2. public int ExecuteNonQuery(string commandText, CommandType cmdType, params SqlParameter[] commandParameters)

The second overloaded method accepts array of parameters to attach to SqlCommand object.

ExecuteNonQuery returns number of rows affected. 

Parameter “commandText” can be a text query ex: "DELETE FROM DemoTable WHERE Id = @Id" or name of the stored procedure ex: "spDelDemoTable". 

Parameter “cmdType” is to specify whether command text is text query or stored procedure name i.e. CommandType.Text or CommandType.StoredProcedure. 

Parameter “commandParameters” are array of parameters to attach to SqlCommand object.

 Example:
// inline query

string query = "DELETE FROM DemoTable WHERE Id = @Id";

// stored procedure name

// string spName =  "spDelDemoTable";

// create an instance of SqlParameter with paramerter name and its value

SqlParameter parId = new SqlParameter("@Id", "19");

// create an instance of SqlHelper

SqlHelper sqlHelper = new SqlHelper();

// open the connection

sqlHelper.OpenConnection();

// call ExecuteNonQuery and specify parameters command text, command type and sql parameter 

int result = sqlHelper.ExecuteNonQuery(query, CommandType.Text, parId);

// int result = sqlHelper.ExecuteNonQuery(spName, CommandType.StoredProcedure, parId);

// close the connection

sqlHelper.CloseConnection();

In the above example, a row is deleted from DemoTable with specific Id. We can use either inline query or stored procedure name as command text and specify the command type accordingly.

ExecuteNonQuery returns an integer i.e. number of rows affected.

About ExecuteDataSet method

ExecuteDataSet is used to retrieve data from database, it returns a DataSet. There are two overloaded methods for ExecuteDataSet, they are:

1. public DataSet ExecuteDataSet (string commandText, CommandType cmdType)

2. public DataSet ExecuteDataSet (string commandText, CommandType cmdType, params SqlParameter[] commandParameters) 

The second overloaded method accepts array of parameters to attach to SqlCommand object. 

Parameter “commandText” can be a text query ex: "SELECT * FROM DemoTable" or name of the stored procedure ex: "spGetDemoTable". 

Parameter “cmdType” is to specify whether command text is text query or stored procedure name i.e. CommandType.Text or CommandType.StoredProcedure. 

Parameter “commandParameters” are array of parameters to attach to SqlCommand object.

Example:

// inline query

string query = "SELECT * FROM DemoTable";

// stored procedure name

// string spName =  "spGetDemoTable";

// create an instance of SqlHelper

SqlHelper sqlHelper = new SqlHelper();

// open the connection

sqlHelper.OpenConnection();

// call ExecuteDataSet and specify parameters command text, command type

DataSet dsResult = sqlHelper.ExecuteDataSet (query, CommandType.Text);

// DataSet dsResult = sqlHelper.ExecuteDataSet (spName, CommandType.StoredProcedure);

// close the connection

sqlHelper.CloseConnection();

In the above example, all the rows are retrieved from DemoTable. We can use either inline query or stored procedure name as command text and specify the command type accordingly.

ExecuteDataSet returns a DataSet which can be used to set as datasource for repeater, gridview, etc.

About ExecuteDataTable method

Same as ExecuteDataSet method,  ExecuteDataSet returns a DataSet but ExecuteDataTable returns a DataTable.

About ExecuteScalar method

ExecuteScalar is used to retrieve a single value from database, it returns an object. There are two overloaded methods for ExecuteScalar, they are:

1. public object ExecuteScalar (string commandText, CommandType cmdType)

2. public object ExecuteScalar (string commandText, CommandType cmdType, params SqlParameter[] commandParameters)

The second overloaded method accepts array of parameters to attach to SqlCommand object. 

Parameter “commandText” can be a text query ex: "SELECT COUNT(*) FROM DemoTable" or name of the stored procedure ex: "spGetRecCount". 

Parameter “cmdType” is to specify whether command text is text query or stored procedure name i.e. CommandType.Text or CommandType.StoredProcedure. 

Parameter “commandParameters” are array of parameters to attach to SqlCommand object.

Example:

// inline query

string query = " SELECT COUNT(*) FROM DemoTable";

// stored procedure name

// string spName =  "spGetRecCount";

// create an instance of SqlHelper

SqlHelper sqlHelper = new SqlHelper();

// open the connection

sqlHelper.OpenConnection();

// call ExecuteScalar and specify parameters command text, command type

object result = sqlHelper.ExecuteScalar (query, CommandType.Text);

// object result = sqlHelper.ExecuteScalar (spName, CommandType.StoredProcedure);

// close the connection

sqlHelper.CloseConnection();

In the above example, number of rows in DemoTable is retrieved. We can use either inline query or stored procedure name as command text and specify the command type accordingly.

ExecuteScalar returns an object which can be converted to int or string or to any other datatype and used accordingly.