Published 4/30/2007 3:38:04 AM  in  Postgresql

Using the database and stored procedure described here we can write our code like this:

        public DataTable GetDataSP()
        {


            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=pagila");
            oConn.Open();

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

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand("getcustomersbystore(:col1)", oConn);
            command.CommandType = CommandType.StoredProcedure;
            // 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;


        }

This make calling a stored procedure in Postgres look pretty much like it does in say SQL Server. I like that!