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