public Person Add(Person entity)
{
Person result = null;
using (SqlCommand cmd = new SqlCommand(@"INSERT INTO Person
(ID, FirstName,LastName,Sex,MariedId, ParentId)
output INSERTED.ID
VALUES( NEWID(), @firstName,@lastName, @sex, @mariedId, @parentId)", dbConnection))
{
cmd.Parameters.AddWithValue("@firstName", entity.FirstName);
cmd.Parameters.AddWithValue("@lastName", entity.LastName);
cmd.Parameters.AddWithValue("@sex", entity.Sex);
cmd.Parameters.AddWithValue("@mariedId", entity.MariedId);
cmd.Parameters.AddWithValue("@parentId", entity.ParentId);
dbConnection.Open();
string ID = (string)cmd.ExecuteScalar();
if (dbConnection.State == System.Data.ConnectionState.Open)
dbConnection.Close();
result = entity;
result.ID = ID;
}
return result;
}
public Person Generate()
{
Person result = new Person();
FirstName firstName = GenerateFirstName();
LastName lastName = GenerateLastName();
result.ID = "-1";
result.FirstName = firstName.Value;
result.LastName = lastName.Value;
result.Sex = firstName.Sex;
result.MariedId = -1;
result.ParentId = -1;
return result;
}
public Person Add(Person entity)
{
Person result = null;
Request addRequest = Session.NewAdhocQuery(
@"xquery version ""1.0-ml"";
import module namespace dbt='http://www.capitalid.nl/xquery/DBTester' at 'Persons.xqy';
declare variable $firstName as xs:string external;
declare variable $lastName as xs:string external;
declare variable $sex as xs:short external;
declare variable $mariedId as xs:long external;
declare variable $parentId as xs:long external;
dbt:add-person($firstName, $lastName, $sex, $mariedId, $parentId);
");
addRequest.SetNewStringVariable("firstName", entity.FirstName);
addRequest.SetNewStringVariable("lastName", entity.LastName);
addRequest.SetNewIntegerVariable("sex", (long)entity.Sex);
addRequest.SetNewIntegerVariable("mariedId", entity.MariedId);
addRequest.SetNewIntegerVariable("parentId", entity.ParentId);
//RequestOptions options = new RequestOptions();
//options.CacheResult = false;
//addRequest.Options = options;
ResultSequence resultSequence = Session.SubmitRequest(addRequest);
if ( resultSequence.HasNext() )
{
ResultItem resultItem = resultSequence.Next();
entity.ID = resultItem.Item.AsString();
}
resultSequence.Close();
return result;
}
public Person Get(long id)
{
Person result = null;
using (SqlCommand cmd = new SqlCommand(@"SELECT ID, FIRSTNAME, LASTNAME, SEX, MARIEDID, PARENTID
FROM PERSON
WHERE ID = @id
", dbConnection))
{
cmd.Parameters.AddWithValue("@id", id);
dbConnection.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (dbConnection.State == System.Data.ConnectionState.Open)
dbConnection.Close();
if (reader.HasRows)
{
result = new Person();
result.ID = reader.GetString(0);
result.FirstName = reader.GetString(1);
result.LastName = reader.GetString(2);
result.Sex = (Sex)reader.GetInt16(3);
result.MariedId = reader.GetInt64(4);
result.ParentId = reader.GetInt64(5);
}
}
return result;
}
public void Remove(Person entity)
{
using (SqlCommand cmd = new SqlCommand(@"DELETE
FROM PERSON
WHERE ID = @id
", dbConnection))
{
cmd.Parameters.AddWithValue("@id", entity.ID);
dbConnection.Open();
cmd.ExecuteNonQuery();
if (dbConnection.State == System.Data.ConnectionState.Open)
dbConnection.Close();
}
}
public void Modify(Person modifiedEntity, Person oldEntity = null)
{
using (SqlCommand cmd = new SqlCommand(@"UPDATE PERSON
SET FirstName = @firstName,
LastName = @lastName,
Sex = @sex,
MariedId = @mariedId,
ParentId = @parentId
WHERE ID = @id
", dbConnection))
{
cmd.Parameters.AddWithValue("@firstName", modifiedEntity.FirstName);
cmd.Parameters.AddWithValue("@lastName", modifiedEntity.LastName);
cmd.Parameters.AddWithValue("@sex", modifiedEntity.Sex);
cmd.Parameters.AddWithValue("@mariedId", modifiedEntity.MariedId);
cmd.Parameters.AddWithValue("@parentId", modifiedEntity.ParentId);
cmd.Parameters.AddWithValue("@id", modifiedEntity.ID);
dbConnection.Open();
cmd.ExecuteNonQuery();
if (dbConnection.State == System.Data.ConnectionState.Open)
dbConnection.Close();
}
}
public void Remove(Person entity)
{
throw new NotImplementedException();
}
public void Modify(Person modifiedEntity, Person oldEntity = null)
{
throw new NotImplementedException();
}