public void AjouterDeses(DesesPersonne P)
{
conx.Query(@"insert into Deces(Num_Deses, AnneeAct_Deses, Date_Deses)
values (@1,@2,@3)");
conx.cmd.Parameters.AddWithValue("@1", P._Num_Deses);
conx.cmd.Parameters.AddWithValue("@2", P._AnneeAct_Deses);
conx.cmd.Parameters.AddWithValue("@3", P._Date_Deses);
conx.ExecuteNonQuery();
}
/* ajout*/
public void AjouterCulture(Culture C)
{
conx.Query("insert into Culture values (@1,@2,@3,@4,@5)");
conx.cmd.Parameters.AddWithValue("@1", C._numCulture);
conx.cmd.Parameters.AddWithValue("@2", C._nomCulture);
conx.cmd.Parameters.AddWithValue("@3", C._coefExportGrain);
conx.cmd.Parameters.AddWithValue("@4", C._coefExportPaille);
conx.cmd.Parameters.AddWithValue("@5", C._phytoSanitaire);
conx.ExecuteNonQuery();
}
/// <summary>
/// Archives the message properties.
/// </summary>
/// <param name="connection">The connection to SQL Server that will be used to perform the archiving operation.</param>
private void ArchiveMessageProperties(SqlServerConnection connection)
{
try
{
string sql =
"INSERT INTO [MessageArchive].[dbo].[MessageProperty] " +
"([MessageId], [ContextData])" +
" VALUES " +
"(@MessageId, @ContextData)";
int index = 0;
foreach (MessageProperty property in this.MessageProperties)
{
index++;
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter parmMessageId = sqlCommand.Parameters.Add("@MessageId", SqlDbType.UniqueIdentifier);
SqlParameter parmContextData = sqlCommand.Parameters.Add("@ContextData", SqlDbType.NVarChar);
parmMessageId.Value = property.MessageId;
parmContextData.Value = property.ContextData;
int rows = connection.ExecuteNonQuery(sqlCommand);
}
}
catch (Exception exception)
{
throw new Exception("Insertion of message property records into the archive failed.", exception);
}
}
/// <summary>
/// Archives the properties of the message part.
/// </summary>
/// <param name="connection">The connection to SQL Server that will be used to perform the archiving operation.</param>
private void ArchivePartProperties(SqlServerConnection connection)
{
try
{
string sql =
"INSERT INTO [MessageArchive].[dbo].[PartProperty] " +
"([PartId], [PropertyIndex], [Namespace], [Name], [Value])" +
" VALUES " +
"(@PartId, @PropertyIndex, @Namespace, @Name, @Value)";
int index = 0;
foreach (PartProperty partProp in this.PartsProperties)
{
index++;
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter parmPartId = sqlCommand.Parameters.Add("@PartId", SqlDbType.UniqueIdentifier);
SqlParameter parmPropertyIndex = sqlCommand.Parameters.Add("@PropertyIndex", SqlDbType.Int);
SqlParameter parmNamespace = sqlCommand.Parameters.Add("@Namespace", SqlDbType.NVarChar, 256);
SqlParameter parmName = sqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256);
SqlParameter parmValue = sqlCommand.Parameters.Add("@Value", SqlDbType.NVarChar);
parmPartId.Value = partProp.PartId;
parmPropertyIndex.Value = partProp.PropertyIndex;
parmNamespace.Value = partProp.Namespace;
parmName.Value = partProp.Name;
parmValue.Value = partProp.Value;
int rows = connection.ExecuteNonQuery(sqlCommand);
}
}
catch (Exception exception)
{
throw new Exception("Insertion of the part property records into the archive failed.", exception);
}
}
public bool UpdateInfo(string firstName, string firstSurname, string secondSurname, string email, string tel, DateTime dob)
{
string query = @"update ANUser set useFirName = @FIRSTNAME, useFirSurname = @FIRSTSURNAME, useSecSurname = @SECONDSURNAME, useEmail = @EMAIL,
useTel = @TEL, useBirthDate = convert(datetime, @DOB, 101) where useNumber = @USERID";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@FIRSTNAME", firstName);
command.Parameters.AddWithValue("@USERID", _useNumber);
command.Parameters.AddWithValue("@FIRSTSURNAME", firstSurname);
command.Parameters.AddWithValue("@SECONDSURNAME", secondSurname);
command.Parameters.AddWithValue("@EMAIL", email);
command.Parameters.AddWithValue("@TEL", tel);
command.Parameters.AddWithValue("@DOB", dob);
if (SqlServerConnection.ExecuteNonQuery(command) > 0)
{
return(true);
}
else
{
return(false);
}
}
public void AjouterTraitementPhytoMetier(Models.TraitementPhyto P)
{
conx.Query("insert into TraitementPhyto values (@1,@2,@3,@4,@5,@6)");
conx.cmd.Parameters.AddWithValue("@1", P._RefProdPhy);
conx.cmd.Parameters.AddWithValue("@2", P._NumParcelle);
conx.cmd.Parameters.AddWithValue("@3", P._DatePrevue);
conx.cmd.Parameters.AddWithValue("@4", P._DateRealisation);
conx.cmd.Parameters.AddWithValue("@5", P._Quantite);
conx.cmd.Parameters.AddWithValue("@6", P._Cout);
conx.ExecuteNonQuery();
}
public void AjouterParcelle(Models.Parcelle P)
{
conx.Query("insert into Parcelle values (@1,@2,@3,@4,@5,@6)");
conx.cmd.Parameters.AddWithValue("@1", P._numparcelle);
conx.cmd.Parameters.AddWithValue("@2", P._nomparcelle);
conx.cmd.Parameters.AddWithValue("@3", P._surface);
conx.cmd.Parameters.AddWithValue("@4", P._typesol);
conx.cmd.Parameters.AddWithValue("@5", P._profondeur);
conx.cmd.Parameters.AddWithValue("@6", P._adress);
conx.ExecuteNonQuery();
}
/* ajout*/
public void AjouterParents(Personne P)
{
conx.Query(@"insert into Personne(Num,AnneeAct,NomFR,NomAR,PrenomFR,prenomAR,DateNaissance,NationnaliteFR,NationnaliteAR,LieuNaissFR,LieuNaissAR,ProfessionFR,professionAR,DomicileFR,DomicileAR)
values (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15)");
conx.cmd.Parameters.AddWithValue("@1", P._Num);
conx.cmd.Parameters.AddWithValue("@2", P._AnneeAct);
conx.cmd.Parameters.AddWithValue("@3", P._NomFR);
conx.cmd.Parameters.AddWithValue("@4", P._NomAR);
conx.cmd.Parameters.AddWithValue("@5", P._PrenomFR);
conx.cmd.Parameters.AddWithValue("@6", P._prenomAR);
conx.cmd.Parameters.AddWithValue("@7", P._DateNaissance);
conx.cmd.Parameters.AddWithValue("@8", P._NationnaliteFR);
conx.cmd.Parameters.AddWithValue("@9", P._NationnaliteAR);
conx.cmd.Parameters.AddWithValue("@10", P._LieuNaissFR);
conx.cmd.Parameters.AddWithValue("@11", P._LieuNaissAR);
conx.cmd.Parameters.AddWithValue("@12", P._ProfessionFR);
conx.cmd.Parameters.AddWithValue("@13", P._professionAR);
conx.cmd.Parameters.AddWithValue("@14", P._DomicileFR);
conx.cmd.Parameters.AddWithValue("@15", P._DomicileAR);
conx.ExecuteNonQuery();
}
/// <summary>
/// Archives the message (not including parts or properties).
/// </summary>
/// <param name="expiryMinutes">The amount of time (in minutes) before the archived message expires and will be automatically deleted from the archive.</param>
/// <param name="tag">A tag value to be associated with the archived message.</param>
/// <param name="connection">The connection to SQL Server that will be used to perform the archiving operation.</param>
private void ArchiveMessage(int expiryMinutes, SqlServerConnection connection)
{
string sql =
"INSERT INTO [MessageArchive].[dbo].[Message] " +
"([MessageId], [InterchangeId], [MessageType], [ActivityId], [Tag], [InsertedDate], [ArchiveTypeId], [SourceSystemId], [TargetSystemId], [Description])" +
" VALUES " +
"(@MessageId, @InterchangeId, @MessageType, @ActivityId, @Tag, @InsertedDate, @ArchiveTypeId, @SourceSystemId, @TargetSystemId, @Description)";
SqlCommand sqlCommand = new SqlCommand(sql);
try
{
DateTime insertedDate = DateTime.UtcNow;
DateTime expiryDate = insertedDate + new TimeSpan(0, expiryMinutes, 0);
SqlParameter parmMessageId = sqlCommand.Parameters.Add("@MessageId", SqlDbType.UniqueIdentifier);
SqlParameter parmInterchangeId = sqlCommand.Parameters.Add("@InterchangeId", SqlDbType.NVarChar, 36);
SqlParameter parmMessageType = sqlCommand.Parameters.Add("@MessageType", SqlDbType.NVarChar, 256);
SqlParameter parmActivityId = sqlCommand.Parameters.Add("@ActivityId", SqlDbType.NVarChar, 36);
SqlParameter parmTag = sqlCommand.Parameters.Add("@Tag", SqlDbType.NVarChar, 50);
SqlParameter parmInsertedDate = sqlCommand.Parameters.Add("@InsertedDate", SqlDbType.DateTime);
SqlParameter parmArchiveType = sqlCommand.Parameters.Add("@ArchiveTypeId", SqlDbType.Int);
SqlParameter parmSourceSystem = sqlCommand.Parameters.Add("@SourceSystemId", SqlDbType.Int);
SqlParameter parmTargetSystem = sqlCommand.Parameters.Add("@TargetSystemId", SqlDbType.Int);
SqlParameter parmDescription = sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 250);
parmMessageId.Value = this.Message.MessageId;
parmInterchangeId.Value = (this.Message.InterchangeId == null ? (object)DBNull.Value : (object)this.Message.InterchangeId);
parmMessageType.Value = this.Message.MessageType;
parmActivityId.Value = (this.Message.ActivityId == null ? (object)DBNull.Value : (object)this.Message.ActivityId);
parmTag.Value = this.Message.Tag;
parmInsertedDate.Value = this.Message.InsertedDate;
parmArchiveType.Value = (this.Message.ArchiveTypeId == null ? (object)DBNull.Value : (object)this.Message.ArchiveTypeId);
parmSourceSystem.Value = (this.Message.SourceSystemId == null ? (object)DBNull.Value : (object)this.Message.SourceSystemId);
parmTargetSystem.Value = (this.Message.TargetSystemId == null ? (object)DBNull.Value : (object)this.Message.TargetSystemId);
parmDescription.Value = (this.Message.Description == null ? (object)DBNull.Value : (object)this.Message.Description);
int rows = connection.ExecuteNonQuery(sqlCommand);
}
catch (Exception exception)
{
string strSqlInsertQuery = GenerateSqlString(sqlCommand);
throw new Exception("Insertion of the message record into the archive failed for this query : " + strSqlInsertQuery, exception);
}
}
public static bool DeleteCard(int id, int user)
{
string query = @"BEGIN TRANSACTION
Delete from User_Card where ucCard = @carUniNumber AND ucUser = @user
Delete from Tarjeta where tarNumUnico = @carUniNumber
COMMIT";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@carUniNumber", id);
command.Parameters.AddWithValue("@user", user);
if (SqlServerConnection.ExecuteNonQuery(command) > 0)
{
return(true); //command executed successfully
}
else
{
return(false); //could not execute command
}
}
public void AjouterEtat(AddEtatPersonne P)
{
conx.Query(@"update Personne set FaiteParFR=@3,FaiteParAR=@4,DateDr=@5,Sexe=@6,SigneFR=@7,signeAR=@8,OfficierFr=@9,
OfficierAr=@10,HamichFR=@11,HamichAR=@12 where Num=@1 and AnneeAct=@2 ");
conx.cmd.Parameters.AddWithValue("@1", P._Num);
conx.cmd.Parameters.AddWithValue("@2", P._AnneeAct);
conx.cmd.Parameters.AddWithValue("@3", P._FaiteParFR);
conx.cmd.Parameters.AddWithValue("@4", P._FaiteParAR);
conx.cmd.Parameters.AddWithValue("@5", P._DateDr);
conx.cmd.Parameters.AddWithValue("@6", P._Sexe);
conx.cmd.Parameters.AddWithValue("@7", P._SigneFR);
conx.cmd.Parameters.AddWithValue("@8", P._signeAR);
conx.cmd.Parameters.AddWithValue("@9", P._OfficierFr);
conx.cmd.Parameters.AddWithValue("@10", P._OfficierAr);
conx.cmd.Parameters.AddWithValue("@11", P._HamichFR);
conx.cmd.Parameters.AddWithValue("@12", P._HamichAR);
conx.ExecuteNonQuery();
}
public static bool AddCard(string number, string name, string expdate, string ccv, int user)
{
string query = "select MAX(carUniNumber) as LastCard from Card";
SqlCommand command = new SqlCommand(query); //command
DataTable table = SqlServerConnection.ExecuteQuery(command); //execute query
int lastid = 0;
foreach (DataRow row in table.Rows)
{
lastid = (int)row["LastCard"];
}
lastid++;
if (lastid != 0)
{
string query2 = @"BEGIN TRANSACTION
INSERT INTO Card values(@carNumber, @carExpDate, @carHolder, @carCCV)
INSERT INTO User_Card VALUES(@ucUser, @ucCard)
COMMIT";
SqlCommand command2 = new SqlCommand(query2); //command
command2.Parameters.AddWithValue("@carNumber", number);
command2.Parameters.AddWithValue("@carExpDate", expdate);
command2.Parameters.AddWithValue("@carHolder", name);
command2.Parameters.AddWithValue("@carCCV", ccv);
command2.Parameters.AddWithValue("@ucUser", user);
command2.Parameters.AddWithValue("@ucCard", lastid);
if (SqlServerConnection.ExecuteNonQuery(command2) > 0)
{
return(true); //command executed successfully
}
else
{
return(false); //could not execute command
}
}
else
{
return(false);
}
}
/// <summary>
/// Archives the parts of the message.
/// </summary>
/// <param name="includeProperties">A flag indicating if properties should be archived along with the message.</param>
/// <param name="connection">The connection to SQL Server that will be used to perform the archiving operation.</param>
private void ArchiveParts(SqlServerConnection connection)
{
string sql =
"INSERT INTO [MessageArchive].[dbo].[Part] " +
"([MessageId], [PartId], [PartName], [PartIndex], [ContentType], [CharSet], [TextData], [ImageData])" +
" VALUES " +
"(@MessageId, @PartId, @PartName, @PartIndex, @ContentType, @CharSet, @TextData, @ImageData)";
foreach (Part part in this.Parts)
{
try
{
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter parmMessageId = sqlCommand.Parameters.Add("@MessageId", SqlDbType.UniqueIdentifier);
SqlParameter parmPartId = sqlCommand.Parameters.Add("@PartId", SqlDbType.UniqueIdentifier);
SqlParameter parmPartName = sqlCommand.Parameters.Add("@PartName", SqlDbType.NVarChar, 256);
SqlParameter parmPartIndex = sqlCommand.Parameters.Add("@PartIndex", SqlDbType.Int);
SqlParameter parmContentType = sqlCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50);
SqlParameter parmCharSet = sqlCommand.Parameters.Add("@CharSet", SqlDbType.NVarChar, 50);
SqlParameter parmTextData = sqlCommand.Parameters.Add("@TextData", SqlDbType.NVarChar);
SqlParameter parmImageData = sqlCommand.Parameters.Add("@ImageData", SqlDbType.Image);
parmMessageId.Value = part.MessageId;
parmPartId.Value = part.PartId;
parmPartName.Value = part.PartName;
parmPartIndex.Value = part.PartIndex;
parmContentType.Value = part.ContentType;
parmCharSet.Value = part.CharSet;
parmTextData.Value = part.TextData ?? (object)DBNull.Value;
parmImageData.Value = ((part.ImageData != null) && (part.ImageData.Length > 0))
? part.ImageData
: (object)DBNull.Value;
int rows = connection.ExecuteNonQuery(sqlCommand);
}
catch (Exception exception)
{
throw new Exception("Insertion of the part record into the archive failed.", exception);
}
}
}
public bool NewBet()
{
string query = @"BEGIN TRANSACTION
insert into Bet values(@AMOUNT, @USER, @TEAM, @MATCH)
update Match set matTotalBets = (matTotalBets + @AMOUNT) where matNumber = @MATCH
update Match_Team set mtTeamBets = (mtTeamBets + @AMOUNT) where mtMatch = @MATCH and mtTeam = @TEAM
COMMIT";
SqlCommand command = new SqlCommand(query); //command
command.Parameters.AddWithValue("@AMOUNT", _amount);
command.Parameters.AddWithValue("@USER", _user.Number);
command.Parameters.AddWithValue("@TEAM", _team.Id);
command.Parameters.AddWithValue("@MATCH", _match.Id);
if (SqlServerConnection.ExecuteNonQuery(command) > 0)
{
return(true); //command executed successfully
}
else
{
return(false); //could not execute command
}
}
public bool ModifyCredits(bool sum, int amount)
{
string query;
if (sum)
{
query = "update ANUser set useCredits = (useCredits + @amount) where useNumber = @userId";
}
else
{
query = "update ANUser set useCredits = (useCredits - @amount) where useNumber = @userId";
}
SqlCommand command2 = new SqlCommand(query); //command
command2.Parameters.AddWithValue("@amount", amount);
command2.Parameters.AddWithValue("@userId", _useNumber);
if (SqlServerConnection.ExecuteNonQuery(command2) > 0)
{
if (sum)
{
_useCredits = _useCredits + amount;
}
else
{
_useCredits = _useCredits - amount;
}
return(true);
}
else
{
return(false); //could not execute command
}
}