Coding ADO.NET Objects: Connection, Command, DataReader

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. Import NameSpace • For MS Access accdb: • using System.Data.OleDb; • For SQL Express: • using System.Data.SqlClient;

4. 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.

5. Connection String • Containing information about database, OLE DB provider, password, if any, security, etc. • For ODBC DSN: SalesDB • =“DSN=SalesDBDSN“ • For Access 2011, 2013: • = "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“ • For SQL Server Express LocalDB • Data Source=(localdb)\projects;Initial Catalog=SalesDB;Integrated Security=True

6. Define Connection Object • Example 1 – Access accdb: • string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CSharpexamples\\SalesDB2011.accdb"; • OleDbConnection objConn = new OleDbConnection(strConn); • objConn.Open(); • Example 2 – SQL Express LocalDB • string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; • SqlConnection objConn = new SqlConnection(strConn); • objConn.Open(); • Basic Methods: • Open, Close • BeginTransaction • Note: The “\\” is very important

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

8. 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.

9. ExecuteReader Example string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.HasRows) { MessageBox.Show("Data reader contains rows"); } else { MessageBox.Show("No row reurns"); }

10. Read a Record in a DataReader • DataReader’s Read method: • The Read method will (1) read a record, (2) advance pointer, (3) return true/false to indicate if more records exist. • To read a field: • DataReader[“field name’].ToString(); • or • DataReader[field index].ToString();

11. Example: Display One Record at a Time

12. Form Load Event SqlDataReader objDataReader; private void Form5_Load(object sender, EventArgs e) { string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); // SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.HasRows) { MessageBox.Show("Data reader contains rows"); objDataReader.Read(); textBox1.Text = objDataReader["CID"].ToString(); textBox2.Text = objDataReader["Cname"].ToString(); } else { MessageBox.Show("No row reurns"); } }

13. Button Event private void button1_Click(object sender, EventArgs e) { if (objDataReader.Read()) { textBox1.Text = objDataReader["CID"].ToString(); textBox2.Text = objDataReader["Cname"].ToString(); } else MessageBox.Show("No more data"); }

14. Use Loop to Read Records in a DataReader • string Results=""; • while (objDataReader.Read()==true) • { • Results+=objDataReader["cid"] + " " + objDataReader["Cname"] + Environment.NewLine; • } • textBox1.Text = Results.ToString();

15. DataReader Example string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); string Results = ""; while (objDataReader.Read() == true) { Results += objDataReader["cid"] + " " + objDataReader["Cname"] + Environment.NewLine; } textBox1.Text = Results.ToString();

16. Using Field Index string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); string Results = ""; while (objDataReader.Read() == true) { Results += objDataReader[0] + " " + objDataReader[1] + Environment.NewLine; } textBox1.Text = Results.ToString();

17. Add Items from a DataReader to a Listbox string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader 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

18. Display Selected Customer Info in Textbox private void listBox1_SelectedIndexChanged(object sender, EventArgs e) { string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer where cid='" + listBox1.SelectedItem + "'"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); objDataReader.Read(); textBox1.Text = objDataReader["Cname"].ToString(); textBox2.Text = objDataReader["rating"].ToString(); objConn.Close(); }

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

20. Using a Parallel ArrayList ArrayList CnameList = new ArrayList(), RatingList = new ArrayList(); private void Form5_Load(object sender, EventArgs e) { string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); CnameList.Add(objDataReader["cname"]); RatingList.Add(objDataReader["rating"]); } }

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

22. Use SQL to Retrieve Summaries string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select count(cid) as custCount from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.Read() == true) { MessageBox.Show(objDataReader["custCount"].ToString()); } objConn.Close(); Note: Alias ----- select count(cid) as custCount

23. Using Command’s ExecuteScalar to Run SQL Aggregates string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select count(cid) as custCount from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close();

24. 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 = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); SqlCommand objComm = new SqlCommand(strSQL, objConn); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close(); } ExecuteScalar Example

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

26. string strConn = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); objConn.Open(); string strSQL = "select * from customer;"; SqlCommand objComm = new SqlCommand(strSQL, objConn); SqlDataReader objDataReader; objDataReader = objComm.ExecuteReader(); dataGridView1.ColumnCount = 4; dataGridView1.Columns[0].HeaderText = "CID"; dataGridView1.Columns[1].HeaderText = "Cname"; dataGridView1.Columns[2].HeaderText = "City"; dataGridView1.Columns[3].HeaderText = "Rating"; dataGridView1.Rows.Clear(); int rowIndex = 0; while (objDataReader.Read() == true) { dataGridView1.Rows.Add(); dataGridView1.Rows[rowIndex].Cells[0].Value = objDataReader["cid"]; dataGridView1.Rows[rowIndex].Cells[1].Value = objDataReader["cname"]; dataGridView1.Rows[rowIndex].Cells[2].Value = objDataReader["city"]; dataGridView1.Rows[rowIndex].Cells[3].Value = objDataReader["rating"]; rowIndex += 1; } objConn.Close(); Display Records with DataGridView

27. Error Handling: Try/Catch 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 = "Data Source=(localdb)\\projects;Initial Catalog=SalesDB;Integrated Security=True"; SqlConnection objConn = new SqlConnection(strConn); try { objConn.Open(); SqlCommand objComm = new SqlCommand(strSQL, objConn); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close(); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } Note: Where do declare objConn?

28. 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.

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

30. using System.Data.Odbc; private void Form5_Load(object sender, EventArgs e) { string strConn = "DSN=SalesDBDSN"; 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(); }