SqlConnection对象和using本质, ExecuteScalar和ExecuteReader的区别

1.连接字符串

 string connectString = "server=.;database=StudentMIS;integrated security=true";

2.指定数据源：data source/server ，IP地址，如果是本机可以使用localhost或者local或者‘ .’，server——服务器名
3.指定数据库：database/inital catalog
4.身份验证方式：1.sql验证：userid= ;password= ; 2.windows验证：integrated security=true
5.

  string connectString = "server=.;database=StudentMIS;integrated security=true";
            SqlConnection connection = new SqlConnection(connectString);
            connection.Open();//打开连接
            Console.WriteLine(connection.State.ToString());//查看连接状态
            //..读取数据的代码
            //connection.Close();//关闭连接
          Console.WriteLine(connection.State.ToString());

6…using有两个作用：一个是引用命名空间，另一个是在using这个代码块结束后，自动调用Close()方法

 using ( SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open();
               // connection.Open();//连接不能重复打开，但是能重复关闭
                Console.WriteLine(connection.State.ToString());
            }

在连接数据库时要加上

using System.Data;
using System.Data.SqlClient;

using的本质：

 1.创建对象 
 2.try..   
 3.finall(.Dispose())    

7.查询一个结果

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace 查询一个结果
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "data source=.;database=StudentMIS;integrated security=true";
            using(SqlConnection connection=new SqlConnection(connStr))
            {
                connection.Open();
                using(SqlCommand cmd=new SqlCommand())
                {
                    //string sql = "select count(1) from TbStudent";
                    //string sql = "select stuName from TbStudent where stuNumber='184804060'";
                    string sql = "select stuName,stuNumber from TbStudent";
                    cmd.CommandText = sql;
                    cmd.Connection = connection;
                    //ExecuteScalar()用来执行一条select语句，并且返回一个数据
                    //int count = Convert.ToInt32(cmd.ExecuteScalar());
                    // Console.WriteLine("全院一共有{0}个同学", count);
                    Console.WriteLine(cmd.ExecuteScalar().ToString());
                   //ExecuteScalar()用来执行一条select语句，并且返回查询结果第1行第1列的数据
                }

            }
        }
    }
}

ExecuteScalar()用来执行一条select语句，并且返回查询结果第1行第1列的数据
8.查询表值结果集

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace 查询表值结果集
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "data source=.;database=StudentMIS;integrated security=true";
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    string sql = "select clsId,clsName,clsTeacher from TbClass";
                    cmd.CommandText = sql;
                    cmd.Connection = connection;
                    //ExecuteReader()用来执行一条查询结果为表值结果集的select语句，
                    //它返回1个SqlDataReader类型的对象
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            //1.使用字段名作为索引
                            //注意：不能使用没有在sql语句中出现的字段名

                            //Console.Write(reader["clsId"].ToString() + "\t");
                            //Console.Write(reader["clsTeacher"].ToString() + "\t");
                            //Console.Write(reader["clsName"].ToString() + "\t");
                            //2.使用索引
                            //Console.Write(reader[2].ToString() + "\t");
                            //Console.Write(reader[0].ToString() + "\t");
                            //Console.Write(reader[1].ToString() + "\t");
                            //3.
                             Console.Write(reader.GetInt32(0).ToString() + "\t");
                            Console.Write(reader.GetString(1).ToString() + "\t");
                            Console.Write(reader.GetValue(2).ToString() + "\t");
                            Console.WriteLine();
           

                        }
                    }
                }
            }
        }
    }
}