1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
//Declaration globale
private const string PARM_Nom = "@Nom";
private const string PARM_Prenom = "@Prenom";
...
object[] ICandidat.AddCandidat(CandidatInfo Candidat)
{
//Corps de la methode
string SqlAdd = "INSERT INTO Personne (Nom, Prenom, ... ";
SqlAdd = SqlAdd + VALUES(@Nom, @Prenom,...";
SqlAdd = SqlAdd + "); SELECT @@IDENTITY;";
SqlParameter[] parms {
new SqlParameter(PARM_Nom, SqlDbType.VarChar),
new SqlParameter(PARM_Prenom, SqlDbType.VarChar),
...
};
parms[0].Value = Candidat.NomFr;
parms[1].Value = Candidat.PrenomFr;
...
SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
object ID;
object[] Resultat = new object[2];
try
{
ID = SqlHelper.ExecuteScalar(trans, CommandType.Text, SqlAdd,parms);
trans.Commit();
Resultat[0] = bool.Parse("true");
Resultat[1] = int.Parse(ID.ToString());
return Resultat;
}
catch
{
trans.Rollback();
Resultat[0] = bool.Parse("false");
Resultat[1] = int.Parse("0");
return Resultat;
}
finally
{
conn.Close();
}
}
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
} |
Utilisation de parametres pour les requêtes SQL
Répondre avec citation
.
), et définir le type de données de ton paramètre:
Envoyé par Saten
) 
Partager