1.查询单个值

SQL语言使用select进行查询。查询返回结果有多有少，通常来说，会返回多行多列的一个结果集。

示例1：查询单个值

1

	select * from student

有些SQL语句必定只返回单个值，不会返回多行，也不会返回多列。 如下：

1
2

	select count(*) from student
	Select max(Id) from student

那么具体如何查询单个值呢？

执行命令ExecuteScalar方法 其返回结果为object，需要进行类型转换。

示例2：查询学生总数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

	String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456";	//定义连接字符串
	SqlConnection connection = new SqlConnection(s);		//定义connection对象
	SqlCommand command = new SqlCommand();
	string sql = "select count(*) from student";
	command.CommandText = sql;
	command.Connection = connection;
	connection.Open();
	int n = (int)command.ExecuteScalar();
	connection.Close();
	MessageBox.Show("共有" + n + "个学生。");

示例2.1：查看男、女数量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

        private void button1_Click(object sender, EventArgs e)
        {
            string s = "server=.;database=SampleDb;user id=sa;password=123456";
            SqlConnection connection = new SqlConnection(s);
            string sql = "select count (*) from student ";
            connection.Open();
            if (radioButton1.Checked==true)
            {
                sql += "where Gender ='男' ";
            }
             if (radioButton2.Checked==true)
            {
                sql += "where Gender ='女'";
            }
             SqlCommand command = new SqlCommand();
             command.CommandText = sql;
             command.Connection = connection;
             int n = (int)command.ExecuteScalar();
             MessageBox.Show("人数为："+n);
             connection.Close();
        }

示例3：用户登录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

	//登录按钮
	String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456";
	SqlConnection connection = new SqlConnection(s);
	SqlCommand command = new SqlCommand();
	string sql = string.Format("select count(*) from where Id='{0}' and Password='{1}'", textBox1.Text, textBox2.Text);
	command.CommandText = sql;
	command.Connection = connection;
	connection.Open();
	int n = (int)command.ExecuteScalar();
	connection.Close();
	if (n > 0)
	{
	  MessageBox.Show("登陆成功");
	  this.DialogResult = DialogResult.OK;
	  this.Close();
	}
	  else MessageBox.Show("用户名或密码不正确");
	//关闭按钮
	this.Close();

2.使用DataReader读取数据

说明： - 当查询结果可能返回多行多列时，需要使用DataReader读取返回的数据; - DataReader的功能是每次一行从读取数据。

主要方法： - Read方法：从数据库读取一行数据，返回bool，为true说明读取到了数据，false说明已经全部读取完成，无数据可读了。 - 【”列名”】：取得当前行某字段的值，返回object类型，通常需要类型转换 - Close：使用完毕后关闭，释放资源。

DataReader读取数据的步骤： - 1.创建连接、创建命令、打开连接； - 2.SqlCommand.ExecuteReader返回一个SqlDataReader对象； - 3.SqlDataReader.Read方法读取一行数据； - 4.SqlDataReader[“列名”]读取当前行的某一列，为object类型，需要类型转换； - 5.重复执行第(3)步骤读取其他字段； - 6.转到第(2)步读取下一行数据

示例4：读取所有学生的信息并输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456;";
SqlConnection connection = new SqlConnection(s);
SqlCommand command = new SqlCommand();
string sql = "select *from student";
command.CommandText = sql;
command.Connection = connection;
connection.Open();
string id, name;
int age;
SqlDataReader reader = command.ExecuteReader();
while(reader.Read() == true)
 {
	id = (string)reader["id"];
	name = (string)reader["name"];
	age = (int)reader["age"];
	textBox1.Text +=(id +","+name+","+age+"\r\n");
	//Console.WriteLine("{0}\t{1}\t{2}\t",id,name,age);      //控制台输出
 }
reader.Close();
connection.Close();

3.实体类封装数据

前面我们说过DataReader对象，但它直接读取数据时有些缺点： - 返回object弱类型 - 列名为字符串，无法智能提示和检验语法错误

在实际项目中，通常用实体类来封装数据。将DataReader读取的数据保存到实体类中，然后再使用。

使用实体类封装数据的好处： - 符合面向对象的思想 - 强类型 - 智能提示

实体类

实体类的创建：与数据库表一一对应。 实体类与数据库表的对应关系为： - 表对应实体类 - 表中字段对应实体类的属性

示例5：添加学生实体类

添加到Student.cs类中。

1
2
3
4
5
6
7
8
9

public class Student
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public int Age { get; set; }
        public int Grade { get; set; }
        public string Major { get; set; }
    }

4.DataGridView显示数据

DataGridView主要属性： - DataSource：数据源，要显示的数据的来源； - ReadOnly：只读； - AutoGenerateColumns：是否自动生成列。

示例6：把学生信息显示在DataGridView上

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

            String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456;";
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand();
            string  sql ="select *from student where 1=1 ";
            if (textBox1.Text !="")
                sql += " and grade =  "+textBox1.Text;
            if (textBox2.Text != "")
                sql += " and gender = '"+textBox2.Text+"'";
            command.CommandText = sql;
            command.Connection = connection;
            connection.Open();
            List<Student> list = new List<Student>();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read() == true)
            {
                Student student = new Student();
                student.Id =(string)reader["Id"];
                student.Name=(string)reader["Name"];
                student.Gender=(string)reader["Gender"];
                student.Age=(int)reader["Age"];
                student.Grade=(int)reader["Grade"];
                student.Major=(string)reader["Major"];
                list.Add(student);
            }
                 reader.Close();
                connection.Close();
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = list;

5.数据访问类DbHelper

问题： - ADO.NET执行命令有7个步骤，每次进行数据库操作（无论C、R、U、D）都要执行。 - 7个步骤大多代码是相同的。

解决： - 可编写一个类，将7个步骤封装成方法，在其他地方直接调用。

具体细节： 1.类名DbHelper(静态类) 2.字段： - String connstr;：表示字符串 - SqlConnection Connection;：表示连接对象 3.方法： - Int executeNonQuery(string sql); - Object executeScalar(string sql); - SqlDataReader executeReader(string sql); - Void closeConnection();

DbHelp.cs类：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

public static class DbHelper
    {
        public static int execute(string sql) {
            String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456;" ;
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = connection;
            connection.Open();
            int n = command.ExecuteNonQuery();
            connection.Close();
            return n;
        }
        public static object selstu(string sql) {
            String s = "Server=.;Database=SampleDb;User Id=sa;Password=123456;";
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            object o = command.ExecuteScalar();
            connection.Close();
            return o;
        }
    }

6.示例7：按年级查询学生

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

        private void button1_Click(object sender, EventArgs e)
        {
            textBox2.Text = "";
            string s = "server=.;database=SampleDb;user id=sa;password=123456";
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand();
            string sql = "select *from student ";
            if (textBox1.Text != "")
            {
                sql += " where grade='"+textBox1.Text+"'";
            }
            command.CommandText = sql;
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while(reader.Read()==true)
            {
                string Id=(string)reader["id"];
                string   Name=(string)reader["name"];
                string Gender=(string)reader["gender"];
                string Major=(string)reader["major"];
                int Age=(int)reader["age"];
                int Grade=(int)reader["grade"];
                textBox2.Text+=("学号："+Id+"  姓名："+Name+"  性别:"+Gender+"  年龄:"+Age+"  年级："+Grade+"  主修："+Grade+"\r\n");
            }
            reader.Close();
            connection.Close();
        }

7.示例8：按课程号删除信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

        private void button1_Click(object sender, EventArgs e)
        {
            string s = "server=.;database=SampleDb;user id=sa;password=123456; ";
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand();
            string sql =string.Format("delete from Lesson where id='{0}'",textBox1.Text);
            command.CommandText = sql;
            command.Connection = connection;
            connection.Open();
            int n = command.ExecuteNonQuery();
            connection.Close();
            MessageBox.Show("已删除"+n+"条记录");
        }

8.示例9：添加课程信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

        private void button1_Click(object sender, EventArgs e)
        {
            string s = "server=.;database=SampleDb;user id=sa;password=123456; ";
            SqlConnection connection = new SqlConnection(s);
            SqlCommand command = new SqlCommand();
            string sql = string.Format("insert into Lesson (Id,Name) values ('{0}','{1}')",textBox1.Text,textBox2.Text);
            command.CommandText = sql;
            command.Connection = connection;
            connection.Open();
            int n = command.ExecuteNonQuery();
            connection.Close();
            MessageBox.Show("添加了"+n+"条记录");
        }