In the last post, we leant how to create a database table. Now let us take a look the usage of add rows and query the table.
To add a row, we need to prepare the value of each column. NavisworksCommand contains the parameters collection which works for the column data. You could either call its method AddWithValue, or create a NavisworksParameter firstly, next add it to the parameters collection. Finally, NavisworksCommand will input these values when it calls SQL. The code demo below add one row with item_name = “MyItemName”, count = 100, price = 123.456.
private void AddRow()
{
//get document database
DocumentDatabase database =
Autodesk.Navisworks.Api.Application.
ActiveDocument.Database;
using (NavisworksTransaction trans =
database.BeginTransaction(DatabaseChangedAction.Edited))
{
NavisworksCommand cmd =
trans.Connection.CreateCommand();
//"item_name"
// way 1: add the value directly
cmd.Parameters.AddWithValue("@p1", "MyItemName");
//"count"
// way 2: create a NavisworksParameter
NavisworksParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@p2";
p2.Value = 100;
cmd.Parameters.Add(p2);
//"price"
// way 2: create a NavisworksParameter
NavisworksParameter p3 = cmd.CreateParameter();
p3.ParameterName = "@p3";
p3.Value = 123.456;
cmd.Parameters.Add(p3);
//build the SQL text
string insert_sql =
"INSERT INTO order_test(item_name, count, price)" +
" VALUES(@p1, @p2, @p3);";
cmd.CommandText = insert_sql;
//execute SQL
cmd.ExecuteNonQuery();
trans.Commit();
}
}
To query the table, we could use NavisworksDataAdapter to retrieve data from the data source. The code below gets the data from the table we defined. It dumps each row and also calculates the total cost from the values of count * price of each row.
private void QueryTable()
{
//get document database
DocumentDatabase database =
Autodesk.Navisworks.Api.
Application.ActiveDocument.Database;
//create adaptor to retrieve data from the data source.
NavisworksDataAdapter dataAdapter =
new NavisworksDataAdapter(
"SELECT item_name, " +
"count, price FROM order_test",
database.Value);
//An empty DataTable instance
//is passed as an argument to the Fill method
//When the method returns,
//the DataTable instance will be
//populated with the queried data
DataTable table = new DataTable();
dataAdapter.Fill(table);
//dump the data of each row
// calculate total cost with the data from
// column "count" and "price"
string showStr= "contents of my table.\n";
showStr += "[item_name] [count] [price] [total cost]\n";
foreach (DataRow row in table.Rows)
{
showStr += string.Format("{0}: {1} {2} {3}",
row["item_name"],
row["count"],
row["price"],
Convert.ToDouble(row["count"]) *
Convert.ToDouble(row["price"]));
}
MessageBox.Show(showStr);
}
You could save the document, close and re-open it to verify the database.