.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 >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Sample SQLHelper file

Posted By:Athira Sreejith       Posted Date: March 11, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  
 

Below is the class similar to SQLHelper.cs file. this code is written in c#.

using System;

using System.Data;

using System.Linq;

using System.Web;

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.Data.SqlClient;

using System.Web.Configuration;

using System.Web.Security;

using System.Configuration;

public class SQLHelper

{

private string strConnectionString = string.Empty;

public SQLHelper()

{

// strConnectionString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;

}

#region Common Function definition to execute any query

///

/// Create the connection with the DataBase using web.config Appsettings tag.

///

/// sqlconnection object

public SqlConnection connection()

{

webencrypt();

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].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 SqlDataReader 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 ExecuteDataSetSPDB(string ProcedureName, SqlParameter[] Parameters)

{

if (ProcedureName != "")

{

SqlConnection m_Scon = connection();

SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);

m_Scmd.CommandType = CommandType.StoredProcedure;

DataSet m_DSObj = new DataSet();

if (Parameters != null)

{

foreach (SqlParameter 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 SqlDataReader ExecuteReaderSPDB(string ProcedureName, SqlParameter[] Parameters)

{

if (ProcedureName != "")

{

SqlConnection m_Scon = connection();

SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);

m_Scmd.CommandType = CommandType.StoredProcedure;

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, SqlParameter[] Parameters)

{

if (ProcedureName != "")

{

SqlConnection m_Scon = connection();

SqlCommand m_Scmd = new SqlCommand(ProcedureName, m_Scon);

m_Scmd.CommandType = CommandType.StoredProcedure;

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, SqlParameter[] 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 "";

}

}

public static object webencrypt()

{

//*************** start ***********************

//Configuration config = WebConfigurationManager.OpenWebConfiguration("~\\Web.Config");

Configuration config = WebConfigurationManager.OpenWebConfiguration("~");

// Write the section name of web.config file (connectionStrings)

ConfigurationSection configSection = config.GetSection("connectionStrings");

// Check the section of web.config file (connectionStrings) if Protected

// then UnprotectSection (decrypt) the section

if (configSection.SectionInformation.IsProtected)

{

configSection.SectionInformation.UnprotectSection();

config.Save();

}

else

{

// Check the section of web.config file (connectionStrings) if UnprotectSection then protect (encrypt) Section the section

// configSection.SectionInformation.ProtectSection("DataProtectionConfigurationProvider");

configSection.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");

config.Save();

}

return config;

}

#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