public static WebServiceResult SQLExecuteNonQuery(this DynamicDBRequest Request, string SQLSprocName, string DBConnectionString)
{
WebServiceResult Result = new WebServiceResult();
SprocSchema Schema = null;
RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);
using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CompleteSQLRequest(Request, ref Schema);
try
{
cmd.ExecuteNonQuery();
try
{
foreach (SprocParam outparam in Schema.OutputParamsList)
{
Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
}
if (Schema.ReturnValue != null)
{
Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
return(Result);
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
return(Result);
}
}
RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
Result.Success = true;
return(Result);
}
public static WebServiceResult SQLExecuteMapObjectFromXML <T>(this DynamicDBRequest Request, T ResultObject, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
{
WebServiceResult Result = new WebServiceResult();
ResultObject = Activator.CreateInstance <T>();
SprocSchema Schema = null;
RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);
using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CompleteSQLRequest(Request, ref Schema);
try
{
cmd.ExecuteNonQuery();
try
{
if (Schema.Serializer != null && Schema.XMLOutParam != null)
{
ResultObject = (T)Helpers.DeserializeDatabaseXML((SqlXml)cmd.Parameters[Schema.XMLOutParam.ParameterName].Value, Schema.Serializer);
}
else
{
foreach (SprocParam outparam in Schema.OutputParamsList)
{
if (outparam.Type == SqlDbType.Xml)
{
Schema.Serializer = new XmlSerializer(typeof(T));
Schema.XMLOutParam = outparam;
ResultObject = (T)Helpers.DeserializeDatabaseXML((SqlXml)cmd.Parameters[Schema.OutputParamsList[0].ParameterName].SqlValue, Schema.Serializer);
}
else
{
PropertyMap pmap = new PropertyMap();
pmap.ParameterName = outparam.ParameterName;
pmap.PropertyName = outparam.PropertyName;
Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
Schema.PropertyMapList.Add(pmap);
}
}
if (Schema.ReturnValue != null)
{
Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
}
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
return(Result);
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
return(Result);
}
}
RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
Result.Success = true;
return(Result);
}
public static WebServiceResult SQLExecuteQuery <T>(this DynamicDBRequest Request, IList <T> ObjectResultList, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
{
//TODO: error handeling if all this info is fubar
//get the types of the item in the result list and the request
Type type = ObjectResultList.GetType().GetGenericArguments()[0];
WebServiceResult Result = new WebServiceResult();
//load in a schema if we have one
SprocSchema Schema = null;
RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);
using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//this call completes the SqlCommand by either filling in the info from the cached schema for the params,
//or calling the DB to pull down meta data, create the params, and then create them in the cache for use
//after the first call
cmd.CompleteSQLRequest(Request, ref Schema);
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
//do while reader.nextresult for multi record sets. We can rebuild this
while (reader.HasRows && reader.Read())
{
try
{
//create a new type in the list and map the data to it.
var ListObject = Activator.CreateInstance(type);
Helpers.MapDataReaderToObject(ListObject, reader, ref Schema, FunctionMap, Result);
ObjectResultList.Add((T)ListObject);
}
catch (Exception ex)
{
Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
Result.Success = false;
Result.Exceptions.Add(ex);
return(Result);
}
}
}
try
{
//map the output params
foreach (SprocParam outparam in Schema.OutputParamsList)
{
Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
}
if (Schema.ReturnValue != null)
{
Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
}
}
catch (Exception ex)
{
Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
Result.Success = false;
Result.Exceptions.Add(ex);
return(Result);
}
}
catch (Exception ex)
{
Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
Result.Success = false;
Result.Exceptions.Add(ex);
return(Result);
}
}
//save the cache to the metafile cache.
RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
Result.Success = true;
return(Result);
}
public static WebServiceResult SQLExecuteMapObjectFromOutputs <T>(this DynamicDBRequest Request, T ResultObject, string SQLSprocName, string DBConnectionString, SpecialPropertyMapper FunctionMap = null)
{
WebServiceResult Result = new WebServiceResult();
ResultObject = Activator.CreateInstance <T>();
SprocSchema Schema = null;
RapidWrapperConfiguration.MetaFile.SprocSchemaMetaData.TryGetValue(SQLSprocName, out Schema);
using (SqlCommand cmd = new SqlCommand(SQLSprocName, Helpers.ConnectToDatabase(DBConnectionString)))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CompleteSQLRequest(Request, ref Schema);
try
{
cmd.ExecuteNonQuery();
try
{
if (Schema.PropertyMapList.Count > 0)
{
foreach (PropertyMap pmap in Schema.PropertyMapList)
{
if (pmap.PropInfo != null)
{
Helpers.MapValue(ResultObject, cmd.Parameters[pmap.ParameterName].Value, pmap, FunctionMap, Result);
}
else
{
Result.Add(pmap.PropertyName, cmd.Parameters[pmap.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
}
}
}
else
{
foreach (SprocParam outparam in Schema.OutputParamsList)
{
PropertyInfo pinfo = typeof(T).GetProperty(outparam.PropertyName, Helpers.BindFlags);
PropertyMap pmap = new PropertyMap();
pmap.ParameterName = outparam.ParameterName;
if (pinfo != null && pinfo.CanWrite)
{
pmap.PropInfo = pinfo;
Helpers.MapValue(ResultObject, cmd.Parameters[outparam.ParameterName].Value, pmap, FunctionMap, Result);
}
else
{
pmap.PropertyName = outparam.PropertyName;
Result.Add(outparam.PropertyName, cmd.Parameters[outparam.ParameterName].Value is DBNull ? null : cmd.Parameters[outparam.ParameterName].Value);
}
Schema.PropertyMapList.Add(pmap);
}
if (Schema.ReturnValue != null)
{
Result.Add(Schema.ReturnValue.PropertyName, cmd.Parameters[Schema.ReturnValue.ParameterName].Value is DBNull ? null : cmd.Parameters[Schema.ReturnValue.ParameterName].Value);
}
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery Outputs: " + ex.Message);
return(Result);
}
}
catch (Exception ex)
{
Result.Exceptions.Add(ex);
Result.Success = false;
Console.WriteLine("Error in SQLExecuteQuery: " + ex.Message);
return(Result);
}
}
RapidWrapperConfiguration.MetaFile.CacheMetaData(SQLSprocName, Schema);
Result.Success = true;
return(Result);
}