Tutorial 5 - Using C1List with SQL Query Results

In this tutorial, you will learn how to use C1List to display the results of ad-hoc SQL queries. In addition, it will also outline how to set up a connection to a DataSet at run-time. Note that in order for the list to automatically respond to field layout changes, you must not have defined any column properties at design time. If a layout is already defined, use the list's Clear Fields context menu command to remove it. This will cause the list to configure itself automatically at run time.

Follow steps 1 through 4 of Tutorial 1 - Binding C1List to a DataSet to create a project with a C1List control bound to a Data Set.
Set the DataMember property of the list to Composer.
Place a C1List control, a Command button and a Text box on the form as shown in the figure. Rename the text property of the command button to read Execute SQL and set the Text property of the TextBox to Enter SQL statement here.

Add the following code to the Button1_Click event:

To write code in Visual Basic

Visual Basic	Copy Code
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim ds As New DataSet() Dim sqlStr As String Dim da As OleDb.OleDbDataAdapter sqlStr = Me.TextBox1.Text.Trim() da = New OleDb.OleDbDataAdapter(sqlStr, Me.ComposerTableAdapter.Connection) Try da.Fill(ds, "TestSQL") Me.C1List1.DataSource = Nothing Me.C1List1.ClearFields() Me.C1List1.DataSource = ds.Tables("TestSQL") Catch x As Exception MsgBox("Invalid SQL statement.") End Try End Sub

To write code in C#

C#	Copy Code
private void Button1_Click( System.object sender, System.EventArgs e) { DataSet ds = new DataSet(); string sqlStr; OleDb.OleDbDataAdapter da; sqlStr = this.TextBox1.Text.Trim(); da = new OleDb.OleDbDataAdapter(sqlStr, this.ComposerTableAdapter.Connection); try { da.Fill(ds, "TestSQL"); this.c1List1.DataSource = null; this.c1List1.ClearFields(); this.c1List1.DataSource = ds.Tables("TestSQL"); } catch( Exception x) { MsgBox("Invalid SQL statement."); } }

Run the program and observe the following:

As in Tutorial 1 - Binding C1List to a DataSet, C1List retrieves the database schema information from the DataSet and automatically configures itself to display the data for all fields in the database table. Note that the field names are used as the default column headings.

In the TextBox control, enter the following SQL statement: Select * from Composer
then press the Execute SQL command button. The above SQL statement displays all fields from the Customer table and is equivalent to the default display.
In the TextBox control, enter the following SQL statement: Select Country from Composer
then click the Execute SQL command button. The list responds by displaying only one column for the Country field.
In the TextBox control, enter the following SQL statement: Select Last, Country from Composer
then click the Execute SQL command button. This is similar to the previous SQL statement except that two columns (Last and Country) are now displayed.
In the TextBox control, enter the following SQL statement: Select Count(*) from Composer
then click the Execute SQL command button. The above SQL statement uses an aggregate function, Count(*):SQL, to return the total number of records in the Customer table. Even though the SQL result is not a set of records, the list faithfully responds by displaying the number of records in a single column. By default, Expr1000 is used as the column heading, indicating that the display is the result of an expression.
In the TextBox control, enter the following SQL statement: Select UCase(Last) as ULAST, UCase(First) AS UFIRST from Composer
then click the Execute SQL command button. The above SQL statement produces two calculated columns which display the Last and First fields in upper case. The list also displays the (assigned) calculated column names, ULAST and UFIRST, as the column headings.
In the TextBox control, enter the following SQL statement: SELECT * FROM Composer WHERE First = "Edward"
then click the Execute SQL command button. The above SQL statement displays only records with First equal to Edward.
In the TextBox control, enter the following SQL statement: SELECT * FROM Composer ORDER BY Last
then press the Execute SQL command button. The above SQL statement displays records in alphabetical order according to the Last field.

This concludes the tutorial.