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