Published 4/30/2007 1:45:01 AM  in  Postgresql

Parameterized queries are recommended to use instead of dynamically building sql statements based on user input - the things to remember is that parameters in Postgresql are prefixed with a ':'

        public DataTable GetData()
        {


            Npgsql.NpgsqlConnection oConn = new Npgsql.NpgsqlConnection("Server=192.168.10.4;Port=5432;Userid=postgres;Password=stefan;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable;Database=test");
            oConn.Open();

            DataSet oDataSet = new System.Data.DataSet("tab1");

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand("select * from testtable where id = :col1", oConn);
            // Now add the parameter to the parameter collection of the command specifying its type.
            command.Parameters.Add(new Npgsql.NpgsqlParameter("col1", DbType.Int32));

            // Now, add a value to it and later execute the command as usual.
            command.Parameters[0].Value = 1;

            Npgsql.NpgsqlDataAdapter oAdapter = new Npgsql.NpgsqlDataAdapter(command);
            oAdapter.Fill(oDataSet, "tab1");
            DataTable dt = oDataSet.Tables["tab1"];
            oConn.Close();

            return dt;


        }