This are two static classes which are highly useful in programming
1) SQLManager.cs
This file serves all sql related transaction just like SQL Helper.
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 System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
/// <summary>
/// Created By: Anand Desai
/// Purpose: Personalized SQL Helper
/// </summary>
public class SqlManager
{
public SqlManager()
{
}
public struct ParameterArray
{
public string strParamName;
public string strSourceColumn;
public SqlDbType sqlDbType;
public int intSize;
public ParameterDirection direction;
public object objValue;
}
public static SqlConnection OpenConnection(SqlConnection sqlConnection)
{
sqlConnection = new SqlConnection(ConfigurationSettings.AppSettings[“ConnString”].ToString()); // YOur ConnectionString will come here
sqlConnection.Open();
return sqlConnection;
}
public static SqlConnection OpenConnection()
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection(ConfigurationSettings.AppSettings[“ConnString”].ToString()); // YOur ConnectionString will come here
sqlConnection.Open();
return sqlConnection;
}
public static SqlDataReader ExecuteSql(string sql, SqlDataReader reader)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = OpenConnection(sqlConnection);
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
public static SqlDataReader ExecuteSql(string sql)
{
SqlConnection sqlConnection = new SqlConnection();
SqlDataReader reader;
sqlConnection = OpenConnection(sqlConnection);
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
public static DataTable ExecuteSqlDataTable(string sql)
{
DataTable dTable = new DataTable();
SqlConnection sqlConnection = new SqlConnection();
DataSet dataset = new DataSet();
sqlConnection = OpenConnection(sqlConnection);
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
da.Fill(dataset);
da.Dispose();
sqlConnection.Close();
dTable = dataset.Tables[0];
dataset.Dispose();
return dTable;
}
public static DataTable ExecuteSqlDataTable(string sql, DataTable dTable)
{
SqlConnection sqlConnection = new SqlConnection();
DataSet dataset = new DataSet();
sqlConnection = OpenConnection(sqlConnection);
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
da.Fill(dataset);
da.Dispose();
sqlConnection.Close();
dTable = dataset.Tables[0];
dataset.Dispose();
return dTable;
}
public static SqlDataReader ExecuteSql(string sql, SqlConnection sqlConnection, SqlTransaction sqlTransaction, SqlDataReader reader)
{
//SqlConnection sqlConnection = OpenConnection();
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection, sqlTransaction);
reader = sqlCommand.ExecuteReader();
return reader;
}
public static DataSet ExecuteSqlDataSet(string sql, DataSet dataSet)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = OpenConnection(sqlConnection);
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
da.Fill(dataSet);
da.Dispose();
sqlConnection.Close();
return dataSet;
}
public static DataSet ExecuteSqlDataSet(string sql, SqlConnection sqlConnection, SqlTransaction sqlTransaction, DataSet dataSet)
{
dataSet = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
da.Fill(dataSet);
da.Dispose();
return dataSet;
}
public static Object ExecuteScalar(string sql)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = OpenConnection(sqlConnection);
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
Object obj = sqlCommand.ExecuteScalar();
sqlConnection.Close();
return obj;
}
public static Object ExecuteScalar(string sql, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
// SqlConnection sqlConnection = OpenConnection();
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection, sqlTransaction);
Object obj = sqlCommand.ExecuteScalar();
// sqlConnection.Close();
return obj;
}
public static bool ExecuteNonResultSql(string sql)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = OpenConnection(sqlConnection);
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
int returnValue = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
return returnValue != -1 ? false : true;
}
public static bool ExecuteNonResultSqlSupplier(string sql, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
//SqlConnection sqlConnection = OpenConnection();
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection, sqlTransaction);
int returnValue = sqlCommand.ExecuteNonQuery();
//sqlConnection.Close();
return returnValue != 0 ? false : true;
}
public static bool ExecuteNonResultSql(string sql, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
//SqlConnection sqlConnection = OpenConnection();
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection, sqlTransaction);
int returnValue = sqlCommand.ExecuteNonQuery();
// sqlConnection.Close();
return returnValue != -1 ? false : true;
}
public static bool ExecuteNonResultSql_SP(string strCommandText, ParameterArray[] paramArray)
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand = PrepareCommand(strCommandText, paramArray);
int returnValue = sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
return returnValue != -1 ? true : false;
}
public static Object ExecuteScalar_SP(string strCommandText, ParameterArray[] paramArray)
{
SqlCommand sqlcmd = new SqlCommand();
sqlcmd = PrepareCommand(strCommandText, paramArray);
Object obj = sqlcmd.ExecuteScalar();
sqlcmd.Parameters.Clear();
return obj;
}
public static DataSet ExecuteDataSet_SP(string strCommandText, ParameterArray[] paramArray)
{
DataSet ds = new DataSet();
try
{
SqlCommand sqlcmd = new SqlCommand();
sqlcmd = PrepareCommand(strCommandText, paramArray);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlcmd);
sqlDa.Fill(ds);
sqlcmd.Parameters.Clear();
}
catch (Exception ex)
{
string strError = ex.Message;
}
return ds;
}
private static SqlCommand PrepareCommand(string strSqlText, ParameterArray[] param)
{
SqlCommand sqlCommand = new SqlCommand();
SqlParameter sqlParameter;
sqlCommand.CommandText = strSqlText;
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlConnection sqlconnect = new SqlConnection();
sqlconnect = OpenConnection(sqlconnect);
sqlCommand.Connection = sqlconnect;
for (int i = 0; i < param.Length; i++)
{
sqlParameter = new SqlParameter(param[i].strParamName, param[i].sqlDbType, param[i].intSize, param[i].strSourceColumn);
sqlParameter.Value = param[i].objValue;
sqlParameter.Direction = param[i].direction;
sqlCommand.Parameters.Add(sqlParameter);
}
return sqlCommand;
}
public static DataSet ExecuteSqlDataSet(string sql)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection = OpenConnection(sqlConnection);
DataSet dataset = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection);
da.Fill(dataset);
da.Dispose();
sqlConnection.Close();
return dataset;
}
}
2) Utility.cs
This file serves all utilities like safe string convert or integer convert, date format, month counting etc.
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;
/// <summary>
/// Created By: Anand Desai
/// Purpose: To personalize Utilites management
/// </summary>
///
public class Utility
{
public Utility()
{
}
}
public sealed class Util
{
private Util()
{
}
public static int GetInteger(bool b)
{
return b ? 1 : 0;
}
public static bool GetBoolean(object str)
{
return (str == null || str == DBNull.Value) ? false : Convert.ToBoolean(str);
}
public static string GetString(object str)
{
return (str == null || str == DBNull.Value) ? string.Empty : str.ToString().Trim();
}
public static int GetInteger(object str)
{
return (str == null || str == DBNull.Value) ? 0 : Convert.ToInt32(str);
}
public static DateTime GetDateTime(object str)
{
return (str == null || str == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(str);
}
public static Single GetSingle(object str)
{
return (str == null || str == DBNull.Value) ? Convert.ToSingle(0.0) : Convert.ToSingle(str);
}
public static string GetRelpace(string str)
{
if (str != string.Empty && str != null)
{
str = str.Replace(“&”, “&”);
str = str.Replace(“‘”, “”);
//str = str.Replace(“‘”,”‘”);
str = str.Replace(“<“, “<”);
str = str.Replace(“>”, “>”);
str = str.Replace(“\n”, “”);
str = str.Replace(“\r”, ” “);
str = str.Replace(“-“, “”);
}
return str;
}
public static string DateTimeFormat(DateTime Date)
{
string Day, month, year, FormattedDate;
Day = Date.Day.ToString();
month = Date.Month.ToString();
//year = Date.Year.ToString().Remove(0,2);
year = Date.Year.ToString();
if (month.Length <= 1)
month = “0” + Date.Month.ToString();
if (Day.Length > 1)
FormattedDate = Day + “/” + month + “/” + year;
else
FormattedDate = “0” + Day + “/” + month + “/” + year;
return FormattedDate;
}
public static string DisplayMonth(int Month)
{
switch (Month)
{
case 1:
return “Jan”;
case 2:
return “Feb”;
case 3:
return “Mar”;
case 4:
return “Apr”;
case 5:
return “May”;
case 6:
return “Jun”;
case 7:
return “Jul”;
case 8:
return “Aug”;
case 9:
return “Sep”;
case 10:
return “Oct”;
case 11:
return “Nov”;
case 12:
return “Dec”;
default:
return “Jan”;
}
}
}
public class ConvertUtility
{
public static string ToString(bool b)
{
return b ? “1” : “0”;
}
/* public static string ToString(int i)
{
return XmlConvert.ToString(i);
}*/
public static int ToInt32(bool b)
{
return b ? 1 : 0;
}
public static bool ToBool(string str)
{
return str == “1”;
}
/* public static int ToInt32(string str)
{
return XmlConvert.ToInt32(str);
}*/
}
Interview Questions 