private string BuildWhereClause(DataRow row)
{
if (row.Table.PrimaryKey.Length <= 0)
{
return("");
}
string PriColumnName = row.Table.PrimaryKey[0].ColumnName;
string PriColumnValue = "";
if (row.RowState == DataRowState.Deleted)
{
switch (row.Table.PrimaryKey[0].DataType.Name.ToLower())
{
case "string":
PriColumnValue = SQLUtility.SQLString(row[row.Table.PrimaryKey[0], DataRowVersion.Original].ToString());
break;
case "guid":
PriColumnValue = SQLUtility.SQLString(row[row.Table.PrimaryKey[0], DataRowVersion.Original].ToString());
break;
default:
PriColumnValue = row[row.Table.PrimaryKey[0], DataRowVersion.Original].ToString();
break;
}
}
else
{
switch (row.Table.PrimaryKey[0].DataType.Name.ToLower())
{
case "string":
PriColumnValue = SQLUtility.SQLString(row[row.Table.PrimaryKey[0]].ToString());
break;
case "guid":
PriColumnValue = SQLUtility.SQLString(row[row.Table.PrimaryKey[0]].ToString());
break;
default:
PriColumnValue = row[row.Table.PrimaryKey[0]].ToString();
break;
}
}
string WhereClause = " WHERE " + PriColumnName + " = " + PriColumnValue;
return(WhereClause);
}
public static void LogThis(string ErrorMessage, LogEventType LogEvent, string BadgeNumber, string AgencyName)
{
bool LogMessage = true;
switch (LogEvent)
{
case LogEventType.Error:
break;
case LogEventType.Warning:
break;
case LogEventType.Info:
break;
case LogEventType.InMessages:
LogMessage = false;
break;
case LogEventType.OutMessages:
LogMessage = false;
break;
case LogEventType.MessagePath:
LogMessage = false;
break;
}
if (LogMessage == false)
{
return;
}
try
{
if (ErrorMessage.Length > 1000)
{
ErrorMessage = ErrorMessage.Substring(0, 999);
}
if (BadgeNumber.Length > 23)
{
BadgeNumber = BadgeNumber.Substring(0, 22);
}
if (AgencyName.Length > 60)
{
AgencyName = AgencyName.Substring(0, 59);
}
AgencyName = "";
string SQL = "INSERT INTO i9SysLog (LogDateTime, LogDescription, LogType, AgencyName, BadgeNumber) VALUES ('" + DateTime.Now.ToString() + "', " + SQLUtility.SQLString(ErrorMessage) + ", " + SQLUtility.SQLString(LogEvent.ToString()) + ", " + SQLUtility.SQLString(AgencyName) + ", " + SQLUtility.SQLString(BadgeNumber) + ")";
SQLAccess da = new SQLAccess();
da.ExecuteSQL(SQL);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// This Method is not used anymore because we switched to a GUID keys
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static long GetSingleTableKey(string TableName)
{
SQLAccess da = new SQLAccess();
long KeyValue = -1;
StringBuilder SQLi9Table = new StringBuilder();
SQLi9Table.AppendLine(" BEGIN TRAN T1 ");
SQLi9Table.AppendLine(" Update i9TableKey set KeyValue = KeyValue + 1 WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" SELECT KeyValue FROM i9TableKey WHERE TableName = " + SQLUtility.SQLString(TableName) + " ");
SQLi9Table.AppendLine(" COMMIT TRAN T1 ");
string SQL = SQLi9Table.ToString();
DataTable dt = da.GetDataTable(SQLi9Table.ToString(), "Results");
if (dt.Rows.Count > 0)
{
if (DBNull.Value != dt.Rows[0]["KeyValue"])
{
if (String.IsNullOrEmpty(dt.Rows[0]["KeyValue"].ToString()) == false)
{
KeyValue = (long)dt.Rows[0]["KeyValue"];
}
}
}
return(KeyValue);
}
// Returns a SQL INSERT command. Assumes autoincrement is identity (optional)
private string BuildInsertSQL(DataRow row)
{
DataTable table = row.Table;
StringBuilder sql = new StringBuilder("INSERT INTO " + GetTableName(table.TableName) + " (");
StringBuilder values = new StringBuilder("VALUES (");
bool bFirst = true;
bool bIdentity = false;
string identityType = null;
foreach (DataColumn column in table.Columns)
{
if (column.AutoIncrement)
{
bIdentity = true;
switch (column.DataType.Name)
{
case "Int16":
identityType = "smallint";
break;
case "SByte":
identityType = "tinyint";
break;
case "Int64":
identityType = "bigint";
break;
case "Decimal":
identityType = "decimal";
break;
default:
identityType = "int";
break;
}
}
else
{
if (bFirst)
{
bFirst = false;
}
else
{
sql.Append(", ");
values.Append(", ");
}
sql.Append(column.ColumnName);
switch (column.DataType.Name)
{
case "Int16":
if (row[column] == DBNull.Value)
{
values.Append(" null ");
}
else
{
values.Append(row[column].ToString());
}
break;
case "SByte":
if (row[column] == DBNull.Value)
{
values.Append(" null ");
}
else
{
values.Append(row[column].ToString());
}
break;
case "Int64":
if (row[column] == DBNull.Value)
{
values.Append(" null ");
}
else
{
values.Append(row[column].ToString());
}
break;
case "Decimal":
if (row[column] == DBNull.Value)
{
values.Append(" null ");
}
else
{
values.Append(row[column].ToString());
}
break;
default:
if (row[column] == DBNull.Value)
{
values.Append(" null ");
}
else
{
values.Append(SQLUtility.SQLString(row[column].ToString()));
}
break;
}
}
}
sql.Append(") ");
sql.Append(values.ToString());
sql.Append(")");
if (bIdentity)
{
sql.Append("; SELECT CAST(scope_identity() AS ");
sql.Append(identityType);
sql.Append(")");
}
return(sql.ToString());
}
private string BuildUpdateSQL(DataRow row)
{
DataTable table = row.Table;
StringBuilder UpdateSql = new StringBuilder("UPDATE " + GetTableName(table.TableName) + " SET ");
string WhereClause = BuildWhereClause(row);
bool bFirst = true;
//bool bIdentity = false;
//string identityType = null;
foreach (DataColumn column in table.Columns)
{
if (column.AutoIncrement || IsPrimaryKey(row, column))
{
//bIdentity = true;
//switch (column.DataType.Name)
//{
// case "Int16":
// identityType = "smallint";
// break;
// case "SByte":
// identityType = "tinyint";
// break;
// case "Int64":
// identityType = "bigint";
// break;
// case "Decimal":
// identityType = "decimal";
// break;
// default:
// identityType = "int";
// break;
//}
}
else
{
if (bFirst)
{
bFirst = false;
}
else
{
UpdateSql.Append(", ");
}
switch (column.DataType.Name)
{
case "Int16":
if (row[column] == DBNull.Value)
{
UpdateSql.Append(column.ColumnName + " = null ");
}
else
{
UpdateSql.Append(column.ColumnName + " = " + row[column].ToString());
}
break;
case "SByte":
if (row[column] == DBNull.Value)
{
UpdateSql.Append(column.ColumnName + " = null ");
}
else
{
UpdateSql.Append(column.ColumnName + " = " + row[column].ToString());
}
break;
case "Int64":
if (row[column] == DBNull.Value)
{
UpdateSql.Append(column.ColumnName + " = null ");
}
else
{
UpdateSql.Append(column.ColumnName + " = " + row[column].ToString());
}
break;
case "Decimal":
if (row[column] == DBNull.Value)
{
UpdateSql.Append(column.ColumnName + " = null ");
}
else
{
UpdateSql.Append(column.ColumnName + " = " + row[column].ToString());
}
break;
default:
if (row[column] == DBNull.Value)
{
UpdateSql.Append(column.ColumnName + " = null ");
}
else
{
UpdateSql.Append(column.ColumnName + " = " + SQLUtility.SQLString(row[column].ToString()));
}
break;
}
}
}
UpdateSql.Append(WhereClause);
//if (bIdentity)
//{
// UpdateSql.Append("; SELECT CAST(scope_identity() AS ");
// UpdateSql.Append(identityType);
// UpdateSql.Append(")");
//}
return(UpdateSql.ToString());
}
public static string GetReportNumber(string i9ModuleID)
{
SQLAccess da = new SQLAccess();
string ReportNumber = "";
//need a transaction
StringBuilder SQLReportNumber = new StringBuilder();
SQLReportNumber.AppendLine(" BEGIN TRAN T1 ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and ResetReportNumber = 'DAY' ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and CONVERT(date, LastUpdate) != CONVERT(date, getdate()) ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" ELSE ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" if EXISTS( select * from i9ModuleReportNumber where i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " and year(LastUpdate) != year(getdate()) ) ");
SQLReportNumber.AppendLine(" BEGIN ");
SQLReportNumber.AppendLine(" update i9ModuleReportNumber set LastUpdate = GetDate(), ReportNumber = StartNumber WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" END ");
SQLReportNumber.AppendLine(" Update i9ModuleReportNumber set ReportNumber = ReportNumber + 1 WHERE i9ModuleID = " + SQLUtility.SQLString(i9ModuleID) + " ");
SQLReportNumber.AppendLine(" select * from i9ModuleReportNumber ");
SQLReportNumber.AppendLine(" COMMIT TRAN T1 ");
string SQL = SQLReportNumber.ToString();
DataTable dt = da.GetDataTable(SQLReportNumber.ToString(), "Results");
if (dt.Rows.Count > 0)
{
ReportNumber = dt.Rows[0]["ReportNumber"].ToString();
if (DBNull.Value != dt.Rows[0]["NumberPrefix"])
if (String.IsNullOrEmpty(dt.Rows[0]["NumberPrefix"].ToString()) == false)
ReportNumber = DateTime.Now.ToString(dt.Rows[0]["NumberPrefix"].ToString()) + ReportNumber;
if (DBNull.Value != dt.Rows[0]["NumberSubFix"])
if (String.IsNullOrEmpty(dt.Rows[0]["NumberSubFix"].ToString()) == false)
ReportNumber = ReportNumber + DateTime.Now.ToString(dt.Rows[0]["NumberSubFix"].ToString());
}
return ReportNumber;
}
