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);
}
static void Main(string[] args)
{
// Get all data out of the vehicles file
Log.Info("Parsing import data...");
TextFieldParser txt = new TextFieldParser(AppDomain.CurrentDomain.BaseDirectory + "\\Test Data\\veh.psv");
txt.Delimiters = new string[] { "|" };
List <string[]> vehicleData = new List <string[]>();
txt.ReadFields();
while (!txt.EndOfData)
{
vehicleData.Add(txt.ReadFields());
}
Log.Info("Finished parsing import data.");
Log.Info("------------------------------------------------------");
Log.Info("Begin Testing Adapters");
Log.Info("------------------------------------------------------");
try
{
SQAdapter[] adapters = new SQAdapter[]
{
new SQLiteAdapter(Settings.Default.SQLiteConn),
new SQLServerAdapter(Settings.Default.SQLServerConn),
new MySQLAdapter(Settings.Default.MySQLConn)
};
foreach (SQAdapter adp in adapters)
{
try
{
Log.Info("------------------------------------------------------");
Log.Info("Testing adapter type: " + adp.GetType().Name);
Log.Info("------------------------------------------------------");
TestAdapter(adp, vehicleData);
}
catch (Exception ex)
{
Log.Error("An error occurred while testing adapter " + adp.GetType().Name + "\r\n" + ex);
}
Log.Info("------------------------------------------------------");
Log.Info("Finished testing adapter: " + adp.GetType().Name);
Log.Info("------------------------------------------------------");
}
}
catch (Exception ex)
{
Log.Error(ex);
}
Log.Info("------------------------------------------------------");
Log.Info("Adapter Testing Complete");
Log.Info("------------------------------------------------------");
}
public static SchemaManager GetMgr(SQAdapter adp)
{
if (!_Mgrs.ContainsKey(adp))
{
_Mgrs.TryAdd(adp, new SchemaManager(adp));
}
return(_Mgrs[adp]);
}
public ActionResult CreateScript()
{
string def = Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
dg.Relate();
SQAdapter adp = (Request["Lang"] == "MySQL") ? (SQAdapter) new MySQLAdapter()
: (Request["Lang"] == "SQL Server") ? (SQAdapter) new SQLServerAdapter()
: (Request["Lang"] == "PostgreSQL") ? (SQAdapter) new PostgreSQLAdapter()
: (SQAdapter) new SQLiteAdapter();
StringBuilder sb = new StringBuilder();
foreach (JSTable table in dg.Tables)
{
SQTable t = table.GetTable();
sb.AppendLine("-- Create Table: " + t.Name);
sb.AppendLine("--------------------------------------------------------------------------------");
sb.Append(adp.WriteCreateTable(t));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
foreach (JSFKey fk in dg.FKeys)
{
sb.AppendLine(string.Format("-- Create Foreign Key: {0}.{1} -> {2}.{3}", fk.From.GetTable().Name, fk.From.GetColumn().Name, fk.To.GetTable().Name, fk.To.GetColumn().Name));
sb.Append(adp.WriteAddForeignKey(fk.From.GetColumn().GetColumn(), fk.To.GetColumn().GetColumn()));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
return(Content(sb.ToString()));
}
private void HandleRequest(string command)
{
switch (command)
{
case "PreserveSession":
{
Respond(User != null ? "true" : "false");
}
break;
case "GetCurrentUser":
{
Respond(User);
}
break;
case "GetDiagrams":
{
List <DGDiagram> dgs = DGDiagram.ByUserID(UserID);
Respond(dgs);
}
break;
case "GetSharedDiagrams":
{
if (UserID != 0)
{
Respond(DGVSharedDiagram.ByEmail(User.Name));
}
else
{
Respond("ERROR:Not Logged In");
}
}
break;
case "GetSharing":
{
if (UserID != 0)
{
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg != null && dg.UserID == UserID)
{
Respond(DGShare.ByDiagram(diagramID));
}
}
}
break;
case "ShareDiagram":
{
if (UserID != 0)
{
string email = _Ctx.Request["Email"];
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg.UserID == UserID)
{
dg.Share(email, DGSharePermisson.View);
Respond(DGShare.ByDiagram(diagramID));
}
else
{
Respond("ERROR:Permission Denied");
}
}
}
break;
case "UnshareDiagram":
{
if (UserID != 0)
{
string email = _Ctx.Request["Email"];
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg.UserID == UserID)
{
dg.Unshare(email);
Respond(DGShare.ByDiagram(diagramID));
}
}
}
break;
case "SaveDiagram":
{
string def = _Ctx.Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
DGDiagram diagram = DGDiagram.ByUserID(UserID).ByID(dg.ID);
if (diagram == null)
{
diagram = new DGDiagram();
diagram.UserID = UserID;
}
if (diagram.UserID != UserID)
{
Respond("ERROR:Permission Denied");
}
else
{
diagram.Name = dg.Name;
diagram.PrimarySnapshot.DiagramData = def;
diagram.Save();
Respond(diagram.ID);
}
}
break;
case "TogglePublicAccess":
{
DGDiagram dg = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (dg != null)
{
if (dg.UserID == UserID)
{
dg.AllowPublicAccess = !dg.AllowPublicAccess;
dg.Save();
Respond(dg.AllowPublicAccess);
}
else
{
Respond("ERROR:Permission Denied");
}
}
}
break;
case "GetDiagram":
{
if (_Ctx.Request["ID"] == "WelcomeDiagram")
{
string email = ConfigurationManager.AppSettings["WelcomeDiagram Email"];
DGUser user = DGUser.ByName(email);
if (user != null)
{
DGDiagram dg = DGDiagram.ByUserID(user.ID).ByName(ConfigurationManager.AppSettings["WelcomeDiagram Name"]);
Respond(dg);
}
}
else
{
DGDiagram dg = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (dg != null)
{
if (dg.UserID == UserID ||
dg.AllowPublicAccess ||
DGShare.ByEmailAndDiagram(User.Name, dg.ID).Count > 0)
{
Respond(dg);
}
}
}
}
break;
case "DeleteDiagram":
{
DGDiagram dg = DGDiagram.ByUserID(UserID).ByID(Convert.ToInt64(_Ctx.Request["ID"]));
dg.Delete();
Respond("OK");
}
break;
case "AddSnapshot":
{
string def = _Ctx.Request["Snapshot"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
// TODO: check user
DGDiagram diagram = DGDiagram.ByUserID(UserID).ByID(Convert.ToInt64(_Ctx.Request["DGID"]));
if (diagram != null)
{
DGSnapshot snapshot = new DGSnapshot()
{
Name = _Ctx.Request["Name"],
DiagramID = diagram.ID,
IsDefault = false,
DiagramData = def
};
snapshot.Save();
Respond(diagram.ID);
}
}
break;
case "GetSnapshots":
{
DGDiagram diagram = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["DGID"])) ?? new DGDiagram();
if (diagram.UserID == UserID ||
DGShare.ByEmailAndDiagram(User.Name, diagram.ID).Count > 0)
{
Respond(DGSnapshot.ByDiagramID(diagram.ID));
}
}
break;
case "DeleteSnapshot":
{
DGSnapshot snapshot = DGSnapshot.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (snapshot != null)
{
if (snapshot.Diagram.UserID == User.ID)
{
snapshot.Delete();
Respond("OK");
}
else
{
Respond("ERROR:Permission Denied");
}
}
else
{
Respond("ERROR:Bad Input ID");
}
}
break;
case "LoadSnapshot":
{
DGSnapshot snapshot = DGSnapshot.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (snapshot.Diagram.UserID == UserID ||
DGShare.ByEmailAndDiagram(User.Name, snapshot.Diagram.ID).Count > 0)
{
Respond(snapshot);
}
else
{
Respond("ERROR:Permission Denied");
}
}
break;
case "CreateScript":
{
string def = _Ctx.Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
dg.Relate();
SQAdapter adp = (_Ctx.Request["Lang"] == "MySQL") ? (SQAdapter) new MySQLAdapter()
: (_Ctx.Request["Lang"] == "SQL Server") ? (SQAdapter) new SQLServerAdapter()
: (_Ctx.Request["Lang"] == "PostgreSQL") ? (SQAdapter) new PostgreSQLAdapter()
: (SQAdapter) new SQLiteAdapter();
StringBuilder sb = new StringBuilder();
foreach (JSTable table in dg.Tables)
{
SQTable t = table.GetTable();
sb.AppendLine("-- Create Table: " + t.Name);
sb.AppendLine("--------------------------------------------------------------------------------");
sb.Append(adp.WriteCreateTable(t));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
foreach (JSFKey fk in dg.FKeys)
{
sb.AppendLine(string.Format("-- Create Foreign Key: {0}.{1} -> {2}.{3}", fk.From.GetTable().Name, fk.From.GetColumn().Name, fk.To.GetTable().Name, fk.To.GetColumn().Name));
sb.Append(adp.WriteAddForeignKey(fk.From.GetColumn().GetColumn(), fk.To.GetColumn().GetColumn()));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
Respond(sb.ToString());
}
break;
case "DoChangeScript":
{
}
break;
}
}
public ORMSelect(SQAdapter adp)
{
_Schema = SchemaManager.GetMgr(adp);
}
public DataContext(SQAdapter adp)
{
Adp = adp;
Schema = SchemaManager.GetMgr(adp);
}
private SchemaManager(SQAdapter apd)
{
Adp = apd;
_SchemaTables = new Dictionary <Type, SchemaTable>();
}
public static string Create(this SQFunctions function, SQAdapter adp, params string[] parameters)
{
return(adp.CreateFunction(function, parameters));
}
public static string Create(this SQAggregates aggregate, SQAdapter adp, params string[] parameters)
{
return(adp.CreateAggregate(aggregate, parameters));
}
protected internal SQTransaction(SQAdapter adp, DbTransaction trans)
{
_Adapter = adp;
_Transaction = trans;
}
private SQAdapter ProvideAdapter()
{
return(_Adp ?? (_Adp = new SQLServerAdapter(ConfigurationManager.AppSettings["SqlServerConn"])));
}
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();
}
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();
}
