Thursday, 19 January 2017

C# - SQL Class Implementation

Hi All, Here LWebCode's class to directly run SQL statements whitout declare and initialize Connection and Command objects.
The class works for aspx page and also for Windows Forms, 
in windows forms remember to change initial parameters like connection string, CodeID ect..

Very useful to write less code
Optionally in 2 ArrayList we can pass parameters and relative values

Class usage Example:

//Put this at top of your *.cs file in aspx page, or Windows Form Class
using LWebCode;

//simple calling function 
public void Calls_iSQL_Class()
{
string cnString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
string sCodeID = Request.QueryString["qryPar1"].ToString();
string sDate = Request.QueryString["qryPar2"].ToString();
ArrayList aFields = new ArrayList();
ArrayList aValues = new ArrayList();

                    aFields.Add("@CODEID");
                    aFields.Add("@DATE"); 


                    aValues.Add(sCodeID);
                    aValues.Add(sDate);

//Performing SQL INSERT and UPDATE Statement:
//Call Example with Parameters more secure          
int res = iSQL.ExecuteNonQuery(cnString , "INSERT INTO MY_TABLE (CODE,DATE_R) VALUES (@CODEID,@DATE)",aFields ,aValues);   

//Call Example without Parameters less secure don't use it, or use it carrefully  recomended use for debugging SQL statement

int res2 = iSQL.ExecuteNonQuery(cnString , "UPDATE MY_TABLE SET DATE_R=NULL WHERE CODE=1235LWEBCODE", null , null);   

//Performing Fast Reading Value valid only for 1 record with SQL SELECT Statemet
string sres = iSQL.ExecuteReader(cnString , "SELECT TOP 1 DATE_R FROM MY_TABLE WHERE CODE=1235LWEBCODE", null , null);
                   
}

HERE THE LWebCode's CLASS:



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;
using System.Collections;

///
/// Class to execute sql statement like SELECT,UPDATE,INSERT other
/// whitout initialize connection and command
///
namespace LWebCode
{
public class iSQL
{
    public iSQL()
    {
       
    }

    ///
    /// Esegue un ExecuteNonQuery con un Singolo Parametro
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Field Add '@'
    /// Value
    ///
    public static int ExecuteNonQuery(string CnString,string SQL,string Campo,string Valore)
    {
         cn = new SqlConnection(CnString);
        cn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;

        cmd.Parameters.AddWithValue(Campo, Valore);
       
        cmd.CommandText = SQL;

        int ris =  cmd.ExecuteNonQuery();
        cn.Close();
        cn.Dispose();
        cmd.Dispose();
        return ris;
    }

    ///
    /// Execute ExecuteScalar with multiple Parameters
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array with @
    /// Values Array
    ///
    public static int ExecuteNonQuery(string CnString, string SQL, ArrayList Fields ,ArrayList Values)
    {
       
            SqlConnection cn = new SqlConnection(CnString);
            cn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;

            for (int i = 0; i < Fields.Count; i++)
            {
                cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
            }

            cmd.CommandText = SQL;

            int ris=cmd.ExecuteNonQuery();
           
            cn.Close();
            cn.Dispose();
            cmd.Dispose();

            return ris;
    }

    ///
    /// Execute ExecuteScalar with multiple Parameters
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array
    /// Values Array
    ///
    public static int ExecuteScalar(string CnString, string SQL, ArrayList Fields, ArrayList Values)
    {

        SqlConnection cn = new SqlConnection(CnString);
        cn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;

        for (int i = 0; i < Fields.Count; i++)
        {
            cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
        }

        cmd.CommandText = SQL;

        int ris = Convert.ToInt32(cmd.ExecuteScalar());

        cn.Close();
        cn.Dispose();
        cmd.Dispose();

        return ris;
    }

    ///
    /// Execute Reader which return only 1 value
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array
    /// Values Array
    ///
    public static string ExecuteReader(string CnString, string SQL, ArrayList Fields, ArrayList Values)
    {
        SqlConnection cn = new SqlConnection(CnString);
        cn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandText = SQL;
        for (int i = 0; i < Fields.Count; i++)
        {
            cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
        }

        SqlDataReader rd;
        rd = cmd.ExecuteReader();
        string a = "";
        while (rd.Read())
        {
            a = rd[0].ToString();
        }
        cmd.Dispose();
        cn.Close();
        rd.Close();
        rd.Dispose();

        return a;
    }

   
}
}

No comments:

Post a Comment