/// <summary>
/// <para>ExecuteDbApi is the DbWebApi extension method to ApiController.</para>
/// </summary>
/// <param name="apiController">Your ApiController to invoke this extension method</param>
/// <param name="sp">Specifies the fully qualified name of database stored procedure or function</param>
/// <param name="parameters">Specifies required parameters as name-value pairs</param>
/// <returns>A complete HttpResponseMessage contains result data returned by the database</returns>
public static HttpResponseMessage ExecuteDbApi(this ApiController apiController, string sp, IDictionary <string, object> parameters)
{
try
{
var negotiationResult = apiController.Request.Negotiate();
if (negotiationResult != null && negotiationResult.Formatter is PseudoMediaTypeFormatter)
{
Encoding negotiatedEncoding = apiController.Request.NegotiateEncoding((negotiationResult == null) ? null : negotiationResult.Formatter);
IFormatPlug formatPlug = MatchFormatPlug(negotiationResult.MediaType);
if (formatPlug != null)
{
return(formatPlug.Respond(apiController, sp, parameters, negotiationResult.MediaType, negotiatedEncoding));
}
}
using (DalCenter dbContext = new DalCenter(apiController.Request.GetQueryStringDictionary()))
{
if (negotiationResult != null)
{
if (negotiationResult.Formatter is RazorMediaTypeFormatter)
{
return(apiController.Request.CreateResponse(HttpStatusCode.OK, new RazorContext(dbContext.ExecuteDbApi(sp, parameters), parameters)));
}
}
return(apiController.Request.CreateResponse(HttpStatusCode.OK, dbContext.ExecuteDbApi(sp, parameters)));
}
}
finally
{
CleanupCache(apiController.Request.RequestUri);
}
}
public HttpResponseMessage Respond(ApiController apiController, string sp, IDictionary <string, object> parameters,
MediaTypeHeaderValue negotiatedMediaType, Encoding negotiatedEncoding)
{
HttpResponseMessage csvResponse = apiController.Request.CreateResponse();
IDictionary <string, object> queryStrings = apiController.Request.GetQueryStringDictionary();
MemoryStream memoryStream = new MemoryStream(); // TBD: To find a more efficient way later
using (XLWorkbook workbook = new XLWorkbook())
using (DalCenter dbContext = new DalCenter(queryStrings))
{
IXLWorksheet currentWorksheet = null;
dbContext.ExecuteDbApi(sp, parameters, rs =>
{
currentWorksheet = workbook.AddWorksheet(string.Format("Sheet{0}", rs + 1));
},
readHeader =>
{
if (currentWorksheet != null)
{
string[] fieldNames = dbContext.NameAllFields(readHeader);
for (int col = 0; col < fieldNames.Length; col++)
{
currentWorksheet.Cell(1, col + 1).SetValue(fieldNames[col]).Style.Font.Bold = true;
}
}
},
readRow =>
{
if (currentWorksheet != null)
{
currentWorksheet.Cell(2, 1).Value = readRow;
}
},
foot =>
{
if (currentWorksheet != null)
{
currentWorksheet.Columns().AdjustToContents();
}
}, null, null, true);
workbook.SaveAs(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
}
csvResponse.Content = new StreamContent(memoryStream);
csvResponse.Content.Headers.ContentType = negotiatedMediaType;
csvResponse.Content.Headers.ContentLength = memoryStream.Length;
csvResponse.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = queryStrings.GetQueryFileName(DbWebApiOptions.QueryStringContract.FileNameParameterName, FormatShortName)
};
return(csvResponse);
}
static void Main(string[] args)
{
Console.WriteLine("输入目录。。。");
string path = Console.ReadLine();
path = string.IsNullOrEmpty(path) ? "c:\\3" : path;
DalCenter.Start(path);
Console.Read();
}
public HttpResponseMessage Respond(ApiController apiController, string sp, IDictionary <string, object> parameters,
MediaTypeHeaderValue negotiatedMediaType, Encoding negotiatedEncoding)
{
HttpResponseMessage csvResponse = apiController.Request.CreateResponse();
IDictionary <string, object> queryStrings = apiController.Request.GetQueryStringDictionary();
int[] resultSetChoices = new int[] { GetQueryResultSetIndex(queryStrings, DbWebApiOptions.QueryStringContract.ResultSetParameterName) };
csvResponse.Content = new PushStreamContent((stream, httpContent, transportContext) =>
{
StreamWriter textWriter = (negotiatedEncoding == null) ? new StreamWriter(stream) : new StreamWriter(stream, negotiatedEncoding);
using (DalCenter dbContext = new DalCenter(queryStrings))
{
CsvExporter csvExporter = new CsvExporter(textWriter);
dbContext.ExecuteDbApi(sp, parameters, null,
readHeader =>
{
string[] headers = new string[readHeader.VisibleFieldCount];
for (int i = 0; i < headers.Length; i++)
{
headers[i] = dbContext.ResolvePropertyName(readHeader.GetName(i));
}
csvExporter.WriteHeader(headers);
},
readRow =>
{
object[] values = new object[readRow.VisibleFieldCount];
readRow.GetColumnValues(values);
csvExporter.WriteRow(values);
},
null, null, resultSetChoices);
textWriter.Flush();
}
stream.Close();
}, negotiatedMediaType ?? DefaultMediaType);
csvResponse.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = queryStrings.GetQueryFileName(DbWebApiOptions.QueryStringContract.FileNameParameterName, FormatShortName)
};
return(csvResponse);
}
public string AddOldPatient(Int64 drID, Int64 patientID, string patientType)
{
string errorMsg = "";
DalCenter dlc2 = new DalCenter(this.Request.GetQueryStringDictionary());
var dic = new Dictionary <string, object>();
dic.Add("@drID", drID);
dic.Add("@patientID", patientID);
dic.Add("@patientType", patientType);
try
{
InputParameters inparams = new InputParameters(dic);
dlc2.ExecuteDbApi("AddAppoinmentOldPatient", inparams.Parameters);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
return(errorMsg);
}
public static int DetectSpChanges(int elapsedMinutes)
{
int cntExpired = -1;
string detectDdlChangesProc = ConfigHelper.DetectDdlChangesProc;
if (string.IsNullOrEmpty(detectDdlChangesProc) || elapsedMinutes < 1)
SwitchDerivedParametersCache(false);
else
{
SwitchDerivedParametersCache(true);
using (DalCenter dbContext = new DalCenter())
{
cntExpired = dbContext.InvalidateAlteredSpFromCache(detectDdlChangesProc, TimeSpan.FromMinutes(elapsedMinutes));
}
LastDetectSpChangesTime = DateTime.Now;
}
return cntExpired;
}
/// <summary>
/// Bulk execute a DbApi with a IList<IDictionary<string, object>> (a collection of input parameters collection)
/// </summary>
/// <typeparam name="T">IDictionary<string, object></typeparam>
/// <param name="apiController">Your ApiController to invoke this extension method</param>
/// <param name="sp">Specifies the fully qualified name of database stored procedure or function</param>
/// <param name="bulkParameterSets">Specifies a collection of required parameter dictionary for every call in the bulk execution</param>
/// <returns>A complete HttpResponseMessage contains an array of every result data returned by the database</returns>
public static HttpResponseMessage BulkExecuteDbApi <T>(this ApiController apiController, string sp, IList <T> bulkParameterSets) where T : IDictionary <string, object>
{
if (bulkParameterSets == null || bulkParameterSets.Count == 0)
{
return(apiController.Request.CreateResponse(HttpStatusCode.BadRequest));
}
try
{
var negotiationResult = apiController.Request.Negotiate();
if (negotiationResult != null)
{
if (negotiationResult.Formatter is PseudoMediaTypeFormatter || negotiationResult.Formatter is RazorMediaTypeFormatter)
{
return(apiController.Request.CreateResponse(HttpStatusCode.UnsupportedMediaType));
}
}
using (DalCenter dbContext = new DalCenter(apiController.Request.GetQueryStringDictionary()))
{
StoredProcedureResponse[] spResponses = new StoredProcedureResponse[bulkParameterSets.Count];
for (int i = 0; i < spResponses.Length; i++)
{
spResponses[i] = dbContext.ExecuteDbApi(sp, bulkParameterSets[i]);
}
return(apiController.Request.CreateResponse(HttpStatusCode.OK, spResponses.AsQueryable()));
}
}
finally
{
CleanupCache(apiController.Request.RequestUri);
}
}
