对SQL Server2000数据块进行查询

问题描述：

       针对各种不同的数据库应用环境，ADO.NET提供了四种数据库提供程序，这四种数据库提供程序位于不同的命名空间，但是它们的组成结构相同，使用方法相似。本实验要求利用VS2003平台对SQL2000进行数据库的查询操作。

实验目的：

1、熟悉T-SQL语句

2、在线式非存储过程方式查询数据

3、在线式存储过程方式查询数据

4、断开式非存储过程方式查询数据

5、断开式存储过程方式查询数据

问题分析：

       无论式那种模式，操纵数据库的步骤都一样：

1、确立查询语句：

select * from job where min_lvl >20

结果如下：

job_id job_desc                                           min_lvl max_lvl

------ -------------------------------------------------- ------- -------

3      Business Operations Manager                        176     225

4      Chief Financial Officier                           176     250

5      Publisher                                          151     250

6      Managing Editor                                    141     225

7      Marketing Manager                                  121     200

8      Public Relations Manager                           103     175

9      Acquisitions Manager                               78      175

10     Productions Manager                                78      165

11     Operations Manager                                 78      150

12     Editor                                             28      100

13     Sales Representative                               28      100

14     Designer                                           28      100

（所影响的行数为 12 行）

2、建立连接

3、建立Command对象

4、执行

5、显示结果

关键代码解析

1、在线式非存储过程方式查询数据

//SQL认证模式的建立连接代码如下

//using( SqlConnection sc = new SqlConnection("server =192.168.8.66;database = pubs;uid = sa;pwd = sa;") )

//Windows集成认证模式的建立连接代码如下

using(SqlConnection sc = new SqlConnection("server = .;database = pubs;trusted_connection = sspi;"))

           {//执行可能抛出异常的代码

              try

              {//打开数据库连接

                  sc.Open();

//实例化命令对象

               SqlCommand com =new SqlCommand("select * from job where min_lvl >20",sc);

                  com.CommandType = ommandType.Text;

//只读向前的方式显示数据

                  SqlDataReader sr = com.ExecuteReader();

                  while(sr.Read())

                  {

                     Console.WriteLine(sr[0]);

                  }

              }

              catch(Exception ex)

              {

                  Console.WriteLine(ex.ToString());

              }

              finally

              {

                  if(sc.State ==ConnectionState.Open)

                     sc.Close();

              }

}

2、在线式存储过程方式查询数据

创建存储过程：

USE pubs

--如果存在Sel存储过程，则将其删除

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Sel' AND type = 'P')

   DROP PROCEDURE Sel

GO

create proc Sel

       (@con int)

as

select * from job where min_lvl < @con

显示代码：

//using( SqlConnection sc = new SqlConnection("server =192.168.8.66;database = pubs;uid = sa;pwd = sa;") )

           using(SqlConnection sc = new SqlConnection("server = .;database = pubs;trusted_connection = sspi;"))

           {

              try

              {

                  sc.Open();

//指明存储过程的过程名和连接对象                

SqlCommand com =new SqlCommand("sel",sc);

//指明命令对象的执行命令类型

                  com.CommandType = CommandType.StoredProcedure;

//为命令对象添加参数对象

/*

    需要指明参数对象的存储过程变量名"@con"，数据类型SqlDbType.Int

*/

SqlParameter sp = new SqlParameter("@con",SqlDbType.Int);

//设定值

sp.Value =20;

//设定传值方向

sp.Direction = ParameterDirection.Input;

//为命令对象添加参数对象

com.Parameters.Add(sp);

//执行命令

SqlDataReader sr = com.ExecuteReader();

                  while(sr.Read())

                  {//遍历数据

                     Console.WriteLine(sr[0]);

                  }

              }

              catch(Exception ex)

              {

                  Console.WriteLine(ex.ToString());

              }

              finally

              {

                  if(sc.State ==ConnectionState.Open)

                     sc.Close();

              }

3、断开式非存储过程方式查询数据

//实例化sqlcommand对象

this.sqlSelectCommand1.CommandText ="SELECT id, name, sex, email FROM stuinfo";

this.sqlSelectCommand1.Connection = this.sqlConnection1;

//为数据适配器设置查询命令对象

this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;

//绑定数据源

this.dataGrid1.DataSource =this.dataSet11;

this.dataGrid1.DataMember = "stuinfo";

//填充数据集

this.sqlDataAdapter1.Fill(this.dataSet11);

4、断开式存储过程方式查询数据

//实例化sqlcommand对象,步骤二完全类似

this.sqlSelectCommand1.CommandText =   "Sel";

this.sqlSelectCommand1.CommandType = CommandType.StoredProcedure;

SqlParameter sp = new SqlParameter("@con",SqlDbType.Int);

        sp.Value =20;

        sp.Direction = ParameterDirection.Input;

        this.sqlSelectCommand1.Parameters.Add(sp);

this.sqlSelectCommand1.Connection = this.sqlConnection1;

//为数据适配器设置查询命令对象

this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;

//绑定数据源

this.dataGrid1.DataSource =this.dataSet11;

this.dataGrid1.DataMember = "stuinfo";

//填充数据集

this.sqlDataAdapter1.Fill(this.dataSet11);