private void DoInsertCommand(SQLiteConnection sqlcon, SQLInsertTable table)
{
CommandPerformed = $"{table.GetSqlWithParameters()}{Environment.NewLine}{table.GetParamList().ToString()}";
SQLPerformed = table.GetSql();
try
{
SqliteConnection db = sqlcon.GetConnection();
using (SqliteCommand command = db.CreateCommand())
{
command.CommandText = table.GetSqlWithParameters();
List <SqliteParameter> Params = GetCommandParameters(table.GetParamList());
if (Params.Count > 0)
{
foreach (var p in Params)
{
command.Parameters.Add(p);
}
}
command.ExecuteNonQuery();
}
Success = true;
}
catch
{
Success = false;
}
}
public SQLiteInsert(SQLiteConnection sqlcon, SQLInsertTable table)
{
DoInsertCommand(sqlcon, table);
}
public SQLiteInsert(SQLiteConnection sqlcon, string TableName, SQLVarsVals Fields)
{
SQLInsertTable table = new SQLInsertTable(TableName, Fields);
DoInsertCommand(sqlcon, table);
}
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();
}
