我在做自动更新的操作.如果在某次升级中。数据库的表结构发生了变化,那么我们要在客户端执行一下类似这样的语句。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Z_Test_Exec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Z_Test_Exec]
GOCREATE TABLE [dbo].[Z_Test_Exec] (
[ZID] [int] IDENTITY (1, 1) NOT NULL ,
[ZContent] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO以前我们是在存储过程中执行的。但现在我想用C#来调用而执行它。有什么方法吗?
提示:用ExecuteNonQuery(),是不可以的。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Z_Test_Exec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Z_Test_Exec]
GOCREATE TABLE [dbo].[Z_Test_Exec] (
[ZID] [int] IDENTITY (1, 1) NOT NULL ,
[ZContent] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO以前我们是在存储过程中执行的。但现在我想用C#来调用而执行它。有什么方法吗?
提示:用ExecuteNonQuery(),是不可以的。
Example :
#region 调用Osql.exe执行建库脚本
/// <summary>
/// 调用Osql.exe执行建库脚本
/// </summary>
/// <param name="UserName">数据库访问用户名</param>
/// <param name="Pwd">数据库访问密码</param>private void CreateDataBase ()
{
Process p = new Process();
p.StartInfo.FileName = "cmd.exe";
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = false;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
string Path = Application.StartupPath.ToString();
string Parameter = "osql.exe -U " + uid + " -P " + pwd + " -S "+ ServerName +" -i " + Path + @"\IPMS.sql";
try
{
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
p.Start();
p.StandardInput.WriteLine(Parameter);
p.StandardInput.WriteLine("exit");
p.StandardInput.WriteLine("exit");
p.WaitForExit();
p.Close();
}
catch(Exception e)
{
MessageBox.Show(e.Message);
this.Close();
}
}
以前写过,不过忘了
-_-
string createStr = "CREATE PROCEDURE InsertCategory " +
" @CategoryName nchar(15), " +
" @Identity int OUT " +
"AS " +
"INSERT INTO Categories (CategoryName) VALUES(@CategoryName) " +
"SET @Identity = @@Identity " +
"RETURN @@ROWCOUNT";SqlCommand createCMD = new SqlCommand(createStr, nwindConn);
createCMD.ExecuteNonQuery();但是这种语句要自己写的.用SqlServer 2000 生成的好象总是有语法错误哦,要改一下.
如:
string createStr = @"
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Z_Test_Exec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Z_Test_Exec]
";SqlCommand createCMD = new SqlCommand(createStr, nwindConn);
createCMD.ExecuteNonQuery();createStr = @"
CREATE TABLE [dbo].[Z_Test_Exec] (
[ZID] [int] IDENTITY (1, 1) NOT NULL ,
[ZContent] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
";createCMD.CommandText = createStr;
createCMD.ExecuteNonQuery();