Rahul Data Base

**ashish1985s** · Apr 10 '08, 05:31 PM

Originally posted by ashish1985s

using System.IO;
using System.Data;
using System.Collecti ons;
using System.Configur ation;
using System.Xml;
using System.Data.Sql Client;
using System;

using log4net;
using log4net.Config;

namespace ChatPublicLib.D ataAccess
{
public class DataAccess
{
#region "Constant(s )"
/// <summary>
///
/// </summary>
protected static string connectionStrin g = ConfigurationMa nager.AppSettin gs["SQLConnectionS tring"];
/// <summary>
///
/// </summary>
protected static readonly ILog log = LogManager.GetL ogger(System.Re flection.Method Base.GetCurrent Method().Declar ingType);
/// <summary>
///
/// </summary>
private static readonly string logConfig = ConfigurationMa nager.AppSettin gs["Log4Net"];

#endregion

#region "Constructor(s) "
/// <summary>
///
/// </summary>
public DataAccess()
{
DOMConfigurator .ConfigureAndWa tch(new FileInfo(logCon fig));
}

/// <summary>
///
/// </summary>
~DataAccess()
{

}
#endregion

#region "Attributes/Properties"
/// <summary>
///
/// </summary>
protected SqlConnection conn = null;
/// <summary>
///
/// </summary>
protected SqlTransaction txn = null;
/// <summary>
///
/// </summary>
public SqlTransaction Transaction
{
get
{
return txn;
}
set
{
txn = value;
}
}
#endregion

#region "Public Methods"
/// <summary>
///
/// </summary>
/// <param name="v_ds"></param>
/// <param name="name"></param>
/// <param name="cmdText"> </param>
/// <param name="cmdParms" ></param>
public void FillDataSet(Dat aSet v_ds, string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == v_ds)
v_ds = new DataSet();
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}
using (SqlCommand cmd = conn.CreateComm and())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Sto redProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters. Add(parm);
}
using (SqlDataAdapter adapter = new SqlDataAdapter( cmd))
{
cmd.ExecuteNonQ uery();
adapter.Fill(v_ ds, name);
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccessBase: CloseConnection :: Database Connection Closed. ");
}
}
}
/// <summary>
/// return DataTable
/// </summary>
/// <param name="name"></param>
/// <param name="cmdText"> </param>
/// <param name="cmdParms" ></param>
public DataTable GetDataByReader (string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateComm and())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Sto redProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters. Add(parm);
}
using (SqlDataReader dr = cmd.ExecuteRead er())
{
dt.Load(dr);
return dt;
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccessBase: CloseConnection :: Database Connection Closed. ");
}
}
}
/// <summary>
///
/// </summary>
/// <param name="cmdText"> </param>
/// <param name="v_dt"></param>
public void DataTableUpdate (string cmdText, DataTable v_dt)
{
try
{
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateComm and())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Transaction = this.txn;
using (SqlDataAdapter adapter = new SqlDataAdapter( cmd))
{
using (SqlCommandBuil der builder = new SqlCommandBuild er(adapter))
{
adapter.Update( v_dt);
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccessBase: CloseConnection :: Database Connection Closed. ");
}
}
}
/// <summary>
///
/// </summary>
/// <param name="cmdText"> </param>
/// <param name="cmdType"> </param>
/// <param name="cmdParms" ></param>
/// <returns></returns>
public int ExecuteNonQuery (string cmdText, SqlParameter[] cmdParms)
{
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateComm and())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters. Add(parm);
}
int val = cmd.ExecuteNonQ uery();

return val;
}
}

/// <summary>
///
/// </summary>
/// <param name="cmdText"> </param>
/// <param name="cmdType"> </param>
/// <param name="cmdParms" ></param>
/// <returns></returns>
public object ExecuteScalar(s tring cmdText, SqlParameter[] cmdParms)
{
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
//if (log.IsInfoEnab led)
// log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateComm and())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters. Add(parm);
}
object val = cmd.ExecuteScal ar();
return val;
}
}
/// <summary>
///
/// </summary>
public void CloseConnection ()
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
/*
if (log.IsInfoEnab led)
log.Info("[" + System.DateTime .Now.ToString() + "] DataAccessBase: CloseConnection :: Database Connection Closed. ");
*/
}
//}
}
/// <summary>
///
/// </summary>
public void BeginTransactio n()
{
if (null == conn)
{
conn = new SqlConnection(c onnectionString );
conn.Open();
/*
if (log.IsInfoEnab led)
log.Info("[" + System.DateTime .Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
*/
}

txn = conn.BeginTrans action();
}
/// <summary>
///
/// </summary>
public void CommitTransacti on()
{
try
{
txn.Commit();
}
catch (System.Excepti on err)
{
throw err;
}
finally
{
this.CloseConne ction();
}
}
/// <summary>
///
/// </summary>
public void RollbackTransac tion()
{
if (txn != null)
txn.Rollback();
}
#endregion

}
}

=============== ======

/// <summary>
/// For getting NewsCategoryID, CategoryName from NewsCategory table By SiteID
/// </summary>
/// <returns></returns>
/// <parameters>Sit eID </parameters>
/// <Remarks>Author :Ashish Shah Date: 2007/11/27</Remarks>
public DataTable GetNewsCategory (int SiteUserID)
{
DataTable dtNewsCategory = new DataTable();
DataAccess.Data Access objDA = new DataAccess.Data Access();

SqlParameter[] param ={
new SqlParameter("@ SiteUserID",Sql DbType.Int)};

try{
param[0].Value = SiteUserID;
dtNewsCategory = objDA.GetDataBy Reader(TABLE_NE WS_CATEGORY, "NewsSelectAllC ategoryBySiteUs erID", param);
return dtNewsCategory;
}
catch (Exception ex){
if (log.IsErrorEna bled)
log.Error("[" + System.DateTime .Now.ToString() + "] Nescontroller:: GetNewsCategory -", ex);
throw ex;
}
finally
{

if (dtNewsCategory != null)
{
dtNewsCategory. Dispose();
dtNewsCategory = null;
}
objDA = null;
}
}

=============== ==============
public class NewsController
{
#region "Static Member(s)"
#region Private Members
/// <summary>
///
/// </summary>
private static readonly ILog log = LogManager.GetL ogger(System.Re flection.Method Base.GetCurrent Method().Declar ingType);
/// <summary>
/// Returns the file download path for save the compressed html files..
/// </summary>
protected static string filedownloadpat h = ConfigurationMa nager.AppSettin gs["FileDownloadPa th"];

private static readonly string logConfig = ConfigurationMa nager.AppSettin gs["Log4Net"];
/// <summary>
/// Contains the table Name of NewsCategory
/// </summary>
private static string TABLE_NEWS_CATE GORY = "NewsCatego ry";
/// <summary>
/// Contains the table Name of NewsURL
/// </summary>
private static string TABLE_NEWS_URL = "NewsURL";
/// <summary>
/// Contains the table Name of SiteUserNews
/// </summary>
private static string TABLE_SITEUSER_ NEWS = "SiteUserNe ws";
#endregion
#endregion

=============== =============== =

<connectionStri ngs>
<add name="ChatConne ctionString1" connectionStrin g="Data Source=SERVER\D EV;Initial Catalog=Chat;Pe rsist Security Info=True;User ID=sa;MultipleA ctiveResultSets =False;Packet Size=4096;Appli cation Name="Micr osoft SQL Server Management Studio""
providerName="S ystem.Data.SqlC lient" />
</connectionStrin gs>

hirahuldoshirah hldoshi

**Plater** · Apr 10 '08, 06:37 PM

Did you have a question or did you just want to show off your code?

Rahul Data Base

Rahul Data Base

Comment

Comment