Using ADO.NET with C#Builder Personal by Bob Swart

By: Bob Swart

Abstract: In this article, Bob Swart shows you how to use C#Builder Personal in combination with "plain" ADO.NET to connect to a MSDE database, create a table, insert records, select the records from this table again and show them in a datagrid.

One of the great new features of C#Builder is the availability of the Borland Data Providers for ADO.NET, with great design-time support to connect & work with databases, specific drivers for DB2, InterBase, Oracle, SQL Server / MSDE, as well as the potential to add third-party drivers (just like dbExpress). However, the Borland Data Providers are not included with C#Builder Personal, which means you have to resort to "plain" ADO.NET; the standard .NET way of talking to a database.
In this article, I'll show you how to use C#Builder Personal in combination with ADO.NET to connect to a MSDE database, create a table, insert records, select the records from this table again, and finally show them in a DataGrid.
For the easiest way to "play-along", I assume that you already have MSDE installed (which comes with C#Builder - including the Personal edition).

SqlConnection
Start C#Builder and do File | New - C# Application to start a new project. Drop an SqlConnection component, and set the Connection property to

 Data Source=.; Initial Catalog=master; Integrated security=SSPI
Note that the master catalog is one of the databases that comes with MSDE, but you can use any other database or .NET DBMS (although you obviously have to modify the ConnectionString accordingly).

Drop a first button on the WinForm, set the caption to "Connect" and write the following code in the button_Click event handler to open the sqlConnection (note that I set the ConnectionString property again, but this is only to show you how it can be done "purely" in code):

  private void button1_Click(object sender, System.EventArgs e)
  {
    try
    {
       sqlConnection1.ConnectionString = "Data Source=.; Initial Catalog=master; Integrated security=SSPI";
       sqlConnection1.Open();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
  }
This is the first test, and the one most likely to fail (all other steps are easy). If you get an exception, you will probably have to edit the ConnectionString to make sure it contains the right reference (note: also make sure that SQL Server / MSDE is running, of course).

SqlCommand
Once you can make a successful connection, it's time to drop an SqlCommand component. This is a component that can be used to execute commands: SQL statements. You can point the Connection property to the SqlConnection component using the Object Inspector, and place the command to execute in the CommandText property. Note that the drop-down combobox for the Connection property does not seem to list the SqlConnection component (which is also on the form), but the "trick" is to double-click on the property value (which will cycle you through the possible values). Another way is to start typing the name of the component you want to use, so "s" will select "sqlConnection1" right away. To clear the Connection property again, you need to type "(" which results in "(none)" again.

Since it may be instructive to see how all this is done in "pure" code, I will now assign all the necessary property values in source code (including the SQL commands that I want to execute).
So, drop another button, set the caption to "SQL" and write the following code in the button_Click event handler to create a table (drop it first if it exists), and insert three records:

  private void button2_Click(object sender, System.EventArgs e)
  {
    try
    {
      sqlCommand1.Connection = sqlConnection1;
      sqlCommand1.CommandText = "drop table test42";
      try
      {
        sqlCommand1.ExecuteNonQuery(); // create table
      }
      catch {}; // ignore
  
      sqlCommand1.CommandText = "create table test42 (id int NOT NULL, name nvarchar(42))";
      sqlCommand1.ExecuteNonQuery(); // create table
  
      sqlCommand1.CommandText = "insert into test42 values(1, 'Bob Swart')";
      sqlCommand1.ExecuteNonQuery(); // insert table
      sqlCommand1.CommandText = "insert into test42 values(2, 'Erik Mark Pascal Swart')"; // my son
      sqlCommand1.ExecuteNonQuery(); // insert table
      sqlCommand1.CommandText = "insert into test42 values(3, 'Natasha Louise Delphine Swart')"; // my daughter
      sqlCommand1.ExecuteNonQuery(); // insert table
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
  }
Note that the first try-catch block is only used to ignore the exception when the table doesn't exist (in which case you cannot drop it).
I have to use the ExecuteNonQuery method of the SqlCommand since the SQL statements return no data, and are only meant to drop the table, create a new table or insert new records (obviously, ExecuteNonQuery also has to be called when deleting records).

SqlDataAdapter, DataSet, and DataGrid
Now that we've made a connection to the MSDE "master" database, and created a table "test42" with three records, it's time to select this data and connect it to a grid. For this purpose, I want to use three components: an SqlDataAdapter, a DataSet and obviously a DataGrid to show the data.
First drop an SqlDataAdapter component. This component has subproperties for the SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. Each of these commands corresponds to an SqlCommand component that we used in the previous section. At this time, we only need the SelectCommand, for which you must first assign the Connection property and then a SELECT statement to the CommandText property (note that I'm doing this in code again).
Now, drop a DataSet component. This DataSet will be filled with the result of the SelectCommand. Unlike Delphi, a .NET DataSet can contain multiple tables, by the way.
Finally, drop a DataGrid component. We can assign the DataSource and DataMember properties of the DataGrid to connect to the DataSet and Table in order to view the data. Feel free to configure the DataGrid and make it look nice (I'll skip that step for now, and focus on the Data Components instead).

The code to initialise the SelectCommand property of the SqlDataAdapter component, and use the SelectCommand to fill the DataSet and finally bind it to the DataGrid is as follows (in the button_Click event handler of a third button):

  private void button3_Click(object sender, System.EventArgs e)
  {
    try
    {
      sqlDataAdapter1.SelectCommand.Connection = sqlConnection1;
      sqlDataAdapter1.SelectCommand.CommandText = "select * from test42";
      sqlDataAdapter1.Fill(dataSet1, "MyTable42");

      dataGrid1.DataSource = dataSet1;
      dataGrid1.DataMember = "MyTable42"; // same name as above
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
  }
Note that the name that I specify when filling the DataSet (by the SqlDataAdapter) is the same name that I have to specify as DataMember property value when I bind the DataSet to the DataGrid.
This will show the output of the select statement in the DataGrid, as follows:

As you can see, it's the SqlConnection that talks to the database, and the SqlCommand and SqlDataAdapter that use the Connection to execute commands (and optionally fill .NET DataSets with the results).

Summary
If you want to repeat everything really quickly (the RAD way), then here are some numbered steps that can help (assuming the master database and test42 table with records already exist by now).

  1. Start a new C#Builder project
  2. Drop an SqlConnection component
    set the ConnectionString property (this is the only "custom" part), like:
      Data Source=.; Initial Catalog=master; Integrated security=SSPI
    
  3. Drop an SqlDataAdapter component
    Point the SelectCommand.Connection property to the SqlConnection component
    Write a select-statement in the CommandText property, for example:
      select * from test42
    
  4. Drop a DataGrid component
    Feel free to use the "Auto Format" link to format the DataGrid
  5. Double-click on the Form, which will bring you to the WinForm_Load event handler. In here, we'll write the code to fill a new DataSet with the results of the SqlDataAdapter.SelectCommand.CommandText, and bind it to the DataGrid, as follows:
      private void WinForm_Load(object sender, System.EventArgs e)
      {
        try
        {
          DataSet ds = new DataSet();
    //    sqlConnection1.Open(); // implicit
          sqlDataAdapter1.Fill(ds, "MyTable42");
          dataGrid1.DataSource = ds;
          dataGrid1.DataMember = "MyTable42"; // same name as above
          sqlConnection1.Close(); // explicit
        }
        catch (Exception ex)
        {
          MessageBox.Show(ex.Message);
        }
      }
    
  6. Save, compile and run your application (make sure SQL Server / MSDE is also running ;-)
The DataGrid will now show the result of the SQL select command, which demonstrates the use of the SqlConnection, SqlDataAdapter (and SqlCommand) and DataSet components.
Note that in the last code snippet, I've also using the SqlConnection.Open() and Close() statements to illustrate that the connection will be opened automatically, but has to be closed explicitly (if you don't want to keep the connection open longer than absolutely necessary).

Conclusion
In this article I have shown how to use C#Builder Personal in combination with ADO.NET to connect to a MSDE database, create a table, insert records, select the records from this table again, and show them in a DataGrid. In future articles we'll explore other capabilities of C#Builder in more details, so stay tuned.
Feel free to send me feedback, comments or any questions about this article. You can either e-mail me directly, or use the borland.public.csharpbuilder.* newsgroups.


Bob Swart (aka. "Dr.Bob" - http://www.drbob42.com) is author, trainer, consultant, developer, and webmaster for his own company Bob Swart Training & Consultancy (eBob42) in Helmond, The Netherlands. Bob is overseas member and webmaster for the UK-BUG Borland User Group as well as the Dotnet Developers Group (DDG). Bob is a technical author for The Delphi Magazine, Borland .NET Developer's Journal, C++Builder Developer's Journal, Hardcore Delphi, Der Entwickler, SDGN Magazine, UK-BUG Developer's Magazine, writes the IBM DB2 and Borland web portals, and has spoken at (Borland) conferences all over the world since 1994.
Bob is married to Yvonne and they have two internet-aware children: Erik Mark Pascal (9 years) and Natasha Louise Delphine (6.5 years).


Server Response from: ETNASC03