CODEWITHSUNDEEP
c#sql
Imen
Imen

Reputation: 59

set value to sql file

this is my sql file :

  USE [master]
GO
DECLARE @DBName nvarchar(50) = ' DataBaseName'
EXEC('ALTER DATABASE ' + @DBName + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE')
GO

and I run it with c# code

 string sqlConnectionString = txtConstring.Text; //connection string
FileInfo file = new FileInfo(path + "Procedure_fn.sql"); //*.sql file path
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);

I wonder how can we set the value of the local variable "DataBaseName" in c#

Upvotes: 1

Views: 1093

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415800

Normally I'd recommend dropping the variable declaration and other accoutrements from the file completely, so that the *.sql file becomes simply:

EXEC('ALTER DATABASE ' + @DBName + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE')

And then I'd tell you to call it like this:

string script = File.ReadAllText(Path.Combine(path, "Procedure_fn.sql"));
using (SqlConnection conn = new SqlConnection(txtConstring.Text))
using (SqlCommand cmd = new SqlCommand(script, conn))
{
    cmd.Parameters.Add("@DBName", SqlDbType.NVarChar, 50).Value = "MyDatabaseName";
    conn.Open();
    cmd.ExecuteNonQuery(script);
}

However, that won't work in this case. Sql Server won't let you use a database name as a parameter. But I did want to bring this up anyway, because when I read your code sample, it looks like the Server type you use is seriously flawed.

The problem is that it's not clear from the sample that a way exists to safely send parameter information with a query string. Even if such a mechanism exists, it looks like the system is built in a way that encourages using finished sql strings, and is therefore built to encourage extremely unsafe practices. In short, it's just begging to get hacked. This is a real and serious problem. The code above demonstrates the correct way to use sql query parameters. If you don't have some mechanism in your database library that supports query parameters that are separate from your sql string, and that encourages their use over string concatenation, it's time to get a new database library.

That out of the way, what you can do for now is change your file like this:

USE [master]
DECLARE @DBName nvarchar(50) = '{0}';
EXEC('ALTER DATABASE ' + @DBName + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE');

You need to remove the GO lines, because "GO" is not part of the sql language. The feature is provided by Management Studio and other query tools, but Sql Server itself will not understand it, and so it will cause a syntax error on the server if you leave them in. Also notice what I used for the database name. When that's done, update your code like this:

string script = string.Format(File.ReadAllText(Path.Combine(path, "Procedure_fn.sql")), "MyDBName");
using (SqlConnection conn = new SqlConnection(txtConstring.Text))
{
    Server server = new Server(new ServerConnection(conn));
    server.ConnectionContext.ExecuteNonQuery(script);
}

But again, if what I'm seeing in your database library is exemplary of how it works, you need to rethink how you send data to your database. Sql Injection is a real and serious problem that will get your application hacked.

Upvotes: 2

Zeph
Zeph

Reputation: 1728

You'd need to modify the string 

script = script.Replace("@DBName nvarchar(50) = ' DataBaseName'", 
                        "@DBName nvarchar(50) = ' NEWDATABASENAME'" /* Put new db name here */);

Upvotes: 1

Related Questions