C#关于 SQL Server 数据库的操作

C# ：创建SQL Server数据库、设置SQL Server数据库为只读状态、修改和压缩SQL Server数据库、新建（删除和修改）数据表、修改（新增和删除）数据列

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using System.Net;
using System.IO;
using System.Threading;
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            throw new NotImplementedException();
        }
        private void button1_Click(object sender, EventArgs e)
        {//创建SQL Server数据库
            string MySQL = "use master;" +
            "IF DB_ID(N'MyDatabase') IS NOT NULL " +
            "DROP DATABASE MyDatabase;" +
            "CREATE DATABASE MyDatabase " +
            "ON(NAME=MyDatabase_dat,FILENAME=\"C:\\MyDatabase.mdf\",SIZE=5,MAXSIZE=10,FILEGROWTH=1) " +
            "LOG ON(NAME=MyDatabase_log,FILENAME=\"C:\\MyDatabase.ldf\",SIZE=2,MAXSIZE=5,FILEGROWTH=1)";
            SqlConnection MyConnection = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功创建数据库", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {//设置SQL Server数据库为只读状态
            string MySQL = "use master; " +
                  "IF DB_ID(N'MyDatabase') IS NOT NULL " +
                "EXEC sp_dboption 'MyDatabase', 'read only', 'TRUE'";
            // "EXEC sp_dboption 'MyDatabase', 'read only', 'FALSE'";
            SqlConnection MyConnection = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("设置MyDatabase数据库为只读状态操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {//设置SQL Server数据库为脱机状态
            string MySQL = "use master; " +
                  "IF DB_ID(N'MyDatabase') IS NOT NULL " +
                "EXEC sp_dboption 'MyDatabase', 'offline', 'TRUE'";
            //   "EXEC sp_dboption 'MyDatabase', 'offline', 'false'";
            SqlConnection MyConnection = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("设置MyDatabase数据库为脱机状态操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button5_Click(object sender, EventArgs e)
        {//压缩SQL Server数据库
            string MySQL = "use master;" +
            "IF DB_ID(N'MyDatabase') IS NOT NULL " +
            "DBCC SHRINKDATABASE (MyDatabase, 90) ";
            SqlConnection MyConnection = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功压缩数据库", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button6_Click(object sender, EventArgs e)
        {//在数据库中新建数据表
            string MySQL = "IF OBJECT_ID(N'MyDatabase..商品清单', N'U') IS NOT NULL " +
                 "DROP TABLE 商品清单;" +
                "CREATE TABLE 商品清单 (" +
                "[货号] [char] (14) NOT NULL Primary Key," +
                "[条码] [char] (14) NULL ," +
                "[拼音编码] [char] (40) NULL," +
                "[品名] [varchar] (80) NULL ," +
                "[规格] [varchar] (40) NULL ," +
                "[单位] [char] (6) NOT NULL ," +
                "[产地] [varchar] (50) NULL ," +
                "[类别] [char] (20) NULL ," +
                "[进货价] [decimal] (28,6) NULL default(0)," +
                "[销售价1] [decimal] (28,6) NULL default(0)," +
                "[销售价2] [decimal] (28,6) NULL default(0)," +
                "[最低售价] [decimal] (28,6) NULL default(0))";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在MyDatabase数据库中创建数据表", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button7_Click(object sender, EventArgs e)
        {//在数据库中删除数据表
            string MySQL = "IF OBJECT_ID(N'MyDatabase..商品清单', N'U') IS NOT NULL " +
                 "DROP TABLE 商品清单;";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在MyDatabase数据库中删除数据表", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button8_Click(object sender, EventArgs e)
        {//在数据表中修改数据列
            //"[产地] [varchar] (50) NULL ,"
            string MySQL = "ALTER TABLE 商品清单 ALTER COLUMN [产地] [char](100) NOT NULL;";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在“商品清单”数据表中修改数据列", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button9_Click(object sender, EventArgs e)
        {//在数据表中添加数据列
            string MySQL = "ALTER TABLE 商品清单 ADD [检验员] [varchar] (50) NULL;";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在“商品清单”数据表中添加数据列", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button10_Click(object sender, EventArgs e)
        {//在数据表中删除数据列
            string MySQL = "ALTER TABLE 商品清单 DROP COLUMN [检验员] ;";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在“商品清单”数据表中删除数据列", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
        private void button11_Click(object sender, EventArgs e)
        {//删除指定数据表中的所有记录
            string MySQL = "TRUNCATE TABLE 商品清单;";
            SqlConnection MyConnection = new SqlConnection("Data Source = .;Database = MyDatabase;Integrated Security=SSPI");
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            try
            {
                MyCommand.Connection.Open();
                MyCommand.ExecuteNonQuery();
                MessageBox.Show("成功在“MyDatabase”数据库中删除“商品清单”数据表的所有记录", "信息提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                MyConnection.Close();
            }
        }
    }
}