/// Transforms a FacebookUser to a Contact object
public static Contact facebookUserToContact( FacebookUser facebookUser ) {
// Contact object with partial information of a Facebook User
var contact = new Contact {
Firstname = facebookUser.first_name,
Lastname = facebookUser.last_name,
SocialNetworkBasicInfo = new SocialNetworkBasicInfo {
SocialNetworkID = facebookUser.id,
WebSite = facebookUser.website,
Languages = new List<SocialNetworkLanguage>(),
EMails = new List<EMail>(),
Addresses = new List<Address>()
},
Contacts = new List<Contact>()
};
// User specified his/her spoken Languages ?
if ( facebookUser.languages != null ) {
// Add Facebook Languages to the Contact object
foreach ( var fbLanguage in facebookUser.languages ) {
// Convert FacebookLanguage to SocialNetworkLanguage
contact.SocialNetworkBasicInfo.Languages.Add(
new SocialNetworkLanguage {
Id = long.Parse( fbLanguage.id ),
Name = fbLanguage.name
}
);
} // For
} // If
// Assume Facebook User has 1 Personal mail (maximum)
contact.SocialNetworkBasicInfo.EMails.Add(
new EMail {
Address = facebookUser.email,
Type = EMailType.Personal
}
);
///
// Assume that Facebook Users do not specify their Addresses
///
return contact;
} // Method
} // Method
#endregion
#region Helper Methods
/// Add Contact to Federation MyNetDB
public void addContact( Contact contact ) {
// Connect to MyNetDB
using ( var dbConnection = new SqlConnection( MyNetDbConnectionString ) ) {
dbConnection.Open();
// Execute SQL statements
using ( SqlCommand sqlCommand = dbConnection.CreateCommand() ) {
///
// Assign Contacts IDs and AddressIDs
///
// Contact ID
contact.Id = nextContactID++;
// Contact Friends IDs
if ( contact.Contacts != null ) {
foreach ( var c in contact.Contacts )
c.Id = nextContactID++;
}
// Contact Addresses IDs
if ( contact.SocialNetworkBasicInfo.Addresses != null ) {
foreach ( var a in contact.SocialNetworkBasicInfo.Addresses )
a.Id = nextAddressID++;
}
// Connect to Federation Member
sqlCommand.CommandText = "USE FEDERATION MyNetFederation(ContactLanguageID=0) WITH RESET, FILTERING=OFF";
sqlCommand.ExecuteNonQuery();
// Step 1: Is the contact already in SocialNetworkBasicInfo ?
sqlCommand.CommandText = String.Format( "SELECT * FROM mynet.SocialNetworkBasicInfo WHERE SocialNetworkID='{0}'", contact.SocialNetworkBasicInfo.SocialNetworkID );
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
// Yes: Insert Contact (and all its associated objects) into the DB
if ( !reader.HasRows ) {
reader.Close();
sqlCommand.CommandText = Utility.Convert.ContactToSqL( contact );
sqlCommand.ExecuteNonQuery();
} else {
reader.Close();
sqlCommand.CommandText = Utility.Convert.BasicContactToSqL( contact );
sqlCommand.ExecuteNonQuery();
}
} // SqlCommand
dbConnection.Close();
} // dbConnection
} // Method
} // Method
/// Transforms a Contact object to SQL statements targeting MyNetDB
public static String ContactToSqL( Contact contact ) {
var sqlStatements = new StringBuilder();
// INSERT for Contact table
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_Contact( contact )
);
// INSERT for IsContactOf table
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_IsContactOf( contact )
);
// INSERT for SocialNetworkBasicInfo table
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_SocialNetworkBasicInfo( contact )
);
// INSERT for EMail and hasEmail tables
List<EMail> emails = contact.SocialNetworkBasicInfo.EMails;
if ( emails != null && emails.Count > 0 ) {
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_Email( contact )
);
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_hasEmail( contact )
);
}
// INSERT for Address and hasAddress tables
List<Address> addresses = contact.SocialNetworkBasicInfo.Addresses;
if ( addresses != null && addresses.Count > 0 ) {
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_Address( contact )
);
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_hasAddress( contact )
);
}
// INSERT for Language and speaksLanguage tables
List<SocialNetworkLanguage> languages = contact.SocialNetworkBasicInfo.Languages;
if ( languages != null && languages.Count > 0 ) {
/*
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_Language( contact )
);
*/
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_speaksLanguage( contact )
);
}
return sqlStatements.ToString();
}
/// VALUES ( ContactID, SocialNetworkID, WebSite )
private static String buildValuesForTable_SocialNetworkBasicInfo( Contact contact ) {
var valuesTemplate = "( {0}, N'{1}', N'{2}' )";
var values = "";
values = String.Format( valuesTemplate,
contact.Id,
contact.SocialNetworkBasicInfo.SocialNetworkID,
contact.SocialNetworkBasicInfo.WebSite
);
return values;
} // Method
/// VALUES ( ID, Firstname, Lastname, Society )
private static String buildValuesForTable_Contact( Contact contact ) {
var valuesTemplate = "( {0}, N'{1}', N'{2}', N'{3}' )";
var values = "";
values = String.Format( valuesTemplate,
contact.Id,
contact.Firstname.Replace( "'", "''" ), // Firstname and Lastname may contain apostrophes '
contact.Lastname.Replace( "'", "''" ), // They are replaced with double apostrophes '' before insertion in DB
contact.Society
);
return values;
}
} // Method
/// VALUES ( ContactID_A, ContactID_B )
private static String buildValuesForTable_IsContactOf( Contact contact_A, Contact contact_B ) {
var valuesTemplate = "( {0}, {1} )";
var values = "";
values = String.Format( valuesTemplate,
contact_A.Id,
contact_B.Id
);
return values;
}
} // Method
/// INSERT INTO mynet.speaksLanguage ( LanguageID, ContactSocialNetworkID, SocialNetworkLanguageID, SocialNetworkLanguageName )
private static String buildSqlInsertStatementForTable_speaksLanguage( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.speaksLanguage ( LanguageID, ContactSocialNetworkID, SocialNetworkLanguageID, SocialNetworkLanguageName ) " +
"VALUES {0}";
var valuesTemplate = "( {0}, N'{1}', {2}, N'{3}' )";
var values = new StringBuilder();
var comma = ",";
// Facebook languages spoken by Contact
List<SocialNetworkLanguage> contactLanguages = contact.SocialNetworkBasicInfo.Languages;
for ( var i = 0; i < contactLanguages.Count; i++ ) {
SocialNetworkLanguage socialNetworkLanguage = contactLanguages[i];
///
// Find the Facebook languages counterpart in MyNet
///
Language mynetLanguage = MyNetDbService.mynetLanguages.Find(
delegate( Language language ) {
return language.Name.CompareTo( socialNetworkLanguage.Name ) == 0;
}
);
// Language ID to be used
int mynetLanguageId;
if ( mynetLanguage != null )
mynetLanguageId = mynetLanguage.Id;
else
mynetLanguageId = 4;
if ( i == contactLanguages.Count - 1 ) {
values.AppendLine(
String.Format( valuesTemplate,
mynetLanguageId,
contact.SocialNetworkBasicInfo.SocialNetworkID,
socialNetworkLanguage.Id,
socialNetworkLanguage.Name
)
);
} else {
values.AppendLine(
String.Format( valuesTemplate,
mynetLanguageId,
contact.SocialNetworkBasicInfo.SocialNetworkID,
socialNetworkLanguage.Id,
socialNetworkLanguage.Name
) + comma
);
}
} // For
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Method
/// INSERT INTO mynet.hasAddress ( AddressID, SocialNetworkID )
private static String buildSqlInsertStatementForTable_hasAddress( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.hasAddress ( AddressID, SocialNetworkID ) " +
"VALUES {0}";
var valuesTemplate = "( {0}, N'{1}' )";
var values = new StringBuilder();
var comma = ",";
List<Address> addresses = contact.SocialNetworkBasicInfo.Addresses;
for ( var i = 0; i < addresses.Count; i++ ) {
Address address = addresses[i];
if ( i == addresses.Count - 1 ) {
values.AppendLine(
String.Format( valuesTemplate,
address.Id,
contact.SocialNetworkBasicInfo.SocialNetworkID
)
);
} else {
values.AppendLine(
String.Format( valuesTemplate,
address.Id,
contact.SocialNetworkBasicInfo.SocialNetworkID
) + comma
);
}
} // For
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Method
/// INSERT INTO mynet.Email ( Email, EmailType )
private static String buildSqlInsertStatementForTable_Email( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.Email ( EmailAddress, EmailType ) " +
"VALUES {0}";
var valuesTemplate = "( N'{0}', N'{1}' )";
var comma = ",";
var values = new StringBuilder();
List<EMail> emails = contact.SocialNetworkBasicInfo.EMails;
for ( var i = 0; i < emails.Count; i++ ) {
var email = emails[i];
if ( i == emails.Count - 1 )
values.AppendLine(
String.Format( valuesTemplate, email.Address, email.Type )
);
else
values.AppendLine(
String.Format( valuesTemplate, email.Address, email.Type ) + comma
);
} // For
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Method
/// INSERT INTO mynet.hasEmail ( SocialNetworkID, Email )
protected static String buildSqlInsertStatementForTable_hasEmail( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.hasEmail ( SocialNetworkID, EmailAddress ) " +
"VALUES {0}";
var valuesTemplate = "( N'{0}', N'{1}' )";
var comma = ",";
var values = new StringBuilder();
List<EMail> emails = contact.SocialNetworkBasicInfo.EMails;
String socialNetworkID = contact.SocialNetworkBasicInfo.SocialNetworkID;
for ( var i = 0; i < emails.Count; i++ ) {
if ( i == emails.Count - 1 ) {
values.AppendLine(
String.Format( valuesTemplate, socialNetworkID, emails[i].Address )
);
} else {
values.AppendLine(
String.Format( valuesTemplate, socialNetworkID, emails[i].Address ) + comma
);
}
} // For
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Method
/// INSERT INTO mynet.SocialNetworkBasicInfo ( ContactID, SocialNetworkID, WebSite )
private static String buildSqlInsertStatementForTable_SocialNetworkBasicInfo( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.SocialNetworkBasicInfo ( ContactID, SocialNetworkID, WebSite ) " +
"VALUES {0}";
var values = new StringBuilder();
var comma = ",";
// Contact info
values.Append(
buildValuesForTable_SocialNetworkBasicInfo( contact )
);
if( contact.Contacts != null ) {
List<Contact> contacts = contact.Contacts;
values.AppendLine( comma );
// Info about their friends
for ( int i = 0; i < contacts.Count; i++ ) {
if ( i == contacts.Count - 1 )
values.AppendLine(
buildValuesForTable_SocialNetworkBasicInfo( contacts[i] )
);
else
values.AppendLine(
buildValuesForTable_SocialNetworkBasicInfo( contacts[i] )
+ comma
);
} // For
} // If
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Method
/// INSERT INTO mynet.IsContactOf ( ContactID_A, ContactID_B )
private static String buildSqlInsertStatementForTable_IsContactOf( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.IsContactOf ( ContactID_A, ContactID_B ) " +
"VALUES {0}";
var values = new StringBuilder();
var comma = ",";
// Contact has a friend ?
if ( contact.Contacts != null ) {
var contacts = contact.Contacts;
var contactA = contact;
// Prepare values
for ( var i = 0; i < contacts.Count; i++ ) {
var contactB = contacts[i];
if ( i == contacts.Count - 1 )
values.AppendLine(
buildValuesForTable_IsContactOf( contactA, contactB )
);
else
values.AppendLine(
buildValuesForTable_IsContactOf( contactA, contactB ) + comma
);
} // For
} // If
// Insert statement
return String.Format( insertTemplate, values );
} // Method
/// INSERT INTO mynet.Contact ( ID, Firstname, Lastname, Society )
private static String buildSqlInsertStatementForTable_Contact( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.Contact ( ID, Firstname, Lastname, Society ) " +
"VALUES {0}";
var values = new StringBuilder();
var comma = ",";
// INSERT containing Contact information
values.Append(
buildValuesForTable_Contact( contact )
);
///
// Set of INSERTs containing Contact' Friends
///
// At least one friend ?
if ( contact.Contacts != null ) {
var contacts = contact.Contacts;
// Convert friends to SQL
// Recall: insert values are separated using commas
for ( var i = 0; i < contacts.Count; i++ ) {
if( i == 0 )
values.AppendLine( comma );
if ( i != contacts.Count - 1 )
values.AppendLine(
buildValuesForTable_Contact( contacts[i] ) + comma
);
else
values.AppendLine(
buildValuesForTable_Contact( contacts[i] )
);
} // For
} // If
// Insert statement
return String.Format( insertTemplate, values.ToString() );
} // Method
} // Method
/// Get Contact from DB. Used by getContact() method
protected Contact getContact( int contactID, SqlCommand sqlCommand ) {
var select = "SELECT contact.ID, contact.Firstname, contact.Lastname, contact.Society, basicInfo.WebSite, basicInfo.SocialNetworkID ";
var from = "FROM mynet.Contact contact, " +
"mynet.SocialNetworkBasicInfo basicInfo ";
var where = "WHERE contact.ID = {0} AND " +
"basicInfo.ContactID = {0}";
// Get Contact information (including its BasicInfo)
sqlCommand.CommandText = String.Format(
select + from + where,
contactID
);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
// Values for constructing the Contact object
int id = (int) reader["ID"];
String firstname = (String) reader["Firstname"];
String lastname = (String) reader["Lastname"];
String socialNetworkID = (String) reader["SocialNetworkID"];
String webSite = (String) reader["WebSite"];
String society = null;
// Note : Society may be NULL
if ( reader["Society"].GetType() != typeof( DBNull ) )
society = (String) reader["Society"];
reader.Close();
// Construction of the Contact object
var contact = new Contact() {
Id = id,
Firstname = firstname,
Lastname = lastname,
Society = society,
SocialNetworkBasicInfo = new SocialNetworkBasicInfo {
SocialNetworkID = socialNetworkID,
WebSite = webSite,
EMails = getContactEmails( socialNetworkID, sqlCommand ),
Languages = getContactLanguages( socialNetworkID, sqlCommand ),
Addresses = getContactAddresses( socialNetworkID, sqlCommand )
},
Contacts = getContactsOfAContact( id, sqlCommand )
};
return contact;
} // Method
} // Method
/// INSERT INTO mynet.Language ( LanguageID, LanguageName )
private static String buildSqlInsertStatementForTable_Language( Contact contact ) {
var insertTemplate = "INSERT INTO mynet.Language ( LanguageID, LanguageName ) " +
"VALUES {0}";
var valuesTemplate = "( {0}, N'{1}' )";
var values = new StringBuilder();
var comma = ",";
List<SocialNetworkLanguage> languages = contact.SocialNetworkBasicInfo.Languages;
for ( var i = 0; i < languages.Count; i++ ) {
SocialNetworkLanguage language = languages[i];
if ( i == languages.Count - 1 ) {
values.AppendLine(
String.Format( valuesTemplate,
language.Id,
language.Name
)
);
} else {
values.AppendLine(
String.Format( valuesTemplate,
language.Id,
language.Name
) + comma
);
}
} // For
// Insert statement
return String.Format( insertTemplate, values );
} // Method
} // Constructor
#region Service Methods
/// Get Contact from DB using the ContactID
public Contact getContact( int contactID ) {
var select = "SELECT contact.ID, contact.Firstname, contact.Lastname, contact.Society, basicInfo.WebSite, basicInfo.SocialNetworkID " + "\n";
var from = "FROM mynet.Contact contact, " + "\n" +
"mynet.SocialNetworkBasicInfo basicInfo " + "\n";
var where = "WHERE contact.ID = {0} AND " + "\n" +
"basicInfo.ContactID = {0}";
Contact contact;
// Connect to MyNetDB
using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {
dbConnection.Open ();
// Execute SQL statements
using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {
// Connect to Federation Member
sqlCommand.CommandText = "USE FEDERATION MyNetFederation(ContactLanguageID=0) WITH RESET, FILTERING=OFF";
sqlCommand.ExecuteNonQuery();
// Get Contact information (including its BasicInfo)
sqlCommand.CommandText = String.Format(
select + from + where,
contactID
);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
// Values for constructing the Contact object
int id = (int) reader["ID"];
String firstname = (String) reader["Firstname"];
String lastname = (String) reader["Lastname"];
String socialNetworkID = (String) reader["SocialNetworkID"];
String webSite = (String) reader["WebSite"];
String society = null;
// Note : Society may be NULL
if ( reader["Society"].GetType() != typeof( DBNull ) )
society = (String) reader["Society"];
reader.Close();
// Construction of the Contact object
contact = new Contact() {
Id = id,
Firstname = firstname,
Lastname = lastname,
Society = society,
SocialNetworkBasicInfo = new SocialNetworkBasicInfo {
SocialNetworkID = socialNetworkID,
WebSite = webSite,
EMails = getContactEmails( socialNetworkID, sqlCommand ),
Languages = getContactLanguages( socialNetworkID, sqlCommand ),
Addresses = getContactAddresses( socialNetworkID, sqlCommand )
},
Contacts = getContactsOfAContact( id, sqlCommand )
};
} // SqlCommand
dbConnection.Close();
} // dbConnection
return contact;
} // Method
///
public static String BasicContactToSqL( Contact contact ) {
var sqlStatements = new StringBuilder();
// INSERT for Contact table
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_Contact( contact )
);
// INSERT for IsContactOf table
sqlStatements.AppendLine(
buildSqlInsertStatementForTable_IsContactOf( contact )
);
return sqlStatements.ToString();
}
