《讲数据库编程》PPT课件.ppt
《《讲数据库编程》PPT课件.ppt》由会员分享,可在线阅读,更多相关《《讲数据库编程》PPT课件.ppt(34页珍藏版)》请在三一办公上搜索。
1、第8讲 数据库编程,数据库基础知识数据库访问技术ADO.NET数据库访问,8.1 数据库基础知识,基本概念:数据库 关系模型 表 主关键字MS SQL Server+MS Access+MySql+OracleSQL语句:增删改查SELECT*FROM Contact WHERE(Sex=True)ORDER BY Name ASCINSERT INTO Contact(Name,Sex)Values(Mary,false);UPDATE Contact SET BirthDate=1980/2/2 WHERE Name=MaryDELETE FROM Contact Where Name=M
2、ary;,存储过程,CREATE PROCEDURE UpDateContactByName(Name)ASUPDATE Contact SET Contact.BirthDate=1982/5/6WHERE(Contact.Name)=Name)Go,8.2 数据库访问技术,ADO.NET作用与ADO相同,但与.NET FrameWork无缝集成,能访问XML,提供断开式的数据访问模型,8.3 ADO.NET数据库访问,ADO.NET组成:分.NET数据提供程序和DataSet两部分,前者用以实现各类数据操作,后者是ADO.NET断开式数据访问模型的核心,依靠DataAdapter类与数据库
3、通信.NET Framework数据提供程序的具体实现有两个,分别是SQL Server.NET数据提供程序和OLE DB.NET数据提供程序,前者可高效访问SQL Server7.0以上版本的数据库,后者更通用,使用两种.NET Data Provider访问数据库,ADO.NET访问ACCESS举例控制台输出数据,using System;using System.Data.OleDb;/人工引入using System.Data;/人工引入namespace class Program static void Main(string args)OleDbConnection conn=n
4、ew OleDbConnection();string connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;conn.ConnectionString=connStr;conn.Open();if(conn.State=ConnectionState.Open)Console.WriteLine(ok);OleDbCommand cmd=conn.CreateCommand();cmd.CommandType=CommandType.Te
5、xt;cmd.CommandText=select*from contact order by Name ASC;OleDbDataReader rd=cmd.ExecuteReader();while(rd.Read()Console.WriteLine(0,1,2,3,rd.GetInt32(0),rd.GetString(1),rd.GetBoolean(2)?男:女,rd.GetDateTime(3).ToShortDateString();rd.Close();conn.Close();,ADO.NET访问MS SQL Server数据库举例:,SqlConnection conn;
6、SqlDataReader reader;try conn=new SqlConnection();string connStr=“Data Source=(local);Initial Catalog=students;user Id=bb;password=bb”;/也可用sa conn.ConnectionString=connStr;conn.Open();if(conn.State=ConnectionState.Open)MessageBox.Show(接通);SqlCommand cmd=conn.CreateCommand();cmd.CommandType=CommandTy
7、pe.Text;cmd.CommandText=Select ID,sName from student;reader=cmd.ExecuteReader();string output;while(reader.Read()output=string.Format(学生0t学号是1n,reader.GetString(1),reader.GetInt32(0);lblOutput.Text=lblOutput.Text+output;catch(Exception ex)MessageBox.Show(“操作失败+ex.Message);finally reader.Close();conn
8、.Close();,再例:注意命令的创建与异常的处理,using System;using System.Data.OleDb;using System.Data;namespace class Program static void Main(string args)string connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.
9、ConnectionString=connStr;conn.Open();if(conn.State=ConnectionState.Open)Console.WriteLine(ok);OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=select*from contact order by Name ASC;OleDbDataReader rd=cmd.ExecuteReader();while(rd.Read()Console.W
10、riteLine(0,1,2,3,rd.GetInt32(0),rd.GetString(1),rd.GetBoolean(2)?男:女,rd.GetDateTime(3).ToShortDateString();rd.Close();,Command详解,向数据库插入一条记录,using System;using System.Data.OleDb;using System.Data;namespace class Program static void Main(string args)string connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data
11、 Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=connStr;conn.Open();OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=INSERT INTO Contact(Name,Sex,BirthDate)Values
12、(Mary,false,1980/5/5);int count=cmd.ExecuteNonQuery();Console.WriteLine(共0行被改变,count);,更新:UPDATE Contact SET telephone=123456789 Where(name=Mary);删除:cmd.CommandText=DELETE FROM Contact Where Name=Mary;,利用用带参数的SQL语句:(处理复杂表和赋变量值更清晰),static void Main(string args)string connStr=Provider=Microsoft.Jet.OL
13、EDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=connStr;conn.Open();OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=INSERT INTO Contact(Name,Sex,Birt
14、hDate)Values(Name,Sex,BirthDate);OleDbParameter parms=new OleDbParameter new OleDbParameter(Name,OleDbType.VarChar,50),new OleDbParameter(Sex,OleDbType.Boolean),new OleDbParameter(BirthDate,OleDbType.Date);parms0.Value=Mary;parms1.Value=true;parms2.Value=Convert.ToDateTime(“1977/5/6”);/可隐式转换 cmd.Par
15、ameters.AddRange(parms);int count=cmd.ExecuteNonQuery();Console.WriteLine(共0行被改变,count);,对比:将文本框中的数据放入sql语句,cmd.CommandText=INSERT INTO Contact(Name,Sex,BirthDate)Values(+textBox1.Text+,+textBox2.Text+,+textBox3.Text+);用带参数的SQL语句更清晰cmd.CommandText=INSERT INTO Contact(Name,Sex,BirthDate)Values(Name,S
16、ex,BirthDate);OleDbParameter parms=new OleDbParameter new OleDbParameter(Name,OleDbType.VarChar,50),new OleDbParameter(Sex,OleDbType.Boolean),new OleDbParameter(BirthDate,OleDbType.Date);parms0.Value=textBox1.Text;parms1.Value=Convert.ToBoolean(textBox2.Text);/可隐式转换parms2.Value=Convert.ToDateTime(te
17、xtBox3.Text);/可隐式转换cmd.Parameters.AddRange(parms);,DataReader详解,关于数据集DataSet和绑定,前者对于复杂数据库操作不适用,后者简化了操作,但破坏了三层结构,通常不提倡使用,自学,回顾:连接数据库并执行查询命令,using System;using System.Data.OleDb;/自行引入using System.Data;/自行引入namespace class Program static void Main(string args)string connStr=Provider=Microsoft.Jet.OLEDB
18、.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=connStr;conn.Open();if(conn.State=ConnectionState.Open)Console.WriteLine(ok);OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.
19、Text;cmd.CommandText=select*from contact order by Name ASC;OleDbDataReader rd=cmd.ExecuteReader();while(rd.Read()/实际应先判断是否有行 Console.WriteLine(0,1,2,3,rd.GetInt32(0),rd.GetString(1),rd.GetBoolean(2)?男:女,rd.GetDateTime(3).ToShortDateString();rd.Close();,连接数据库并执行增删改命令,using System;using System.Data.Ol
20、eDb;using System.Data;namespace class Program static void Main(string args)string connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=connStr;conn.Open();OleDbCommand cmd=new Ol
21、eDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=INSERT INTO Contact(Name,Sex,BirthDate)Values(Mary,false,1980/5/5);int count=cmd.ExecuteNonQuery();Console.WriteLine(共0行被改变,count);,更新:UPDATE Contact SET telephone=123456789 Where(name=Mary);删除:cmd.CommandText=DELETE F
22、ROM Contact Where Name=Mary;,带参数的SQL语句:,static void Main(string args)string connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=connStr;conn.Open();OleDbCommand cmd=new OleDbCom
23、mand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=INSERT INTO Contact(Name,Sex,BirthDate)Values(Name,Sex,BirthDate);OleDbParameter parms=new OleDbParameter new OleDbParameter(Name,OleDbType.VarChar,50),new OleDbParameter(Sex,OleDbType.Boolean),new OleDbParameter(BirthDate,O
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 讲数据库编程 数据库 编程 PPT 课件
链接地址:https://www.31ppt.com/p-5605042.html