C#

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using ObjectExtensions;

namespace DataModels
{
    public partial class DBObject
    {
        /// <summary>
        /// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should 
        /// throw an exception if the DataTable retrieved by the query does not match the model 
        /// being created (it compares the number of datatable columns with the number of assigned 
        /// values in the model). The default falue is false.
        /// </summary>
        public bool FailOnMismatch     { get; set; }
        public int TimeoutSecs         { get; set; }
        public string ConnectionString { get; set; }

        public DBObject(string connStr)
        {
            this.ConnectionString = connStr;
            // five minutes should be enough, right?
            this.TimeoutSecs      = 300;
            this.FailOnMismatch   = false;
        }

        /// <summary>
        /// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
        /// </summary>
        /// <param name="cmdText">The storedproc or query to execute</param>
        /// <param name="parameters">The parameters to use in the storedproc/query</param>
        /// <returns></returns>
        protected DataTable GetData(string cmdText, SqlParameter[] parameters=null, CommandType cmdType = CommandType.StoredProcedure)
        {
            // by defining these variables OUTSIDE the using statements, we can evaluate them in 
            // the debugger even when the using's go out of scope.
            SqlConnection conn   = null;
            SqlCommand    cmd    = null;
            SqlDataReader reader = null;
            DataTable     data   = null;

            // create the connection
            using (conn = new SqlConnection(this.ConnectionString))
            {
                // open it
                conn.Open();
                // create the SqlCommand object
                using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
                {
                    // give the SqlCommand object the parameters required for the stored proc/query
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    //create the SqlDataReader
                    using (reader = cmd.ExecuteReader())
                    {
                        // move the data to a DataTable
                        data = new DataTable();
                        data.Load(reader);
                    }
                }
            }
            // return the DataTable object to the calling method
            return data;
        }

        /// <summary>
        /// Calls SqlCommand.ExecuteNonQuery to save data to the database.
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int SetData(string cmdText, SqlParameter[] parameters, CommandType cmdType = CommandType.StoredProcedure)
        {
            int result = 0;
            SqlConnection conn   = null;
            SqlCommand    cmd    =  null;
            using (conn = new SqlConnection(this.ConnectionString))
            {
                conn.Open();
                using (cmd = new SqlCommand(cmdText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
                {
                    SqlParameter rowsAffected = null;
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                        if (cmdType == CommandType.StoredProcedure)
                        {
                            rowsAffected = parameters.FirstOrDefault(x=>x.Direction == ParameterDirection.ReturnValue);
                            if (rowsAffected == null)
                            {
                                rowsAffected = cmd.Parameters.Add(new SqlParameter("@rowsAffected", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue } );
                            }
                        }
                    }
                    result = cmd.ExecuteNonQuery();
                    result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
                }
            }
            return result;
        }

        /// <summary>
        /// Converts a value from its database value to something we can use (we need this because 
        /// we're using reflection to populate our entities)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
        {
            T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
            return result;
        }

        /// <summary>
        /// Creates the list of entities from the specified DataTable object. We do this because we 
        /// have two methods that both need to do the same thing.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        protected List<T> MakeEntityFromDataTable<T>(DataTable data)
        {
            Type objType = typeof(T);
            List<T> collection = new List<T>();
            // if we got back data
            if (data != null && data.Rows.Count > 0)
            {
                // we're going to count how many properties in the model were assigned from the 
                // datatable.
                int matched = 0;

                foreach(DataRow row in data.Rows)
                {
                    // create an instance of our object
                    T item = (T)Activator.CreateInstance(objType);

                    // get our object type's properties
                    PropertyInfo[] properties = objType.GetProperties();

                    // set the object's properties as they are found.
                    foreach (PropertyInfo property in properties)
                    {
                        if (data.Columns.Contains(property.Name))
                        {
                            Type pType = property.PropertyType;
                            var defaultValue = pType.GetDefaultValue();
                            var value = row[property.Name];
                            value = ConvertFromDBValue(value, defaultValue );
                            property.SetValue(item, value);
                            matched++;
                        }
                    }
                    if (matched != data.Columns.Count && this.FailOnMismatch)
                    {
                        throw new Exception("Data retrieved does not match specified model.");
                    }
                    collection.Add(item);
                }
            }
            return collection;
        }

        /// <summary>
        /// Executes the named stored proc (using ExecuteNonQuery) that gets data from the database. 
        /// It uses reflection to set property values in the specified type. If nothing was returned 
        /// from the stored proc, the returned list will be empty.
        /// </summary>
        /// <typeparam name="T">The type of the list item</typeparam>
        /// <param name="storedProc"></param>
        /// <param name="parameters"></param>
        /// <returns>A list of the specified type.</returns>
        /// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
        public List<T> ExecuteStoredProc<T>(string storedProc, params SqlParameter[] parameters)
        {
            // get the data from the database
            DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
            List<T> collection = this.MakeEntityFromDataTable<T>(data);
            return collection;
        }

        /// <summary>
        /// Executes the named stored proc (using ExecuteNonQuery) that stores data in the database. 
        /// </summary>
        /// <param name="storedProc"></param>
        /// <param name="parameters"></param>
        /// <returns>The number of records affected</returns>
        public int ExecuteStoredProc(string storedProc, params SqlParameter[] parameters)
        {
            // Save the data to the database. If the command run in the stored proc is not UPDATE, 
            // INSERT, or DELETE, the return value will be -1, regardless of how many rows are 
            // actually affected.
            int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);

            return result;
        }

        // in case you need to use a query instead of a stored proc

        public List<T> ExecuteQuery<T>(string storedProc, params SqlParameter[] parameters)
        {
            DataTable data = this.GetData(storedProc, parameters, CommandType.Text);
            List<T> collection = this.MakeEntityFromDataTable<T>(data);
            return collection;
        }

        public int ExecuteQuery(string storedProc, params SqlParameter[] parameters)
        {
            // Save the data to the database. If the command run in the stored proc is not UPDATE, 
            // INSERT, or DELETE, the return value will be -1, regardless of how many rows are 
            // actually affected.
            int result = this.SetData(storedProc, parameters, CommandType.Text);
            return result;
        }

    }
}

#realJSOP wrote:

This is the way I do it

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

protected static object ConvertFromDBValue(object obj, object defaultValue)
{
    object result = (obj == null || obj == DBNull.Value) ? defaultValue : obj;
    return result;
}

PropertyInfo.SetValue Method (System.Reflection) | Microsoft Docs[^]:

If this PropertyInfo object is a value type and value is null, then the property will be set to the default value for that type.

Using jsonStream = New FileStream(appPath, FileMode.Create, FileAccess.Write)