public override string WriteRenameColumn(SQColumn column, string oldName)
{
SQTable table = GetTable(column.Table.Name);
// get list of columns
List <string> newColumns = new List <string>();
List <string> oldColumns = new List <string>();
foreach (SQColumn col in table.Columns)
{
if (oldName.ToUpper() == col.Name.ToUpper())
{
newColumns.Add(column.Name);
oldColumns.Add(col.Name);
}
else
{
newColumns.Add(col.Name);
oldColumns.Add(col.Name);
}
}
table.GetColumnByName(oldName).Name = column.Name;
return(WriteUpdateTable(table, newColumns, oldColumns));
}
public override SQTable GetTable(string name)
{
SQLiteConnection sqlcon = new SQLiteConnection(ConnectionString);
sqlcon.Open();
DataTable dt = sqlcon.GetSchema("Columns", new string[] { null, null, name, null });
DataTable fks = sqlcon.GetSchema("ForeignKeys", new string[] { null, null, name, null });
sqlcon.Close();
SQTable res = null;
if (dt.Rows.Count > 0)
{
res = new SQTable();
res.Name = name;
foreach (DataRow row in dt.Rows)
{
SQDataTypes dataType = GetTypeFromName(row["DATA_TYPE"].ToString());
int precision = row["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]);
res.Columns.Add(new SQColumn_SQLite()
{
Name = row["COLUMN_NAME"].ToString(),
DataType = dataType != SQDataTypes.Int32 ? dataType : precision == 19 ? SQDataTypes.Int64 : SQDataTypes.Int32,
IsIdentity = row["AUTOINCREMENT"].Equals(true),
IsPrimary = row["PRIMARY_KEY"].Equals(true),
DefaultValue = row["COLUMN_DEFAULT"],
Length = Convert.ToInt64(row["CHARACTER_MAXIMUM_LENGTH"]),
Nullable = row["IS_NULLABLE"].Equals(true),
Precision = precision,
Scale = row["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"])
});
}
foreach (DataRow row in fks.Rows)
{
((SQColumn_SQLite)res.GetColumnByName(row["FKEY_FROM_COLUMN"].ToString())).ForeignKey = new SQColumn()
{
Name = row["FKEY_TO_COLUMN"].ToString(),
Table = new SQTable()
{
Name = row["FKEY_TO_TABLE"].ToString()
}
};
}
}
return(res);
}
public override string WriteRemoveColumn(SQColumn column)
{
// add foreign key to from property
SQTable table = GetTable(column.Table.Name);
table.Columns.Remove(table.GetColumnByName(column.Name));
// get list of columns
List <string> columns = new List <string>();
foreach (SQColumn col in table.Columns)
{
columns.Add(col.Name);
}
return(WriteUpdateTable(table, columns, columns));
}
public override string WriteAddForeignKey(SQColumn from, SQColumn to)
{
// add foreign key to from property
SQTable table = GetTable(from.Table.Name);
((SQColumn_SQLite)table.GetColumnByName(from.Name)).ForeignKey = to;
// get list of columns
List <string> columns = new List <string>();
foreach (SQColumn col in table.Columns)
{
columns.Add(col.Name);
}
return(WriteUpdateTable(table, columns, columns));
}
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();
}
