ADO.NET各种数据返回方法使用3

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


namespace ADO.NET1
{
    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);
            }
            /*
             * 连接数据库字符串并插入数据
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                   AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")) //连接字符串
            {
                conn.Open();//打开数据库
                using (SqlCommand cmd = conn.CreateCommand())//创建sqlCommand对象，用于程序和数据库交互的命令
                {
                    cmd.CommandText = "Insert into MyTable1(Name) values('xiaoxiao')";//向表MyTable1中插入数据;
                    cmd.ExecuteNonQuery();//对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于所有其他类型的语句，返回值为 -1。如果发生回滚，返回值也为 -1;
                    Console.WriteLine("插入数据成功");
                }
            }
            //当跳出括号之外时，自动释放数据库连接Dispose端口的资源 
             */


            /*
             * 接受用户输入的用户名和密码并插入表中
             Console.WriteLine("请输入要插入的用户名：");
             string username=Console.ReadLine();
             Console.WriteLine("请输入要插入的密码：");
             string password=Console.ReadLine();

             using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                    AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
             {
                 conn.Open();
                 using (SqlCommand cmd = conn.CreateCommand())
                 {
                    // cmd.CommandText = "Insert into [T_Users](UserName,Password) vslues('"+username"','"+password"')";//values接受输入的字符串
                     cmd.CommandText = "Insert into [T_Users](UserName,Password) values('username','password')";//因为Users是关键字，所以用[],表名T开头，字段F开头
                     cmd.ExecuteNonQuery();
                     Console.WriteLine("插入成功");
                 }
             }
             */


            /*
             * 判断输入的用户名和密码
            Console.WriteLine("请输入用户名");
            string username = Console.ReadLine();
            Console.WriteLine("请输入密码");
            string password = Console.ReadLine();

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                   AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText="select * from T_Users where UserName='"+username+"'";
                    using (SqlDataReader reader =cmd.ExecuteReader())//执行有返回多行结果集的
                    {
                        if (reader.Read())
                        {
                           string dbpassword=reader.GetString(reader.GetOrdinal("password")); 
                            if(password==dbpassword)
                            {
                                Console.WriteLine("输入正确，登录成功");
                            }
                            else
                            {
                                Console.WriteLine("密码错误，登录失败");
                            }
                        }
                        else
                        {
                            Console.WriteLine("用户名错误");
                            
                        }
                    }
                }
            }*/


            /*
             * cmd的ExecuteScalar()方法使用
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                   AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                 conn.Open();
                using(SqlCommand cmd =  conn.CreateCommand())
                {
                   //cmd.CommandText="select count(*) from T_Users";
                   //Console.WriteLine(cmd.ExecuteScalar());//cmd的ExecuteScalar()方法返回第一行第一列的数据,一般用于count(*)的结果返回
                    cmd.CommandText = "select count(*) from T_Users";
                    int i = Convert.ToInt32(cmd.ExecuteScalar());//返回值类型转换，一般的返回值类型是object类型
                    Console.WriteLine(i);
                }
            }
            */



            /*
           // 执行有多行结果集的用ExcuteReader，而reader.GetString(1)方法是列值，用法如下：
           using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                  AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
           {
               conn.Open();
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "select * from T_Users";
                   using (SqlDataReader reader = cmd.ExecuteReader())//得到一个reader对象
                   {
                       while (reader.Read())//调用Read()方法
                       {
                           //Console.WriteLine(reader.GetString(1));//每调用一次Read()方法就继续往下调，直到读到最后一条数据否则调用下一条都是True
                           string username=reader.GetString(reader.GetOrdinal("UserName"));//取用户名名并打印出来
                           int id =reader.GetInt32(reader.GetOrdinal("Id"));//取用户名的序号并打印出来
                          int password =reader.GetInt32(reader.GetOrdinal("Password"));//取用户名的密码并打印出来
                          Console.WriteLine("id={0},UserName={1},password={2}",id,username,password);
                          // reader.GetOrdinal("UserName");//如果不知道列名是第几列，则用 reader.GetOrdinal(列值)方法得到用户名的序号
                       }
                   }
               }
           }
             */
            



            /*
             * 通过OUTPUT inserted.Id获取自增字段的值
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                   AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "Insert into T_Users (UserName,Password) OUTPUT inserted.Id VALUES('admin','888888')";   //得到自增字段的值
                    int i = Convert.ToInt32(cmd.ExecuteScalar());
                    Console.WriteLine("新插入的主键是:[0]", i);
                }
            }
             */
            Console.WriteLine("打开数据库连接成功");
            Console.ReadKey();
        }
    }
}