Updateable Query Result Pane in the VS2005 Part I (by Ming Chen)

I meant to talk about the Updateable Result Pane soon on My First Post. But get distracted by personal live a bit (my son is very sick for a while.

My personal feeling on VS2005 Query Designer Update result pane will be common used for

1. For programmer:
• Quickly view some Table data in the database
• It allows VC/VB/C# programmer to quickly input some sample data into a table.  (or copy a bunch data from notepad/Excel and insert into the database. 
• It allows verify the data in the database after their programming is running
• Quickly tweak some data in particular rows and test the edge case secnario case for their application
• Test run their working Parameterized query or regular --- copy the sql text to their program.
2. For database designer:
• Verify the constraint, data, relationship, design & view result. 

It is also great for people doing the demo so they can change some particular data/insert data quickly :) 

 Here is some high level high light

1. Better Look and feel -- We change the updatable result pane to use the new VS2005 new Winform Controla DataGridView and DataNavigator (speciallized ToolStrip) at bottom of the Result Pane to allow you quickly navigate to particular Rows.  Overall, look like the Microsoft Access's Table Data view.
2. Multiple DataSource - support multiple kind of ADO.NET Provider which include SQL Provider, Oracle Provider, OLEDB, and ODBC provider.  They are all should be updateable (given that the initial query is a select statement from single table.)
3. Disconnect Data Model -- It is a cancelable download.  Once it is finished, it is disconnected from the database (ie, it won't lock the table.)  (You should be able to continue the editing, do other stuff in VS while it is downloading the data.)
4. Row commit Model and Cell validation against column type -- Only commit the change to database when you change the row Index.  Input data type is check against that .Net Framework data type.
5. Data commit using Optimistic Concurrency Control -- Or you can read about Optimistic vs Pessmisstic locking here.  Just a FYI -- we don't use DataSet -- it will give user a chance to see if they want to override other application's change on the database(typically their own application.)
6. Treat DBNULL as Keyword and first class citizen -- It will be represent as NULL.  As you type along, it will change to Italitic.  You can almost set every field to NULL except for readonly or not allow null column. This consistent UI will extend to Query Parameter Dialog.

Some (new & old) Limitations:

1. Edge data is not retrievable --- It is intend to be generic, the underline object is the generic IDataReader -- not the backend specialized DataReader.  That is, for example, if you have a SQL Decimal with more than 27 percision (.Net framework Decimal limitation.) You can't read the data nor to update a Decimal with more than 27 percision.  Note: SQL decimal support up to 38 percison.  Same thing to OracleNumber, all you get is a Decimal because generic IDataReader will return a Decimal.
2. Few data type is not updateable -- In the nutshell, if we know how to convert the original Select statement to a valid SQL Update/Insert/Delete statement, it will be updateable.  As you can image, BLOB, Binary can only set to NULL but nothing else.  This is because we only know how to generate a valid statement for setting a binary field to NULL.
3. The biggest data that you can hold in the field is bound to TextBox limit (ie, TextBox.Length & String.Length is a int32. you can't get bigger than that.) -- Also for a long data like that, the UI preformance will suffer a little bit.
4. Only retrieve the First IDataReader back -- that is you can't get to the next result.
5.  Only updateable for the Single Table/View

Hmm....I forget to mention - How do you get to this Updateable result pane. You can typically get it through the following:

1. VS2005->View->Server Explorer
2. Add a database connection to it (SQL, Oracle, OLEDB, ODBC..)
3. Expand the Tables node(or View node).
4. On, say authors table, right click (context menu)
5. Either pick “New Query” or “Retrieve Table Data” --- they all show the Query Designer, the only difference is that one show up as 4 pane, the other show up as 1 pane (result pane.).  But you can use context menu to toggle all 4 panes(Diagram, Criteria, SQL, result) on an off as you wish. -- Of courese, the initial SQL text for the New Query is empty as oppose to “Retrieve Table Data“ will have initial SQL text of (select * from authors.)

Or you can have a Database project (VS2005 -> File -> New Project-> other project-> Database project.) and add a new Query from there.
 

-Ming