关于C#数据库连接的SqlConnection

一. 关于数据库连接的状态

可以对数据库连接对象conn的状态进行判断，如 conn.State == System.Data.ConnectionState.Open 

二.  关于Close()和Dispose的区别

Close() ：使数据库连接对象的状态设为Closed，但不释放connectionString，这意味着我们再次连接只需要调用Open()方法即可

Dispose()：使数据库连接对象的状态设为Closed，并且释放connectionString，这意味着如果我们想再次连接，需要重新初始化SqlConnection对象（实际上需要把SqlConnection对象的connectionString重新赋值即可），然后再调用Open即可打开

下面这个例子区分Close()、和 Dispose()：

namespace WinFormsApp1
{
    internal static class Program
    {
        static void Main()
        {
            //Data Source 里写自己的服务器名;Initial Catalog里写数据库的名字;User ID 里写用户名;pwd里写自己的密码
            String connectionString = "Data Source=;Initial Catalog=;User ID=;pwd=";
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            //Close()
            conn.Close();
            //Close()之后可以再次Open()
            conn.Open();
            if (conn.State == System.Data.ConnectionState.Open)
            {
                Console.WriteLine("数据库连接成功");//数据库连接成功
            }

            //Dispose()
            conn.Dispose();
            //1. Dispose() 后直接调用 Open()
            //conn.Open();//报错
            //Unhandled exception. System.InvalidOperationException: 
            //The ConnectionString property has not been initialized. ConnectionString未初始化
            //2. 如果想重新连接，就初始化conn.connectionSrting
            conn.ConnectionString = "Data Source=;Initial Catalog=;User ID=;pwd=";
            conn.Open();//此时不报错
            if (conn.State == System.Data.ConnectionState.Open)
            {
                Console.WriteLine("数据库连接成功");//数据库连接成功
            }
        }
    }
}

三. 基于WinForm对数据库连接测试

namespace WinFormsApp1
{
    public partial class Form1 : Form
    {
        //Data Source 里写自己的服务器名;Initial Catalog里写数据库的名字;User ID 里写用户名;
        //pwd里写自己的密码
        static string connectionStr = "Data Source=;Initial Catalog=;User ID=;pwd=";
        static SqlConnection conn = new SqlConnection(connectionStr);
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (conn.State == ConnectionState.Open)
            {
                textBox1.Text = "数据库已经处于连接状态";
                return;
            }
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    textBox1.Text = "数据库连接成功";
                }
            }
            catch (Exception exception)
            {
                textBox1.Text = "数据库连接未被初始化";
                Console.WriteLine(exception.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Close();
                if (conn.State == ConnectionState.Closed)
                {
                    textBox1.Text = "数据库连接已关闭";
                }
            }
            catch (Exception exception)
            {
                textBox1.Text = exception.Message;
                Console.WriteLine(exception.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Dispose();
                textBox1.Text = "数据库连接已释放";
            } catch (Exception exception)
            {
                textBox1.Text = "数据库连接未被初始化";
                Console.WriteLine(exception.Message);
            }
        }
    }
}