Data Access Layer

Data Access Layer

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

public class DAL

{

    static SqlConnection con;

    static SqlCommand cmd;

    public DAL()

    {

    }

    public static string connection = (ConfigurationManager.ConnectionStrings["Connect"].ConnectionString);

    public static int GetFirstValue(String commandText, SqlParameter[] parameters)

    {

        using (SqlConnection con = new SqlConnection(connection))

        {

            cmd = new SqlCommand(commandText, con);

            cmd.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter p in parameters)

            {

                if (p.Value != null)

                {

                    cmd.Parameters.Add(p);

                }

            }

            con.Open();

            int result = 0;

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)

            {

                while (reader.Read())

                {

                    if (reader[0].ToString() != string.Empty)

                        result = Convert.ToInt32(reader[0].ToString());

                }

            }

            con.Close();

            return result;

        }

    }

    public static int executeNonquery_SP(String CommandText, SqlParameter[] parameters)

    {

        using (SqlConnection con = new SqlConnection(connection))

        {

            try

            {

                cmd = new SqlCommand(CommandText, con);

                cmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter p in parameters)

                {

                    if (p.Value != null)

                    {

                        cmd.Parameters.Add(p);

                    }

                }

                con.Open();

                int result = cmd.ExecuteNonQuery();

                if (result > 0)

                    return result;

                else

                    return 0;

            }

            catch (Exception ex)

            {

                throw new ArgumentException(ex.Message);

            }

            finally

            {

                con.Close();

            }

        }

    }

  

    public static DataTable Ret_datatable(String CommandText1, SqlParameter[] parameters)

    {

        con = new SqlConnection(connection);

        cmd = new SqlCommand(CommandText1, con);

        con.Open();

        cmd.CommandType = CommandType.StoredProcedure;

        foreach (SqlParameter c in parameters)

        {

            if (c.Value != null)

            {

                cmd.Parameters.Add(c);

            }

           

        }

        SqlDataAdapter da = new SqlDataAdapter(cmd);

      

        DataTable dt = new DataTable();

        da.Fill(dt);

       

        con.Close();

        return dt;

    }

    // Method that return a DataTable

    public static DataTable GetItems(String CommandText, SqlParameter[] parameters)

    {

        DataTable dt;

        using (SqlConnection con = new SqlConnection(connection))

        {

            cmd = new SqlCommand(CommandText, con);

            cmd.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter c in parameters)

            {

                if (c.Value != null)

                {

                    cmd.Parameters.Add(c);

                }

            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            dt = new DataTable();

            adapter.Fill(dt);

        }

        return dt;

    }

 

    public static string GetValue(String commandText, SqlParameter[] parameters)

    {

      

        string result = string.Empty;

        using (SqlConnection con = new SqlConnection(connection))

        {

            cmd = new SqlCommand(commandText, con);

            cmd.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter p in parameters)

            {

                if (p.Value != null)

                {

                    cmd.Parameters.Add(p);

                }

            }

            con.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)

            {

                while (reader.Read())

                {

                    if (reader[0].ToString() != string.Empty)

                        result = reader[0].ToString();

                }

                con.Close();

            }

            return result;

        }

    }

   

    public static int executereader(String CommandText, SqlParameter[] parameters)

    {

        con = new SqlConnection(connection);

        cmd = new SqlCommand(CommandText, con);

        cmd.CommandType = CommandType.StoredProcedure;

        foreach (SqlParameter p in parameters)

        {

            if (p != null)

            {

                cmd.Parameters.Add(p);

            }

        }

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        if (rdr.HasRows)

        {

            return 1;

        }

        else

        {

            return 0;

        }

        con.Close();

    }

}