private static void HandleGroups(List <Group> groups, SQLiteConnection conn) { foreach (Group g in groups) { string sql = string.Format("SELECT GroupID, Name FROM DeviceGroups WHERE GroupID = {0}", g.id); using (SQLiteCommand command = new SQLiteCommand(sql, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.Read()) { if (reader.GetString(1) != g.name) { Updater updater = new Updater("DeviceGroups", string.Format("GroupID = {0}", g.id), conn); updater.Set("Name", g.name.Trim(), true); updater.Execute(); } } else { Inserter insert = new Inserter("DeviceGroups", conn); insert.Set("GroupID", g.id); insert.Set("Name", g.name.Trim(), true); insert.Execute(); } } } }
public void Set(string path, string value, SQLiteConnection conn) { ILog log = LogManager.GetLogger(typeof(Attribute)); try { log.DebugFormat("Setting attribute '{0}' to '{1}'", path, value); using (SQLiteTransaction t = conn.BeginTransaction()) { Changer change = new Deleter("Attributes", $"Path = '{path}'", conn); change.Execute(); change = new Inserter("Attributes", conn); change.Set("Path", path, true); change.Set("Value", value, false); change.Execute(); t.Commit(); } } catch (Exception ex) { log.Error(ex); } }
/// <summary> /// Used when a new device is being added to the system /// </summary> /// <param name="di">The device being added</param> /// <param name="conn">The DB connection to use</param> private static void AddDevice(DeviceInfo di, DateTimeOffset timestamp, SQLiteConnection conn) { Inserter inserter = new Inserter("Devices", conn); if (di.id >= 0) { inserter.Set("DeviceID", di.id); } inserter.Set("Name", di.name, true); inserter.Set("Type", (int)di.type); inserter.Set("IPAddress", di.ipAddress, true); inserter.Set("Username", di.username, true); SimpleEncryptDecrypt sed = new SimpleEncryptDecrypt(); inserter.Set("Password", sed.Encrypt(di.password), true); inserter.Set("DateActivated", timestamp); if (di.groupID < 0) { inserter.SetNull("GroupID"); } else { inserter.Set("GroupID", di.groupID); } inserter.Execute(); long device_id = conn.LastInsertRowId; Database.AddCollectors(di, device_id, timestamp, conn); }
private void UpdateMostRecentData(long data_id, SQLiteConnection conn) { Changer change = new Deleter("MostRecentDataPerCollector", $"CollectorID = {DCContext.ID.ID}", conn); change.Execute(); change = new Inserter("MostRecentDataPerCollector", conn); change.Set("CollectorID", DCContext.ID.ID); change.Set("DataID", data_id); change.Execute(); }
public long InsertData(SQLiteConnection conn) { Inserter insert = new Inserter("Data", conn); insert.Set("CollectorID", DCContext.ID.ID); insert.Set("Value", Value, false); insert.Set("TimeStamp", Timestamp); insert.Execute(); UpdateMostRecentData(conn.LastInsertRowId, conn); return(conn.LastInsertRowId); }
private static void Insert(SQLiteConnection conn, int count, Dictionary <int, string> dict) { Random r = new Random(); for (int i = 0; i < count; ++i) { int a = r.Next(); string b = Guid.NewGuid().ToString(); Inserter inserter = new Inserter("Temp", conn); inserter.Set("A", a); inserter.Set("B", b, false); inserter.Execute(); dict[a] = b; } }
/// <summary> /// Used to make sure the *Type tables are updated /// </summary> /// <param name="conn">The connection to the database</param> /// <param name="values">The key/value pair that needs to exist in the DB. If there's a missing entry, or the description /// changed, we'll insert or update as appropriate</param> /// <param name="table">The table we're going to be updating</param> /// <param name="type_column">The name of the type column.</param> public static void PopulateTypesTable(this SQLiteConnection conn, Dictionary <int, string> values, string table, string type_column) { ILog log = LogManager.GetLogger(typeof(Extensions)); Dictionary <int, string> db_values = new Dictionary <int, string>(); string select_query = string.Format("SELECT {0}, Description FROM {1};", type_column, table); using (SQLiteCommand command = new SQLiteCommand(select_query, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { db_values[reader.GetInt32(0)] = reader.GetString(1); } } List <int> db_types = new List <int>(db_values.Keys); List <int> types = new List <int>(values.Keys); db_types.ForEach(t => types.Remove(t)); // Whatever's left needs to be added foreach (int type in types) { log.Info($"Inserting {type}=>{values[type]} into {table}"); Inserter inserter = new Inserter(table, conn); inserter.Set(type_column, type); inserter.Set("Description", values[type], false); inserter.Execute(); db_values[type] = values[type]; } // Now compare the existing descriptions with the descriptions in values foreach (int type in values.Keys) { if (values[type] != db_values[type]) { log.Info($"Changing {type}=>{db_values[type]} to {values[type]} in {table}"); Updater updater = new Updater(table, $"{type_column} = {type}", conn); updater.Set("Description", values[type], false); updater.Execute(); } } }
public void ProperlyHandleDates() { using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile())) { Database db = new Database(new Context(fd.Fi)); using (SQLiteConnection conn = db.Connection) { conn.Open(); conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)"); Assert.True(conn.DoesTableExist("Temp")); Dictionary <int, DateTimeOffset> dict = new Dictionary <int, DateTimeOffset>(); Random r = new Random(); int count = 100; for (int i = 0; i < count; ++i) { int a = r.Next(); DateTimeOffset b = new DateTimeOffset(2018, r.Next(1, 12), r.Next(1, 28), r.Next(0, 23), r.Next(0, 59), r.Next(0, 59), r.Next(0, 999), TimeSpan.FromHours(r.Next(-12, 12))); Inserter ins = new Inserter("Temp", conn); ins.Set("A", a); ins.Set("B", b); ins.Execute(); dict[a] = b; } foreach (int a in dict.Keys) { string sql = $"SELECT B FROM Temp WHERE A = {a}"; using (SQLiteCommand command = new SQLiteCommand(sql, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { bool read = reader.Read(); Assert.True(read); if (read) { DateTimeOffset b = DateTimeOffset.Parse(reader.GetString(0)); Assert.Equal(dict[a], b); } } } } } }
public void ProperlyHandleLongs() { using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile())) { Database db = new Database(new Context(fd.Fi)); using (SQLiteConnection conn = db.Connection) { conn.Open(); conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B INTEGER NOT NULL)"); Assert.True(conn.DoesTableExist("Temp")); Dictionary <int, long> dict = new Dictionary <int, long>(); Random r = new Random(); int count = 100; for (int i = 0; i < count; ++i) { int a = r.Next(); long b = r.NextLong(); Inserter ins = new Inserter("Temp", conn); ins.Set("A", a); ins.Set("B", b); ins.Execute(); dict[a] = b; } foreach (int a in dict.Keys) { string sql = $"SELECT B FROM Temp WHERE A = {a}"; using (SQLiteCommand command = new SQLiteCommand(sql, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { bool read = reader.Read(); Assert.True(read); if (read) { long b = reader.GetInt64(0); Assert.Equal(dict[a], b); } } } } } }
/// <summary> /// Create a new Hyper file with a single table and write some data into it. /// </summary> /// <param name="exampleDataDir">Path to the directory with example data.</param> public override void Execute(string exampleDataDir) { Console.WriteLine("EXAMPLE - Insert data into a single tables within a new Hyper file."); // Start the Hyper process with telemetry enabled. using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau)) { // Connect to hyper and create new Hyper file "superstore.hyper". // Replaces file if it already exists when CreateMode.CreateAndReplace is set. using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace)) { // The table is called "Extract" and will be created in the "Extract" schema. // This has historically been the default table name and schema for extracts created by Tableau. TableName extractTable = new TableName("Extract", "Extract"); TableDefinition extractTableDefinition = new TableDefinition(extractTable) .AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable) .AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable) .AddColumn("Segment", SqlType.Text(), Nullability.NotNullable); // Create the schema and the table connection.Catalog.CreateSchema("Extract"); connection.Catalog.CreateTable(extractTableDefinition); // Insert data into the "Extract"."Extract" table using (Inserter inserter = new Inserter(connection, extractTable)) { inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer"); inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate"); inserter.Execute(); } // ExecuteScalarQuery is for executing a query that returns exactly one row with one column long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}"); Console.WriteLine($"Table {extractTable} has a count of {count} rows"); } Console.WriteLine("The connection to the Hyper file has been closed."); } Console.WriteLine("The Hyper process has been shut down."); }
public void NotBeNullable() { using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile())) { Database db = new Database(new Context(fd.Fi)); using (SQLiteConnection conn = db.Connection) { conn.Open(); conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)"); Assert.True(conn.DoesTableExist("Temp")); Inserter i = new Inserter("Temp", conn); i.Set("A", 1); i.SetNull("B"); Assert.Throws <SQLiteException>(() => i.Execute()); } } }
public static void SetValue(string path, string value, bool normalize, DateTimeOffset timestamp, SQLiteConnection conn) { if (string.IsNullOrEmpty(path)) { return; } string existing_value = GetValue(path, conn); if (value != existing_value) { Clear(path, conn); Inserter insert = new Inserter("Configuration", conn); insert.Set("Path", path, true); insert.Set("Value", value ?? string.Empty, normalize); insert.Set("DateAdded", timestamp); insert.Set("IsValid", 1); insert.Execute(); } }
/// <summary> /// Push down computations to Hyper during Insertion using expressions /// </summary> /// <param name="exampleDataDir">Path to the directory with example data.</param> public override void Execute(string exampleDataDir) { Console.WriteLine("EXAMPLE - Push down computations to Hyper during Insertion using Expressions"); // Start the Hyper process with telemetry enabled. using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau)) { // Connect to hyper and create new Hyper file "superstore.hyper". // Replaces file if it already exists when CreateMode.CreateAndReplace is set. using (Connection connection = new Connection(hyper.Endpoint, "orders.hyper", CreateMode.CreateAndReplace)) { // The table is called "Extract" and will be created in the "Extract" schema. // This has historically been the default table name and schema for extracts created by Tableau. TableName extractTable = new TableName("Extract", "Extract"); TableDefinition extractTableDefinition = new TableDefinition(extractTable) .AddColumn("Order ID", SqlType.Int(), Nullability.NotNullable) .AddColumn("Ship Timestamp", SqlType.Timestamp(), Nullability.NotNullable) .AddColumn("Ship Mode", SqlType.Text(), Nullability.NotNullable) .AddColumn("Ship Priority", SqlType.Int(), Nullability.NotNullable); // Create the schema and the table connection.Catalog.CreateSchema("Extract"); connection.Catalog.CreateTable(extractTableDefinition); // Hyper API's Inserter allows users to transform data during insertion. // To make use of data transformation during insertion, the inserter requires the following inputs // 1. The connection to the Hyper instance containing the table. // 2. The table name or table defintion into which data is inserted. // 3. List of Inserter.ColumnMapping. // This list informs the inserter how each column in the target table must be tranformed. // The list must contain all the columns into which data is inserted. // "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table // For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.EscapeName("colB")}) // The column "target_column" contains the product of "colA" and "colB" after successful insertion. // SQL expression string is optional in Inserter.ColumnMapping. // For a column without any transformation (identity transformation) only the column name is required. // For example new Inserter.ColumnMapping("no_data_transformation_column") // 4. Inserter Definition, a list of column definitions for all the input values provided during insertion. // Inserter definition contains the column definition for the values that are inserted. List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>(); inserterDefinition.Add(new TableDefinition.Column("Order ID", SqlType.Int(), Nullability.NotNullable)); inserterDefinition.Add(new TableDefinition.Column("Ship Timestamp Text", SqlType.Text(), Nullability.NotNullable)); inserterDefinition.Add(new TableDefinition.Column("Ship Mode", SqlType.Text(), Nullability.NotNullable)); inserterDefinition.Add(new TableDefinition.Column("Ship Priority Text", SqlType.Text(), Nullability.NotNullable)); // Column 'Order Id' is inserted into "Extract"."Extract" as-is. // Column 'Ship Timestamp' in "Extract"."Extract" of timestamp type is computed from Column 'Ship Timestamp Text' of text type using 'to_timestamp()'. // Column 'Ship Mode' is inserted into "Extract"."Extract" as-is. // Column 'Ship Priority' is "Extract"."Extract" of integer type is computed from Colum 'Ship Priority Text' of text type using 'CASE' statement. string textToTimeStampExpression = $"to_timestamp({Sql.EscapeName("Ship Timestamp Text")}, {Sql.EscapeStringLiteral("YYYY-MM-DD HH24:MI:SS")})"; string shipPriorityAsIntCaseExpression = $"CASE {Sql.EscapeName("Ship Priority Text")}" + $" WHEN {Sql.EscapeStringLiteral("Urgent")} THEN 1 " + $" WHEN {Sql.EscapeStringLiteral("Medium")} THEN 2 " + $" WHEN {Sql.EscapeStringLiteral("Low")} THEN 3 END"; List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>(); columnMappings.Add(new Inserter.ColumnMapping("Order ID")); columnMappings.Add(new Inserter.ColumnMapping("Ship Timestamp", textToTimeStampExpression)); columnMappings.Add(new Inserter.ColumnMapping("Ship Mode")); columnMappings.Add(new Inserter.ColumnMapping("Ship Priority", shipPriorityAsIntCaseExpression)); // Insert data into the "Extract"."Extract" table with expressions. using (Inserter inserter = new Inserter(connection, extractTable, columnMappings, inserterDefinition)) { inserter.AddRow(399, "2012-09-13 10:00:00", "Express Class", "Urgent"); inserter.AddRow(530, "2012-07-12 14:00:00", "Standard Class", "Low"); inserter.Execute(); } // ExecuteScalarQuery is for executing a query that returns exactly one row with one column. long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}"); Console.WriteLine($"Table {extractTable} has a count of {count} rows"); } Console.WriteLine("The connection to the Hyper file has been closed."); } Console.WriteLine("The Hyper process has been shut down."); }
public void InsertNullOK() { using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile())) { Database db = new Database(new Context(fd.Fi)); using (SQLiteConnection conn = db.Connection) { conn.Open(); conn.ExecuteNonQuery(CreateTable); Assert.True(conn.DoesTableExist("Temp")); int count = 100; Random r = new Random(); Dictionary <int, string> dict = new Dictionary <int, string>(); for (int i = 0; i < count; ++i) { Inserter inserter = new Inserter("Temp", conn); int a = r.Next(); inserter.Set("A", a); // Make every other one null if (i % 2 == 0) { string b = Guid.NewGuid().ToString(); inserter.Set("B", b, false); dict[a] = b; } else { inserter.SetNull("B"); dict[a] = null; } inserter.Execute(); } foreach (int a in dict.Keys) { string sql = $"SELECT B FROM Temp WHERE A = {a}"; using (SQLiteCommand command = new SQLiteCommand(sql, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { bool read = reader.Read(); Assert.True(read); if (read) { if (dict[a] != null) { Assert.False(reader.IsDBNull(0)); string b = reader.GetString(0); Assert.Equal(dict[a], b); } else { Assert.True(reader.IsDBNull(0)); Assert.Throws <InvalidCastException>(() => reader.GetString(0)); } } } } } } }
/// <summary> /// Create a new Hyper file with a single table and write spatial data into it. /// </summary> /// <param name="exampleDataDir">Path to the directory with example data.</param> public override void Execute(string exampleDataDir) { Console.WriteLine("EXAMPLE - Insert spatial data into a single tables within a new Hyper file."); // Start the Hyper process with telemetry enabled. using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau)) { // Connect to hyper and create new Hyper file "spatial_data.hyper". // Replaces file if it already exists when CreateMode.CreateAndReplace is set. using (Connection connection = new Connection(hyper.Endpoint, "spatial_data.hyper", CreateMode.CreateAndReplace)) { // The table is called "Extract" and will be created in the "Extract" schema. // This has historically been the default table name and schema for extracts created by Tableau. TableName extractTable = new TableName("Extract", "Extract"); TableDefinition extractTableDefinition = new TableDefinition(extractTable) .AddColumn("Name", SqlType.Text(), Nullability.NotNullable) .AddColumn("Location", SqlType.Geography(), Nullability.NotNullable); // Create the schema and the table connection.Catalog.CreateSchema("Extract"); connection.Catalog.CreateTable(extractTableDefinition); // Hyper API's Inserter allows users to transform data during insertion. // To make use of data transformation during insertion, the inserter requires the following inputs // 1. The connection to the Hyper instance containing the table. // 2. The table name or table defintion into which data is inserted. // 3. List of Inserter.ColumnMapping. // This list informs the inserter how each column in the target table must be tranformed. // The list must contain all the columns into which data is inserted. // "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table // For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.EscapeName("colB")}) // The column "target_column" contains the product of "colA" and "colB" after successful insertion. // SQL expression string is optional in Inserter.ColumnMapping. // For a column without any transformation (identity transformation) only the column name is required. // For example new Inserter.ColumnMapping("no_data_transformation_column") // 4. Inserter Definition, a list of column definitions for all the input values provided during insertion. // Inserter definition contains the column definition for the values that are inserted. // The data input has two text values Name and Location_as_text. List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>(); inserterDefinition.Add(new TableDefinition.Column("Name", SqlType.Text(), Nullability.NotNullable)); inserterDefinition.Add(new TableDefinition.Column("Location_as_text", SqlType.Text(), Nullability.NotNullable)); // Column 'Name' is inserted into "Extract"."Extract" as-is. // Column 'Location' in "Extract"."Extract" of geography type is computed from Column 'Location_as_text' of text type // using the expression 'CAST("Location_as_text") AS GEOGRAPHY'. // Inserter.ColumnMapping is used for mapping the CAST expression to Column 'Location'. string textToGeographyCastExpression = $"CAST({Sql.EscapeName("Location_as_text")} AS GEOGRAPHY)"; List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>(); columnMappings.Add(new Inserter.ColumnMapping("Name")); columnMappings.Add(new Inserter.ColumnMapping("Location", textToGeographyCastExpression)); // Insert spatial data into the "Extract"."Extract" table using CAST expression. using (Inserter inserter = new Inserter(connection, extractTableDefinition, columnMappings, inserterDefinition)) { inserter.AddRow("Seattle", "point(-122.338083 47.647528)"); inserter.AddRow("Munich", "point(11.584329 48.139257)"); inserter.Execute(); } // ExecuteScalarQuery is for executing a query that returns exactly one row with one column. long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}"); Console.WriteLine($"Table {extractTable} has a count of {count} rows"); } Console.WriteLine("The connection to the Hyper file has been closed."); } Console.WriteLine("The Hyper process has been shut down."); }
/// <summary> /// Updates a device's status. If the new status is null, the existing status should be invalidated and no new /// entry added. If the new status is non-null, see it the existing status matches the new status. If the new /// and old status are identical, do nothing. If the new and old status are different somehow (the status itself, /// the is-alarm flag or the message are different), invalidate the old status and insert a new status. /// /// A single device may have different statuses, so we need to restrict our decisions to a set of types, which /// is included in the 3rd parameter. For example, the memory statuses are adequate, low, or critically low. Only /// one of these should be 'active' at a given time. /// </summary> /// <param name="device_id">The ID of the device we're checking</param> /// <param name="type">The new type. Can be null to indicate the status should be cleared</param> /// <param name="statuses">The full set of statuses we should be considering. Will be a subset of all /// the statuses a device can have.</param> /// <param name="message">A message regarding the status</param> /// <param name="conn">The DB connection to use</param> protected static void SetDeviceStatus(long device_id, EStatusType?type, List <EStatusType> statuses, string message, SQLiteConnection conn) { if (type.HasValue) { Debug.Assert(statuses.Contains(type.Value)); } EAlertLevel alert_level = (type.HasValue ? type.Value.GetAlertLevel() : null) ?? EAlertLevel.Normal; string in_clause = statuses.Join(); string clause = $"DeviceID = {device_id} AND StatusType IN ({in_clause}) AND IsValid = 1"; string sql = $"SELECT StatusType, IsAlarm, Message FROM DeviceStatus WHERE {clause};"; bool insert = type.HasValue; bool remove = type.HasValue == false; if (type.HasValue) { // We may be inserting a new type. We need to see if the current value for the device/status is the // same, or has changed. If it's the same we don't need to do anything. If it's changed, we'll want to // mark the old value as invalid, and insert the new value. using (SQLiteCommand command = new SQLiteCommand(sql, conn)) using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.Read()) { EStatusType existing_type = (EStatusType)reader.GetInt32(0); EAlertLevel existing_alert_level = (EAlertLevel)reader.GetInt32(1); string existing_message = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); // An existing record exists, so insert the new one and update the old one if something's changed. // If nothing changed, we'll leave the old one alone bool something_changed = (type.Value != existing_type) || (alert_level != existing_alert_level) || (string.Compare(message, existing_message) != 0); // If something has changed, we'll want to remove the old version, and insert the new // version. If nothing changed, we don't want to do either. insert = remove = something_changed; } // If it wasn't found, just insert, which is the default } } // In this case there's no status value, so that means were clearing out the old one, if it exists. if (insert || remove) { if (remove) { Updater updater = new Updater("DeviceStatus", clause, conn); updater.Set("IsValid", 0); updater.Execute(); } if (insert) { Inserter inserter = new Inserter("DeviceStatus", conn); inserter.Set("DeviceID", device_id); inserter.Set("StatusType", (int)type); inserter.Set("IsAlarm", (int)alert_level); inserter.Set("Date", DateTimeOffset.Now); inserter.Set("Message", message, false); inserter.Set("IsValid", 1); inserter.Execute(); } } // else, no change }
protected override void CloseLogic() { _inserter.Execute(); }
/// <summary> /// Create a new Hyper file with multiple tables and write some data into them. /// </summary> /// <param name="exampleDataDir">Path to the directory with example data.</param> public override void Execute(string exampleDataDir) { Console.WriteLine("EXAMPLE - Insert data into multiple tables within a new Hyper file."); // Start the Hyper process with telemetry enabled. using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau)) { // Connect to Hyper and create new Hyper file "superstore.hyper". // It replaces the file if it already exists when CreateMode.CreateAndReplace is set. using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace)) { // Create definitions for the tables to be created. // Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace. TableDefinition orders = new TableDefinition("Orders") .AddColumn("Address ID", SqlType.SmallInt(), Nullability.NotNullable) .AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Order Date", SqlType.Date(), Nullability.NotNullable) .AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Ship Date", SqlType.Date()) .AddColumn("Ship Mode", SqlType.Text()); // Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace. TableDefinition customer = new TableDefinition("Customer") .AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable) .AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable) .AddColumn("Segment", SqlType.Text(), Nullability.NotNullable); // Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace. TableDefinition products = new TableDefinition("Products") .AddColumn("Category", SqlType.Text(), Nullability.NotNullable) .AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Product Name", SqlType.Text(), Nullability.NotNullable) .AddColumn("Sub-Category", SqlType.Text(), Nullability.NotNullable); // Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace. TableDefinition lineItems = new TableDefinition("Line Items") .AddColumn("Line Item ID", SqlType.BigInt(), Nullability.NotNullable) .AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable) .AddColumn("Sales", SqlType.Double(), Nullability.NotNullable) .AddColumn("Quantity", SqlType.SmallInt(), Nullability.NotNullable) .AddColumn("Discount", SqlType.Double()) .AddColumn("Profit", SqlType.Double(), Nullability.NotNullable); // Create tables in the database. connection.Catalog.CreateTable(orders); connection.Catalog.CreateTable(customer); connection.Catalog.CreateTable(products); connection.Catalog.CreateTable(lineItems); // Insert data into Orders table. using (Inserter inserter = new Inserter(connection, orders)) { inserter.AddRow(399, "DK-13375", new Date(2012, 9, 7), "CA-2011-100006", new Date(2012, 9, 13), "Standard Class"); inserter.AddRow(530, "EB-13705", new Date(2012, 7, 8), "CA-2011-100090", new Date(2012, 7, 12), "Standard Class"); inserter.Execute(); } // Insert data into Customers table. using (Inserter inserter = new Inserter(connection, customer)) { inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer"); inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate"); inserter.Execute(); } // Insert data into Product table. using (Inserter inserter = new Inserter(connection, products)) { inserter.AddRow("TEC-PH-10002075", "Technology", "Phones", "AT&T EL51110 DECT"); inserter.Execute(); } // Insert data into Line Items table. using (Inserter inserter = new Inserter(connection, lineItems)) { inserter.AddRow(2718, "CA-2011-100006", "TEC-PH-10002075", 377.97, 3, 0.0, 109.6113); inserter.AddRow(2719, "CA-2011-100090", "TEC-PH-10002075", 377.97, 3, null, 109.6113); inserter.Execute(); } foreach (var name in new[] { orders.TableName, customer.TableName, products.TableName, lineItems.TableName }) { // ExecuteScalarQuery is for executing a query that returns exactly one row with one column long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {name}"); Console.WriteLine($"Table {name} has a count of {count} rows"); } } Console.WriteLine("The connection to the Hyper file has been closed."); } Console.WriteLine("The Hyper process has been shut down."); }