-
Notifications
You must be signed in to change notification settings - Fork 20
Examples
Adam edited this page May 26, 2015
·
2 revisions
A solution with these simple examples is included with the code for you to download.
Given a stored procedure that looks like this:
CREATE PROCEDURE [dbo].[usp_TaskGet]
@TaskId int
AS
-- Specifying "TOP 1" makes the generated return value a single instance instead of an array.
SELECT TOP 1
t.Name, t.[Description], t.Created, t.CreatedBy, t.Updated, t.UpdatedBy,
ts.Name as [Status]
FROM Tasks t
JOIN TaskStatus ts ON t.TaskStatusId = ts.Id
WHERE t.Id = @TaskId
The included pre-compiled Stored Procedure template will generate C# code that includes a function to call the stored procedure, DTO objects for the input and output of the stored procedure, and the interfaces all the objects implement so you can do dependency-injection:
// ------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by SqlSharpener.
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
// ------------------------------------------------------------------------------
namespace SimpleExample.DataLayer
{
using System;
using System.IO;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;
/// <summary>
/// Interface of the wrapper class for calling stored procedures.
/// </summary>
public partial interface IStoredProcedures
{
/// <summary>
/// Calls the "usp_TaskGet" stored procedure
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
Result<TaskGetOutputDto> TaskGet( TaskGetInputDto input );
/// <summary>
/// Calls the "usp_TaskGet" stored procedure using POCO objects
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
TPocoOutputDto TaskGet<TPocoOutputDto>( IProcedureInputDto input )
where TPocoOutputDto : IProcedureOutputDto<Result<TaskGetOutputDto>>, new();
/// <summary>
/// Calls the "usp_TaskGet" stored procedure
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
Result<TaskGetOutputDto> TaskGet( Int32? TaskId );
}
/// <summary>
/// Interface that a POCO can implement to be able to pass it in as the input DTO of a stored procedure
/// if you prefer not to use the generated input dto.
/// </summary>
public partial interface IProcedureInputDto
{
/// <summary>
/// Converts the property values of the POCO into an array of objects.
/// The order of values in the array should match the parameters of the
/// stored procedure (excluding any output parameters).
/// </summary>
object[] ToObjectArray();
/// <summary>
/// Sets property values of the POCO with values from any output parameters
/// of the stored procedure. Value will be passed in the same order as the
/// output parameters appear in the stored procedure.
/// </summary>
void SetFromOutputParameters(object[] outputValues);
}
/// <summary>
/// Interface that a POCO can implement to be used as the output DTO of a stored procedure
/// if you prefer not to use the generated output dto.
/// </summary>
public partial interface IProcedureOutputDto<TGeneratedOutput>
{
/// <summary>
/// Sets property values of the POCO with values from any output parameters
/// of the stored procedure. Value will be passed in the same order as the
/// output parameters appear in the stored procedure.
/// </summary>
void SetFromResult(TGeneratedOutput result);
}
/// <summary>
/// Interface that a POCO can implement to be able to pass it into a table-valued parameter
/// if you prefer not to use the generated parameter dto.
/// </summary>
public partial interface ITableValuedParamRow
{
SqlDataRecord ToSqlDataRecord();
}
/// <summary>
/// Wrapper class for calling stored procedures.
/// </summary>
public partial class StoredProcedures : IStoredProcedures
{
private string connectionString;
public StoredProcedures(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// Calls the "usp_TaskGet" stored procedure using a generated input DTO
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
public virtual Result<TaskGetOutputDto> TaskGet( TaskGetInputDto input )
{
var result = this.TaskGet(input.TaskId);
return result;
}
/// <summary>
/// Calls the "usp_TaskGet" stored procedure using POCO objects
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
public virtual TPocoOutputDto TaskGet<TPocoOutputDto>( IProcedureInputDto input )
where TPocoOutputDto : IProcedureOutputDto<Result<TaskGetOutputDto>>, new()
{
var parameters = input.ToObjectArray();
var result = this.TaskGet((Int32?)parameters[0]);
var outputValues = new List<object>();
input.SetFromOutputParameters(outputValues.ToArray());
var outputPoco = new TPocoOutputDto();
outputPoco.SetFromResult(result);
return outputPoco;
}
/// <summary>
/// Calls the "usp_TaskGet" stored procedure
/// </summary>
/// <returns>A DTO filled with the results of the SELECT statement.</returns>
public virtual Result<TaskGetOutputDto> TaskGet( Int32? TaskId )
{
OnTaskGetBegin();
Result<TaskGetOutputDto> result = new Result<TaskGetOutputDto>();
using(var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_TaskGet";
cmd.Parameters.Add("TaskId", SqlDbType.Int).Value = (object)TaskId ?? DBNull.Value;
using(var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
result.RecordsAffected = reader.RecordsAffected;
while (reader.Read())
{
var item = new TaskGetOutputDto();
item.Name = reader.GetString(0);
item.Description = reader.GetString(1);
item.Status = reader.GetString(2);
item.Created = reader.GetDateTime(3);
item.CreatedBy = reader.GetString(4);
item.Updated = reader.GetDateTime(5);
item.UpdatedBy = reader.GetString(6);
result.Data = item;
}
reader.Close();
}
}
conn.Close();
}
OnTaskGetEnd(result);
return result;
}
partial void OnTaskGetBegin();
partial void OnTaskGetEnd(Result<TaskGetOutputDto> result);
/// <summary>
/// Helper function to get the bytes out of varbinary columns
/// </summary>
private byte[] GetBytes(IDataReader reader, int ordinal)
{
MemoryStream ms = new MemoryStream();
BinaryWriter writer = new BinaryWriter(ms);
byte[] buffer = new byte[1024];
long blobSize = reader.GetBytes(ordinal, 0, null, 0, 0);
long currPos = 0;
while (currPos < blobSize) {
currPos += reader.GetBytes(ordinal, currPos, buffer, 0, 1024);
writer.Write(buffer);
writer.Flush();
}
writer.Close();
return ms.ToArray();
}
}
/// <summary>
/// The return value of the stored procedure functions.
/// </summary>
public partial class Result<T>
{
public T Data { get; set; }
public int RecordsAffected { get; set; }
}
/// <summary>
/// DTO for the input of the "usp_TaskGet" stored procedure.
/// </summary>
public partial class TaskGetInputDto
{
/// <summary>
/// Property that fills the TaskId input parameter.
/// </summary>
public Int32? TaskId { get; set; }
}
/// <summary>
/// DTO for the output of the "usp_TaskGet" stored procedure.
/// </summary>
public partial class TaskGetOutputDto
{
public String Name { get; set; }
public String Description { get; set; }
public String Status { get; set; }
public DateTime Created { get; set; }
public String CreatedBy { get; set; }
public DateTime Updated { get; set; }
public String UpdatedBy { get; set; }
}
}
Given tables like these:
CREATE TABLE [dbo].[Tasks]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
[Name] VARCHAR(50) NOT NULL,
[Description] VARCHAR(1000) NOT NULL,
[TaskStatusId] INT NOT NULL,
[Created] DATETIME NOT NULL ,
[CreatedBy] VARCHAR(50) NOT NULL,
[Updated] DATETIME NOT NULL,
[UpdatedBy] VARCHAR(50) NOT NULL,
CONSTRAINT [FK_Tasks_ToTaskStatus] FOREIGN KEY ([TaskStatusId]) REFERENCES [TaskStatus]([Id])
)
CREATE TABLE [dbo].[TaskStatus]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL
)
You can easily create a T4 template, such as the one in the simple example solution, that uses SqlSharpener's MetaBuilder class to create Entity Framework Code-First entities:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
namespace SimpleExample.EntityFrameworkCodeFirst
{
public partial class TaskContext : DbContext
{
public TaskContext(): base()
{
}
public DbSet<Tasks> Tasks { get; set; }
public DbSet<TaskStatus> TaskStatus { get; set; }
}
[Table("Tasks")]
public partial class Tasks
{
[Key]
[Required]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Int32? Id { get; set; }
[Required]
[MaxLength(50)]
public String Name { get; set; }
[Required]
[MaxLength(1000)]
public String Description { get; set; }
[Required]
public Int32? TaskStatusId { get; set; }
[ForeignKey("Id")]
public virtual TaskStatus TaskStatus { get; set; }
[Required]
public DateTime? Created { get; set; }
[Required]
[MaxLength(50)]
public String CreatedBy { get; set; }
[Required]
public DateTime? Updated { get; set; }
[Required]
[MaxLength(50)]
public String UpdatedBy { get; set; }
}
[Table("TaskStatus")]
public partial class TaskStatus
{
[Key]
[Required]
public Int32? Id { get; set; }
public virtual ICollection<Tasks> Tasks { get; set; }
[Required]
[MaxLength(50)]
public String Name { get; set; }
}
}