c# tutorial msm_murach chapter-20-slides
Post on 06-Apr-2017
16 Views
Preview:
TRANSCRIPT
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 1
Chapter 20
How to use ADO.NET to write your own data access code
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 2
Objectives Applied 1. Use a data reader to retrieve data from a database. 2. Use data commands to execute action queries or queries that
return a scalar value. 3. Use parameters to limit the data that’s processed by a data
command.
Knowledge 1. Describe the use of parameters with SQL statements. 2. Describe the use of a data reader. 3. Describe the use of the two types of queries that don’t return
result sets.
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 3
Two constructors for the SqlConnection class new SqlConnection() new SqlConnection(connectionString)
Common properties and methods of the SqlConnection class ConnectionString Open() Close()
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 4
Common values used in the ConnectionString property for SQL Server Data source/Server Initial catalog/Database Integrated security User ID Password/Pwd Persist security info Workstation ID
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 5
A connection string for the SQL Server provider Data Source=localhost\\SqlExpress; Initial Catalog=MMABooks; Integrated Security=True
A connection string for the Jet OLE DB provider Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Databases\\MMABooks.mdb
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 6
Code that creates, opens, and closes a SQL connection
string connectionString = "Data Source= localhost\\SqlExpress; Initial Catalog=MMABooks;" + "Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionString); connection.Open(); ... connection.Close();
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 7
Three constructors for the SqlCommand class new SqlCommand() new SqlCommand(commandText) new SqlCommand(commandText, connection)
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 8
Common properties of the SqlCommand class Connection CommandText CommandType Parameters
Common methods of the SqlCommand class ExecuteReader() ExecuteNonQuery() ExecuteScalar()
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 9
CommandType enumeration members Text StoredProcedure TableDirect
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 10
Code that creates a SqlCommand object that executes a Select statement SqlConnection connection = new SqlConnection(connectionString); string selectStatement = "SELECT CustomerID, Name, Address, City, State, ZipCode " + "FROM Customers"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 11
A SQL Server Select statement that uses a parameter
SELECT CustomerID, Name, Address, City, State, ZipCode FROM Customers WHERE CustomerID = @CustomerID
An Oracle Select statement that uses a parameter SELECT CustomerID, Name, Address, City, State, ZipCode FROM Customers WHERE CustomerID = :CustomerID
An OLE DB or ODBC Select statement that uses a parameter
SELECT CustomerID, Name, Address, City, State, ZipCode FROM Customers WHERE CustomerID = ?
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 12
A SQL Server Insert statement that uses parameters
INSERT INTO Customers (Name, Address, City, State, ZipCode) VALUES (@Name, @Address, @City, @State, @ZipCode)
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 13
Four constructors for the SqlParameter class new SqlParameter() new SqlParameter(name, value) new SqlParameter(name, type) new SqlParameter(name, type, size)
Common properties of the SqlParameter class DbType ParameterName Size SqlDbType Value
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 14
Code that creates a parameter SqlParameter customerIDParm = new SqlParameter(); customerIDParm.ParameterName = "@CustomerID"; customerIDParm.Value = customerID;
Another way to create a parameter SqlParameter customerIDParm = new SqlParameter("@CustomerID", customerID);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 15
Common indexers of the Parameters collection [parametername] [index]
Common methods of the Parameters collection Add(parameter) Add(name, type) Add(name, type, size) AddWithValue(name, value)
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 16
A statement that adds a parameter to the Parameters collection
selectCommand.Parameters.Add(customerIDParm);
A statement that creates a parameter and adds it to the Parameters collection
selectCommand.Parameters.AddWithValue( "@CustomerID", customerID);
A statement that changes the value of an existing parameter
selectCommand.Parameters["@CustomerID"]. Value = customerID;
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 17
Two ways to create a SqlDataReader object sqlCommand.ExecuteReader() sqlCommand.ExecuteReader(behavior)
Common CommandBehavior enumeration members CloseConnection Default SingleRow
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 18
Common indexers of the SqlDataReader class [columnname] [index]
Common property of the SqlDataReader class IsClosed
Common methods of the SqlDataReader class Close() Read()
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 19
Code that uses a data reader to read a list of State objects
connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader( CommandBehavior.CloseConnection); List<State> states = new List<State>(); while (reader.Read()) { State s = new State(); s.StateCode = reader["StateCode"].ToString(); s.StateName = reader["StateName"].ToString(); states.Add(s); } reader.Close();
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 20
Code that creates and executes a command that returns an aggregate value
string selectStatement = "SELECT SUM(InvoiceTotal) FROM Invoices"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); connection.Open(); decimal invoiceTotal = (decimal) selectCommand.ExecuteScalar; connection.Close();
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 21
Code that inserts a row string insertStatement = "INSERT Products " + "(ProductCode, Description, UnitPrice) " + "VALUES (@ProductCode, @Description, @UnitPrice)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@ProductCode", product.Code); insertCommand.Parameters.AddWithValue( "@Description", product.Description); insertCommand.Parameters.AddWithValue( "@UnitPrice", product.Price); try { connection.Open(); int productCount = insertCommand.ExecuteNonQuery(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 22
The Customer Maintenance form
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 23
The Add/Modify Customer form
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 24
The dialog box that’s displayed to confirm a delete operation
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 25
The class diagram
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 26
The code for the CustomerDB class public static class CustomerDB { public static Customer GetCustomer(int customerID) { SqlConnection connection = MMABooksDB.GetConnection(); string selectStatement = "SELECT CustomerID, Name, Address, City, State, ZipCode " + "FROM Customers " + "WHERE CustomerID = @CustomerID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue( "@CustomerID", customerID); try {
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 27
The code for the CustomerDB class (cont.) connection.Open(); SqlDataReader custReader = selectCommand.ExecuteReader( CommandBehavior.SingleRow); if (custReader.Read()) { Customer customer = new Customer(); customer.CustomerID = (int)custReader["CustomerID"]; customer.Name = custReader["Name"].ToString(); customer.Address = custReader["Address"].ToString(); customer.City = custReader["City"].ToString(); customer.State = custReader["State"].ToString(); customer.ZipCode = custReader["ZipCode"].ToString(); return customer; }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 28
The code for the CustomerDB class (cont.) else { return null; } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 29
The code for the CustomerDB class (cont.) public static int AddCustomer(Customer customer) { SqlConnection connection = MMABooksDB.GetConnection(); string insertStatement = "INSERT Customers " + "(Name, Address, City, State, ZipCode) " + "VALUES (@Name, @Address, @City, @State, @ZipCode)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@Name", customer.Name); insertCommand.Parameters.AddWithValue( "@Address", customer.Address); insertCommand.Parameters.AddWithValue( "@City", customer.City); insertCommand.Parameters.AddWithValue( "@State", customer.State); insertCommand.Parameters.AddWithValue( "@ZipCode", customer.ZipCode);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 30
The code for the CustomerDB class (cont.) try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Customers') FROM Customers"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int customerID = Convert.ToInt32( selectCommand.ExecuteScalar()); return customerID; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 31
The code for the CustomerDB class (cont.) public static bool UpdateCustomer(Customer oldCustomer, Customer newCustomer) { SqlConnection connection = MMABooksDB.GetConnection(); string updateStatement = "UPDATE Customers SET " + "Name = @NewName, " + "Address = @NewAddress, " + "City = @NewCity, " + "State = @NewState, " + "ZipCode = @NewZipCode " + "WHERE Name = @OldName " + "AND Address = @OldAddress " + "AND City = @OldCity " + "AND State = @OldState " + "AND ZipCode = @OldZipCode";
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 32
The code for the CustomerDB class (cont.) SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@NewName", newCustomer.Name); updateCommand.Parameters.AddWithValue( "@NewAddress", newCustomer.Address); updateCommand.Parameters.AddWithValue( "@NewCity", newCustomer.City); updateCommand.Parameters.AddWithValue( "@NewState", newCustomer.State); updateCommand.Parameters.AddWithValue( "@NewZipCode", newCustomer.ZipCode); updateCommand.Parameters.AddWithValue( "@OldName", oldCustomer.Name); updateCommand.Parameters.AddWithValue( "@OldAddress", oldCustomer.Address); ... ... updateCommand.Parameters.AddWithValue( "@OldZipCode", oldCustomer.ZipCode);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 33
The code for the CustomerDB class (cont.) try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 34
The code for the CustomerDB class (cont.) public static bool DeleteCustomer(Customer customer) { SqlConnection connection = MMABooksDB.GetConnection(); string deleteStatement = "DELETE FROM Customers " + "WHERE Name = @Name " + "AND Address = @Address " + "AND City = @City " + "AND State = @State " + "AND ZipCode = @ZipCode"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue( "@Name", customer.Name); deleteCommand.Parameters.AddWithValue( "@Address", customer.Address); ... ... deleteCommand.Parameters.AddWithValue( "@ZipCode", customer.ZipCode);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 35
The code for the CustomerDB class (cont.) try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 36
The code for the StateDB class public static class StateDB { public static List<State> GetStates() { List<State> states = new List<State>(); SqlConnection connection = MMABooksDB.GetConnection(); string selectStatement = "SELECT StateCode, StateName " + "FROM States " + "ORDER BY StateName"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 37
The code for the StateDB class (cont.) try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { State s = new State(); s.StateCode = reader["StateCode"].ToString(); s.StateName = reader["StateName"].ToString(); states.Add(s); } reader.Close(); }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 38
The code for the StateDB class (cont.) catch (SqlException ex) { throw ex; } finally { connection.Close(); } return states; } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 39
The code for the MMABooksDB class public static class MMABooksDB { public static SqlConnection GetConnection() { // If necessary, change the following connection string // so it works for your system string connectionString = "Data Source=localhost\\SqlExpress; Initial Catalog=MMABooks;" + "Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionString); return connection; } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 40
The Customer Maintenance form public partial class frmCustomerMaintenance : Form { private Customer customer; private void btnGetCustomer_Click( object sender, EventArgs e) { if (Validator.IsPresent(txtCustomerID) && Validator.IsInt32(txtCustomerID)) { int customerID = Convert.ToInt32( txtCustomerID.Text); this.GetCustomer(customerID);
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 41
The Customer Maintenance form (cont.) if (customer == null) { MessageBox.Show( "No customer with this ID. " + "Please try again.", "Customer Not Found"); this.ClearControls(); } else this.DisplayCustomer(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 42
The Customer Maintenance form (cont.) private void GetCustomer(int customerID) { try { customer = CustomerDB.GetCustomer(customerID); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 43
The Customer Maintenance form (cont.) private void ClearControls() { txtCustomerID.Text = ""; txtName.Text = ""; txtAddress.Text = ""; txtCity.Text = ""; txtState.Text = ""; txtZipCode.Text = ""; btnModify.Enabled = false; btnDelete.Enabled = false; txtCustomerID.Focus(); }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 44
The Customer Maintenance form (cont.) private void DisplayCustomer() { txtName.Text = customer.Name; txtAddress.Text = customer.Address; txtCity.Text = customer.City; txtState.Text = customer.State; txtZipCode.Text = customer.ZipCode; btnModify.Enabled = true; btnDelete.Enabled = true; }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 45
The Customer Maintenance form (cont.) private void btnAdd_Click(object sender, EventArgs e) { frmAddModifyCustomer addCustomerForm = new frmAddModifyCustomer(); addCustomerForm.addCustomer = true; DialogResult result = addCustomerForm.ShowDialog(); if (result == DialogResult.OK) { customer = addCustomerForm.customer; txtCustomerID.Text = customer.CustomerID.ToString(); this.DisplayCustomer(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 46
The Customer Maintenance form (cont.) private void btnModify_Click(object sender, EventArgs e) { frmAddModifyCustomer modifyCustomerForm = new frmAddModifyCustomer(); modifyCustomerForm.addCustomer = false; modifyCustomerForm.customer = customer; DialogResult result = modifyCustomerForm.ShowDialog(); if (result == DialogResult.OK) { customer = modifyCustomerForm.customer; this.DisplayCustomer(); }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 47
The Customer Maintenance form (cont.) else if (result == DialogResult.Retry) { this.GetCustomer(customer.CustomerID); if (customer != null) this.DisplayCustomer(); else this.ClearControls(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 48
The Customer Maintenance form (cont.) private void btnDelete_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show( "Delete " + customer.Name + "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) {
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 49
The Customer Maintenance form (cont.) try { if (!CustomerDB.DeleteCustomer(customer)) { MessageBox.Show("Another user has " + "updated or deleted " + "that customer.", "Database Error"); this.GetCustomer(customer.CustomerID); if (customer != null) this.DisplayCustomer(); else this.ClearControls(); }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 50
The Customer Maintenance form (cont.) else this.ClearControls(); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } private void btnExit_Click(object sender, EventArgs e) { this.Close(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 51
The Add/Modify Customer form public partial class frmAddModifyCustomer : Form { public bool addCustomer; public Customer customer; private void frmAddModifyCustomer_Load( object sender, EventArgs e) { this.LoadStateComboBox(); if (addCustomer) { this.Text = "Add Customer"; cboStates.SelectedIndex = -1; } else { this.Text = "Modify Customer"; this.DisplayCustomer(); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 52
The Add/Modify Customer form (cont.) private void LoadStateComboBox() { List<State> states = new List<State>(); try { states = StateDB.GetStates(); cboStates.DataSource = states; cboStates.DisplayMember = "StateName"; cboStates.ValueMember = "StateCode"; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 53
The Add/Modify Customer form (cont.) private void DisplayCustomer() { txtName.Text = customer.Name; txtAddress.Text = customer.Address; txtCity.Text = customer.City; cboStates.SelectedValue = customer.State; txtZipCode.Text = customer.ZipCode; }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 54
The Add/Modify Customer form (cont.) private void btnAccept_Click(object sender, EventArgs e) { if (IsValidData()) { if (addCustomer) { customer = new Customer(); this.PutCustomerData(customer); try { customer.CustomerID = CustomerDB.AddCustomer(customer); this.DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 55
The Add/Modify Customer form (cont.) else { Customer newCustomer = new Customer(); newCustomer.CustomerID = customer.CustomerID; this.PutCustomerData(newCustomer); try { if (! CustomerDB.UpdateCustomer( customer, newCustomer)) { MessageBox.Show( "Another user has updated or " + "deleted that customer.", "Database Error"); this.DialogResult = DialogResult.Retry; }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 56
The Add/Modify Customer form (cont.) else { customer = newCustomer; this.DialogResult = DialogResult.OK; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }
Murach’s C# 2010, C20 © 2010, Mike Murach & Associates, Inc. Slide 57
The Add/Modify Customer form (cont.) private bool IsValidData() { return Validator.IsPresent(txtName) && Validator.IsPresent(txtAddress) && Validator.IsPresent(txtCity) && Validator.IsPresent(cboStates) && Validator.IsPresent(txtZipCode); } private void PutCustomerData(Customer customer) { customer.Name = txtName.Text; customer.Address = txtAddress.Text; customer.City = txtCity.Text; customer.State = cboStates.SelectedValue.ToString(); customer.ZipCode = txtZipCode.Text; } }
top related