Get multiple result set from stored proc using Entity Framework

October 30, 2015

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 ūüôā


Get updated table list between particular duration

October 2, 2013

Hi,

Here is a table valued function to get updated table list between particular two datetime in sql database.


CREATE FUNCTION [dbo].[GetLastUpdateTablesDetail](@FromDate datetime,@ToDate datetime)
RETURNS @trackingItems TABLE (
ID    int    identity NOT NULL,
TableName    varchar(100)    NOT NULL,
LastUpdateDate    datetime    NOT NULL,
LastScanDate datetime    NULL
)
AS
BEGIN
set @FromDate =  DATEADD(MINUTE, -1, CONVERT(VARCHAR(10), @FromDate, 101))
set @ToDate = DATEADD(DAY, 1, CONVERT(VARCHAR(10), @ToDate, 101))

INSERT INTO @trackingItems (TableName, LastUpdateDate,LastScanDate)
SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
last_user_update,
last_system_scan
FROM sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID(DB_NAME())    AND
OBJECT_NAME(OBJECT_ID) in (select Name from sys.tables)
AND sys.dm_db_index_usage_stats.last_user_update >= @FromDate and sys.dm_db_index_usage_stats.last_user_update <= @ToDate
order by sys.dm_db_index_usage_stats.last_user_update desc

RETURN;
END;

then run this function like

 


SELECT * FROM  [dbo].[GetLastUpdateTablesDetail]('2013/10/01','2013/10/02')

You will get output like below  snapshot,You will get table name with updated date and time.

GetLastUpdateTablesDetail

 


Create entities by using MS Sql in asp.net

July 25, 2013

Hello,

Sometime it is very difficult stuff to create entities for each table.

Here is a stored procedure,where we have to pass just table name only and it will automatically create entities(output as a string message)  as an output.

I’ve table Database named MyTest,which is containing table dbo.tblSLtest which structure is like a

structure

For create the entities for this table execute this stored procedure

DECLARE @TableName VARCHAR(MAX) = ‘tblSLtest’
DECLARE @TableSchema VARCHAR(MAX) = ‘dbo’
DECLARE @result varchar(max) = ”

SET @result = @result + ‘using System;’ + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + ‘namespace ‘ + @TableSchema¬† + CHAR(13) + ‘{‘ + CHAR(13)
END

SET @result = @result + ‘public class ‘ + @TableName + CHAR(13) + ‘{‘ + CHAR(13)

SET @result = @result + ‘#region Instance Properties’ + CHAR(13)

SELECT @result = @result + CHAR(13)
+ ‘ public ‘ + ColumnType + ‘ ‘ + ColumnName + ‘ { get; set; } ‘ + CHAR(13)
FROM
(
SELECT  c.COLUMN_NAME   AS ColumnName
, CASE c.DATA_TYPE
WHEN ‘bigint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int64?’ ELSE ‘Int64’ END
WHEN ‘binary’ THEN ‘Byte[]’
WHEN ‘bit’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Boolean?’ ELSE ‘Boolean’ END
WHEN ‘char’ THEN ‘String’
WHEN ‘date’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetime’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetime2’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetimeoffset’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTimeOffset?’ ELSE ‘DateTimeOffset’ END
WHEN ‘decimal’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘float’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Single?’ ELSE ‘Single’ END
WHEN ‘image’ THEN ‘Byte[]’
WHEN ‘int’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int32?’ ELSE ‘Int32’ END
WHEN ‘money’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘nchar’ THEN ‘String’
WHEN ‘ntext’ THEN ‘String’
WHEN ‘numeric’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘nvarchar’ THEN ‘String’
WHEN ‘real’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Double?’ ELSE ‘Double’ END
WHEN ‘smalldatetime’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘smallint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int16?’ ELSE ‘Int16’END
WHEN ‘smallmoney’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘text’ THEN ‘String’
WHEN ‘time’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘TimeSpan?’ ELSE ‘TimeSpan’ END
WHEN ‘timestamp’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘tinyint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Byte?’ ELSE ‘Byte’ END
WHEN ‘uniqueidentifier’ THEN ‘Guid’
WHEN ‘varbinary’ THEN ‘Byte[]’
WHEN ‘varchar’ THEN ‘String’
ELSE ‘Object’
END AS ColumnType
, c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + ‘#endregion Instance Properties’ + CHAR(13)

SET @result = @result¬† + ‘}’ + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + CHAR(13) + ‘}’
END

PRINT @result

and you we have output like(with corresponding datatypes)

output

So now enjoy with entities…


Get day count for particular month using MS Sql

July 24, 2013

Hello,

By using below code you can find the day count-number of days in particular month

So here it is,

DECLARE @dtDate DATETIME
SET @dtDate = ‘8/18/2007’

select datediff(day, dateadd(day, 1-day(@dtDate), @dtDate),
dateadd(month, 1, dateadd(day, 1-day(@dtDate), @dtDate)))  as dayCount

you will get output like,

Capture

Here august month passed in dtDate parameter of sql so will have an output 31 day count.


Select the inserted/updated value to get affected rows in single query with OUTPUT keyword

April 3, 2013

Select the inserted/Updated Value to get affected rows in single query with OUTPUT keyword

for insert query,

Insert Into tblTest(Name) OutPut Inserted.TestID,Inserted.Name values(‘Juhi’)

Output you will get

Capture

The same thing for update ,

Update tblTest set Name=’Juhi Paunikar’ OutPut inserted.TestID,inserted.Name

The output you will get

bg.jpg Capture1 Enjoy “Sql Queries….!!!”


This query is useful for create table and copy the schema and data from another table

March 8, 2013

–This query is useful for create table and copy the schema and data from another table

select * into tbldemo from dbo.tblEmployee

tbldemo-New table which have to create
dbo.tblEmployee-Old table ,Copy schema and data from this table
—Copy only schemaa not data

SELECT *
INTO my_new_table1
FROM tblEmployee
WHERE 1=2
–it will not generate p.k


MS Sql Server stores all database details

February 27, 2013

MS SQL Server Stores Detail in System table

SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE type = ‘p’
Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table