Csharp/CSharp Tutorial/ADO.Net/OleDbDataReader
Содержание
Get SchemaTable from OleDbDataReader
<source lang="csharp">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();
}
}</source>
OleDbDataAdapter and SqlDataAdapter: Update
<source lang="csharp">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;
}
}</source>
Read result set from OleDbDataReader
<source lang="csharp">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();
}
}
}</source>
Typed Accessors
<source lang="csharp">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();
}
}
}</source>
Use to OleDbDataReader retrieve data
<source lang="csharp">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();
}
}
}</source>
OrderID:10330 ,Customer:LILAS OrderID:10331 ,Customer:BONAP OrderID:10332 ,Customer:MEREP OrderID:10333 ,Customer:WARTH OrderID:10334 ,Customer:VICTE ... ...
