ADO.NET- 基础总结及实例
1、ADO.NET基础介绍
(1、程序要和数据库交互要通过ADO.NET进行,通过ADO.NET就能在程序中执行SQL了。ADO.Net中提供了对各种不同数据库的统一操作接口。
(2、直接在项目中内嵌mdf文件的方式使用SQL Server数据库(基于服务的数据库)。mdf文件随着项目走,用起来方便,和在数据库服务器上创建数据库没什么区别,运行的时候会自动附加(Attach)。
(3、双击mdf文件会在“服务器资源管理器”中打开,管理方式和在Mnagemen Studio没什么本质不同,要拷贝mdf 文件需要关闭所有指向mdf文件的连接。
(4、正式生产运行的时候附加到SQLServer上、修改连接字符串即可,除此之外没有任何的区别,在“数据库”节点上点右键“附加”;在数据库节点上à任何à分离 就可以得到可以拷来拷去mdf 文件。
(5、用的时候要在控制台、WinForm项目中的Main函数最开始的位置加入“一段神奇的代码"。ASP.Net项目中不需要。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 7 namespace myadonet 8 { 9 class Program 10 { 11 static void Main(string[] args) 12 { 13 //神奇代码 14 string dateDir = AppDomain.CurrentDomain.BaseDirectory; 15 if (dateDir.EndsWith(@"\bin\Debug\") 16 || dateDir.EndsWith(@"\bin\Release\")) 17 { 18 dateDir = System.IO.Directory.GetParent(dateDir).Parent.Parent.FullName; 19 AppDomain.CurrentDomain.SetData("DataDirectory", dateDir); 20 } 21 22 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) 23 { 24 conn.Open(); 25 26 Console.WriteLine("连接成功!"); 27 Console.ReadKey(); 28 } 29 } 30 }
2、连接到SQLServer数据库
(1、连接字符串:程序通过连接字符串 指定要连哪台服务器上的、哪个实例的哪个数据库、用什么用户名 密码等。
(2、项目内嵌mdf文件形式的连接字符串“Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Inte grated Security=True;User Intance=True”。“.SQLEXPRESS”表示本机上的SQLEXPRESS实例,如果数据库实例名不是SQLEXPRESS,则需要修改。 “Database1.mdf”为mdf的文件名。
(3、ADO.Net中通过SQLConnection在创建SQLServer的连接,SQLConnection代表一个数据库连接,ADO.Net中的连接等资源都实现了IDisposable接口,可以使用using进行资源管理。执行这段段代码,如果成功了就OK。
(4、在实现了IDisposable接口的对象,在使用完后需要用行资源释放。
3、执行简单的Insert语句
(1、SQLCommand表示向服务器提交的一个命令(SQL语句等)。
(2、CommandText属性为要执行的SQL语句,Execute NonQuery方法执行一个非查询语句(Update、inset、Delete等)
(3、ExecuteNonQuery返回值是执行的影响行数
在conn.open();下面敲上这样一段代码即可插入数据库数据:
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 7 namespace myadonet 8 { 9 class Program 10 { 11 static void Main(string[] args) 12 { 13 //神奇代码 14 string dateDir = AppDomain.CurrentDomain.BaseDirectory; 15 if (dateDir.EndsWith(@"\bin\Debug\") 16 || dateDir.EndsWith(@"\bin\Release\")) 17 { 18 dateDir = System.IO.Directory.GetParent(dateDir).Parent.Parent.FullName; 19 AppDomain.CurrentDomain.SetData("DataDirectory", dateDir); 20 } 21 22 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) 23 { 24 conn.Open(); 25 /* 插入数据* */ 26 using (SqlCommand commd = conn.CreateCommand()) { 27 commd.CommandText = "Insert into T_UserInfo (sUser,sPassWord) values('admin','888888')"; 28 commd.ExecuteNonQuery(); 29 Console.WriteLine("插入成功"); 30 } 31 32 33 } 34 35 Console.WriteLine("连接成功!"); 36 Console.ReadKey(); 37 } 38 } 39 }
4、模拟登陆
同样在conn.open();下面敲上这样一段代码即可
1 bool flag = true; 2 while (flag) 3 { 4 Console.WriteLine("请输入用户名:"); 5 string sUser = Console.ReadLine(); 6 using(SqlCommand cmd=conn.CreateCommand()){ 7 cmd.CommandText = "Select * from T_UserInfo where sUser='"+sUser+"'"; 8 using (SqlDataReader reader = cmd.ExecuteReader()) { 9 if (reader.Read()) 10 { 11 Console.WriteLine("请输入密码:"); 12 string sPassWord = Console.ReadLine(); 13 string QueryPassWord=reader.GetString(reader.GetOrdinal("sPassWord")); 14 if (sPassWord == QueryPassWord) 15 { 16 Console.WriteLine("登陆成功!"); 17 flag = false; 18 } 19 else { 20 Console.WriteLine("密码错误"); 21 } 22 } 23 else { Console.WriteLine("用户不存在,请重新输入"); } 24 } 25 } 26 }
5、ExecuteScalar
(1、SqlCommand中的ExecuteScalar方法用于执行查询,并返回查询所返回的结果集中第一行的第一列,因为不能确定返回值的类型,所以,ExecuteScalar的返回值为object类型。
(2、得到自动增长字段的主键值,在values关键词前加上output inserted.Id,其中Id为主键字段名。执行结果就是插入的主键值,用ExecuteScalar执行最方便。
1 using (SqlCommand cmd = conn.CreateCommand()) 2 { 3 cmd.CommandText = "Select * from T_UserInfo"; 4 using (SqlDataReader reader = cmd.ExecuteReader()) 5 { 6 while (reader.Read()) //这里得到一个bool值 7 { 8 string sUser = reader.GetString(reader.GetOrdinal("sUser")); 9 //int ID = reader.GetInt32(reader.GetOrdinal("ID")); 10 string sPassWord = reader.GetString(reader.GetOrdinal("sPassword")); //多行结果集 11 Console.WriteLine("{0}{1}", sUser, sPassWord); 12 13 } 14 } 15 }
(3、执行有多行结果集的用 ExecuteReader:读到最后一条数据返回false
reader的GetString、GetInt32等方法只接受整数参数,也就是序号,用GetOrdinal方法根据列名动态得到序号了。
1 /*最佳登陆方式*/ 2 bool flag = true; 3 while (flag) 4 { 5 Console.WriteLine("请输入用户名:"); 6 string sUser = Console.ReadLine(); 7 using (SqlCommand cmd = conn.CreateCommand()) 8 { 9 cmd.CommandText = "Select * from T_UserInfo where sUser='" + sUser + "'"; 10 using (SqlDataReader reader = cmd.ExecuteReader()) 11 { 12 if (reader.Read()) 13 { 14 Console.WriteLine("请输入密码:"); 15 string sPassWord = Console.ReadLine(); 16 string QueryPassWord = reader.GetString(reader.GetOrdinal("sPassWord")); 17 if (sPassWord == QueryPassWord) 18 { 19 Console.WriteLine("登陆成功!"); 20 flag = false; 21 } 22 else 23 { 24 Console.WriteLine("密码错误"); 25 } 26 } 27 else { Console.WriteLine("用户不存在,请重新输入"); } 28 } 29 } 30 }
6、为什么用using?
Close:关闭以后还能打开。 Dispose:直接销毁,不能再次使用。
Using在出了作用域以后调用Dispose,SqlConnection 、FileStream 等的Dispose内部都会做这样的判断:判断有没有close,如果没有,就
先Close再Dispose。
7、SQL注入漏洞攻击/参数化查询
**例如第4点所说的登录判断:select count(*) from T_Users where sUser=…and Password=…,将参数拼到SQL语句中。
我们可以通过构造恶意的sPassword输入 1`or`1`=`1
编译通过后识别出来的就是:select count(*) from T_Users where sUser= ‘admin’ and Password=‘1`or`1`=`1’
**参数化查询解决漏洞攻击
SQL语句使用@sUser表示“此处用参数代替”,向SqlCommand的Parameters中添加参数。
1 cmd.CommandText = "select count(*)from T_Users where sUser =@UN and sPassword=@PW"; 2 3 cmd.Parameters.Add(new SqlParameter("UN", sUser)); 4 5 cmd.Parameters.Add(new SqlParameter("PW", sPassword));
参数在SQLServer内部不是简单的字符串替换,SQLServer直接用添加的值进行数据比较,因此不会有注入漏洞攻击。
8、模拟窗体登陆,并且设置登陆错误次数
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.SqlClient; 10 11 namespace myAdoNet02 12 { 13 public partial class Form1 : Form 14 { 15 public Form1() 16 { 17 InitializeComponent(); 18 } 19 20 private void IntsErrorTime() 21 { 22 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf; 23 Integrated Security=True;Connect Timeout=30;User Instance=True")) 24 { 25 conn.Open(); 26 using (SqlCommand cmd = conn.CreateCommand()) 27 { 28 using (SqlCommand updateCmd = conn.CreateCommand()) 29 { 30 //假如用户输入的用户名和密码错误次数过多,则将数据库中的错误记录次数加1 31 updateCmd.CommandText = "update T_UserInfo Set sErrorTime=sErrorTime+1 where sUser=@sUser"; 32 updateCmd.Parameters.Add(new SqlParameter("sUser", textBox1.Text)); 33 updateCmd.ExecuteNonQuery(); 34 } 35 } 36 } 37 } 38 private void ResetsErrorTime() 39 { 40 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\UsersDB.mdf; 41 Integrated Security=True;Connect Timeout=30;User Instance=True")) 42 { 43 conn.Open(); 44 using (SqlCommand cmd = conn.CreateCommand()) 45 { 46 using (SqlCommand updateCmd = conn.CreateCommand()) 47 { 48 //假如用户输入的用户名和密码均正确,则将数据库的错误次数归0,重新统计。 49 updateCmd.CommandText = "update T_UserInfo Set sErrorTime=0 where sUser=@sUser"; 50 updateCmd.Parameters.Add(new SqlParameter("sUser", textBox1.Text)); 51 updateCmd.ExecuteNonQuery(); 52 } 53 } 54 } 55 } 56 private void button1_Click(object sender, EventArgs e) 57 { 58 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\UsersDB.mdf; 59 Integrated Security=True;Connect Timeout=30;User Instance=True")) 60 { 61 conn.Open(); 62 using (SqlCommand cmd = conn.CreateCommand()) 63 { 64 cmd.CommandText = "Select * from T_UserInfo where sUser=@sUser";//加"@"参数化查询 65 cmd.Parameters.Add(new SqlParameter("sUser", textBox1.Text)); 66 using (SqlDataReader reader = cmd.ExecuteReader()) 67 { 68 if (reader.Read()) 69 { 70 int errorTimes = reader.GetInt32(reader.GetOrdinal("sErrorTime")); 71 if (errorTimes > 3) 72 { 73 MessageBox.Show("错误次数过多,请三小时后再登录"); 74 return; 75 } 76 string dbpassword = reader.GetString(reader.GetOrdinal("Password")); 77 if (dbpassword == textBox2.Text) 78 { 79 ResetsErrorTime(); 80 MessageBox.Show("登录成功!"); 81 } 82 else 83 { 84 //在同一个连接中,如果SqlDataReader没有关闭,那么是不能执行Update之类的语句的, 85 //因此,Update语句要放在其它函数内。 86 IntsErrorTime();//调用此方法即可 87 MessageBox.Show("登录失败!"); 88 } 89 } 90 else 91 { 92 MessageBox.Show("用户名不存在!"); 93 } 94 } 95 } 96 } 97 } 98 } 99 }
9、导入导出数据案例
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.IO; 10 using System.Data.SqlClient; 11 12 namespace myAdoNet02 13 { 14 public partial class Form2 : Form 15 { 16 public Form2() 17 { 18 InitializeComponent(); 19 } 20 21 private void button1_Click(object sender, EventArgs e) 22 { 23 //单纯从button中的name属性objImport得到ShowDialog()方法是不科学的 24 // 25 OpenFileDialog objImport = new OpenFileDialog(); 26 if (objImport.ShowDialog() != DialogResult.OK) 27 { 28 return; 29 } 30 using (FileStream filestream = File.OpenRead(objImport.FileName)) 31 { 32 using (StreamReader streamreader = new StreamReader(filestream)) 33 { 34 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf; 35 Integrated Security=True;Connect Timeout=30;User Instance=True")) 36 { 37 //创建连接是很耗时的,所以不能每插入一条数据就创建一次连接 38 conn.Open(); 39 using (SqlCommand cmd = conn.CreateCommand()) 40 { 41 cmd.CommandText = "insert into T_UserInfo(sUser,sPassWord) values(@sUser,@sPassWord)"; 42 string line = null; 43 while ((line = streamreader.ReadLine()) != null) 44 { 45 string[] str = line.Split('|'); 46 string sUser = str[0]; 47 string sPassWord = str[1]; 48 cmd.Parameters.Clear(); 49 //参数不能重复添加,在本while中用的是同一个SqlCommand对象,所以要在用完一次后,清除参数 50 cmd.Parameters.Add(new SqlParameter("sUser", sUser)); 51 cmd.Parameters.Add(new SqlParameter("sPassWord", sPassWord)); 52 cmd.ExecuteNonQuery(); 53 } 54 } 55 } 56 } 57 MessageBox.Show("导入成功"); 58 } 59 } 60 } 61 }
10、手机号码归属地查询省市下拉列表实例
(1、数据库资源-全国省市数据库 :http://www.programfan.com/blog/article.asp?id=28128
(2、数据库连接:大概有两种形式
<add key="" value /?
<add name="" connectonstring="" /?
这两方法对应的后台访问方式不一样的
第一种:System.Configuration.ConfigurationManager.AppSettings["myConn"]
第二种:ConfigurationManager.ConnectionStrings["myConn"].ConnectionString
(3、要在类库中找到ConfigurationManager.ConnectionStrings需要添加System.configuration
(4、遇到一个问题,是这样,因为同一个解决方案里我建立了多个项目,项目中基于数据库服务文件名字起的差不多(我都是没改名,自动生成的名字),所以导致后
面在生成调试的时候,总是遇到“数据库未创建实例”这样的错误。
下面是手机号码归属地查询省市下拉列表实例:
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.SqlClient; 10 using System.Configuration; 11 12 namespace myAdoNet02 13 { 14 public partial class Form3 : Form 15 { 16 public Form3() 17 { 18 InitializeComponent(); 19 } 20 //初始化 21 22 private void Form3_Load(object sender, EventArgs e) 23 { 24 string myconStr = ConfigurationManager.ConnectionStrings["myConStr"].ConnectionString; 25 26 using (SqlConnection conn = new SqlConnection(myconStr)) 27 { 28 conn.Open(); 29 using (SqlCommand cmd = conn.CreateCommand()) { 30 cmd.CommandText = "select * from promary"; 31 using (SqlDataReader reader = cmd.ExecuteReader()) { 32 while (reader.Read()) { 33 //构造model类 34 Promary p = new Promary(); 35 //将数据库中的数据赋值给model类中的属性值 36 p.ID = reader.GetInt32(reader.GetOrdinal("proID")); 37 p.sName = reader.GetString(reader.GetOrdinal("proName")); 38 Console.WriteLine(p.ID); 39 Console.WriteLine(p.sName); 40 Console.WriteLine(p); 41 省.Items.Add(p); 42 } 43 } 44 } 45 } 46 } 47 //选择省后,查看市 48 private void 省_SelectedIndexChanged(object sender, EventArgs e) 49 { 50 市.Items.Clear();//清楚旧数据 51 Promary p2 = (Promary)省.SelectedItem; 52 int proID = p2.ID; 53 54 string myConStr = ConfigurationManager.ConnectionStrings["myConStr"].ConnectionString; 55 using (SqlConnection conn = new SqlConnection(myConStr)) 56 { 57 conn.Open(); 58 using(SqlCommand cmd=conn.CreateCommand()){ 59 cmd.CommandText = "select * from city where proID=@ID"; 60 cmd.Parameters.Add(new SqlParameter("ID", proID)); 61 using (SqlDataReader reader = cmd.ExecuteReader()) { 62 while (reader.Read()) { 63 string sCityName = reader.GetString(reader.GetOrdinal("cityName")); 64 市.Items.Add(sCityName); 65 } 66 } 67 } 68 } 69 } 70 71 class Promary 72 { 73 //下拉框的DisplayMember要设置为sName,才能显示出来 74 public string sName { set; get; } 75 public int ID { set; get; } 76 } 77 78 } 79 }
配置文件App.config
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="myConStr" connectionString="Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf; 5 Integrated Security=True;Connect Timeout=30;User Instance=True"/> 6 </connectionStrings> 7 </configuration>
11、手机号码归属地查询
后台代码:
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.SqlClient; 10 using System.Configuration; 11 using System.IO; 12 13 namespace 手机归属地导入查询 14 { 15 public partial class Form1 : Form 16 { 17 public Form1() 18 { 19 InitializeComponent(); 20 } 21 22 private void button1_Click(object sender, EventArgs e) 23 { 24 25 FolderBrowserDialog dlg = new FolderBrowserDialog(); 26 if (dlg.ShowDialog() != DialogResult.OK) 27 { 28 return; 29 } 30 string path = dlg.SelectedPath; 31 //清除数据 32 String connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 33 using (SqlConnection conn = new SqlConnection(connStr)) 34 { 35 conn.Open(); 36 using (SqlCommand cmd = conn.CreateCommand()) 37 { 38 cmd.CommandText = "delete from T_PhoneInfo"; 39 cmd.ExecuteNonQuery(); 40 } 41 } 42 43 44 MessageBox.Show("11!"); 45 string[] files = Directory.GetFiles(path, "*.txt", SearchOption.AllDirectories); 46 47 using (SqlConnection conn = new SqlConnection(connStr)) 48 { 49 conn.Open(); 50 using (SqlCommand cmd = conn.CreateCommand()) 51 { 52 cmd.CommandText = "Insert into T_PhoneInfo(sEndNo,sName,sStartNo) values(@sEndNo,@sName,@sStartNo)"; 53 54 foreach (string file in files)//遍历文件名 55 { 56 string 运营商名称 = Path.GetFileNameWithoutExtension(file); 57 //不用StreamReader,因为文件很小, 58 string[] lines = File.ReadAllLines(file, Encoding.Default); 59 //一次性加载,也不占多少内存,ReadAllLines默认编码是UTF-8 60 61 MessageBox.Show("22!"); 62 foreach (string line in lines) 63 { 64 string[] strs = line.Split('-'); 65 string 开始号码 = strs[0]; 66 string 结束号码 = strs[1]; 67 string 市 = strs[2]; 68 InsertExecuteNonQuery(结束号码, 运营商名称+市, 开始号码); 69 } 70 } 71 } 72 } 73 MessageBox.Show("导入成功!"); 74 } 75 76 public int InsertExecuteNonQuery(string sEndNo, string sName, string sStartNo) 77 { 78 string sqlString = "Insert into T_PhoneInfo(sEndNo,sName,sStartNo) values(@sEndNo,@sName,@sStartNo)"; 79 String connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 80 81 using (SqlConnection connetion = new SqlConnection(connStr)) 82 { 83 connetion.Open(); 84 using (SqlCommand Command = new SqlCommand(sqlString, connetion)) 85 { 86 SqlParameter[] param = new SqlParameter[] 87 { 88 new SqlParameter("@sEndNo", sEndNo), 89 new SqlParameter("@sName", sName), 90 new SqlParameter("@sStartNo", sStartNo) 91 92 }; 93 Command.Parameters.AddRange(param); 94 return Command.ExecuteNonQuery(); 95 } 96 } 97 } 98 } 99 }
program.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Windows.Forms; 5 6 namespace 手机归属地导入查询 7 { 8 static class Program 9 { 10 /// <summary> 11 /// 应用程序的主入口点。 12 /// </summary> 13 [STAThread] 14 static void Main() 15 { 16 //(1)不要忘记把这段代码放进来 17 //一定要放在最开始,否则提示成功也没有导入到数据库中 18 //(2)数据库表设置主键 19 string dataDir = AppDomain.CurrentDomain.BaseDirectory; 20 MessageBox.Show(dataDir); 21 if (dataDir.EndsWith("\\bin\\Debug\\") 22 || dataDir.EndsWith("\\bin\\Release\\")) 23 { 24 //dataDir = System.IO.Directory.GetParent(dataDir).Parent.FullName; 25 dataDir=dataDir.Replace("\\bin\\Debug\\", ""); 26 MessageBox.Show(dataDir); 27 AppDomain.CurrentDomain.SetData("DataDirectory", dataDir); 28 } 29 30 Application.EnableVisualStyles(); 31 Application.SetCompatibleTextRenderingDefault(false); 32 Application.Run(new Form1()); 33 } 34 } 35 }
配置文件:
DataDirectory路径无法读取到,因为在DBbrowser里的mdf并不是debug中的mdf,所以改成绝对路就可以了
备注:如果配置文件不改为绝对路径的话,需要改program.cs中
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <add name="ConnStr" connectionString="Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\phone.mdf; 5 Integrated Security=True;User Instance=True"/> 6 </connectionStrings> 7 </configuration> 8 <!--Connect Timeout=30;-->
12、DataSet的基本使用
(1、封装一个SQLHelper类:(用于被其它代码调用,以下为该类的完整代码)
***1、该类中含有四个方法,分别是:ExecuteNonQuery(string sql, params SqlParameter[] parameters)、ExecuteScalar(string sql, params
SqlParameter[] parameters)、ExecuteReader(string sql, params SqlParameter[] parameters)和ExecuteDataTable(string sql, params
SqlParameter[] parameters)。
在封装这个类之前称简单介绍几点:
a、封装一个SQLHelper类方便使用,提供ExecuteNonQuery(string sql, params SqlParameter[] parameters);ExecuteScalar(string sql,
params SqlParameter[] parameters);ExecuteReader(string sql, params SqlParameter[] parameters);ExecuteDataTable(string sql,
params SqlParameter[] parameters)等方法,网上有微软提供的最全的SQLHelper类,是Enterprise Library中的一部分。
b、用SQLHelper重写登录程序
c、new SqlParameter("e",0)的陷阱
d、sqlconnection在程序中一直保持它open可以吗?对于数据库来说,连接是非常宝贵的资源,一定要用完不close或dispose。
***2、ExecuteScalar 和ExecuteNonQuery的区别
a、ExecuteScala 返回r结果集中第一行的第一列或空引用(如果结果集为空),返回的是一个object
b、ExecuteNonQuery针对Connection 执行 SQL 语句并返回受影响的行数,返回的是一个int型值。
SQLHelp.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.SqlClient; 6 using System.Configuration; 7 using System.Data; 8 9 namespace 封装 10 { 11 class SQLHelp 12 { 13 public static readonly string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 14 /*优点:1、把数据库连接代码都放在SQLHelper中,使代码更简洁 15 * 2、使用DataTable可以随意读取数据库,而之前做的用户登录使用的SqlReader只能逐行往前读*/ 16 17 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) 18 { 19 using (SqlConnection conn = new SqlConnection(connStr)) 20 { 21 conn.Open(); 22 using (SqlCommand cmd = conn.CreateCommand()) 23 { 24 cmd.CommandText = sql; 25 foreach (SqlParameter parameter in parameters) 26 { 27 cmd.Parameters.Add(parameter); 28 } 29 return cmd.ExecuteNonQuery(); 30 } 31 } 32 } 33 34 public static object ExecuteScalar(string sql, params SqlParameter[] parameters) 35 { 36 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 37 using (SqlConnection conn = new SqlConnection(connStr)) 38 { 39 conn.Open(); 40 using (SqlCommand cmd = conn.CreateCommand()) 41 { 42 cmd.CommandText = sql; 43 foreach (SqlParameter parameter in parameters) 44 { 45 cmd.Parameters.Add(parameter); 46 } 47 //执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其它的行或列。 48 return cmd.ExecuteScalar(); 49 } 50 } 51 } 52 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters) 53 { 54 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 55 using (SqlConnection conn = new SqlConnection(connStr)) 56 { 57 conn.Open(); 58 using (SqlCommand cmd = conn.CreateCommand()) 59 { 60 cmd.CommandText = sql; 61 foreach (SqlParameter parameter in parameters) 62 { 63 cmd.Parameters.Add(parameter); 64 } 65 return cmd.ExecuteReader(); 66 } 67 } 68 } 69 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) 70 { 71 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 72 using (SqlConnection conn = new SqlConnection(connStr)) 73 { 74 conn.Open(); 75 using (SqlCommand cmd = conn.CreateCommand()) 76 { 77 cmd.CommandText = sql; 78 foreach (SqlParameter parameter in parameters) 79 { 80 cmd.Parameters.Add(parameter); 81 } 82 //执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其它的行或列。 83 DataSet dataset = new DataSet(); 84 SqlDataAdapter adapter = new SqlDataAdapter(cmd); 85 adapter.Fill(dataset); 86 return dataset.Tables[0]; 87 } 88 } 89 } 90 } 91 }
Form1.cs
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Configuration; 10 using System.Data.SqlClient; 11 using System.IO; 12 using 封装.DataSet1TableAdapters; 13 14 namespace 封装 15 { 16 public partial class Form1 : Form 17 { 18 public Form1() 19 { 20 InitializeComponent(); 21 } 22 23 private void button1_Click(object sender, EventArgs e) 24 { 25 //ExecuteNonQuery 插入数据 26 SQLHelp.ExecuteNonQuery("insert into T_PhoneInfo(sStartNo,sEndNo,sName) values(@sStartNo,@sEndNo,@sName)", new SqlParameter("sStartNo", "11"), new SqlParameter("sEndNo", "11"), new SqlParameter("sName", "gaug懂")); 27 MessageBox.Show("插入数据成功"); 28 } 29 30 private void button2_Click(object sender, EventArgs e) 31 { 32 //ExecuteScalar 查询条数 33 object i = SQLHelp.ExecuteScalar("select count(*)from T_PhoneInfo"); 34 MessageBox.Show(Convert.ToString(i)); 35 } 36 37 private void button3_Click(object sender, EventArgs e) 38 { 39 //SqlDataReader 40 SqlDataReader reader = SQLHelp.ExecuteReader("select * from T_PhoneInfo"); 41 while (reader.Read()) 42 { 43 //运行到这里报错,因为跟数据库的连接已关闭,利用DataSet可以解决这类问题 44 string sName = reader.GetString(reader.GetOrdinal("sName")); 45 } 46 } 47 48 private void button4_Click(object sender, EventArgs e) 49 { 50 //注意只有在小数据量的时候才往DataSet里放, 51 //因为DataSet要占内存,大数据量的时候还是要用DataReader 52 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 53 //定义一个DataSet 54 DataSet dataset = new DataSet(); 55 //数据库操作表 56 using (SqlConnection con = new SqlConnection(connStr)) 57 { 58 con.Open(); 59 using (SqlCommand cmd = con.CreateCommand()) 60 { 61 cmd.CommandText = "select * from T_PhoneInfo"; 62 SqlDataAdapter adapter = new SqlDataAdapter(cmd); //执行select语句(要把cmd传进去) 63 adapter.Fill(dataset);//将执行结果得到的数据填充到dataset中 64 } 65 } 66 //数据库操作列 67 ///取dataset的表中的第0条数据 68 DataTable table = dataset.Tables[0]; 69 for (int i = 0; i < table.Rows.Count; i++) 70 { 71 DataRow row = table.Rows[i]; 72 string sName = Convert.ToString(row["sName"]); 73 MessageBox.Show(sName); 74 } 75 } 76 77 private void button5_Click(object sender, EventArgs e) 78 { 79 DataTable table = SQLHelp.ExecuteDataTable("select * from T_PhoneInfo"); 80 for (int i = 0; i < table.Rows.Count; i++) 81 { 82 DataRow row = table.Rows[i]; 83 string sName = Convert.ToString(row["sName"]); 84 MessageBox.Show(sName); 85 } 86 } 87 88 89 90 } 91 }
(2、利用上述SQLHelper.cs类中的ExecuteDataTable(string sql, params SqlParameter[] parameters)方法,做一个登陆界面
Form1.cs
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Configuration; 10 using System.Data.SqlClient; 11 using System.IO; 12 using 封装.DataSet1TableAdapters; 13 14 namespace 封装 15 { 16 public partial class Form1 : Form 17 { 18 public Form1() 19 { 20 InitializeComponent(); 21 } 22 23 private void button9_Click(object sender, EventArgs e) 24 { 25 //登陆 26 DataTable table = SQLHelp.ExecuteDataTable("select * from T_UserInfo where sUser=@sUser", new SqlParameter("sUser", txtUser.Text)); 27 if (table.Rows.Count <= 0) 28 { 29 MessageBox.Show("用户名不存在"); 30 } 31 else 32 { 33 //用户名存在,已经知道是哪一列了,要返回的行是从零开始的索引 34 DataRow row = table.Rows[0]; 35 int sErroTime = Convert.ToInt32(row["sErroTime"]); 36 if (sErroTime > 3) 37 { 38 MessageBox.Show("登陆次数过多"); 39 return; 40 } 41 string sPassWord = Convert.ToString(row["sPassWord"]); 42 if (sPassWord == txtPassWord.Text) 43 { 44 SQLHelp.ExecuteNonQuery("update T_UserInfo set sErroTime=0 where sUser=@sUser", new SqlParameter("sUser", txtUser.Text)); 45 MessageBox.Show("登陆成功"); 46 } 47 else 48 { 49 SQLHelp.ExecuteNonQuery("update T_UserInfo set sErroTime=sErroTime+1 where sUser=@sUser", new SqlParameter("sUser", txtUser.Text)); 50 MessageBox.Show("密码错误!"); 51 } 52 53 } 54 } 55 56 } 57 }
(3、
**修改DataSet单击事件如下:
a、可以更新行row["Name"]="sUser"、删除行datatable.Rows.Remove()、新增行datatable.NewRow()。这一切都是修改的内存中的DataSet,并没
有修改数据库。
b、可以调用SqlDataAdapter的Update方法将对DataSet的修改提交到数据库,Update方法有很多重载方法,可以提交整个DataSet、DataTable
或者若干DataRow。但是需要为SqlDataAdapter提供DeleteCommand、UpdateCommand、InsertCommand它才知道如何将对DataSet的修改提交
到数据库,由于这几个Command要求的格式非常苛刻,因此开发人员自己写非常困难;可以用SqlCommandBuilder自动生成这几个Command,
SqlCommandBuilder要求表必须有主键。
c、通过DataRow的RowState可以获得行的状态(删除、修改、新增等);调用DataSet的GetChanges()方法得到变化的结果集,降低传递的资源
**测试强类型DataSet单击事件
敲写代码前,还要添加一个数据集DataSet1.xsd文件,然后打开数据集,将表拖到数据集中:
1 private void button6_Click(object sender, EventArgs e) 2 { 3 //陷阱 4 //实参为0与不为0时,这两种情况下将光标放在SqlParameter中按F12转到定义就出现不同的重载函数的情况, 5 //非常诡异类型转换陷阱! 6 SQLHelp.ExecuteDataTable("select * from T_Users where Id=@Id", new SqlParameter("Id", (object)0)); 7 //修改方法是在0前加上object 8 } 9 10 11 12 private void button7_Click(object sender, EventArgs e) 13 { 14 //修改DataSet 15 DataSet dataset = new DataSet(); 16 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; 17 using (SqlConnection conn = new SqlConnection(connStr)) 18 { 19 conn.Open(); 20 using (SqlCommand cmd = conn.CreateCommand()) 21 { 22 cmd.CommandText = "select * from T_UserInfo"; 23 SqlDataAdapter adapter = new SqlDataAdapter(cmd); 24 adapter.Fill(dataset); 25 26 //修改Dataset中的数据 27 DataTable talbe = dataset.Tables[0]; 28 DataRow row = talbe.Rows[0]; 29 row["sUser"] = "lcy"; 30 31 talbe.Rows.RemoveAt(1);//删除一行 32 DataRow otherRow = talbe.NewRow();//新加一行 33 //自动生成更新语句 34 SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 35 //更新DataSet,同步DataSet中的数据到数据库 36 adapter.Update(dataset); 37 } 38 //对于不返回任何键列信息的 SelectCommand,不支持 UpdateCommand 的动态 SQL 生成。 39 //解决方案:表要设置主键 40 } 41 42 } 43 44 private void button8_Click(object sender, EventArgs e) 45 { 46 //测试强类型DataSet 47 T_UserInfoTableAdapter adapter = new T_UserInfoTableAdapter(); 48 封装.DataSet1.T_UserInfoDataTable data = adapter.GetData(); //获取数据 49 50 for (int i = 0; i < data.Count; i++) 51 { 52 封装.DataSet1.T_UserInfoRow userRow = data[i]; 53 MessageBox.Show(userRow.sUser); 54 } 55 }
(4 、可空数据类型
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 namespace 可空数据类型 7 { 8 class Program 9 { 10 static void Main(string[] args) 11 { 12 string s1 = null; 13 //int i1=null; 14 int? i2 = 0; 15 int? i3 = null;//int? →可空的int,解决数据库和C#对于int是否可以为null的不同所设置的 16 if (i3 == null) 17 { 18 Console.WriteLine("i3为空"); 19 } 20 else 21 { 22 i3++; 23 int i4 = (int)i3; //将可空的数据赋给不可空的,会报错,加(int)i3以保证i3一定不为空 24 Console.WriteLine("i3不为空,i3++={0}", i3); 25 } 26 if (i3.HasValue) 27 { 28 int i4 = i3.Value; 29 Console.WriteLine("i3不为空"); 30 } 31 else 32 { 33 Console.WriteLine("i3为空"); 34 } 35 int i6 = 10; 36 int? i5 = i6; //将不可空的赋给可空的,不会报错 37 } 38 }
(5、强类型DataSet判断数据库字段是否为null,强类型DataSet登陆,数据库连接的连续性
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using 封装.DataSet1TableAdapters; 10 using System.Diagnostics; 11 12 namespace 封装 13 { 14 public partial class Form2 : Form 15 { 16 public Form2() 17 { 18 InitializeComponent(); 19 } 20 21 private void button1_Click(object sender, EventArgs e) 22 { 23 //判断数据库字段是否为空 24 T_UserInfoTableAdapter adapter = new T_UserInfoTableAdapter(); 25 封装.DataSet1.T_UserInfoDataTable table = adapter.GetData(); 26 封装.DataSet1.T_UserInfoRow row = table[1]; 27 28 if (row.IssPassWordNull()) 29 { 30 table[1].sPassWord = "888888"; 31 adapter.Update(table); 32 MessageBox.Show("密码为空,已重置密码"); 33 } 34 else { 35 MessageBox.Show("密码为:"+table[0].sPassWord); 36 } 37 38 } 39 40 private void button2_Click(object sender, EventArgs e) 41 { 42 //DataSet 密码登陆 43 T_UserInfoTableAdapter adapter = new T_UserInfoTableAdapter(); 44 封装.DataSet1.T_UserInfoDataTable table = adapter.GetDataByUser(txtUser.Text); 45 46 if (table.Count <= 0) 47 { 48 MessageBox.Show("用户不存在"); 49 } 50 else { 51 封装.DataSet1.T_UserInfoRow row = table[0]; 52 if (row.sErroTime > 3) 53 { 54 MessageBox.Show("登陆次数过多"); 55 return; 56 } 57 else { 58 if (row.sPassWord == txtPassWord.Text) 59 { 60 adapter.UpdateErrorTimeBack(row.sUser); 61 MessageBox.Show("登陆成功"); 62 } 63 else { 64 adapter.UpdateErrorTimeAdd(row.sUser); 65 MessageBox.Show("登陆失败,密码错误"); 66 } 67 } 68 } 69 70 } 71 72 private void button3_Click(object sender, EventArgs e) 73 { 74 75 Stopwatch gameTime = new Stopwatch(); 76 gameTime.Start(); 77 T_UserInfoTableAdapter adapter = new T_UserInfoTableAdapter(); 78 79 /*慢方法:数据连接是间断的 80 for (int i = 0; i < 3000; i++) 81 { 82 adapter.Insert(i.ToString(),i.ToString(),0); 83 }*/ 84 85 /*快方法:数据连接是连续的*/ 86 adapter.Connection.Open(); 87 for (int i = 0; i < 3000; i++) 88 { 89 //先删除上个例子的数据 adapter.Delete(i.ToString(), i.ToString(), 0); 90 adapter.Insert(i.ToString(), i.ToString(), 0); 91 } 92 adapter.Connection.Close(); 93 94 gameTime.Stop(); 95 96 MessageBox.Show(gameTime.Elapsed.ToString()); 97 } 98 99 } 100 }