ado代码

luodao 贡献于2013-11-04

作者 微软用户  创建于2012-11-12 08:47:00   修改者微软用户  修改于2012-11-14 14:17:00字数6784

文档摘要:为什么使用using (SqlCommand cmd = cnn.CreateCommand())中的using? Close与Dispose方法的区别:Close:关闭之后还可以打开,Dispose:直接销毁,不能再次被使用。Using在出了作用域以后调用Dispose,SqlConnection等内部会做这样的判断,先判断是否有Close,如果没有Close则先Close再Dispose
关键词:

例题1:简单的连接检测 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ADO1 { class Program { static void Main(string[] args) { string dataDir = AppDomain.CurrentDomain.BaseDirectory; if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\")) { dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName; AppDomain.CurrentDomain.SetData("DataDirectory", dataDir); } //using的作用是对资源的释放 using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand cmd =cnn.CreateCommand ()) { cmd.CommandText = "insert into Table1(Name) values('abc')"; cmd.ExecuteNonQuery(); Console.WriteLine("插入成功"); } } Console.WriteLine ("数据库连接成功"); Console.ReadKey (); } } } 例题2:简单登陆检测 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ADO2 { class Program { static void Main(string[] args) { Console.WriteLine("请输入你的用户名"); string UsName=Console.ReadLine(); Console.WriteLine ("请输入你的密码"); string password=Console.ReadLine (); using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open (); using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "select * from Person where Name='"+UsName+"'"; using(SqlDataReader read=cmd.ExecuteReader ()) { if(read.Read ()) { string dbPassword=read.GetString(read.GetOrdinal ("Password")); if(password ==dbPassword ) { Console.WriteLine ("登陆成功"); } else { Console.WriteLine ("密码错误"); } } else { Console.WriteLine ("用户名不存在"); } } } } Console.ReadKey(); } } } 例题3:执行查询 执行多条结果集用ExecuteReader SqldataReader reader=cmd.ExecuteReader(); While(reader.read()) { Int id=reader.GetInt32(reader.GetOrdinal(“Id”)); string name=reader.GetString(reader.GetOrdinal(“Name”)); string password=reader.GetString(reader.GetOrdinal(“Password”)); Console.WriteLine(“{0}{1}{2}”,id,name,password); } Reader的GetString和GetInt32只能接受整数参数,也就是序号,用GetOrdinal能够动态的生成序号 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ADO5 { class Program { static void Main(string[] args) { using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "select * from T_person"; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string name = reader.GetString(reader.GetOrdinal("Name")); string password = reader.GetString(reader.GetOrdinal("Password")); Console.WriteLine("{0}{1}", name, password); } } } } Console.ReadKey(); } } } 为什么使用using (SqlCommand cmd = cnn.CreateCommand())中的using? Close与Dispose方法的区别:Close:关闭之后还可以打开,Dispose:直接销毁,不能再次被使用。Using在出了作用域以后调用Dispose,SqlConnection等内部会做这样的判断,先判断是否有Close,如果没有Close则先Close再Dispose 例题4:登陆的另一种写法 缺点:拼接字符串形式很容易形成黑客漏洞 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ADO6登陆程序另一种写法 { class Program { static void Main(string[] args) { Console.WriteLine ("请输入你的用户名"); string name=Console.ReadLine (); Console.WriteLine ("请输入你的密码"); string password=Console.ReadLine ();//当输入 1'or '1'='1时会登陆成功 所以造成SQL注入漏洞攻击 using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "select count(*) from T_person where Name='"+name+"'and Password='"+password+"'"; int i = Convert.ToInt32(cmd.ExecuteScalar());//cmd.ExecuteScalar()返回执行查询结果的第一行第一列 if (i > 0) { Console.WriteLine("登陆成功"); } else { Console.WriteLine("登录失败,用户名或者密码错误"); } Console.ReadKey(); } } } } } 例题5:登陆最为安全的一种查询:参数化查询 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace ADO6登陆程序另一种写法 { class Program { static void Main(string[] args) { Console.WriteLine ("请输入你的用户名"); string name=Console.ReadLine (); Console.WriteLine ("请输入你的密码"); string password=Console.ReadLine ();//当输入 1'or '1'='1时会登陆成功 所以造成SQL注入漏洞攻击 using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "select count(*) from T_person where Name=@UN and Password=@P"; cmd.Parameters.Add(new SqlParameter ("UN",name)); cmd.Parameters.Add(new SqlParameter ("P",password)); //参数化查询声明一个参数集合使用添加方法 int i = Convert.ToInt32(cmd.ExecuteScalar());//cmd.ExecuteScalar()返回执行查询结果的第一行第一列 if (i > 0) { Console.WriteLine("登陆成功"); } else { Console.WriteLine("登录失败,用户名或者密码错误"); } Console.ReadKey(); } } } } } 案例登陆2 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace ADO案例登陆2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } //封装一个新的连接 private void InfoErrorTimes() { using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand updatecmd = cnn.CreateCommand()) { updatecmd.CommandText = "update T_person set errorTimes=errorTimes+1 where UserName=@username"; updatecmd.Parameters.Add(new SqlParameter("username", txtUsername.Text)); updatecmd.ExecuteNonQuery(); } } } //如果登陆成功,将错误次数清零 private void ResetErrorTimes() { using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open(); using (SqlCommand updatecmd = cnn.CreateCommand()) { updatecmd.CommandText = "update T_person set errorTimes=0 where UserName=@username"; updatecmd.Parameters.Add(new SqlParameter("username", txtUsername.Text)); updatecmd.ExecuteNonQuery(); } } } private void button1_Click(object sender, EventArgs e) { using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) { cnn.Open();//打开连接 using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "select * from T_person where UserName=@user"; cmd.Parameters.Add(new SqlParameter ("user",txtUsername .Text )); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { //取数据库中的errorTimes判断是否超过3次 int i = reader.GetInt32 (reader.GetOrdinal("errorTimes")); if (i > 3) { MessageBox.Show("登陆次数超过三次,不能再登陆!"); } //取数据库中的密码 string dbpassword = reader.GetString(reader.GetOrdinal ("Password")); if (dbpassword == txtPassword.Text) { MessageBox.Show("登陆成功"); ResetErrorTimes(); } else { /* //在同一个连接中,如果SqlReader没有关闭,那么不能执行update之类的语句 using (SqlCommand updatecmd = cnn.CreateCommand()) { updatecmd.CommandText = "update T_person set errorTimes=errorTimes+1 where UserName=@username"; updatecmd.Parameters.Add(new SqlParameter ("username",txtUsername .Text )); updatecmd.ExecuteNonQuery(); } * */ //调用封装的连接 InfoErrorTimes(); MessageBox.Show("登录失败"); } } else { MessageBox.Show("没有此用户名"); } } } } } } }

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档