by DuttaluruVijayakumar Updated : Oct 12, 2019 in Articles

Ado.net data access utility class for SQL server

in this article, we will go through the Ado.net data access utility class for SQL server

Executenonquery helper class in Ado.net

In Ado.Net ExecuteNonQuery can return the number of rows affected and return type is int. the given below static SqlExecuteNonQuery() function can accept the Queries only (Create, Alter, Drop, Insert, Update, Delete)

 public static void SqlExecuteNonQuery(string sqlQry)//, SqlConnection sqlCon)
        {
            try
            {
                using (sqlCon = new SqlConnection(conString))
                {
                    sqlCon.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch { throw; }
        }

Execute Scalar helper class in Ado.net

Execute Scalar() Function will work for non-action queries and it contains aggregate functions. it will return the first row and first column value of the query result

 public static void SqlExecuteNonQuery(string sqlQry,SqlTransaction trans)
        {
            try
            {
                using (sqlCon = new SqlConnection(conString))
                {
                    sqlCon.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch { throw; }
        }

Return DataTable in Ado.Net

the given below static SqlExecuteDataTable() function will return the data table and it is depending on passing queries

public static DataTable SqlExecuteDataTable(string sqlQry)//, SqlConnection sqlCon)
        {
            try
            {
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
               // string conString = ConfigurationManager.ConnectionStrings["SqlConString"].ToString();
                using (sqlCon = new SqlConnection(conString))
                {
                    if (sqlCon.State != ConnectionState.Open)
                    {
                        sqlCon.Open();
                    }
                    using (SqlCommand cmd = new SqlCommand(sqlQry, sqlCon))
                    {
                        cmd.CommandType = CommandType.Text;

                        using (SqlDataAdapter da = new SqlDataAdapter(sqlQry, sqlCon))
                        {
                            da.Fill(ds);
                            dt = ds.Tables[0];
                            sqlCon.Close();
                        }

                        //return cmd.ExecuteScalar();
                        //sqlcon.Close();
                    }
                }
                return dt;
            }
            catch {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                throw; }
            finally
            {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
            }
        }

Append Where Clause

the static AppendWhereClause() function will the appending the where clause to passing query

    public static string AppendWhereClause(string strQry)
        {
            try
            {
                if (strQry.Contains(" WHERE"))
                {
                    strQry = strQry + " " + "AND";
                }
                else
                {
                    strQry = strQry + " " + " WHERE";
                }
                return strQry;
            }
            catch { throw; }
        }

Return SqlDataReader object

 public SqlDataReader ExecuteReader(string sql, SqlParameter[] parameters = null)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            if (parameters != null)
            {
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

for more Ado.net data access utility class for SQL server

Author
Recent Posts

DuttaluruVijayakumar

hello ! my name is Duttaluru Vijay.i am working in Microsoft .net technologies since 2016.
I like to share my work experience, research, and knowledge through my blog.
I love developing the applications in Microsoft technologies including asp.net mvc,c#.net, windfarms, SQL server,entityfrawork,Ajax,Jquery,WEbApi,Asp.net core, SEO, WordPress, UI..more