public void ProcessRequest(HttpContext context)
{
var queryStringConverter = new QueryStringConverter();
var parameters = queryStringConverter
.ConvertToParameters(context.Request.QueryString);
string procedure = queryStringConverter.StoredProcedure;
string connectionName = queryStringConverter.ConnectionName;
context.Response.ContentType = "application/json";
context.Response.ContentEncoding = Encoding.UTF8;
List<SourceTarget> sourceTargets = context.Application["SourceTargets"] as List<SourceTarget>;
// Does this procedure and connection combo exist
var matchedSourceTarget = FetchMatchingSourceTarget(procedure,
connectionName,
sourceTargets);
if (matchedSourceTarget != null)
{
var dataProvider = new DataProvider(matchedSourceTarget.ConnectionString, procedure);
var records = dataProvider.SetParameters(parameters)
.ExecuteStoredProcedure();
context.Response.Write(JsonConvert.SerializeObject(records));
}
else
{
context.Response.Write(string.Empty);
}
}
public void CanAddNamedSQLParametersToProvider()
{
string procedure = "usp_GetLLXDealsCalYearForSharepoint";
var dataProvider = new DataProvider(connection, procedure);
object integerParm = 5;
object decimalParm = 5.6;
object stringParm = "a line of text";
object datetimeParm = DateTime.Now;
var parameters = new Dictionary<string, object>();
parameters.Add("integerParm", integerParm);
parameters.Add("decimalParm", decimalParm);
parameters.Add("stringParm", stringParm);
parameters.Add("datatimeParm", datetimeParm);
dataProvider.SetParameters(parameters);
Assert.AreEqual(4, dataProvider.Parameters.Count);
var resultStringParm = dataProvider.Parameters
.Where(x => x.ParameterName == "stringParm")
.ToList();
Assert.AreEqual("stringParm", resultStringParm[0].ParameterName);
}
private void ExecuteProcedure(string procedure, SourceTarget sourceTarget)
{
var dataProvider = new DataProvider(this.connection, procedure);
dataProvider.SetParameters(sourceTarget.ConvertToParameters())
.ExecuteStoredProcedure();
}
public void CanSerializeListOfDynamicToXML()
{
//usp_GetLLXDealsCalYearForSharepoint
var dataProvider = new DataProvider(connection, "usp_GetLLXCalYearForSharepoint");
var results = dataProvider.ExecuteStoredProcedure();
string json = JsonConvert.SerializeObject(results);
string rootStart = @"{'root' : {'records' : ";
string end = @"}}";
json = rootStart + json + end;
System.Xml.XmlNode myXmlNode = (XmlNode)JsonConvert.DeserializeXmlNode(json);
Console.WriteLine(myXmlNode.InnerXml);
Assert.IsTrue(myXmlNode.InnerXml.Length > 1);
}
public void CanSerializeExpandoListToJson()
{
string procedure = "usp_GetLLXDealsCalYearForSharepoint";
var dataProvider = new DataProvider(connection, procedure);
var results = dataProvider.ExecuteStoredProcedure();
string json = JsonConvert.SerializeObject(results);
Assert.IsTrue(json.Length > 0);
}
public void CanReadStoredProcedureSchema()
{
string procedure = "sp_Report_Agent_Wity_Listing";
var dataProvider = new DataProvider(connection, procedure);
var sqlParms = dataProvider.ReadStoreProcedureSchema(procedure)
.Parameters;
Assert.AreEqual(4, sqlParms.Count);
}
public void CanIterateListOfDynamic()
{
// 15,'07/01/09','06/30/10','pai,app'
string procedure = "sp_Report_Agent_Wity_Listing";
var dataProvider = new DataProvider(connection, procedure);
var parameters = new Dictionary<string, object>();
parameters.Add("AgentId", 15);
parameters.Add("StartDate", new DateTime(2009, 7, 1));
parameters.Add("EndDate", new DateTime(2010, 6, 30));
parameters.Add("StatusList", "pai,app");
List<dynamic> results = dataProvider.SetParameters(parameters)
.ExecuteStoredProcedure();
Assert.AreEqual(36, results.Count);
IDictionary<string, object> record = (IDictionary<string, object>)results[0];
Assert.AreEqual(15, record["AgentID"]);
}
public void CanExecuteStoredProcedure()
{
string procedure = "usp_GetLLXDealsCalYearForSharepoint";
var dataProvider = new DataProvider(connection, procedure);
var results = dataProvider.ExecuteStoredProcedure();
int actualCount = StoredProcedureCount(procedure);
Assert.AreEqual(results.Count, actualCount);
}
public void CanExecProceWithConvertedParms()
{
// 15,'07/01/09','06/30/10','pai,app'
int agentId = 15;
DateTime startDate = new DateTime(2009, 7, 1);
DateTime endDate = new DateTime(2010, 6, 30);
string statusList = "pai,app";
string procedure = "sp_Report_Agent_Wity_Listing";
var dataProvider = new DataProvider(connection, procedure);
var nvCollection = new NameValueCollection();
nvCollection.Add("AgentId", agentId.ToString());
nvCollection.Add("StartDate", startDate.ToShortDateString());
nvCollection.Add("EndDate", endDate.ToShortDateString());
nvCollection.Add("StatusList", statusList);
var queryStringConverter = new QueryStringConverter();
var parameters = queryStringConverter.ConvertToParameters(nvCollection);
var results = dataProvider.SetParameters(parameters)
.ExecuteStoredProcedure();
Assert.AreEqual(36, results.Count);
}