/// <summary>
/// Returns the count of tables
/// </summary>
/// <returns></returns>
public int GetRecordCount(Epi2000.View view)
{
try
{
#region Input Validation
if (view == null)
{
throw new System.ArgumentNullException("view");
}
#endregion Input Validation
string qryString = " select count(*) from " + db.InsertInEscape(view.TableNames[0]);
Query query = db.CreateQuery(qryString);
return(Int32.Parse((db.ExecuteScalar(query)).ToString()));
}
catch (Exception ex)
{
throw new ApplicationException("Could not retrieve record count.", ex); //TODO: move to shared strings
}
finally
{
}
}
/// <summary>
/// Finds Epi Version and Purpose of the database.
/// </summary>
/// <param name="db">Abstract data type for database.</param>
/// <returns>Results of test.</returns>
public static bool IsEpi7CollectedData(IDbDriver dbDriver)
{
#region Input Validation
if (dbDriver == null)
{
throw new ArgumentNullException("DB");
}
#endregion
try
{
// Open the database and look for dbInfo table, find Epi Version and purpose ...
bool isEpi7CollectedData = false;
Query query = dbDriver.CreateQuery("select" + Util.InsertIn(dbDriver.InsertInEscape("EpiVersion"), StringLiterals.SPACE) + StringLiterals.COMMA + Util.InsertIn(dbDriver.InsertInEscape("Purpose"), StringLiterals.SPACE) +
"from" + StringLiterals.SPACE + "metaDBInfo");
DataTable results = dbDriver.Select(query);
if (results.Rows.Count > 0)
{
foreach (DataRow row in results.Rows)
{
isEpi7CollectedData = (row["EpiVersion"].ToString().Substring(0, 1) == "7") && ((row["Purpose"].ToString() == "0") || (row["Purpose"].ToString() == "2"));
}
}
return isEpi7CollectedData;
}
finally
{
}
}
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>
/// 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);
}
}
//);
}