protected override string VisitMemberAccess(MemberExpression?m)
{
if (m is null)
{
return(string.Empty);
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(TDto))
{
return(Visited ? string.Empty : GetFieldName(_pd, m.Member.Name, _alias));
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Convert)
{
return(Visited ? string.Empty : GetFieldName(_pd, m.Member.Name, _alias));
}
UnaryExpression member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
Func <object> getter = lambda.Compile();
var o = getter();
SqlParameters.Add(o);
return(Visited ? string.Empty : "@" + (SqlParameters.Count - 1));
}
protected virtual string VisitNew(NewExpression nex)
{
// TODO : check !
var member = Expression.Convert(nex, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
try
{
var getter = lambda.Compile();
object o = getter();
SqlParameters.Add(o);
return(string.Format("@{0}", SqlParameters.Count - 1));
//return GetQuotedValue(o, o.GetType());
}
catch (InvalidOperationException)
{
// FieldName ?
List <Object> exprs = VisitExpressionList(nex.Arguments);
var r = new StringBuilder();
foreach (Object e in exprs)
{
r.AppendFormat("{0}{1}",
r.Length > 0 ? "," : "",
e);
}
return(r.ToString());
}
}
protected override string VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null &&
m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(T))
{
var field = _mapper.Map(m.Member.Name);
return(field);
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Convert)
{
var field = _mapper.Map(m.Member.Name);
return(field);
}
var member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
object o = getter();
SqlParameters.Add(o);
return(string.Format("@{0}", SqlParameters.Count - 1));
//return GetQuotedValue(o, o != null ? o.GetType() : null);
}
protected override string VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null &&
m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(T))
{
var field = _mapper.Map(m.Member.Name, true);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException("The mapper returned an empty field for the member name: " + m.Member.Name);
}
return(field);
}
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Convert)
{
var field = _mapper.Map(m.Member.Name, true);
if (field.IsNullOrWhiteSpace())
{
throw new InvalidOperationException("The mapper returned an empty field for the member name: " + m.Member.Name);
}
return(field);
}
var member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
object o = getter();
SqlParameters.Add(o);
return(string.Format("@{0}", SqlParameters.Count - 1));
//return GetQuotedValue(o, o != null ? o.GetType() : null);
}
protected virtual string VisitUnary(UnaryExpression u)
{
switch (u.NodeType)
{
case ExpressionType.Not:
var o = Visit(u.Operand);
//use a Not equal operator instead of <> since we don't know that <> works in all sql servers
switch (u.Operand.NodeType)
{
case ExpressionType.MemberAccess:
//In this case it wil be a false property , i.e. x => !Trashed
SqlParameters.Add(true);
return(string.Format("NOT ({0} = @0)", o));
default:
//In this case it could be anything else, such as: x => !x.Path.StartsWith("-20")
return(string.Format("NOT ({0})", o));
}
default:
return(Visit(u.Operand));
}
}
/// <summary>
/// Forms an ExecutableSql collection which is the SQL without DECLARE or GO statements, or comments.
/// Puts Comments into a separate collection
/// </summary>
/// <param name="lines">The Array of lines of SQL to process (usually from File.ReadAllLines())</param>
public void ParseSql(string[] lines)
{
Init();
bool inCommentBlock = false;
foreach (var line in lines)
{
// Ignore empty lines
if (string.IsNullOrWhiteSpace(line))
{
continue;
}
// Remove 'go' statements
if (IsGo(line))
{
continue;
}
// Preserve block comment content
if (inCommentBlock)
{
if (EndsCommentBlock(line))
{
inCommentBlock = false;
}
SaveCommentIfNonEmpty(line);
continue;
}
else if (StartsCommentBlock(line))
{
// We're in a comment block if this line doesn't end the block it started /* Like this */
inCommentBlock = !EndsCommentBlock(line);
SaveCommentIfNonEmpty(line);
continue;
}
else if (IsInlineComment(line))
{
// Preserve inline comment content
SaveCommentIfNonEmpty(line);
continue;
}
// Parse delcarations and save meaningful SQL statements
var declaration = ParseDeclaration(line);
if (declaration != null)
{
SqlParameters.Add(declaration);
}
else
{
// This is a regular, meaningful SQL line which is not a DECLARE statement
ExecutableLines.Add(line);
}
}
}
internal void AddAllParameters(Dictionary <string, IDbDataParameter> parameters)
{
foreach (var parameter in parameters)
{
if (!SqlParameters.ContainsKey(parameter.Key))
{
SqlParameters.Add(parameter.Key, parameter.Value);
}
}
}
public static SqlParameters DecodeParameters(IEnumerable <KeyValue> parameters)
{
SqlParameters sqlParameters = new SqlParameters();
foreach (var parameter in parameters)
{
sqlParameters.Add(DecodeParameter(parameter));
}
return(sqlParameters);
}
//public readonly string[] FormsAlias = { "it", "A", "B", "C", "D", "E" };
/// <summary>
/// 追加参数
/// </summary>
public void AddParameter(object obj)
{
if (obj == null || obj == DBNull.Value)
{
Builder.Append("NULL");
}
else
{
string name = "p" + SqlParameters.Count;
SqlParameters.Add(name, obj);
Builder.Append('@');
Builder.Append(name);
}
}
private bool IsValidData()
{
string userName = tbUserName.Text;
string password = MD5EncryptionFunction.Encrypt(tbPassword.Text);
string selectSql = @"SELECT * FROM `user` WHERE `status`=1 AND username = @username AND password = @password";
SqlParameters sqlParameters = new SqlParameters();
sqlParameters.Add(new SqlParameter("username", userName));
sqlParameters.Add(new SqlParameter("password", password));
DataTable resultDt = MySqlFunction.GetDataTable(new SqlDetail(selectSql, sqlParameters));
if (resultDt.Rows.Count <= 0)
{
fncFilter.alert(MessagesVariable.InvalidUserNameOrPasswod);
ClearData();
tbUserName.Select();
return(false);
}
string code = resultDt.Rows[0]["usercode"].ToString();
string fullName = resultDt.Rows[0]["fullname"].ToString();
string syncId = resultDt.Rows[0]["SyncId"].ToString();
selectSql = @"SELECT * FROM `userauth` WHERE `userid` = @userwid";
sqlParameters.Clear();
sqlParameters.Add(new SqlParameter("userwid", syncId));
resultDt = MySqlFunction.GetDataTable(new SqlDetail(selectSql, sqlParameters));
List <string> Authorizations = new List <string>();
foreach (DataRow dr in resultDt.Rows)
{
Authorizations.Add(dr["authorization"].ToString());
}
User = new cls_user();
User.setcls_user(code, fullName, Authorizations, Convert.ToInt32(syncId));
return(true);
}
protected override string VisitMethodCall(MethodCallExpression?m)
{
if (m is null)
{
return(string.Empty);
}
Type?declaring = m.Method.DeclaringType;
if (declaring != typeof(SqlTemplate))
{
return(base.VisitMethodCall(m));
}
if (m.Method.Name != "Arg" && m.Method.Name != "ArgIn")
{
throw new NotSupportedException($"Method SqlTemplate.{m.Method.Name} is not supported.");
}
ParameterInfo[] parameters = m.Method.GetParameters();
if (parameters.Length != 1 || parameters[0].ParameterType != typeof(string))
{
throw new NotSupportedException(
$"Method SqlTemplate.{m.Method.Name}({string.Join(", ", parameters.Select(x => x.ParameterType))} is not supported.");
}
Expression arg = m.Arguments[0];
string? name;
if (arg.NodeType == ExpressionType.Constant)
{
name = arg.ToString();
}
else
{
// though... we probably should avoid doing this
UnaryExpression member = Expression.Convert(arg, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
Func <object> getter = lambda.Compile();
name = getter().ToString();
}
SqlParameters.Add(new SqlTemplate.TemplateArg(RemoveQuote(name)));
return(Visited
? string.Empty
: $"@{SqlParameters.Count - 1}");
}
/// <summary>
/// 追加参数
/// </summary>
public void AddParameter(object obj)
{
if (obj == null || obj == DBNull.Value)
{
Builder.Append("NULL");
Builder.Replace(" = NULL", " IS NULL ");
Builder.Replace(" <> NULL", " IS NOT NULL ");
}
else
{
string name = "Param_" + SqlParameters.Count;
SqlParameters.Add(name, obj);
Builder.Append('@');
Builder.Append(name);
}
}
protected virtual string VisitConstant(ConstantExpression c)
{
if (c.Value == null)
{
return("null");
}
SqlParameters.Add(c.Value);
return(string.Format("@{0}", SqlParameters.Count - 1));
//if (c.Value is bool)
//{
// object o = GetQuotedValue(c.Value, c.Value.GetType());
// return string.Format("({0}={1})", GetQuotedTrueValue(), o);
//}
//return GetQuotedValue(c.Value, c.Value.GetType());
}
protected virtual string VisitLambda(LambdaExpression lambda)
{
if (lambda.Body.NodeType == ExpressionType.MemberAccess)
{
var m = lambda.Body as MemberExpression;
if (m.Expression != null)
{
//This deals with members that are boolean (i.e. x => IsTrashed )
string r = VisitMemberAccess(m);
SqlParameters.Add(true);
return(string.Format("{0} = @{1}", r, SqlParameters.Count - 1));
//return string.Format("{0}={1}", r, GetQuotedTrueValue());
}
}
return(Visit(lambda.Body));
}
internal void BuildDelete(T entity, PropertyInfo[] primaryKeys)
{
SqlQuery.AppendFormat(numberFormat, connectorQuery.DeleteQuery, PluralizedEntityName, PluralizedEntityName[0]);
var mainPrimaryKeyName = primaryKeys[0].GetName();
// Append first primary key condition.
SqlQuery.AppendFormat(numberFormat, connectorQuery.Equal, mainPrimaryKeyName);
SqlParameters.Add($"{mainPrimaryKeyName}", primaryKeys[0].GetGetter <T>().GetValue(entity));
for (var i = 1; i < primaryKeys.Length; i++)
{
var primaryKeyName = primaryKeys[i].GetName();
SqlQuery.AppendFormat(numberFormat, connectorQuery.AndEqual, primaryKeyName);
SqlParameters.Add($"{primaryKeyName}", primaryKeys[i].GetGetter <T>().GetValue(entity));
}
}
internal void BuildUpdate(MethodCallExpression[] expression, Expression condition)
{
SqlQuery.AppendFormat(numberFormat, connectorQuery.UpdateQuery, Pluralize <T>());
for (var i = 0; i < expression.Length; i++)
{
var memberName = (expression[i].Arguments[0] as MemberExpression).Member.GetName();
MemberExpression memberExpression = null;
object memberValue = null;
if (expression[i].Arguments[1].NodeType == ExpressionType.MemberAccess)
{
memberExpression = expression[i].Arguments[1] as MemberExpression;
}
else if (expression[i].Arguments[1].NodeType == ExpressionType.Convert)
{
memberExpression = (expression[i].Arguments[1] as UnaryExpression).Operand as MemberExpression;
}
else if (expression[i].Arguments[1].NodeType == ExpressionType.Constant)
{
memberValue = (expression[i].Arguments[1] as ConstantExpression).Value;
}
memberValue = memberValue ?? GetExpressionValue(memberExpression);
SqlQuery.AppendFormat(numberFormat, connectorQuery.Equal + ", ", memberName);
SqlParameters.Add($"@{memberName}", memberValue);
}
if (condition != null)
{
SqlQuery.Append("WHERE ");
Visit(condition);
SqlQuery.Replace(", WHERE", " WHERE");
SqlQuery.Replace(", FROM", " FROM");
}
else
{
SqlQuery.Remove(SqlQuery.Length - 2, 2);
}
}
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)
{
string field = GetFieldName(_pd, m.Member.Name);
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)
{
string field = GetFieldName(_pd, m.Member.Name);
return(field);
}
//already compiled, return
return(string.Empty);
}
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);
}
internal void BuildUpdate(T entity, PropertyInfo[] properties, PropertyInfo[] primaryKeys)
{
SqlQuery.AppendFormat(numberFormat, connectorQuery.UpdateQuery, PluralizedEntityName, PluralizedEntityName[0]);
for (var i = 0; i < properties.Length; i++)
{
// Don't update primary keys.
if (properties[i].HasAttribute <PrimaryKeyAttribute>())
{
continue;
}
var name = properties[i].GetName();
var value = properties[i].GetGetter <T>().GetValue(entity);
SqlQuery.AppendFormat(numberFormat, connectorQuery.Equal + ", ", name);
SqlParameters.Add($"@{name}", value is bool?Convert.ToByte(value) : value);
}
SqlQuery.AppendFormat(numberFormat, connectorQuery.UpdateQueryEnd, PluralizedEntityName, PluralizedEntityName[0]);
var mainPrimaryKeyName = primaryKeys[0].GetName();
// Append first primary key condition.
SqlQuery.AppendFormat(numberFormat, connectorQuery.Equal, mainPrimaryKeyName);
SqlParameters.Add($"@{mainPrimaryKeyName}", primaryKeys[0].GetGetter <T>().GetValue(entity));
for (var i = 1; i < primaryKeys.Length; i++)
{
var primaryKeyName = primaryKeys[i].GetName();
SqlQuery.AppendFormat(numberFormat, connectorQuery.AndEqual, primaryKeyName);
SqlParameters.Add($"{primaryKeyName}", primaryKeys[i].GetGetter <T>().GetValue(entity));
}
SqlQuery.Replace(", WHERE", " WHERE");
SqlQuery.Replace(", FROM", " FROM");
}
public override void VisitInvocationExpression(InvocationExpressionSyntax node)
{
var identifierNames = node.DescendantNodes()
.OfType <SimpleNameSyntax>()
.ToList();
if (identifierNames.Count < 2)
{
return;
}
if (identifierNames[0].Identifier.Text != _variableName)
{
return;
}
if (identifierNames[1].Identifier.Text != "Add")
{
IsAllParametersStatic = false;
return;
}
if (node.ArgumentList.Arguments.Count < 1)
{
return;
}
if (!(node.ArgumentList.Arguments[0].Expression is LiteralExpressionSyntax literalExpression))
{
IsAllParametersStatic = false;
return;
}
SqlParameters.Add(literalExpression.Token.ValueText);
}
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 "Code":
//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());
}
}
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();
}
}
}
internal void AppendParameter(ValueObject valueObject, string parameterKey, object parameterValue,
Type parameterType, ValueNode node)
{
if (valueObject.Value == null)
{
_rawSqlBuilder.Append("null");
_formattedSqlBuilder.Append("null");
return;
}
var convertedValue = parameterValue;
var isDateOnly = false;
if (valueObject.IsDateTime && node.UseBuiltinFunction)
{
convertedValue = valueObject.TruncateTime((DateTime)convertedValue);
isDateOnly = true;
}
if (valueObject.IsDateTimeOffset && node.UseBuiltinFunction)
{
convertedValue = valueObject.TruncateTime((DateTimeOffset)convertedValue);
isDateOnly = true;
}
if (valueObject.IsString && node.UseBuiltinFunction)
{
switch (node.BuiltinFunctionName)
{
case BuiltinFunctionName.StartsWith:
convertedValue = valueObject.GetStartsWithValue((string)convertedValue, node.EscapeChar);
break;
case BuiltinFunctionName.Contains:
convertedValue = valueObject.GetContainsValue((string)convertedValue, node.EscapeChar);
break;
case BuiltinFunctionName.EndsWith:
convertedValue = valueObject.GetEndsWithValue((string)convertedValue, node.EscapeChar);
break;
case BuiltinFunctionName.Escape:
convertedValue = valueObject.GetEscapedValue((string)convertedValue, node.EscapeChar);
break;
}
}
var param = _config.DataParameterCreator()
.AddName(parameterKey)
.AddValue(convertedValue);
if (valueObject.EasySqlParameterAttribute != null)
{
param.AddDbType(valueObject.EasySqlParameterAttribute.DbType);
}
if (!SqlParameters.ContainsKey(parameterKey))
{
SqlParameters.Add(parameterKey, param);
}
// parameterKey is sql parameter name
var localParameterKey = parameterKey;
if (!_config.Dialect.EnableNamedParameter)
{
localParameterKey = _config.Dialect.ParameterPrefix;
}
_rawSqlBuilder.Append(localParameterKey);
if (isDateOnly)
{
if (valueObject.IsDateTime)
{
_formattedSqlBuilder.Append(valueObject.ToLogFormatDateOnly((DateTime)convertedValue));
}
if (valueObject.IsDateTimeOffset)
{
_formattedSqlBuilder.Append(valueObject.ToLogFormatDateOnly((DateTimeOffset)convertedValue));
}
}
else
{
_formattedSqlBuilder.Append(valueObject.ToLogFormat(convertedValue));
}
}
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(SqlSyntax, m.Member.Name, true);
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(SqlSyntax, m.Member.Name, true);
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) &&
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 = _mappers[m.Expression.Type]; // throws if not found
var field = subMapper.Map(SqlSyntax, m.Member.Name, true);
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.
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);
}
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 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 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.");
}
}
protected override string VisitMemberAccess(MemberExpression?m)
{
if (m is null)
{
return(string.Empty);
}
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 is not null &&
_mappers.TryGetMapper(m.Expression.Type, out BaseMapper? 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);
*/
UnaryExpression member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
Func <object> 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);
}
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();
}
}
}