ado.net db helper class

Updated : Oct 12, 2019 in Articles

Ado.net SQL server helper class

in this article, we will go through Ado.net SQL server helper class for building the different types of applications this helper class will reduce the time and code

Create Class name Like SqlServerDatabaseConnection and dump the given below Code

Ado.net SQL server helper class
public class SqlServerDatabaseConnection
    {
        string sqlConnectionString = Configuration.Configurations.SqlServerConnection;
        public int GetID(string tableName, string parameters, string whereCondition)
        {
            string query = String.Empty;
            int retrievedId = -1;
            try
            {
                if (!String.IsNullOrEmpty(whereCondition))
                {
                    query = "SELECT " + parameters + " FROM " + tableName + " WHERE " + whereCondition;
                }
                else
                {
                    query = "SELECT " + parameters + " FROM " + tableName;
                }
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    object obj = cmd.ExecuteScalar();
                    if (obj != null)
                    {
                        retrievedId = Convert.ToInt32(obj.ToString());
                    }
                    else
                    {
                        retrievedId = 0;
                    }
                }
            }
            catch (Exception ex)
            {
                retrievedId = 0;
            }
            return retrievedId;
        }

        public List<object> GetData(string tableName, string parameters, string whereCondition)
        {
            string query = String.Empty;
            List<object> reqData = null;
            DataTable dt = new DataTable();
            try
            {
                if (!String.IsNullOrEmpty(whereCondition))
                {
                    query = "SELECT " + parameters + " FROM " + tableName + " WHERE " + whereCondition;
                }
                else
                {
                    query = "SELECT " + parameters + " FROM " + tableName;
                }
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
                    {
                        da.Fill(dt);
                    }
                }
                if (dt != null &amp;&amp; dt.Rows.Count > 0)
                {
                    reqData = dt.AsEnumerable().ToList<object>();
                }
            }
            catch (Exception ex)
            {
                reqData = null;
            }
            return reqData;
        }

        public int GetIDValue(string databaseQuery)
        {

            int id = 0;
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(databaseQuery, conn);
                    object obj = cmd.ExecuteScalar();
                    if (obj != null)
                    {
                        id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                id = 0;
            }
            return id;


        }

        public List<object> GetData(string query)
        {
            List<object> reqData = null;
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
                    {
                        da.Fill(dt);
                    }
                }
                if (dt != null &amp;&amp; dt.Rows.Count > 0)
                {
                    reqData = dt.AsEnumerable().ToList<object>();
                }
            }
            catch (Exception ex)
            {
                reqData = null;
            }
            return reqData;
        }

        public DataTable GetDataTable(string tableName, string parameters, string whereCondition)
        {
            string query = String.Empty;
            DataTable dt = new DataTable();
            try
            {
                if (!String.IsNullOrEmpty(whereCondition))
                {
                    query = "SELECT " + parameters + " FROM " + tableName + " WHERE " + whereCondition;
                }
                else
                {
                    query = "SELECT " + parameters + " FROM " + tableName;
                }
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                LogControl.Write(ex.Message.ToString());
                dt = null;
            }
            return dt;
        }

        public int InsertData(string tableName, string paramList, string paramValues, int id)
        {
            int result = -1;
            try
            {
                string query = "INSERT INTO " + tableName + "(" + paramList + ") VALUES(" + paramValues + ")";
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                    result = 1;
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }

        internal DataSet GetDataSet(string ProcedureName, SqlParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = ProcedureName;
                    cmd.CommandTimeout = 0;
                    cmd.CommandType = CommandType.StoredProcedure;                    
                    cmd.Parameters.Clear();
                    if (commandParameters != null)
                    {
                        cmd.Parameters.AddRange(commandParameters);
                    }
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                }
            }
            catch (Exception ex)
            {
                string exceptionmessage = ex.Message;
                LogControl.Write(exceptionmessage);
                ds = null;
            }
            return ds;
        }

        public int InsertData(string tableName, string paramList, string paramValues)
        {
            int result = -1;
            try
            {
                string query = "INSERT INTO " + tableName + "(" + paramList + ") VALUES(" + paramValues + ") SELECT IDENT_CURRENT('" + tableName + "')";
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                    //result = 1;
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }

        public int InsertData(string databaseQuery)
        {
            int result = -1;
            try
            {

                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(databaseQuery, conn);
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                    //result = 1;
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }

        public int DeleteData(string tableName, string parameter, int value)
        {
            int result = -1;
            try
            {
                string query = "DELETE FROM " + tableName + " WHERE " + parameter + " = " + value;
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }
        public int DeleteData(string query)
        {
            int result = -1;
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }

        public int UpdateData(string tableName, string paramDetails, string whereCondition)
        {
            string query = String.Empty;
            int result = -1;
            try
            {
                if (!String.IsNullOrEmpty(whereCondition))
                {
                    query = "UPDATE " + tableName + " SET " + paramDetails + " WHERE " + whereCondition;
                }
                else
                {
                    query = "UPDATE " + tableName + " SET " + paramDetails;
                }
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                result = 0;
            }
            return result;
        }

        public int UpdateData(string databaseQuery)
        {
            int result = -1;
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(databaseQuery, conn);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {

                result = 0;
            }
            return result;
        }

        public string GetName(string databaseQuery)
        {
            string retrieveString = String.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(databaseQuery, conn);
                    object obj = cmd.ExecuteScalar();
                    if (obj != null)
                    {
                        retrieveString = cmd.ExecuteScalar().ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                string exceptionmessage = ex.Message;
                LogControl.Write(exceptionmessage);
                retrieveString = String.Empty;
            }
            return retrieveString;
        }

        public string GetName(string tablename, string parameters, string wherecondition)
        {
            string query = String.Empty;
            string retrivestring = string.Empty;
            try
            {

                if (!String.IsNullOrEmpty(wherecondition))
                {
                    query = "SELECT " + parameters + " FROM " + tablename + " WHERE " + wherecondition;
                }
                else
                {
                    query = "SELECT " + parameters + " FROM " + tablename;
                }
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    object obj = cmd.ExecuteScalar();
                    if (obj != null)
                    {
                        retrivestring = cmd.ExecuteScalar().ToString();
                    }

                }



            }

            catch (Exception ex)
            {
                string exceptionmessage = ex.Message;
                LogControl.Write(exceptionmessage);
                retrivestring = "";
            }

            return retrivestring;


        }

        public DataTable GetDataTable(string databaseQuery)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(databaseQuery, conn);
                    using (SqlDataAdapter da = new SqlDataAdapter(databaseQuery, conn))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                string exceptionmessage = ex.Message;
                LogControl.Write(exceptionmessage);
                dt = null;
            }
            return dt;
        }

        public DataTable GetDataTable(string procedureName, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = procedureName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 0;
                    cmd.Parameters.Clear();
                    if (commandParameters != null)
                    {
                        cmd.Parameters.AddRange(commandParameters);
                    }
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                string exceptionmessage = ex.Message;
                LogControl.Write(exceptionmessage);
                dt = null;
            }
            return dt;
        }

        public int InsertTable(string procedureName, params SqlParameter[] commandParameters)
        {
            int result = -1;
            try
            {
                using (SqlConnection conn = new SqlConnection(sqlConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = procedureName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    if (commandParameters != null)
                    {
                        cmd.Parameters.AddRange(commandParameters);
                    }                   
                    cmd.ExecuteNonQuery();
                    result = Convert.ToInt32(cmd.Parameters["@new_identity"].Value);
                }
            }
            catch (Exception ex)
            {
                string msg = ex.Message.ToString();
                result = -1;
            }
            return result;
        }


        public int CreatePackageNew(int townid)
        {
            int result = -1;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("sp_GeneratePackageName", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@IP_TownID", SqlDbType.Int).Value = townid;

                        cmd.Parameters.Add("@OP_PackageID", SqlDbType.Int).Direction = ParameterDirection.Output;
                        con.Open();
                        cmd.ExecuteNonQuery();

                        result = Convert.ToInt32(cmd.Parameters["@OP_PackageID"].Value);
                        con.Close();
                    }
                }
            }
            catch (Exception e)
            {
                result = 0;
            }
            return result;
        }

    }

for more ado.Net Helper class

Like
Like Love Haha Wow Sad Angry

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Myung Alsbrook
Myung Alsbrook
April 7, 2020 8:09 pm

thanks to the author for taking his clock time on this one.

1
0
Would love your thoughts, please comment.x
()
x