the balance between performance and code resue

Question

i am currently working on a project, as the project builds up into lots of lines of code, so i am trying to take the trivial approach and start using some smarter way to make the code be reusable. so for instance i need to connect to the Sql Server:

_{(it could be any other task i am learning while i code i am aware of all availble .Net built-in dedicated classes)}

really all i need is : SqlCommand, SqlDataReader (&SqlConnection) and the appropriate container object to return data usually i am using List<dictionary<string, object>> for my tests with database so till now it is very minimal i should think. but, then i say ah that's a good way to connect to sql and fetch some rowsets then i decide that this is not enough, cause there's some other kinds of returned data, so let's just make some alternative to cover more options, actually let's cover all available scenarios, and then i come up with a class full of "models" covering all available data Formats, single record multiple rows datasets, etc' . but that's not all there's also 2 main options to read from sql server from .NET like SqlDataReader And SqlDataAdapter. so i will cover those options too, but wait , there's also the magic words i could use such as stored procedurs and plain text command , so lets make a class that will hold some stractures to define a stored procedure, and then there's storedprocedure parameters, lets make an easy way to call a stored procedure as an object, so later one-line-call and we have a stored procedure object...

now if i will stop right here cause this is yet only the beginning there's much more in that rabbit hole.. and that even was not the best example as the objects created has a very good reason to exist, well unless you want to code it more efficiently you will not create those extra classes and objects with this behavior and some more tasks to cover on a "daily basis" i begin to see there's a pattern that leads to a huge .Cs file that contains around 10 namespaces with Dozens of classes and functions on each. the question is how do you decide when to use minimal code and when do you let it go and do not care a lot cause hey, the work station is on say .. 8 cores I7 machine there would not be much of a difference, so lets just worry about the ease of coding.

where do you put the red line ?

Edit the code below is not for a code review purpose, it's just to show the scale of the application and the performance impact it could lead to.**

this is only the core of the data object factory, thats about 1/3 of the class which one of a few in other realted tasks.

public class Inventory
{
    public class DataCariers
    {
        public class DbDataCar
        {
            public Prototypes.DataCarPrototypes.CarTypeMeta CarierSpec { get; set; }
            public AlsDataCariers.Inventory.Compartments.DataCarCompartment Trunk { get; set; }
            public string CarierName { get; private set; }
            public DbDataCar(AlsTransMods.Direction bsDir, AlsTransMods.CarSize bsCs, AlsTransMods.useCar bsFamType, Compartments.DataCarCompartment bsTrunk)
            {
                this.CarierSpec = new Prototypes.DataCarPrototypes.CarTypeMeta()
                {
                    CarDirection = bsDir,
                    carOfSize = bsCs,
                    CarFamilySelected = bsFamType
                };
                this.Trunk = bsTrunk;
                this.Trunk.Value = new object();
                this.Trunk.compTypeMeta.CompartmentParent = this.GetType();

            }

            public DbDataCar(Prototypes.DataCarPrototypes.CarTypeMeta bsCarierSpec, Compartments.DataCarCompartment bsTrunk)
            {
                this.CarierSpec = bsCarierSpec;
                this.Trunk = bsTrunk;
                this.Trunk.Value = new object();
                this.Trunk.compTypeMeta.CompartmentParent = this.GetType();

            }
            internal void SetTunk(string CsvVal)
            {
                //this.Trunk = new AlsDataCariers.Inventory.Compartments.GenericsDataCarCompartment.Singles(CsvVal);
                if (this.CarierSpec.CarDirection == AlsTransMods.Direction.In)
                {
                    // new Compartments.MultipleRowsTabedMultipleRecordsCompartmnet(new prototypesFactory.DataCarPrototypes.selectionsTypeCreator(prototypesFactory.DataCarPrototypes.SelectionMode.selectionMultyRow).SelectedSelectionType);
                }
                else this.Trunk.Value = new Csv() { val = CsvVal };
            }
        }


        public class GenericDbDataCar : DbDataCar
        {
            public GenericDbDataCar(AlsTransMods.Direction bsDir, AlsTransMods.CarSize bsCs, AlsTransMods.useCar bsFamType, Compartments.DataCarCompartment bsTrunk)
                : base(bsDir, bsCs, bsFamType, bsTrunk)
            {
                this.CarierSpec = new Prototypes.DataCarPrototypes.CarTypeMeta();
                this.CarierSpec.CarDirection = bsDir;
                this.CarierSpec.carOfSize = bsCs;
                this.CarierSpec.CarFamilySelected = bsFamType;
                this.CarierSpec.CarOfType = this.GetType();

                base.CarierSpec = this.CarierSpec;

                //this.Trunk = this.TrunkCreate();
                //base.Trunk = this.Trunk;
            }

            public GenericDbDataCar(Prototypes.DataCarPrototypes.CarTypeMeta bsCarierSpec, Compartments.DataCarCompartment bsTrunk)
                : base(bsCarierSpec, bsTrunk)
            {
                this.CarierSpec.CarOfType = this.GetType();
                base.CarierSpec = this.CarierSpec;
            }

        }
        public class TabularDbDataCar : DbDataCar
        {
            public TabularDbDataCar(AlsTransMods.Direction bsDir, AlsTransMods.CarSize bsCs, AlsTransMods.useCar bsFamType, Compartments.DataCarCompartment bsTrunk)
                : base(bsDir,  bsCs, bsFamType, bsTrunk)
            {
                this.CarierSpec = new Prototypes.DataCarPrototypes.CarTypeMeta();
                //this.CarierName = string.Concat(bsCs, bsFamType);
                this.CarierSpec.CarDirection = bsDir;
                this.CarierSpec.carOfSize = bsCs;
                this.CarierSpec.CarFamilySelected = bsFamType;
                this.CarierSpec.CarOfType = this.GetType();
                base.CarierSpec = this.CarierSpec;
            }
            public TabularDbDataCar(Prototypes.DataCarPrototypes.CarTypeMeta bsCarierSpec, Compartments.DataCarCompartment bsTrunk):base(bsCarierSpec, bsTrunk)
            {
                this.CarierSpec.CarOfType = this.GetType();

            }

        }

    }
    public class Compartments
    {
        public class DataCarCompartment
        {
            //private Prototypes.DataCarPrototypes.selectionsType selectionsType;

            public RobCs509b.MyModels.Prototypes.DataCarPrototypes.CompartmentMeta compTypeMeta{get; private set;}
            public DataCarCompartment(RobCs509b.MyModels.Prototypes.DataCarPrototypes.CompartmentMeta Bs_CompTypeMeta)
            {
                this.compTypeMeta = Bs_CompTypeMeta;
                this.compTypeMeta.CompartmentSeed = this.GetType();

            }
            public virtual object Value { get; set; }

        }

        public class TabularDataCarCompartment : DataCarCompartment
        {
            public TabularDataCarCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentFamilyType = this.GetType();
            }

        }
        public class TabedSingleRecordComartment : TabularDataCarCompartment
        {
            public TabedSingleRecordComartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();
                this.Value = new DataColumn();
                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
            }
            public new DataColumn Value;

        }
        public class TabedMultipleRecordsCompartment : TabularDataCarCompartment
        {
            public TabedMultipleRecordsCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
               base.compTypeMeta.CompartmentDeliverBaseType= this.GetType();
            }

        }
        public class TabedSingleRowMultipleRecordsCompartment : TabedMultipleRecordsCompartment
        {
            public TabedSingleRowMultipleRecordsCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();

                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
                base.Value = this.Value;
            }
            public new DataRow Value;
        }
        public class MultipleRowsTabedMultipleRecordsCompartmnet : TabedMultipleRecordsCompartment
        {
            public MultipleRowsTabedMultipleRecordsCompartmnet(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();
                this.Value = new DataTable();
                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
                base.Value = this.Value;
            }
            public new DataTable Value;
        }
        public class MultipleRowsClonedTabedMultipleRecordsCompartmnet : TabedMultipleRecordsCompartment
        {
            public MultipleRowsClonedTabedMultipleRecordsCompartmnet(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();
                this.Value = new ClonedSchemaDtt("NotSet");
                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
            }
            public new ClonedSchemaDtt Value;
        }


        public class GenericDataCarCompartment : DataCarCompartment
        {
            public GenericDataCarCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentFamilyType = this.GetType();
            }

        }
        public class GenericSingelRecordCompartment : GenericDataCarCompartment
        {
            public GenericSingelRecordCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {

            }
        }
        public class GenericSingleCsv :GenericSingelRecordCompartment
        {
            public GenericSingleCsv(string CsvVal,Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                this.Value = new Csv();
                this.Value.val = CsvVal;
            }
            public new Csv Value;
        }
        public class GenericMultipleRecordsCompartment : GenericDataCarCompartment
        {
            public GenericMultipleRecordsCompartment(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {

            }
                //public RepetablevaluesCollections RepeatbleCollNameVal;
                //public UniqCollectionNameval UniqCollNameVal;
        }
        public class GenericSingleRowMultipleRecordsCompartment3s: GenericMultipleRecordsCompartment
        {
            public GenericSingleRowMultipleRecordsCompartment3s(Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();
                this.Value = new MyGenericObject3<string, string, string>("", "", "");
                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
            }
            /// <summary>
            /// MyGenericObject3 (string,string,string) values.
            /// </summary>
            public new MyGenericObject3<string, string, string> Value;
        }
        public class GenericMultipleRowsMyGenericObj3DataCarCompartment:GenericMultipleRecordsCompartment
        {
            public GenericMultipleRowsMyGenericObj3DataCarCompartment (Prototypes.DataCarPrototypes.CompartmentMeta compartmentMeta)
                : base(compartmentMeta)
            {
                base.compTypeMeta.CompartmentDeliverBaseType = this.GetType();

                this.Value = new List<MyGenericObject3<string, string, string>>();
                                                base.compTypeMeta.CompartmentDeliverType = this.Value.GetType();
                base.Value = this.Value;
            }
            /// <summary>
            /// DbMagaZine (List MyGenereicObject3) valueType
            /// </summary>
            public new List<MyGenericObject3<string, string, string>> Value;
        }

    }

    public class CompartmentTypes
    {
        private CompartmentTypes creator;
        public CompartmentTypes ()
        {
   }
    }
}



public class Compartments
{
    public class Tabular :Inventory.CompartmentTypes
    {
            Type CompartmentOfFamilyType = typeof(System.ComponentModel.MarshalByValueComponent);
            Type CompartmnetOfType = typeof(Tabular);
    }
    public class Generic : Inventory.CompartmentTypes
    {
            Type CompartmnetOfType = typeof(Generic);
    }
}

Answer 1

The problem of yours is, that there are so many options to do the same thing. My advise is to use the technology that is easiest to program and only switch to lower level abstractions if absolutely necessary for perfomance reasons. And don't compare performance for simple cases but think about the whole system performane and maintenance. I did database programming in the pre Entity Framework times, and it takes much more expertise and effort to this correctly, so I would not recommend this as of today.

So I reccommend technology in the following order:

1. Entity Framework pure
2. Entity Framework with stored procedures for special cases
3. Datasets and hand crafted logic
4. Datasets with stored procedures for special cases
5. SqlDatareader for read only sequential access of a whole table