C# 创建SQL Server 2005数据库及建表
2010-10-28 17:39:46| 分类: C# | 标签:c# 创建数据库 建表 system.data.sqlclient |字号大中小 订阅
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace notebook
{
class MyDB
{
private const string DB_NAME = "NOTEBOOK";
private const string DB_PATH = "E:\\notebook\\";
private string ConnectionString = "Integrated Security=SSPI;" +
"Initial Catalog=" + DB_NAME + ";" +
"Data Source=localhost;";
private SqlConnection conn = null;
private SqlCommand cmd = null;
private string sql = null;
public MyDB()
{
}
//------------------------------------创建数据库------------------------------------------------------------------------
public void createDB()
{
string newConnectionString = "Integrated Security=SSPI;Initial Catalog=;Data Source=localhost;";
conn = new SqlConnection(newConnectionString);
// 打开数据库连接
if (conn.State != ConnectionState.Open)
conn.Open();
string sql = "CREATE DATABASE " + DB_NAME + " ON PRIMARY" +
"(name=test_data,filename = '" + DB_PATH + DB_NAME + ".mdf', size=3," +
"maxsize=5,filegrowth=10%)log on" +
"(name=mydbb_log,filename='" + DB_PATH + DB_NAME + "_log.ldf',size=3," +
"maxsize=20,filegrowth=1)";
cmd = new SqlCommand(sql, conn);
try
{
cmd.ExecuteNonQuery();
this.createTable();
}
catch (SqlException ae)
{
MessageBox.Show(ae.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
//------------------------------------建表--------------------------------------------------------------------------
private void createTable()
{
// 打开数据库连接
if (conn.State == ConnectionState.Open)
conn.Close();
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());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
}
}
说明:要确保E盘下存在notebook这个文件夹,否则会报错。
转载请说明出处
自动检查待改进