Monthly Archives: September 2011

Sample DataAccess (ADO.NET)

using System;using System.Data;using System.Configuration;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using Microsoft.VisualBasic;using System.Collections;using System.Collections.Generic;using System.Diagnostics;using System.Data.SqlClient;namespace LondonHydro.Classes{    public class DataAccess    {        //After doing some reading on SQL connections, using a Private Shared variable will cause these errors:        //There is already an open DataReader associated with this Command which must be closed first        //Private Shared objSqlConnection As New SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DB_CONNECTION").ConnectionString)        public static DataSet FillDataSet(string Query, SqlParameterCollection ParamCollection, CommandType sCommandType)        {            System.Data.SqlClient.SqlConnection objSqlConnection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DB_CONNECTION"].ConnectionString);            System.Data.SqlClient.SqlDataAdapter objAdapter = null;            DataSet objDataSet = null;            System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand();            objSqlCommand.CommandType = sCommandType;            objSqlCommand.CommandText = Query;            foreach (SqlParameter SqlParam in ParamCollection)            {                SqlParameter LocalSqlParam = new SqlParameter(SqlParam.ParameterName, SqlParam.SqlDbType);                LocalSqlParam.Value = SqlParam.Value;                objSqlCommand.Parameters.Add(LocalSqlParam);            }            objAdapter = new System.Data.SqlClient.SqlDataAdapter();            objAdapter.SelectCommand = objSqlCommand;            objAdapter.SelectCommand.Connection = objSqlConnection;            objDataSet = new DataSet();            objAdapter.Fill(objDataSet);            objSqlCommand.Parameters.Clear();            return objDataSet;        }        /// <summary>        /// Fills the data set.        /// </summary>        /// <param name="Query">The query.</param>        /// <param name="ParamCollection">The param collection.</param>        /// <returns></returns>        public static DataSet FillDataSet(string Query, SqlParameterCollection ParamCollection)        {            return FillDataSet(Query, ParamCollection, CommandType.Text);        }        /// <summary>        /// Executes the non query.        /// </summary>        /// <param name="Query">The query.</param>        /// <param name="ParamCollection">The param collection.</param>        public static void ExecuteNonQuery(string Query, SqlParameterCollection ParamCollection, CommandType sCommandType)        {            System.Data.SqlClient.SqlConnection objSqlConnection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DB_CONNECTION"].ConnectionString);            System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand();            objSqlCommand.CommandText = Query;            foreach (SqlParameter SqlParam in ParamCollection)            {                SqlParameter LocalSqlParam = new SqlParameter(SqlParam.ParameterName, SqlParam.SqlDbType);                LocalSqlParam.Value = SqlParam.Value;                objSqlCommand.Parameters.Add(LocalSqlParam);            }            objSqlCommand.Connection = objSqlConnection;            objSqlCommand.CommandType = sCommandType;            objSqlConnection.Open();            try            {                objSqlCommand.ExecuteNonQuery();                objSqlConnection.Close();            }            catch (Exception ex)            {                objSqlConnection.Close();                throw ex;                //Finally            }        }        /// <summary>        /// Executes the non query.        /// </summary>        /// <param name="Query">The query.</param>        /// <param name="ParamCollection">The param collection.</param>        public static void ExecuteNonQuery(string Query, SqlParameterCollection ParamCollection)        {            ExecuteNonQuery(Query, ParamCollection, CommandType.Text);        }        /// <summary>        /// Empties the string on DB null.        /// </summary>        /// <param name="obj">The obj.</param>        /// <returns></returns>        public static string EmptyStringOnDBNull(object obj)        {            if ((!object.ReferenceEquals(obj, DBNull.Value)))            {                return obj.ToString();            }            else            {                return "";            }        }        /// <summary>        /// Zeroes the on DB null.        /// </summary>        /// <param name="obj">The obj.</param>        /// <returns></returns>        public static int ZeroOnDBNull(object obj)        {            if ((!object.ReferenceEquals(obj, DBNull.Value)))            {                return int.Parse(obj.ToString());            }            else            {                return 0;            }        }        /// <summary>        /// Dates the time now on DB null.        /// </summary>        /// <param name="obj">The obj.</param>        /// <returns></returns>        public static DateTime DateTimeNowOnDBNull(object obj)        {            if ((!object.ReferenceEquals(obj, DBNull.Value)))            {                return DateTime.Parse(obj.ToString());            }            else            {                return DateTime.Now;            }        }    }}

ADO.NET Read single row

Just use CommandBehavior.SingleRow as an argument in ExecuteReader()

string queryString = "SELECT TOP 100 ProductID, ListPrice, Name from Production.Product ORDER BY ListPrice DESC;";
using (SqlConnection conn = new SqlConnection(MyConnectionString))
{
    using (SqlCommand command = new SqlCommand(queryString, conn))
    {
        try
        {
            conn.Open();
            SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader[2]);
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            // handle error
        }
    }
}

ASP.NET Localization: set language/culture

protected override void InitializeCulture()
{
	 base.InitializeCulture();  
	 string CurrentLanguageCode = "fr"; 
	 
	// sample setting 
	if (CurrentLanguageCode == "fr") 
	{  
		System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(3084);  
		System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(3084); 
	} 
	else 
	{  
		System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(1033);  
		System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(1033); 
	}
}