Click here to Skip to main content
14,364,458 members
   

C#

 
AnswerRe: How to perform the content of a variable as a C# instruction Pin
jschell2-Jun-19 9:53
memberjschell2-Jun-19 9:53 
QuestionHow To Make This Generic Pin
Kevin Marois31-May-19 12:55
professionalKevin Marois31-May-19 12:55 
AnswerRe: How To Make This Generic Pin
#realJSOP1-Jun-19 6:00
mve#realJSOP1-Jun-19 6:00 
QuestionAsk C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
Dhjjf29-May-19 23:15
memberDhjjf29-May-19 23:15 
AnswerRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
BillWoodruff29-May-19 23:41
mveBillWoodruff29-May-19 23:41 
AnswerRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
Eddy Vluggen29-May-19 23:56
mveEddy Vluggen29-May-19 23:56 
AnswerRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
OriginalGriff30-May-19 0:00
protectorOriginalGriff30-May-19 0:00 
AnswerRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
#realJSOP30-May-19 5:55
mve#realJSOP30-May-19 5:55 
This is the way I do it. This class uses ADO.Net to access the database. It can populate any specified model, as long as the property names/types in the specified model match what is returned in the dataset. It uses reflect to perform this process. By default, it will NOT throw an exception if the data returrned from the dataset cannot be matched up to the model, but that can be enabled as needed. I have several dusiness layer objects that inherit this code that all hit different databases. Ninety-nine percent of our database access is performed via stored procs, but there is support in this object for using parameterized queries as well. Catching exceptions is the responsibility of the method that calls the BLL methods. I don't recall if this code relies on any other code I've written, and if it does, it's nothing more than extension methods. I can provide those if they're needed. The class is reasonably well commented, so you shouldn't have many/any problems understanding what I've done, and adapting it to sqllight shouldn't be a problem.

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;
        }

    }
}

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013


modified 30-May-19 11:14am.

GeneralRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
Eddy Vluggen30-May-19 8:20
mveEddy Vluggen30-May-19 8:20 
GeneralRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
#realJSOP30-May-19 8:24
mve#realJSOP30-May-19 8:24 
PraiseRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
Eddy Vluggen30-May-19 8:29
mveEddy Vluggen30-May-19 8:29 
SuggestionRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
Richard Deeming30-May-19 8:46
mveRichard Deeming30-May-19 8:46 
GeneralRe: Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way Pin
#realJSOP1-Jun-19 6:02
mve#realJSOP1-Jun-19 6:02 
QuestionFile keeps getting corrupted on just one machine Pin
jkirkerx29-May-19 13:51
professionaljkirkerx29-May-19 13:51 
AnswerRe: File keeps getting corrupted on just one machine PinPopular
Dave Kreskowiak29-May-19 14:11
mveDave Kreskowiak29-May-19 14:11 
GeneralRe: File keeps getting corrupted on just one machine Pin
jkirkerx29-May-19 14:17
professionaljkirkerx29-May-19 14:17 
AnswerRe: File keeps getting corrupted on just one machine Pin
Dave Kreskowiak29-May-19 14:13
mveDave Kreskowiak29-May-19 14:13 
GeneralRe: File keeps getting corrupted on just one machine Pin
jkirkerx29-May-19 14:25
professionaljkirkerx29-May-19 14:25 
QuestionDownload files Pin
Member 1447263829-May-19 1:23
memberMember 1447263829-May-19 1:23 
AnswerRe: Download files Pin
#realJSOP29-May-19 1:36
mve#realJSOP29-May-19 1:36 
AnswerRe: Download files Pin
Pete O'Hanlon29-May-19 1:42
protectorPete O'Hanlon29-May-19 1:42 
GeneralRe: Download files Pin
Member 1447263829-May-19 4:12
memberMember 1447263829-May-19 4:12 
GeneralRe: Download files Pin
OriginalGriff29-May-19 4:14
protectorOriginalGriff29-May-19 4:14 
GeneralRe: Download files Pin
Pete O'Hanlon29-May-19 4:36
protectorPete O'Hanlon29-May-19 4:36 
Questiondownload files Pin
Member 1447263829-May-19 2:30
memberMember 1447263829-May-19 2:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.