public IImmutableList <Transport.Column> GetSchema(string sql, SqlParameters sqlParameters, HttpContext httpContext)
{
var resultColumns = _sqlExecutor.GetSchema(sql, sqlParameters).Columns;
return(ConvertColumns(resultColumns));
}
public void SaveRule()
{
string queryString = "";
var p = new SqlParameters(this);
if (ID != 0)
{
queryString =
"UPDATE " + FillFrom_Table +
" SET " +
modeField + "=" + p.modeValue + ", " +
messageFromField + "=" + p.messageFromValue + ", " +
messageToField + "=" + p.messageToValue + ", " +
messageTextField + "=" + p.messageTextValue + ", " +
signedByField + "=" + p.signedByValue + ", " +
documentTypeField + "=" + p.documentTypeValue + ", " +
modeDocTypeField + "=" + p.modeDocTypeValue + ", " +
personField + "=" + p.personValue + ", " +
documentField + "=" + p.documentValue + ", " +
noDocumentField + "=" + p.noDocumentValue + ", " +
notSignedByField + "=" + p.notSignedByValue + ", " +
noPersonField + "=" + p.noPersonValue + ", " +
nameField + "=" + p.nameValue + " " +
" WHERE " + ID_Field + "=" + p.idValue;
}
else
{
queryString =
"INSERT INTO " + FillFrom_Table + " (" +
folderIDField + ", " +
modeField + ", " +
messageFromField + ", " +
messageToField + ", " +
messageTextField + ", " +
signedByField + ", " +
documentTypeField + ", " +
modeDocTypeField + ", " +
personField + ", " +
documentField + ", " +
noDocumentField + ", " +
noPersonField + ", " +
notSignedByField + ", " +
nameField +
") VALUES (" +
p.folderValue + ", " +
p.modeValue + ", " +
p.messageFromValue + ", " +
p.messageToValue + ", " +
p.messageTextValue + ", " +
p.signedByValue + ", " +
p.documentTypeValue + ", " +
p.modeDocTypeValue + ", " +
p.personValue + ", " +
p.documentValue + ", " +
p.noDocumentValue + ", " +
p.noPersonValue + ", " +
p.notSignedByValue + ", " +
p.nameValue + ")";
}
using (var cm = new SqlCommand(queryString))
using (cm.Connection = new SqlConnection(connectionString))
{
try
{
cm.Connection.Open();
cm.ExecuteNonQuery();
}
catch (SqlException sex)
{
Env.WriteSqlToLog(sex, cm);
}
catch (Exception ex)
{
Env.WriteToLog(ex);
}
finally
{
cm.Connection.Close();
}
}
}
public async Task <Transport.TransportPartitionsResult> GetPartitions(bool canHandlePartitions, string sql, SqlParameters sqlParameters, HttpContext httpContext)
{
var sqlTree = _sqlParser.Parse(sql, out var errors);
foreach (var header in httpContext.Request.Headers)
{
if (header.Key.StartsWith("P_", StringComparison.OrdinalIgnoreCase))
{
var parameterName = header.Key.Substring(2);
if (header.Value.Count > 1)
{
throw new SqlErrorException("Two parameters found with the same name in the http headers.");
}
var value = header.Value.First();
var base64Value = Convert.ToBase64String(Encoding.UTF8.GetBytes(value));
//Add the parameter as base64 inline parameter
sqlTree.Statements.Insert(0, new SetVariableStatement()
{
VariableReference = new SqlParser.Expressions.VariableReference()
{
Name = parameterName
},
ScalarExpression = new Base64Literal()
{
Value = base64Value
}
});
}
}
if (errors.Count > 0)
{
throw new SqlErrorException(errors.First().Message);
}
//Apply the row level security filter on the query
await RowLevelSecurityHelper.ApplyRowLevelSecurity(sqlTree, httpContext, _metadataStore, _serviceProvider);
var schema = _sqlExecutor.GetSchema(sqlTree, sqlParameters);
//Take table name and get the partition resolver
if (!_metadataStore.TryGetTable(schema.TableName, out var table))
{
throw new SqlErrorException($"The table {schema.TableName} was not found.");
}
//Get the partitions
var discoveryService = _serviceProvider.GetService <IDiscoveryService>();
var partitionsBuilder = new PartitionsBuilder(sqlTree);
var partitions = await table.PartitionResolver.GetPartitions(canHandlePartitions, partitionsBuilder, httpContext, new PartitionOptions(_serviceProvider, discoveryService));
var partitionListBuilder = ImmutableList.CreateBuilder <Transport.TransportPartition>();
foreach (var partition in partitions)
{
List <TransportServiceLocation> locations = new List <TransportServiceLocation>();
foreach (var location in partition.Locations)
{
locations.Add(new TransportServiceLocation(location.Host, location.Tls));
}
partitionListBuilder.Add(new Transport.TransportPartition(locations, partition.SqlTree.Print()));
}
return(new Transport.TransportPartitionsResult(ConvertColumns(schema.Columns), partitionListBuilder.ToImmutable()));
}
private void pcatBrowseButton_Click(object sender, EventArgs e)
{
if (cn.IsOpen)
{
//Open the dialog for the user to choose a spreadsheet
OpenFileDialog openSheetDialog = new OpenFileDialog();
openSheetDialog.FileName = "";
openSheetDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openSheetDialog.FilterIndex = 1;
if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
{
using (new CursorWait())
{
label1.Text = "Importing spreadsheet data. This may take a moment.";
label1.Refresh();
//Update UI
mcatBrowseButton.Visible = false;
mcatBrowseButton.Refresh();
pcatBrowseButton.Visible = false;
//open the file as a Spreadsheet Light document
SLDocument ss = new SLDocument(openSheetDialog.FileName);
SLWorksheetStatistics stats = ss.GetWorksheetStatistics();
//iterate through rows until the final row is reached
for (int r = 2; r <= stats.EndRowIndex; r++)
{
//Get the string values from the table
string lname = ss.GetCellValueAsString(r, 1).Trim();
string fname = ss.GetCellValueAsString(r, 2).Trim();
string mi = ss.GetCellValueAsString(r, 3).Trim();
string email = ss.GetCellValueAsString(r, 4).Trim();
string address1 = ss.GetCellValueAsString(r, 5).Trim();
string address2 = ss.GetCellValueAsString(r, 6).Trim();
string city = ss.GetCellValueAsString(r, 8).Trim();
string state = ss.GetCellValueAsString(r, 9).Trim();
string zip = ss.GetCellValueAsString(r, 10).Trim();
string country = ss.GetCellValueAsString(r, 11).Trim();
//add each row to the standard data table
pcatTable.Rows.Add("", lname, fname, mi, email, address1, address2, city, state, zip, country);
}
//close the SLDocument, as the data is now in a datatable
ss.CloseWithoutSaving();
if (cn.IsOpen)
{
//clear any old results from the table
cn.Execute("DELETE FROM LECOM_MATCHING_PCAT_TABLE", SQLTypes.Text);
SqlParameters dtParm = new SqlParameters();
dtParm.Add("exampleDT", pcatTable, SqlDbType.Structured);
dtParm.List[0].TypeName = "dbo.LecomMatchingPcatTableType";
cn.Execute("dbo.Lecom_MatchingImportPcat", SQLTypes.StoredProcedure, dtParm);
label1.Text = "Excel data import complete! Click below to view the match report.";
pcatReportViewButton.Visible = true;
}
}
}
else
{
MessageBox.Show("Please select a valid Excel 2007 file!");
openSheetDialog.ShowDialog();
}
}
}
//This button gets LECOM Candidates
private void sqlConnectButton_Click(object sender, EventArgs e)
{
//define datasets and tables
DataTable candidacyStandard = new DataTable();
if (maskedTextBox1.Text.Length == 4)
{
using (new CursorWait())
{
//update UI
label1.Text = "Fetching LECOM candidates. Please wait.";
label2.Text = "";
label1.Refresh();
label2.Refresh();
maskedTextBox1.Visible = false;
sqlConnectButton.Visible = false;
string dbName = "tmsEPrd", server = "SIS";
string connectionString = System.String.Format("Server={0};Database={1};Connection Timeout=90;Max Pool Size=2048;Pooling=true;Trusted_Connection=True;", server, dbName);
string strSQLTemplate = "SELECT DISTINCT NAME_AND_ADDRESS.EMAIL_ADDRESS AS Email, DIV_CDE FROM CANDIDACY INNER JOIN NAME_AND_ADDRESS ON CANDIDACY.ID_NUM = NAME_AND_ADDRESS.ID_NUM WHERE yr_cde = {0}";
string yr_code = maskedTextBox1.Text;
string strSQL = string.Format(strSQLTemplate, yr_code);
try
{
//This connection is different from cn
conn = new System.Data.SqlClient.SqlConnection(connectionString);
conn.Open();
//Check if the SQL Connection is open
if (conn.State == System.Data.ConnectionState.Open)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSQL, conn);
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
da.Fill(candidacyStandard);
//make a table of candidates with values as strings
DataTable candidacyStrings = new DataTable();
candidacyStrings.Columns.Add("Email", typeof(string));
candidacyStrings.Columns.Add("DIV_CDE", typeof(string));
cmd = null;
if (candidacyStandard.Rows.Count > 0)
{
for (int r = 0; r < candidacyStandard.Rows.Count; r++)
{
string email = "";
string div_cde = "";
email = candidacyStandard.Rows[r][0].ToString().Trim();
div_cde = candidacyStandard.Rows[r][1].ToString().Trim();
candidacyStrings.Rows.Add(email, div_cde);
}
if (cn.IsOpen)
{
//delete any previously imported candidates
cn.Execute("DELETE FROM LECOM_MATCHING_CURRENT_YEAR_CANDIDACY", SQLTypes.Text);
//populate a separate table in Sarah db with this year's candidates
SqlParameters CParm = new SqlParameters();
CParm.Add("exampleDT", candidacyStrings, SqlDbType.Structured);
CParm.List[0].TypeName = "dbo.LecomMatchingCurrentYearCandidacyTableType";
cn.Execute("dbo.Lecom_MatchingCurrentYearCandidacy", SQLTypes.StoredProcedure, CParm);
label1.Text = "Candidate import complete! Please click 'Browse' and choose the excel spreadsheet you want to import.";
//Make options available
mcatBrowseButton.Visible = true;
pcatBrowseButton.Visible = true;
//prep for excel import
//Declare the MCAT table's columns
mcatTable.Columns.Add("LECOM", typeof(string)).MaxLength = 5;
mcatTable.Columns.Add("legal_state_cd", typeof(string)).MaxLength = 2;
mcatTable.Columns.Add("aamc_id", typeof(string)).MaxLength = 8;
mcatTable.Columns.Add("test_date", typeof(DateTime));
mcatTable.Columns.Add("lname", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("fname", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("mname", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("suffix", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("address", typeof(string)).MaxLength = 256;
mcatTable.Columns.Add("city", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("state_cd", typeof(string)).MaxLength = 2;
mcatTable.Columns.Add("postal_cd", typeof(string)).MaxLength = 20;
mcatTable.Columns.Add("country", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("email", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("sex", typeof(string)).MaxLength = 1;
mcatTable.Columns.Add("age_at_test", typeof(int));
mcatTable.Columns.Add("major_cd", typeof(string)).MaxLength = 2;
mcatTable.Columns.Add("major", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("primary_interest_cd", typeof(string));
mcatTable.Columns.Add("primary_interest", typeof(string)).MaxLength = 50;
mcatTable.Columns.Add("cpbs_score", typeof(int));
mcatTable.Columns.Add("cpbs_cb_lower", typeof(int));
mcatTable.Columns.Add("cpbs_cb_upper", typeof(int));
mcatTable.Columns.Add("cpbs_%ile_rank", typeof(int));
mcatTable.Columns.Add("cars_score", typeof(int));
mcatTable.Columns.Add("cars_cb_lower", typeof(int));
mcatTable.Columns.Add("cars_cb_upper", typeof(int));
mcatTable.Columns.Add("cars_%ile_rank", typeof(int));
mcatTable.Columns.Add("bbfl_score", typeof(int));
mcatTable.Columns.Add("bbfl_cb_lower", typeof(int));
mcatTable.Columns.Add("bbfl_cb_upper", typeof(int));
mcatTable.Columns.Add("bbfl_%ile_rank", typeof(int));
mcatTable.Columns.Add("psbb_score", typeof(int));
mcatTable.Columns.Add("psbb_cb_lower", typeof(int));
mcatTable.Columns.Add("psbb_cb_upper", typeof(int));
mcatTable.Columns.Add("psbb_%ile_rank", typeof(int));
mcatTable.Columns.Add("total_score", typeof(int));
mcatTable.Columns.Add("total_cb_lower", typeof(int));
mcatTable.Columns.Add("total_cb_upper", typeof(int));
mcatTable.Columns.Add("total_%ile_rank", typeof(int));
//PCAT table columns
pcatTable.Columns.Add("LECOM", typeof(string)).MaxLength = 5;
pcatTable.Columns.Add("lname", typeof(string)).MaxLength = 50;
pcatTable.Columns.Add("fname", typeof(string)).MaxLength = 50;
pcatTable.Columns.Add("MI", typeof(string)).MaxLength = 1;
pcatTable.Columns.Add("email", typeof(string)).MaxLength = 50;
pcatTable.Columns.Add("address1", typeof(string)).MaxLength = 100;
pcatTable.Columns.Add("address2", typeof(string)).MaxLength = 100;
pcatTable.Columns.Add("city", typeof(string)).MaxLength = 50;
pcatTable.Columns.Add("state", typeof(string)).MaxLength = 2;
pcatTable.Columns.Add("zip", typeof(string)).MaxLength = 20;
pcatTable.Columns.Add("country", typeof(string)).MaxLength = 20;
}
}
else
{
MessageBox.Show("Error: no candidacy results returned!");
label1.Text = "Welcome! Please enter the current applicant year code in the box below and click the button to get started.";
label1.Refresh();
sqlConnectButton.Visible = true;
maskedTextBox1.Visible = true;
}
conn.Close();
}
else
{
MessageBox.Show("Error: connection state not open. Current state is '" + conn.State.ToString() + "'");
}
}
catch (Exception ex)
{
MessageBox.Show("Unexpected error " + ex.ToString());
}
}
}
else
{
MessageBox.Show("Year code not valid. Please enter a valid year code.");
}
}
public static IEnumerable <T> CallSp <T>(this DatabaseFacade database, string spName, SqlParameters parameters)
where T : new()
{
return(RunCustomCommand <T>(database, spName, CommandType.StoredProcedure, parameters.Array));
}
private void mcatBrowseButton_Click(object sender, EventArgs e)
{
if (cn.IsOpen)
{
//Open the dialog for the user to choose a spreadsheet
OpenFileDialog openSheetDialog = new OpenFileDialog();
openSheetDialog.FileName = "";
openSheetDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openSheetDialog.FilterIndex = 1;
if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
{
using (new CursorWait())
{
label1.Text = "Importing spreadsheet data. This may take a moment.";
label1.Refresh();
mcatBrowseButton.Visible = false;
mcatBrowseButton.Refresh();
pcatBrowseButton.Visible = false;
//open the file as a Spreadsheet Light document
SLDocument ss = new SLDocument(openSheetDialog.FileName);
SLWorksheetStatistics stats = ss.GetWorksheetStatistics();
//iterate through rows until the final row is reached
for (int r = 2; r <= stats.EndRowIndex; r++)
{
//Get the string values from the table
string legal_state_cd = ss.GetCellValueAsString(r, 1).Trim();
string aamc = ss.GetCellValueAsString(r, 2).Trim();
DateTime date = ss.GetCellValueAsDateTime(r, 3);
string lname = ss.GetCellValueAsString(r, 4).Trim();
string fname = ss.GetCellValueAsString(r, 5).Trim();
string mname = ss.GetCellValueAsString(r, 6).Trim();
string suffix = ss.GetCellValueAsString(r, 7).Trim();
string address = ss.GetCellValueAsString(r, 8).Trim();
string city = ss.GetCellValueAsString(r, 9).Trim();
string state = ss.GetCellValueAsString(r, 10).Trim();
string postal_cd = ss.GetCellValueAsString(r, 11).Trim();
string country = ss.GetCellValueAsString(r, 12).Trim();
string email = ss.GetCellValueAsString(r, 13).Trim();
string sex = ss.GetCellValueAsString(r, 14).Trim();
int age = ss.GetCellValueAsInt32(r, 15);
string major_cd = ss.GetCellValueAsString(r, 16).Trim();
string major = ss.GetCellValueAsString(r, 17).Trim();
string primary_interest_cd = ss.GetCellValueAsString(r, 18).Trim();
string primary_interest = ss.GetCellValueAsString(r, 19).Trim();
int cpbs_score = ss.GetCellValueAsInt32(r, 20);
int cpbs_cb_lower = ss.GetCellValueAsInt32(r, 21);
int cpbs_cb_upper = ss.GetCellValueAsInt32(r, 22);
int cpbs_percentile_rank = ss.GetCellValueAsInt32(r, 23);
int cars_score = ss.GetCellValueAsInt32(r, 24);
int cars_cb_lower = ss.GetCellValueAsInt32(r, 25);
int cars_cb_upper = ss.GetCellValueAsInt32(r, 26);
int cars_percentile_rank = ss.GetCellValueAsInt32(r, 27);
int bbfl_score = ss.GetCellValueAsInt32(r, 28);
int bbfl_cb_lower = ss.GetCellValueAsInt32(r, 29);
int bbfl_cb_upper = ss.GetCellValueAsInt32(r, 30);
int bbfl_percentile_rank = ss.GetCellValueAsInt32(r, 31);
int psbb_score = ss.GetCellValueAsInt32(r, 32);
int psbb_cb_lower = ss.GetCellValueAsInt32(r, 33);
int psbb_cb_upper = ss.GetCellValueAsInt32(r, 34);
int psbb_percentile_rank = ss.GetCellValueAsInt32(r, 35);
int total_score = ss.GetCellValueAsInt32(r, 36);
int total_cb_lower = ss.GetCellValueAsInt32(r, 37);
int total_cb_upper = ss.GetCellValueAsInt32(r, 38);
int total_percentile_rank = ss.GetCellValueAsInt32(r, 39);
//add each row to the standard data table
mcatTable.Rows.Add("", legal_state_cd, aamc, date, lname, fname, mname, suffix, address, city, state, postal_cd, country, email, sex, age, major_cd, major, primary_interest_cd, primary_interest, cpbs_score, cpbs_cb_lower, cpbs_cb_upper, cpbs_percentile_rank, cars_score, cars_cb_lower, cars_cb_upper, cars_percentile_rank, bbfl_score, bbfl_cb_lower, bbfl_cb_upper, bbfl_percentile_rank, psbb_score, psbb_cb_lower, psbb_cb_upper, psbb_percentile_rank, total_score, total_cb_lower, total_cb_upper, total_percentile_rank);;
}
//close the SLDocument, as the data is now in a datatable
ss.CloseWithoutSaving();
if (cn.IsOpen)
{
//clear any old results from the table
cn.Execute("DELETE FROM LECOM_MATCHING_MCAT_TABLE", SQLTypes.Text);
SqlParameters dtParm = new SqlParameters();
dtParm.Add("exampleDT", mcatTable, SqlDbType.Structured);
dtParm.List[0].TypeName = "dbo.LecomMatchingMcatTableType";
cn.Execute("dbo.Lecom_MatchingImportMcat", SQLTypes.StoredProcedure, dtParm);
label1.Text = "Excel data import complete! Click below to view the match report.";
mcatReportViewButton.Visible = true;
}
}
}
else
{
MessageBox.Show("Please select a valid Excel 2007 file!");
openSheetDialog.ShowDialog();
}
}
}
protected virtual string VisitMethodCall(MethodCallExpression m)
{
//Here's what happens with a MethodCallExpression:
// If a method is called that contains a single argument,
// then m.Object is the object on the left hand side of the method call, example:
// x.Path.StartsWith(content.Path)
// m.Object = x.Path
// and m.Arguments.Length == 1, therefor m.Arguments[0] == content.Path
// If a method is called that contains multiple arguments, then m.Object == null and the
// m.Arguments collection contains the left hand side of the method call, example:
// x.Path.SqlStartsWith(content.Path, TextColumnType.NVarchar)
// m.Object == null
// m.Arguments.Length == 3, therefor, m.Arguments[0] == x.Path, m.Arguments[1] == content.Path, m.Arguments[2] == TextColumnType.NVarchar
// So, we need to cater for these scenarios.
var objectForMethod = m.Object ?? m.Arguments[0];
var visitedObjectForMethod = Visit(objectForMethod);
var methodArgs = m.Object == null
? m.Arguments.Skip(1).ToArray()
: m.Arguments.ToArray();
switch (m.Method.Name)
{
case "ToString":
SqlParameters.Add(objectForMethod.ToString());
return(string.Format("@{0}", SqlParameters.Count - 1));
case "ToUpper":
return(string.Format("upper({0})", visitedObjectForMethod));
case "ToLower":
return(string.Format("lower({0})", visitedObjectForMethod));
case "SqlWildcard":
case "StartsWith":
case "EndsWith":
case "Contains":
case "Equals":
case "SqlStartsWith":
case "SqlEndsWith":
case "SqlContains":
case "SqlEquals":
case "InvariantStartsWith":
case "InvariantEndsWith":
case "InvariantContains":
case "InvariantEquals":
string compareValue;
if (methodArgs[0].NodeType != ExpressionType.Constant)
{
//This occurs when we are getting a value from a non constant such as: x => x.Path.StartsWith(content.Path)
// So we'll go get the value:
var member = Expression.Convert(methodArgs[0], typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
compareValue = getter().ToString();
}
else
{
compareValue = methodArgs[0].ToString();
}
//special case, if it is 'Contains' and the member that Contains is being called on is not a string, then
// we should be doing an 'In' clause - but we currently do not support this
if (methodArgs[0].Type != typeof(string) && TypeHelper.IsTypeAssignableFrom <IEnumerable>(methodArgs[0].Type))
{
throw new NotSupportedException("An array Contains method is not supported");
}
//default column type
var colType = TextColumnType.NVarchar;
//then check if the col type argument has been passed to the current method (this will be the case for methods like
// SqlContains and other Sql methods)
if (methodArgs.Length > 1)
{
var colTypeArg = methodArgs.FirstOrDefault(x => x is ConstantExpression && x.Type == typeof(TextColumnType));
if (colTypeArg != null)
{
colType = (TextColumnType)((ConstantExpression)colTypeArg).Value;
}
}
return(HandleStringComparison(visitedObjectForMethod, compareValue, m.Method.Name, colType));
//case "Substring":
// var startIndex = Int32.Parse(args[0].ToString()) + 1;
// if (args.Count == 2)
// {
// var length = Int32.Parse(args[1].ToString());
// return string.Format("substring({0} from {1} for {2})",
// r,
// startIndex,
// length);
// }
// else
// return string.Format("substring({0} from {1})",
// r,
// startIndex);
//case "Round":
//case "Floor":
//case "Ceiling":
//case "Coalesce":
//case "Abs":
//case "Sum":
// return string.Format("{0}({1}{2})",
// m.Method.Name,
// r,
// args.Count == 1 ? string.Format(",{0}", args[0]) : "");
//case "Concat":
// var s = new StringBuilder();
// foreach (Object e in args)
// {
// s.AppendFormat(" || {0}", e);
// }
// return string.Format("{0}{1}", r, s);
//case "In":
// var member = Expression.Convert(m.Arguments[0], typeof(object));
// var lambda = Expression.Lambda<Func<object>>(member);
// var getter = lambda.Compile();
// var inArgs = (object[])getter();
// var sIn = new StringBuilder();
// foreach (var e in inArgs)
// {
// SqlParameters.Add(e);
// sIn.AppendFormat("{0}{1}",
// sIn.Length > 0 ? "," : "",
// string.Format("@{0}", SqlParameters.Count - 1));
// //sIn.AppendFormat("{0}{1}",
// // sIn.Length > 0 ? "," : "",
// // GetQuotedValue(e, e.GetType()));
// }
// return string.Format("{0} {1} ({2})", r, m.Method.Name, sIn.ToString());
//case "Desc":
// return string.Format("{0} DESC", r);
//case "Alias":
//case "As":
// return string.Format("{0} As {1}", r,
// GetQuotedColumnName(RemoveQuoteFromAlias(RemoveQuote(args[0].ToString()))));
default:
throw new ArgumentOutOfRangeException("No logic supported for " + m.Method.Name);
//var s2 = new StringBuilder();
//foreach (Object e in args)
//{
// s2.AppendFormat(",{0}", GetQuotedValue(e, e.GetType()));
//}
//return string.Format("{0}({1}{2})", m.Method.Name, r, s2.ToString());
}
}
protected TEntity ExecuteSingleRowSelect(string sql, SqlParameters sqlParameters = null)
{
return(ExecuteSingleRowSelect(sql, DefaultRowMapping, sqlParameters));
}
protected override string VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null &&
m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(T))
{
//don't execute if compiled
if (Visited == false)
{
var field = _mapper.Map(m.Member.Name);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException($"The mapper returned an empty field for the member name: {m.Member.Name} for type: {m.Expression.Type}.");
}
return(field);
}
//already compiled, return
return(string.Empty);
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Convert)
{
//don't execute if compiled
if (Visited == false)
{
var field = _mapper.Map(m.Member.Name);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException($"The mapper returned an empty field for the member name: {m.Member.Name} for type: {m.Expression.Type}.");
}
return(field);
}
//already compiled, return
return(string.Empty);
}
if (m.Expression != null &&
m.Expression.Type != typeof(T) &&
EndsWithConstant(m) == false &&
_mappers.TryGetMapper(m.Expression.Type, out var subMapper))
{
//if this is the case, it means we have a sub expression / nested property access, such as: x.ContentType.Alias == "Test";
//and since the sub type (x.ContentType) is not the same as x, we need to resolve a mapper for x.ContentType to get it's mapped SQL column
//don't execute if compiled
if (Visited == false)
{
var field = subMapper.Map(m.Member.Name);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException($"The mapper returned an empty field for the member name: {m.Member.Name} for type: {m.Expression.Type}");
}
return(field);
}
//already compiled, return
return(string.Empty);
}
// TODO: When m.Expression.NodeType == ExpressionType.Constant and it's an expression like: content => aliases.Contains(content.ContentType.Alias);
// then an SQL parameter will be added for aliases as an array, however in SqlIn on the subclass it will manually add these SqlParameters anyways,
// however the query will still execute because the SQL that is written will only contain the correct indexes of SQL parameters, this would be ignored,
// I'm just unsure right now due to time constraints how to make it correct. It won't matter right now and has been working already with this bug but I've
// only just discovered what it is actually doing.
// TODO
// in most cases we want to convert the value to a plain object,
// but for in some rare cases, we may want to do it differently,
// for instance a Models.AuditType (an enum) may in some cases
// need to be converted to its string value.
// but - we cannot have specific code here, really - and how would
// we configure this? is it even possible?
/*
* var toString = typeof(object).GetMethod("ToString");
* var member = Expression.Call(m, toString);
*/
var member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
var o = getter();
SqlParameters.Add(o);
//don't execute if compiled
if (Visited == false)
{
return($"@{SqlParameters.Count - 1}");
}
//already compiled, return
return(string.Empty);
}
protected virtual string VisitBinary(BinaryExpression b)
{
string left, right;
var operand = BindOperant(b.NodeType);
if (operand == "AND" || operand == "OR")
{
MemberExpression m = b.Left as MemberExpression;
if (m != null && m.Expression != null)
{
string r = VisitMemberAccess(m);
SqlParameters.Add(1);
left = string.Format("{0} = @{1}", r, SqlParameters.Count - 1);
//left = string.Format("{0}={1}", r, GetQuotedTrueValue());
}
else
{
left = Visit(b.Left);
}
m = b.Right as MemberExpression;
if (m != null && m.Expression != null)
{
string r = VisitMemberAccess(m);
SqlParameters.Add(1);
right = string.Format("{0} = @{1}", r, SqlParameters.Count - 1);
//right = string.Format("{0}={1}", r, GetQuotedTrueValue());
}
else
{
right = Visit(b.Right);
}
}
else
{
left = Visit(b.Left);
right = Visit(b.Right);
}
if (operand == "=" && right == "null")
{
operand = "is";
}
else if (operand == "<>" && right == "null")
{
operand = "is not";
}
else if (operand == "=" || operand == "<>")
{
//if (IsTrueExpression(right)) right = GetQuotedTrueValue();
//else if (IsFalseExpression(right)) right = GetQuotedFalseValue();
//if (IsTrueExpression(left)) left = GetQuotedTrueValue();
//else if (IsFalseExpression(left)) left = GetQuotedFalseValue();
}
switch (operand)
{
case "MOD":
case "COALESCE":
return(string.Format("{0}({1},{2})", operand, left, right));
default:
return(left + " " + operand + " " + right);
}
}
private void sqlConnectButton_Click(object sender, EventArgs e)
{
//define datasets and tables
DataTable candidacyStandard = new DataTable();
if (maskedTextBox1.Text.Length == 4)
{
using (new CursorWait())
{
//update UI
label1.Text = "Fetching LECOM candidates. Please wait.";
label2.Text = "";
label1.Refresh();
label2.Refresh();
maskedTextBox1.Visible = false;
sqlConnectButton.Visible = false;
string dbName = "tmsEPly", server = "SIS";
string connectionString = System.String.Format("Server={0};Database={1};Connection Timeout=90;Max Pool Size=2048;Pooling=true;Trusted_Connection=True;", server, dbName);
string strSQLTemplate = "SELECT DISTINCT NAME_AND_ADDRESS.EMAIL_ADDRESS AS Email FROM CANDIDACY INNER JOIN NAME_AND_ADDRESS ON CANDIDACY.ID_NUM = NAME_AND_ADDRESS.ID_NUM WHERE yr_cde = {0}";
string yr_code = maskedTextBox1.Text;
string strSQL = string.Format(strSQLTemplate, yr_code);
try
{
conn = new System.Data.SqlClient.SqlConnection(connectionString);
conn.Open();
//Check if the SQL Connection is open
if (conn.State == System.Data.ConnectionState.Open)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSQL, conn);
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
da.Fill(candidacyStandard);
//make a table of candidates with values as strings
DataTable candidacyStrings = new DataTable();
candidacyStrings.Columns.Add("Email", typeof(string));
cmd = null;
if (candidacyStandard.Rows.Count > 0)
{
for (int r = 0; r < candidacyStandard.Rows.Count; r++)
{
string email = "";
email = candidacyStandard.Rows[r][0].ToString().Trim();
candidacyStrings.Rows.Add(email);
}
if (cn.IsOpen)
{
//delete any previously imported data
string deleteSQL = "DELETE FROM LECOM_CURRENT_YEAR_CANDIDACY";
cn.Execute(deleteSQL, SQLTypes.Text);
//populate a separate table in Sarah db with this year's candidates
SqlParameters CParm = new SqlParameters();
CParm.Add("exampleDT", candidacyStrings, SqlDbType.Structured);
CParm.List[0].TypeName = "dbo.LecomCurrentYearCandidacyTableType";
cn.Execute("dbo.LecomCurrentYearCandidacy", SQLTypes.StoredProcedure, CParm);
label1.Text = "Candidate import complete! Please click 'Browse' and choose the excel spreadsheet you want to import.";
browseButton.Visible = true;
}
}
else
{
MessageBox.Show("Error: no candidacy results returned!");
}
conn.Close();
}
else
{
MessageBox.Show("Error: connection state not open. Current state is '" + conn.State.ToString() + "'");
}
}
catch (Exception ex)
{
MessageBox.Show("Unexpected error " + ex.ToString());
}
}
}
else
{
MessageBox.Show("Year code not valid. Please enter a valid year code.");
}
}
private void browseButton_Click(object sender, EventArgs e)
{
//Define a standard data table
DataTable mcatPcat = new DataTable();
if (cn.IsOpen)
{
//Open the dialog for the user to choose a spreadsheet
OpenFileDialog openSheetDialog = new OpenFileDialog();
openSheetDialog.FileName = "";
openSheetDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openSheetDialog.FilterIndex = 1;
if (openSheetDialog.ShowDialog() == DialogResult.OK && Path.GetExtension(openSheetDialog.FileName) == ".xlsx")
{
using (new CursorWait())
{
label1.Text = "Importing spreadsheet data. This may take a moment.";
label1.Refresh();
browseButton.Visible = false;
//Declare the standard data table's columns
mcatPcat.Columns.Add("LECOM", typeof(string));
mcatPcat.Columns.Add("aamc_id", typeof(string));
mcatPcat.Columns.Add("lname", typeof(string));
mcatPcat.Columns.Add("fname", typeof(string));
mcatPcat.Columns.Add("city", typeof(string));
mcatPcat.Columns.Add("state_cd", typeof(string));
mcatPcat.Columns.Add("email", typeof(string));
//open the file as a Spreadsheet Light document
SLDocument ss = new SLDocument(openSheetDialog.FileName);
SLWorksheetStatistics stats = ss.GetWorksheetStatistics();
//iterate through rows until the final row is reached
for (int r = 2; r <= stats.EndRowIndex; r++)
{
//Get the string values from the table
string aamc = ss.GetCellValueAsString(r, 2).Trim();
string lname = ss.GetCellValueAsString(r, 4).Trim();
string fname = ss.GetCellValueAsString(r, 5).Trim();
string city = ss.GetCellValueAsString(r, 9).Trim();
string state = ss.GetCellValueAsString(r, 10).Trim();
string email = ss.GetCellValueAsString(r, 13).Trim();
//add each row to the standard data table
mcatPcat.Rows.Add("", aamc, lname, fname, city, state, email);
}
//close the SLDocument, as the data is now in a datatable
ss.CloseWithoutSaving();
//pass the data to an import procedure and save to Sarah database
SqlParameters dtParm = new SqlParameters();
dtParm.Add("exampleDT", mcatPcat, SqlDbType.Structured);
dtParm.List[0].TypeName = "dbo.LecomMcatPcatTableType";
cn.Execute("dbo.LecomImportMcatPcat", SQLTypes.StoredProcedure, dtParm);
label1.Text = "Excel data import complete! Click below to view the match report or write an email (to all non-matches on the list).";
reportViewButton.Visible = true;
emailButton.Visible = true;
}
}
else
{
MessageBox.Show("Please select a valid Excel 2007 file!");
openSheetDialog.ShowDialog();
}
}
}
internal MappingParameters(TableMapping mapping, object parameters)
{
this._mapping = mapping;
this._rawParams = parameters;
this._sqlParams = new SqlParameters(parameters);
}
public async ValueTask <Transport.QueryResult> Execute(string sql, SqlParameters sqlParameters, HttpContext httpContext)
{
_logger.LogInformation("Executing query: " + sql);
foreach (var header in httpContext.Request.Headers)
{
if (header.Key.StartsWith("P_", StringComparison.OrdinalIgnoreCase))
{
var parameterName = header.Key.Substring(2);
if (header.Value.Count > 1)
{
throw new SqlErrorException("Two parameters found with the same name in the http headers.");
}
var value = header.Value.First();
// URL decode
value = HttpUtility.UrlDecode(value, Encoding.UTF8);
sqlParameters.Add(SqlParameter.Create(parameterName, value));
}
}
//Parse the sql
var sqlTree = _sqlParser.Parse(sql, out var errors);
//Check for parsing errors
if (errors.Count > 0)
{
throw new SqlErrorException(errors.First().Message);
}
//Apply the row level security filter on the query
await RowLevelSecurityHelper.ApplyRowLevelSecurity(sqlTree, httpContext, _metadataStore, _serviceProvider);
//Only calculate the sql after the row level security if logging level is debug
_logger.LogConditionally(LogLevel.Debug, logger => logger.LogDebug($"Sql after row level security: {sqlTree.Print()}"));
CustomMetadataStore customMetadataStore = new CustomMetadataStore();
var result = await _sqlExecutor.Execute(sqlTree, sqlParameters, new TableResolverData(
httpContext,
_serviceProvider,
customMetadataStore)).ConfigureAwait(false);
var columnsBuilder = ImmutableList.CreateBuilder <Transport.Column>();
foreach (var column in result.Columns)
{
if (!_metadataStore.TryGetTypeColumns(column.Type, out var columns))
{
columns = new List <TableColumn>();
}
var childrenList = ImmutableList.CreateBuilder <Transport.Column>();
foreach (var child in columns)
{
childrenList.Add(GetTransportColumn(child));
}
var(columnType, nullable) = ColumnTypeHelper.GetKoraliumType(column.Type);
columnsBuilder.Add(new Transport.Column(column.Name, column.Type, column.GetFunction, childrenList.ToImmutable(), columnType, nullable));
}
return(new Transport.QueryResult(
result.Result,
columnsBuilder.ToImmutable(),
customMetadataStore.GetMetadataValues().Select(x => new KeyValuePair <string, string>(x.Key, x.Value.ToString()))
));
}
protected IList <TEntity> ExecuteSelect(string sql, SqlParameters sqlParameters = null)
{
return(ExecuteSelect(sql, DefaultRowMapping, sqlParameters));
}
protected override string VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null &&
m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(T))
{
//don't execute if compiled
if (Visited == false)
{
var field = _mapper.Map(m.Member.Name, true);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException(string.Format("The mapper returned an empty field for the member name: {0} for type: {1}", m.Member.Name, m.Expression.Type));
}
return(field);
}
//already compiled, return
return(string.Empty);
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Convert)
{
//don't execute if compiled
if (Visited == false)
{
var field = _mapper.Map(m.Member.Name, true);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException(string.Format("The mapper returned an empty field for the member name: {0} for type: {1}", m.Member.Name, m.Expression.Type));
}
return(field);
}
//already compiled, return
return(string.Empty);
}
if (m.Expression != null &&
m.Expression.Type != typeof(T) &&
TypeHelper.IsTypeAssignableFrom <IUmbracoEntity>(m.Expression.Type) &&
EndsWithConstant(m) == false)
{
//if this is the case, it means we have a sub expression / nested property access, such as: x.ContentType.Alias == "Test";
//and since the sub type (x.ContentType) is not the same as x, we need to resolve a mapper for x.ContentType to get it's mapped SQL column
//don't execute if compiled
if (Visited == false)
{
var subMapper = _mappingResolver.ResolveMapperByType(m.Expression.Type);
if (subMapper == null)
{
throw new NullReferenceException("No mapper found for type " + m.Expression.Type);
}
var field = subMapper.Map(m.Member.Name, true);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException(string.Format("The mapper returned an empty field for the member name: {0} for type: {1}", m.Member.Name, m.Expression.Type));
}
return(field);
}
//already compiled, return
return(string.Empty);
}
//TODO: When m.Expression.NodeType == ExpressionType.Constant and it's an expression like: content => aliases.Contains(content.ContentType.Alias);
// then an SQL parameter will be added for aliases as an array, however in SqlIn on the subclass it will manually add these SqlParameters anyways,
// however the query will still execute because the SQL that is written will only contain the correct indexes of SQL parameters, this would be ignored,
// I'm just unsure right now due to time constraints how to make it correct. It won't matter right now and has been working already with this bug but I've
// only just discovered what it is actually doing.
var member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
var o = getter();
SqlParameters.Add(o);
//don't execute if compiled
if (Visited == false)
{
return(string.Format("@{0}", SqlParameters.Count - 1));
}
//already compiled, return
return(string.Empty);
}
public static IEnumerable <T> GetCustomQuery <T>(this DatabaseFacade database, string sql, SqlParameters parameters)
where T : new()
{
return(RunCustomCommand <T>(database, sql, CommandType.Text, parameters.Array));
}