sb.AppendFormat(@"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{1}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[{0}]",dbName,dbName);========= 他这个是判断数据库是否已经存在该表,存在的话先删除,要不然肯定抛异常的
这个是建库的SqlConnection conn = new SqlConnection(); string connToMaster = "Server=.\\sqlexpress;DataBase=master;Integrated Security=true"; string connToDb = "Server=.\\sqlexpress;DataBase=test1;Integrated Security=true"; string createDbStr = "Create database [test1]"; string createTableStr = "create table example ([id] int primary key,[name] Nchar(5))"; string insertStr = "insert into example values(1,'Mike')"; string selectStr = "select * from example"; SqlCommand cmd = new SqlCommand(createDbStr, conn); conn.ConnectionString = connToMaster; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); conn.ConnectionString = connToDb; conn.Open(); cmd.CommandText = createTableStr; cmd.ExecuteNonQuery(); cmd.CommandText = insertStr; cmd.ExecuteNonQuery(); conn.Close(); cmd.CommandText = selectStr; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); GridView gv = new GridView(); form1.Controls.Add(gv); gv.AutoGenerateColumns = true; gv.DataSource = dt; gv.DataBind();
sb.AppendFormat(@"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{1}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[{0}]",dbName,dbName);
sb.Append("\r\n");
sb.AppendFormat("CREATE TABLE [dbo].[{0}] (",dbName);
//columns
foreach (DataColumn dc in dt.Columns)
{
sb.AppendFormat("[{0}] varchar(50) null,",dc.ColumnName);
}
string tableSql = sb.ToString().Trim(',') + " )";//然后用SqlCommand执行tableSql就行了
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "CREATE DATABASE test";
cmd.ExecuteNonQuery();
if exists (select * from sysobjects where objectproperty(object_id('table_name'),'istable')=1)drop table table_name
和数据库create table..一样的,只是转让用sqlCommand来执行sql语句SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand comm=new SqlCommand();
comm.Connection=conn;
string sql="";
sql="CREATE TABLE @tablename";
sql+="( ";
sql+="date_in datetime,";
sql+="user_id int,";
sql+="myuser_name varchar(200)";
sql+=")";
comm.CommandText=sql;
SqlParameter parm1=new SqlParameter("@tablename",SqlDbType.VarChar(20));
parm1.Value=s;
comm.Parameters.Add(parm1);
conn.Open();
comm.ExecuteNonQuery();
如果加约束之类的如下:
cmd.CommandText = "Create Table Admin (ID integer identity(1,1) primary key,UserNameVarChar(50),UserPassVarChar(50));";
cmd.ExecuteNonQuery();
drop table [dbo].[{0}]",dbName,dbName);=========
他这个是判断数据库是否已经存在该表,存在的话先删除,要不然肯定抛异常的
string connToMaster = "Server=.\\sqlexpress;DataBase=master;Integrated Security=true";
string connToDb = "Server=.\\sqlexpress;DataBase=test1;Integrated Security=true";
string createDbStr = "Create database [test1]";
string createTableStr = "create table example ([id] int primary key,[name] Nchar(5))";
string insertStr = "insert into example values(1,'Mike')";
string selectStr = "select * from example";
SqlCommand cmd = new SqlCommand(createDbStr, conn); conn.ConnectionString = connToMaster;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close(); conn.ConnectionString = connToDb;
conn.Open();
cmd.CommandText = createTableStr;
cmd.ExecuteNonQuery();
cmd.CommandText = insertStr;
cmd.ExecuteNonQuery();
conn.Close(); cmd.CommandText = selectStr;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt); GridView gv = new GridView();
form1.Controls.Add(gv);
gv.AutoGenerateColumns = true;
gv.DataSource = dt;
gv.DataBind();
private void button1_Click(object sender, EventArgs e)
{
String database_string = "server=zp;user id=sa;password=sa";
System.Data.SqlClient.SqlConnection con1 = new System.Data.SqlClient.SqlConnection(database_string);
con1.Open();
String command_string = "create database test_cSharp";
System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand(command_string, con1);
cmd1.Prepare();
cmd1.ExecuteNonQuery();
con1.Close();
}
创建表
private void button2_Click(object sender, EventArgs e)
{
String database_string = "server=zp;database=test_cSharp;user id=sa;password=sa";
SqlConnection con1 = new SqlConnection(database_string);
con1.Open();
String command_string = "create table 表名(字段1 int primary key,字段2 varchar(10))";
SqlCommand cmd1 = new SqlCommand(command_string, con1);
cmd1.Prepare();
cmd1.ExecuteNonQuery();
con1.Close(); }