Coding an ADO.NET Application

The C#Builder IDE offers powerful facilities for working with ADO.NET. It is graphical and enables the developer to be very productive when building applications that access databases. Although working with drag-and-drop components is very convenient, developers still need to understand the code. There will be times when the GUI won't do everything or a difficult problem needs to be debugged. At these times, the developer who knows the code will solve problems faster than anyone else. Therefore, before delving into the world of drag-and-drop BDP components, this section will present a couple of simple code-based ADO.NET applications.

Coding an ADO.NET Data Reader

The first application demonstrated will be a program that reads the Employees table from the Northwind database in SQL Server. The information will be displayed in a TextBox control in comma-separated value (CSV) format on a Windows Form. Listing 15.1 shows how to code this application, using the BdpConnection, BdpCommand, and BdpDataReader objects.

To get started with this application, start a new Windows Forms application, drop a TextBox control on it, name it txtCsv, set ScrollBars to Both, set WordWrap to false, delete the contents of its Text property, set its Multiline property to true, and set its Dock property to Fill in the Object Inspector. See the Shop Talk in this section for advice on setting up Microsoft Desktop Engine (MSDE), which is a scaled-down version of SQL Server 2000 that hosts the Northwind database used for examples in this book.

Listing 15.1 Using the ADO.NET DataReader (CodeDataReader.cs)
 using System; using System.Collections; using System.Data; using System.Drawing; using System.ComponentModel; using System.Windows.Forms; using Borland.Data.Common; using Borland.Data.Provider; namespace CodeDataReader {    /// <summary>    /// Summary description for CodeDataReader.    /// </summary>    public class CodeDataReader : System.Windows.Forms.Form    {       /// <summary>       /// Required designer variable.       /// </summary>       private System.ComponentModel.Container components = null;       private System.Windows.Forms.TextBox txtCsv;       public CodeDataReader()       {          //          // Required for Windows Form Designer support          //          InitializeComponent();          string connectionString = "database=Northwind;" +             "hostname=denver;assembly=Borland.Data.Mssql," +             "Version=1.1.0.0,Culture=neutral," +             "PublicKeyToken=91d62ebb5b0d1b1b;" +             "vendorclient=sqloledb.dll;provider=MSSQL;" +             "username=sa;password=YourPassword" +             "";          BdpConnection conn   = new BdpConnection(connectionString);          BdpDataReader reader = null;          try          {             conn.Open();             BdpCommand cmd = new BdpCommand(                "select * from products", conn);             reader = cmd.ExecuteReader();             ArrayList row         = null;             string[]  fields      = null;             string    fieldString = null;             while (reader.Read())             {                row = new ArrayList();                // get all the fields for the current row                for (int i=0; i < reader.FieldCount; i++)                {                   row.Add(reader[i].ToString());                }                // form the fields into a csv string                fields = (string[])row.ToArray(typeof(string));                fieldString = String.Join(",", fields);                txtCsv.AppendText(fieldString + "\r\n");             }          }          catch (BdpException bdpe)          {             MessageBox.Show(bdpe.ToString());          }          finally          {             if (reader != null)             {                reader.Close();             }             if (conn != null)             {                conn.Close();             }          }       }       /// <summary>       /// Clean up any resources being used.       /// </summary>       protected override void Dispose (bool disposing)       {          if (disposing)          {             if (components != null)             {                components.Dispose();             }          }          base.Dispose(disposing);       }       #region Windows Form Designer generated code       /// <summary>       /// Required method for Designer support - do not modify       /// the contents of this method with the code editor.       /// </summary>       private void InitializeComponent()       {          this.txtCsv = new System.Windows.Forms.TextBox();          this.SuspendLayout();          //          // txtCsv          //          this.txtCsv.Dock = System.Windows.Forms.DockStyle.Fill;          this.txtCsv.Location = new System.Drawing.Point(0, 0);          this.txtCsv.Multiline = true;          this.txtCsv.Name = "txtCsv";          this.txtCsv.ScrollBars =             System.Windows.Forms.ScrollBars.Both;          this.txtCsv.Size = new System.Drawing.Size(292, 266);          this.txtCsv.TabIndex = 0;          this.txtCsv.Text = "";          this.txtCsv.WordWrap = false;          //          // CodeDataReader          //          this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);          this.ClientSize = new System.Drawing.Size(292, 266);          this.Controls.Add(this.txtCsv);          this.Name = "CodeDataReader";          this.Text = "CodeDataReader";          this.ResumeLayout(false);       }       #endregion       /// <summary>       /// The main entry point for the application.       /// </summary>       [STAThread]       static void Main()       {          Application.Run(new CodeDataReader());       }    } } 

The BdpDataReader is the fastest way to pull information out of a database. A BdpDataReader returns a fast-forward, read-only stream of data that is often read one time and disposed or closed. The CodeDataReader constructor in Listing 15.1 shows how to do this in just a few easy steps: create and open a connection, create a command object, and then call ExecuteReader on the command to return a BdpDataReader. When manually coding BDP components, remember to add using statements for the Borland.Data.Common and Borland.Data.Provider namespaces. The following discussion introduces each of these objects in more detail.

SHOP TALK
SETTING UP MICROSOFT DESKTOP ENGINE (MSDE)

Microsoft Desktop Engine (MSDE) is a scaled-down version of Microsoft SQL Server 2000 that is suitable for distribution with an application that requires limited database support but doesn't need the high capacity and performance features of a full-blown SQL Server 2000 installation. MSDE ships with Borland C#Builder Professional and above and is the database I use for examples in this book.

Unfortunately, MSDE installation is not automatic and requires manual intervention. When you click on the Microsoft(R) SQL Server 2000 Desktop Engine Service Pack 3a link when installing C#Builder components via the CD Launcher, a text document pops up with setup instructions. The first part is easy because all you have to do is locate the MSDE self-extracting file in the MSDE directory of Disk 2 and run it. For the purposes of this discussion, I'll assume that during extraction you accepted the default location of c:\sql2ksp3.

The second part is a little more tricky. The procedures I'm going to explain are from the HTML file at c:\sql2ksp3\MSDE\sp3readme.htm, paragraph 3.7.5. Two of the bullets in subparagraph 3 have INSTANCE parameters, which you may use if you want, but it is not required and I don't see a problem with leaving it out. What is important is the database System Administrator (SA) password and security mode. You really should give your SA account a password because existing viruses can exploit the blank SA password vulnerability. You should also set the security mode to mixed mode. If you don't, the security mode will default to Windows Integrated Authentication, which means that you will not be able to specify a system-independent password to access the database. Mixed mode allows MSDE to work with both Windows and SQL Server authentication. After the MSDE files have been extracted, open a command prompt. On my Windows XP system, I select Start, All Programs, Accessories, Command Prompt. Change directories to c:\sql2ksp3\MSDE (just type cd c:\sql2ksp3\MSDE). Then type setup SAPWD="YourPasswordHere" SECURITYMODE=SQL, which will set up MSDE with an SA password and mixed security mode.

One last note before moving on you really should make sure that all your SQL Server and MSDE instances are upgraded to Service Pack 3 (SP3) or above. According to the April 2003 editorial in MSDN Magazine, Joshua Trupin advised that the SQL SLAMMER virus (announced on January 25, 2003), which is blocked by SP3, is still infecting systems because systems are still being left unprotected. So, if you have older versions of SQL Server or MSDE installed without SP3, they should be upgraded or uninstalled.

A BdpConnection object maintains database connections and is explicitly opened before interacting with the database and closed when complete. BdpConnection objects can be instantiated with a connection string, which contains parameters necessary to open the connection. There is also a default BdpConnection constructor, but if it is used, remember to add the connection string to the BdpCommand object's ConnectionString property. The password in the connection string in Listing 15.1 is invalid and should be replaced to prevent problems when running the program. I obtained this connection string by dropping the BdpConnection component onto a test form and copying the string in the ConnectionString property of the Object Inspector, which will be explained in a later section of this chapter. In Listing 15.1, the BdpConnection is opened within a try block so that the program can recover from exceptions raised if the connection can't be made. Notice that there is code in the finally block to guarantee that the BdpConnection will be properly closed if an exception occurs. This is a perfect example of when a finally block should be used.

BdpCommand objects are for executing SQL queries in a database. These queries may be either stored procedures or query strings that are sent to the database. The example in Listing 15.1 takes the latter approach by sending a select statement. The BdpCommand object must either be instantiated with a BdpConnection object or have its Connection property set for it to figure out which database to talk to.

To execute a command, the BdpCommand class has the ExecuteNonQuery, ExecuteScalar, and ExecuteReader methods. The ExecuteNonQuery command would be used for SQL commands such as delete, insert, and update. ExecuteScalar returns a single item from a query, which could be the first column of the first row returned or, more commonly, a single value from a query such as select count(*). The method used in Listing 15.1 is ExecuteReader, which returns a BdpDataReader object.

After a BdpDataReader object is created in Listing 15.1, it is used in a while loop to read each record. The Read method returns true as long as there are more records. Within the while loop in the example, each column is retrieved by index in a for loop. In this case, it didn't matter what the column was because the purpose of the program was just to get the data and dump it. However, if a program were interested in some type of processing on a specific column, a string representation of the column name could have been used. In addition to indexer access, the BdpDataReader has accessor methods, such as GetInt32, GetString, and so on for obtaining type-specific data.

Coding an ADO.NET DataAdapter and DataSet

The next program shows how to fill a DataSet using a BdpDataAdapter. This time, the results will be bound to a DataGrid control. To set up the next example, create a new Windows Forms application, drag-and-drop a DataGrid control, and set the DataGrid's Dock property to Fill. Listing 15.2 shows how to build an application that fills a DataSet.

Listing 15.2 Using the ADO.NET DataAdapter and DataSet (CodeDataSet.cs)

 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using Borland.Data.Common; using Borland.Data.Provider; namespace CodeDataSet {    /// <summary>    /// Summary description for CodeDataSet.    /// </summary>    public class CodeDataSet : System.Windows.Forms.Form    {       /// <summary>       /// Required designer variable.       /// </summary>       private System.ComponentModel.Container components = null;       private System.Windows.Forms.DataGrid dataGrid1;       public CodeDataSet()       {          //          // Required for Windows Form Designer support          //          InitializeComponent();          string connectionString = "database=Northwind;hostname=denver; assembly=Borland graphics/ccc.gif.Data.Mssql,Ver" +          "sion=1.1.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;vend" +          "orclient=sqloledb.dll;provider=MSSQL;username=sa;password=YourPwdHere" +          "";          BdpConnection conn = new BdpConnection(connectionString);          DataSet       ds   = new DataSet();          try          {             BdpDataAdapter da =                new BdpDataAdapter("select * from products", conn);             da.Fill(ds, "Products");             dataGrid1.DataSource = ds;             dataGrid1.DataMember = "Products";          }          catch (BdpException bdpe)          {             MessageBox.Show(bdpe.ToString());          }       }       /// <summary>       /// Clean up any resources being used.       /// </summary>       protected override void Dispose (bool disposing)       {          if (disposing)          {             if (components != null)             {                components.Dispose();             }          }          base.Dispose(disposing);       }       #region Windows Form Designer generated code       /// <summary>       /// Required method for Designer support - do not modify       /// the contents of this method with the code editor.       /// </summary>       private void InitializeComponent()       {          this.dataGrid1 = new System.Windows.Forms.DataGrid();             ((System.ComponentModel.ISupportInitialize)                (this.dataGrid1)).BeginInit();             this.SuspendLayout();             //             // dataGrid1             //             this.dataGrid1.DataMember = "";             this.dataGrid1.Dock = System.Windows.Forms.DockStyle.Fill;             this.dataGrid1.HeaderForeColor =                System.Drawing.SystemColors.ControlText;             this.dataGrid1.Location = new System.Drawing.Point(0, 0);             this.dataGrid1.Name = "dataGrid1";             this.dataGrid1.Size = new System.Drawing.Size(292, 266);             this.dataGrid1.TabIndex = 0;             //             // CodeDataSet             //             this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);             this.ClientSize = new System.Drawing.Size(292, 266);             this.Controls.Add(this.dataGrid1);             this.Name = "CodeDataSet";             this.Text = "CodeDataSet";             ((System.ComponentModel.ISupportInitialize)                (this.dataGrid1)).EndInit();             this.ResumeLayout(false);          }          #endregion          /// <summary>          /// The main entry point for the application.          /// </summary>          [STAThread]          static void Main()          {             Application.Run(new CodeDataSet());          }    } } 

A DataSet is the primary means of holding disconnected data in ADO.NET. The CodeDataSet constructor in Listing 15.2 shows how to fill a DataSet and bind it to a DataGrid control. This happens with the help of another ADO.NET component, called the DataAdapter.

A BdpDataAdapter is the linkage between a data source and the DataSet. As the code in the constructor of Listing 15.2 shows, a BdpConnection object is created, but not opened. This is because the BdpDataAdapter opens and closes the connection on an as-needed basis. The BdpDataAdapter is instantiated with a select query and the connection object. To load data into the DataSet, the code uses the Fill method of the BdpDataAdapter, which accepts a reference to the DataSet to load and the logical name of the table being read. During execution of the Fill method, the BdpDataAdapter is opened and closed by the BdpDataAdapter.

To load information into the DataGrid, assign the DataSet to its DataSource property. Because a DataSet may contain more than one table, each added with a separate BdpDataAdapter, the DataGrid will attempt to display a tree view of all the tables in the DataSet. In this case there is only a single table. So, to get this table to show without the tree view, assign the logical table name specified in the Fill method to the DataMember property of the DataGrid.



C# Builder KickStart
C# Builder KickStart
ISBN: 672325896
EAN: N/A
Year: 2003
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net