# Using sql query with parameters in dotnet client

**URL:** <https://discuss.elastic.co/t/using-sql-query-with-parameters-in-dotnet-client/338553>\
**Category:** Elasticsearch\
**Tags:** language-clients\
**Created:** [July 17, 2023, 12:32pm UTC](https://discuss.elastic.co/t/using-sql-query-with-parameters-in-dotnet-client/338553 "2023-07-17T12:32:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![darooman](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@darooman](https://discuss.elastic.co/u/darooman)\
**Post date:** [July 17, 2023, 12:32pm UTC](https://discuss.elastic.co/t/using-sql-query-with-parameters-in-dotnet-client/338553/1 "2023-07-17T12:32:22Z")

</div>

When using the .net nuget package Elastic.Clients.Elasticsearch (version 8.1.3) to connect to an elastic cloud instance (running elastic v8.7.1), I am trying to use the sql query but I am struggling with the Params property on the QueryRequest. This property is a IDictionary\<string, object\> but if it is set, then Elasticsearch always seems to return an error.

I have found documentation on using sql syntax with the nuget package hard to find. The documentation here [Introduction | Elasticsearch .NET Client [8.1] | Elastic](https://www.elastic.co/guide/en/elasticsearch/client/net-api/current/introduction.html) doesn't mention the sql namespace at all.

The following code demonstrates the issue:

```auto
using Elastic.Clients.Elasticsearch;
using Elastic.Clients.Elasticsearch.Sql;
using Elastic.Transport;

const string CloudId = "MyCloudId";
const string ApiKey = "MyApiKey";

var client = new ElasticsearchClient(CloudId, new ApiKey(ApiKey));

// create simple sql query needing no parameters
var request1 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors GROUP BY machineName, username,message ORDER BY errorCount DESC",
    FetchSize = 100,
};

// this request works fine
var response1 = client.Sql.Query(request1);
Console.WriteLine(response1.DebugInformation);

// attempt to create a dictionary of parameters - no idea what the key should be as the rest client just wants an array
var parameters = new Dictionary<string, object>
{
    { "0", DateTime.Now.Date },
};

var request2 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors WHERE dateAdded=? GROUP BY machineName, username,message ORDER BY errorCount DESC",
    Params = parameters,
    FetchSize = 100,
};

/* this request fails with:
Invalid Elasticsearch response built from a unsuccessful (400) low level call on POST: /_sql
 Exception: Request failed to execute. Call: Status code 400 from: POST /_sql. ServerError: Type: x_content_parse_exception Reason: "[1:207] [sql/query] params doesn't support values of type: START_OBJECT"
*/
var response2 = client.Sql.Query(request2);
Console.WriteLine(response2.DebugInformation);

// create request requiring no parameters but provide an empty dictionary
var request3 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors GROUP BY machineName, username,message ORDER BY errorCount DESC",
    FetchSize = 100,
    Params = new Dictionary<string, object>(),
};

// this request also fails with an empty dictionary
var response3 = client.Sql.Query(request3);
Console.WriteLine(response3.DebugInformation);

```

If I use postman, I can get the query to work with the \_sql endpoint:

```auto
{ 
    "fetch_size": 100, 
    "query": "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors WHERE dateAdded=? GROUP BY machineName, username,message ORDER BY errorCount DESC", 
    "params": ["2023-07-14"] 
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 14, 2023, 12:32pm UTC](https://discuss.elastic.co/t/using-sql-query-with-parameters-in-dotnet-client/338553/2 "2023-08-14T12:32:36Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.