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)); }