Csharp/CSharp Tutorial/ADO.Net/OleDbDataReader — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 15:19, 26 мая 2010
Содержание
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 ... ...