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项目中不需要。

View Code
 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();下面敲上这样一段代码即可插入数据库数据：

View Code
 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();下面敲上这样一段代码即可

View Code
 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执行最方便。

 

View Code
 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方法根据列名动态得到序号了。

View Code
 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中添加参数。

View Code
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、模拟窗体登陆,并且设置登陆错误次数

View Code
 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、导入导出数据案例

View Code
 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、数据库资源-全国省市数据库     （2、数据库连接：大概有两种形式

<add key="" value /?
<add name="" connectonstring="" /?
这两方法对应的后台访问方式不一样的
第一种：System.Configuration.ConfigurationManager.AppSettings["myConn"]
第二种：ConfigurationManager.ConnectionStrings["myConn"].ConnectionString

   (3、要在类库中找到ConfigurationManager.ConnectionStrings需要添加System.configuration

  （4、遇到一个问题，是这样，因为同一个解决方案里我建立了多个项目，项目中基于数据库服务文件名字起的差不多（我都是没改名，自动生成的名字），所以导致后

面在生成调试的时候，总是遇到“数据库未创建实例”这样的错误。

 下面是手机号码归属地查询省市下拉列表实例：

View Code
 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

View Code
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、手机号码归属地查询

 后台代码：

View Code
 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