1

• Week 8-1

2

ADO .NET (2)

These slides are meant to be for teaching purposes only and only for the students that are registered in CSE4413 and should not be published as a

book or in any form of commercial product, unless written permission is obtained.

3

Another example (modify the DB)

The Course table retrieved.

Upon entering a course number (e.g., 778) and clicking the button, the course

(“New course”, 778, “Computer Sciences”) is added to the Course

table.

4

.../

5

The related code.

The button event

dataSet1 holds the Course table before the insertion.

dataSet2 holds the Course table after the insertion.

6

The code …

public Form1()

{InitializeComponent();

oleDbDataAdapter1.Fill( dataSet1, "res");

dataGrid1.SetDataBinding( dataSet1, "res");

}

Retrieve and display the table before the

insertion.

7

The code …/private void button1_Click(object sender, System.EventArgs e){

string theInsertSQL;theInsertSQL = "INSERT INTO Course (cname, cno, dname) "

+ " VALUES ( 'New Course' , "+ textBox1.Text + " , 'Computer Sciences' )";

oleDbDataAdapter1.InsertCommand.CommandText = theInsertSQL;

oleDbConnection1.Open();

oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();

dataSet2.Clear(); // clear the accepting dataSet … just in case (no need here).

oleDbDataAdapter1.Fill( dataSet2, "res");dataGrid2.SetDataBinding( dataSet2, "res");

}

Load query results in dataSet and and display on

dataGrid.

Assemble query

Establish connection to DB

Execute query

8

Extract individual values from a DataGrid

• Can extract individual values from a DataGrid(using classes DataTable, DataRow, DataColumn).

DataTable myTable = dataGrid1.DataSource;

private void PrintValues(DataTable myTable){

foreach( DataRow myRow in myTable.Rows){

foreach( DataColumn myCol in myTable.Columns){

Console.WriteLine(myRow[myCol]);

}

}

}

9

ADO and XML

• Once the data is in the DataSet, it can be converted to XML. – The XML can be written to a file

• dataSet1.WriteXml( "Course.xml") or– Can be displayed in the GUI

• dataSet1.GetXml( )

10

Example …

11

XML file generated

12

The design

Generates XML and writes it in file and also in TextBox

TextBox that displays XML

Retrieves Course table

Gets filled with Course table.

DataGrid

13

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

namespace WindowsApplication2ADOXML{

/// <summary>/// Summary description for Form1./// </summary>public class Form1 : System.Windows.Forms.Form{

private System.Data.OleDb.OleDbConnection oleDbConnection1;private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;private System.Data.DataSet dataSet1;private System.Windows.Forms.DataGrid dataGrid1;private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;private System.Windows.Forms.Button button1;private System.Windows.Forms.TextBox textBox1;/// <summary>/// Required designer variable./// </summary>private System.ComponentModel.Container components = null;

public Form1(){

//// Required for Windows Form Designer support//InitializeComponent();

//// TODO: Add any constructor code after InitializeComponent call//oleDbDataAdapter1.Fill( dataSet1, "res");dataGrid1.SetDataBinding( dataSet1, "res");

}

The code …

14

…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>

15

…private void InitializeComponent(){

this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();this.dataSet1 = new System.Data.DataSet();this.dataGrid1 = new System.Windows.Forms.DataGrid();this.button1 = new System.Windows.Forms.Button();this.textBox1 = new System.Windows.Forms.TextBox();((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();

((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();this.SuspendLayout();// // oleDbConnection1// this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk

Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""E:\4413 -- e-commerce course\__WINTER 2006\MySlides\_Slides _3 ADO NET slides\ADO .NET My code tests\MyStudentsDB.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";

// // oleDbDataAdapter1// this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;this.oleDbDataAdapter1.TableMappings.AddRange(new

System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping("Table", "Course", new

System.Data.Common.DataColumnMapping[] {

16

…new System.Data.Common.DataColumnMapping("cname", "cname"),new System.Data.Common.DataColumnMapping("cno", "cno"),

new System.Data.Common.DataColumnMapping("dname", "dname")})});// // oleDbInsertCommand1//

this.oleDbInsertCommand1.CommandText = "INSERT INTO Course(cname, cno, dname) VALUES (?, ?, ?)";this.oleDbInsertCommand1.Connection = this.oleDbConnection1;this.oleDbInsertCommand1.Parameters.Add(new

System.Data.OleDb.OleDbParameter("cname", System.Data.OleDb.OleDbType.VarWChar, 255, "cname"));this.oleDbInsertCommand1.Parameters.Add(new

System.Data.OleDb.OleDbParameter("cno", System.Data.OleDb.OleDbType.Integer, 0, "cno"));this.oleDbInsertCommand1.Parameters.Add(new

System.Data.OleDb.OleDbParameter("dname", System.Data.OleDb.OleDbType.VarWChar, 255, "dname"));// // oleDbSelectCommand1// this.oleDbSelectCommand1.CommandText = "SELECT cname, cno, dname FROM Course";this.oleDbSelectCommand1.Connection = this.oleDbConnection1;// // dataSet1// this.dataSet1.DataSetName = "NewDataSet";this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US");// // dataGrid1//

this.dataGrid1.AlternatingBackColor = System.Drawing.Color.WhiteSmoke;

this.dataGrid1.BackColor = System.Drawing.Color.Gainsboro;this.dataGrid1.BackgroundColor = System.Drawing.Color.DarkGray;this.dataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;this.dataGrid1.CaptionBackColor = System.Drawing.Color.DarkKhaki;this.dataGrid1.CaptionFont = new System.Drawing.Font("Tahoma", 8F,

System.Drawing.FontStyle.Bold);this.dataGrid1.CaptionForeColor = System.Drawing.Color.Black;this.dataGrid1.CaptionText = "Course";this.dataGrid1.DataMember = "";this.dataGrid1.FlatMode = true;this.dataGrid1.Font = new System.Drawing.Font("Times New Roman", 9F);this.dataGrid1.ForeColor = System.Drawing.Color.Black;this.dataGrid1.GridLineColor = System.Drawing.Color.Silver;this.dataGrid1.HeaderBackColor = System.Drawing.Color.Black;

this.dataGrid1.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);

17

…this.dataGrid1.HeaderForeColor = System.Drawing.Color.White;this.dataGrid1.LinkColor = System.Drawing.Color.DarkSlateBlue;this.dataGrid1.Location = new System.Drawing.Point(16, 24);this.dataGrid1.Name = "dataGrid1";this.dataGrid1.ParentRowsBackColor = System.Drawing.Color.LightGray;this.dataGrid1.ParentRowsForeColor = System.Drawing.Color.Black;this.dataGrid1.SelectionBackColor = System.Drawing.Color.Firebrick;this.dataGrid1.SelectionForeColor = System.Drawing.Color.White;this.dataGrid1.Size = new System.Drawing.Size(400, 128);this.dataGrid1.TabIndex = 0;// // button1this.button1.Font = new System.Drawing.Font("Microsoft Sans Serif", 10F,

System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(161)));this.button1.Location = new System.Drawing.Point(16, 168);this.button1.Name = "button1";this.button1.Size = new System.Drawing.Size(136, 24);this.button1.TabIndex = 1;

this.button1.Text = "Make XML";this.button1.Click += new System.EventHandler(this.button1_Click);

// // textBox1this.textBox1.AcceptsReturn = true;this.textBox1.AcceptsTab = true;this.textBox1.AllowDrop = true;this.textBox1.Location = new System.Drawing.Point(24, 208);this.textBox1.Multiline = true;this.textBox1.Name = "textBox1";this.textBox1.ScrollBars = System.Windows.Forms.ScrollBars.Both;this.textBox1.Size = new System.Drawing.Size(392, 144);this.textBox1.TabIndex = 2;this.textBox1.Text = "textBox1";// // Form1this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);this.ClientSize = new System.Drawing.Size(432, 357);this.Controls.Add(this.textBox1);this.Controls.Add(this.button1);this.Controls.Add(this.dataGrid1);this.Name = "Form1";this.Text = "Form1";((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();this.ResumeLayout(false);

}#endregion

18

…//// <summary>/// The main entry point for the application./// </summary>[STAThread]static void Main() {

Application.Run(new Form1());}

private void button1_Click(object sender, System.EventArgs e){

// dataSet1.WriteXml( "Course.xml");

textBox1.Text = "Writing the following XML: \r\n" + dataSet1.GetXml( )+ "\r\n";

}}

}

generate XML corresponding to data residing in dataSet1 and andwrite it in file Course.xml.

Generate XML that corresponds to data residing

in dataSet1.

19

The generated Course.xml file is independently accessible …

20

• The end