/// <summary>
/// This constructor creates a table with the connection that is passed to it. The name of the table is passed into it
/// A long with a List of Fields, their type and whether or not the fields are nullable, the primary key and if they should
/// be auto incredmented. In addition the length that is to be saved for each field for those fieldtypes that need it.
/// All the lists need to be the same size.
/// </summary>
/// <param name="sqlcon">This is the connection to the SQLite DB</param>
/// <param name="tablename">The name of the table to create</param>
/// <param name="Fields">A list of fields to create</param>
/// <param name="FieldType">A list of the types the fields are</param>
/// <param name="NotNullable">A list of whether the field is nullable</param>
/// <param name="Length">A list of the length of the different fields</param>
/// <param name="IsPrimaryKey">A list of whether the field is a primary key</param>
/// <param name="AutoIncrement">A list of whether a field should be autoincremented</param>
public SQLiteCreate(SQLiteConnection sqlcon, string tablename, List <string> Fields, List <string> FieldType,
List <bool> NotNullable, List <string> Length, List <bool> IsPrimaryKey, List <bool> AutoIncrement)
{
try
{
SQLCreateTable table = new SQLCreateTable(tablename);
for (int index = 0; index < Fields.Count; index++)
{
table.AddField(Fields[index], FieldType[index], Int32.Parse(Length[index]), NotNullable[index],
AutoIncrement[index], IsPrimaryKey[index]);
}
DoCreateCommand(sqlcon, table.GetSql());
}
catch
{ }
}
/// <summary>
/// This constructor creates a table with the connection with the table name and also a list of the fields with their
/// values.
/// The SQLiteField name contains all the information you need for the Fields. Such as their type, nullable, length,
/// is it the primary key, and to autoincrement them
/// </summary>
/// <param name="sqlcon">This is the connection to the SQLite DB</param>
/// <param name="tablename">The name of the table to create</param>
/// <param name="Fields">A variable that contains a class that contains all the information about the fields to be
/// created</param>
public SQLiteCreate(SQLiteConnection sqlcon, string tablename, List <SQLField> Fields)
{
SQLCreateTable table = new SQLCreateTable(tablename, Fields);
DoCreateCommand(sqlcon, table.GetSql());
}
/// <summary>
/// This constructor creates a table that has been set up with the SQLiteTable variable. It contains all the information
/// including the table name, the fields and their types.
/// </summary>
/// <param name="sqlcon">This is the connection to the SQlite DB</param>
/// <param name="table">This contains a class of everything concerning the table to create</param>
public SQLiteCreate(SQLiteConnection sqlcon, SQLCreateTable table)
{
DoCreateCommand(sqlcon, table.GetSql());
}
public virtual string SerializeCreateTable(SQLCreateTable createTable)
{
if (String.IsNullOrEmpty(createTable.Name))
throw new Exceptions.DatabaseObjectsException("TableName has not been set.");
var tokens = new TokenSerializer();
tokens.Add("CREATE TABLE");
tokens.Add(SerializeIdentifier(createTable.Name));
tokens.Add("(" + SerializeTableFields(createTable.Fields, includeColumnModifier: false) + ")");
return tokens.ToString();
}
public Result CreateTable(SQLCreateTable sql)
{
return(CreateTable(sql.TbName, sql.ColumnDefs));
}
static void Main(string[] args)
{
//create a sqiite data connection
SQLiteConnection db = new SQLiteConnection();
//create a table
SQLCreateTable table = new SQLCreateTable("Customers");
table.AddField("ID", "INTEGER", 0, false, true, true);
table.AddField("Name", "VARCHAR", 30);
table.AddField("Address", "VARCHAR", 30);
table.AddField("City", "VARCHAR", 30);
table.AddField("State", "VARCHAR", 2);
table.AddField("Zip", "VARCHAR", 7);
//See the SQL that will be generated
Console.WriteLine("Creating Customers Table");
//Perform the Create
SQLiteCreate create = new SQLiteCreate(db, table);
Console.WriteLine(create.GetSuccess());
Console.WriteLine(create.GetCommmandPerformed());
//Create values to insert into the Customers table
SQLInsertTableMultiValues insertvalues = new SQLInsertTableMultiValues("Customers");
insertvalues.SetFields("Name", "Address", "City", "State", "Zip");
insertvalues.AddValues("Bob Moore", "123 No where", "NoneTown", "GH", "14538-3213");
insertvalues.AddValues("Jack Friar", "342 Main St", "Hopetown", "JI", "46753-3234");
// See the SQL that will be generated
Console.WriteLine("Inserting Values into the Customers Table");
//Perform the insert into the SQL db
SQLiteInsert insert = new SQLiteInsert(db, insertvalues);
Console.WriteLine(insert.GetSuccess());
Console.WriteLine(insert.GetCommmandPerformed());
Console.WriteLine(insert.GetSQLPerfomed());
//Select all rows in the Customers table
SQLiteSelect select = new SQLiteSelect(db);
select.DoQuery("Customers", "*");
// Access the fields selected in the query
Console.WriteLine($"Fields: {select.GetFields()}");
//Access the results of the query
SqliteDataReader reader = select.GetReader();
while (reader.Read())
{
Console.WriteLine($"{reader["ID"]} {reader["Name"]} {reader["Address"]} {reader["City"]} {reader["State"]} {reader["Zip"]}");
}
//Get the number of rows in the query
Console.WriteLine($"Total Rows: {select.Count()}");
//insert a single row with SQLInsertTable
SQLInsertTable inserttable = new SQLInsertTable("Customers");
inserttable.Add("Name", "Jack Waler");
inserttable.Add("Address", "545 Tracker Street");
inserttable.Add("City", "Walpum");
inserttable.Add("State", "BO");
inserttable.Add("Zip", "13422-222");
Console.WriteLine("Inserting another record into the customers table");
insert = new SQLiteInsert(db, inserttable);
//Insert a single row more compact with SQLInsertTable
inserttable = new SQLInsertTable("Customers");
inserttable.Add(new SQLVarVal("Name", "Henry Thomas"), new SQLVarVal("Address", "34 Downing Street"), new SQLVarVal("City", "Kiltown"), new SQLVarVal("State", "YI"), new SQLVarVal("Zip", "45430-2232"));
Console.WriteLine("Inserting another record into the customers table");
insert = new SQLiteInsert(db, inserttable);
//Get result in the form of a DataTable
DataTable dt = select.GetTable();
Console.WriteLine(dt.Rows.Count);
//update a field with SQLUpdateTable and SQLiteUpdate
SQLUpdateTable updatetable = new SQLUpdateTable("Customers", new SQLVarVal("State", "HI"), new SQLWhereVar("State", "JI"));
Console.WriteLine("Changing state in customers table");
Console.WriteLine(updatetable.GetSql());
SQLiteUpdate update = new SQLiteUpdate(db, updatetable);
//passing normal SQL rather than building it in code is possible
SQLiteSelect select2 = new SQLiteSelect(db);
select2.DoQuery("Select ID, Name, State FROM Customers");
//Check fields of the query
Console.WriteLine($"Fields: {select2.GetFields()}");
Console.WriteLine();
//Access the results of the query
reader = select2.GetReader();
//Display the results
while (reader.Read())
{
Console.WriteLine($"{reader["ID"]} {reader["Name"]} {reader["State"]}");
}
//get the row count
Console.WriteLine($"Total Rows: {select2.Count()}");
//How to do a Special function with Select like Count, or Sum, or ect...
SQLSpecialSelectTable selecttable = new SQLSpecialSelectTable("Customers");
selecttable.AddSelectField("Name");
selecttable.AddWhereField(new SQLWhereVar("", "Name", "B%", "LIKE"));
selecttable.MakeCount();
select2 = new SQLiteSelect(db);
Console.WriteLine(selecttable.GetSql());
select2.DoQuery(selecttable);
reader = select2.GetReader();
reader.Read();
Console.WriteLine($"The number of matching records is {reader[0]}");
//Can also get the count with this method
SQLSelectTable selecttable2 = new SQLSelectTable("Customers");
selecttable2.AddSelectField("Name");
selecttable2.AddWhereField(new SQLWhereVar("", "Name", "B%", "LIKE"));
Console.WriteLine(selecttable2.GetSql());
select2 = new SQLiteSelect(db);
select2.DoQuery(selecttable2);
int count = select2.Count();
Console.WriteLine($"The number of matching records is {count}");
//SQLSelectIntoTable tab = new SQLSelectIntoTable(selecttable2, "destin", "extdb");
//Console.WriteLine(tab.GetSql());
//close the db connection
db.Close();
}
