locked
Duplicate Nested Using Blocks - A better way? RRS feed

  • Question

  • User-851967432 posted

    Hi All,

    I know this isn't an MVC specific question and I'd appreciate a refrain from blurting out, "Use EF", but is there a design pattern or a better way to write a general ADO.net-like class to handle nested using blocks so that these aren't rewritten for every method that needs to query the database:

                using (OracleConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings[""].ConnectionString))
                {
                    cn.Open();
                    using (OracleCommand cmd = new OracleCommand("", cn))
                    {
                        using (OracleDataReader odr = cmd.ExecuteReader())
                        {
                            while(odr.Read())
                            {
                                ddlPositions.Items.Add(new ListItem(odr[1].ToString(), odr[0].ToString()));
                            }
                        }                    
                    }
                }

    Thanks,

    Adam

    Monday, April 9, 2018 5:45 PM

Answers

  • User475983607 posted

    Create a generic method that can execute any SQL or procedure and return a generic result set. 

            public List<T> Select<T>(string procedureName, List<SimpleParameters> parameters) where T : new()
            {
                Type obj = typeof(T);
                List<T> results = new List<T>();
    
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionNode].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(procedureName, con);
                    cmd.CommandType = CommandType.StoredProcedure;
    
                    foreach(SimpleParameters p in parameters)
                    {
                        cmd.Parameters.AddWithValue(p.Name, p.Value);
                    }
                    
    
                    con.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        Hashtable hashtable = new Hashtable();
                        PropertyInfo[] properties = obj.GetProperties();
                        foreach (PropertyInfo info in properties)
                        {
                            hashtable[info.Name.ToUpper()] = info;
                        }
                        while (reader.Read())
                        {
                            T newObject = new T();
                            for (int index = 0; index < reader.FieldCount; index++)
                            {
                                PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                if ((info != null) && info.CanWrite)
                                {
                                    info.SetValue(newObject, reader.GetValue(index), null);
                                }
                            }
                            results.Add(newObject);
                        }
                        reader.Close();
                    }
    }
    } return results; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 6:04 PM
  • User-851967432 posted

    I used your Select method (with a minor tweak) as well as implemented a Repository pattern. I'm sure there's a better way to break this out even more to reuse the ado.net libraries but I'm happy with this for now:

    using Oracle.ManagedDataAccess.Client;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Web;
    
    namespace OAP
    {
        public class DAL
        {
            public abstract class EntityBase
            {
                public Int64 Id { get; protected set; }
            }
            public interface IRepository : IDisposable
            {
                List<T> Select<T>(string procedureName, List<OracleParameter> parameters) where T : new();
                List<T> SelectQuery<T>(string Query) where T : new();
                int Create(string procedureName, List<OracleParameter> parameters);
                void Delete(string procedureName, List<OracleParameter> parameters);
                void Update(string procedureName, List<OracleParameter> parameters);
            }
            public class Repository : EntityBase, IRepository, IDisposable
            {
                private string cnString = ConfigurationManager.ConnectionStrings["OAPA_DB"].ConnectionString;
                public int Create(string procedureName, List<OracleParameter> parameters)
    
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                            OracleParameter p = (OracleParameter)(from x in parameters where x.Direction == ParameterDirection.Output select x).FirstOrDefault();
                            if (p != null)
                            {
                                return Convert.ToInt16(p.Value.ToString());
                            }
                            return -1;
                        }
                    }
                }
    
                public void Delete(string procedureName, List<OracleParameter> parameters)
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
    
                public List<T> SelectQuery<T>(string Query) where T : new()
                {
                    Type obj = typeof(T);
                    List<T> results = new List<T>();
    
                    using (OracleConnection con = new OracleConnection(cnString))
                    {
                        OracleCommand cmd = new OracleCommand(Query, con);
                        cmd.CommandType = CommandType.Text;
    
                        con.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            Hashtable hashtable = new Hashtable();
                            PropertyInfo[] properties = obj.GetProperties();
                            foreach (PropertyInfo info in properties)
                            {
                                hashtable[info.Name.ToUpper()] = info;
                            }
                            while (reader.Read())
                            {
                                T newObject = new T();
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                    var v = reader.GetValue(index) == DBNull.Value ? null : reader.GetValue(index);
                                    if ((info != null) && info.CanWrite)
                                    {
                                        var val = reader.GetValue(index);
                                        if (val == DBNull.Value)
                                            val = null;
    
                                        info.SetValue(newObject, val, null);
                                    }
                                }
                                results.Add(newObject);
                            }
                            reader.Close();
                        }
                    }
                    return results;
                }
                public List<T> Select<T>(string procedureName, List<OracleParameter> parameters) where T : new()
                {
                    Type obj = typeof(T);
                    List<T> results = new List<T>();
    
                    using (OracleConnection con = new OracleConnection(cnString))
                    {
                        OracleCommand cmd = new OracleCommand(procedureName, con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters.ToArray());
    
                        con.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            Hashtable hashtable = new Hashtable();
                            PropertyInfo[] properties = obj.GetProperties();
                            foreach (PropertyInfo info in properties)
                            {
                                hashtable[info.Name.ToUpper()] = info;
                            }
                            while (reader.Read())
                            {
                                T newObject = new T();
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                    if ((info != null) && info.CanWrite)
                                    {
                                        var val = reader.GetValue(index);
                                        if (val == DBNull.Value)
                                            val = null;
    
                                        info.SetValue(newObject, val, null);
                                    }
                                }
                                results.Add(newObject);
                            }
                            reader.Close();
                        }
                    }
                    return results;
                }
    
                public void Update(string procedureName, List<OracleParameter> parameters)
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
    
                private bool disposed = false;
    
                protected virtual void Dispose(bool disposing)
                {
                    if (disposing)
                    {
                        if (!disposed)
                        {
                            disposed = true;
                        }
                    }
                }
    
                public void Dispose()
                {
                    Dispose(true);
                    GC.SuppressFinalize(this);
                }
            }
        }
    }
    

    Thanks again,

    Adam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 12:48 PM

All replies

  • User475983607 posted

    Create a generic method that can execute any SQL or procedure and return a generic result set. 

            public List<T> Select<T>(string procedureName, List<SimpleParameters> parameters) where T : new()
            {
                Type obj = typeof(T);
                List<T> results = new List<T>();
    
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionNode].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(procedureName, con);
                    cmd.CommandType = CommandType.StoredProcedure;
    
                    foreach(SimpleParameters p in parameters)
                    {
                        cmd.Parameters.AddWithValue(p.Name, p.Value);
                    }
                    
    
                    con.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        Hashtable hashtable = new Hashtable();
                        PropertyInfo[] properties = obj.GetProperties();
                        foreach (PropertyInfo info in properties)
                        {
                            hashtable[info.Name.ToUpper()] = info;
                        }
                        while (reader.Read())
                        {
                            T newObject = new T();
                            for (int index = 0; index < reader.FieldCount; index++)
                            {
                                PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                if ((info != null) && info.CanWrite)
                                {
                                    info.SetValue(newObject, reader.GetValue(index), null);
                                }
                            }
                            results.Add(newObject);
                        }
                        reader.Close();
                    }
    }
    } return results; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 6:04 PM
  • User-851967432 posted

    I like this answer simply for the boxing reasons but you're showing your age with the hashtable my friend. ;)

    I need to start using Generics more. I read about it when it was first introduced but have since forgotten about it and never really learned it.

    Thanks for the sample.

    Monday, April 9, 2018 6:58 PM
  • User-832373396 posted

    <g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="10" data-gr-id="10">Hi</g> <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="5" data-gr-id="5">adamturnerj</g>,

    I'd appreciate a refrain from blurting out, "Use EF", but is there a design pattern or a better way to write a general ADO.net

    • 1 Sir, first of all, I am not good at Oracle, but I recommend this for you. it seems looks good and not EF.

    and code(it will save our time)

    CrmDemoDataContext context = new CrmDemoDataContext();
    var query = from it in context.Companies
                orderby it.CompanyID
                select it;
    
    foreach (Company comp in query)
      Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country);
    
    Console.ReadLine();

    From https://www.devart.com/dotconnect/oracle/articles/tutorial_linq.html 

    • 2 and as far as I know, in c#, there is DataContext class, not EF. 

    The purpose of the DataContext is to translate your requests for objects into SQL queries to be made against the database, and then to assemble objects out of the results. The DataContext enables Language-Integrated Query (LINQ) by implementing the same operator pattern as the Standard Query Operators, such <g class="gr_ gr_442 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="442" data-gr-id="442">as</g><g class="gr_ gr_442 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="442" data-gr-id="442"> </g>Where<g class="gr_ gr_442 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="442" data-gr-id="442"> </g><g class="gr_ gr_443 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="443" data-gr-id="443"><g class="gr_ gr_442 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="442" data-gr-id="442">and</g></g><g class="gr_ gr_443 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="443" data-gr-id="443"> </g>Select<g class="gr_ gr_443 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="443" data-gr-id="443">.</g>

    // DataContext takes a connection string. 
    DataContext db = new DataContext(@"c:\Northwnd.mdf");
    
    // Get a typed table to run queries.
    Table<Customer> Customers = db.GetTable<Customer>();
    
    // Query for customers from London.
    var query =
        from cust in Customers
        where cust.City == "London"
        select cust;
    
    foreach (var cust in query)
        Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

    From https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-connect-to-a-database 

    Hope it is good for you :)

    With regards, Angelina Jolie

    Tuesday, April 10, 2018 8:19 AM
  • User-851967432 posted

    I used your Select method (with a minor tweak) as well as implemented a Repository pattern. I'm sure there's a better way to break this out even more to reuse the ado.net libraries but I'm happy with this for now:

    using Oracle.ManagedDataAccess.Client;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Web;
    
    namespace OAP
    {
        public class DAL
        {
            public abstract class EntityBase
            {
                public Int64 Id { get; protected set; }
            }
            public interface IRepository : IDisposable
            {
                List<T> Select<T>(string procedureName, List<OracleParameter> parameters) where T : new();
                List<T> SelectQuery<T>(string Query) where T : new();
                int Create(string procedureName, List<OracleParameter> parameters);
                void Delete(string procedureName, List<OracleParameter> parameters);
                void Update(string procedureName, List<OracleParameter> parameters);
            }
            public class Repository : EntityBase, IRepository, IDisposable
            {
                private string cnString = ConfigurationManager.ConnectionStrings["OAPA_DB"].ConnectionString;
                public int Create(string procedureName, List<OracleParameter> parameters)
    
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                            OracleParameter p = (OracleParameter)(from x in parameters where x.Direction == ParameterDirection.Output select x).FirstOrDefault();
                            if (p != null)
                            {
                                return Convert.ToInt16(p.Value.ToString());
                            }
                            return -1;
                        }
                    }
                }
    
                public void Delete(string procedureName, List<OracleParameter> parameters)
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
    
                public List<T> SelectQuery<T>(string Query) where T : new()
                {
                    Type obj = typeof(T);
                    List<T> results = new List<T>();
    
                    using (OracleConnection con = new OracleConnection(cnString))
                    {
                        OracleCommand cmd = new OracleCommand(Query, con);
                        cmd.CommandType = CommandType.Text;
    
                        con.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            Hashtable hashtable = new Hashtable();
                            PropertyInfo[] properties = obj.GetProperties();
                            foreach (PropertyInfo info in properties)
                            {
                                hashtable[info.Name.ToUpper()] = info;
                            }
                            while (reader.Read())
                            {
                                T newObject = new T();
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                    var v = reader.GetValue(index) == DBNull.Value ? null : reader.GetValue(index);
                                    if ((info != null) && info.CanWrite)
                                    {
                                        var val = reader.GetValue(index);
                                        if (val == DBNull.Value)
                                            val = null;
    
                                        info.SetValue(newObject, val, null);
                                    }
                                }
                                results.Add(newObject);
                            }
                            reader.Close();
                        }
                    }
                    return results;
                }
                public List<T> Select<T>(string procedureName, List<OracleParameter> parameters) where T : new()
                {
                    Type obj = typeof(T);
                    List<T> results = new List<T>();
    
                    using (OracleConnection con = new OracleConnection(cnString))
                    {
                        OracleCommand cmd = new OracleCommand(procedureName, con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters.ToArray());
    
                        con.Open();
                        using (OracleDataReader reader = cmd.ExecuteReader())
                        {
                            Hashtable hashtable = new Hashtable();
                            PropertyInfo[] properties = obj.GetProperties();
                            foreach (PropertyInfo info in properties)
                            {
                                hashtable[info.Name.ToUpper()] = info;
                            }
                            while (reader.Read())
                            {
                                T newObject = new T();
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    PropertyInfo info = (PropertyInfo)hashtable[reader.GetName(index).ToUpper()];
                                    if ((info != null) && info.CanWrite)
                                    {
                                        var val = reader.GetValue(index);
                                        if (val == DBNull.Value)
                                            val = null;
    
                                        info.SetValue(newObject, val, null);
                                    }
                                }
                                results.Add(newObject);
                            }
                            reader.Close();
                        }
                    }
                    return results;
                }
    
                public void Update(string procedureName, List<OracleParameter> parameters)
                {
                    using (OracleConnection cn = new OracleConnection(cnString))
                    {
                        cn.Open();
                        using (OracleCommand cmd = new OracleCommand(procedureName, cn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
    
                private bool disposed = false;
    
                protected virtual void Dispose(bool disposing)
                {
                    if (disposing)
                    {
                        if (!disposed)
                        {
                            disposed = true;
                        }
                    }
                }
    
                public void Dispose()
                {
                    Dispose(true);
                    GC.SuppressFinalize(this);
                }
            }
        }
    }
    

    Thanks again,

    Adam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 11, 2018 12:48 PM
  • User-851967432 posted

    Angelina,

    This doesn't seem to be compatible with framework 4.5.2 but very cool idea.

    Thanks,

    Adam

    Wednesday, April 11, 2018 5:39 PM