14602424605

Committed 22 Apr 2025 06:53PM UTC coverage: 89.849% (+0.02%) from 89.832%

Build # 14602424605

Build Type

Pull #258

github

Committed by

web-flow

Commit Message

Merge 44bb2141c into 35e92d966

Pull Request Pull Request #258: Cache prepared statements

Run Details

1114 of 1277 branches covered (87.24%)

Branch coverage included in aggregate %.

91 of 95 new or added lines in 3 files covered. (95.79%)

1 existing line in 1 file now uncovered.

2152 of 2358 relevant lines covered (91.26%)

749413.62 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

92.97

/DuckDB.NET.Data/DuckDBCommand.cs

﻿using DuckDB.NET.Native;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using System.Runtime.CompilerServices;

namespace DuckDB.NET.Data;

public class DuckDBCommand : DbCommand
{
    private DuckDBConnection? connection;
    private readonly DuckDBParameterCollection parameters = new();
    private bool prepared;
    private readonly List<PreparedStatement.PreparedStatement> preparedStatements = new();

    protected override DbTransaction? DbTransaction { get; set; }
    protected override DbParameterCollection DbParameterCollection => parameters;

    public new virtual DuckDBParameterCollection Parameters => parameters;

    public override int CommandTimeout { get; set; }
    public override CommandType CommandType { get; set; }
    public override bool DesignTimeVisible { get; set; }
    public override UpdateRowSource UpdatedRowSource { get; set; }

    /// <summary>
    /// A flag to determine whether to use streaming mode or not when executing a query. Defaults to false.
    /// In streaming mode DuckDB will use less RAM but query execution might be slower. Applies only to queries that return a result-set.
    /// </summary>
    /// <remarks>
    /// Streaming mode uses `duckdb_execute_prepared_streaming` and `duckdb_stream_fetch_chunk`, non-streaming (materialized) mode uses `duckdb_execute_prepared` and `duckdb_result_get_chunk`.
    /// </remarks>
    public bool UseStreamingMode { get; set; } = false;

    internal DuckDBDataReader? DataReader { get; set; }

    private string commandText = string.Empty;

#if NET6_0_OR_GREATER
    [AllowNull]
#endif
    [DefaultValue("")]
    public override string CommandText
    {
        get => commandText;
        set
        {
            if (DataReader != null)
                throw new InvalidOperationException("no open reader must exist");

            if (commandText == value)
                return;

            DisposePreparedStatements();
            commandText = value ?? string.Empty;
        }
    }

    protected override DbConnection? DbConnection
    {
        get => connection;
        set => connection = (DuckDBConnection?)value;
    }

    public DuckDBCommand()
    { }

    public DuckDBCommand(string commandText)
    {
        CommandText = commandText;
    }

    public DuckDBCommand(string commandText, DuckDBConnection connection)
        : this(commandText)
    {
        Connection = connection;
    }

    public override void Cancel()
    {
        if (connection != null)
        {
            connection.NativeConnection.Interrupt();
        }
    }

    public override int ExecuteNonQuery()
    {
        EnsureConnectionOpen();

        var count = 0;

        foreach (var statement in GetStatements())
        {
            var current = statement.Execute();
            count += (int)NativeMethods.Query.DuckDBRowsChanged(ref current);
            current.Dispose();
        }

        return count;
    }

    public override object? ExecuteScalar()
    {
        EnsureConnectionOpen();

        using var reader = ExecuteReader();
        return reader.Read() ? reader.GetValue(0) : null;
    }

    public new DuckDBDataReader ExecuteReader()
    {
        return (DuckDBDataReader)base.ExecuteReader();
    }

    public new DuckDBDataReader ExecuteReader(CommandBehavior behavior)
    {
        return (DuckDBDataReader)base.ExecuteReader(behavior);
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            DataReader?.Dispose();
        }

        DisposePreparedStatements();

        base.Dispose(disposing);
    }

    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
        if (DataReader != null)
            throw new InvalidOperationException("no open reader must exist");

        EnsureConnectionOpen();

        var closeConnection = behavior.HasFlag(CommandBehavior.CloseConnection);

        return new DuckDBDataReader(this, GetStatements(), closeConnection);
    }

    public override void Prepare() { }

    protected override DbParameter CreateDbParameter() => new DuckDBParameter();

    internal void CloseConnection() => Connection!.Close();

    private void DisposePreparedStatements()
    {
        foreach (var statement in preparedStatements)
        {
            statement.Dispose();
        }

        preparedStatements.Clear();
        prepared = false;
    }

    private void EnsureConnectionOpen([CallerMemberName] string operation = "")
    {
        if (Connection is null || Connection.State != ConnectionState.Open)
        {
            throw new InvalidOperationException($"{operation} requires an open connection");
        }
    }

    private IEnumerable<PreparedStatement.PreparedStatement> GetStatements()
    {
        foreach (var statement in prepared
                     ? preparedStatements
                     : PrepareAndEnumerateStatements())
        {
            statement.BindParameters(Parameters);
            statement.UseStreamingMode = UseStreamingMode;
            yield return statement;
        }
    }

    private IEnumerable<PreparedStatement.PreparedStatement> PrepareAndEnumerateStatements()
    {
        DisposePreparedStatements();

        using var unmanagedQuery = CommandText.ToUnmanagedString();

        var statementCount = NativeMethods.ExtractStatements.DuckDBExtractStatements(connection!.NativeConnection, unmanagedQuery, out var extractedStatements);

        using (extractedStatements)
        {
            if (statementCount <= 0)
            {
                var error = NativeMethods.ExtractStatements.DuckDBExtractStatementsError(extractedStatements);
                throw new DuckDBException(error.ToManagedString(false));
            }

            for (int index = 0; index < statementCount; index++)
            {
                var status = NativeMethods.ExtractStatements.DuckDBPrepareExtractedStatement(connection!.NativeConnection, extractedStatements, index, out var unmanagedStatement);

                if (status.IsSuccess())
                {
                    var statement = new PreparedStatement.PreparedStatement(unmanagedStatement);
                    preparedStatements.Add(statement);
                    yield return statement;
                }
                else
                {
                    var errorMessage = NativeMethods.PreparedStatements.DuckDBPrepareError(unmanagedStatement).ToManagedString(false);

                    throw new DuckDBException(string.IsNullOrEmpty(errorMessage) ? "DuckDBQuery failed" : errorMessage);
                }
            }
        }

        prepared = true;
    }
}

1	using DuckDB.NET.Native;
2	using System;
3	using System.Collections.Generic;
4	using System.ComponentModel;
5	using System.Data;
6	using System.Data.Common;
7	using System.Diagnostics.CodeAnalysis;
8	using System.Runtime.CompilerServices;
9
10	namespace DuckDB.NET.Data;
11
12	public class DuckDBCommand : DbCommand
13	{
14	private DuckDBConnection? connection;
15	private readonly DuckDBParameterCollection parameters = new();	80,541✔
16	private bool prepared;
17	private readonly List<PreparedStatement.PreparedStatement> preparedStatements = new();	80,541✔
18
19	protected override DbTransaction? DbTransaction { get; set; }	80,532✔
20	protected override DbParameterCollection DbParameterCollection => parameters;	378✔
21
22	public new virtual DuckDBParameterCollection Parameters => parameters;	158,613✔
23
24	public override int CommandTimeout { get; set; }	×
25	public override CommandType CommandType { get; set; }	60,429✔
26	public override bool DesignTimeVisible { get; set; }	×
27	public override UpdateRowSource UpdatedRowSource { get; set; }	×
28
29	/// <summary>
30	/// A flag to determine whether to use streaming mode or not when executing a query. Defaults to false.
31	/// In streaming mode DuckDB will use less RAM but query execution might be slower. Applies only to queries that return a result-set.
32	/// </summary>
33	/// <remarks>
34	/// Streaming mode uses `duckdb_execute_prepared_streaming` and `duckdb_stream_fetch_chunk`, non-streaming (materialized) mode uses `duckdb_execute_prepared` and `duckdb_result_get_chunk`.
35	/// </remarks>
36	public bool UseStreamingMode { get; set; } = false;	157,617✔
37
38	internal DuckDBDataReader? DataReader { get; set; }	336,834✔
39
40	private string commandText = string.Empty;	80,541✔
41
42	#if NET6_0_OR_GREATER
43	[AllowNull]
44	#endif
45	[DefaultValue("")]
46	public override string CommandText
47	{
48	get => commandText;	138,408✔
49	set
50	{
51	if (DataReader != null)	138,414!
NEW 52	throw new InvalidOperationException("no open reader must exist");	×
53
54	if (commandText == value)	138,414✔
55	return;	12✔
56
57	DisposePreparedStatements();	138,402✔
58	commandText = value ?? string.Empty;	138,402!
59	}	138,402✔
60	}
61
62	protected override DbConnection? DbConnection
63	{
64	get => connection;	353,682✔
65	set => connection = (DuckDBConnection?)value;	80,538✔
66	}
67
68	public DuckDBCommand()	80,535✔
69	{ }	80,535✔
70
71	public DuckDBCommand(string commandText)	6✔
72	{
73	CommandText = commandText;	6✔
74	}	6✔
75
76	public DuckDBCommand(string commandText, DuckDBConnection connection)
77	: this(commandText)	3✔
78	{
79	Connection = connection;	3✔
80	}	3✔
81
82	public override void Cancel()
83	{
84	if (connection != null)	12✔
85	{
86	connection.NativeConnection.Interrupt();	12✔
87	}
88	}	12✔
89
90	public override int ExecuteNonQuery()
91	{
92	EnsureConnectionOpen();	118,197✔
93
94	var count = 0;	118,197✔
95
96	foreach (var statement in GetStatements())	472,920✔
97	{
98	var current = statement.Execute();	118,275✔
99	count += (int)NativeMethods.Query.DuckDBRowsChanged(ref current);	118,251✔
100	current.Dispose();	118,251✔
101	}
102
103	return count;	118,155✔
104	}
105
106	public override object? ExecuteScalar()
107	{
108	EnsureConnectionOpen();	19,323✔
109
110	using var reader = ExecuteReader();	19,320✔
111	return reader.Read() ? reader.GetValue(0) : null;	19,314✔
112	}	19,314✔
113
114	public new DuckDBDataReader ExecuteReader()
115	{
116	return (DuckDBDataReader)base.ExecuteReader();	39,153✔
117	}
118
119	public new DuckDBDataReader ExecuteReader(CommandBehavior behavior)
120	{
121	return (DuckDBDataReader)base.ExecuteReader(behavior);	9✔
122	}
123
124	protected override void Dispose(bool disposing)
125	{
126	if (disposing)	80,532✔
127	{
128	DataReader?.Dispose();	80,505✔
129	}
130
131	DisposePreparedStatements();	80,532✔
132
133	base.Dispose(disposing);	80,532✔
134	}	80,532✔
135
136	protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
137	{
138	if (DataReader != null)	39,315!
NEW 139	throw new InvalidOperationException("no open reader must exist");	×
140
141	EnsureConnectionOpen();	39,315✔
142
143	var closeConnection = behavior.HasFlag(CommandBehavior.CloseConnection);	39,315✔
144
145	return new DuckDBDataReader(this, GetStatements(), closeConnection);	39,315✔
146	}
147
148	public override void Prepare() { }	3✔
149
150	protected override DbParameter CreateDbParameter() => new DuckDBParameter();	69✔
151
152	internal void CloseConnection() => Connection!.Close();	9✔
153
154	private void DisposePreparedStatements()
155	{
156	foreach (var statement in preparedStatements)	991,652✔
157	{
158	statement.Dispose();	138,493✔
159	}
160
161	preparedStatements.Clear();	357,333✔
162	prepared = false;	357,333✔
163	}	357,333✔
164
165	private void EnsureConnectionOpen([CallerMemberName] string operation = "")
166	{
167	if (Connection is null \|\| Connection.State != ConnectionState.Open)	176,835✔
168	{
169	throw new InvalidOperationException($"{operation} requires an open connection");	3✔
170	}
171	}	176,832✔
172
173	private IEnumerable<PreparedStatement.PreparedStatement> GetStatements()
174	{
175	foreach (var statement in prepared	630,210✔
176	? preparedStatements	157,512✔
177	: PrepareAndEnumerateStatements())	157,512✔
178	{
179	statement.BindParameters(Parameters);	157,614✔
180	statement.UseStreamingMode = UseStreamingMode;	157,596✔
181	yield return statement;	157,596✔
182	}
183	}	157,461✔
184
185	private IEnumerable<PreparedStatement.PreparedStatement> PrepareAndEnumerateStatements()
186	{
187	DisposePreparedStatements();	138,399✔
188
189	using var unmanagedQuery = CommandText.ToUnmanagedString();	138,399✔
190
191	var statementCount = NativeMethods.ExtractStatements.DuckDBExtractStatements(connection!.NativeConnection, unmanagedQuery, out var extractedStatements);	138,399✔
192
193	using (extractedStatements)	138,399✔
194	{
195	if (statementCount <= 0)	138,399✔
196	{
197	var error = NativeMethods.ExtractStatements.DuckDBExtractStatementsError(extractedStatements);	3✔
198	throw new DuckDBException(error.ToManagedString(false));	3✔
199	}
200
201	for (int index = 0; index < statementCount; index++)	553,710✔
202	{
203	var status = NativeMethods.ExtractStatements.DuckDBPrepareExtractedStatement(connection!.NativeConnection, extractedStatements, index, out var unmanagedStatement);	138,507✔
204
205	if (status.IsSuccess())	138,507✔
206	{
207	var statement = new PreparedStatement.PreparedStatement(unmanagedStatement);	138,501✔
208	preparedStatements.Add(statement);	138,501✔
209	yield return statement;	138,501✔
210	}
211	else
212	{
213	var errorMessage = NativeMethods.PreparedStatements.DuckDBPrepareError(unmanagedStatement).ToManagedString(false);	6✔
214
215	throw new DuckDBException(string.IsNullOrEmpty(errorMessage) ? "DuckDBQuery failed" : errorMessage);	6!
216	}
217	}
218	}	138,348✔
219
220	prepared = true;	138,348✔
221	}	138,348✔
222	}

Giorgi / DuckDB.NET / 14602424605

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous