《讲数据库编程》PPT课件.ppt
第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=Mary;,存储过程,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类与数据库通信.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=new 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.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();conn.Close();,ADO.NET访问MS SQL Server数据库举例：,SqlConnection conn;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=CommandType.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.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.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.WriteLine(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 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(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.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 OleDbCommand();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,OleDbType.Date);parms0.Value=Mary;parms1.Value=true;parms2.Value=Convert.ToDateTime(“1977/5/6”);/可隐式转换 cmd.Parameters.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,Sex,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(textBox3.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.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.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.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.ConnectionString=connStr;conn.Open();OleDbCommand cmd=new OleDbCommand();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 FROM 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 OleDbCommand();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,OleDbType.Date);parms0.Value=Mary;parms1.Value=true;parms2.Value=Convert.ToDateTime(“1977/5/6”);/可隐式转换 cmd.Parameters.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,Sex,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(textBox3.Text);/可隐式转换cmd.Parameters.AddRange(parms);,三层结构开发：,表现层(Client)：MainForm窗体,常用DataGridView(参考教材)或ListView控件(参考视频，通过Columns属性添加字段名,设置FullRowSelect和 Gridlines为Ture，设置View属性为Details)业务逻辑层(Business)：ContactBiz类,提供静态方法数据服务层(DataAccess):ContactDaol类,提供静态方法实体模型类(Model):ContactInfo类,封装各字段,对应一行,实体类：注意字段、属性修饰符及可空类型,class ContactInfo private int _id;public int Id/重构封装字段 get return _id;set _id=value;private string _name;public string Name private bool?_sex;/可空类型，因数据库中此字段可空 public bool?Sex/可空类型 get return _sex;set _sex=value;private DateTime?_birthDate;public DateTime?BirthDate private string _telePhone;/引用类型本身就可为空 public string TelePhone get return _telePhone;set _telePhone=value;,数据服务层：,class ContactDao private const string CONN_STRING=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:教学dotnetContactDb.mdb;Persist Security Info=False;private const string INSERT_STRING=INSERT INTO Contact(Name,Sex,BirthDate,TelePhone)Values(Name,Sex,BirthDate,TelePhone);private const string DELETE_STRING=DELETE FROM Contact WHERE ID=ID;private const string UPDATE_STRING=UPDATE Contact SET Name=Name,Sex=Sex,BirthDate=BirthDate,TelePhone=TelePhone Where ID=ID;private const string SELECT_ALL_STRING=SELECT*FROM Contact;private const string SELECT_BY_NAME_STRING=SELECT*FROM Contact Where Name=Name;public static int InsertInfo(ContactInfo contactInfo)public static int DeleteInfo(int Id)public static int UpdateInfo(ContactInfo contactInfo)public static IList GetAllContact()public static IList GetByName(string name),public static int InsertInfo(ContactInfo contactInfo)using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=CONN_STRING;conn.Open();OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=INSERT_STRING;OleDbParameter parms=new OleDbParameter new OleDbParameter(Name,OleDbType.VarChar,50),new OleDbParameter(Sex,OleDbType.Boolean),new OleDbParameter(BirthDate,OleDbType.Date),new OleDbParameter(TelePhone,OleDbType.VarChar,50);parms0.Value=contactInfo.Name;parms1.IsNullable=true;if(contactInfo.Sex.HasValue)parms1.Value=contactInfo.Sex;else parms1.Value=DBNull.Value;parms2.IsNullable=true;if(contactInfo.BirthDate.HasValue)parms2.Value=contactInfo.BirthDate;else parms2.Value=DBNull.Value;parms3.IsNullable=true;if(String.IsNullOrEmpty(contactInfo.TelePhone)parms3.Value=DBNull.Value;else parms3.Value=contactInfo.TelePhone;cmd.Parameters.AddRange(parms);return(cmd.ExecuteNonQuery();,public static int DeleteInfo(int Id)using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=CONN_STRING;conn.Open();OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=DELETE_STRING;OleDbParameter parms=new OleDbParameter new OleDbParameter(ID,OleDbType.Integer);parms0.Value=Id;cmd.Parameters.AddRange(parms);return(cmd.ExecuteNonQuery();,public static IList GetAllContact()List contactList=new List();using(OleDbConnection conn=new OleDbConnection()conn.ConnectionString=CONN_STRING;conn.Open();OleDbCommand cmd=new OleDbCommand();cmd.Connection=conn;cmd.CommandType=CommandType.Text;cmd.CommandText=SELECT_ALL_STRING;using(OleDbDataReader rd=cmd.ExecuteReader()if(rd.HasRows)while(rd.Read()ContactInfo contactInfo=featch(rd);contactList.Add(contactInfo);return contactList;,private static ContactInfo featch(OleDbDataReader rd)ContactInfo contactInfo=new ContactInfo();contactInfo.Id=rd.GetInt32(0);contactInfo.Name=rd.GetString(1);if(!rd.IsDBNull(2)contactInfo.Sex=rd.GetBoolean(2);if(!rd.IsDBNull(3)contactInfo.BirthDate=rd.GetDateTime(3);if(!rd.IsDBNull(4)contactInfo.TelePhone=rd.GetString(4);return contactInfo;,业务逻辑层：,class ContactBiz public static int InsertInfo(ContactInfo contactInfo)return ContactDao.InsertInfo(contactInfo);/之前应判断是否重名 public static int DelteInfo(int Id)return ContactDao.DeleteInfo(Id);public static int UpdateInfo(ContactInfo contactInfo)return ContactDao.UpdateInfo(contactInfo);public static IList GetAllContact()return ContactDao.GetAllContact();public static IList GetByName(string name)return(ContactDao.GetByName(name);,表现层：控制台界面，测试使用,static void Main(string args)ContactInfo contactInfo=new ContactInfo();contactInfo.Name=Mary;contactInfo.Sex=true;contactInfo.BirthDate=Convert.ToDateTime(1987/5/6);int result=ContactBiz.InsertInfo(contactInfo);Console.WriteLine(插入0行,result);Console.WriteLine(删除0行,ContactBiz.DelteInfo(16);/Console.WriteLine(更新0行,ContactBiz.UpdateInfo(contactInfo);List contactList=(List)ContactBiz.GetAllContact();foreach(ContactInfo contact in contactList)Console.WriteLine(0 1 2 3 4,contact.Id,contact.Name,contact.Sex,contact.BirthDate,contact.TelePhone);contactList=(List)ContactBiz.GetByName(Mary);foreach(ContactInfo contact in contactList)Console.WriteLine(0 1 2 3 4,contact.Id,contact.Name,contact.Sex,contact.BirthDate,contact.TelePhone);,表现层：Windows窗体界面,菜单：可继续添加分组管理等项DataGridView控件:RowHeadersVisible+SelectionMode+ReadOnly+DataSource+Rows+Columns外观属性：见P279，如交替行等事件:RowsAdded RowsRemoved RowEnter RowLeave,主窗体加载：,private void Form1_Load(object sender,EventArgs e)LoadAllToView();private void LoadAllToView()List contactList=new List();contactList=(List)ContactBiz.GetAllContact();dataGridView1.DataSource=contactList;,添加联系人：联系人信息编辑窗体,public partial class EditContactForm:Form private ContactInfo contact;internal ContactInfo Contact get return contact;set contact=value;private void submitButton_Click(object sender,EventArgs e)/此处应验证输入有效性，自行添加 this.contact=new ContactInfo();this.contact.Name=textBox1.Text;this.contact.Sex=comboBox1.SelectedIndex=0?true:false;this.contact.BirthDate=dateTimePicker1.Value;this.contact.TelePhone=textBox2.Text;this.DialogResult=DialogResult.OK;,添加联系人：主窗体菜单,private void newContactMenuItem_Click(object sender,EventArgs e)EditContactForm dlg=new EditContactForm();dlg.Text=添加联系人;if(dlg.ShowDialog()=DialogResult.OK)/此处应验证输入有效性 ContactBiz.InsertInfo(dlg.Contact);LoadAllToView();/性能低,可单独添加一行,但无Id,DataGridViewRow row=new DataGridViewRow();row.CreateCells(dataGridView1);row.Cells1.Value=;row.Cells2.Value=;row.Cells3.Value=;.dataGridView1.Rows.Add(row);,联系人管理：,删除、更新类似添加更新:再定义一个窗体专门显示查找结果，或者以MessageBox的方式给出结果,查看菜单：,控制GridView中显示的列，类似记事本自动换行项例：dataGridView1.ColumnsId.Visible=false;,作业说明：,参照博客视频作三层结构的个人通讯录，可简化，如不设置联系人组，采用控制台界面等(视频不完整，需自己补充)说明:有无联系人分组+界面设计与菜单功能+业务逻辑处理+性能分析,
