August 30, 2018 | Posted in: .Net

If you want to call stored procedures via EF, there is a way, which allows you to keep the entities aspect, and in particular, allows for the transposing of table rows to your own custom DTO’s, which will end up being your entites. Or, if you want a simple conversion from a list of entities to something like a dataset that is also possible. In simplistic terms you need to:

1) Create a new model (edmx) from your database, and when it asks which database schema you want to inject i.e. tables, stored procedures etc, choose none (it will ask you if you are sure, say yes)

2) Copy the new connection string in to your main application configuration file otherwise an exception will get raised when opening the connection

3) Create a DAL and to call your stored procedure

Here is an example:

public class SystemWarningsDTO
{
public string WarningType { get; set; }

public string Supplier { get; set; }

public int Count { get; set; }

public string SuggestedFix { get; set; }
}

public sealed class Adhoc : IDisposable
{
#region class declarations

private bool _disposed = false;

private AdhocQueriesEntities _objectContext = null;

#endregion

#region “constructors”

public Adhoc()
{
this._objectContext = new AdhocQueriesEntities();
}

#endregion

#region “destructor”

~Adhoc()
{
this.Dispose(false);
}

#region IDisposable

public void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}

#endregion

private void Dispose(bool disposing)
{
if (!this._disposed)
{
if (disposing)
{
//// TODO: dispose managed resources
}

//// TODO: Call the appropriate methods to clean up unmanaged resources here

this._disposed = true;
}
}

#endregion

#region “properties”
#endregion

#region “methods”
#endregion

#region “functions”

private static DataTable ConvertToDatatable<T>(IList<T> data)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
table.Columns.Add(prop.Name, prop.PropertyType.GetGenericArguments()[0]);
else
table.Columns.Add(prop.Name, prop.PropertyType);
}

object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}

public DataSet DoSomething()
{
DataSet dataSource = new DataSet();

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
DbDataReader reader = cmd.ExecuteReader();

IList<SystemWarningsDTO> systemWarnings =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<SystemWarningsDTO>(reader).ToList();

DataTable warnings = ConvertToDatatable(systemWarnings);

dataSource.Tables.Add(warnings);
}
catch (Exception)
{
throw;
}

return dataSource;
}

#endregion

#region “base overrides”

#region “properties”
#endregion
#region “methods”
#endregion
#region “functions”
#endregion

#endregion

#region “events”
#endregion
}

 

That accounts for the get side of things, but what if you want to update via a stored procedure? Well that is also a straight forward operation, below is an example function that would achieve this:

public bool DoSomething(string invoiceLineIds,
string creditOrderNumber,
string creditNotes,
string resolvedBy,
DateTime resolvedOn)
{
bool returnVal = false;

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

DbParameter invoiceLineIdsSqlParam = cmd.CreateParameter();
invoiceLineIdsSqlParam.ParameterName = “@invoiceLineIds”;
invoiceLineIdsSqlParam.Value = invoiceLineIds;
cmd.Parameters.Add(invoiceLineIdsSqlParam);

DbParameter creditOrderNumberSqlParam = cmd.CreateParameter();
creditOrderNumberSqlParam.ParameterName = “@creditOrderNumber”;
creditOrderNumberSqlParam.Value = creditOrderNumber;
cmd.Parameters.Add(creditOrderNumberSqlParam);

DbParameter creditNotesSqlParam = cmd.CreateParameter();
creditNotesSqlParam.ParameterName = “@creditNotes”;
creditNotesSqlParam.Value = creditNotes;
cmd.Parameters.Add(creditNotesSqlParam);

DbParameter resolvedBySqlParam = cmd.CreateParameter();
resolvedBySqlParam.ParameterName = “@resolvedBy”;
resolvedBySqlParam.Value = resolvedBy;
cmd.Parameters.Add(resolvedBySqlParam);

DbParameter resolvedOnSqlParam = cmd.CreateParameter();
resolvedOnSqlParam.ParameterName = “@resolvedOn”;
resolvedOnSqlParam.Value = resolvedOn;
cmd.Parameters.Add(resolvedOnSqlParam);

DbParameter returnSqlParam = cmd.CreateParameter();
returnSqlParam.ParameterName = “@result_value”;
returnSqlParam.DbType = DbType.Int32;
returnSqlParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnSqlParam);

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
cmd.ExecuteNonQuery();

int result = (int)cmd.Parameters["@result_value"].Value;

if (result > 0)
returnVal = true;
}
catch (Exception)
{
throw;
}

return returnVal;
}

 

And finally, what if there are multiple rowsets being returned from our stored procedure, they have a parent/child relationship and you want to display them in some type of grid control? Well that is also straightforward enough, here is an example of how to achieve that by trasposing, in this example, invoice headers and invoice lines:

public DataSet DoSomething(string supplierCode,
string poNumber,
string invoiceNumber,
DateTime? invoicesFrom,
DateTime? invoicesTo,
string fileName,
bool includeDiscrepancyResolved,
bool includeNoDiscrepancy)
{
DataSet dataSource = new DataSet();

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

DbParameter supplierCodeSqlParam = cmd.CreateParameter();
supplierCodeSqlParam.ParameterName = “@supplierCode”;
supplierCodeSqlParam.Value = string.IsNullOrWhiteSpace(supplierCode) ? (object)DBNull.Value : supplierCode;
supplierCodeSqlParam.IsNullable = true;
cmd.Parameters.Add(supplierCodeSqlParam);

DbParameter poNumberSqlParam = cmd.CreateParameter();
poNumberSqlParam.ParameterName = “@poNumber”;
poNumberSqlParam.Value = string.IsNullOrWhiteSpace(poNumber) ? (object)DBNull.Value : poNumber;
poNumberSqlParam.IsNullable = true;
cmd.Parameters.Add(poNumberSqlParam);

DbParameter invoiceNumberSqlParam = cmd.CreateParameter();
invoiceNumberSqlParam.ParameterName = “@invoiceNumber”;
invoiceNumberSqlParam.Value = string.IsNullOrWhiteSpace(invoiceNumber) ? (object)DBNull.Value : invoiceNumber;
invoiceNumberSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceNumberSqlParam);

DbParameter invoiceDateFromSqlParam = cmd.CreateParameter();
invoiceDateFromSqlParam.ParameterName = “@invoiceDateFrom”;
invoiceDateFromSqlParam.Value = invoicesFrom ?? (object)DBNull.Value;
invoiceDateFromSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceDateFromSqlParam);

DbParameter invoiceDateToSqlParam = cmd.CreateParameter();
invoiceDateToSqlParam.ParameterName = “@invoiceDateTo”;
invoiceDateToSqlParam.Value = invoicesTo ?? (object)DBNull.Value;
invoiceDateToSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceDateToSqlParam);

DbParameter fileNameSqlParam = cmd.CreateParameter();
fileNameSqlParam.ParameterName = “@fileName”;
fileNameSqlParam.Value = string.IsNullOrWhiteSpace(fileName) ? (object)DBNull.Value : fileName;
fileNameSqlParam.IsNullable = true;
cmd.Parameters.Add(fileNameSqlParam);

DbParameter includeDiscrepancyResolvedSqlParam = cmd.CreateParameter();
includeDiscrepancyResolvedSqlParam.ParameterName = “@includeDiscrepancyResolved”;
includeDiscrepancyResolvedSqlParam.Value = includeDiscrepancyResolved;
includeDiscrepancyResolvedSqlParam.IsNullable = true;
cmd.Parameters.Add(includeDiscrepancyResolvedSqlParam);

DbParameter includeNoDiscrepancySqlParam = cmd.CreateParameter();
includeNoDiscrepancySqlParam.ParameterName = “@includeNoDiscrepancy”;
includeNoDiscrepancySqlParam.Value = includeNoDiscrepancy;
includeNoDiscrepancySqlParam.IsNullable = true;
cmd.Parameters.Add(includeNoDiscrepancySqlParam);

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
DbDataReader reader = cmd.ExecuteReader();

IList<InvoiceDiscrepancyHeaderDTO> invoiceHeaders =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<InvoiceDiscrepancyHeaderDTO>(reader).ToList();

// move to invoice lines
reader.NextResult();

IList<InvoiceDiscrepancyLineDTO> invoiceLines =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<InvoiceDiscrepancyLineDTO>(reader).ToList();

DataTable headers = ConvertToDatatable(invoiceHeaders);
DataTable lines = ConvertToDatatable(invoiceLines);

dataSource.Tables.Add(headers);
dataSource.Tables.Add(lines);

DataRelation newRelation = new DataRelation(“PK_RecID”, headers.Columns["RecID"], lines.Columns["RecID"]);
dataSource.Relations.Add(newRelation);
}
catch (Exception)
{
throw;
}

return dataSource;
}

 

Be the first to comment.

Leave a Reply

*


nine − six =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>