SQL Server: running queries
Query methods
-- name: AddOrder -> one
INSERT INTO dbo.orders (customer_id, placed_at, total, status)
OUTPUT INSERTED.id
VALUES (@customerId, @placedAt, @total, @status);
[SqlSourceGenerate]
public partial class OrderRepository(string connectionString);
// elsewhere
AddOrderDto order = await orders.AddOrder(
new AddOrderParams(customerId, placedAt, 1234.5m, "placed"));
A described query whose output is repository or methods gets a method that runs it through Microsoft.Data.SqlClient: see query methods. The project must reference Microsoft.Data.SqlClient 6.1 or later (SQLSRC273, SQLSRC267).
- A repository's connection source is a connection string, a method that returns a connection, or a connection that the repository borrows. Microsoft.Data.SqlClient has no data source. From a connection string, as above, each call makes a
SqlConnection, opens it and disposes it. - A
methodsmethod runs on theDbConnectionthat you give it, such as aSqlConnection. - A value that fits its type never changes how it is sent. Above,
placed_atis adatetime2(7),totaladecimal(12,2)andstatusannvarchar(20). So the method sends@placedAtas adatetime2, with all seven digits of its time,@totalas adecimal(12,2)and@statusas annvarchar(20). What happens to a value that does not fit is in how a parameter is sent.
How a parameter is sent
A query method sends each parameter as the SQL Server type it was described with, with that type's length, precision and scale. It never chooses a type from the value. So:
- a value keeps its digits;
- a null is a
NULLof the parameter's type, which a column of that type takes; - the server sees one declaration of the query, whatever the lengths of the values that fit their types, and keeps one plan of it.
How a method names a parameter and sends a null is on the Query methods page. For some types:
A string or a
byte[]longer than its type is sent whole, as avarchar(max), annvarchar(max)or avarbinary(max). The method never cuts it. The server refuses anINSERTor anUPDATEthat would cut it, with error 2628, "String or binary data would be truncated". The error is 8152 on SQL Server 2017, and in a database whose compatibility level is below 150 or whoseVERBOSE_TRUNCATION_WARNINGSis off. AWHEREthat compares a column with such a value finds no row that holds its start:abcdeffinds nochar(5)that holdsabcde.Spaces at the end of a string, and zero bytes at the end of a
byte[], are the exception. If a value is longer than its column only by them, the server cuts them off with no error and stores the rest. To compare two strings, the server pads the shorter one with spaces, soabcdefinds thechar(5)that holdsabcde.datetime2,timeanddatetimeoffsetare sent with seven digits of a second, whatever the scale of their type. The server rounds a value to the scale of the column that stores it:12:00:00.1235678written to adatetime2(3)column is12:00:00.124. AWHEREcompares a column with all seven digits of the value. So12:00:00.124finds adatetime2(3)that holds12:00:00.124, and12:00:00.1235678does not.A
decimalornumericwith more digits after the point than its type is rounded before it is sent. It is rounded to the scale of the type, half away from zero, as the server rounds it when it stores it.1.23456for adecimal(12,4)is sent as1.2346, which anINSERTstores and aWHEREfinds.A
decimalornumericwith more digits before the point than its type holds throwsArgumentException, "Parameter value '123456789.1000' is out of range.", and nothing is sent.jsonis sent as annvarchar(max), which the server checks and stores asjson. The server refuses a value that is not an object or an array: see JSON.vectoris sent as itsSqlVector<float>, whose length is its dimension. A null is sent as the nullvectorof the parameter's dimension.An alias type is sent as its base type: a parameter of
dbo.Email, madeFROM nvarchar(320), as annvarchar(320).An
xmlof an XML schema collection is sent asxml, which has no collection. Such a parameter is declared-- param: @p xml(DOCUMENT dbo.S). If the query needs the typing of the collection, as@p.value('/a', 'nvarchar(10)')of a document does, cast the parameter in the query:CAST(@p AS xml(DOCUMENT dbo.S)). Without the cast, SQL Server refuses the query when the tool describes it.A parameter that has a converter is sent as its database type too. An
Emailwhose converter returns astringis sent as thatnvarchar(320). A converted string longer than that is sent whole, as above.A parameter of one of System.Text.Json's types, or of a class of yours in a JSON column, is sent as its database type. The method converts it to the text of its JSON, as it converts a parameter that has a converter. It sends the text as an
nvarchar(max)for ajson, and as the column's own type, with its length, for a string column.A parameter of a NodaTime type is sent as its database type. The method converts it to the C# type of the table, as it converts a parameter that has a converter. A
NodaTime.LocalDateTimefor adatetimeis sent as adatetime, and aNodaTime.LocalTimeas atime. What the conversion does to a value that its column cannot hold is under NodaTime.A parameter of a NetTopologySuite type is sent as its
geographyorgeometry, with the SRID of the value. A null is sent as aNULLof that type.hierarchyid,geography,geometryand a CLR type of your own assembly are sent as their SQL Server type, when a type override gives them a C# type, such asMicrosoft.SqlServer.Types.SqlGeography. A null is sent as aNULLof that type. A CLR type is named by its schema:Moneyof the schemadbois sent asdbo.Money.A type of a newer SQL Server is sent by its value, and Microsoft.Data.SqlClient chooses its type from the value. Only a type override gives such a type a C# type.
A list parameter sends each of its values as a parameter of the values' type, as above.
Lists
-- name: ListOrdersByIds
SELECT id, placed_at FROM dbo.orders WHERE id IN @ids;
SQL Server has no arrays, so a query method sends a list of values as a list parameter. Above, ListOrdersByIdsParams.Ids is an IEnumerable<int>. Each of its values is sent as an int, the type of id, as any parameter of that type is. The test of a list for null, @ids IS NULL, is sent as a bit that has the name of the list.
A list after a
NOTcannot stand inside an aggregate, aGROUP BYor anOUTPUTclause, under the outputrepositoryormethods. SQL Server refuses there the form in which such a list is sent when it is empty or null. So a query such asSELECT COUNT(IIF(category NOT IN @categories, 1, NULL)) AS others FROM dbo.productsis SQLSRC127 at build. Test the list in aWITHquery, and aggregate its result, as the error's page shows.An aggregate that
OVERfollows is not one of these places.A list that no
NOTreaches runs empty in all of these places.A
NOTreaches only the condition that it stands in. So aNOTin aWHEREleaves a list in theHAVINGafter it alone.Inside an aggregate of your own, a list after a
NOTfails when the query runs, if the list is empty or null: see the limit.An empty list assumes that the connection leaves
ANSI_NULLSon, as Microsoft.Data.SqlClient does: see the limit.A query sends at most 2,098 parameters. SQL Server takes 2,100 parameters in one request, and Microsoft.Data.SqlClient uses two of them to send a query with parameters. What counts, and what a method does past it, is in the limit of parameters.
For more values than that, send them as one JSON array in an
nvarchar(max)parameter, and read it withOPENJSON. This needs a database whose compatibility level is 130 or later:-- name: ListOrdersByIdArray -- param: @ids nvarchar(max) SELECT id, placed_at FROM dbo.orders WHERE id IN (SELECT CAST(value AS int) FROM OPENJSON(@ids));var found = await orders.ListOrdersByIdArray(new ListOrdersByIdArrayParams(JsonSerializer.Serialize(ids)));The query sends one parameter whatever the count, and has one form, so SQL Server keeps one plan of it. Write the marker: SQL Server cannot tell the type of
@idsfromOPENJSON(@ids)alone.A table-valued parameter does the same through a table type of your database, but a query method cannot send one. Give such a query
-- output: sql, and send its parameter with your own code.
What a method reads and throws
- Give each
methodsmethod the transaction of a connection that has one. Without it, Microsoft.Data.SqlClient refuses the command withInvalidOperationExceptionbefore anything is sent, as the connection says. The message names the call that was refused:ExecuteReader,ExecuteScalarorExecuteNonQueryfrom a synchronous method, andBeginExecuteReaderorBeginExecuteNonQueryfrom an asynchronous one. So undermethod-type=both, the two methods of a query throw messages that differ in that word. - A connection that a repository borrows must have no transaction that your code began on it with
BeginTransaction. Otherwise Microsoft.Data.SqlClient refuses the command withInvalidOperationException, as a repository method takes no transaction: see transactions and timeouts. - A
methodsmethod refuses a transaction that has completed. If the transaction was committed or rolled back before the call, every method throwsInvalidOperationException, "The transaction given to the query 'ListOrders' has completed; it is no longer usable.", with the query's name. Nothing runs: the connection is not opened, and no command is sent. Without this, Microsoft.Data.SqlClient would run the command outside any transaction and commit it on its own. - A loop over streamed rows checks the transaction when the loop starts. So it throws when the loop asks for its first row, and it also refuses a transaction that completes between the call and the loop.
- A completed transaction of a library that wraps Microsoft.Data.SqlClient's can be missed. A method knows that a transaction has completed by its
Connection, which Microsoft.Data.SqlClient'sSqlTransactionsets to null. If a wrapper keeps itsConnectionset after it completes, the method does not refuse it, and the command runs outside any transaction. Give the method theSqlTransactioninside the wrapper.
Reading a row
- A column is read as the C# type of its property: see how a row is read. If a converter converts it, the method first reads the column as the type of the table, and an alias type as its base type.
- NodaTime's, NetTopologySuite's and System.Text.Json's types, and a class of yours in a JSON column, need nothing registered, on the connection or in the application. The method reads them itself, as NodaTime, NetTopologySuite and JSON say.
- A C# enum is read only from a column of its own underlying type. An enum over
int, the default, is read from anint, and an enum overbytefrom atinyint. An enum overintthrowsInvalidCastExceptionfor atinyint, asmallintor abigint, since Microsoft.Data.SqlClient never widens or narrows a value. Declare the enum over the type of its column:enum Level : bytefor atinyint.
Null values
- A null in a column whose property cannot be null throws before any converter is called. The method throws Microsoft.Data.SqlClient's
SqlNullValueException, and for avectorcolumn its ownInvalidCastException, "Column 'name' is null.".
Errors and rollbacks
- A query of several statements returns the rows of its first result set, as its model has them. Its other statements still run.
- Every method throws an error that a statement after the first result set raises, such as a
THROW, as the error'sSqlException. The statements before the error have run. In a transaction, if a later error of the batch then rolls the transaction back, the method throwsTransactionRolledBackExceptioninstead, as the bullets below say. - A method that reads rows throws the error in place of its rows. A method of the shape
one,one-optionalormanyreads the rest of the batch before it returns. - A loop over streamed rows throws the error after the last row.
- In a transaction, the error may have rolled the transaction back. This depends on the error and on
XACT_ABORT:- Under
SET XACT_ABORT ON, nearly every error rolls it back. - With
XACT_ABORToff,CAST('x' AS int)rolls it back. ATHROW, a divide by zero orCAST(300 AS tinyint)leaves it open.
- Under
rowcountadds up the rows that the statements insert, update or delete. ASELECTamong them adds nothing.- A loop over streamed rows that ends early throws the error of a row or a statement that it left unread. It throws it at the
break, or in place of the exception of the loop's body. In a transaction, that error may have rolled the transaction back, as for an error after the rows. See streamed rows, which also says how to keep the exception of the body. - The first exception wins over a later error of the batch. A method throws the first exception that it meets, and not an error that the rest of the batch raises after it. The first exception is one of these:
- an exception of the method's own, thrown before it has read the rest of its batch. It is
UnexpectedRowCountException, a null in a column whose property cannot be null, theInvalidCastExceptionof avectorcolumn, or a converter's exception; - an error that SQL Server raises for a row, such as a divide by zero, thrown as its
SqlException; - an error that SQL Server raises for a statement after the rows, such as
CAST(300 AS tinyint), thrown as itsSqlException.
- an exception of the method's own, thrown before it has read the rest of its batch. It is
- In a transaction that a later error rolled back, the method throws
TransactionRolledBackExceptionin place of the first exception. The rest of the batch still runs after the first exception. A later error there can roll the transaction back, such as aCAST('x' AS int)withXACT_ABORToff. TheInnerExceptionis the first exception, and not the later error. - A
catchofTransactionRolledBackExceptiontells you that your transaction has completed. ItsConnectionis null, andCommitthrowsInvalidOperationException, "This SqlTransaction has completed; it is no longer usable.". - An error that itself rolls the transaction back is thrown as its
SqlException, as the bullet on an error after the first result set says. Examples are aTHROWor a duplicate key underSET XACT_ABORT ON, and aCAST('x' AS int)for a row or after the rows. - The first exception is thrown as it is in these cases:
- without a transaction;
- with the transaction still open;
- for a transaction of a library that wraps Microsoft.Data.SqlClient's and keeps its
Connectionset, as the bullet on a completed transaction says.
- The same rules hold for a loop over streamed rows. They hold at a row and after its last row. They also hold for the error of a row or a statement that a
break, or an exception of the body, leaves unread. - An error that SQL Server raises before the rows begin is thrown as its
SqlException, even when the rest of the batch then rolls the transaction back: see the limit.
rowcount
rowcountis-1underSET NOCOUNT ON. A query that setsNOCOUNTreturns-1. If the query has no parameters, the setting also lasts for the session of the connection, so a laterrowcountmethod on the same open connection returns-1too. A query with parameters sets it for itself alone. See the limit.
Timeouts, cancelling and the connection
- A
commandTimeoutthat passes throwsSqlExceptionwithNumber-2, whose message starts "Execution Timeout Expired.". This is thecommandTimeoutof amethodsmethod. - A cancelled method throws what Microsoft.Data.SqlClient throws, unchanged. That is one of two exceptions:
TaskCanceledException, if the token is cancelled before the call, or once the server has begun to send the result;SqlException, withNumber0and a message that ends "Operation cancelled by user.", if the token is cancelled while the command runs, before that.
- A
SELECTusually begins its result before its first row. So a cancel while it waits for that row is aTaskCanceledException, and a cancel while anUPDATEwaits on a lock is aSqlException. - A
catch (OperationCanceledException)does not catch theSqlException. Check the token in acatch (SqlException)as well. - A method with parameters fails on a connection that wraps a
SqlConnectionand creates parameters of its own: see the limit.
Collection methods
-- name: AddOrder -> one
-- generator: collection-method
INSERT INTO dbo.orders (customer_id, placed_at, total, status)
OUTPUT INSERTED.id
VALUES (@customerId, @placedAt, @total, @status);
A query's collection method runs its items through Microsoft.Data.SqlClient. What a failed item leaves in the database depends on the transaction that the method runs in, and on XACT_ABORT:
| The transaction | When an item fails |
|---|---|
Its own, by transaction=on, the default, or an isolation level |
Every item is rolled back, those of earlier round trips too, and nothing is left |
None, by transaction=off |
SQL Server commits each item on its own. With XACT_ABORT off, an error such as a duplicate key ends its item's statement alone: the other items of its round trip, those after it included, and the round trips before it stay, and the round trips after it do not run. Under SET XACT_ABORT ON the error also stops its round trip, so the items after it do not run, and so does an error that ends the batch, such as a failed conversion, CAST('x' AS int), with XACT_ABORT off |
The caller's, a transaction given at the output methods or an ambient one |
The method leaves it to you. With XACT_ABORT off, a duplicate key ends its item's statement alone, and the transaction stays open, with every other item of its round trip and of those before it, for you to commit or roll back. An error that rolls the transaction back itself, nearly any under SET XACT_ABORT ON, or one that ends the batch, such as a failed conversion, CAST('x' AS int), leaves nothing, and the transaction has completed: its Connection is null |
A query whose statements return more than one result set cannot run over a collection at the shapes
one,one-optionalandmany, which read one result set for each item. The shapesnoneandrowcountread no rows, so their result sets do not matter.A statement that returns no result set is fine, such as
SET NOCOUNT ON, aDECLAREor anUPDATEwithoutOUTPUT.A second result set, or none for an item, throws
InvalidOperationException, and the method's own transaction is rolled back. This happens to a query of two result sets, and to one whoseSELECTstands in anIFand returns no result set for some items. The message is "The query 'FindOrder' returned a result set of another shape, or none, for one of the items from 0 to 1, so it cannot run over a collection: a query whose statements return more than one result set, or none for an item, cannot.". The items it names are those of the round trip up to where the mismatch showed, since the result set too many or missing can be any of theirs.A second result set with as many columns as the query's rows can fail in another way. The method throws the same exception at the end of the round trip. Or, before that, an item reads the result set as its own and throws
UnexpectedRowCountExceptionor Microsoft.Data.SqlClient'sInvalidCastException. Either way, the method never returns its rows.Given a transaction, a method that reads rows can throw
TransactionRolledBackException, as a query method does above. It throws it when both of these are true:- it threw an exception of its own, or reached an item's error that left the transaction open;
- a later error of the round trip then rolled the transaction back.
Its
InnerExceptionis the method's own exception, such asUnexpectedRowCountException, or theCollectionItemExceptionof the item's error.An error that rolled the transaction back itself is thrown as its
CollectionItemException.A transaction that has completed is refused before anything runs, as for a query method: see transactions.
rowcountadds 0 for an item underSET NOCOUNT ON, where a query method returns-1.The isolation level of the method's own transaction lasts for that transaction alone, as transactions says. It is the level that
transactionnames, or read committed underon. SQL Server keeps a level on the connection after a transaction ends, and the pool keeps it on a connection that is closed and opened again. So the method sets the connection back to its level before the call, whether the method returns or throws, and before it closes a connection that it opened. The pooled connection opened next runs at the level it would have run at without the call.transaction=snapshotneeds the database to allow it:ALTER DATABASE app SET ALLOW_SNAPSHOT_ISOLATION ON. Without it, SQL Server refuses the first item of the transaction with its error 3952.SQL Server has all five isolation levels:
read-uncommitted,read-committed, which is its default,repeatable-read,serializableandsnapshot.commandTimeoutis that of each round trip, and a cancelled method throws as a query method does: see timeouts and cancelling. It often throwsTaskCanceledExceptionwhen an item waits for the rows it returns, as on a lock. Either way, the method's own transaction is rolled back.
Limits
An empty list after IN matches the rows whose value is null
What you see. An empty or a null list after IN matches the rows whose value is null, where it should match none. A null value in a list matches them too.
Why. The connection turns ANSI_NULLS off. With ANSI_NULLS off, SQL Server takes = NULL as true for a null.
What to do. Leave ANSI_NULLS on, as Microsoft.Data.SqlClient turns it on when it connects. Run no SET ANSI_NULLS OFF on a connection that runs your queries.
A SqlException, and the transaction is rolled back
What you see. A method throws the SqlException of an error raised before the query's first rows, and the transaction that you gave it has been rolled back. If your code catches the SqlException and goes on, the transaction's Connection is null:
- A generated method that you give the transaction refuses it with
InvalidOperationException. - A command of your own, or one that Dapper runs, given the transaction runs outside any transaction, and is committed on its own.
CommitthrowsInvalidOperationException, "This SqlTransaction has completed; it is no longer usable.".
Why. An error before the first rows does not stop the batch, and the rest of it still runs. Examples are a divide by zero in a DECLARE, and a failed UPDATE before the SELECT. With XACT_ABORT off, the first error alone leaves the transaction open, but a later error of the batch can roll it back, such as a later CAST('x' AS int). The SqlException may not list that later error in its Errors. So the method cannot tell this from an error that rolled the transaction back itself, and it throws the SqlException as it is.
What to do. After you catch a SqlException, check the transaction, and treat a Connection that is null as a rollback:
try
{
var orders = await connection.ListOrders(new ListOrdersArgs(customerId), transaction: transaction, cancellationToken: token);
}
catch (SqlException exception) when (transaction.Connection is null)
{
throw new InvalidOperationException("The transaction was rolled back by an error the query raised.", exception);
}
A rowcount method returns -1 after SET NOCOUNT ON
What you see. On SQL Server, a rowcount method returns -1 once SET NOCOUNT ON has run on the session of the connection.
Why. A query without parameters, or a command of your own without them, that runs SET NOCOUNT ON sets it for the session, not for the statement. On a connection that you opened, every later rowcount method returns -1, until the connection is closed and its session reset. A query with parameters, which Microsoft.Data.SqlClient runs through sp_executesql, sets it for itself alone, and only its own count is -1.
What to do. Do one of these:
- Run
SET NOCOUNT OFFafter it. - Do not set it in a query without parameters on a connection that runs a
rowcountmethod.
SqlException 130 for an empty or null list after NOT inside an aggregate of your own
What you see. A query such as SELECT dbo.Median(IIF(status NOT IN @statuses, balance, NULL)) AS median FROM dbo.accounts throws SqlException when the list is empty or null: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." (130). It runs for every list that holds a value, and the build and describing succeed.
Why. SQL Server refuses, inside an aggregate, the form in which an empty or null list after a NOT is sent. A list after a NOT inside an aggregate of your own fails when the query runs. So does such a list where a token writes the aggregate's name, or the GROUP BY or OUTPUT that the list stands in. Inside a built-in aggregate, a GROUP BY or an OUTPUT clause written in the query, such a list is SQLSRC127 at build: see lists. A list that no NOT reaches runs empty in every one of these places.
What to do. Test the list in a WITH query, or a subquery in FROM, and aggregate its result. This runs for an empty list too:
-- name: CountAccountsWithoutStatuses
WITH tested AS (
SELECT CASE WHEN status NOT IN @statuses THEN 1 ELSE 0 END AS hit
FROM dbo.accounts
)
SELECT SUM(hit) AS outside
FROM tested;