How use LINQ in the server, and build tables from BINARY?

Data Abstract
1

I want to build some methods for my service on top of the server DA schema. I run custom functions in the postgres database, and need to build dinamic tables & run LINQ queries against them. I want to make my clients very dumb.

However, I’m lost in how do that.

Let’s say I have a function called in postgres like this:

SELECT login_user(‘user’,‘pwd’);

and it return a table.

I can call

Binary data = this.srv.SQLGetData(sql, true, -1);

But how transform it as a DataTable?

And how execute LINQ queries?

P.D. If I use SQLGetData is necessary to turn ON AllowExecuteSQL. Is possible do direct calls to the DB without this?

2

Hello.

You can define custom function as DataTable in Schema Modeler. Then define custom method (for example, with the name GetSPData) for DataService in Service Builder. This method should allow to get data from custom function and send it as Binary. Please take a look at the implementation of this method:

    public Binary GetSPData()
    {
        this.CheckConnection();
        Binary result = new Binary();
        using (IDbCommand lCommand = this.Connection.NewCommand("custom_function", CommandType.StoredProcedure, String.Empty))
        {
            try
            {
                dataStreamer.InitializeStreamer(result, StreamerInitialization.Write);
                SchemaDataTable tableSchema;
                string tableName = "custom_function";

                tableSchema = ServiceSchema.DataTables[tableName];

                using (IDataReader lReader = lCommand.ExecuteReader())
                {
                    int maxRecords = -1;
                    ServiceDataStreamer.WriteDataReader(lReader, tableSchema, maxRecords, true);
                }
            }
            finally
            {
                ServiceDataStreamer.FinalizeStreamer();
            }
        }
        return result;
    }

On the client side you will need to recreate TableDefinitions file by using option “Create DA LINQ Classes” of LinqRemoteDataAdapter. Then change DataRequestCall.MethodName property of LinqRemoteDataAdapter to “GetSPData” and use the next code to fill the data:

        //this.remoteDataAdapter.DataRequestCall.MethodName = "GetSPData";
        var q = from p in fDataModule.DataAdapter.GetTable<custom_function>(parameters)
                         select p;

        dataGridView1.DataSource = q.ToList();

Hope this helps.

3

Hi again, so this mean that I can create on the fly from this.srv.SQLGetData(sql, true, -1); a datatable, and need to recreate each returned table from the functions?

And what happen if the function return a simple type (text, int, etc)?