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