While supporting flexible schema, often it is required to construct and execute dynamic queries.
It is also sometimes required to keep data in temp tables for processing.
Creating temp tables in dynamic sql puts the table out of context for the connection.
Once solution is to create a table in code, then create the definition for the table using dynamic sql and then dump data into the table via dynamic sql as shown below.
create table #t (id int)
exec( N'alter table #t add ActivityId int , AssetId int')
declare @sql nvarchar(max)
set @sql = 'select * from ActivityExportData'
insert into #t EXECUTE sp_executesql @sql
alter table #t add id_identity int identity(1,1)
select * from #t
drop table #t
Tuesday, April 7, 2015
Wednesday, August 21, 2013
Generate Repository Pattern for Entity Framework DbContext
Generating Repositories for the database entities could be a big repetitive task with similar code. The project at https://efrepository.codeplex.com solves the problem by generating a T4 Template for the edmx model. The T4 Template then generates the required repositories, the Unit of Work pattern, and also provides a factory for the repositories.
The template, however, generates code for the ObjectContext approach.
I have modified the template, so that it can be used with the EntityFramework 5.0 and above to generate the relevant repositories.
Here is the Template below.
//==========================================================================
<#@ template language="C#" debug="true" hostspecific="true"#>
<#@ include file="EF.Utility.CS.ttinclude"#>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ output extension=".cs"#>
<#
// This needs to be set to the .edmx file that you want to process.
string edmxFile = FindEDMXFileName(); // @"Model1.edmx";
CodeGenerationTools code = new CodeGenerationTools(this);
MetadataLoader loader = new MetadataLoader(this);
MetadataTools ef = new MetadataTools(this);
#>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace <#= code.VsNamespaceSuggestion() #>
{
public interface IRepository
{
IUnitOfWork UnitOfWork { get; set; }
IQueryable All();
IQueryable Where(Func expression);
void Add(T entity);
void Delete(T entity);
}
}<#
EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(edmxFile);
EntityContainer container = ItemCollection.GetItems().FirstOrDefault();
EntityFrameworkTemplateFileManager fileManager = EntityFrameworkTemplateFileManager.Create(this);
foreach (EntityType entity in ItemCollection.GetItems().OrderBy(e => e.Name))
{;
if(!DoesFileExist(entity.Name + "Repository.cs"))
{
fileManager.StartNewFile(entity.Name + "Repository.cs");
#>using System;
using System.Linq;
using System.Collections.Generic;
namespace <#= code.VsNamespaceSuggestion() #>
{
<#=Accessibility.ForType(entity)#> <#=code.SpaceAfter(code.AbstractOption(entity))#>partial class <#=code.Escape(entity)#>Repository : EFRepository<<#=code.Escape(entity)#>>, I<#=code.Escape(entity)#>Repository
{
}
<#=Accessibility.ForType(entity)#> <#=code.SpaceAfter(code.AbstractOption(entity))#> interface I<#=code.Escape(entity)#>Repository
{
}
}<#
}
else
{
fileManager.StartNewFile(entity.Name + "Repository.cs");
this.Write(OutputFile(entity.Name + "Repository.cs"));
}
}
fileManager.StartNewFile("IUnitOfWork.cs");
#>using System.Data.Objects;
using System.Data.Entity;
namespace <#= code.VsNamespaceSuggestion() #>
{
public interface IUnitOfWork
{
DbContext Context { get; set; }
void Commit();
bool LazyLoadingEnabled { get; set; }
bool ProxyCreationEnabled { get; set; }
string ConnectionString { get; set; }
}
}<# fileManager.StartNewFile("RepositoryIQueryableExtensions.cs");
#>using System.Data.Objects;
using System.Linq;
namespace <#= code.VsNamespaceSuggestion() #>
{
public static class RepositoryIQueryableExtensions
{
public static IQueryable Include
(this IQueryable source, string path)
{
var objectQuery = source as ObjectQuery;
if (objectQuery != null)
{
return objectQuery.Include(path);
}
return source;
}
}
}<# fileManager.StartNewFile("EFRepository.cs");
#>using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
namespace <#= code.VsNamespaceSuggestion() #>
{
public class EFRepository : IRepository where T : class
{
public IUnitOfWork UnitOfWork { get; set; }
private DbSet _dbSet;
private DbSet DbSet
{
get
{
if (_dbSet == null)
{
_dbSet = UnitOfWork.Context.Set();
}
return _dbSet;
}
}
public virtual IQueryable All()
{
return DbSet.AsQueryable();
}
public IQueryable Where(Func expression)
{
return DbSet.Where(expression).AsQueryable();
}
public void Add(T entity)
{
DbSet.Add(entity);
}
public void Delete(T entity)
{
DbSet.Remove(entity);
}
}
}<#fileManager.StartNewFile("EFUnitOfWork.cs");
#>using System.Data.Objects;
using System.Data.Entity;
namespace <#= code.VsNamespaceSuggestion() #>
{
public class EFUnitOfWork : IUnitOfWork
{
public DbContext Context { get; set; }
public EFUnitOfWork()
{
Context = new <#=code.Escape(container)#>();
}
public void Commit()
{
Context.SaveChanges();
}
public bool LazyLoadingEnabled
{
get { return true; }
set { }
}
public bool ProxyCreationEnabled
{
get { return true; }
set { }
}
public string ConnectionString
{
get { return Context.Database.Connection.ConnectionString; }
set { Context.Database.Connection.ConnectionString = value; }
}
}
}
<#fileManager.StartNewFile("RepositoryHelper.cs");
#>
namespace <#= code.VsNamespaceSuggestion() #>
{
public static class RepositoryHelper
{
public static IUnitOfWork GetUnitOfWork()
{
return new EFUnitOfWork();
}
<# foreach (EntityType entity in ItemCollection.GetItems().OrderBy(e => e.Name))
{; #>
public static <#=code.Escape(entity)#>Repository Get<#=code.Escape(entity)#>Repository()
{
return new <#=code.Escape(entity)#>Repository();
}
public static <#=code.Escape(entity)#>Repository Get<#=code.Escape(entity)#>Repository(IUnitOfWork unitOfWork)
{
var repository = new <#=code.Escape(entity)#>Repository();
repository.UnitOfWork = unitOfWork;
return repository;
}
<# } #>
}
}<# fileManager.Process();
#>
<#+
bool DoesFileExist(string filename)
{
return File.Exists(Path.Combine(GetCurrentDirectory(),filename));
}
string OutputFile(string filename)
{
using(StreamReader sr = new StreamReader(Path.Combine(GetCurrentDirectory(),filename)))
{
string contents = sr.ReadToEnd();
return contents;
}
}
string GetCurrentDirectory()
{
string executingDirectoryName = "";
string stackTraceFileName = new StackTrace(true).GetFrame(0).GetFileName();
if (String.IsNullOrEmpty(stackTraceFileName))
{
throw new ArgumentException("No value was specified for the 'directoryName' configuration parameter" +
", and we could not figure out the file name from the stack trace (most likely because of running " +
"the template with debug='False' specified in the <\u0023@ template \u0023> directive.");
}
else
{
executingDirectoryName = Path.GetDirectoryName(stackTraceFileName);
}
return executingDirectoryName;
}
string FindEDMXFileName()
{
string edmxFile = "";
string[] entityFrameworkFiles = Directory.GetFiles(GetCurrentDirectory(), "*.edmx");
if(entityFrameworkFiles.Length > 0)
edmxFile = entityFrameworkFiles[0];
return edmxFile;
}
#>
Thursday, August 15, 2013
CTE up and down -- finding path to a set of specific nodes in a parent child heirarchy
While dealing with hierarchical data, most of us have
used Common Table Expressions (CTEs) to find all the children or parents of the
node.
Here is how we can use the CTEs to calculate and build the
path to a particular node with having to traverse the complete data set.
/* Let us create the
table and fill it with demo data. */
Declare @SelectedIds table(Id int not null);
create table #ParentChild(
Id int not null,
ParentId int NULL,
CONSTRAINT pk_ParentChild
PRIMARY KEY (Id));
insert into #ParentChild values (11,null);
insert into #ParentChild values (2555,11);
insert into #ParentChild values (2666,11);
insert into #ParentChild values (2777,11);
insert into #ParentChild values (38888,2555);
insert into #ParentChild values (39999,2555);
insert into #ParentChild values (37777,2666);
insert into #ParentChild values (32222,2666);
insert into #ParentChild values (499999,38888);
insert into #ParentChild values (488888,38888);
insert into #ParentChild values ( 422222,37777);
insert into #ParentChild values (411111,37777);
/* The following CTE
is used to calculate the path to all the child nodes. Starting from the parent
down to the leaf*/
;with cte as (
select Id,ParentId, cast('' as varchar(500)) as AllParents from #ParentChild
where ParentId is null
union ALL
select pc.Id,pc.ParentId ,cast((cast(cte.AllParents as varchar(500))+','+(cast(pc.ParentId as varchar(500)))) as varchar(500)) as AllParents
from #ParentChild pc join cte on pc.ParentId = cte.Id
)
select ID, ParentId, STUFF( AllParents,1,1,'') AllParents from cte;
/* Results of the previous Query
-------------------------------
Id
ParentId AllParents
11 NULL
2555 11 11
2666 11 11
2777 11 11
32222 2666 1,2666
37777 2666 1,2666
411111 37777 ,2666,37777
422222 37777 ,2666,37777
38888 2555 1,2555
39999 2555 1,2555
488888 38888 ,2555,38888
499999 38888 ,2555,38888
*/
/* We need to find
the complete path to these two nodes in the hierarchy without traversing the
complete tree. */
insert into @SelectedIds values (422222);
insert into @SelectedIds values (488888);
/* The following CTE
queries first find the path to the top root parent from the child leaf nodes.
Then the second
query joins with the first one to get only the nodes that fall in the path for
the leaf nodes we are interested in.
*/
;with cte as (
select * from #ParentChild
where Id in (select id from @SelectedIds)
union ALL
select pc.* from #ParentChild pc join cte on pc.Id = cte.ParentId
)
,cte2 as (
select ID, ParentId, cast('' as varchar(500)) as AllParents
from cte
where ParentId is null
union all
select pc.Id, pc.ParentId,cast((cast(td.AllParents as varchar(500))+','+(cast(pc.ParentId as varchar(500)))) as varchar(500)) as AllParents
from cte2 td
inner join #ParentChild pc on pc.ParentId = td.Id
inner join cte c on c.Id=pc.Id
)
select distinct cte2.Id,ParentId, STUFF( AllParents,1,1,'') AllParents from cte2
join @SelectedIds si on si.Id=cte2.Id
/* Results of the previous Query
-------------------------------
Id
ParentId AllParents
422222 37777 11,2666,37777
488888 38888 11,2555,38888
*/
drop table #ParentChild
Tuesday, July 23, 2013
Pivoting data in SqlServer
While working on transforming data (ETL)
from a Entity-attribute-value data model,
there are several challenges. Once of which is to transform data between values
in a column in table and a string representation (like all values concatenated
in a comma separated string), which I already described in one of my blog.
Another interesting challenge is to be able
to transform attribute-values to a row structure, so that the attributes become
column names, and you can see all the values for the attributes in a row.
I used the pivot function in SqlServer to achieve this as described below.
You need to know the attribute names to be
able to lay them across as columns, but it is easy to generate sql string using
my previous blog on concatenating strings for rows of a column.
CREATE TABLE #EAV_Test (
ID
int not null,
code
VARCHAR(30) NOT NULL,
value
VARCHAR(200),
CONSTRAINT
pk_EAV_Test
PRIMARY
KEY (ID, code));
INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Risk', 'High');
INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Priority', 'Med');
INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'type', 'personal');
INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'date', '20080215');
INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'amount', '3500.00');
INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Risk', 'Low');
INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Priority', 'High');
INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'type', 'open');
INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'amount', '250.00');
SELECT ID, code, value FROM #EAV_Test;
select ID,Amount,[Date],[Priority],[Risk],[Type] from
(
select Id, Code,Value from #EAV_Test) as t
pivot (max(value) FOR code in (Amount,[Date],[Priority],[Risk],[Type])) as pt
order
by ID;
/* Results for previous query
ID Amount Date Priority Risk Type
1 3500.00 20080215 Med High personal
2 250.00 NULL High Low open
*/
drop table #EAV_Test
Subscribe to:
Posts (Atom)