1 / 33

Coding ADO.NET Objects: Connection, Command, DataReader

Coding ADO.NET Objects: Connection, Command, DataReader. A Simplified View of ADO.Net Objects. Ado.Net. Data Provider. Data Consumer. Adapter. Dataset. WinForm. SQL Updates. Connection. Command. WebForm. Results of SQL Selects. Reader. Connection with a Connection Object.

zion
Download Presentation

Coding ADO.NET Objects: Connection, Command, DataReader

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Coding ADO.NET Objects:Connection, Command, DataReader

  2. A Simplified View of ADO.Net Objects Ado.Net Data Provider Data Consumer Adapter Dataset WinForm SQL Updates Connection Command WebForm Results of SQL Selects Reader

  3. Connection with a Connection Object • A connection object represents a unique session with a data source. • Property: • Connection string: string used to open a database. • Data source, OLE DB provider, password, if any, security, etc. • Methods: • Use the Open/Close method to open/close a connection.

  4. Connection String • Containing information about database, OLE DB provider, password, if any, security, etc. • For Jet database: • ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb“ • For Access 2007, 2011: • = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2011.accdb“ • For SQL Server Express • "Data Source=David-PC\sqlexpress;Initial Catalog=testSQL08;Integrated Security=True"

  5. Connection Object • Example 1 – Jet Database: • string strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\\salesDB.mdb"; • OleDbConnection objConn = new OleDbConnection(strConn); • objConn.Open(); • Example 2 – Access 2011: • string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; • OleDbConnection objConn = new OleDbConnection(strConn); • objConn.Open(); • Object Browser: System.Data.OleDB/oledbConnection/New • Basic Methods: • Open, Close • BeginTransaction

  6. Command Object • The command object allows us to execute a SQL statement. • Properties: • CommandType: Text, stored procedure, tableDirect • CommandText: SQL statement, procedure name, table name • Connection • Other properties: • Parameters • Basic Methods: • ExecuteReader: Creates a DataReader object that contains the results of the query. • ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Typically used to execute a SQL aggregate function such as SUM, MAX, MIN, etc. • ExecuteNonQuery: Change the data in a database without using a DataSet by executing SQL’s UPDATE, INSERT, or DELETE statements.

  7. DataReader Object • It is read-only and forward-only cursor. • Basic Methods: • Read: Reads the current record and advances the pointer to the next record. It returns False if no more record in the DataReader. • Close: Closes the dataReader. • Property: • HasRows – Boolean, indicates whether this System.Data.Common.DbDataReader contains one or more rows.

  8. ExecuteReader Example string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL,objConn); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.HasRows) { MessageBox.Show("Data reader contains rows"); } else { MessageBox.Show("No row reurns"); } Note: No NEW in the Dim statement when define a dataReader object.

  9. Import NameSpace • The “using” statement must appear before all other declarations in a file and cannot appear inside a class or module declaration. • using System.Data.OleDb;

  10. Read Records in a DataReader • string Results=""; • while (objDataReader.Read()==true) • { • Results+=objDataReader["cid"] + " " + objDataReader["Cname"] + Environment.NewLine; • } • textBox1.Text = Results.ToString(); • Note: Use index to read a column - objDataReader[0] • Note: The Read method will (1) read a record, (2) advance pointer, (3) return true/false to indicate if more records exist.

  11. DataReader Example string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL,objConn); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); string Results=""; while (objDataReader.Read()==true) { Results += objDataReader["cid"] + " " + objDataReader["Cname"] + Environment.NewLine; } textBox1.Text = Results.ToString();

  12. Add Items from a DataReader to a Listbox string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select cid from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader=objComm.ExecuteReader(); while (objDataReader.Read()==true) { listBox1.Items.Add(objDataReader["cid"]); // listBox1.Items.Add(objDataReader.GetString(0)); } Note1: Listbox is not bound. 2. SelectedItem

  13. Display Selected Customer Info in Textbox private void listBox1_SelectedIndexChanged(object sender, EventArgs e) { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select * from customer where cid='" + listBox1.SelectedItem + "'"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); objDataReader.Read(); textBox1.Text = objDataReader["Cname"].ToString(); textBox2.Text = objDataReader["rating"].ToString(); objConn.Close(); }

  14. Using a Parallel ArrayList to Store Cname and Rating • ArrayList • Add • Clear • Remove • IndexOf • Must add this reference: • using System.Collections;

  15. Using a Parallel ArrayList ArrayList CnameList = new ArrayList(),RatingList = new ArrayList(); private void Form1_Load(object sender, EventArgs e) { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select * from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); CnameList.Add(objDataReader["cname"]); RatingList.Add(objDataReader["rating"]); } objConn.Close(); }

  16. Use Listbox SelectedIndex private void listBox1_SelectedIndexChanged(object sender, EventArgs e) { textBox1.Text = CnameList[listBox1.SelectedIndex].ToString(); textBox2.Text = RatingList[listBox1.SelectedIndex].ToString(); }

  17. Use SQL to Retrieve Summaries string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select count(cid) as custCount from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.Read() == true) { MessageBox.Show(objDataReader["custCount"].ToString()); } objConn.Close(); Note: Alias ----- select count(cid) as custCount

  18. Using Command’s ExecuteScalar to Run SQL Aggregates string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select count(cid) as custCount from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close();

  19. private void radioButton_CheckedChanged(object sender, EventArgs e) { string strSQL; if (radioButton1.Checked) { strSQL = "select count(pid) as prodCount from product;"; } else if (radioButton2.Checked) { strSQL = "select max(price) as maxPrice from product;"; } else { strSQL = "select min(price) as minPrice from product;"; } string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); OleDbCommand objComm = new OleDbCommand(strSQL, objConn); objConn.Open(); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close(); } ExecuteScalar Example

  20. Testing for Null:DBNull.Value if ( objDataReader["rating"]==DBNull.Value) { textBox2.Text = "NA"; } else { textBox2.Text = objDataReader["rating"].ToString(); }

  21. Error Handling string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select cid from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); try { objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); Note: Where do declare objConn?

  22. Command Object’s ExecuteNonQuery Method • To run SQL: • Insert • Delete • Update • The ExecuteNonQuery method also returns a value indicating the number of records affected by the SQL statement.

  23. Use ExecuteNonQuery to Insert A New Record Create unbound text boxes to enter new customer record. string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQLInsert; strSQLInsert = "Insert into Customer values ('"; strSQLInsert += textBox1.Text + "','" + textBox2.Text + "','"; strSQLInsert += textBox3.Text + "','" + textBox4.Text + "')"; OleDbCommand objCommInsert= new OleDbCommand(strSQLInsert, objConn); objConn.Open(); MessageBox.Show(strSQLInsert.ToString()); try { objCommInsert.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } objConn.Close();

  24. Use ExecuteNonQuery to Delete A New Record • Create a listbox with CIDs • Delete the selected record and remove the CID from the listbox. string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "delete from customer where cid = '" + listBox1.SelectedItem + "'"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); try { int affectedRecords; objConn.Open(); affectedRecords= objComm.ExecuteNonQuery(); MessageBox.Show(affectedRecords.ToString() + "records deleted"); listBox1.Items.RemoveAt(listBox1.SelectedIndex); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close();

  25. Use ExecuteNonQuery to Update A New Record • Create a project that do the following tasks: • Use a DataReader to retrieve customer IDs and populate a listbox. • Select a new rating from radio buttons for the selected customer. • Update customer’s rating using the ExecuteNonQuery method of a Command object.

  26. create CID listbox: private void Form4_Load(object sender, EventArgs e) { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "select cid from customer;"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); try { objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }

  27. Update customer rating: private void button1_Click(object sender, EventArgs e) { string newRating; if (radioButton1.Checked) { newRating = "A"; } else if (radioButton2.Checked) { newRating = "B"; } else { newRating = "C"; } string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\SalesDB2011.accdb"; OleDbConnection objConn = new OleDbConnection(strConn); string strSQL = "Update customer set rating = '" + newRating + "' where cid='" + listBox1.SelectedItem + "'"; OleDbCommand objComm = new OleDbCommand(strSQL, objConn); try { objConn.Open(); objComm.ExecuteNonQuery(); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }

  28. Working with ODBC Data Source • Enter: using System.Data.Odbc; • Use: • OdbcConnection: Use DSN • Example of connection string: • string strConn = "DSN=test32SQLDS"; • OdbcCommand • OdbcDataReader

  29. using System.Data.Odbc; private void Form5_Load(object sender, EventArgs e) { string strConn = "DSN=test32SQLDS"; OdbcConnection objConn = new OdbcConnection(strConn); string strSQL = "select cid from customer;"; OdbcCommand objComm = new OdbcCommand(strSQL, objConn); try { objConn.Open(); OdbcDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }

  30. Possible Database Errors • SQL syntax errors • Database connection not open • Null • Violate database constraints • Referential integrity • Field data type and length • Primary key constraint • Invalid data – database may have validation rules.

  31. Setting a Field to Null with a Update Statement string strSQL = "Update customer set city = null where cid='" + listBox1.SelectedItem + "'";

  32. Working with SQL Server Database • Enter: using System.Data.SqlClient; • Use: • SqlConnection: • Example of connection string: • string strConn = "Data Source=David-PC\\SQLEXPRESS;Initial Catalog=TestSQLServer;Integrated Security=True;Pooling=False"; • SqlCommand • SqlDataReader

  33. Working with SQL Server Data Source private void Form5_Load(object sender, EventArgs e) { string strConn = "Data Source=David-PC\\SQLEXPRESS;Initial Catalog=TestSQLServer;Integrated Security=True;Pooling=False"; SqlConnection objConn = new SqlConnection(strConn); string strSQL = "select cid from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); try { objConn.Open(); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }

More Related