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(); } } }}