c# - set value to sql file -

this sql file :

  use [master] go declare @dbname nvarchar(50) = ' databasename' exec('alter database ' + @dbname + ' set read_only rollback immediate') go 

and run 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 set value of local variable "databasename" in c#

normally i'd recommend dropping variable declaration , other accoutrements file completely, *.sql file becomes simply:

exec('alter database ' + @dbname + ' set read_only rollback immediate')

and i'd tell call 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, won't work in case. sql server won't let use database name parameter. did want bring anyway, because when read code sample, it looks server type use flawed.

the problem it's not clear sample way exists safely send parameter information query string. if such mechanism exists, looks system built in way encourages using finished sql strings, , therefore built encourage extremely unsafe practices. in short, it's begging hacked. real , serious problem. code above demonstrates correct way use sql query parameters. if don't have mechanism in database library supports query parameters separate sql string, and encourages use on string concatenation, it's time new database library.

---

that out of way, can change file this:

use [master] declare @dbname nvarchar(50) = '{0}'; exec('alter database ' + @dbname + ' set read_only rollback immediate'); 

you need remove go lines, because "go" not part of sql language. feature provided management studio , other query tools, sql server will not understand it, , cause syntax error on server if leave them in. notice used database name. when that's done, update code 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 i'm seeing in database library exemplary of how works, need rethink how send data database. sql injection real , serious problem will application hacked.