Coding ADONET Objects Connection, Command, DataReader

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

2、sion 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,Connection String,Containing information about database, OLE DB provider, password, if an

3、y, 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-PCsqlexpress;Initial Catalog=testSQL08;Integrated Security=True,Conn

4、ection 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; OleDb

5、Connection objConn = new OleDbConnection(strConn); objConn.Open(); Object Browser: System.Data.OleDB/oledbConnection/New Basic Methods: Open, Close BeginTransaction,Command Object,The command object allows us to execute a SQL statement. Properties: CommandType: Text, stored procedure, tableDirect Co

6、mmandText: 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 b

7、y 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 SQLs UPDATE, INSERT, or DELETE statements,DataReader Object,It is read-only and forward-only cursor. Basic Methods: Read: Re

8、ads 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,ExecuteReader Example,string strConn = P

9、rovider=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.ExecuteRea

10、der(); 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,Import NameSpace,The “using” statement must appear before all other declarations in a file and cannot appear inside a c

11、lass or module declaration. using System.Data.OleDb,Read Records in a DataReader,string Results=; while (objDataReader.Read()=true) Results+=objDataReadercid + + objDataReaderCname + Environment.NewLine; textBox1.Text = Results.ToString(); Note: Use index to read a column - objDataReader0 Note: The

12、Read method will (1) read a record, (2) advance pointer, (3) return true/false to indicate if more records exist,DataReader Example,string strConn = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:SalesDB2011.accdb; OleDbConnection objConn = new OleDbConnection(strConn); objConn.Open(); string strSQ

13、L = select * from customer; OleDbCommand objComm = new OleDbCommand(strSQL,objConn); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); string Results=; while (objDataReader.Read()=true) Results += objDataReadercid + + objDataReaderCname + Environment.NewLine; textBox1.Text = Re

14、sults.ToString(,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); obj

15、Conn.Open(); OleDbDataReader objDataReader; objDataReader=objComm.ExecuteReader(); while (objDataReader.Read()=true) listBox1.Items.Add(objDataReadercid); / listBox1.Items.Add(objDataReader.GetString(0); Note1: Listbox is not bound. 2. SelectedItem,Display Selected Customer Info in Textbox,private v

16、oid 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

17、 = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); objDataReader.Read(); textBox1.Text = objDataReaderCname.ToString(); textBox2.Text = objDataReaderrating.ToString(); objConn.Close();,Using a Parallel ArrayList to Store Cnam

18、e and Rating,ArrayList Add Clear Remove IndexOf Must add this reference: using System.Collections,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

19、 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() =

20、 true) listBox1.Items.Add(objDataReadercid); CnameList.Add(objDataReadercname); RatingList.Add(objDataReaderrating); objConn.Close();,Use Listbox SelectedIndex,private void listBox1_SelectedIndexChanged(object sender, EventArgs e) textBox1.Text = CnameListlistBox1.SelectedIndex.ToString(); textBox2.

21、Text = RatingListlistBox1.SelectedIndex.ToString();,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 obj

22、Comm = new OleDbCommand(strSQL, objConn); objConn.Open(); OleDbDataReader objDataReader; objDataReader = objComm.ExecuteReader(); if (objDataReader.Read() = true) MessageBox.Show(objDataReadercustCount.ToString(); objConn.Close(); Note: Alias - select count(cid) as custCount,Using Commands ExecuteSc

23、alar 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(

24、); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close(,ExecuteScalar Example,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 = selec

25、t 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); objCo

26、nn.Open(); textBox1.Text = objComm.ExecuteScalar().ToString(); objConn.Close();,Testing for Null:DBNull.Value,if ( objDataReaderrating=DBNull.Value) textBox2.Text = NA; else textBox2.Text = objDataReaderrating.ToString();,Error Handling,string strConn = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=

27、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() = t

28、rue) listBox1.Items.Add(objDataReadercid); catch (System.Exception ex) MessageBox.Show(ex.Message); objConn.Close(,Note: Where do declare objConn,Command Objects ExecuteNonQuery Method,To run SQL: Insert Delete Update The ExecuteNonQuery method also returns a value indicating the number of records a

29、ffected by the SQL statement,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

30、 = 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.ExecuteNonQu

31、ery(); catch (Exception ex) MessageBox.Show(ex.Message); objConn.Close(,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; OleDbConnec

32、tion 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.ToS

33、tring() + records deleted); listBox1.Items.RemoveAt(listBox1.SelectedIndex); catch (System.Exception ex) MessageBox.Show(ex.Message); objConn.Close(,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 listb

34、ox. Select a new rating from radio buttons for the selected customer. Update customers rating using the ExecuteNonQuery method of a Command object,create CID listbox: private void Form4_Load(object sender, EventArgs e) string strConn = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:SalesDB2011.accd

35、b; 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.Item

36、s.Add(objDataReadercid); catch (System.Exception ex) MessageBox.Show(ex.Message); objConn.Close();,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 =

37、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); t

38、ry objConn.Open(); objComm.ExecuteNonQuery(); catch (System.Exception ex) MessageBox.Show(ex.Message); objConn.Close();,Working with ODBC Data Source,Enter: using System.Data.Odbc; Use: OdbcConnection: Use DSN Example of connection string: string strConn = DSN=test32SQLDS; OdbcCommand OdbcDataReader

39、,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.It