Scott Harrison - The golden apples
Henry Parker's Robot Wars

ASP.Net, ADO, MS Access
C# How to call a stored procedure in dot.net core

C# How to call a stored procedure in dot.net core

(3 hits)
Overview : Hits=4 In dot.net core the database.sqlquery<T> command was not added. I found a work around on github that involved an static extension methods which I am grateful to have found. In the code snippet I demonstrate the long method for interacting with stored procedures and the short method. In the long method, I create an ado command object from the datacontext with the GetDbConnection and createCommand method. The command object is initialized as a storedprocedure commandtype with the name of the stored procedure and the parameter forwhich I pass called "NextNumberName". The NextNumberName is the entity identifier in the NextNumber table. The ado database connection is opened and a ado reader created using the executereader. The returning dataset can be accessed by name or ordinal position in the recordset. You are required to type cast the returning data.

In the short method, an extension class method is created called sqlquery. The database context is passed along with the name of the stored procedure and a list of sqlparameters. The method is typed as T an generic type that you provide. Using reflection, var propts = typeof(T).GetProperties(); returns the class properties of the generic class that is passed. I pass the type T of NextNumber. A model of type T is new up and the data of the returning dataset is setvalue to properties in the NextNumber class of generic T. A Ilist of type T is returned from the extension method.

EF 6.0 code that needs replaced


      NextNumber nextNumber = await _dbContext.Database.SqlQuery<NextNumber>("usp_GetNextNumber @NextNumberName", param1).SingleAsync();

 public partial class NextNumber
    {
        public long NextNumberId { get; set; }
        public string NextNumberName { get; set; }
        public long NextNumberValue { get; set; }
    }

New conversion (Long way)


SqlParameter param1 = new SqlParameter("@NextNumberName", nextNumberName);
 var command = _dbContext.Database.GetDbConnection().CreateCommand();

                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "usp_GetNextNumber";
                command.Parameters.Add(param1);

                _dbContext.Database.OpenConnection();

        

                var queryResults = command.ExecuteReader();
                NextNumber nextNumber = new NextNumber();
                while (queryResults.Read())
                {
                    nextNumber.NextNumberId = (long) queryResults["NextNumberId"];
                    nextNumber.NextNumberName = queryResults["NextNumberName"].ToString();
                    nextNumber.NextNumberValue = (long)queryResults["NextNumberValue"];
                   


                }

New Conversion (Short way)



                List<SqlParameter> parameters = new List<SqlParameter>();

                parameters.Add( new SqlParameter("@NextNumberName", nextNumberName));

   		IList<NextNumber> nextNumber= _dbContext.SqlQuery<NextNumber>(CommandType.Text, "usp_GetNextNumber @NextNumberName",parameters);
		
	
     public static class EF_Extension
    {
        //https://github.com/aspnet/EntityFrameworkCore/issues/10262
        public static IList<T> SqlQuery<T>(this DbContext db, CommandType type, string sql, List<SqlParameter> parameters) where T : new()
        {
            var conn = db.Database.GetDbConnection();
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                    conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = type;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        foreach (var item in parameters)
                        {
                            DbParameter p = command.CreateParameter();
                            p.DbType = item.DbType;
                            p.ParameterName = item.ParameterName;
                            p.Value = item.Value;
                            command.Parameters.Add(p);
                        }
                    }
                    var propts = typeof(T).GetProperties();
                    var rtnList = new List<T>();
                    T model;
                    object val;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            model = new T();
                            foreach (var l in propts)
                            {
                                val = reader[l.Name];
                                if (val == DBNull.Value)
                                    l.SetValue(model, null);
                                else
                                    l.SetValue(model, val);
                            }
                            rtnList.Add(model);
                        }
                    }
                    return rtnList;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

....

...<<<Register to correspond>>> ...

Members : 152
Name:
Email:

Register to View

Help