473,467 Members | 1,512 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

Rahul Data Base

32 New Member
using System.IO;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Xml;
using System.Data.SqlClient;
using System;

using log4net;
using log4net.Config;

namespace ChatPublicLib.DataAccess
{
public class DataAccess
{
#region "Constant(s)"
/// <summary>
///
/// </summary>
protected static string connectionString = ConfigurationManager.AppSettings["SQLConnectionString"];
/// <summary>
///
/// </summary>
protected static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase. GetCurrentMethod().DeclaringType);
/// <summary>
///
/// </summary>
private static readonly string logConfig = ConfigurationManager.AppSettings["Log4Net"];

#endregion

#region "Constructor(s)"
/// <summary>
///
/// </summary>
public DataAccess()
{
DOMConfigurator.ConfigureAndWatch(new FileInfo(logConfig));
}

/// <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(DataSet v_ds, string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == v_ds)
v_ds = new DataSet();
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
cmd.ExecuteNonQuery();
adapter.Fill(v_ds, name);
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
adapter.Update(v_dt);
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val = cmd.ExecuteNonQuery();

return val;
}
}

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

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
object val = cmd.ExecuteScalar();
return val;
}
}
/// <summary>
///
/// </summary>
public void CloseConnection()
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
/*
if (log.IsInfoEnabled)
log.Info("[" + System.DateTime.Now.ToString() + "] DataAccessBase: CloseConnection:: Database Connection Closed. ");
*/
}
//}
}
/// <summary>
///
/// </summary>
public void BeginTransaction()
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
/*
if (log.IsInfoEnabled)
log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
*/
}


txn = conn.BeginTransaction();
}
/// <summary>
///
/// </summary>
public void CommitTransaction()
{
try
{
txn.Commit();
}
catch (System.Exception err)
{
throw err;
}
finally
{
this.CloseConnection();
}
}
/// <summary>
///
/// </summary>
public void RollbackTransaction()
{
if (txn != null)
txn.Rollback();
}
#endregion

}
}




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

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

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



try{
param[0].Value = SiteUserID;
dtNewsCategory = objDA.GetDataByReader(TABLE_NEWS_CATEGORY, "NewsSelectAllCategoryBySiteUserID", param);
return dtNewsCategory;
}
catch (Exception ex){
if (log.IsErrorEnabled)
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.GetLogger(System.Reflection.MethodBase. GetCurrentMethod().DeclaringType);
/// <summary>
/// Returns the file download path for save the compressed html files..
/// </summary>
protected static string filedownloadpath = ConfigurationManager.AppSettings["FileDownloadPath"];

private static readonly string logConfig = ConfigurationManager.AppSettings["Log4Net"];
/// <summary>
/// Contains the table Name of NewsCategory
/// </summary>
private static string TABLE_NEWS_CATEGORY = "NewsCategory";
/// <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 = "SiteUserNews";
#endregion
#endregion

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

<connectionStrings>
<add name="ChatConnectionString1" connectionString="Data Source=SERVER\DEV;Initial Catalog=Chat;Persist Security Info=True;User ID=sa;MultipleActiveResultSets=False;Packet Size=4096;Application Name=&quot;Microsoft SQL Server Management Studio&quot;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Apr 10 '08 #1
2 1509
ashish1985s
32 New Member
using System.IO;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Xml;
using System.Data.SqlClient;
using System;

using log4net;
using log4net.Config;

namespace ChatPublicLib.DataAccess
{
public class DataAccess
{
#region "Constant(s)"
/// <summary>
///
/// </summary>
protected static string connectionString = ConfigurationManager.AppSettings["SQLConnectionString"];
/// <summary>
///
/// </summary>
protected static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase. GetCurrentMethod().DeclaringType);
/// <summary>
///
/// </summary>
private static readonly string logConfig = ConfigurationManager.AppSettings["Log4Net"];

#endregion

#region "Constructor(s)"
/// <summary>
///
/// </summary>
public DataAccess()
{
DOMConfigurator.ConfigureAndWatch(new FileInfo(logConfig));
}

/// <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(DataSet v_ds, string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == v_ds)
v_ds = new DataSet();
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
cmd.ExecuteNonQuery();
adapter.Fill(v_ds, name);
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
adapter.Update(v_dt);
}
}
}
}
finally
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
//if (log.IsInfoEnabled)
// 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(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val = cmd.ExecuteNonQuery();

return val;
}
}

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

using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
object val = cmd.ExecuteScalar();
return val;
}
}
/// <summary>
///
/// </summary>
public void CloseConnection()
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
/*
if (log.IsInfoEnabled)
log.Info("[" + System.DateTime.Now.ToString() + "] DataAccessBase: CloseConnection:: Database Connection Closed. ");
*/
}
//}
}
/// <summary>
///
/// </summary>
public void BeginTransaction()
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
/*
if (log.IsInfoEnabled)
log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
*/
}


txn = conn.BeginTransaction();
}
/// <summary>
///
/// </summary>
public void CommitTransaction()
{
try
{
txn.Commit();
}
catch (System.Exception err)
{
throw err;
}
finally
{
this.CloseConnection();
}
}
/// <summary>
///
/// </summary>
public void RollbackTransaction()
{
if (txn != null)
txn.Rollback();
}
#endregion

}
}




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

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

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



try{
param[0].Value = SiteUserID;
dtNewsCategory = objDA.GetDataByReader(TABLE_NEWS_CATEGORY, "NewsSelectAllCategoryBySiteUserID", param);
return dtNewsCategory;
}
catch (Exception ex){
if (log.IsErrorEnabled)
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.GetLogger(System.Reflection.MethodBase. GetCurrentMethod().DeclaringType);
/// <summary>
/// Returns the file download path for save the compressed html files..
/// </summary>
protected static string filedownloadpath = ConfigurationManager.AppSettings["FileDownloadPath"];

private static readonly string logConfig = ConfigurationManager.AppSettings["Log4Net"];
/// <summary>
/// Contains the table Name of NewsCategory
/// </summary>
private static string TABLE_NEWS_CATEGORY = "NewsCategory";
/// <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 = "SiteUserNews";
#endregion
#endregion

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

<connectionStrings>
<add name="ChatConnectionString1" connectionString="Data Source=SERVER\DEV;Initial Catalog=Chat;Persist Security Info=True;User ID=sa;MultipleActiveResultSets=False;Packet Size=4096;Application Name=&quot;Microsoft SQL Server Management Studio&quot;"
providerName="System.Data.SqlClient" />
</connectionStrings>

hirahuldoshirahhldoshi
Apr 10 '08 #2
Plater
7,872 Recognized Expert Expert
Did you have a question or did you just want to show off your code?
Apr 10 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
using dict for data attributes instead of self
by: Alexander Kervero | last post by:
Hi ,today i was reading diveinto python book,in chapter 5 it has a very generic module to get file information,html,mp3s ,etc. The code of the example is here :...
Python
28
data members as "protected"?
by: Act | last post by:
Why is it suggested to not define data members as "protected"? Thanks for help!
C / C++
2
Data base in PDA: how to improve the time of update?
by: Vicente Nicolau | last post by:
Hello I'm making a PDA project that uses a data base. That data base is saved in a xml file. When the application starts up, I load the xml file in memory. The application makes changes in the...
C# / C Sharp
5
Extract data from Crystal Report 7 to Access 2003 Secure
by: ElTipo | last post by:
Hello People, I made a data base with secure wizard to provide to users a PID and Passwords. I need to extract data from Crystal Reports 7 in this data base but Crystal Reports send me a message...
Microsoft Access / VBA
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++
0
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Online Marketing
1
The easy way to turn off automatic updates for Windows 10/11
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Windows Server
0
tracyyun
Discussion: How does Zigbee compare with other wireless protocols in smart home applications?
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
General
0
Couldn’t get equations in html when convert word .docx file to html file in C#.
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
C# / C Sharp
0
Trying to create a lan-to-lan vpn between two differents networks
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
Networking - Hardware / Configuration
0
Windows Forms - .Net 8.0
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
Visual Basic .NET
0
transfer the data from one system to another through ip address
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
C# / C Sharp

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes
How to Post and Respond on Bytes
How to Promote and Link on Bytes
How to increase your Ranking on Bytes
Become a Recognized Expert on Bytes
Feedback Welcomed! Contact Us