static DGVSharedDiagram()
{
SQSelectQuery q = new SQSelectQuery()
{
From = new SQFromClause(new SQFromTable("Share")
{
Join = new SQJoin("Diagram")
{
Predicate = new SQCondition("Diagram.ID", SQRelationOperators.Equal, "Share.DiagramID"),
Join = new SQJoin("DiagramUser")
{
Predicate = new SQCondition("DiagramUser.ID", SQRelationOperators.Equal, "Diagram.UserID")
}
}
}),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("Share.Permission", "Permission"),
new SQAliasableObject("Share.Email", "Email"),
new SQAliasableObject("Diagram.ID", "DiagramID"),
new SQAliasableObject("Diagram.Name", "Diagram"),
new SQAliasableObject("DiagramUser.ID", "OwnerID"),
new SQAliasableObject("DiagramUser.Name", "Owner")
}
};
RegisterView(q);
}
public static List <T> Select(SQConditionBase cond, params SQParameter[] parameters)
{
List <T> res = new List <T>();
SQSelectQuery q = _Views[typeof(T)];
q.Condition = cond;
q.Parameters = new List <SQParameter>(parameters);
SQAdapter adp = DGBase.AdapterProvider();
SQSelectResult rdr = q.Execute(adp);
if (rdr.Reader.Read())
{
List <string> fields = new List <string>();
for (int i = 0; i < rdr.Reader.VisibleFieldCount; i++)
{
fields.Add(rdr.Reader.GetName(i));
}
do
{
T item = new T();
for (int i = 0; i < rdr.Reader.VisibleFieldCount; i++)
{
item.PopulateProperty(fields[i], rdr.Reader.GetValue(i));
}
res.Add(item);
}while (rdr.Reader.Read());
}
return(res);
}
public override SQSelectResult Select(SQSelectQuery query)
{
string queryText = Write(query);
SqlConnection con = GetConnection <SqlConnection>();
bool alreadyOpen = con.State == ConnectionState.Open;
if (!alreadyOpen)
{
con.Open();
}
SqlCommand sqlcmd = new SqlCommand(queryText, con, (SqlTransaction)Transaction);
foreach (SQParameter p in query.Parameters)
{
sqlcmd.Parameters.AddWithValue(p.Name, p.Value);
}
SqlDataReader rdr = sqlcmd.ExecuteReader();
SQSelectResult res = new SQSelectResult(alreadyOpen ? null : con, rdr);
if (query.IncludeTotalRows)
{
if (res.Reader.Read())
{
SetStoredTotalRecordCount(res, res.Reader.IsDBNull(res.Reader.FieldCount - 1) ? 0 : res.Reader.GetInt64(res.Reader.FieldCount - 1));
}
}
return(res);
}
public override string Write(SQSelectQuery q)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + Write(col.Actual) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " '" + col.Alias + "'"));
}
// branch off from original string builder to create
// a get row count statement
StringBuilder sb2 = new StringBuilder();
sb2.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb2.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sb2.AppendLine("ORDER BY " + string.Join(", ", cols.ToArray()));
}
sb.AppendLine(sb2.ToString());
if (q.Top > 0)
{
sb.AppendLine("LIMIT " + q.Top);
}
else if (q.RecordCount > 0)
{
sb.AppendLine(string.Format("LIMIT {0} OFFSET {1}", q.RecordCount, q.RecordStart));
}
if (q.IncludeTotalRows)
{
sb.AppendLine(";SELECT COUNT(*)\r\n" + sb2.ToString());
}
return(sb.ToString());
}
public ORMSelectQueryBuilder(ORMSelect select, SchemaManager schema)
{
_Schema = schema;
SQSelectQuery q = new SQSelectQuery();
_Query = q;
SchemaTable t = _Schema.EnsureSchema(select.FocalType);
q.From = new SQFromClause();
q.From.Tables.Add(new SQFromTable(t.Table.Name, select.FocalType.Name));
if (select.Columns.Count == 0)
{
foreach (SQColumn col in t.Table.Columns)
{
q.Columns.Add(new SQAliasableObject(select.FocalType.Name + "." + col.Name));
}
}
else
{
foreach (string col in select.Columns)
{
EnsureChainJoins(col, select);
q.Columns.Add(new SQAliasableObject(GetPropertyChainColumn(col, select)));
}
}
q.Condition = GetCondition(select.Condition, select);
q.Top = select.Top;
if (select.SortProperties != null)
{
foreach (ORMSortProperty sp in select.SortProperties)
{
EnsureChainJoins(sp.Property, select);
q.SortColumns.Add(new SQSortColumn()
{
Column = GetPropertyChainColumn(sp.Property, select),
Direction = !sp.Ascending.HasValue ?
SortOrder.Unspecified : sp.Ascending.Value ?
SortOrder.Ascending : SortOrder.Descending
});
}
}
q.RecordCount = select.RecordCount;
q.RecordStart = select.RecordStart;
}
public override string Write(SQSelectQuery q)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
if (q.Distinct)
{
sb.AppendLine("\tDISTINCT");
}
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + col.Actual.Write(this) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " '" + col.Alias + "'"));
}
sb.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sb.AppendLine("ORDER BY " + string.Join(", ", cols.ToArray()));
}
if (q.Top > 0)
{
sb.AppendLine("LIMIT 0, " + q.Top);
}
else if (q.RecordCount > 0)
{
sb.AppendLine(string.Format("LIMIT {0}, {1}", q.RecordStart, q.RecordCount));
}
// TODO: total rows
return(sb.ToString());
}
static DGDiagramView()
{
SQSelectQuery sq = new SQSelectQuery()
{
From = new SQFromClause(new SQFromTable("Diagram", "DG")
{
Join = new SQJoin("Snapshot", "Snap")
{
Predicate = new SQCondition("Snap.DiagramID", SQRelationOperators.Equal, "DG.ID")
.And("Snap.IsDefault", SQRelationOperators.Equal, true)
}
}),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("DG.Name", "ID"),
new SQAliasableObject("DG.Name", "Name"),
new SQAliasableObject("Snap.DiagramData", "Def"),
}
};
RegisterView(sq);
}
protected static void RegisterView(SQSelectQuery sq)
{
_Views.TryAdd(typeof(T), sq);
}
public abstract string Write(SQSelectQuery query);
/// <summary>
/// Run the select query.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public virtual SQSelectResult Select(SQSelectQuery query)
{
return(Select(Write(query), query.Parameters));
}
static void Main(string[] args)
{
// get an adapter
SQAdapter adp = !string.IsNullOrEmpty(Settings.Default.SQLServerConn) ? (SQAdapter) new SQLServerAdapter(Settings.Default.SQLServerConn)
: !string.IsNullOrEmpty(Settings.Default.MySQLConn) ? (SQAdapter) new MySQLAdapter(Settings.Default.MySQLConn)
: null;
if (adp == null)
{
return;
}
// Create Table 1
// -------------------------------------------------------
Console.WriteLine("Creating table: FamousQuote");
Console.WriteLine("ID Int64");
Console.WriteLine("FamousPersonID Int64");
Console.WriteLine("Quote String(500)");
SQTable quote = new SQTable()
{
Name = "FamousQuote",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
},
new SQColumn()
{
Name = "FamousPersonID", DataType = SQDataTypes.Int64, Nullable = false
},
new SQColumn()
{
Name = "Quote", DataType = SQDataTypes.String, Length = 500
}
}
};
// delete table if it already exists
if (adp.GetTable(quote.Name) != null)
{
Console.WriteLine("Table '" + quote.Name + "' exists. Deleting...");
adp.RemoveTable(quote.Name);
}
adp.CreateTable(quote);
Console.WriteLine("Table Created. ");
// Create Table 2
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Creating table: FamousPerson");
Console.WriteLine("ID Int64");
Console.WriteLine("FirstName String(50)");
Console.WriteLine("LastName String(50)");
SQTable person = new SQTable()
{
Name = "FamousPerson",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
},
new SQColumn()
{
Name = "FirstName", DataType = SQDataTypes.String, Length = 50
},
new SQColumn()
{
Name = "LastName", DataType = SQDataTypes.String, Length = 50
}
}
};
// delete table if it already exists
if (adp.GetTable(person.Name) != null)
{
Console.WriteLine("Table '" + person.Name + "' exists. Deleting...");
adp.RemoveTable(person.Name);
}
adp.CreateTable(person);
Console.WriteLine("Table Created. ");
// add a foreign key
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Adding a foreign key.");
Console.WriteLine("FamousQuote.FamousPersonID -> FamousPerson.ID");
adp.AddForeignKey(quote.GetColumnByName("FamousPersonID"), person.GetColumnByName("ID"));
// make a couple of insert query objects
// -------------------------------------------------------
string varFirstName = adp.CreateVariable("FirstName");
string varLastName = adp.CreateVariable("LastName");
SQInsertQuery personInsert = new SQInsertQuery()
{
Table = new SQAliasableObject(person.Name),
ReturnID = true,
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FirstName", varFirstName),
new SQSetQueryPair("LastName", varLastName),
}
};
string varPersonID = adp.CreateVariable("PersonID");
string varQuote = adp.CreateVariable("Quote");
SQInsertQuery quoteInsert = new SQInsertQuery()
{
Table = new SQAliasableObject(quote.Name),
ReturnID = false,
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FamousPersonID", varPersonID),
new SQSetQueryPair("Quote", varQuote),
}
};
// add some data using insert objects
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Inserting Data");
Console.WriteLine("Inserting Person. ");
personInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varFirstName, "I"),
new SQParameter(varLastName, "Asimov")
};
Int64 id = personInsert.ExecuteReturnID <Int64>(adp);
Console.WriteLine("Inserting Quote. ");
quoteInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varPersonID, id),
new SQParameter(varQuote, "Never let your sense of morals get in the way of doing what's right. ")
};
quoteInsert.Execute(adp);
Console.WriteLine("Inserting Quote. ");
quoteInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varPersonID, id),
new SQParameter(varQuote, "I do not fear computers. I fear the lack of them. ")
};
quoteInsert.Execute(adp);
// update data
// -------------------------------------------------------
new SQUpdateQuery()
{
UpdateTable = new SQAliasableObject(person.Name),
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FirstName", varFirstName)
},
Condition = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
Parameters = new List <SQParameter>()
{
new SQParameter(varFirstName, "Isaac"),
new SQParameter(varLastName, "Asimov")
}
}.Execute(adp);
// Select data
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Selecting quotes by Asimov. ");
SQSelectQuery select = new SQSelectQuery()
{
From = new SQFromClause(new SQFromTable(quote.Name, "q")
{
Join = new SQJoin(person.Name, "p")
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
}
}),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("q.Quote")
},
Condition = new SQCondition("p.LastName", SQRelationOperators.Like, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
};
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("SQL: ");
Console.Write(select.Write(adp));
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Result: ");
// get datatable instead of reader. this closes the reader
// automatically
foreach (DataRow row in select.Execute(adp).GetDataTable().Rows)
{
Console.WriteLine(row[0]);
}
// Select data joinless
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Selecting quotes by Asimov. Joinless. ");
select = new SQSelectQuery()
{
From = new SQFromClause(
new SQFromTable(quote.Name, "q"),
new SQFromTable(person.Name, "p")
),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("q.Quote")
},
Condition = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
.And("p.LastName", SQRelationOperators.Like, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
};
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("SQL: ");
Console.Write(select.Write(adp));
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Result: ");
SQSelectResult res = select.Execute(adp);
while (res.Reader.Read())
{
Console.WriteLine(res.Reader.GetValue(0));
}
res.Close();
// Delete data
// -------------------------------------------------------
new SQDeleteQuery()
{
DeleteTable = new SQAliasableObject(quote.Name),
Join = new SQJoin(person.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("FamousPersonID", SQRelationOperators.Equal, "FamousPerson.ID")
},
Condition = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
}.Execute(adp);
// Remove created tables
// -------------------------------------------------------
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Removing Table '" + quote.Name + "'");
adp.RemoveTable(quote.Name);
Console.WriteLine("Removing Table '" + person.Name + "'");
adp.RemoveTable(person.Name);
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
public override SQTable GetTable(string name)
{
string varTable = CreateVariable("Table");
string varPK = CreateVariable("PK");
SQSelectQuery q = new SQSelectQuery();
q.Columns.AddRange(new List <SQAliasableObject>
{
new SQAliasableObject("cols.COLUMN_NAME"),
new SQAliasableObject("IS_NULLABLE"),
new SQAliasableObject("DATA_TYPE"),
new SQAliasableObject("CHARACTER_MAXIMUM_LENGTH"),
new SQAliasableObject("NUMERIC_PRECISION"),
new SQAliasableObject("NUMERIC_SCALE"),
new SQAliasableObject("EXTRA", "IS_IDENTITY"),
new SQAliasableObject("CONSTRAINT_TYPE")
});
q.From = new SQFromClause(new SQFromTable("INFORMATION_SCHEMA.COLUMNS", "cols")
{
Join = new SQJoin("INFORMATION_SCHEMA.KEY_COLUMN_USAGE", "tuse")
{
JoinType = SQJoinTypes.Left,
Predicate = new SQCondition("tuse.COLUMN_NAME", SQRelationOperators.Equal, "cols.COLUMN_NAME")
.And("tuse.TABLE_NAME", SQRelationOperators.Equal, "cols.TABLE_NAME"),
Join = new SQJoin("INFORMATION_SCHEMA.TABLE_CONSTRAINTS", "tcons")
{
JoinType = SQJoinTypes.Left,
Predicate = new SQCondition("tcons.CONSTRAINT_NAME", SQRelationOperators.Equal, "tuse.CONSTRAINT_NAME")
.And("tcons.TABLE_NAME", SQRelationOperators.Equal, "cols.TABLE_NAME")
.And("tcons.CONSTRAINT_TYPE", SQRelationOperators.Equal, varPK)
}
}
});
q.Condition = new SQCondition("cols.TABLE_NAME", SQRelationOperators.Equal, varTable);
q.Parameters.Add(new SQParameter(varTable, name));
q.Parameters.Add(new SQParameter(varPK, "PRIMARY KEY"));
SQSelectResult selres = Select(q);
MySqlDataReader rdr = (MySqlDataReader)selres.Reader;
try
{
if (rdr.HasRows)
{
SQTable res = new SQTable()
{
Name = name
};
while (rdr.Read())
{
res.Columns.Add(new SQColumn()
{
Table = res,
Name = rdr.GetString(0),
DataType = GetTypeFromName(rdr.GetString(2)),
Length = rdr.IsDBNull(3) ? 0 : rdr.GetInt64(3),
Nullable = rdr.GetString(1) == "YES",
Precision = rdr.IsDBNull(4) ? 0 : Convert.ToInt32(rdr.GetInt64(4)),
Scale = rdr.IsDBNull(5) ? 0 : rdr.GetInt32(5),
IsIdentity = rdr.GetString(6).ToLower().Contains("auto_increment"),
IsPrimary = rdr.IsDBNull(7) ? false : rdr.GetString(7) == "PRIMARY KEY"
});
}
return(res);
}
}
finally
{
selres.Close();
}
return(null);
}
public override string Write(SQSelectQuery q)
{
string sortColumns = null;
if (q.SortColumns.Count > 0)
{
List <string> cols = new List <string>();
foreach (SQSortColumn col in q.SortColumns)
{
cols.Add(col.Column + (col.Direction == SortOrder.Ascending ? " ASC"
: col.Direction == SortOrder.Descending ? " DESC" : ""));
}
sortColumns = "ORDER BY " + string.Join(", ", cols.ToArray());
}
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
if (q.Distinct)
{
sb.AppendLine("\tDISTINCT");
}
if (q.Top > 0)
{
sb.AppendLine("\tTOP " + q.Top);
}
for (int i = 0; i < q.Columns.Count; i++)
{
SQAliasableObject col = q.Columns[i];
sb.AppendLine((i == 0 ? "\t" : "\t,") + Write(col.Actual) + (string.IsNullOrEmpty(col.Alias) ? string.Empty : " [" + col.Alias + "]"));
}
if (q.IncludeTotalRows)
{
sb.AppendLine(string.Format("\t,ROW_NUMBER() OVER({0}) [{1}]", sortColumns, "_RowNum"));
}
sb.AppendLine("FROM " + Write(q.From));
if (q.Condition != null)
{
sb.AppendLine(string.Format("WHERE {0}", Write(q.Condition)));
}
if (!q.IncludeTotalRows)
{
sb.AppendLine(sortColumns);
}
else
{
// set up paging as an outer query
sb.Insert(0, @";WITH Paged AS
(
");
string pagingWhere = "";
if (q.IncludeTotalRows)
{
pagingWhere = string.Format("WHERE _RowNum BETWEEN {0} AND {1}", q.RecordStart, q.RecordStart + q.RecordCount - 1);
}
sb.AppendLine(@"
)
SELECT R.*, C._RowCount
FROM (SELECT * FROM Paged " + pagingWhere + @") AS R
FULL JOIN (SELECT MAX(_RowNum) _RowCount FROM Paged) AS C ON
C._RowCount = -1
ORDER BY _RowCount DESC
");
}
return(sb.ToString());
}
static void TestAdapter(SQAdapter adp, List <string[]> data)
{
SQTable tMake, tModel, tBodyType, tTrans, tVehicle;
#region Create Tables
SQTable[] tables = new SQTable[]
{
tMake = new SQTable()
{
Name = "Make",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "MK_ID", DataType = SQDataTypes.Int64, IsPrimary = true
},
new SQColumn()
{
Name = "MK_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tModel = new SQTable()
{
Name = "Model",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "MD_ID", DataType = SQDataTypes.Int64, IsPrimary = true
},
new SQColumn()
{
Name = "MD_MKID", DataType = SQDataTypes.Int64
},
new SQColumn()
{
Name = "MD_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tBodyType = new SQTable()
{
Name = "BodyType",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "BT_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
},
new SQColumn()
{
Name = "BT_Name", DataType = SQDataTypes.String, Length = 250
},
new SQColumn()
{
Name = "BT_Dummy", DataType = SQDataTypes.String, Length = 36
}
}
},
tTrans = new SQTable()
{
Name = "TransmissionType",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "TR_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
},
new SQColumn()
{
Name = "TR_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tVehicle = new SQTable()
{
Name = "TestedVehicle",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "TV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
},
new SQColumn()
{
Name = "TV_VIN", DataType = SQDataTypes.String, Length = 25
},
new SQColumn()
{
Name = "TV_MDID", DataType = SQDataTypes.Int64
},
new SQColumn()
{
Name = "TV_BTID", DataType = SQDataTypes.String, Length = 20
},
new SQColumn()
{
Name = "TV_TRID", DataType = SQDataTypes.String, Length = 20
},
new SQColumn()
{
Name = "TV_Year", DataType = SQDataTypes.Int32
}
}
}
};
Log.Info("------------------------------------------------------");
Log.Info("Creating Tables");
foreach (SQTable t in tables.Reverse <SQTable>())
{
if (adp.GetTable(t.Name) != null)
{
Log.Info("Table '" + t.Name + "' exists. Deleting...");
Log.Info("SQL: " + adp.WriteRemoveTable(t.Name));
adp.RemoveTable(t.Name);
}
Log.Info("Creating table '" + t.Name + "'");
Log.Info("SQL: " + adp.WriteCreateTable(t));
adp.CreateTable(t);
}
#endregion
#region Add Foreign Keys
Log.Info("------------------------------------------------------");
Log.Info("Adding Foreign Keys");
Log.Info("Creating Foreign Key Model -> Make");
adp.AddForeignKey(tModel.GetColumnByName("MD_MKID"), tMake.GetColumnByName("MK_ID"));
Log.Info("Creating Foreign Key Vehicle -> Model");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_MDID"), tModel.GetColumnByName("MD_ID"));
Log.Info("Creating Foreign Key Vehicle -> Body");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_BTID"), tBodyType.GetColumnByName("BT_ID"));
Log.Info("Creating Foreign Key Vehicle -> Transmission");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_TRID"), tTrans.GetColumnByName("TR_ID"));
#endregion
#region Import Data
Log.Info("------------------------------------------------------");
Log.Info("Importing Data");
List <string> makeIDs = new List <string>();
List <string> modelIDs = new List <string>();
List <string> bodyIDs = new List <string>();
List <string> transIDs = new List <string>();
string varMakeID = adp.CreateVariable("MakeID");
string varMake = adp.CreateVariable("Make");
string varModelID = adp.CreateVariable("ModelID");
string varModel = adp.CreateVariable("Model");
string varTransID = adp.CreateVariable("TransID");
string varTrans = adp.CreateVariable("Trans");
string varBodyID = adp.CreateVariable("BodyID");
string varBody = adp.CreateVariable("Body");
string varVIN = adp.CreateVariable("VIN");
string varYear = adp.CreateVariable("Year");
string varDummy = adp.CreateVariable("Dummy");
SQInsertQuery iMake = new SQInsertQuery()
{
Table = new SQAliasableObject(tMake.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("MK_ID", varMakeID),
new SQSetQueryPair("MK_Name", varMake)
},
Parameters = new List <SQParameter> {
new SQParameter(varMakeID, ""), new SQParameter(varMake, "")
}
};
SQInsertQuery iModel = new SQInsertQuery()
{
Table = new SQAliasableObject(tModel.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("MD_ID", varModelID),
new SQSetQueryPair("MD_Name", varModel),
new SQSetQueryPair("MD_MKID", varMakeID)
},
Parameters = new List <SQParameter> {
new SQParameter(varModelID, ""), new SQParameter(varModel, ""), new SQParameter(varMakeID, "")
}
};
SQInsertQuery iBodyType = new SQInsertQuery()
{
Table = new SQAliasableObject(tBodyType.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("BT_ID", varBodyID),
new SQSetQueryPair("BT_Name", varBody),
new SQSetQueryPair("BT_Dummy", varDummy)
},
Parameters = new List <SQParameter> {
new SQParameter(varBodyID, ""), new SQParameter(varBody, ""), new SQParameter(varDummy, "")
}
};
SQInsertQuery iTrans = new SQInsertQuery()
{
Table = new SQAliasableObject(tTrans.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("TR_ID", varTransID),
new SQSetQueryPair("TR_Name", varTrans)
},
Parameters = new List <SQParameter> {
new SQParameter(varTransID, ""), new SQParameter(varTrans, "")
}
};
SQInsertQuery iVehicle = new SQInsertQuery()
{
Table = new SQAliasableObject(tVehicle.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("TV_VIN", varVIN),
new SQSetQueryPair("TV_MDID", varModelID),
new SQSetQueryPair("TV_BTID", varBodyID),
new SQSetQueryPair("TV_TRID", varTransID),
new SQSetQueryPair("TV_Year", varYear)
},
Parameters = new List <SQParameter> {
new SQParameter(varVIN, ""),
new SQParameter(varModelID, ""),
new SQParameter(varBodyID, ""),
new SQParameter(varTransID, ""),
new SQParameter(varYear, "")
}
};
Log.Info("------------------------------------------------------");
Log.Info("Testing Inserts...");
Log.Info("Make SQL: " + iMake.Write(adp));
Log.Info("Model SQL: " + iModel.Write(adp));
Log.Info("BodyType SQL: " + iBodyType.Write(adp));
Log.Info("Transmission SQL: " + iTrans.Write(adp));
Log.Info("TestedVehicle SQL: " + iVehicle.Write(adp));
int vehicleCount = 0;
SQTransaction trn = adp.OpenTransaction();
foreach (string[] dataline in data)
{
string makeID = dataline[2];
string make = dataline[3];
// model id's are unique with respect to the make
string modelID = makeID + dataline[4];
string model = dataline[5];
string transID = dataline[14];
string trans = dataline[15];
string bodyID = dataline[8];
string body = dataline[9];
string VIN = dataline[10];
string year = dataline[6];
int numYear;
// if there's no year, it's not a valid vehicle. skip this line
if (!int.TryParse(year, out numYear) || numYear <= 0)
{
continue;
}
long numMakeID = Convert.ToInt64(makeID.TrimStart('0'));
long numModelID = Convert.ToInt64(modelID.TrimStart('0'));
if (!makeIDs.Contains(makeID))
{
makeIDs.Add(makeID);
iMake.Parameters[0].Value = numMakeID;
iMake.Parameters[1].Value = make;
iMake.Execute(trn);
}
if (!modelIDs.Contains(modelID))
{
modelIDs.Add(modelID);
iModel.Parameters[0].Value = numModelID;
iModel.Parameters[1].Value = model;
iModel.Parameters[2].Value = numMakeID;
iModel.Execute(trn);
}
if (!transIDs.Contains(transID))
{
transIDs.Add(transID);
iTrans.Parameters[0].Value = transID;
iTrans.Parameters[1].Value = trans;
iTrans.Execute(trn);
}
if (!bodyIDs.Contains(bodyID))
{
bodyIDs.Add(bodyID);
iBodyType.Parameters[0].Value = bodyID;
iBodyType.Parameters[1].Value = body;
iBodyType.Parameters[2].Value = Guid.NewGuid().ToString();
iBodyType.Execute(trn);
}
iVehicle.Parameters[0].Value = VIN;
iVehicle.Parameters[1].Value = numModelID;
iVehicle.Parameters[2].Value = bodyID;
iVehicle.Parameters[3].Value = transID;
iVehicle.Parameters[4].Value = numYear;
long id = iVehicle.ExecuteReturnID <Int64>(trn);
vehicleCount++;
if (vehicleCount > 0 && vehicleCount % 500 == 0)
{
Log.Info("Records Inserted: " + (vehicleCount + bodyIDs.Count + transIDs.Count + modelIDs.Count + makeIDs.Count));
}
}
trn.Commit();
Log.Info("------------------------------------------------------");
Log.Info(string.Format(@"Insert Test Complete.
Makes: {0};
Models: {1};
BodyTypes: {2};
TransmissionTypes: {3};
TestedVehicle: {4}; ", makeIDs.Count, modelIDs.Count, bodyIDs.Count, transIDs.Count, vehicleCount));
#endregion
Log.Info("------------------------------------------------------");
Log.Info("Select Test");
SQSelectQuery q = new SQSelectQuery()
{
From = new SQFromClause(
new SQFromTable(tVehicle.Name),
new SQFromTable(tModel.Name),
new SQFromTable(tTrans.Name),
new SQFromTable(tBodyType.Name),
new SQFromTable(tMake.Name)
),
Columns = new List <SQAliasableObject> {
new SQAliasableObject("Model.*, make.*")
},
Condition = new SQConditionGroup(
new SQCondition("MD_ID", SQRelationOperators.Equal, "TV_MDID")
.And("BT_ID", SQRelationOperators.Equal, "TV_BTID")
.And("MD_MKID", SQRelationOperators.Equal, "MK_ID")
.And("TV_TRID", SQRelationOperators.Equal, "TR_ID"))
};
Log.Info("Selecting with joinless joins types");
Log.Info("SQL: " + q.Write(adp));
DataTable dt = new DataTable();
SQSelectResult res = q.Execute(adp);
int count = 0;
while (res.Reader.Read())
{
count++;
}
res.Close();
Log.Info("Rows Found: " + count);
Log.Info("------------------------------------------------------");
Log.Info("Rename Column");
Log.Info("Rename BT_Dummy to BT_DummyOK");
SQColumn dummyColumn = tBodyType.GetColumnByName("BT_Dummy");
string oldname = dummyColumn.Name;
dummyColumn.Name = "BT_DummyOK";
Log.Info("SQL: " + adp.WriteRenameColumn(dummyColumn, oldname));
adp.RenameColumn(dummyColumn, oldname);
Log.Info("------------------------------------------------------");
Log.Info("Remove Column");
Log.Info("Removing column BT_DummyOK");
Log.Info("SQL: " + adp.WriteRemoveColumn(dummyColumn));
adp.RemoveColumn(dummyColumn);
Log.Info("------------------------------------------------------");
Log.Info("Insert From Test");
SQTable tVehicleList = new SQTable()
{
Name = "SimpleVehicle",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "SV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
},
new SQColumn()
{
Name = "SV_VIN", DataType = SQDataTypes.String, Length = 25
},
new SQColumn()
{
Name = "SV_Year", DataType = SQDataTypes.Int32
},
new SQColumn()
{
Name = "SV_Make", DataType = SQDataTypes.String, Length = 250
},
new SQColumn()
{
Name = "SV_Model", DataType = SQDataTypes.String, Length = 250
}
}
};
Log.Info("Create new table");
if (adp.GetTable(tVehicleList.Name) != null)
{
Log.Info("Table '" + tVehicleList.Name + "' exists. Deleting...");
Log.Info("SQL: " + adp.WriteRemoveTable(tVehicleList.Name));
adp.RemoveTable(tVehicleList.Name);
}
Log.Info("Creating table '" + tVehicleList.Name + "'");
Log.Info("SQL: " + adp.WriteCreateTable(tVehicleList));
adp.CreateTable(tVehicleList);
SQInsertFromQuery insertFrom = new SQInsertFromQuery(new SQAliasableObject(tVehicleList.Name),
new string[] { "SV_VIN", "SV_Year", "SV_Make", "SV_Model" },
new string[] { "TV_VIN", "TV_Year", "MK_Name", "MD_Name" })
{
From = new SQFromClause(
new SQFromTable(tVehicle.Name)
{
Join = new SQJoin(tModel.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("TV_MDID", SQRelationOperators.Equal, "MD_ID"),
Join = new SQJoin(tMake.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("MD_MKID", SQRelationOperators.Equal, "MK_ID")
}
}
}),
Condition = new SQCondition("TV_Year", SQRelationOperators.GreaterThanOrEqual, varYear),
Parameters = new List <SQParameter> {
new SQParameter(varYear, 1990)
}
};
Log.Info("Do Insert From");
Log.Info("SQL: " + insertFrom.Write(adp));
insertFrom.Execute(adp);
q = new SQSelectQuery()
{
Columns = new List <SQAliasableObject> {
new SQAliasableObject(SQAggregates.COUNT.Create(adp, "*"), "RecordCount")
},
From = new SQFromClause(new SQFromTable(tVehicleList.Name))
};
Log.Info("Get record count for " + tVehicleList.Name);
Log.Info("SQL: " + q.Write(adp));
res = q.Execute(adp);
if (res.Reader.Read())
{
Log.Info("Count: " + res.Reader.GetValue(0));
}
res.Close();
Log.Info("------------------------------------------------------");
Log.Info("Rollback Test");
trn = adp.OpenTransaction();
SQDeleteQuery delete = new SQDeleteQuery()
{
DeleteTable = new SQAliasableObject(tVehicleList.Name),
Condition = new SQCondition("SV_Year", SQRelationOperators.LessThan, varYear),
Parameters = new List <SQParameter> {
new SQParameter(varYear, 2000)
}
};
Log.Info("Deleting data from " + tVehicleList.Name);
Log.Info("SQL: " + delete.Write(adp));
delete.Execute(trn);
res = q.Execute(trn);
if (res.Reader.Read())
{
Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
}
res.Close();
Log.Info("Rolling Back");
trn.RollBack();
res = q.Execute(adp);
if (res.Reader.Read())
{
Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
}
res.Close();
}