Thursday, 12 September 2013

Class using System; using System.Collections.Generic; using System.Data; using System.Collections; using System.IO; using System.Web; using System.Web.UI.WebControls; /// /// Summary description for CommonClass /// namespace CommonClassMethod { public class CommonClass { /// /// Convert the HTML to XML format. /// /// The string XML. /// Convert Html To Xml Format public static string ConvertHtmlToXmlFormat(string xml) { if (!string.IsNullOrEmpty(Convert.ToString(xml))) { xml = xml.Replace("&", "&"); xml = xml.Replace("\"", """); xml = xml.Replace("'", "'"); xml = xml.Replace("<", "<"); xml = xml.Replace(">", ">"); } else { xml = string.Empty; } return xml; } public static string ConvertXmlChartFormat(string xml) { if (!string.IsNullOrEmpty(Convert.ToString(xml))) { xml = xml.Replace("&", ""); xml = xml.Replace("\"", ""); xml = xml.Replace("'", ""); xml = xml.Replace("<", ""); xml = xml.Replace(">", ""); xml = xml.Replace("!", ""); xml = xml.Replace("@", ""); xml = xml.Replace("#", ""); xml = xml.Replace("*", ""); xml = xml.Replace("|", ""); xml = xml.Replace("/", ""); xml = xml.Replace("?", ""); } else { xml = string.Empty; } return xml; } //Generate Array list for In Query public static string GenerateArrayList(ArrayList strArray) { string strList = ""; for (int i = 0; i <= strArray.Count - 1; i++) { if (i == 0) { strList = strList + "'" + strArray[i].ToString() + "'"; } else { strList = strList + ",'" + strArray[i].ToString() + "'"; } } return strList; } public string TrimText(object oItem) { string strText = ""; if (oItem != null) strText = TrimText(oItem.ToString()); return strText; } public void GetClientDetails(ref string strIPAddress, ref string strBrowser) { strIPAddress = TrimText(HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"]); if (strIPAddress == "127.0.0.1") strIPAddress = "Local"; strBrowser = TrimText(HttpContext.Current.Request.Browser.Type); } //Function to set Title Case public string TitleCase(string strText) { string strOutput = "", strTemp = ""; strText = TrimText(strText); string[] arrText = strText.Split(' '); int lngWords = arrText.Length; for (int i = 0; i < lngWords; i++) { strTemp = TrimText(arrText[i]); if (Len(strTemp) == 1) strTemp = UCase(Left(strTemp, 1)); else strTemp = UCase(Left(strTemp, 1)) + LCase(Mid(strTemp, 2, Len(strTemp))); if (strOutput != "") strOutput += " "; strOutput += strTemp; } return strOutput; } //Get specified length string at middle [Start and End Position] of given string public string Mid(string strText, int lngStartIndex, int lngLength) { int lngTextLen = 0; if (string.IsNullOrEmpty(strText) == true) strText = ""; lngTextLen = Len(strText); if (lngTextLen >= lngStartIndex) { if ((lngStartIndex + lngLength) > lngTextLen) lngLength = lngTextLen - (lngStartIndex - 1); if (lngLength > 0) strText = strText.Substring(lngStartIndex - 1, lngLength); } return strText; } //Get specified length string from right of given string public string Right(string strText, int lngLength) { int lngTextLen = 0; if (string.IsNullOrEmpty(strText) == true) strText = ""; lngTextLen = Len(strText); if (lngTextLen > lngLength) strText = strText.Substring(0, lngLength); return strText; } //Get specified length string from left of given string public string Left(string strText, int lngLength) { int lngTextLen = 0; if (string.IsNullOrEmpty(strText) == true) strText = ""; lngTextLen = Len(strText); if (lngTextLen > lngLength) strText = strText.Substring(0, lngLength); return strText; } //Get the length of given string public int Len(string strText) { if (string.IsNullOrEmpty(strText) == true) strText = ""; return strText.Length; } //Change string into lower case public string LCase(string strText) { if (string.IsNullOrEmpty(strText) == true) strText = ""; return strText.ToLower(); } //Change string into upper case public string UCase(string strText) { if (string.IsNullOrEmpty(strText) == true) strText = ""; return strText.ToUpper(); } //Checking for empty string and trim given input string public string TrimText(string strText) { if (string.IsNullOrEmpty(strText) == true) strText = ""; strText = strText.ToString().Trim(); return strText; } public static void Load_DropDownList(DropDownList dropDownListName, DataTable datatableData, string dataTextField, string dataValueField, string value, bool allowSelect, bool defaultSelect) { dropDownListName.DataTextField = dataTextField; dropDownListName.DataValueField = dataValueField; dropDownListName.DataSource = datatableData; dropDownListName.DataBind(); if (allowSelect) { if (defaultSelect) { dropDownListName.Items.Insert(0, new ListItem(":: Select ::", "")); } else { dropDownListName.Items.Insert(0, new ListItem("All", "0")); } } if (Convert.ToString(value) != string.Empty) { Load_DropDownList(dropDownListName, value); } } public static void Load_DropDownList(DropDownList ddlList, string assignValue) { int returnValue = 0; for (int i = 0; i < ddlList.Items.Count; i++) { ListItem lstItem = ddlList.Items[i]; if (Convert.ToString(lstItem.Value).Equals(assignValue)) { returnValue = i; break; } } ddlList.SelectedIndex = returnValue; } public static DataTable CreateDataTable(string[] columnNames) { string columnName; DataTable dataDynamicControls = new DataTable(); int noOfColums = columnNames.Length; for (int count = 0; count <= noOfColums - 1; count++) { columnName = columnNames[count].ToString(); DataColumn columnControls = new DataColumn(columnName); dataDynamicControls.Columns.Add(columnName); } return dataDynamicControls; } public static DataTable AddDatasToColumn(DataTable dataDynamicControls, string[] columnNames) { DataRow rowDynamicControls = dataDynamicControls.NewRow(); int noOfColums = columnNames.Length; for (int count = 0; count <= noOfColums - 1; count++) { string columnName = columnNames[count].ToString(); rowDynamicControls[columnName] = string.Empty; } dataDynamicControls.Rows.Add(rowDynamicControls); return dataDynamicControls; } public static DataTable AddDynamicDataToGrid(DataTable dataGridItems, GridView gridViewId, string[] controlIds, string[] columnNames) { int noOfControls = columnNames.Length; int gridRowCount = gridViewId.Rows.Count; for (int index = 0; index <= gridRowCount - 1; index++) { DataRow rowGridItems = dataGridItems.NewRow(); for (int count = 0; count <= noOfControls - 1; count++) { string controlId = controlIds[count].ToString(); if (gridViewId.Rows[index].FindControl(controlId).GetType() == typeof(TextBox)) { TextBox txtDynamic = new TextBox(); txtDynamic = (TextBox)gridViewId.Rows[index].FindControl(controlId); rowGridItems[columnNames[count].ToString()] = txtDynamic.Text.Trim(); } else if (gridViewId.Rows[index].FindControl(controlId).GetType() == typeof(DropDownList)) { DropDownList chdynamic = new DropDownList(); chdynamic = (DropDownList)gridViewId.Rows[index].FindControl(controlId); rowGridItems[columnNames[count].ToString()] = chdynamic.SelectedValue.Trim(); } else if (gridViewId.Rows[index].FindControl(controlId).GetType() == typeof(HiddenField)) { HiddenField hdndynamic = new HiddenField(); hdndynamic = (HiddenField)gridViewId.Rows[index].FindControl(controlId); rowGridItems[columnNames[count].ToString()] = hdndynamic.Value; } } dataGridItems.Rows.Add(rowGridItems); } return dataGridItems; } public static void BindGrid(GridView gridview, DataTable datatable) { gridview.DataSource = datatable; gridview.DataBind(); } public static void SessionExpiry_SGID() { string Empsgid = Convert.ToString(HttpContext.Current.Session["uid"]); string EmpUserid = Convert.ToString(HttpContext.Current.Session["UserAuthentication"]); if ((EmpUserid == "" || EmpUserid == "0" || EmpUserid == null) && (Empsgid == "" || Empsgid == "0" || Empsgid == null)) { //if (Empsgid == "" || Empsgid == "0" || Empsgid == null) //{ HttpContext.Current.Response.Redirect("SessionExpire.aspx"); //} } //else if(Empsgid== "") //{ // if (EmpUserid == "" || EmpUserid == "0" || EmpUserid == null) // { // HttpContext.Current.Response.Redirect("SessionExpire.aspx"); // } //} } } }
DataLayer namespace DataAccessLayer { using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Globalization; using System.Text; /// /// Represent the Description of the DataAccess. /// public sealed class DataAccessProvider { #region Variable declaration. /// /// Connection string. /// private static string connectionString; /// /// Connectionn Object. /// private static SqlConnection conn; /// /// Command Object. /// private static SqlCommand command; /// /// Adapte Object. /// private static SqlDataAdapter adapter; /// /// Transaction Object. /// private static SqlTransaction transaction; /// /// Simple Object. /// private static object result; #endregion /// /// Prevents a default instance of the class from being created. /// private DataAccessProvider() { } /// /// Gets or sets connection string. /// /// Connection string as string public static string ConnectionString { get { if (String.IsNullOrEmpty(connectionString)) { try { connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString(); } catch (NullReferenceException) { connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ToString(); } } else if (String.IsNullOrEmpty(connectionString)) { AppSettingsReader objConfig = new AppSettingsReader(); connectionString = objConfig.GetValue("connectionString", typeof(string)).ToString(); } return connectionString; } set { connectionString = value; } } /// /// Return the Collection of the record against Query. /// /// The object of the Query. /// The dataset of the Result set. public static DataSet GetDataSet(Query query) { try { PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters); adapter = new SqlDataAdapter(command); if (query.DataSet == null) { query.DataSet = new DataSet(); query.DataSet.Locale = CultureInfo.InvariantCulture; } conn.Open(); if (!String.IsNullOrEmpty(query.DataSetTableName)) { if ((query.DataSet.Tables.Count == 0) || (query.DataSet.Tables[query.DataSetTableName] == null)) { query.DataSet.Tables.Add(query.DataSetTableName); } adapter.Fill(query.DataSet, query.DataSetTableName); } else { adapter.Fill(query.DataSet); } } catch (MyException) { throw; } catch (Exception) { throw; } finally { if (conn != null) { conn.Close(); if (adapter != null) { adapter.Dispose(); } if (command != null) { command.Dispose(); } conn.Dispose(); conn = null; connectionString = string.Empty; } } return query.DataSet; } /// /// Return the Collecton of records against Query. /// /// The object the query. /// The datatable of recordset. public static DataTable GetDataTable(Query query) { try { PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters); adapter = new SqlDataAdapter(command); if (query.DataTable == null) { query.DataTable = new DataTable(); query.DataTable.Locale = CultureInfo.InvariantCulture; } conn.Open(); adapter.Fill(query.DataTable); } catch (MyException) { throw; } catch (Exception) { throw; } finally { if (conn != null) { conn.Close(); if (adapter != null) { adapter.Dispose(); } if (command != null) { command.Dispose(); } conn.Dispose(); conn = null; connectionString = string.Empty; } } return query.DataTable; } /// /// Gets the value of specified colum in its native format. /// /// The object of the Query. /// The object of the column in its native format. public static object GetValues(Query query) { try { PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters); conn.Open(); SqlDataReader read = command.ExecuteReader(); while (read.Read()) { query.ReturnValue = read.GetValue(0); } read.Close(); read = null; } catch (MyException) { throw; } catch (Exception) { throw; } finally { if (conn != null) { conn.Close(); if (adapter != null) { adapter.Dispose(); } if (command != null) { command.Dispose(); } conn.Dispose(); conn = null; connectionString = string.Empty; } } return query.ReturnValue; } /// /// Excute the inset and update Query. /// /// The object of the Query. /// The first column of first rows in the result set. public static object ExecuteScalar(Query query) { try { PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters); conn.Open(); transaction = conn.BeginTransaction(); command.Transaction = transaction; result = command.ExecuteScalar(); PopulateOutPutParamValues(query.Parameters, command); transaction.Commit(); return result; } catch (MyException) { throw; } catch (Exception) { if (transaction != null) { transaction.Rollback(); } throw; } finally { if (conn != null) { conn.Close(); if (command != null) { command.Dispose(); } if (transaction != null) { transaction.Dispose(); transaction = null; } conn.Dispose(); conn = null; connectionString = string.Empty; } } } /// /// Excute the inset and update Query. /// /// The object of the Query. /// The Number of rows affected. public static int ExecuteNonQuery(Query query) { int result = 0; try { PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters); conn.Open(); transaction = conn.BeginTransaction(); command.Transaction = transaction; result = command.ExecuteNonQuery(); PopulateOutPutParamValues(query.Parameters, command); transaction.Commit(); return result; } catch (MyException) { throw; } catch (Exception) { if (transaction != null) { transaction.Rollback(); } throw; } finally { if (conn != null) { conn.Close(); if (command != null) { command.Dispose(); } if (transaction != null) { transaction.Dispose(); transaction = null; } conn.Dispose(); conn = null; connectionString = string.Empty; } } } /// /// Excute the set of query string againt connection. /// /// set of query string as arraylist /// The Number of row affected. public static int ExecuteNonQuery(ArrayList arrayQuery) { int result = 0; SqlConnection conn = null; SqlCommand command = null; Query query; // start the Ienum collection. if (conn == null) { conn = new SqlConnection(ConnectionString); } conn.Open(); if (command == null) { command = new SqlCommand(); transaction = conn.BeginTransaction(); command.Transaction = transaction; } try { IEnumerator objenum = arrayQuery.GetEnumerator(); objenum.MoveNext(); do { query = (Query)objenum.Current; PopulatCommandObject(conn, command, query.Name, query.Parameters); result = command.ExecuteNonQuery(); PopulateOutPutParamValues(command, query); command.Parameters.Clear(); } while (objenum.MoveNext()); transaction.Commit(); } catch (MyException) { throw; } catch (Exception) { if (transaction != null) { transaction.Rollback(); } throw; } finally { if (conn != null) { conn.Close(); if (command != null) { command.Dispose(); } if (transaction != null) { transaction.Dispose(); transaction = null; } conn.Dispose(); conn = null; connectionString = string.Empty; } } return result; } /// /// Build the command object. /// /// The connection object reference. /// The command object Reference. /// The Command Text. /// The parameter name as parameter list. private static void PopulatCommandObject(ref SqlConnection conn, ref SqlCommand command, string queryName, List parameters) { try { if (conn == null) { conn = new SqlConnection(ConnectionString); } command = new SqlCommand(queryName, conn); command.CommandType = CommandType.StoredProcedure; foreach (Parameter parameter in parameters) { SqlParameter param = new SqlParameter(); param.ParameterName = "@" + parameter.ParameterName; param.DbType = parameter.DbType; param.Value = parameter.Value; param.Direction = parameter.Direction; command.Parameters.Add(param); // command.Parameters.AddWithValue("@" + parameter.ParameterName, parameter.DbType.ToString()).Value = parameter.Value; } } catch (MyException) { throw; } catch (Exception) { throw; } } /// /// Build the command object. /// /// The connection object Reference. /// The command object Reference. /// The command text. /// The parameter name as parameter list. private static void PopulatCommandObject(SqlConnection conn, SqlCommand command, string queryName, List parameters) { try { command.Connection = conn; command.CommandText = queryName; command.CommandType = CommandType.StoredProcedure; foreach (Parameter parameter in parameters) { SqlParameter param = new SqlParameter(); param.ParameterName = "@" + parameter.ParameterName; param.DbType = parameter.DbType; param.Value = parameter.Value; param.Direction = parameter.Direction; command.Parameters.Add(param); } } catch (MyException) { throw; } catch (Exception) { throw; } } /// /// Build output parameter values. /// /// the object of IDbCommand interface. /// the object of the query. private static void PopulateOutPutParamValues(IDbCommand command, Query query) { query.Parameters.Clear(); // only return output and return type param from Storedprocedure. foreach (IDataParameter param in command.Parameters) { query.Parameters.Add(new Parameter(param.ParameterName, param.DbType, param.Value, param.Direction)); } } /// /// Build ouput parameter. /// /// the parameter of the parameter list. /// the object of the IDbcommand interface. private static void PopulateOutPutParamValues(List parameters, IDbCommand command) { parameters.Clear(); // only return output and return type param from Storedprocedure. foreach (IDataParameter param in command.Parameters) { parameters.Add(new Parameter(param.ParameterName, param.DbType, (command.Parameters[param.ParameterName] as IDataParameter).Value, param.Direction)); } } } }