/// <summary> /// Logs a database query /// </summary> /// <param name="query"></param> public static void Log(Query query) { // Log the sql statement ... Log(query.SqlStatement); string paramsString = string.Empty; // Log parameters ... foreach (QueryParameter param in query.Parameters) { paramsString += param.ParameterName + "=" + (Util.IsEmpty(param.Value) ? String.Empty:param.Value.ToString()) + "; "; } Log(paramsString); }
/// <summary> /// Logically delete or undelete the records (change RecStatus) that match the criteria /// </summary> /// <remarks> /// Criteria may be '*', which is all records; observes current selection criteria /// </remarks> /// <param name="criteria"></param> /// <param name="delete">Delete or undelete the records that match the criteria</param> /// <returns>The number of rows affected</returns> public int LogicallyDeleteRecords(string criteria, bool delete) { string tableName = PrimaryTable.TableName; string where = GetSqlStatementPartWhere(); if (!string.IsNullOrEmpty(criteria)) { if (string.IsNullOrEmpty(where)) { where = " where "; } else if (criteria != "*") { where += " and "; } where += criteria; } string sql = "Update " + tableName + " set recstatus = " + ((delete) ? "0" : "1") + where; Epi.Data.Query qry = Db.CreateQuery(sql); return(Db.ExecuteNonQuery(qry)); }
/// <summary> /// Physically (permanently) delete the records that match the criteria /// </summary> /// <remarks> /// Criteria may be '*', which is all records; observes current selection criteria /// </remarks> /// <param name="criteria"></param> /// <returns>The number of rows affected</returns> public int PhysicallyDeleteRecords(string criteria) { string tableName = PrimaryTable.TableName; string where = GetSqlStatementPartWhere(); if (!string.IsNullOrEmpty(criteria)) { if (string.IsNullOrEmpty(where)) { where = " where "; } else if (criteria != "*") { where += " and "; } where += criteria; } string sql = "Delete from " + tableName + where; Epi.Data.Query qry = Db.CreateQuery(sql); return(Db.ExecuteNonQuery(qry)); }
/// <summary> /// Checks the Epi Info 3.5.x project for problems. /// </summary> private void CheckSourceProjectForProblems() { if (SetStatus != null) { SetStatus(string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_MESSAGE_ANALYZING_PROJECT, sourceProject.Name)); } errorList = new ImportExportErrorList(); try { Epi.Data.IDbDriver db = sourceProject.CollectedData.GetDatabase(); string validationMessage = string.Empty; if (!db.IsDatabaseFormatValid(ref validationMessage)) { errorList.Add(ImportExportMessageType.Error, "1100", validationMessage); return; } if (sourceProject.Views.Count == 0) { errorList.Add(ImportExportMessageType.Error, "1020", ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1020); } foreach (Epi.Epi2000.View view in sourceProject.Views) { // Check #1 - Does the view have a name? if (string.IsNullOrEmpty(view.Name)) { errorList.Add(ImportExportMessageType.Error, "1000", ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1000); } if (!view.Name.ToLowerInvariant().StartsWith("view")) { errorList.Add(ImportExportMessageType.Error, "1021", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1021, view.Name)); } if (view.Name.Contains(' ')) { errorList.Add(ImportExportMessageType.Error, "1022", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1022, view.Name)); } // Check #2 - Does the view have any pages? if (view.Pages.Count <= 0) { errorList.Add(ImportExportMessageType.Warning, "5000", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_W5000, view.Name)); } CheckViewSourceReferences(view); if (!string.IsNullOrEmpty(view.TableName) && db.TableExists(view.TableName)) { object result = db.ExecuteScalar(db.CreateQuery("SELECT Count(*) FROM [" + view.TableName + "]")); CheckViewColumns(view); int baseRowCount = 0; if (result is Int32) { baseRowCount = (Int32)result; } else { errorList.Add(ImportExportMessageType.Error, "1001", "Something that should never fail has failed."); } bool hasUniqueKey = db.ColumnExists(view.TableName, "UniqueKey"); bool hasRecStatus = db.ColumnExists(view.TableName, "RecStatus"); // Check #3a - Does the table have a UniqueKey? if (!hasUniqueKey) { errorList.Add(ImportExportMessageType.Error, "1002", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1002, view.Name)); } // Check #3b - Does the table have a RECSTATUS column? if (!hasRecStatus) { errorList.Add(ImportExportMessageType.Error, "1003", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1003, view.Name)); } if (hasUniqueKey) { // Check #4a - Is the unique key implemented as a number? DataTable dt = db.GetTableData(view.TableName, "UniqueKey"); string dataType = dt.Columns[0].DataType.ToString(); if (dataType != "System.Int32") { errorList.Add(ImportExportMessageType.Error, "1004", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1004, view.Name)); } // Check #4b - Does the unique key have any missing / null values? (May occur if user modifies table by hand for various reasons) Epi.Data.Query selectDistinctQuery = db.CreateQuery("SELECT UniqueKey FROM [" + view.TableName + "] WHERE UniqueKey = NULL OR UniqueKey is NULL"); dt = db.Select(selectDistinctQuery); if (dt.Rows.Count > 0) { errorList.Add(ImportExportMessageType.Error, "1005", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1005, view.Name)); } } if (hasRecStatus) { // Check #4c - Is RECSTATUS implemented as a number? DataTable dt = db.GetTableData(view.TableName, "RECSTATUS"); string dataType = dt.Columns[0].DataType.ToString(); if (dataType != "System.Int16" && dataType != "System.Byte") { errorList.Add(ImportExportMessageType.Error, "1006", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1006, view.Name)); } // Check #4d - Does RECSTATUS have any values other than 0 or 1? Epi.Data.Query selectDistinctQuery = db.CreateQuery("SELECT RECSTATUS FROM [" + view.TableName + "] WHERE RECSTATUS = NULL OR RECSTATUS is NULL OR RECSTATUS < 0 OR RECSTATUS > 1"); dt = db.Select(selectDistinctQuery); if (dt.Rows.Count > 0) { errorList.Add(ImportExportMessageType.Error, "1007", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1007, view.Name)); } } List <string> reservedTableNames = new List <string>(); reservedTableNames.Add("metabackgrounds"); reservedTableNames.Add("metadatatypes"); reservedTableNames.Add("metadbinfo"); reservedTableNames.Add("metafields"); reservedTableNames.Add("metafieldtypes"); reservedTableNames.Add("metagridcolumns"); reservedTableNames.Add("metaimages"); reservedTableNames.Add("metalayerrendertypes"); reservedTableNames.Add("metalayers"); reservedTableNames.Add("metamaplayers"); reservedTableNames.Add("metamappoints"); reservedTableNames.Add("metamaps"); reservedTableNames.Add("metapages"); reservedTableNames.Add("metapatterns"); reservedTableNames.Add("metaprograms"); reservedTableNames.Add("metaviews"); // Check #5 - Does the view's table name conflict with Epi Info 7 meta table names? if (reservedTableNames.Contains(view.TableName.ToLowerInvariant())) { errorList.Add(ImportExportMessageType.Error, "1008", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1008, view.Name, view.TableName)); } if (view.IsWideTableView) { foreach (string tableName in view.TableNames) { // Wide Table Check #1 - Does this wide table have a name? if (string.IsNullOrEmpty(tableName)) { errorList.Add(ImportExportMessageType.Error, "1009", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1009, view.Name)); } // Wide Table Check #2 - Does this wide table not exist? (We're in an IF that checks to see if the first table exists, so if this doesn't exist, there is a serious problem) else if (!db.TableExists(tableName)) { errorList.Add(ImportExportMessageType.Error, "1010", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1010, tableName, view.Name)); } else { // Wide Table Check #3 - See if the wide table has a UniqueKey. Each wide table should have this. hasUniqueKey = db.ColumnExists(tableName, "UniqueKey"); if (!hasUniqueKey) { errorList.Add(ImportExportMessageType.Error, "1011", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1011, tableName, view.Name)); } if (hasUniqueKey) { DataTable dt = db.GetTableData(tableName, "UniqueKey"); string dataType = dt.Columns[0].DataType.ToString(); if (dataType != "System.Int32") { errorList.Add(ImportExportMessageType.Error, "1012", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1012, tableName, view.Name)); } Epi.Data.Query selectDistinctQuery = db.CreateQuery("SELECT UniqueKey FROM [" + tableName + "] WHERE UniqueKey = NULL or UniqueKey is NULL"); dt = db.Select(selectDistinctQuery); if (dt.Rows.Count > 0) { errorList.Add(ImportExportMessageType.Error, "1013", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1013, tableName, view.Name)); } } // Wide Table Check #4 - Check to make sure the row counts match across wide tables. result = db.ExecuteScalar(db.CreateQuery("SELECT Count(*) FROM [" + tableName + "]")); if (result is Int32) { int tRowCount = (Int32)result; if (tRowCount != baseRowCount) { errorList.Add(ImportExportMessageType.Error, "1014", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1014, view.Name)); } } // Wide Table Check #5 - Check all DATA references for duplicates or missing references DataTable generalCheckTable = db.Select(db.CreateQuery("SELECT [Datatable] FROM [" + view.Name + "] WHERE [Name] = 'GENERAL'")); if (generalCheckTable.Rows.Count == 0) { errorList.Add(ImportExportMessageType.Error, "1015", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1015, view.Name)); } else if (generalCheckTable.Rows.Count > 1) { errorList.Add(ImportExportMessageType.Error, "1016", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1016, view.Name)); } else { if (generalCheckTable.Rows[0][0].ToString().Contains(",")) { string[] dataRefs = generalCheckTable.Rows[0][0].ToString().Split(','); List <string> dataRefsList = new List <string>(); foreach (string dataRef in dataRefs) { if (!dataRefsList.Contains(dataRef)) { dataRefsList.Add(dataRef); } else { errorList.Add(ImportExportMessageType.Error, "1017", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1017, view.Name)); } } foreach (string dataRef in dataRefsList) { Query dataRefQuery = db.CreateQuery("SELECT [Name] FROM [" + view.Name + "] WHERE [Name] = @DataRef AND [Type] = 'SOURCE'"); dataRefQuery.Parameters.Add(new QueryParameter("@DataRef", DbType.String, dataRef)); DataTable dataRefTable = db.Select(dataRefQuery); if (dataRefTable.Rows.Count == 0) { errorList.Add(ImportExportMessageType.Error, "1018", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1018, view.Name, dataRef)); } } } else { errorList.Add(ImportExportMessageType.Error, "1019", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E1019, view.Name)); } } } } } // end if wide table } // end if (!string.IsNullOrEmpty(view.TableName) && db.TableExists(view.TableName)) if (SetStatus != null) { SetStatus(string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_MESSAGE_ANALYZING_RELATIONS, view.Name)); } Dictionary <string, string> relateButtonNames = new Dictionary <string, string>(); DataTable relateCheckTable1 = db.Select(db.CreateQuery("SELECT [Name], [Pfonttype] FROM [" + view.Name + "] WHERE [Type] = 'RELATE'")); foreach (DataRow row in relateCheckTable1.Rows) { string relButtonName = row[0].ToString(); string relViewName = row[1].ToString(); if (!relateButtonNames.ContainsKey(relButtonName)) { relateButtonNames.Add(relButtonName, relViewName); } else { errorList.Add(ImportExportMessageType.Error, "2000", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E2000, view.Name, relButtonName)); } } DataTable relateCheckTable2 = db.Select(db.CreateQuery("SELECT [Name], [Datatable] FROM [" + view.Name + "] WHERE [Name] LIKE 'RELVIE%' AND [Type] = 'SOURCE'")); foreach (DataRow row in relateCheckTable2.Rows) { string relViewRefName = row[0].ToString(); string rViewName = row[1].ToString(); if (!relateButtonNames.ContainsValue(relViewRefName)) { errorList.Add(ImportExportMessageType.Warning, "5001", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_W5001, relViewRefName, view.Name)); } if (string.IsNullOrEmpty(rViewName)) { errorList.Add(ImportExportMessageType.Error, "2001", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E2001, relViewRefName, view.Name)); } else if (!db.TableExists(rViewName)) { errorList.Add(ImportExportMessageType.Error, "2002", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E2002, relViewRefName, view.Name)); } else { foreach (Epi.Epi2000.View iView in sourceProject.Views) { if (iView.Name == rViewName && !string.IsNullOrEmpty(iView.TableName)) { bool tableExists = db.TableExists(iView.TableName); if (tableExists && !db.ColumnExists(iView.TableName, "FKEY")) { errorList.Add(ImportExportMessageType.Error, "2003", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E2003, view.Name, rViewName)); } else if (tableExists) { DataTable relateCheckTable3 = db.Select(db.CreateQuery("SELECT FKEY FROM [" + iView.TableName + "] WHERE [FKEY] is NULL")); if (relateCheckTable3.Rows.Count > 0) { errorList.Add(ImportExportMessageType.Error, "2004", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E2004, iView.Name)); } } } } } } if (SetStatus != null) { SetStatus(string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_MESSAGE_ANALYZING_FIELDS, view.Name)); } foreach (Epi.Epi2000.Page page in view.Pages) { DataTable fieldsTable = sourceProject.Metadata.GetFieldsOnPageAsDataTable(view.Name, page.Position); foreach (DataRow fieldRow in fieldsTable.Rows) { string fieldName = fieldRow["Name"].ToString(); if (Char.IsNumber(fieldName[0])) { errorList.Add(ImportExportMessageType.Error, "3000", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E3000, fieldName, view.Name)); } if (fieldName.Contains(" ")) { errorList.Add(ImportExportMessageType.Error, "3001", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E3001, fieldName, view.Name)); } MetaFieldType fieldType = Epi.Epi2000.MetadataDbProvider.InferFieldType(fieldRow); //if (view.IsWideTableView == false) //{ // if (fieldType != MetaFieldType.CommandButton && fieldType != MetaFieldType.Grid && fieldType != MetaFieldType.Group && fieldType != MetaFieldType.LabelTitle && fieldType != MetaFieldType.Mirror && fieldType != MetaFieldType.Relate && // db.ColumnExists(view.TableName, fieldName) == false && db.TableExists(view.TableName)) // { // problems.Add(new KeyValuePair<ProjectUpgradeProblemType, string>(ProjectUpgradeProblemType.Error, "3002: The field " + fieldName + " in view " + view.Name + " has no corresponding column in the data table.")); // } //} if (fieldType == MetaFieldType.Grid) { errorList.Add(ImportExportMessageType.Warning, "5002", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_W5002, fieldName, view.Name)); } else if (fieldType == MetaFieldType.Mirror) { string mirrorSource = fieldRow["Lists"].ToString(); if (mirrorSource == fieldName) { errorList.Add(ImportExportMessageType.Error, "3003", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E3003, fieldName, view.Name)); } if (string.IsNullOrEmpty(mirrorSource)) { errorList.Add(ImportExportMessageType.Warning, "5003", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_W5003, fieldName, view.Name)); } else { bool found = false; foreach (DataRow iRow in sourceProject.Metadata.GetFieldsAsDataTable(view.Name).Rows) { if (iRow["Name"].ToString().Equals(mirrorSource)) { found = true; break; } } if (!found) { errorList.Add(ImportExportMessageType.Warning, "5004", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_W5004, fieldName, view.Name)); } } } else if (fieldType == MetaFieldType.LegalValues || fieldType == MetaFieldType.CommentLegal) { string lists = fieldRow["Lists"].ToString(); string valueFieldName = string.Empty; string[] rightSide = lists.Split('('); if (rightSide.Length > 1) { string[] leftSide = rightSide[1].Split(','); if (leftSide.Length > 1) { valueFieldName = leftSide[1].Trim(')').Trim(); string codeReference = leftSide[0]; Query codeTableCheckQuery = db.CreateQuery("SELECT [Name], [Datatable] FROM [" + view.Name + "] WHERE [Name] = @CodeReference"); codeTableCheckQuery.Parameters.Add(new QueryParameter("@CodeReference", DbType.String, codeReference)); DataTable codeTableCheck = db.Select(codeTableCheckQuery); if (codeTableCheck.Rows.Count > 0) { string tableName = codeTableCheck.Rows[0]["Datatable"].ToString(); foreach (Epi.Epi2000.View sView in sourceProject.Views) { if (!string.IsNullOrEmpty(sView.TableName) && sView.TableName.ToLowerInvariant() == tableName.ToLowerInvariant()) { errorList.Add(ImportExportMessageType.Error, "3013", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E3013, fieldName, view.Name)); } } } } } } else if (fieldType == MetaFieldType.Codes) { string lists = fieldRow["Lists"].ToString(); string valueFieldName = string.Empty; string[] rightSide = lists.Split('('); if (rightSide.Length > 1) { string[] leftSide = rightSide[1].Split(','); if (leftSide.Length > 2) { valueFieldName = leftSide[2].Trim(')').Trim(); string[] asterisks = valueFieldName.Split('*'); if (asterisks.Length > 1) { valueFieldName = asterisks[1]; } } } string tableName = string.Empty; int indexOf = lists.IndexOf(','); tableName = lists.Substring(indexOf + 1); indexOf = tableName.IndexOf('*'); if (indexOf > 0) { tableName = tableName.Substring(0, indexOf).Trim(); } foreach (Epi.Epi2000.View sView in sourceProject.Views) { if (!string.IsNullOrEmpty(sView.TableName) && sView.TableName.ToLowerInvariant() == tableName.ToLowerInvariant()) { errorList.Add(ImportExportMessageType.Error, "3004", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E3004, fieldName, view.Name)); } } } } } CheckAllFields(view, db); } db.Dispose(); db = null; } catch (Exception ex) { errorList.Add(ImportExportMessageType.Error, "4000", string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_ERROR_E4000, ex.Message)); } finally { if (SetStatus != null) { SetStatus(string.Format(ImportExportSharedStrings.UPGRADE_PROBLEM_CHECK_MESSAGE_FINISHED, sourceProject.Name)); } } }
/// <summary> /// Creates a new blank row for a given form's base table and all of its page tables. /// </summary> /// <param name="form">The form where the row should be added.</param> /// <param name="guid">The Guid value to use for the row.</param> /// <param name="keyValues">The key values to use for custom matching</param> /// <param name="fkey">The foreign key for the row.</param> /// <param name="firstSaveId">The user ID of the first person that saved this record.</param> /// <param name="firstSaveTime">The time when the record was first saved.</param> /// <param name="lastSaveId">The user ID of the last person that saved this record.</param> /// <param name="lastSaveTime">The time when the record was last saved.</param> protected virtual void CreateNewBlankRow(View form, string guid, Dictionary <Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null) { #region Input Validation if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); } if (form == null) { throw new ArgumentNullException("form"); } #endregion // Input Validation if (Conn.State != ConnectionState.Open) { Conn.Open(); } IDbDriver db = DestinationProject.CollectedData.GetDatabase(); StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(form.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); WordBuilder fields = new WordBuilder(","); fields.Append("[GlobalRecordId]"); if (!string.IsNullOrEmpty(fkey)) { fields.Append("[FKEY]"); } if (!string.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); } if (!string.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); } if (firstSaveTime.HasValue) { fields.Append("[FirstSaveTime]"); } if (lastSaveTime.HasValue) { fields.Append("[LastSaveTime]"); } sb.Append("(" + fields.ToString() + ")"); sb.Append(" values ("); List <QueryParameter> parameters = new List <QueryParameter>(); WordBuilder values = new WordBuilder(","); values.Append("'" + guid + "'"); if (!string.IsNullOrEmpty(fkey)) { values.Append("@FKEY"); parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey)); } if (!string.IsNullOrEmpty(firstSaveId)) { values.Append("@FirstSaveLogonName"); parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId)); } if (!string.IsNullOrEmpty(lastSaveId)) { values.Append("@LastSaveLogonName"); parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId)); } if (firstSaveTime.HasValue) { values.Append("@FirstSaveTime"); parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime)); } if (lastSaveTime.HasValue) { values.Append("@LastSaveTime"); parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime)); } sb.Append(values.ToString()); sb.Append(") "); Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString()); insertQuery.Parameters = parameters; if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } //Parallel.ForEach(form.Pages, page => foreach (Page page in form.Pages) { WordBuilder wbFields = new WordBuilder(","); WordBuilder wbParams = new WordBuilder(","); List <QueryParameter> queryParams = new List <QueryParameter>(); wbFields.Add("[GlobalRecordId]"); wbParams.Add("@GlobalRecordId"); queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid)); foreach (KeyValuePair <Field, object> kvp in keyValues) { RenderableField field = kvp.Key as RenderableField; PackageFieldData fieldData = new PackageFieldData(); fieldData.FieldValue = kvp.Value; fieldData.FieldName = field.Name; fieldData.Page = field.Page; if (field.Page.TableName.Equals(page.TableName)) { wbFields.Add(db.InsertInEscape(fieldData.FieldName)); wbParams.Add("@" + fieldData.FieldName); QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page); queryParams.Add(parameter); } } sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(page.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); sb.Append("("); sb.Append(wbFields.ToString()); sb.Append(")"); sb.Append(" values ("); sb.Append(wbParams.ToString()); sb.Append(") "); insertQuery = db.CreateQuery(sb.ToString()); foreach (QueryParameter queryParam in queryParams) { insertQuery.Parameters.Add(queryParam); } if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } } //); }
// Implements IDisposable.Dispose /// <summary> /// Releases all resources used by the form data importer /// </summary> public void Dispose() { SetProgressBar = null; SetStatus = null; AddStatusMessage = null; if (formsToProcess != null) { formsToProcess.Clear(); formsToProcess = null; } if (columnsToNull != null) { columnsToNull.Clear(); columnsToNull = null; } if (gridColumnsToNull != null) { gridColumnsToNull.Clear(); gridColumnsToNull = null; } if (selectQuery != null) { selectQuery = null; } if (sourceGUIDs != null) { sourceGUIDs.Clear(); sourceGUIDs = null; } destinationProjectDataDriver = null; destinationProject = null; destinationView = null; sourceProjectDataDriver.Dispose(); sourceProjectDataDriver = null; sourceProject.Dispose(); sourceProject = null; sourceView.Dispose(); sourceView = null; GC.Collect(); }
/// <summary> /// TODO: Add method description here /// </summary> /// <param name="dataTable"></param> /// <param name="tableName"></param> /// <param name="insertQuery"></param> /// <param name="updateQuery"></param> public abstract void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery);
/// <summary> /// Helper method for the CreateFromXml method. Loads a list of row filter conditions from XML. /// </summary> /// <param name="element">XmlElement to process</param> private void CreateRowFiltersFromXML(XmlElement element) { if (element.Name.ToLower().Equals("rowfilter")) { foreach (XmlElement childElement in element.ChildNodes) { if (childElement.Name.ToLower().Equals("rowfilterquery")) { selectQuery = sourceProject.CollectedData.GetDbDriver().CreateQuery(childElement.InnerText.Replace(">", ">").Replace("<", "<")); } else if (childElement.Name.ToLower().Equals("rowfilterparameters")) { foreach (XmlElement gcElement in childElement.ChildNodes) { System.Data.DbType dbType = System.Data.DbType.String; string name = ""; object value = null; foreach (XmlElement ggcElement in gcElement.ChildNodes) { switch (ggcElement.Name.ToLower()) { case "dbtype": dbType = ((DbType)Int32.Parse(ggcElement.InnerText)); break; case "name": name = ggcElement.InnerText; break; case "value": value = ggcElement.InnerText; break; } } QueryParameter queryParameter = new QueryParameter(name, dbType, value); if (selectQuery != null) { selectQuery.Parameters.Add(queryParameter); } } } else if (childElement.Name.ToLower().Equals("rowfilterconditions")) { List<IRowFilterCondition> conditions = new List<IRowFilterCondition>(); foreach (XmlElement grandChildElement in childElement.ChildNodes) { if (grandChildElement.HasAttribute("filterType")) { string strType = grandChildElement.Attributes["filterType"].Value + ",Epi.ImportExport"; Type filterType = Type.GetType(strType, false, true); if (filterType != null) { IRowFilterCondition condition = (IRowFilterCondition)Activator.CreateInstance(filterType, new object[] { }); condition.CreateFromXml(grandChildElement); conditions.Add(condition); } } } this.rowFilterConditions = conditions; } } } string filter = String.Empty; if (this.rowFilterConditions.Count > 0) { filter = ImportExportSharedStrings.SCRIPT_FILTERS; WordBuilder wb = new WordBuilder(" and "); foreach (IRowFilterCondition rfc in this.rowFilterConditions) { wb.Add(rfc.Description); } filter = filter + wb.ToString(); CallbackAddStatusMessage(filter); } }
/// <summary> /// Executes a SELECT statement against the database and returns a disconnected data table. NOTE: Use this overload to work with Typed DataSets. /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <param name="dataTable">Table that will contain the result</param> /// <returns>A data table object</returns> public override DataTable Select(Query selectQuery, DataTable dataTable) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("selectQuery"); } if (dataTable == null) { throw new ArgumentNullException("dataTable"); } #endregion Input Validation if (selectQuery.SqlStatement.Contains("TOP 2")) { selectQuery = CreateQuery(selectQuery.SqlStatement.Replace("TOP 2 ", string.Empty).Replace(";",string.Empty) + " LIMIT 2"); } if (selectQuery.SqlStatement.Contains("[")) { selectQuery = CreateQuery(selectQuery.SqlStatement.Replace("[", string.Empty).Replace("]", string.Empty)); } IDbConnection connection = GetConnection(connectionString); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(); adapter.SelectCommand = (NpgsqlCommand)GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters); try { //Logger.Log(selectQuery); adapter.Fill(dataTable); adapter.FillSchema(dataTable, SchemaType.Source); return dataTable; } catch (Exception ex) { throw new System.ApplicationException("Error executing select query against the database.", ex); } }
/// <summary> /// Executes a sql query to select records into a data table /// </summary> /// <param name="selectQuery"></param> /// <returns></returns> public abstract DataTable Select(Query selectQuery);
/// <summary> /// Executes a SQL statement that does not return anything. /// </summary> /// <param name="nonQueryStatement">The query to be executed against the database</param> public abstract int ExecuteNonQuery(Query nonQueryStatement);
/// <summary> /// Create a DataReader on the specified table /// </summary> /// <param name="selectQuery"></param> /// <param name="commandBehavior"></param> /// <returns>An instance of an object that implements IDataReader</returns> public abstract IDataReader ExecuteReader(Query selectQuery, CommandBehavior commandBehavior);
/// <summary> /// Creates a new connection and executes a select query /// </summary> /// <param name="selectQuery"></param> /// <returns>Result set</returns> public override DataTable Select(Query selectQuery) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("selectQuery"); } #endregion DataTable table = new DataTable(); return Select(selectQuery, table); }
/// <summary> /// Warning! This method does not support transactions! /// </summary> /// <param name="dataTable">Table that will contain the result.</param> /// <param name="tableName">Name of table to update.</param> /// <param name="insertQuery">SQL statement and parameters for inserting row.</param> /// <param name="updateQuery">SQL statement and parameters for updating row.</param> public override void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery) { #region Input Validation if (dataTable == null) { throw new ArgumentNullException("DataTable"); } if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("TableName"); } #endregion Input Validation IDbConnection connection = GetConnection(); OleDbDataAdapter adapter = new OleDbDataAdapter(); if (insertQuery != null) { adapter.InsertCommand = (OleDbCommand)GetCommand(insertQuery.SqlStatement, connection, insertQuery.Parameters); } if (updateQuery != null) { adapter.UpdateCommand = (OleDbCommand)GetCommand(updateQuery.SqlStatement, connection, updateQuery.Parameters); } try { adapter.Update(dataTable); } catch (Exception ex) { throw new System.ApplicationException("Error updating data.", ex); } }
/// <summary> /// Executes a SELECT statement against the database and returns a disconnected data table. NOTE: Use this overload to work with Typed DataSets. /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <param name="dataTable">Table that will contain the result.</param> /// <returns>A data table object</returns> public override DataTable Select(Query selectQuery, DataTable dataTable) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("selectQuery"); } if (dataTable == null) { throw new ArgumentNullException("dataTable"); } #endregion Input Validation IDbConnection connection = GetConnection(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = (OleDbCommand)GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters); try { adapter.Fill(dataTable); try { adapter.FillSchema(dataTable, SchemaType.Source); } catch { } return dataTable; } catch (OleDbException oleDbException) { throw oleDbException; } catch (Exception ex) { throw new System.ApplicationException(SharedStrings.ERROR_SELECT_QUERY_DATA_SOURCE, ex); } }
/// <summary> /// Executes a non-query statement /// </summary> /// <param name="nonQueryStatement">Query to execute</param> /// <returns>An integer</returns> public override int ExecuteNonQuery(Query nonQueryStatement) { #region Input Validation if (nonQueryStatement == null) { throw new ArgumentNullException("query"); } #endregion //Logger.Log(nonQueryStatement); IDbConnection conn = this.GetConnection(connectionString); IDbCommand command = GetCommand(nonQueryStatement.SqlStatement, conn, nonQueryStatement.Parameters); try { OpenConnection(conn); return command.ExecuteNonQuery(); } finally { CloseConnection(conn); } }
/// <summary> /// Warning! This method does not support transactions! /// </summary> /// <param name="dataTable"></param> /// <param name="tableName"></param> /// <param name="insertQuery"></param> /// <param name="updateQuery"></param> public override void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery) { #region Input Validation if (dataTable == null) { throw new ArgumentNullException("DataTable"); } if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("TableName"); } if (insertQuery == null) { throw new ArgumentNullException("InsertQuery"); } if (updateQuery == null) { throw new ArgumentNullException("UpdateQuery"); } #endregion Input Validation IDbConnection connection = GetConnection(connectionString); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(); string edittedUpdateQuery = updateQuery.SqlStatement; //edittedUpdateQuery = updateQuery.SqlStatement.Replace("@OldValue", "`@OldValue`"); //edittedUpdateQuery = edittedUpdateQuery.Replace("@NewValue", "`@NewValue`"); adapter.InsertCommand = (NpgsqlCommand)GetCommand(insertQuery.SqlStatement, connection, insertQuery.Parameters); adapter.UpdateCommand = (NpgsqlCommand)GetCommand(edittedUpdateQuery, connection, updateQuery.Parameters); try { //Logger.Log(insertQuery); //Logger.Log(updateQuery); adapter.Update(dataTable); } catch (Exception ex) { throw new System.ApplicationException("Error updating data.", ex); } }
protected override bool CreateNewRow(IDbConnection conn, View form, XElement record, string guid, string fkey = "", string recStatus = "1", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null) { SqlConnection connection = conn as SqlConnection; IDbDriver db = Project.CollectedData.GetDatabase(); StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(form.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); WordBuilder fields = new WordBuilder(","); fields.Append("[GlobalRecordId]"); if (!String.IsNullOrEmpty(fkey)) { fields.Append("[FKEY]"); } if (!String.IsNullOrEmpty(recStatus)) { fields.Append("[RecStatus]"); } if (!String.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); } if (!String.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); } if (firstSaveTime.HasValue) { firstSaveTime = new DateTime(firstSaveTime.Value.Year, firstSaveTime.Value.Month, firstSaveTime.Value.Day, firstSaveTime.Value.Hour, firstSaveTime.Value.Minute, firstSaveTime.Value.Second); fields.Append("[FirstSaveTime]"); } if (lastSaveTime.HasValue) { lastSaveTime = new DateTime(lastSaveTime.Value.Year, lastSaveTime.Value.Month, lastSaveTime.Value.Day, lastSaveTime.Value.Hour, lastSaveTime.Value.Minute, lastSaveTime.Value.Second); fields.Append("[LastSaveTime]"); } sb.Append("(" + fields.ToString() + ")"); sb.Append(" values ("); List <QueryParameter> parameters = new List <QueryParameter>(); WordBuilder values = new WordBuilder(","); values.Append("'" + guid + "'"); if (!String.IsNullOrEmpty(fkey)) { values.Append("@FKEY"); parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey)); } if (!String.IsNullOrEmpty(recStatus)) { values.Append("@RecStatus"); parameters.Add(new QueryParameter("@RecStatus", DbType.Int32, Convert.ToInt32(recStatus))); } if (!String.IsNullOrEmpty(firstSaveId)) { values.Append("@FirstSaveLogonName"); parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId)); } if (!String.IsNullOrEmpty(lastSaveId)) { values.Append("@LastSaveLogonName"); parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId)); } if (firstSaveTime.HasValue) { values.Append("@FirstSaveTime"); parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime)); } if (lastSaveTime.HasValue) { values.Append("@LastSaveTime"); parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime)); } sb.Append(values.ToString()); sb.Append(") "); Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString()); insertQuery.Parameters = parameters; using (SqlTransaction transaction = connection.BeginTransaction("SampleTransaction")) { try { using (IDbCommand baseTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters)) { baseTableCommand.Transaction = transaction; object baseObj = baseTableCommand.ExecuteNonQuery(); } //foreach (Page page in form.Pages) //{ // sb = new StringBuilder(); // sb.Append(" insert into "); // sb.Append(db.InsertInEscape(page.TableName)); // sb.Append(StringLiterals.SPACE); // sb.Append(StringLiterals.SPACE); // sb.Append("([GlobalRecordId])"); // sb.Append(" values ("); // sb.Append("'" + guid + "'"); // sb.Append(") "); // insertQuery = db.CreateQuery(sb.ToString()); // using (IDbCommand pageTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters)) // { // pageTableCommand.Transaction = transaction; // object pageObj = pageTableCommand.ExecuteNonQuery(); // } //} foreach (Page page in form.Pages) { WordBuilder wbFieldNames = new WordBuilder(", "); WordBuilder wbParamNames = new WordBuilder(", "); List <QueryParameter> pageInsertParameters = new List <QueryParameter>(); foreach (RenderableField field in page.Fields) { if (field is IDataField && record.Element(field.Name) != null) { wbFieldNames.Add(field.Name); wbParamNames.Add("@" + field.Name); pageInsertParameters.Add( GetQueryParameterForField(field, FormatFieldData(form, field.Name, record.Element(field.Name).Value), form, page)); } } wbFieldNames.Add("GlobalRecordId"); wbParamNames.Add("@GlobalRecordId"); pageInsertParameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid)); Query inserteQuery = db.CreateQuery("INSERT INTO " + page.TableName + " (" + wbFieldNames.ToString() + ") VALUES (" + wbParamNames.ToString() + ")"); foreach (QueryParameter parameter in pageInsertParameters) { inserteQuery.Parameters.Add(parameter); } using (IDbCommand pageTableCommand = GetCommand(inserteQuery.SqlStatement, conn, inserteQuery.Parameters)) { pageTableCommand.Transaction = transaction; object obj = pageTableCommand.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception ex) { Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Type: {0}", ex.GetType())); Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Message: {0}", ex.Message)); try { transaction.Rollback(); } catch (Exception ex2) { Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Type: {0}", ex2.GetType())); Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Message: {0}", ex2.Message)); } return(false); } } return(true); }
/// <summary> /// Executes a scalar query against the database /// </summary> /// <param name="scalarStatement">The query to be executed against the database</param> /// <returns>object</returns> public abstract object ExecuteScalar(Query scalarStatement);
/// <summary> /// Convert Query to String format for EnterWebService /// </summary> /// <param name="pValue"></param> /// <returns>Sql query in string form</returns> private string ConvertQueryToString(Query pValue) { string result = pValue.SqlStatement; foreach (QueryParameter parameter in pValue.Parameters) { switch (parameter.DbType) { case DbType.Currency: case DbType.Byte: case DbType.Decimal: case DbType.Double: case DbType.Int16: case DbType.Int32: case DbType.Int64: case DbType.SByte: case DbType.UInt16: case DbType.UInt32: case DbType.UInt64: case DbType.Boolean: result = Regex.Replace(result, parameter.ParameterName, parameter.Value.ToString(), RegexOptions.IgnoreCase); break; default: result = Regex.Replace(result, parameter.ParameterName, "'" + parameter.Value.ToString() + "'", RegexOptions.IgnoreCase); break; } } return result; }
/// <summary> /// Assigns GUIDs to the form's base table and all page tables /// </summary> /// <param name="min"></param> /// <param name="max"></param> private void AssignGUIDs(int min, int max) { try { if (conn.State != ConnectionState.Open) { conn.Open(); } for (int i = min; i < max; i++) { DataRow row = DT.Rows[i]; string GUID = row["GlobalRecordId"].ToString(); StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationDriver.InsertInEscape(destinationView.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); sb.Append("([GlobalRecordId])"); sb.Append(" values ("); sb.Append("'" + GUID + "'"); sb.Append(") "); Epi.Data.Query insertQuery = destinationDriver.CreateQuery(sb.ToString()); if (project.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { destinationDriver.ExecuteNonQuery(insertQuery); } if (SetProgressBar != null) { SetProgressBar(1); } foreach (Page destinationPage in destinationView.Pages) { sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationDriver.InsertInEscape(destinationPage.TableName)); sb.Append(StringLiterals.SPACE); sb.Append("([GlobalRecordId])"); sb.Append(" values ("); sb.Append("'" + GUID + "'"); sb.Append(") "); insertQuery = destinationDriver.CreateQuery(sb.ToString()); if (project.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { destinationDriver.ExecuteNonQuery(insertQuery); } } if (CheckForCancellation != null) { bool cancelled = CheckForCancellation(); if (cancelled) { return; } } } } catch (Exception ex) { conn.Close(); conn.Dispose(); throw ex; } finally { } }
/// <summary> /// Executes a SQL non-query within a transaction. /// </summary> /// <param name="nonQueryStatement">The query to be executed against the database</param> /// <param name="transaction">The transaction object</param> public abstract int ExecuteNonQuery(Query nonQueryStatement, IDbTransaction transaction);
/// <summary> /// Executes a SQL statement and returns total records affected. /// </summary> /// <param name="query">query object</param> /// <returns>total records affected</returns> public override int ExecuteNonQuery(Query query) { #region Input Validation if (query == null) { throw new ArgumentNullException("query"); } #endregion //Logger.Log(query); IDbConnection conn = this.GetConnection(); IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters); try { OpenConnection(conn); object obj = command.ExecuteNonQuery(); return (int)obj; } finally { CloseConnection(conn); } }
/// <summary> /// Create a DataReader on the specified table /// </summary> /// <param name="selectQuery"></param> /// <returns>An instance of an object that implements IDataReader</returns> public abstract IDataReader ExecuteReader(Query selectQuery);
/// <summary> /// Executes a transacted SQL statement and returns total records affected. /// </summary> /// <param name="query"></param> /// <param name="transaction"></param> /// <returns>total records affected</returns> public override int ExecuteNonQuery(Query query, IDbTransaction transaction) { #region Input Validation if (query == null) { throw new ArgumentNullException("query"); } if (transaction == null) { throw new ArgumentNullException("transaction"); } #endregion //Logger.Log(query); IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters); try { // do not try to open connection, we are inside a transaction return command.ExecuteNonQuery(); } finally { // do not close connection, we are inside a transaction } }
/// <summary> /// Executes a scalar query against the database using an existing transaction /// </summary> /// <param name="scalarStatement">The query to be executed against the database</param> /// <param name="transaction">The existing transaction within which to execute</param> /// <returns>object</returns> public abstract object ExecuteScalar(Query scalarStatement, IDbTransaction transaction);
/// <summary> /// Executes a query and returns a stream of rows /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <returns>A data reader object</returns> public override IDataReader ExecuteReader(Query selectQuery) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("SelectQuery"); } #endregion return ExecuteReader(selectQuery, CommandBehavior.Default); }
/// <summary> /// Executes a SELECT statement against the database and returns a disconnected data table. NOTE: Use this overload to work with Typed DataSets. /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <param name="table">Table that will contain the result</param> /// <returns>A DataTable containing the results of the query</returns> public abstract DataTable Select(Query selectQuery, DataTable table);
/// <summary> /// Executes a query and returns a stream of rows /// </summary> /// <param name="selectQuery">The query to be executed against the database</param> /// <returns>A data reader object</returns> public override IDataReader ExecuteReader(Query selectQuery, CommandBehavior commandBehavior) { #region Input Validation if (selectQuery == null) { throw new ArgumentNullException("SelectQuery"); } #endregion IDbCommand command = null; IDbConnection connection = null; try { connection = GetConnection(); OpenConnection(connection); command = GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters); return command.ExecuteReader(commandBehavior); } catch (Exception ex) { throw new System.ApplicationException("Could not execute reader", ex); } }
/// <summary> /// Handles the Click event for the row filter button /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void btnRowFilter_Click(object sender, EventArgs e) { if (cmbPackageForm.SelectedIndex != -1) { FormName = cmbPackageForm.SelectedItem.ToString(); PackageRecordSelectionDialog prsDialog = new PackageRecordSelectionDialog(sourceProject, FormName, rowFilterConditions); prsDialog.AttachDatabase(sourceProject.CollectedData.GetDbDriver()); this.rowFilterConditions = prsDialog.RowFilterConditions; DialogResult result = prsDialog.ShowDialog(); if (result == System.Windows.Forms.DialogResult.OK) { selectQuery = prsDialog.SelectQuery; rowFilterConditions = prsDialog.RowFilterConditions; } } }
/// <summary> /// Executes a scalar query against the database using an existing transaction /// </summary> /// <param name="query">The query to be executed against the database</param> /// <param name="transaction">Null is allowed</param> /// <returns></returns> public override object ExecuteScalar(Query query, IDbTransaction transaction) { #region Input Validation if (query == null) { throw new ArgumentNullException("query"); } if (transaction == null) { throw new ArgumentNullException("transaction"); } #endregion object result; IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters); try { // do not open connection, we are inside a transaction result = command.ExecuteScalar(); } finally { // do not close connection, we are inside a transaction } return result; }
/// <summary> /// Executes a scalar query against the database /// </summary> /// <param name="query">The query to be executed against the database</param> /// <returns></returns> public override object ExecuteScalar(Query query) { #region Input Validation if (query == null) { throw new ArgumentNullException("query"); } #endregion object result; IDbConnection conn = GetConnection(); IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters); try { OpenConnection(conn); result = command.ExecuteScalar(); } finally { CloseConnection(conn); } return result; }
private void btnOK_Click(object sender, EventArgs e) { if (db != null) { View sourceView = sourceProject.Views[formName]; string baseTableName = "t"; string fromClause = sourceView.FromViewSQL; string sql = "SELECT [" + baseTableName + "].[GlobalRecordId] " + fromClause + " WHERE "; foreach (IRowFilterCondition rowFc in rowFilterConditions) { sql = sql + rowFc.Sql; } selectQuery = db.CreateQuery(sql); foreach (IRowFilterCondition rowFc in rowFilterConditions) { selectQuery.Parameters.Add(rowFc.Parameter); } } }