Document Database – part 2

By Xiaodong Liang

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.