Version du 14 mars 2023 à 10:47

Connection string

Server	nom ou adresse du serveur	(localdb)\MSSQLLocalDB 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 program_name de la table sys.dm_exec_sessions

<add name="MyConnectionString"
     providerName="System.Data.SqlClient"
     connectionSTring="Server=(localdb)\MSSQLLocalDB;
    Initial Catalog=DataBaseName;
    Integrated Security=True;
    User ID=Domain\Username;
    Password=Password;" />

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" />

var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

SqlConnectionStringBuilder

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

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

Base

var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

var cmd = "SELECT COUNT(*) from table where x IS NULL AND y <> 'NULL';";

using(var sqlConnection = new SqlConnection(connectionString))
{
    using(var sqlCmd = new SqlCommand(cmd, sqlConnection))
    {
        try
        {
            sqlConnection.Open();
            var count = sqlCmd.ExecuteScalar();
        }
        catch (SqlException ex)
        { }
    }
}

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" />

Paramètres

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

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);
    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
    sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar).Value = (object)UserName ?? DBNull.Value;
}

Le nom de la table ne peut être passé en paramètre.

Insérer une liste d'éléments

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

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);

	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.

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

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

ExecuteReader

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

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

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

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

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

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)
{

}

Erreurs

string or binary data would be truncated

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

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

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

Version du 14 mars 2023 à 10:47

Sommaire

Connection string

Web.config

SqlConnectionStringBuilder

Base

Paramètres

Insérer une liste d'éléments

SQLParameter

SqlCommand

ExecuteScalar

ExecuteReader

ExecuteNonQuery

Transaction

DataTable.Load

Mapping types TSQL et C#

Activer l'intégration CLR

Exceptions

Erreurs

string or binary data would be truncated

Actions de la page

Actions de la page

Outils personnels

Menu de navigation

Navigation

Rechercher

Outils

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

Version du 14 mars 2023 à 10:47

Connection string

Web.config

SqlConnectionStringBuilder

Base

Paramètres

Insérer une liste d'éléments

SQLParameter

SqlCommand

ExecuteScalar

ExecuteReader

ExecuteNonQuery

Transaction

DataTable.Load

Mapping types TSQL et C#

Activer l'intégration CLR

Exceptions

Erreurs

string or binary data would be truncated

Menu de navigation

Rechercher

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