C#中创建SQL Server数据库及表的方法

悟静 2013-04-05

展开全文

本文介绍了在C#程序中创建SQL SERVER数据库的方法，在此基础上介绍了向数据库中添加数据的方法。

首先，我们在程序中动态地创建SQL Server数据库。

我们将数据库创建在C:\mysql目录下，所以读者要练习该实例的话得先在C:下创建一个名为mysql的文件夹，否则会出错！创建数据库的关键是函数中的sql对象，通过该对象我们指定了数据库文件的一些基本属性。之后，我们新创建了一个SqlCommand对象，通过该对象我们就实际完成了对数据库的操作。函数的实现如下：

private void button1_Click(object sender, System.EventArgs e)
{
// 打开数据库连接
if( conn.State != ConnectionState.Open)
     conn.Open();
string sql = "CREATE DATABASE mydb ON PRIMARY"
                      +"(name=test_data, filename = 'C:\\mysql\\mydb_data.mdf', size=3,"
                      +"maxsize=5, filegrowth=10%) log on"
                     +"(name=mydbb_log, filename='C:\\mysql\\mydb_log.ldf',size=3,"
                      +"maxsize=20,filegrowth=1)";
   cmd = new SqlCommand(sql, conn);
   try { cmd.ExecuteNonQuery(); }
catch(SqlException ae)
{
       MessageBox.Show(ae.Message.ToString());
   }
}

其次，我们通过CREATE TABLE语句创建基本表，并向该表中添加4条数据。函数的实现如下：

private void button2_Click(object sender, System.EventArgs e)
       {
              // 打开数据库连接
               if( conn.State == ConnectionState.Open)
                     conn.Close();
               ConnectionString ="Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;";
                conn.ConnectionString = ConnectionString;
                conn.Open();
                 sql = "CREATE TABLE myTable"+ "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY,"
                        + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)";
                cmd = new SqlCommand(sql, conn);
                  try {
                               cmd.ExecuteNonQuery(); // 向表中添加记录
                               sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) "+ "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) " ;
                              cmd = new SqlCommand(sql, conn);
                              cmd.ExecuteNonQuery();
                              sql = "INSERT INTO myTable(myId, myName,myAddress, myBalance) "+ "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) " ;
                              cmd = new SqlCommand(sql, conn);
                              cmd.ExecuteNonQuery();
                              sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) "+ "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) " ;
                              cmd = new SqlCommand(sql, conn);
                              cmd.ExecuteNonQuery();
                               sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) "+ "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) " ;
                              cmd = new SqlCommand(sql, conn);
                               cmd.ExecuteNonQuery(); }
                  catch(SqlException ae) { MessageBox.Show(ae.Message.ToString()); }
       }