Csharp/CSharp Tutorial/ADO.Net/OleDbDataReader

Содержание

1 Get SchemaTable from OleDbDataReader
2 OleDbDataAdapter and SqlDataAdapter: Update
3 Read result set from OleDbDataReader
4 Typed Accessors
5 Use to OleDbDataReader retrieve data

Get SchemaTable from OleDbDataReader

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
  public class Form1 : System.Windows.Forms.Form
  {
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.TextBox textBox2;
        private System.Windows.Forms.TextBox textBox3;
        private System.Windows.Forms.TextBox textBox4;
    public Form1()
    {
            this.label1 = new System.Windows.Forms.Label();
            this.button1 = new System.Windows.Forms.Button();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.textBox2 = new System.Windows.Forms.TextBox();
            this.textBox3 = new System.Windows.Forms.TextBox();
            this.textBox4 = new System.Windows.Forms.TextBox();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 24F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
            this.label1.Location = new System.Drawing.Point(0, 0);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(232, 48);
            this.label1.TabIndex = 0;
            this.label1.Text = "Data Readers";
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(128, 232);
            this.button1.Name = "button1";
            this.button1.TabIndex = 1;
            this.button1.Text = "Read data";
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(8, 56);
            this.textBox1.Multiline = true;
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(72, 152);
            this.textBox1.TabIndex = 2;
            this.textBox1.Text = "";
            // 
            // textBox2
            // 
            this.textBox2.Location = new System.Drawing.Point(88, 56);
            this.textBox2.Multiline = true;
            this.textBox2.Name = "textBox2";
            this.textBox2.Size = new System.Drawing.Size(72, 152);
            this.textBox2.TabIndex = 3;
            this.textBox2.Text = "";
            // 
            // textBox3
            // 
            this.textBox3.Location = new System.Drawing.Point(168, 56);
            this.textBox3.Multiline = true;
            this.textBox3.Name = "textBox3";
            this.textBox3.Size = new System.Drawing.Size(72, 152);
            this.textBox3.TabIndex = 4;
            this.textBox3.Text = "";
            // 
            // textBox4
            // 
            this.textBox4.Location = new System.Drawing.Point(248, 56);
            this.textBox4.Multiline = true;
            this.textBox4.Name = "textBox4";
            this.textBox4.Size = new System.Drawing.Size(72, 152);
            this.textBox4.TabIndex = 5;
            this.textBox4.Text = "";
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(328, 273);
            this.Controls.Add(this.textBox4);
            this.Controls.Add(this.textBox3);
            this.Controls.Add(this.textBox2);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.label1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.ResumeLayout(false);
        }
    [STAThread]
    static void Main() 
    {
      Application.Run(new Form1());
    }
        private void button1_Click(object sender, System.EventArgs e)
        {
            string connection1String = "Provider=SQLOLEDB;Data Source=;User ID=sa;Initial Catalog=pubs;"; 
            OleDbConnection connection1 = new OleDbConnection(connection1String);
            OleDbCommand command1 = new OleDbCommand("select * from authors", connection1);
            connection1.Open();
            OleDbDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable schemaTable = reader1.GetSchemaTable();
            textBox1.Text += schemaTable.Rows[0][0].ToString() + "\r\n";
            textBox2.Text += schemaTable.Rows[1][0].ToString() + "\r\n";
            textBox3.Text += schemaTable.Rows[2][0].ToString() + "\r\n";
            textBox4.Text += schemaTable.Rows[3][0].ToString() + "\r\n";

            while (reader1.Read())
            {
                if (schemaTable.Rows[0][5].ToString() == "System.String") {
                    textBox1.Text += reader1.GetString(0) + "\r\n";
                }
                if (schemaTable.Rows[0][5].ToString() == "System.Boolean") {
                    textBox1.Text += reader1.GetBoolean(0).ToString() + "\r\n";
                }
                if (schemaTable.Rows[1][5].ToString() == "System.String") {
                    textBox2.Text += reader1.GetString(1) + "\r\n";
                }
                if (schemaTable.Rows[1][5].ToString() == "System.Boolean") {
                    textBox2.Text += reader1.GetBoolean(1).ToString() + "\r\n";
                }
                if (schemaTable.Rows[2][5].ToString() == "System.String") {
                    textBox3.Text += reader1.GetString(2) + "\r\n";
                }
                if (schemaTable.Rows[2][5].ToString() == "System.Boolean") {
                    textBox3.Text += reader1.GetBoolean(2).ToString() + "\r\n";
                    }
                if (schemaTable.Rows[3][5].ToString() == "System.String") 
                {
                    textBox4.Text += reader1.GetString(3) + "\r\n";
                }
                if (schemaTable.Rows[3][5].ToString() == "System.Boolean") {
                    textBox4.Text += reader1.GetBoolean(3).ToString() + "\r\n";
                }
            }
            reader1.Close();
            connection1.Close();
        }
  }

OleDbDataAdapter and SqlDataAdapter: Update

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
public class OleDbDataAdapterSqlDataAdapter : System.Windows.Forms.Form
{
  private System.Windows.Forms.Button OleDbDataAdapter;
  private System.Windows.Forms.Button SqlDataAdapter;
  private System.Windows.Forms.DataGrid dataGrid1;
  private System.ruponentModel.Container components = null;
  public OleDbDataAdapterSqlDataAdapter()
  {
    InitializeComponent();
  }
  protected override void Dispose( bool disposing )
  {
    if( disposing )
    {
      if (components != null) 
      {
        components.Dispose();
      }
    }
    base.Dispose( disposing );
  }
  private void InitializeComponent()
  {
    this.OleDbDataAdapter = new System.Windows.Forms.Button();
    this.SqlDataAdapter = new System.Windows.Forms.Button();
    this.dataGrid1 = new System.Windows.Forms.DataGrid();
    ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
    this.SuspendLayout();
    // 
    // OleDbDataAdapter
    // 
    this.OleDbDataAdapter.Location = new System.Drawing.Point(16, 16);
    this.OleDbDataAdapter.Name = "OleDbDataAdapter";
    this.OleDbDataAdapter.Size = new System.Drawing.Size(136, 32);
    this.OleDbDataAdapter.TabIndex = 0;
    this.OleDbDataAdapter.Text = "OleDb DataAdapter";
    this.OleDbDataAdapter.Click += new System.EventHandler(this.OleDbDataAdapter_Click);
    // 
    // SqlDataAdapter
    // 
    this.SqlDataAdapter.Location = new System.Drawing.Point(176, 16);
    this.SqlDataAdapter.Name = "SqlDataAdapter";
    this.SqlDataAdapter.Size = new System.Drawing.Size(168, 32);
    this.SqlDataAdapter.TabIndex = 1;
    this.SqlDataAdapter.Text = "SQL DataAdapter";
    this.SqlDataAdapter.Click += new System.EventHandler(this.SqlDataAdapter_Click);
    // 
    // dataGrid1
    // 
    this.dataGrid1.DataMember = "";
    this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
    this.dataGrid1.Location = new System.Drawing.Point(16, 72);
    this.dataGrid1.Name = "dataGrid1";
    this.dataGrid1.Size = new System.Drawing.Size(408, 192);
    this.dataGrid1.TabIndex = 2;
    // 
    // OleDbDataAdapterSqlDataAdapter
    // 
    this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
    this.ClientSize = new System.Drawing.Size(440, 273);
    this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                    this.dataGrid1,
                                    this.SqlDataAdapter,
                                    this.OleDbDataAdapter});
    this.Name = "OleDbDataAdapterSqlDataAdapter";
    this.Text = "OleDbDataAdapterSqlDataAdapter";
    ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
    this.ResumeLayout(false);
  }
  [STAThread]
  static void Main() 
  {
    Application.Run(new OleDbDataAdapterSqlDataAdapter());
  }
  private void OleDbDataAdapter_Click(object sender, System.EventArgs e)
    {
        string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
        string SQL = "SELECT * FROM Customers";
        OleDbConnection conn = new OleDbConnection(ConnectionString);
        conn.Open();
        
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = new OleDbCommand(SQL, conn);
    
        DataSet ds = new DataSet("Customers");         
        adapter.Fill(ds, "Customers");
    adapter.DeleteCommand = new OleDbCommand("DELETE * FROM Customers");
    
    adapter.Update(ds, "Customers");
    dataGrid1.DataSource = ds.DefaultViewManager;
    }
  private void SqlDataAdapter_Click(object sender, System.EventArgs e)
    {
    
        string ConnectionString ="Integrated Security=SSPI;" +
            "Initial Catalog=Northwind;" +
            "Data Source=localhost;";
        string SQL = "SELECT CustomerID, CompanyName FROM Customers";
        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL, conn);
        DataSet ds = new DataSet("Customers");
        adapter.Fill(ds);
        dataGrid1.DataSource = ds.DefaultViewManager;           
    }
}

Read result set from OleDbDataReader

using System;
using System.Data;
using System.Data.OleDb;
class MainClass
{
   static void Main(string[] args)
   {
      string connString = "provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
      string sql = @"select * from employee";
      OleDbConnection conn = null;
      OleDbDataReader reader = null;
      try
      {
         conn = new OleDbConnection(connString);
         conn.Open();
         OleDbCommand cmd = new OleDbCommand(sql, conn);
         reader = cmd.ExecuteReader();
         Console.WriteLine("Querying database {0} with query {1}\n", conn.Database, cmd.rumandText );
         while(reader.Read()) {
            Console.WriteLine("{0} | {1}", reader["FirstName"].ToString().PadLeft(10), reader[1].ToString().PadLeft(10));
         }
      }
      catch (Exception e)
      {
         Console.WriteLine("Error: " + e);
      }
      finally
      {
         reader.Close();
         conn.Close();
      }
   }
}

Typed Accessors

using System;
using System.Data;
using System.Data.SqlClient;
    class TypedAccessors
    {
        static void Main(string[] args)
        {
            string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
            string sql = @"select productname, unitprice, unitsinstock,discontinued from products";
            SqlConnection conn = new SqlConnection(connString);
            try{
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine(rdr.GetString(0));
                    Console.WriteLine(rdr.GetDecimal(1));
                    Console.WriteLine(rdr.GetInt16(2));
                    Console.WriteLine(rdr.GetBoolean(3));
                }
                rdr.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error Occurred: " + e);
            }
            finally
            {
                conn.Close();
            } 
        }
    }

Use to OleDbDataReader retrieve data

using System;
using System.Data;
using System.Data.OleDb;
class MainClass
{
  static void Main(string[] args)
  {
        string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
        string SQL = "SELECT * FROM Orders";
        OleDbConnection conn = new OleDbConnection(ConnectionString);       
        OleDbCommand cmd = new OleDbCommand(SQL);
        cmd.Connection = conn;
        conn.Open();
        OleDbDataReader reader = cmd.ExecuteReader();
        try 
        {
            while (reader.Read()) 
            {
                Console.Write("OrderID:"+reader.GetInt32(0).ToString() );
                Console.Write(" ,");
                Console.WriteLine("Customer:" + reader.GetString(1).ToString() );
            }
        }
        finally 
        {
            reader.Close();
            conn.Close();
        }   
  }
}

OrderID:10330 ,Customer:LILAS
OrderID:10331 ,Customer:BONAP
OrderID:10332 ,Customer:MEREP
OrderID:10333 ,Customer:WARTH
OrderID:10334 ,Customer:VICTE
...
...

Csharp/CSharp Tutorial/ADO.Net/OleDbDataReader

Содержание

Get SchemaTable from OleDbDataReader

OleDbDataAdapter and SqlDataAdapter: Update

Read result set from OleDbDataReader

Typed Accessors

Use to OleDbDataReader retrieve data

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация