Return dynamic result set from SqlQuery EF #3100

bilalhaidar created 7 years ago

Hi, I am in need to run a query directly on SQL Server. When using Abp, this means I have to create a new repository and add this method inside to call the Stored Procedure using SqlQuery.

My question is, the result I am returning is not and wont be mapped to any entity. Is it possible to return a result from SqlQuery as Dictionary of key/value or DataTable?

I will need then to generate and export Excel out of the data returned.

It happens in my case the data is dynamic. I am generating PIVOT out of data and hence columns are not known ahead of time.

I appreciate your assistance.

Bilal

23 Answer(s)

0

alirizaadiyahsi created 7 years ago

Hi,

First, I suggest you to look at this conversations: #3001@1ae4525e-277b-4e15-9771-14ec911be476

Second,

Is it possible to return a result from SqlQuery as Dictionary of key/value or DataTable?

Yes, it is poosible, for example: <a class="postlink" href="http://stackoverflow.com/questions/30771552/using-sqlquerydictionarystring-string-in-entity-framework-6">http://stackoverflow.com/questions/3077 ... ramework-6</a>
0

bilalhaidar created 7 years ago

Thank you. In my case, it is a bit more involved. I am returning an unknown number of columns from SP. Is there a way to map that result maybe to a DataTable or so?

Thanks

bilalhaidar created 7 years ago

I found something on the internet that I could use inside a custom repository. One concern though that I have is related to DB Connection, would any of the code below break the UOW or so in Abp?

public Dictionary<string, List<object>> GetTableInformation(string tableName, FinkonaDatabaseType type)
{
   var sqlText = "SELECT * from " + tableName;
   DataTable dt = new DataTable();

   // Use DataTables to extract the whole table in one hit
   using(SqlDataAdapter da = new SqlDataAdapter(sqlText, optimumEntities.Database.ConnectionString)
   {
      da.Fill(dt);   
   }

   var tableData = new Dictionary<string, List<object>>();

   // Go through all columns, retrieving their names and populating the rows
   foreach(DataColumn dc in dt.Columns)
   {
      string columnName = dc.Name;
      rowData = new List<object>();
      tableData.Add(columnName, rowData);

      foreach(DataRow dr in dt.Rows)
      {
         rowData.Add(dr[columnName]);   
      }
   }

   return tableData;
}

Another method

DataSet GetDataSet(string sql, CommandType commandType, Dictionary<string, Object> parameters)
    {
        // creates resulting dataset
        var result = new DataSet();

        // creates a data access context (DbContext descendant)
        using (var context = new MyDbContext())
        {
            // creates a Command 
            var cmd = context.Database.Connection.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = sql;

            // adds all parameters
            foreach (var pr in parameters)
            {
                var p = cmd.CreateParameter();
                p.ParameterName = pr.Key;
                p.Value = pr.Value;
                cmd.Parameters.Add(p);
            }

            try
            {
                // executes
                context.Database.Connection.Open();
                var reader = cmd.ExecuteReader();

                // loop through all resultsets (considering that it's possible to have more than one)
                do
                {
                    // loads the DataTable (schema will be fetch automatically)
                    var tb = new DataTable();
                    tb.Load(reader);
                    result.Tables.Add(tb);

                } while (!reader.IsClosed);
            }
            finally
            {
                // closes the connection
                context.Database.Connection.Close();
            }
        }

        // returns the DataSet
        return result;
    }

0

ismcagdas created 7 years ago
Support Team

Hi,

As you know repositories are UoW by default. Your code seems fine except one thing, you should not create a new dbContext in your repository. When a new repository is created, it has access to a dbContext instance, you can use it for running your query.
0

bilalhaidar created 7 years ago

Thanks Ismail, true, repositories are UOW.

But I was confused regarding "using()" the dbContext. Would that close the connection or affect UOW or what happens in this case "using()"?

Can you pls point me your UOW code in Abp? Is it done as an interceptor?
0

hikalkan created 7 years ago
Support Team

Use "Context" property to access to DbContext, never create a new one.

Do not use "using" for dbcontextes, because it will be already disposed when UOW is completed.
0

bilalhaidar created 7 years ago

Thanks Hilal

Can you please provide me link to code where UOW is done in Abp?
0

alirizaadiyahsi created 7 years ago

Actually, according to @hikalkan's suggestion, you don't need read document about unit of work.

But you can see the document here : <a class="postlink" href="https://www.aspnetboilerplate.com/Pages/Documents/Unit-Of-Work">https://www.aspnetboilerplate.com/Pages ... it-Of-Work</a>
0

bilalhaidar created 7 years ago

Thanks but I'm looking into a link to source code if possible.

bilalhaidar created 7 years ago

Something like this would be fine and wont break the UOW?

public DataTable GetPocPivotQueue(int? tenantId)
        {
            // creates resulting Queue
            var result = new DataTable();

            // Use DataTables to extract the whole table in one hit
            using (SqlDataAdapter da = new SqlDataAdapter("[dbo].[sp_GetPocInPivot]", this.Context.Database.Connection.ConnectionString))
            {
                da.Fill(result);
            }

            return result;
        }

0

alirizaadiyahsi created 7 years ago

Right, it wont break.
0

bilalhaidar created 7 years ago

Thanks :-)

Can you point me to the source code of UOW just to dig more and understand the idea better?
0

bilalhaidar created 7 years ago

Hello, This works fine when there are no parameters to feed in. Once I introduced parameters, no results are returned. I am trying to use a DataReader now. Since this is a single method on an AppService to generate an excel, is it possible to Open connection and Close it after executing reader?

Are SQL connections usually opened/closed per UOW method? If yes, then I should be able to open/close the connection without affecting anything else.

Or else, I need to create a new SqlConnection using the ConnectionString from the DbContext.Connection.ConnectionString.

Can you guide in here?

Thanks
0

ismcagdas created 7 years ago
Support Team

Hi Bilal,

You can find UoW related code for EF here <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/tree/master/src/Abp.EntityFramework/EntityFramework/Uow">https://github.com/aspnetboilerplate/as ... mework/Uow</a>.

Your problem might not be related to UoW, can you check SQL profiler to see the executing qurey when you add parameters to your query ?

Thanks.

bilalhaidar created 7 years ago

Hi Ismail, I ended up with this functional code, in case someone else needs it:

public async Task<DataTable> GetPocPivotQueueAsync(int? tenantId, string filter)
        {
            return await Task.Run(() =>
            {
                return GetPocPivotQueue(tenantId, filter);
            });
        }

        public DataTable GetPocPivotQueue(int? tenantId, string filter)
        {
            // creates resulting Queue
            var result = new DataTable();

            SqlCommand cmd = null;
            try
            {
                // prepare command
                cmd = new SqlCommand("[dbo].[sp_GetPocInPivot]", (SqlConnection)this.Context.Database.Connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@TenantId", tenantId.HasValue ? tenantId.Value : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@Filter", string.IsNullOrEmpty(filter) ? (object)DBNull.Value : filter);

                // Use DataTables to extract the whole table in one hit
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    // fill the datatable
                    da.Fill(result);
                }
            }
            finally
            {
                if (cmd != null)
                {
                    // close command
                    cmd.Dispose();
                }
            }

            return result;
        }

tteoh created 7 years ago

<cite>drcgreece: </cite> Hi Ismail, I ended up with this functional code, in case someone else needs it:

public async Task<DataTable> GetPocPivotQueueAsync(int? tenantId, string filter)
       {
           return await Task.Run(() =>
           {
               return GetPocPivotQueue(tenantId, filter);
           });
       }

       public DataTable GetPocPivotQueue(int? tenantId, string filter)
       {
           // creates resulting Queue
           var result = new DataTable();

           SqlCommand cmd = null;
           try
           {
               // prepare command
               cmd = new SqlCommand("[dbo].[sp_GetPocInPivot]", (SqlConnection)this.Context.Database.Connection);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@TenantId", tenantId.HasValue ? tenantId.Value : (object)DBNull.Value);
               cmd.Parameters.AddWithValue("@Filter", string.IsNullOrEmpty(filter) ? (object)DBNull.Value : filter);

               // Use DataTables to extract the whole table in one hit
               using (SqlDataAdapter da = new SqlDataAdapter(cmd))
               {
                   // fill the datatable
                   da.Fill(result);
               }
           }
           finally
           {
               if (cmd != null)
               {
                   // close command
                   cmd.Dispose();
               }
           }

           return result;
       }

Hi, would you able to provide more details on the actual implementation?

Are you using Custom Repository or EF Integration-Custom Repository methods?

Thanks. /tommy

0

bilalhaidar created 7 years ago

Yes this is the code inside custom repository.
I define a new interface in the .Core app. Then the implementation is inside .Entityframework project
0

john created 6 years ago

Did you ever get that link to code? Thanks
0

ismcagdas created 6 years ago
Support Team

Hi @John,

Do you need a custom repository sample ?

Thanks.

bilalhaidar created 6 years ago

Yes this is the code:

public async Task<DataTable> GetPocPivotQueueAsync(int? tenantId, string firstOrLastName, bool? isActive)
        {
            return await Task.Run(() =>
            {
                return GetPocPivotQueue(tenantId, firstOrLastName, isActive);
            });
        }

        public DataTable GetPocPivotQueue(int? tenantId, string firstOrLastName, bool? isActive)
        {
            // creates resulting Queue
            var result = new DataTable();

            SqlCommand cmd = null;
            try
            {
                // prepare command
                cmd = new SqlCommand("[dbo].[sp_GetPocInPivot]", (SqlConnection)this.Context.Database.Connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@TenantId", tenantId.HasValue ? tenantId.Value : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@Filter", string.IsNullOrEmpty(firstOrLastName) ? (object)DBNull.Value : firstOrLastName);
                cmd.Parameters.AddWithValue("@IsActive", !isActive.HasValue ? (object)DBNull.Value : isActive.Value);

                // Use DataTables to extract the whole table in one hit
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    // fill the datatable
                    da.Fill(result);
                }
            }
            finally
            {
                if (cmd != null)
                {
                    // close command
                    cmd.Dispose();
                }
            }

            return result;
        }

0

ismcagdas created 6 years ago
Support Team

Thanks @drcgreece :)
0

bilalhaidar created 6 years ago

Always welcome and happy to share my little knowledge in this framework with others :-)
0

ismcagdas created 6 years ago
Support Team

We really appriciate it :)

Have an answer to this question? Log in and write your answer.