Get a list of month & year for particular duration

Here i have requirement to display month,year wise record count from table.The condition is starting month,year  would be month & year of Min start date in table and end month,year would be current month & year.

So here a query to fulfill the above requirement.

DECLARE @FromDate DATETIME, @ToDate DATETIME
SELECT @FromDate=MIN(CreatedDate) FROM dbo.U***
SET @ToDate = GETDATE()

DECLARE @MonthsAndYears table (Month INT, Year int)

;WITH dates AS
(
    SELECT @FromDate 'date'
        UNION ALL
    SELECT DATEADD(dd, 1, t.date)
        FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= @ToDate
)
INSERT INTO @MonthsAndYears
SELECT
    DATEPART(MONTH, date),
    DATEPART(YEAR, date)
FROM dates
GROUP BY
DATEPART(MONTH, date),
DATEPART(YEAR, date)
option (maxrecursion 0)

SELECT *,CONCAT(DATENAME(MONTH,DATEADD(MONTH,  [Month] - 1, 0)),',',[Year]) AS Duration FROM @MonthsAndYears

Result is :

By using outer apply with your table on column created date (month & year part) to month and year column in query result ,we can get the desired result.

Get multiple result set from stored proc using Entity Framework

Hello folks,

I have some strange requirement.I want multiple result set from stored procedure.It does not seems a tough task but it’s taken few minutes to search out on google ,’cause i need this one in entity framework.So here i’m sharing the code snippet for the same.

For this we need to create wrapper class .Wrapper class is nothing but a simple class which wraps other types of class in single object.Seems quite confusing.Let’s go through example.

For example you have class named MyClass1,MyClass2,MyClass3 ,and you have stored proc which is returns these three types of result set.

So wrapper class would be,

public class WrapperClass
{
 public MyClass1 class1 { get; set; }
 
 public List<MyClass2> class2 { get; set; }
 
 public MyClass2 class2 { get; set; }
}

Our wrapper class ready now.

So here is a method to get result set from stored procedure.

public static WrapperClass GetMultipleResultSet(int Id)
 {
 WrapperClass wrapper = new WrapperClass();

 using (var context = new myDBContext())
 {
 context.Database.Initialize(force: false);
 var cmd = context.Database.Connection.CreateCommand();
 cmd.CommandText = "[dbo].[YourStoredProcedureName] @Id";
 
 var param = cmd.CreateParameter();
 param.ParameterName = "@Id";
 param.Value = Id;

 cmd.Parameters.Add(param);

 context.Database.Connection.Open(); 

 // Run the sproc 
 var reader = cmd.ExecuteReader();

 // Read first result set 
 wrapper.class1 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass1>(reader).FirstOrDefault();

 reader.NextResult();

 wrapper.class2 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass2>(reader).ToList();
 
 reader.NextResult();
 
 wrapper.class3 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass3>(reader).FirstOrDefault();
 
 return wrapper;
 }
 }

Happy Coding 🙂