.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> Validations >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Example of DataAccessLayer in ASP.NET

Posted By:Athira Sreejith       Posted Date: February 22, 2009    Points: 5    Category: Validations    URL: http://www.dotnetspark.com  
 

using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; /// /// Summary description for DAL /// public class DAL { public DAL() { // // TODO: Add constructor logic here // } #region Common Function definition to execute any query /// /// Create the connection with the DataBase using web.config Appsettings tag. /// /// sqlconnection object public SqlConnection connection() { SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); return con; } /// /// Used to execute query using ExecuteNonQuery() Method /// /// /// Boolean public bool ExecuteNonQueryDB(string Query) { if (Query != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(Query, m_Scon); m_Scon.Close(); m_Scon.Open(); int m_Sdr = m_Scmd.ExecuteNonQuery(); if (m_Sdr > 0) { if (m_Scon.State == ConnectionState.Open) { m_Scon.Close(); } return true; } else { if (m_Scon.State == ConnectionState.Open) { m_Scon.Close(); } return false; } } else { return false; } } /// /// Used to execute query using ExecuteReader() Method /// /// /// IDataReader public IDataReader ExecuteReaderDB(string Query) { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(Query, m_Scon); m_Scon.Close(); m_Scon.Open(); SqlDataReader m_Sdr = m_Scmd.ExecuteReader(CommandBehavior.CloseConnection); return m_Sdr; } /// /// Used to execute query using ExecuteDataSet() Method /// /// /// DataSet public DataSet ExecuteDataSetDB(string Query) { if (Query != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(Query, m_Scon); DataSet m_DSObj = new DataSet(); SqlDataAdapter adr = new SqlDataAdapter(m_Scmd); adr.Fill(m_DSObj); if (m_Scon.State == ConnectionState.Open) { m_Scon.Close(); } return m_DSObj; } else { return null; } } /// ///Used to execute the stored procedure and return DataSet /// /// /// ///DataSet public DataSet ExecuteDataSetDB(string ProcedureName, object[] Parameters) { if (ProcedureName != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon); DataSet m_DSObj = new DataSet(); if (Parameters != null) { foreach (object para in Parameters) { m_Scmd.Parameters.Add(para); } } SqlDataAdapter adr = new SqlDataAdapter(m_Scmd); adr.Fill(m_DSObj); if (m_Scon.State == ConnectionState.Open) { m_Scon.Close(); } return m_DSObj; } else { return null; } } /// /// Used to execute the stored procedure and return IDataReader /// /// /// /// IDataReader public IDataReader ExecuteReaderSPDB(string ProcedureName, object[] Parameters) { if (ProcedureName != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon); m_Scon.Close(); m_Scon.Open(); if (Parameters != null) { foreach (SqlParameter para in Parameters) { m_Scmd.Parameters.Add(para); } } SqlDataReader m_Sdr = m_Scmd.ExecuteReader(CommandBehavior.CloseConnection); return m_Sdr; } else { return null; } } /// /// Used to execute the stored procedure using executenonquery /// /// /// /// Boolean public bool ExecuteNonQuerySPDB(string ProcedureName, object[] Parameters) { if (ProcedureName != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon); m_Scon.Close(); m_Scon.Open(); if (Parameters != null) { foreach (SqlParameter para in Parameters) { m_Scmd.Parameters.Add(para); } } int rowsAffected = m_Scmd.ExecuteNonQuery(); if (rowsAffected > 0) { return true; } else { return false; } } else { return false; } } /// /// Used to execute stored procedure using executescalar method /// /// /// /// string public string ExecuteScalarSPDB(string ProcedureName, object[] Parameters) { if (ProcedureName != "") { SqlConnection m_Scon = connection(); SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon); m_Scon.Close(); m_Scon.Open(); if (Parameters != null) { foreach (SqlParameter para in Parameters) { m_Scmd.Parameters.Add(para); } } object rowsID = m_Scmd.ExecuteScalar(); return rowsID.ToString(); } else { return ""; } } #endregion }

     

Further Readings:

    Responses

    No response found. Be the first to respond this post

    Post Comment

    You must Sign In To post reply
    Find More code samples in C#, ASP.Net, Vb.Net and more Here

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend