第五节 4ADO.NET详解

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
/* 执行简单的Insert语句
 * SqlCommand表示向服务提交的一个命令(SQL语句等)
 * CommandText属性为要执行的sql语句，ExecuteNonQuery方法执行一个非查询语句(Update, Insert, Delete等)
 * using (SqlCommand cmd = conn.CreateCommand()){
 *    cmd CommandText = "Insert into T_Users(UserName, Password) Values('admin','88888888')";
 *    cmd.ExecuteNonQuery();
 * }
 * 
 * ExecuteNonQuery返回值是执行的影响行数
 * 常犯错:
 * string username='test';
 * 
 * cmd CommandText = "Insert into T_users(UserName, Passwd value('username','8888888'))";
 *
 * 执行查询
 * 搪行的多行结果集的用ExecuteReader
 * SqlDateReader reader = cmd.ExecuteReader()
 * while(reader.Read()){
 *    Console.WriteLine(read.GetString(1));
 * }
 * reader的GetString,Getint32等方法只接受束和参数，也就是序号，用GetOrdinal方法根据列名动态得到序号
 * 
 */
namespace _4ADO.NET详解
{
    class Program
    {
        static void Main(string[] args)
        {
            string dataDir = AppDomain.CurrentDomain.BaseDirectory;
            if (dataDir.EndsWith(@"\bin\Debug\")
                || dataDir.EndsWith(@"\bin\Release\"))
            {
                dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
            }


            //SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS.AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            //SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS.AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            //using进行连接析放
            /*using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\MyProjects\C#net传智播客\第五季ADO.NET\4ADO.NET详解\4ADO.NET详解\Database1.mdf;Integrated Security=True;User Instance=True")) 
            {
                conn.Open(); //打开链接
                using (SqlCommand cmd = conn.CreateCommand()) 
                {
                    cmd.CommandText = "Insert into MyTable(Name) values('abc')";
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("插入成功");
                }
            }*/

            //以下进行数据读取
            /*
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\MyProjects\C#net传智播客\第五季ADO.NET\4ADO.NET详解\4ADO.NET详解\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = "select * FROM T_User";
                    //创建一个读取的字符, cmd.ExecuteReader进行读取操作
                    using (SqlDataReader reader = cmd.ExecuteReader()) 
                    {
                        //只能逐行向前处理，无法回头，无法往前跳着走
                        while (reader.Read()) {
                            string username = reader.GetString(reader.GetOrdinal("UserName"));
                            //操,用reader.GetInt32还提示类型转换不行，非得用Convert.ToInt32才行 
                            //int id = reader.GetInt32(reader.GetOrdinal("Id"));
                            //不对，这里也是错误的，刚才没看清楚
                            //int id = Convert.ToInt32(reader.GetOrdinal("Id"));
                            
                            //数据类型还真的很严格哈
                            long id = reader.GetInt64(0);
                            //总算试出来了，因为ID为Int64的，所以只能用GetInt64来读取
                            
                            string passwd = reader.GetString(reader.GetOrdinal("Password"));
                            Console.WriteLine("ID={0},UserName={1},Password={2}", id, username, passwd);
                        }
                    }
                }
            }
            Console.WriteLine("数据库链接成功");
            }*/
            

            //登陆操作
            /*Console.WriteLine("请输入用户名:");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码:");
            string password = Console.ReadLine();
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\MyProjects\C#net传智播客\第五季ADO.NET\4ADO.NET详解\4ADO.NET详解\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand()) 
                {
                    cmd.CommandText = "select * FROM T_User WHERE UserName='"+username+"'";
                    //先到表中查用户输入的用户名对应的信息是否存在
                    using (SqlDataReader reader = cmd.ExecuteReader()) 
                    {
                        if (reader.Read())
                        {
                            //用户存在
                            string pass = reader.GetString(reader.GetOrdinal("Password"));
                            //比较数据库中记录的密码和用户输入的密码是否一致
                            if (pass == password)
                            {
                                Console.WriteLine("登陆成功!");
                            }
                            else {
                                Console.WriteLine("密码错误!");
                            }
                        }
                        else {//Read返回false,就是没有查找这个用户名
                            Console.WriteLine("用户不存在!");
                        }
                    }
                }
            }*/

            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\MyProjects\C#net传智播客\第五季ADO.NET\4ADO.NET详解\4ADO.NET详解\Database1.mdf;Integrated Security=True;User Instance=True");
            //....
            conn.Close(); //关闭链接
            conn.Close(); //关闭链接
            conn.Open();  //但关闭后还可以打开的
            conn.Dispose(); //释放资源，
            //conn.Open();    //Disponse以后就不能在open了，资源已经被释放了
            //为什么用using,close: 关闭以后还能打开.
            //Disponse:直接销毁，不能再次使用.
            //using在出了作用域以后调用Disponse,SqlConnection. FileStream等的Disponse内部都会做这样的判断
            //判断有没有close,如果没有close就先close再Disponse
            
            Console.ReadKey();

        }
    }
}