public string BuildFieldClause(DataJuggler.Net5.DataRow FieldRow, DataJuggler.Net5.DataField Field, int FieldCount)
{
// if FieldCount > 0
StringBuilder sb = new StringBuilder();
// If FieldCount > 0
if (FieldCount > 1)
{
// Append The Word And
sb.Append(" And ");
}
// Append fieldName
sb.Append(Field.FieldName);
sb.Append(" = ");
// If This Is Not Numeric Data Add Single Quote '
if (!FieldRow.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Append fieldValue
sb.Append(Field.FieldValue);
// If This Is Not Numeric Data Add Single Quote '
if (!FieldRow.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Return New String
return(sb.ToString());
}
public bool IsUpdateable(DataJuggler.Net5.DataRow Row)
{
// Initial Value
bool updateable = false;
// Check If There Is A PrimaryKey
DataJuggler.Net5.DataField PrimaryKey = Row.PrimaryKey();
// Check If PrimaryKey Exists
if (PrimaryKey.FieldName == "void")
{
// Not Updateable
updateable = false;
}
else
{
// Check If PrimaryKey Has A Value
// If It Does Have A Value Then It Is Updateable
// Else It Is Not
updateable = PrimaryKey.HasValue();
}
// return value
return(updateable);
}
public string CreateDeleteSQL(DataJuggler.Net5.DataRow Row)
{
// Verify There Are Changes In This row & The row Is Updateable
if (IsUpdateable(Row))
{
// Create StringBuilder
StringBuilder sb = new StringBuilder("Delete From [");
// Append tableName
sb.Append(Row.ParentTable.Name);
// Append Closing Bracket
sb.Append("]");
// Add Where Clause
string WhereClause = BuildWhereClause(Row);
sb.Append(WhereClause);
// Return sql Statement To Insert This row
return(sb.ToString());
}
else
{
return("void");
}
}
public string BuildWhereClause(DataJuggler.Net5.DataRow Row)
{
// Create StringBuilder
StringBuilder sb = new StringBuilder(" Where ");
// String
string FieldClause = "";
// int FieldCount
int PrimaryKeyCount = 0;
// Loop Through Each field
foreach (DataJuggler.Net5.DataField Field in Row.Fields)
{
// If This field Is A Primary Key
if (Field.PrimaryKey)
{
// Increment Primary Key Count
PrimaryKeyCount++;
// Build FieldClause
FieldClause = BuildFieldClause(Row, Field, PrimaryKeyCount);
// Append FieldClause
sb.Append(FieldClause);
}
}
// Return
return(sb.ToString());
}
public string CreateUpdateSQL(DataJuggler.Net5.DataRow Row)
{
// Boolean For Is This The First field
bool FirstField = true;
// Verify There Are Changes In This row & The row Is Updateable
if ((Row.Changes) && (IsUpdateable(Row)))
{
// Create StringBuilder
StringBuilder sb = new StringBuilder("Update [");
// Append tableName
sb.Append(Row.ParentTable.Name);
sb.Append("] Set ");
// Add Each fieldName
foreach (DataJuggler.Net5.DataField Field in Row.Fields)
{
// If This field.dataType Is Supported
if (DataManager.IsSupported(Field.DataType))
{
// If This Is Not The Primary Key
if (!Field.PrimaryKey)
{
// If This field Has Changes
if (Field.Changes)
{
// If This Is Not The First Item Add A Comma
if (!FirstField)
{
sb.Append(",");
}
// Append fieldName
sb.Append(Field.FieldName);
// Append Equal Sign =
sb.Append(" = ");
// If This Is Not Numberic Data Add Single Quote '
if (!Row.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Add field Value
sb.Append(AppendFieldValue(Field.FieldValue.ToString()));
// If This Is Not Numberic Data Add Single Quote '
if (!Row.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Set FirstField To False
FirstField = false;
}
}
}
}
// Verify 1 Or More Fields Has Been Updated
if (!FirstField)
{
// Add Where Clause
string WhereClause = BuildWhereClause(Row);
sb.Append(WhereClause);
// Return sql Statement To Insert This row
return(sb.ToString());
}
else
{
return("void");
}
}
else
{
return("void");
}
}
public string CreateInsertSQL(DataJuggler.Net5.DataRow Row, bool WithOrWithoutChanges)
{
// Boolean For Is This The First field
bool FirstField = true;
// Verify There Are Changes In This row
if ((Row.Changes) || (WithOrWithoutChanges))
{
// Create StringBuilder
StringBuilder sb = new StringBuilder("Insert Into [");
// Append tableName
sb.Append(Row.ParentTable.Name);
sb.Append("] (");
// Add Each fieldName
foreach (DataJuggler.Net5.DataField Field in Row.Fields)
{
// If This field.dataType Is Supported
if (DataManager.IsSupported(Field.DataType))
{
// If This field Is Not An AutoNumber && This Is Not A Null Date (Had To Be And)
if ((Field.DataType != DataManager.DataTypeEnum.Autonumber) && (!IsNullDateField(Field)) && (Field.HasValue()))
{
// If This Is Not The First Item Add A Comma
if (!FirstField)
{
sb.Append(",");
}
// Append fieldName
sb.Append(Field.FieldName);
// Set FirstField To False
FirstField = false;
}
}
}
// Append Values
sb.Append(") Values(");
// Reset First field
FirstField = true;
// Add Each fieldName
foreach (DataJuggler.Net5.DataField Field in Row.Fields)
{
// If This field.dataType Is Supported
if (DataManager.IsSupported(Field.DataType))
{
// If This field Is Not An AutoNumber && This Is Not A Null Date (Had To Be And)
if ((Field.DataType != DataManager.DataTypeEnum.Autonumber) && (!IsNullDateField(Field)) && (Field.HasValue()))
{
// If This Is Not The First Item Add A Comma
if (!FirstField)
{
sb.Append(",");
}
// If This Is A String Add A Single Quote
if (!Row.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Add fieldValue
sb.Append(AppendFieldValue(Field.FieldValue.ToString()));
// If This Is A String Add A Single Quote
if (!Row.IsNumericDataType(Field.DataType))
{
sb.Append("'");
}
// Set FirstField To False
FirstField = false;
}
}
}
// Append Closing Parenthese
sb.Append(")");
// Return sql Statement To Insert This row
return(sb.ToString());
}
else
{
return("void");
}
}
public string CreateInsertSQL(DataJuggler.Net5.DataRow Row)
{
// Return CreateInsertSQL
return(CreateInsertSQL(Row, false));
}
