SQL Query against Database instance in .NET

beerygaz

I'd like to execute a simple SQL query using the Datatables libraries in .NET (without returning an Editor). Much like this example in PHP https://datatables.net/forums/discussion/31952/raw-sql-query-for-editor

Does anyone have a simple example to get me started please? I'm really not sure how to go about constructing and executing my query.

rf1234

You will basically only need this if you want to use the Editor database handler. If you don't need to use it there is no need for this because you can use your own. In some instances it might be advisable to use Editor's database handler because you might run into troubles with concurring updates using Editor and your own db handler at the same time.

Here are some examples for using Editor's raw method (sorry this is PHP but it should work very similarly in .NET). Just take a look at the docs. As you can see I commented out the "raw" database insert because Editor provides a much simpler insert method that also takes care of binding.

$statement = ('SELECT number FROM report_type  
                    WHERE user_id = :user_id');  
    $result =
    $db ->raw()
        ->bind(':user_id', $userId)
        ->exec($statement);

    $row = $result->fetchAll(PDO::FETCH_ASSOC);

    foreach ($row as $key => $values) {
        unset($reportTypeOptions[$values["number"]]);
    }
    foreach ($reportTypeOptions as $key => $val) {
//        $db->raw()
//           ->bind( ':user_id',  $_SESSION['id'] )
//           ->bind( ':number',   $key )
//           ->bind( ':label',    $val )     
//           ->exec( 'INSERT INTO report_type
//                    (user_id, number, label)  
//                       VALUES
//                    (:user_id, :number, :label)' );
        $result = $db->insert( 'report_type', array (
            'user_id'     => $_SESSION['id'],
            'number'      => $key,
            'label'       => $val
        ), array ( 'id' ) );
    }

Here is the link to the .NET docs:
https://editor.datatables.net/docs/1.7.3/net/html/a23fa4f7-6e80-19f5-e5b0-49e0e7a50c88.htm

allan

The Database.Sql method is the one you want here:

db.Sql( "SELECT ... " );

Allan