版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024年穿水冷却装置项目建议书
- 2024年矿用防爆电器项目发展计划
- 2024年胸腔心血管外科手术器械项目建议书
- 2024年网络接口适配器项目建议书
- 2024年高性能预应力钢丝项目建议书
- 2024年枕头合作协议书
- 电玩城装修合同范例
- 金融机构装修质保合同
- 石油化工车间装修协议
- 矿山车辆运输安全协议
- 自然教育师证书
- 西宁城市职业技术学院“高水平专业群建设”项目满意度调查问卷(教职工卷)
- 五年级上册语文第一~四单元阶段性综合复习(附答案)
- 温经汤幻灯片
- 《异分母分式的加减》教学评一致性教案设计
- 医药公司风险评估及应对报告3篇完整版
- 新版FMEA表单模板(带AP明细)(OK)
- 临床科室技术操作规范
- 维生素-维生素A(食品营养与配餐课件)
- 社会学概论案例
- 2022信息安全技术网络安全众测服务要求
评论
0/150
提交评论