关于C#程序中建库建表
在sql中写了一个T-Sql脚本,执行后建库,建表,建视图,存储过程,在sql中运行正常现在想要在C#程序中执行这sql脚本,希望也可以达到像在sql中的效果,应该怎样写,完整点.
sb = new StringBuilder();
sb.Append("use master");
sb.Append(" if exists(select * from sysdatabases where name='RemPacs_1')");
sb.Append(" drop database RemPacs_1");
sb.Append(" create database RemPacs_1");
sb.Append(" use RemPacs_1");
sb.Append(" if exists(select * from sysobjects where name='GLB_AgeUnit')");
sb.Append(" drop table GLB_AgeUnit");
sb.Append(" create table GLB_AgeUnit");
sb.Append(" (");
sb.Append(" AgeUnitID int not null primary key,");
sb.Append(" [Name] nvarchar(4) not null,");
sb.Append(" DisplayNo int not null");
sb.Append(" )");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(1,'岁',1)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(2,'月',2)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(3,'周',3)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(4,'天',4)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(5,'时',5)");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery();
我这样写的 ,但是建不了 --------------------编程问答--------------------
StringBuilder sb = new StringBuilder();
我刚才试试了,分开创建就可以了。。。。另外用AppendLine更好一些。
sb.Append("use master");
sb.AppendLine(" if exists(select 1 from sysdatabases where name='RemPacs_1')");
sb.AppendLine(" drop database RemPacs_1");
sb.AppendLine(" create database RemPacs_1");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery();
sb = new StringBuilder();
sb.AppendLine(" use RemPacs_1");
sb.AppendLine(" if exists(select 1 from sysobjects where name='GLB_AgeUnit')");
sb.AppendLine(" drop table GLB_AgeUnit");
sb.AppendLine(" create table GLB_AgeUnit");
sb.AppendLine(" (");
sb.AppendLine(" AgeUnitID int not null primary key,");
sb.AppendLine(" [Name] nvarchar(4) not null,");
sb.AppendLine(" DisplayNo int not null");
sb.AppendLine(" )");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(1,'岁',1)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(2,'月',2)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(3,'周',3)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(4,'天',4)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(5,'时',5)");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery(); --------------------编程问答--------------------
格式错了,重发
--------------------编程问答-------------------- 如果是SqlServer, 既然有了T-sql,就用 osql就可以执行了,下面是一个封装函数
我刚才试试了,分开创建就可以了。。。。另外用AppendLine更好一些。
StringBuilder sb = new StringBuilder();
sb.Append("use master");
sb.AppendLine(" if exists(select 1 from sysdatabases where name='RemPacs_1')");
sb.AppendLine(" drop database RemPacs_1");
sb.AppendLine(" create database RemPacs_1");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery();
sb = new StringBuilder();
sb.AppendLine(" use RemPacs_1");
sb.AppendLine(" if exists(select 1 from sysobjects where name='GLB_AgeUnit')");
sb.AppendLine(" drop table GLB_AgeUnit");
sb.AppendLine(" create table GLB_AgeUnit");
sb.AppendLine(" (");
sb.AppendLine(" AgeUnitID int not null primary key,");
sb.AppendLine(" [Name] nvarchar(4) not null,");
sb.AppendLine(" DisplayNo int not null");
sb.AppendLine(" )");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(1,'岁',1)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(2,'月',2)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(3,'周',3)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(4,'天',4)");
sb.AppendLine(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(5,'时',5)");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery();
/// <summary>
/// 执行单条命令
/// </summary>
/// <param name= "commandText "> 命令文本 </param>
/// <returns> 命令输出文本 </returns>
public static string ExeCommand(string commandText)
{
Process p = new Process();
p.StartInfo.FileName = "cmd.exe ";
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
string strOutput = null;
try
{
p.Start();
p.StandardInput.WriteLine(commandText);
p.StandardInput.WriteLine("exit ");
strOutput = p.StandardOutput.ReadToEnd();
//Console.Write(strOutput);
p.WaitForExit();
p.Close();
}
catch (Exception e)
{
strOutput = e.Message;
}
return strOutput;
}
string commandText= "osql.exe -E /Slocalhost /dDvision3 /iDBCreate.sql "; --------------------编程问答--------------------
sb = new StringBuilder();
sb.Append("use master");
sb.Append(" if exists(select * from sysdatabases where name='RemPacs_1')");
sb.Append(" drop database RemPacs_1");
sb.Append(" create database RemPacs_1");
sb.Append(" go");
sb.Append(" use RemPacs_1");
sb.Append(" if exists(select * from sysobjects where name='GLB_AgeUnit')");
sb.Append(" drop table GLB_AgeUnit");
sb.Append(" create table GLB_AgeUnit");
sb.Append(" (");
sb.Append(" AgeUnitID int not null primary key,");
sb.Append(" [Name] nvarchar(4) not null,");
sb.Append(" DisplayNo int not null");
sb.Append(" )");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(1,'岁',1)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(2,'月',2)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(3,'周',3)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(4,'天',4)");
sb.Append(" insert into GLB_AgeUnit (AgeUnitID,[Name],DisplayNo) values(5,'时',5)");
SqlCommand cmd = new SqlCommand(sb.ToString(), GetConnection());
int i = cmd.ExecuteNonQuery();
加个go不就可以了 --------------------编程问答-------------------- 在查询分析器中加go可以,从C#中不行 --------------------编程问答--------------------
请解释一下commandText 路径怎么写 --------------------编程问答-------------------- 为啥不用存储过程执行呢 --------------------编程问答--------------------
怎么写啊 --------------------编程问答-------------------- 把你刚才写的sql语句创建成存储过程,然后执行的时候指定CommandType为CommandType.StoredProcedure --------------------编程问答-------------------- CommandText就是存储过程名称
补充:.NET技术 , C#