Execute postgres script with npgsql

Question

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.

you could save it as a stored procedure in your db and call it from code behind — Banana
– Banana, Commented May 23, 2014 at 7:15
No, because I am going to execute in many different databases. I prefer to execute it from the application — Za7pi
– Za7pi, Commented May 23, 2014 at 7:19
you could save your query in a text file and load it as string an execute in your db's — Banana
– Banana, Commented May 23, 2014 at 7:21
I have it in a text file, I have tried to translate this: stackoverflow.com/questions/650098/… to npgsql connector but no luck... — Za7pi
– Za7pi, Commented May 23, 2014 at 7: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
– Banana, Commented May 23, 2014 at 7:33

Christoph Fink · Accepted Answer · 2014-05-23 08:51:36Z

11

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();

edited May 23, 2014 at 8:51

Christoph Fink

23.2k9 gold badges72 silver badges118 bronze badges

answered May 23, 2014 at 8:28

Za7pi

1,4289 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anis Alibegić Over a year ago

Remove extra closing bracket at new NpgsqlConnection("yourconnectionstring")).

Collectives™ on Stack Overflow

Execute postgres script with npgsql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related