【.Net基础】浅谈ADO.Net及其应用

一、ADO.Net基础：

1、程序和数据库交互要通过ADO.Net进行，通过ADO.Net就能在程序中执行SQL了。ADO.NET中提供了对各种不同数据库的统一操作接口。
2、直接在项目中内嵌mdf文件的方式使用SQLServer数据库（基于服务的数据库）。mdf文件随着项目走，用起来方便，和在数据库服务器上创建数据库没什么区别，运行的时候会自动附加（Attach）。
3、双击mdf文件会在“服务器资源管理器”中打开，管理方式和在Management Studio没有什么本质不同。要拷贝mdf文件需要关闭所有指向mdf文件的连接。
4、正式生产运行的时候附加到SQLServer上，修改连接字符串即可，除此之处没有任何的区别，在“数据库”节点上点右键“附加”；在数据库节点上->任务->分离就可以得到拷来拷去的mdf文件。
5、用的时候要在控制台、WinForm项目中在Main函数最开始的位置加入备注中的代码。ASP.NET项目中不需要。

6、创建数据库的步骤：在启动项目里右键->添加->新建项->在添加新项对话框中的Visual C#项中选"数据"项->基于服务的数据库->添加->到了数据源配置向导，不要作任何选择（不要选数据库模型），直接点取消就OK
备注：在项目中内嵌mdf文件时加入下面的神奇代码即可：
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);
            }
详细介绍请到：http://www.rupeng.com/forum/thread-11988-1-1.html（如鹏网杨老师介绍）

以上所谓的“神奇”可以简单理解为：

1.AppDomain.CurrentDomain.BaseDirectory 读取当前程序的的路径，返回值为字符串 赋值给dataDir然后  Consoel.WriteLine(dataDir )结果为:
E:\program\ADO.NET MDF文件\ADO.NET MDF文件\bin\Debug\
2.判断是否dataDir 是程序的路径 也就是以bin\Debug\ 或\bin\Release\ 结尾（MDF文件存储在Debug 或Release中 ）
3.通过调用System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName方法 得到dataDir中的父路径(调用两次Parent属性)
注：Directory 属性开用于创建、移动和枚举通过目录和子目录的静态方法。无法继承此类。 GetParent()  方法检索指定路径的父目录，包括绝对路径和相对路径。 FullName 属性获取程序集的显示名称。
4.调用   AppDomain.CurrentDomain.SetData();重新讲dataDir的值赋值给DataDirectory;因为程序是通过DataDirectory
的值来连接MDF文件的 这段代码修改了 MDF文件的路径所 所以程i序可以直接连到跟目录的文件内，而不是Debug目录下的MDF.此代码用于控制台和WINFORM。ASP.NET不用写这段代码 ASP中直接将MDF文件放到App_Data文件夹即可。

二、连接SQLServer及其要注意的事项：（很多人都会遇到相似的情况，包括我自己刚学ADO.Net时也陷入了这个误区）
1、连接字符串：程序通过连接字符串，指定要连哪台服务器上的、哪个实例的、哪个数据库、用什么用户名密码等。
2、项目内嵌mdf文件形式的连接字符串"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Securlty=True;User Instance=True"。其中，".\SQLEXPRESS"表示“本机上的SQLEXPRESS实例（若装的是正式版则去掉，免费版才要加上）”，如果数据库实例名不是SQLEXPRESS，则需要修改。"Database1.mdf"为mdf的文件名。
3、ADO.Net中通过SqlConnection类创建到SQLServer的连接，SqlConnection代表一个数据库连接，ADO.Net中的连接等资源都实现了IDisposable接口，可以使用using进行资源管理。执行备注中的代码如果成功了就OK。
连接数据库的代码：

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|
             DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();
            }
            Console.WriteLine("打开数据库连接成功");//在控制台中测试是否成功连接数据库
            Console.ReadKey();

运行上面的代码会提示AttachDBFilename值无效的情况：

原因可能如下：建表时没把ID设置为标识（自动增长）导致，我的就是这原因……还要把数据库的路径改为根目录下的。如下所示：
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=" + 
                    AppDomain.CurrentDomain.BaseDirectory +"Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"))

最后，我发现我在创建数据库时，在数据源配置向导里选择了数据库模型DataSet后再下一步，也会出现以上情况，只有把数据库路径改为根目录下”AppDomain.CurrentDomain.BaseDirectory“才可以连接上数据库并在VS下查看数据库的数据，否则，在ADO.Net下建立的数据库是没用的，因为查看不了数据。

三、sqlcommand对象的有几个操作方法：

1）ExecuteReader() 方法
1、sqlcommand.ExecuteReader（）该方法主要是执行SQL的select语句，ExecuteReader（）方法主要提供顺序读取数据库中的数据的方法，然后返回SqlReader对象，编者可以使用read的方法循环依次读取每个记录中各字段的内容。若要创建SqlDataReader，必须调用sqlcommand对象的ExecuteReader（）方法来返回数据库中数据表的数据：
      SqlConnection conn = new SqlConnection("  ");
        string sqlcmd="select * from 表名字";
        SqlCommand cmd =new SqlCommand (sqlcmd ,conn );
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
    if  (dr.Read ()==true )
        {
            ListBox1.Items.Add(string .Format ("[{0}]，\"{1}\"", dr[0],dr [1]));
        }
    conn.close();

2、 执行有多行结果集的用ExecuteReader：
SqlDataReader reader=cmd.ExecuteReader();……
while(reader.Read())
{
    Console.WriteLine(reader.GetString(1));
}

2）ExecuteScalar()  方法

1、SqlCommand的ExecuteScalar方法用于执行查询，并返回查询所返回的结果集中第一行的第一列，因为不能确定返回值的类型，所以返回值是object类型。
a、cmd.CommandText="select count(*)from T_Users";
int i=Convert.ToInt32(cmd.ExecuteScalar());
b、cmd.CommandText="select getdate()";
DateTime dt=Convert.ToDateTime(cmd.ExecuteScalar());
2、要得到自动增长字段的主键值，在values关键字前加上output inserted.Id即可，其中Id为主键字段名。执行结果就试插入的主键值，用ExecuteScalar执行最方便。
using(SqlCommand cmd=conn.CreateCommand())
                    {
                        cmd.CommandText = "Insert into T_Users(Name,Password)output inserted.Id values('admin','123456')";
                        //得到自增字段（Id）的值
                        int id = Convert.ToInt32(cmd.ExecuteScalar());
                        Console.WriteLine("新插入的主键为：{0}",id);

3、此方法主要是用来返回一个值的情况，例如使用count()函数求表中的所有记录的条数，或者是使用sum（）求数据的函数求和。sqlcommand.commandtext是为了获取或者设置来执行T——SQL语句、表名和存储过程的。

#region 运用 sqlcommand.Excutescaler（）来返回数据的行数
        SqlConnection conn = new SqlConnection(" ");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        conn.Open();
        cmd .CommandText  = "select count(*) from表名";
        int num = (int)cmd.ExecuteScalar();
        Response.Write(string.Format("{0}", num));
#endregion

3）ExecuteNonQuery()   

该方法主要是执行SQL语句的插入、修改、删除的命令、返回所影响的行数，并不返回操作数据库中数据表的数据。

实例：做一个简单的用户登录窗体：

首先，在Program.cs窗体中添加一段上面提到的“神奇代码”

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace 登录练习
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            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);
            }
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

再者，用户登录窗体中的登录按钮里写如下代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 登录练习
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void IntErrorTimes()
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\UsersDB.mdf;
            Integrated Security=True;Connect Timeout=30;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    using (SqlCommand updateCmd = conn.CreateCommand())
                    {
                        //假如用户输入的用户名和密码错误次数过多，则将数据库中的错误记录次数加1
                        updateCmd.CommandText = "update T_Users Set ErrorTimes=ErrorTimes+1 where UserName=@UserName";
                        updateCmd.Parameters.Add(new SqlParameter("UserName", tBox_UN.Text));
                        updateCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        private void ResetErrorTimes()
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\UsersDB.mdf;
            Integrated Security=True;Connect Timeout=30;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    using (SqlCommand updateCmd = conn.CreateCommand())
                    {
                        //假如用户输入的用户名和密码均正确，则将数据库的错误次数归0，重新统计。
                        updateCmd.CommandText = "update T_Users Set ErrorTimes=0 where UserName=@UserName";
                        updateCmd.Parameters.Add(new SqlParameter("UserName", tBox_UN.Text));
                        updateCmd.ExecuteNonQuery();
                    }
                }
            }
        }
        private void Login_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\UsersDB.mdf;
            Integrated Security=True;Connect Timeout=30;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Select * from T_Users where UserName=@UserName";//加"@"参数化查询
                    cmd.Parameters.Add(new SqlParameter("UserName",tBox_UN.Text));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            int errorTimes = reader.GetInt32(reader.GetOrdinal("ErrorTimes"));
                            if (errorTimes > 3)
                            {
                                MessageBox.Show("错误次数过多，请三小时后再登录");//比如支付宝系统就是这样的
                                return; 
                            }
                            string dbpassword = reader.GetString(reader.GetOrdinal("Password"));
                            if (dbpassword == tBox_Pwd.Text)
                            {
                                ResetErrorTimes();
                                MessageBox.Show("登录成功！");
                            }
                            else
                            {
                                //在同一个连接中，如果SqlDataReader没有关闭，那么是不能执行Update之类的语句的,因此，Update语句要放在其它函数内。
                                IntErrorTimes();//调用此方法即可
                                MessageBox.Show("登录失败！");
                            }
                        }
                        else
                        {
                            MessageBox.Show("用户名不存在！");
                        }
                    }
                }
            }
        }
    }
}

四、Close()与Dispose()的区别:

conn.Close()后再conn.Open()还可以打开；若conn.Dispose()后，则不能
再打开，直接销毁，不能再次使用。
using在出了作用域以后调用Dispose，SqlConnection、FileStream等的Dispose内部都会做这样的判断，判断是否有Close，若没Close就先Close再Dispose。

此总结只是我个人的笔记，还未完善，待续……