ADO.NET
两个命名空间,三个类。一、两个命名空间:using System.Data;using System.Data.SqlClient;二、三个类:SqlConnection SqlCommand SqlDataReader(一)SqlConnection链接类1.构造链接字符串的写法:server=服务器IP或服务器名;database=数据库名;uid=用户名;pwd=密码server=.;database=mydb;uid=sa;pwd=123Data Source=.;Initial Catalog=mydb;Persist Security Info=True;User ID=sa;Password=***********SqlConnection conn = new SqlConnection();SqlConnection conn = new SqlConnection("链接字符串");2.属性ConnectionString:string,链接字符串3.方法Open()Close()CreateCommand()(二)SqlCommand命令类1.构造SqlCommand cmd = new SqlCommand(["Sql语句",链接对象,事务对象]);SqlCommand cmd = conn.CreateCommand();2.属性CommandText:string 要执行的SQL语句或存储过程的名子Connection:SqlConnection 指定通过哪个链接访问数据库Parameters:SqlParameterCollection类型 ,指定Sql文本中的变量的值。 AddWithValue("名","值")CommandType:CommandType枚举类型,指定CommandText中是SQL语句(Text),还是存储过程(StoredProcedure)。例:SQL存储过程语句:CREATE PROCEDURE UP_INFO_DELETE @code varchar(50)ASBEGIN delete from Work where InfoCode=@code delete from Family where InfoCode=@code delete from Info where Code=@codeENDGOTransaction:SqlTransaction类型,执行命令时需要的事务对象3.方法ExecuteNonQuery()ExecuteReader()ExecuteScalar()(三)SqlDataReader读取器类1.构造注意:不能直接new出来。只有唯一的一种生成方式SqlDataReader dr = cmd.ExecuteReader();2.属性HasRows:bool,检查是否查出数据来了。3.方法Read():bool,读取数据,读取成功为true,否则为false4.语法while(dr.Read()){ dr[索引号] dr["列名"]}三、事务ACID 原子性,一致性,隔离性,持久性(一)链接内的事务1.创建事务对象。使用链接对象的BeginTransaction()SqlTransaction trans = conn.BeginTransaction();注意:必须在链接打开的时候创建事务。2.把事务对象挂到命令对象上cmd.Transaction = trans;3.执行命令,提交与回滚事务trans.Commit() //一般放在try块中trans.Rollback() //一般放在catch块中(二)跨链接的事务 TransactionScope对象。1.启动服务:DTC 分布式事务协调器。2.引用事务程序集。在解决方案管理器中,右键添加引用。System.Transactions3.使用using System.Transactions 把命名空间导进来。4.使用TransactionScope类来控制事务。 using (TransactionScope ts = new TransactionScope()) { DeleteWork("p001"); DeleteFamily("p001"); DeleteInfo("p001"); ts.Complete(); //必须。它是用来提交事务的。 }//都执行或正确,事务提交,成功运行,否则事务回滚,恢复到修改前的状态。简单举例:class Program { public const string CONNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=5587725"; static void Deleteinfo(string code) { SqlConnection conn = new SqlConnection(CONNECTIONSTRING); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "UP_INFO_DELETE"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@code",code); cmd.ExecuteNonQuery(); } finally { conn.Close(); } } static void Main(string[] args) { using (TransactionScope ts = new TransactionScope()) { Deleteinfo("p003"); ts.Complete(); } } }1.造DBConnection 做一个链接字符串的常量2.造实体类 把数据库的表封装类,把表的字段都封状成成员变量和属性3.造数据访问类 a.声明三个成员:SqlConnection,SqlCommand,SqlDataReader b.在构造函数中,把SqlConnection和SqlCommand实例化。 c.做一系列的增、删、改、查的方法。4.Main函数 (1)。 a.调用数据访问类,获得数据。 b.在界面上使用for显示出来。 (2)。 a.从界面上获得输入的数据,存在变量中。 b.调用数据访问类,把变量传进相应的增删改的方法中去,实现数据库相应操作。练习1:mydb数据库中Nation(民族)表的增删改查class DBconnection { public const string CONNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=5587725"; } //实体类 class Nation { private string _Code; // 类的字段 public string Code //类的属性 { get { return _Code; } set { _Code = value; } } private string _Name; public string Name { get { return _Name; } set { _Name = value; } } } //数据访问类---增删改查 class NationDA { //定义类里面的三个字段 private SqlConnection _Conn; private SqlCommand _Cmd; private SqlDataReader _DR; //构造函数 public NationDA() { _Conn = new SqlConnection(DBconnection.CONNECTIONSTRING); _Cmd = _Conn.CreateCommand(); } //查询 public Nation Select(string code) { _Cmd.CommandText = "select * from Nation where code=@code"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code",code); try { _Conn.Open(); _DR = _Cmd.ExecuteReader(); if (_DR.Read()) { Nation data = new Nation(); data.Code = _DR["Code"].ToString(); data.Name = _DR["Name"].ToString(); return data; } else { return null; } } finally { _Conn.Close(); } } public List<Nation> Select() { List<Nation> list = new List<Nation>(); _Cmd.CommandText = "select * from Nation"; try { _Conn.Open(); _DR = _Cmd.ExecuteReader(); while (_DR.Read()) { Nation data = new Nation(); data.Code = _DR["Code"].ToString(); data.Name = _DR["Name"].ToString(); list.Add(data); } } finally { _Conn.Close(); } return list; } //插入(增加) public void Insert(string code,string name) { _Cmd.CommandText = "insert into Nation values(@code,@name)"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@name", name); _Cmd.Parameters.AddWithValue("@code", code); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } //更改 public void Update(string code,string name) { _Cmd.CommandText = "update Nation set name=@name where code=@code;"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@name",name); _Cmd.Parameters.AddWithValue("@code",code); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } //删除 public void Delete(string code) { _Cmd.CommandText = "delete from Nation where code=@code"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code",code); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } }class Program { //界面 static void Main(string[] args) { List<Nation> list = new NationDA().Select(); for (int i = 0; i < list.Count; i++)//数组中长度用.length,集合中长度用.count { Console.WriteLine(list[i].Code+"\t"+list[i].Name); } Console.ReadKey(); } }练习2:mydb数据库中Info表的增删改查()注意:要调用NationDA中的select ,需要放在同一个项目下。 class DBconnection { public const string CONNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=5587725"; } //实体类 class info { private string _Code; public string Code { get { return _Code; } set { _Code = value; } } private string _Name; public string Name { get { return _Name; } set { _Name = value; } } private bool _Sex; public bool Sex { get { return _Sex; } set { _Sex = value; } } private string _Nation; public string Nation { get { return _Nation; } set { _Nation = value; } } private DateTime _Birthday; public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } } //数据访问类 class infoDA { private SqlConnection _Conn; private SqlCommand _Cmd; private SqlDataReader _DR; //构造函数 public infoDA() { _Conn = new SqlConnection(DBconnection.CONNECTIONSTRING); _Cmd = _Conn.CreateCommand(); } //增 public void Insert(string code,string name,bool sex,string nation, DateTime birthday ) { _Cmd.CommandText = "insert into info values(@code,@name,@sex,@natiao,@birthday)"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code",code); _Cmd.Parameters.AddWithValue("@name",name); _Cmd.Parameters.AddWithValue("@sex", sex); _Cmd.Parameters.AddWithValue("@nation", nation); _Cmd.Parameters.AddWithValue("@birthday", birthday); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } //删 public void Delete(string code) { _Cmd.CommandText = "delete from info where code=@code"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code",code); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } //改 public void Update(string code, string name, bool sex, string nation, DateTime birthday) { _Cmd.CommandText = "update info set code=@code,name=@name,sex=@sex,nation=@nation,birthday=@birthday where code=@code"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code", code); _Cmd.Parameters.AddWithValue("@name", name); _Cmd.Parameters.AddWithValue("@sex", sex); _Cmd.Parameters.AddWithValue("@nation", nation); _Cmd.Parameters.AddWithValue("@birthday", birthday); try { _Conn.Open(); _Cmd.ExecuteNonQuery(); } finally { _Conn.Close(); } } //查 public info Select(string code) { _Cmd.CommandText = "select * from info where code=@code"; _Cmd.Parameters.Clear(); _Cmd.Parameters.AddWithValue("@code",code); try { _Conn.Open(); _DR = _Cmd.ExecuteReader(); if (_DR.Read()) { info data = new info(); data.Code = _DR["Code"].ToString(); data.Name = _DR["Name"].ToString(); data.Sex = Convert.ToBoolean(_DR["Sex"]); data.Nation = _DR["Nation"].ToString(); data.Birthday = Convert.ToDateTime(_DR["Birthday"]); return data; } else { return null; } } finally { _Conn.Close(); } } public List<info> Select() { List<info> list =new List<info>(); _Cmd.CommandText = "select * from info"; try { _Conn.Open(); _DR = _Cmd.ExecuteReader(); while (_DR.Read()) { info data = new info(); data.Code = _DR["Code"].ToString(); data.Name = _DR["Name"].ToString(); data.Sex = Convert.ToBoolean(_DR["Sex"]); data.Nation = _DR["Nation"].ToString(); data.Birthday = Convert.ToDateTime(_DR["Birthday"]); list.Add(data); } } finally { _Conn.Close(); } return list; } } class Program { static void Main(string[] args) { List<info> list = new infoDA().Select(); for (int i = 0; i < list.Count; i++) { info data = list[i]; Console.Write(data.Code+"\t"); Console.Write(data.Name + "\t"); Console.Write(data.Sex?"男":"女" +"\t"); Console.Write(new NationDA().Select(data.Nation).Name + "\t"); Console.Write(data.Birthday.ToString("yyy年MM月dd日") + "\n"); } Console.ReadKey(); } }