4

I have a script (*.sql) which creates tables. I am using Visual studio 2010 with npgsql to access postgres database.

Could I execute a script from codebehind?

This is the code I have tried:

string sqlConnectionString = @"myconnection";

FileInfo file = new FileInfo(@"myfile.sql");

string script = file.OpenText().ReadToEnd();

NpgsqlConnection conn = new NpgsqlConnection(sqlConnectionString);

Server server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);
file.OpenText().Close();

But But Visual studio dont recognize Server.

Christoph Fink
  • 22,727
  • 9
  • 68
  • 113
Za7pi
  • 1,338
  • 7
  • 22
  • 33
  • you could save it as a stored procedure in your db and call it from code behind – Banana May 23 '14 at 07:15
  • No, because I am going to execute in many different databases. I prefer to execute it from the application – Za7pi May 23 '14 at 07:19
  • you could save your query in a text file and load it as string an execute in your db's – Banana May 23 '14 at 07:21
  • I have it in a text file, I have tried to translate this: http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp to npgsql connector but no luck... – Za7pi May 23 '14 at 07:24
  • could you post the full code that you have tried? mask the ip's and other personal info, i just want to see the logic if possible please. also try executing a simple insert query just to rule out the possibility that your script fails – Banana May 23 '14 at 07:33
  • I have posted in my first post. Thanks! – Za7pi May 23 '14 at 08:11
  • put a breakpoint, which object fails to initialize first? `server`, `new ServerConnection(conn)`, `conn` or `script` ? – Banana May 23 '14 at 08:20
  • I got the answer. Look at the asnwer at the bottom. Thank you very much!! – Za7pi May 23 '14 at 08:29
  • In future, please try to always include any relevant code, *exact text* of error messages, your versions, etc. That said, +1 for useful self-answer. – Craig Ringer Jul 23 '14 at 15:15

1 Answers1

10

I got it. Here the answer:

NpgsqlConnection _connPg = new NpgsqlConnection("yourconnectionstring"));

FileInfo file = new FileInfo(HttpContext.Current.Server.MapPath("DatabaseSchema.sql"));
string script = file.OpenText().ReadToEnd();
var m_createdb_cmd = new NpgsqlCommand(script, _connPg);
_connPg.Open();
m_createdb_cmd.ExecuteNonQuery();
_connPg.Close();
Christoph Fink
  • 22,727
  • 9
  • 68
  • 113
Za7pi
  • 1,338
  • 7
  • 22
  • 33