« SQL et CSharp » : différence entre les versions

De Banane Atomic
Aller à la navigationAller à la recherche
Aucun résumé des modifications
 
(25 versions intermédiaires par le même utilisateur non affichées)
Ligne 1 : Ligne 1 :
[[Category:CSharp]]
[[Category:CSharp]]
[[Category:SQL]]
[[Category:SQL]]
= Base =
= Connection string =
{{info | Nuget package: {{boxx|Microsoft.Data.SqlClient}}}}
{{info | You can get the connection string from Visual Studio → View → SQL Server Object Explorer → Select a server → Properties → Connection string}}
{| class="wikitable wtp wtmono1 wtmono3"
! Key || Description || Value
|-
| Data Source<br>Server || nom ou adresse du serveur || (localdb)\MSSQLLocalDB<br>
tcp:my-server.database.windows.net,1433
|-
| Initial Catalog<br>Database || nom de la bdd ||
|-
| Integrated Security<br>Trusted_Connection || || True, False
|-
| User ID || || Domain\Username
|-
| Password || mot de passe de l'utilisateur ||
|-
| Application Name || se retrouve dans la colonne {{boxx|program_name}}<br>de la table {{boxx|sys.dm_exec_sessions}} ||
|}
 
== Web.config ==
<filebox fn='Web.config' lang=xml>
<?xml version="1.0" encoding="utf-8"?>
 
<configuration>
    <connectionStrings>
        <add name="MyConnectionString"
            connectionString="Server=MyServer;
                              Database=MyDatabase;
                              Trusted_Connection=True;
                              MultipleActiveResultSets=true;
                              Connection Timeout=3"
            providerName="System.Data.SqlClient" />
</filebox>
 
<kode lang=csharp>
<kode lang=csharp>
var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
</kode>


var cmd = "SELECT COUNT(*) from table where x IS NULL AND y <> 'NULL';";
== SqlConnectionStringBuilder ==
<kode lang=csharp>
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
connectionStringBuilder.ConnectionString = connectionString;
 
string serverName = connectionStringBuilder.DataSource;
string databaseName = connectionStringBuilder.InitialCatalog;
</kode>
 
= Select query =
== [https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader DataReader] ==
Faster because it retries data as soon as it is available, and stores only one row at a time in memory, reducing system overhead.
<kode lang='cs'>
var query = $"select top 1 * from MyTable";
 
using var sqlConnection = new SqlConnection(connectionString);
await sqlConnection.OpenAsync(cancellationToken);
using var cmd = new SqlCommand(query, sqlConnection);
using var reader = await cmd.ExecuteReaderAsync(cancellationToken);


using(var sqlConnection = new SqlConnection(connectionString))
var dataList = new List<Data>();
while (await reader.ReadAsync(cancellationToken))
{
{
     using(var sqlCmd = new SqlCommand(cmd, sqlConnection))
     var data = new Data
     {
     {
         try
         Id = reader.GetFieldValue<int>("id"),
        {
        Name = reader.GetFieldValue<string>("name")
            sqlConnection.Open();
    };
            var count = sqlCmd.ExecuteScalar();
    dataList.Add(data);
        }
        catch (SqlException ex)
        { }
    }
}
}
</kode>
</kode>


<filebox fn='Web.config' lang=xml>
== DataAdapter ==
<?xml version="1.0" encoding="utf-8"?>
<kode lang='cs'>
var query = $"select top 1 * from MyTable";
 
using var sqlConnection = new SqlConnection(connectionString);
 
var dataAdapter = new SqlDataAdapter(query, sqlConnection);
 
var dataTable = new DataTable();
sqlConnection.Open();
dataAdapter.Fill(dataTable);
</kode>
 
== ExecuteScalar ==
Reurns the first column of the first row in the result set, or a null reference if the result set is empty.
<kode lang='cs'>
var query = $"select count(*) from MyTable";
 
using var sqlConnection = new SqlConnection(connectionString);
using var sqlCmd = new SqlCommand(query, sqlConnection);
 
sqlConnection.Open();
var count = sqlCmd.ExecuteScalar();
</kode>
 
= Insert query =
<kode lang='cs'>
var query = "insert into MyTable (Column1) values (@value1), (@value2)";
 
using var sqlConnection = new SqlConnection(connectionString);
using var sqlCmd = new SqlCommand(cmd, sqlConnection);
 
sqlCmd.Parameters.Add("@value1", SqlDbType.NVarChar, 50).Value = "Value1";
sqlCmd.Parameters.Add("@value2", SqlDbType.NVarChar, 50).Value = "Value2";
 
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
</kode>
 
= SqlBulkCopy =
<kode lang='cs'>
// TableLock: take bulk update locks on the destination table instead of row locks.
// Avoid transaction deadlock on lock resources with other processes while running SqlBulkCopy in parallel.
using var sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock););
sqlBulkCopy.BulkCopyTimeout = 0; // default: 60s, 0 → no limitation
 
sqlBulkCopy.DestinationTableName = "[schema].[MyDestinationTable]";
// Add mappings so that the column order doesn't matter
sqlBulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");
 
// with DbDataReader
using var cmd = new OdbcCommand(query, odbcConnection);
using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
await sqlBulkCopy.WriteToServerAsync(reader, cancellationToken);


<configuration>
// with DataTable
    <connectionStrings>
await sqlBulkCopy.WriteToServerAsync(sourceDataTable, cancellationToken);
        <add name="MyConnectionString"
</kode>
            connectionString="Server=MyServer;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true;Connection Timeout=3"
            providerName="System.Data.SqlClient" />
</filebox>


= Paramètres =
= Paramètres =
Ligne 38 : Ligne 135 :
var cmd = "SELECT * FROM User WHERE UserName = @UserName AND Password = @Password";
var cmd = "SELECT * FROM User WHERE UserName = @UserName AND Password = @Password";


using (var sqlCmd = new SqlCommand(cmd))
using var sqlCmd = new SqlCommand(cmd);
{
 
    // Create the parameter objects as specific as possible.
// Create the parameter objects as specific as possible.
    sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50);
sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = UserName;
    sqlCmd.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 25);
 
    // Add the parameter values.  Validation should have already happened.
    sqlCmd.Parameters["@UserName"].Value = UserName;
    sqlCmd.Parameters["@Password"].Value = Password;
       
    // écriture plus concise
    sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = UserName;


    // avec DBNull
// avec DBNull
    sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar).Value = (object)UserName ?? DBNull.Value;
sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = (object)UserName ?? DBNull.Value;
}
</kode>
</kode>
{{warn | Le nom de la table ne peut être passé en paramètre.}}
{{warn | Le nom de la table ne peut être passé en paramètre.}}
Ligne 238 : Ligne 326 :
         catch (SqlException ex)
         catch (SqlException ex)
         { }
         { }
</kode>
= ConnectionString =
{{info | You can get the connection string from Visual Studio → View → SQL Server Object Explorer → Select a server → Properties → Connection string}}
{| class="wikitable wtp wtmono1 wtmono3"
|-
| Server || nom ou adresse du serveur || (localdb)\MSSQLLocalDB<br>
tcp:my-server.database.windows.net,1433
|-
| Initial Catalog || nom de la bdd ||
|-
| Integrated Security || || True False
|-
| User ID || || Domain\Username
|-
| Password || mot de passe de l'utilisateur ||
|-
| Application Name || se retrouve dans la colonne {{boxx|program_name}}<br>de la table {{boxx|sys.dm_exec_sessions}} ||
|}
<kode lang='xml'>
<add name="MyConnectionString"
    providerName="System.Data.SqlClient"
    connectionSTring="Server=(localdb)\MSSQLLocalDB;
    Initial Catalog=DataBaseName;
    Integrated Security=True;
    User ID=Domain\Username;
    Password=Password;" />
</kode>
= SqlConnectionStringBuilder =
<kode lang=csharp>
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
connectionStringBuilder.ConnectionString = connectionString;
string serverName = connectionStringBuilder.DataSource;
string databaseName = connectionStringBuilder.InitialCatalog;
</kode>
</kode>


= [http://msdn.microsoft.com/en-us/library/ms131092.aspx Mapping types TSQL et C#] =
= [http://msdn.microsoft.com/en-us/library/ms131092.aspx Mapping types TSQL et C#] =
{| class="wikitable wtp"  
{| class="wikitable wtp wtmono1 wtmono2"  
! TSQL type
! TSQL type
! C# type
! C# type
Ligne 347 : Ligne 398 :
}
}
</kode>
</kode>
= Nuget package =
{| class="wikitable wtp wtmono1"
! Nuget package
! Description
|-
| Microsoft.Data.SqlClient ||
|}


= Erreurs =
= Erreurs =

Dernière version du 23 mars 2023 à 13:02

Connection string

You can get the connection string from Visual Studio → View → SQL Server Object Explorer → Select a server → Properties → Connection string
Key Description Value
Data Source
Server
nom ou adresse du serveur (localdb)\MSSQLLocalDB

tcp:my-server.database.windows.net,1433

Initial Catalog
Database
nom de la bdd
Integrated Security
Trusted_Connection
True, False
User ID Domain\Username
Password mot de passe de l'utilisateur
Application Name se retrouve dans la colonne program_name
de la table sys.dm_exec_sessions

Web.config

Web.config
<?xml version="1.0" encoding="utf-8"?>

<configuration>
    <connectionStrings>
        <add name="MyConnectionString"
             connectionString="Server=MyServer;
                               Database=MyDatabase;
                               Trusted_Connection=True;
                               MultipleActiveResultSets=true;
                               Connection Timeout=3"
             providerName="System.Data.SqlClient" />
Csharp.svg
var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

SqlConnectionStringBuilder

Csharp.svg
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
connectionStringBuilder.ConnectionString = connectionString;

string serverName = connectionStringBuilder.DataSource;
string databaseName = connectionStringBuilder.InitialCatalog;

Select query

DataReader

Faster because it retries data as soon as it is available, and stores only one row at a time in memory, reducing system overhead.

Cs.svg
var query = $"select top 1 * from MyTable";

using var sqlConnection = new SqlConnection(connectionString);
await sqlConnection.OpenAsync(cancellationToken);
using var cmd = new SqlCommand(query, sqlConnection);
using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

var dataList = new List<Data>();
while (await reader.ReadAsync(cancellationToken))
{
    var data = new Data
    {
        Id = reader.GetFieldValue<int>("id"),
        Name = reader.GetFieldValue<string>("name")
    };
    dataList.Add(data);
}

DataAdapter

Cs.svg
var query = $"select top 1 * from MyTable";

using var sqlConnection = new SqlConnection(connectionString);

var dataAdapter = new SqlDataAdapter(query, sqlConnection);

var dataTable = new DataTable();
sqlConnection.Open();
dataAdapter.Fill(dataTable);

ExecuteScalar

Reurns the first column of the first row in the result set, or a null reference if the result set is empty.

Cs.svg
var query = $"select count(*) from MyTable";

using var sqlConnection = new SqlConnection(connectionString);
using var sqlCmd = new SqlCommand(query, sqlConnection);

sqlConnection.Open();
var count = sqlCmd.ExecuteScalar();

Insert query

Cs.svg
var query = "insert into MyTable (Column1) values (@value1), (@value2)";

using var sqlConnection = new SqlConnection(connectionString);
using var sqlCmd = new SqlCommand(cmd, sqlConnection);

sqlCmd.Parameters.Add("@value1", SqlDbType.NVarChar, 50).Value = "Value1";
sqlCmd.Parameters.Add("@value2", SqlDbType.NVarChar, 50).Value = "Value2";

sqlConnection.Open();
sqlCmd.ExecuteNonQuery();

SqlBulkCopy

Cs.svg
// TableLock: take bulk update locks on the destination table instead of row locks.
// Avoid transaction deadlock on lock resources with other processes while running SqlBulkCopy in parallel.
using var sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock););
sqlBulkCopy.BulkCopyTimeout = 0; // default: 60s, 0 → no limitation

sqlBulkCopy.DestinationTableName = "[schema].[MyDestinationTable]";
// Add mappings so that the column order doesn't matter
sqlBulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");

// with DbDataReader
using var cmd = new OdbcCommand(query, odbcConnection);
using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
await sqlBulkCopy.WriteToServerAsync(reader, cancellationToken);

// with DataTable
await sqlBulkCopy.WriteToServerAsync(sourceDataTable, cancellationToken);

Paramètres

L'utilisation de paramètres rend le code résistant aux injections de code SQL.

Csharp.svg
var cmd = "SELECT * FROM User WHERE UserName = @UserName AND Password = @Password";

using var sqlCmd = new SqlCommand(cmd);

// Create the parameter objects as specific as possible.
sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = UserName;

// avec DBNull
sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = (object)UserName ?? DBNull.Value;
Le nom de la table ne peut être passé en paramètre.

Insérer une liste d'éléments

Csharp.svg
var cmd = "insert into MaTable values (@id, @data1, @data2)";

using (var sqlConnection = new SqlConnection("ConnectionString"))
{
    using (var sqlCmd = new SqlCommand(cmd, sqlConnection))
    {
        try
        {
            sqlCmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
            sqlCmd.Parameters.Add("@data1", System.Data.SqlDbType.Date);
            sqlCmd.Parameters.Add("@data2", System.Data.SqlDbType.TinyInt);

            sqlConnection.Open();

            foreach (var element in elements)
            {
                sqlCmd.Parameters["@id"].Value = element.Id;
                sqlCmd.Parameters["@data1"].Value = element.Data1;
                sqlCmd.Parameters["@data2"].Value = element.Data2;

                sqlCmd.ExecuteNonQuery();
            }
        }
        catch (SqlException ex) { }

SQLParameter

Csharp.svg
var parameters = new List<SqlParameter>();

parameters.Add(new SqlParameter(name, SqlDbType.Int) { Value = (object)myInt?.ToInt32(CultureInfo.InvariantCulture) ?? DBNull.Value });
parameters.Add(new SqlParameter(name, SqlDbType.Decimal) { Value = (object)myDecimal ?? DBNull.Value });
parameters.Add(new SqlParameter(name, SqlDbType.Bit) { Value = myBool.HasValue ? (object)(value.Value ? 1 : 0) : DBNull.Value });
parameters.Add(new SqlParameter(name, SqlDbType.Date) { Value = (object)myDate ?? DBNull.Value });
parameters.Add(new SqlParameter(name, SqlDbType.Char) { Value = (object)myString ?? DBNull.Value });
parameters.Add(new SqlParameter(name, SqlDbType.NVarChar) { Value = (object)myString ?? DBNull.Value });

// List<int>
var idRecords = myListInt.Distinct().Select(
id =>
{
    var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));
    record.SetInt32(0, id);
    return record;
}).ToList();
parameters.Add(
    new SqlParameter(name, SqlDbType.Structured)
    {
        TypeName = "schema.T_IdTable",
        Value = idRecords.Count > 0 ? idRecords : null
    });

// List<string>
var nameRecords = value.Distinct().Select(
    name =>
    {
        var record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 50));
        record.SetString(0, name);
        return record;
    }).ToList();
parameters.Add(
    new SqlParameter(name, SqlDbType.Structured)
    {
        TypeName = "schema.T_NameTable",
        Value = nameRecords.Count > 0 ? nameRecords: null
    });

var parameterNamesList = string.Join(", ", parameters.Select(p => p.ParameterName));

var storedProcedureResultRows = await dbContext.Database.SqlQuery<StoredProcedureResultRow>(
    $"{StoredProcedureName} {parameterNamesList}",
    parameters.Cast<object>().ToArray()).ToListAsync(cancellationToken);
User-Defined Table Type
SqlParameter et EF.

SqlCommand

ExecuteScalar

Exécute la requête et retourne la première colonne de la première ligne du jeu de résultats retourné par la requête.
Les colonnes ou lignes supplémentaires sont ignorées.

Csharp.svg
var cmd = "SELECT UserId FROM User WHERE UserName = Nicolas";

using (var sqlCmd = new SqlCommand(cmd))
{
    var userId = sqlCmd.ExecuteScalar();
}

ExecuteReader

Csharp.svg
var cmd = "SELECT * FROM table";

using(var sqlCmd = new SqlCommand(cmd, sqlConnection))
{
    sqlConnection.Open();
    using(SqlDataReader sdr = sqlCmd.ExecuteReader())
    {
        // liste des headers des colonnes
        var columns = Enumerable.Range(0, sdr.FieldCount).Select(sdr.GetName).ToList();

        while (sdr.Read())
        {
            var value = sdr["column name"] as string;
            var value = sdr["column name"] as int?;
            var value = (short)sdr["column name"];
            // lance IndexOutOfRangeException si column name n'existe pas

            for (int i = 0; i < sdr.FieldCount; i++)
            {
                var columnName = sdr.GetName(i);
                var value = sdr.GetValue(i);
            }
        }
    }
}

ExecuteNonQuery

Csharp.svg
string cmd = "INSERT INTO ma_table (colonne1, colonne2) VALUES (@param1, @param2)";
using (var sqlCmd = new SqlCommand(cmd, sqlConnection))
{
    sqlCmd.Parameters.Add("@param1", SqlDbType.NVarChar, 50).Value = "valeur1";
    sqlCmd.Parameters.Add("@param2", SqlDbType.SmallInt, 1).Value = 0;

    // retourne le nombre de lignes affectées
    int nbOfAffectedRows = sqlCmd.ExecuteNonQuery();
}

Transaction

Cs.svg
string cmd = "insert into MaTable (colonne1, colonne2) values (@param1, @param2)";
using (var sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();
    SqlTransaction txn = sqlConnection.BeginTransaction();
    try
    {
        foreach (var element in MyCollection)
        {
            using (var sqlCmd = new SqlCommand(cmd, sqlConnection, txn))
            {
                sqlCmd.Parameters.Add("@param1", SqlDbType.NVarChar, 50).Value = element.Property1;
                sqlCmd.Parameters.Add("@param2", SqlDbType.SmallInt, 1).Value = element.Property2;
                sqlCmd.ExecuteNonQuery();
            }
        }
        txn.Commit();
    }
    catch (Exception ex)
    {
        txn.Rollback();
    }
}

DataTable.Load

Csharp.svg
var dataTable = new DataTable();
var cmd = "SELECT * FROM ma_table;";

using(var sqlConnection = new SqlConnection(connectionString))
{
    using(var sqlCmd = new SqlCommand(cmd, sqlConnection))
    {
        try
        {
            sqlConnection.Open();

            using (var sdr = sqlCmd.ExecuteReader())
            {
                dataTable.Load(sdr);
            }
        }
        catch (SqlException ex)
        { }

Mapping types TSQL et C#

TSQL type C# type Détails
bit bool, bool? 1 → TRUE, 0 → FALSE
date
datetime
datetime2
DateTime, DateTime? T-SQL date
tinyint sbyte, sbyte? (SByte, SByte?) 1 Byte: -128 to 127
smallint short, short? (Int16, Int16?) 2 Bytes: -2^15 (-32,768) to 2^15-1 (32,767)
int int, int? (Int32, Int32?) 4 Bytes: -2^31 to 2^31-1
bigint long, long? (Int64, Int64?) 8 Bytes: -2^63 to 2^63-1
decimal decimal, decimal?
float double, double?
nchar
nvarchar
string, char[]
real float, float? (Single, Single?)

Activer l'intégration CLR

Tsql.svg
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

-- test si l'intégration CLR est activée
SELECT * FROM sys.configurations
WHERE name = 'clr enabled'
-- La colonne value doit avoir la valeur 1
IF NOT EXISTS (SELECT 1 FROM sys.configurations WHERE name = 'clr enabled' and value = 1) 
begin
    RAISERROR('CLR is NOT Enabled.',16,1)
end
GO

Exceptions

Csharp.svg
catch (SqlException ex)
{
    foreach (SqlError error in ex.Errors)
    {
        string message = error.Message;
    }
}
catch (IndexOutOfRangeException ex)
{
    // xxx n'est pas compris dans le select
    // ou si * est utilisé, xxx n'est pas une colonne de la table
    // sdr["xxx"] as string
    // ex.Message = "xxx"
}
catch (Exception ex)
{

}

Nuget package

Nuget package Description
Microsoft.Data.SqlClient

Erreurs

string or binary data would be truncated

Ici c'est l'utilisation de quotes autour de @value1 et @value2 qui cause l'erreur.

Csharp.svg
string cmd = "INSERT INTO ma_table (param1, param2) VALUES ('@value1', '@value2')";