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();
}