Newbie: Anyone have small recordset-like example? 2

[JeffTullin]

Hiyall..
I'm just getting started with c#, and as a get-me-running project I want to just open a database connection, select a field from an ad-hoc bit of SQL, and iterate through the results.

Most examples I have seen are huge - does anyone have a 20 liner example of this sort of thing?

I have access to SQL Server data, Oracle data, and many ODBC DSNs.
I always found it tricky to choose between ADO, DAO, OLEDB in the past, but I see that the choices are wider again in .NET. What is this dataset stuff all about?

 

[AmazingGrace]

Hi!

Here is a sample that reads through a resultset. You usually use this method of manual iteration when you want to process the data returned from the query. This method does not require the use of the DataSet class. I'll post another sample after this that shows how to use a DataSet without having to process the records.

For this example, create a new Windows Application project.
Add

using System.Data.SqlClient;

to the top of the class so as to not have to type the namespace prefix everytime we do something inside SqlClient.
Add a button to the form. In the Click event handler for the form, add the following code:

try
{
   SqlConnection conn = new SqlConnection("server=(local);database=Northwind;trusted_connection=yes");
   conn.Open();
   SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
   SqlDataReader reader = cmd.ExecuteReader();
   // iterate through resultset and build a string list of all the customer contacts.
   string message = "The customers are: \r\n";
   while (reader.Read())
   {
      message += reader["ContactName"] + "\r\n";
   }
   TextBox txtOutput = new TextBox();
   txtOutput.Text = message;
   txtOutput.Multiline = true;
   txtOutput.Top = 30;
   txtOutput.Width = this.Width-20;
   txtOutput.Height = this.Height - txtOutput.Top - 40;
   txtOutput.Anchor = AnchorStyles.Left  | 
                      AnchorStyles.Right | 
                      AnchorStyles.Top   |
                      AnchorStyles.Bottom;
   txtOutput.ScrollBars = ScrollBars.Vertical;
   this.Controls.Add(txtOutput);
   txtOutput.BringToFront();
}
catch (SqlException ex)
{
   MessageBox.Show(ex.ToString());
}
catch (Exception ex)
{
   MessageBox.Show(ex.ToString());
}

This code is for SQLServer only. That is why we used the SqlConnection class.

 

[JeffTullin]

Well that's exciting. DataReader is a new one on me.
I assume the reader goes out of scope and closes/disposes of itself at the end of the function.
"While (reader.Read())" is pretty cool.

 

[AmazingGrace]

Hi,

As promised, here is a sample that uses a SqlDataReader to interate through records, and a DataSet to bind to a control (a grid in this case.)

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;


namespace SimpleDataSetExample
{
   /// <summary>
   /// Example of working with data retrieved through SQL.
   /// </summary>
   public class Form1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components = null;

   /// <summary>
   /// A screen that demonstates a method of binding data through
   /// a dataset and another that manually reads the records in a
   /// SqlDataReader
   /// </summary>
      public Form1()
      {
	      // Required for Windows Form Designer support
	      InitializeComponent();

	      // Add a button to retrieve SQL and put in a grid
	      Button btnRetrieveDS = new Button();
	      btnRetrieveDS.Width = 100;
	      btnRetrieveDS.Left = 20; 
	      btnRetrieveDS.Text = "Retrieve SQL";
	      btnRetrieveDS.Click += new EventHandler(btnRetrieveDS_Click);
	      this.Controls.Add(btnRetrieveDS);

	      // Add a button to retrieve SQL and interate through records
	      Button btnReadSQL = new Button();
	      btnReadSQL.Width = 125;
	      btnReadSQL.Left = btnRetrieveDS.Left + btnRetrieveDS.Width + 20;
	      btnReadSQL.Text = "Read SQL Results";
	      btnReadSQL.Click += new EventHandler(btnReadSQL_Click);
	      this.Controls.Add(btnReadSQL);
      }
   
      /// <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()
      {
      // 
      // Form1
      // 
      this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
      this.ClientSize = new System.Drawing.Size(288, 277);
      this.Name = "Form1";
      this.Text = "Form1";
      }
      #endregion

      /// <summary>
      /// The main entry point for the application.
      /// </summary>
      [STAThread]
      static void Main() 
      {
         Application.Run(new Form1());
      }

      private void btnRetrieveDS_Click(object sender, System.EventArgs e)
      {
         try
         {
            /* Use the Connection->Data Adapter->DataSet method to get
               a resultset and bind it to a control on the form */
            SqlConnection conn = new SqlConnection("server=(local);database=Northwind;trusted_connection=yes;");
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers", conn);
            DataSet ds = new DataSet("Customers");
            da.Fill(ds,"Customers");
            // Now create a datagrid and bind the results
            DataGrid grdResults = new DataGrid();
            grdResults.Top = 30;
            grdResults.Width = this.Width-20;
            grdResults.Height = this.Height - grdResults.Top - 40;
            grdResults.Anchor = AnchorStyles.Left  | 
                    AnchorStyles.Right | 
                    AnchorStyles.Top   |
                    AnchorStyles.Bottom;
            this.Controls.Add(grdResults);
            grdResults.BringToFront();
            grdResults.DataSource = ds.Tables[0];
         }
         catch (SqlException ex)
         {
            MessageBox.Show(ex.ToString());
         }
         catch (Exception ex)
         {
            MessageBox.Show(ex.ToString());
         }
      }

      private void btnReadSQL_Click(object sender, EventArgs e)
      {
         try
         {
            SqlConnection conn = new SqlConnection("server=(local);database=Northwind;trusted_connection=yes");
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            // iterate through resultset and build a string list of all the customer contacts
            string message = "The customers are: \r\n";
            while (reader.Read())
            {
            message += reader["ContactName"] + "\r\n";
            }
            TextBox txtOutput = new TextBox();
            txtOutput.Text = message;
            txtOutput.Multiline = true;
            txtOutput.Top = 30;
            txtOutput.Width = this.Width-20;
            txtOutput.Height = this.Height - txtOutput.Top - 40;
            txtOutput.Anchor = AnchorStyles.Left  | 
            AnchorStyles.Right | 
            AnchorStyles.Top   |
            AnchorStyles.Bottom;
            txtOutput.ScrollBars = ScrollBars.Vertical;
            this.Controls.Add(txtOutput);
            txtOutput.BringToFront();
         }
         catch (SqlException ex)
         {
            MessageBox.Show(ex.ToString());
         }
         catch (Exception ex)
         {
            MessageBox.Show(ex.ToString());
         }
      }
   }
}

 

[ebedard]

Hi,
im new with it and i still dont get it. Where in the pocket pc did you created your database named Northwind ? I created my own database (in access) and I synkronized it. The synkronization created a .cdb but i'm still searching how can I access those data. Can I create an ODBC somewhere into the pocket pc (Windows CE) ?

Im programming with c#.net Have you any exemple of how I can access a data from a database created with access that have been synchronized ?

Thanks a lot for your help.


Emmanuel