public CrmConnectionInfo GetConnectionInfo(CrmDbConnection connection)
{
var connInfo = _Cache.GetOrAdd(connection.ConnectionString, f =>
{
WhoAmIRequest whoRequest = new WhoAmIRequest();
WhoAmIResponse whoResponse = (WhoAmIResponse)connection.OrganizationService.Execute(whoRequest);
var info = new CrmConnectionInfo();
if (whoResponse != null)
{
info.OrganisationId = whoResponse.OrganizationId;
info.UserId = whoResponse.UserId;
info.BusinessUnitId = whoResponse.BusinessUnitId;
}
var orgEntity = connection.OrganizationService.Retrieve("organization", info.OrganisationId, new ColumnSet("name"));
if (orgEntity != null)
{
info.OrganisationName = (string)orgEntity["name"];
}
var versionReq = new RetrieveVersionRequest();
var versionResponse = (RetrieveVersionResponse)connection.OrganizationService.Execute(versionReq);
if (versionResponse != null)
{
info.ServerVersion = versionResponse.Version;
}
return info;
});
return connInfo;
}
public void Experiment_For_Contains_And_Not_Contains()
{
var sql = string.Format("Select contactid, firstname, lastname From contact Where firstname Like '%ax%' ");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
Console.WriteLine(string.Format("{0} {1} {2}", contactId, firstName, lastName));
}
//while (reader.Read())
//{
//}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection must not be null");
if (storeItemCollection == null)
throw new ArgumentNullException("storeItemCollection must not be null");
CrmDbConnection crmConnection = connection as CrmDbConnection;
if (crmConnection == null)
throw new ArgumentException("connection must be a valid SampleConnection");
string databaseName = GetDatabaseName(crmConnection);
// bool exists = false;
throw new NotImplementedException();
//UsingMasterConnection(crmConnection, conn =>
//{
// throw new NotImplementedException();
//StoreVersion storeVersion = StoreVersionUtils.GetStoreVersion(conn);
//string databaseExistsScript = DdlBuilder.CreateDatabaseExistsScript(databaseName);
//int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
//exists = (result == 1);
// });
// return exists;
}
public void Experiment_For_Contains_And_Not_Contains()
{
var sql = string.Format("Select contactid, firstname, lastname From contact Where firstname Like '%ax%' ");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
Console.WriteLine(string.Format("{0} {1} {2}", contactId, firstName, lastName));
}
//while (reader.Read())
//{
//}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
protected T GetOrganizationRequest <T>(CrmDbConnection connection, string sql) where T : OrganizationRequest
{
var cmd = new CrmDbCommand(connection);
cmd.CommandText = sql;
return(GetOrganizationRequest <T>(cmd));
}
// private CrmDbConnection _MockConnection = null;
protected QueryExpression GetQueryExpression(CrmDbConnection connection, string sql)
{
var cmd = new CrmDbCommand(connection);
cmd.CommandText = sql;
return(GetQueryExpression(cmd));
}
protected ICrmOperation GetOperation(CrmDbConnection connection, string sql)
{
var cmd = new CrmDbCommand(connection);
cmd.CommandText = sql;
return(GetOperation(cmd));
}
/// <summary>
/// Executes the SQL using ExecuteReader and makes an assertion against the number of rows returned.
/// </summary>
/// <param name="sql"></param>
/// <param name="assertResultCount"></param>
protected virtual void ExecuteReader(string sql, int assertResultCount)
{
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing sql command: " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
// var contactId = (Guid)reader["contactid"];
// Console.WriteLine(string.Format("{0}", contactId));
}
Assert.That(resultCount, Is.EqualTo(assertResultCount));
}
}
}
public void Should_Be_Able_To_Select_From_Table_Prefixed_With_Dbo_Schema(String schemaPrefix)
{
var sql = string.Format("Select C.contactid, C.firstname, C.lastname From {0}.contact C", schemaPrefix);
var cmd = new CrmDbCommand(null);
cmd.CommandText = sql;
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["C.contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
Console.WriteLine(string.Format("{0} {1} {2}", contactId, firstName, lastName));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
/// <summary>
/// Creates a new entity for testing with in Crm and returns its name.
/// </summary>
/// <param name="sqlFormatString"></param>
protected virtual string CreateTestEntity()
{
// create a random name for the entity. We use half a guid because names cant be too long.
string randomEntityName = "createtest" + Guid.NewGuid().ToString().Replace("-", "").Remove(0, 16);
string createEntitySqlFormatString = "CREATE TABLE {0}{1}({0}{1}id UNIQUEIDENTIFIER PRIMARY KEY, {0}{1}name NVARCHAR(125))";
var sql = string.Format(createEntitySqlFormatString, DefaultPublisherPrefix, randomEntityName);
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
ExecuteCrmNonQuery(sql, -1);
}
string entityName = string.Format("{0}{1}", DefaultPublisherPrefix, randomEntityName);
return(entityName);
}
public void Should_Be_Able_To_Select_One_To_Many_Relationship_Metadata_For_Entity()
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
//string lookupToEntity = "contact";
var sql = "SELECT o.* FROM entitymetadata e INNER JOIN onetomanyrelationshipmetadata o ON e.MetadataId = o.MetadataId WHERE e.LogicalName = 'account'";
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
var results = command.ExecuteReader();
while (results.Read())
{
for (int i = 0; i < results.FieldCount; i++)
{
Console.Write(results[i]);
Console.Write(',');
}
Console.WriteLine("");
}
}
}
public void Should_Be_Able_To_Insert_A_Contact(string insertSql)
{
var sql = string.Format(insertSql, "Derren", "Brown");
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
Console.WriteLine(string.Format("{0}", contactId));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
private static void UsingConnection(CrmDbConnection connection, Action<CrmDbConnection> act)
{
// remember the connection string so that we can reset it if credentials are wiped
string holdConnectionString = connection.ConnectionString;
bool openingConnection = connection.State == ConnectionState.Closed;
if (openingConnection)
{
connection.Open();
}
try
{
act(connection);
}
finally
{
if (openingConnection && connection.State == ConnectionState.Open)
{
// if we opened the connection, we should close it
connection.Close();
}
if (connection.ConnectionString != holdConnectionString)
{
connection.ConnectionString = holdConnectionString;
}
}
}
public void Should_Be_Able_To_Select_Entity_Logical_Names()
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
//string lookupToEntity = "contact";
var sql = "SELECT LogicalName FROM EntityMetadata";
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
var results = command.ExecuteReader();
while (results.Read())
{
Console.WriteLine("LogicalName: " + results[0]);
}
}
}
protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection must not be null");
if (storeItemCollection == null)
throw new ArgumentNullException("storeItemCollection must not be null");
CrmDbConnection sampleConnection = connection as CrmDbConnection;
if (sampleConnection == null)
throw new ArgumentException("connection must be a valid SampleConnection");
throw new NotImplementedException();
//string databaseName = GetDatabaseName(sampleConnection);
//string dropDatabaseScript = DdlBuilder.DropDatabaseScript(databaseName);
//// clear the connection pool in case someone is holding on to the database
//sampleConnection.ClearPool();
//UsingMasterConnection(sampleConnection, (conn) =>
//{
// CreateCommand(conn, dropDatabaseScript, commandTimeout).ExecuteNonQuery();
//});
}
public void Should_Be_Able_To_Select_Attribute_Metadata_For_Entity()
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
//string lookupToEntity = "contact";
var sql = "SELECT a.LogicalName FROM EntityMetadata AS e INNER JOIN AttributeMetadata a ON e.MetadataId = a.MetadataId WHERE e.LogicalName = 'contact'";
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
var results = command.ExecuteReader();
while (results.Read())
{
for (int i = 0; i < results.FieldCount; i++)
{
Console.Write(results[i]);
Console.Write(',');
}
Console.WriteLine("");
}
}
}
public void Should_Be_Able_To_Insert_Many_Contacts()
{
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
// create 10000 contacts in dynamics..
Console.WriteLine("starting insert..");
var watch = new Stopwatch();
watch.Start();
for (int i = 0; i < 1000; i++)
{
Guid testdata = Guid.NewGuid();
var sql = string.Format("INSERT INTO contact (contactid, firstname, lastname) Values('{0}','{1}','{2}')", testdata, "Derren" + testdata.ToString(), "Brown");
var command = conn.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteNonQuery();
Assert.That(result, Is.EqualTo(1));
}
watch.Stop();
Console.WriteLine("Inserting 1000 contacts took " + watch.Elapsed.ToString());
}
}
public CrmDataAdapter(string selectCommandText, CrmDbConnection connection)
: base()
{
// var dbprovider = DbProviderFactories.GetFactory(CrmAdoConstants.Invariant);
this.SelectCommand = new CrmDbCommand(connection, selectCommandText);
this.SelectCommand.CommandText = selectCommandText;
this.SelectCommand.Connection = connection;
}
private static string GetDatabaseName(CrmDbConnection sampleConnection)
{
string databaseName = sampleConnection.Database;
if (string.IsNullOrEmpty(databaseName))
throw new InvalidOperationException("Connection String did not specify an Initial Catalog");
return databaseName;
}
public void Should_Get_Results_When_Querying_Crm_Using_Filter_Operators(string filterOperator, object value, string filterFormatString)
{
// Arrange
// Formulate DML (SQL) statement from test case data.
var columnName = "firstname";
if (value == null || !value.GetType().IsArray)
{
filterFormatString = string.Format(filterFormatString, columnName, filterOperator, value);
}
else
{
var formatArgs = new List <object>();
formatArgs.Add(columnName);
formatArgs.Add(filterOperator);
var args = value as IEnumerable;
foreach (var arg in args)
{
formatArgs.Add(arg);
}
filterFormatString = string.Format(filterFormatString, formatArgs.ToArray());
}
var sql = string.Format("Select contactid, firstname, lastname From contact Where {0} ", filterFormatString);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
Console.WriteLine(string.Format("{0} {1} {2}", contactId, firstName, lastName));
}
//while (reader.Read())
//{
//}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
public void Should_Be_Able_To_Query_Using_Filter_Groups(String joinType, string whereClause, int expectedResults)
{
// var join = JoinOperator.Natural;
//switch (joinType)
//{
// case "INNER":
// join = JoinOperator.Inner;
// // Enum.Parse(typeof(JoinOperator), joinType)
// break;
// case "LEFT":
// join = JoinOperator.LeftOuter;
// break;
//}
var sql = string.Format("Select C.contactid, C.firstname, C.lastname, A.line1, A.customeraddressid From contact C {0} JOIN customeraddress A on C.contactid = A.parentid WHERE {1}", joinType, whereClause);
var cmd = new CrmDbCommand(null);
cmd.CommandText = sql;
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["C.contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
var line1 = (string)reader.SafeGetString(3);
var alsoLine1 = (string)reader.SafeGetString("A.line1");
var customerAddressId = (Guid)reader["A.customeraddressid"];
Console.WriteLine(string.Format("{0} {1} {2} {3} {4}", contactId, firstName, lastName, line1, customerAddressId));
}
Console.WriteLine("There were " + resultCount + " results..");
Assert.That(resultCount, Is.EqualTo(expectedResults));
}
}
}
protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection must not be null");
if (storeItemCollection == null)
throw new ArgumentNullException("storeItemCollection must not be null");
CrmDbConnection sampleConnection = connection as CrmDbConnection;
if (sampleConnection == null)
{
throw new ArgumentException("The connection is not of type 'SampleConnection'.");
}
string databaseName = GetDatabaseName(sampleConnection);
if (string.IsNullOrEmpty(databaseName))
{
throw new InvalidOperationException("Initial Catalog is missing from the connection string");
}
//TODO: Need to create CRM organisation, and then create all necessary objects.
// Need to skip over system entities... only creating custom attributes and custom entities.
// Could put entire EF schema into a single managed crm solution and do it that way??
throw new NotImplementedException();
//string dataFileName, logFileName;
//GetDatabaseFileNames(sampleConnection, out dataFileName, out logFileName);
//string createDatabaseScript = DdlBuilder.CreateDatabaseScript(databaseName, dataFileName, logFileName);
//string createObjectsScript = DdlBuilder.CreateObjectsScript(storeItemCollection);
//UsingMasterConnection(sampleConnection, conn =>
//{
// // create database
// CreateCommand(conn, createDatabaseScript, commandTimeout).ExecuteNonQuery();
//});
//// Clear connection pool for the database connection since after the 'create database' call, a previously
//// invalid connection may now be valid.
//sampleConnection.ClearPool();
//UsingConnection(sampleConnection, conn =>
//{
// // create database objects
// CreateCommand(conn, createObjectsScript, commandTimeout).ExecuteNonQuery();
//});
}
public void Experiment_For_Creating_Publisher()
{
////Define a new publisher
//Publisher _crmSdkPublisher = new Publisher
//{
// UniqueName = "sdksamples",
// FriendlyName = "Microsoft CRM SDK Samples",
// SupportingWebsiteUrl = "http://msdn.microsoft.com/en-us/dynamics/crm/default.aspx",
// CustomizationPrefix = "sample",
// EMailAddress = "*****@*****.**",
// Description = "This publisher was created with samples from the Microsoft Dynamics CRM SDK"
//};
// var sql = string.Format("Select C.firstname, C.lastname From contact Where firstname Like '%ax%' ");
var sqlFormatString = @"INSERT INTO Publisher (UniqueName,FriendlyName,SupportingWebsiteUrl,CustomizationPrefix,EMailAddress,Description) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}');";
var sql = string.Format(sqlFormatString, "CrmAdo", "Crm Ado", @"http://dazinator.github.io/CrmAdo/", "crmado", "*****@*****.**", "crm ado publisher");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var publisherid = (Guid)reader["publisherid"];
// var versionNumber = (long)reader[3];
Console.WriteLine(string.Format("{0}", publisherid));
}
//while (reader.Read())
//{
//}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
public void Should_Be_Able_To_Insert_A_Contact_With_OutputClause(string insertSql, string name)
{
// var sql = string.Format(insertSql, "Derren", "Brown");
Guid id = Guid.NewGuid();
Console.WriteLine(insertSql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + insertSql);
command.CommandText = insertSql;
var param = command.CreateParameter();
param.ParameterName = "@0";
param.DbType = System.Data.DbType.String;
param.Direction = System.Data.ParameterDirection.Input;
param.Size = 40;
param.Value = id;
command.Parameters.Add(param);
var param2 = command.CreateParameter();
param2.ParameterName = "@1";
param2.DbType = System.Data.DbType.String;
param2.Direction = System.Data.ParameterDirection.Input;
param2.Size = 4000;
param2.Value = id;
command.Parameters.Add(param2);
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var createdOn = (DateTime)reader["createdon"];
Console.WriteLine(string.Format("{0}", createdOn));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
/// <summary>
/// Creates a publisher in Crm if it doesn't allready exist.
/// </summary>
/// <param name="name"></param>
/// <param name="prefix"></param>
protected virtual void CreatePublisher(string name, string prefix)
{
var sqlFormatString = @"INSERT INTO Publisher (UniqueName,FriendlyName,SupportingWebsiteUrl,CustomizationPrefix,EMailAddress,Description) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}');";
var sql = string.Format(sqlFormatString, name, name, @"http://dazinator.github.io/CrmAdo/", prefix, "*****@*****.**", "crmado int test publisher");
var existsSqlFormatString = "SELECT PublisherId FROM Publisher WHERE UniqueName = '{0}'";
var existsSql = string.Format(existsSqlFormatString, name);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var existingPublisherCommand = conn.CreateCommand();
existingPublisherCommand.CommandText = existsSql;
using (var publisherReader = existingPublisherCommand.ExecuteReader())
{
if (publisherReader.HasRows)
{
Console.WriteLine("Publisher with unique name: " + name + " exists.");
publisherReader.Read();
PublisherId = (Guid)publisherReader["publisherid"];
return;
}
}
// Create publiseher.
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
PublisherId = (Guid)reader["publisherid"];
Console.WriteLine(string.Format("Publisher created, id: {0}", PublisherId));
}
}
}
}
public CrmDbCommand(CrmDbConnection connection, IOperationExecutor crmCommandExecutor, ICrmOperationProvider organisationCommandProvider)
{
if (crmCommandExecutor == null)
{
throw new ArgumentNullException("crmCommandExecutor");
}
if (organisationCommandProvider == null)
{
throw new ArgumentNullException("organisationCommandProvider");
}
_DbConnection = connection;
_CrmCommandExecutor = crmCommandExecutor;
_OrganisationCommandProvider = organisationCommandProvider;
_CommandType = CommandType.Text;
_ParameterCollection = new CrmParameterCollection();
}
/// <summary>
/// Executes the Sql using ExecuteNonQuery, and makes an assertion against the reported number of rows effected.
/// </summary>
/// <param name="sql"></param>
/// <param name="assertResult"></param>
protected virtual void ExecuteCrmNonQuery(string sql, int assertResult)
{
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing sql command: " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
var result = command.ExecuteNonQuery();
Assert.That(result, Is.EqualTo(assertResult));
}
}
/// <summary>
/// Creates a publisher in Crm if it doesn't allready exist.
/// </summary>
/// <param name="name"></param>
/// <param name="prefix"></param>
protected virtual void CreatePublisher(string name, string prefix)
{
var sqlFormatString = @"INSERT INTO Publisher (UniqueName,FriendlyName,SupportingWebsiteUrl,CustomizationPrefix,EMailAddress,Description) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}');";
var sql = string.Format(sqlFormatString, name, name, @"http://dazinator.github.io/CrmAdo/", prefix, "*****@*****.**", "crmado int test publisher");
var existsSqlFormatString = "SELECT PublisherId FROM Publisher WHERE UniqueName = '{0}'";
var existsSql = string.Format(existsSqlFormatString, name);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var existingPublisherCommand = conn.CreateCommand();
existingPublisherCommand.CommandText = existsSql;
using (var publisherReader = existingPublisherCommand.ExecuteReader())
{
if (publisherReader.HasRows)
{
Console.WriteLine("Publisher with unique name: " + name + " exists.");
publisherReader.Read();
PublisherId = (Guid)publisherReader["publisherid"];
return;
}
}
// Create publiseher.
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
PublisherId = (Guid)reader["publisherid"];
Console.WriteLine(string.Format("Publisher created, id: {0}", PublisherId));
}
}
}
}
public void Can_Select_Large_Number_Of_Contacts(int total)
{
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
Console.WriteLine("selecting " + total + " contacts..");
var watch = new Stopwatch();
watch.Start();
string sql;
if (total != -1)
{
sql = string.Format("select top " + total + " contactid, firstname, lastname from contact");
}
else
{
sql = string.Format("select contactid, firstname, lastname from contact");
}
var command = conn.CreateCommand();
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
Console.WriteLine(contactId);
}
Console.WriteLine("There were " + resultCount + " results..");
if (total != -1)
{
Assert.That(resultCount, Is.Not.GreaterThan(total));
}
watch.Stop();
Console.WriteLine("selecting " + resultCount + " contacts took " + watch.Elapsed.ToString());
}
}
}
public void Can_Select_Large_Number_Of_Contacts(int total)
{
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
Console.WriteLine("selecting " + total + " contacts..");
var watch = new Stopwatch();
watch.Start();
string sql;
if (total != -1)
{
sql = string.Format("select top " + total + " contactid, firstname, lastname from contact");
}
else
{
sql = string.Format("select contactid, firstname, lastname from contact");
}
var command = conn.CreateCommand();
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
Console.WriteLine(contactId);
}
Console.WriteLine("There were " + resultCount + " results..");
if (total != -1)
{
Assert.That(resultCount, Is.Not.GreaterThan(total));
}
watch.Stop();
Console.WriteLine("selecting " + resultCount + " contacts took " + watch.Elapsed.ToString());
}
}
}
public void Should_Be_Able_To_Select_Specific_Attribute_Metadata_Columns_For_Entity()
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
//string lookupToEntity = "contact";
var sql = "SELECT attributemetadata.MetadataId, attributemetadata.EntityLogicalName, attributemetadata.LogicalName, attributemetadata.ColumnNumber FROM entitymetadata INNER JOIN attributemetadata ON entitymetadata.MetadataId = attributemetadata.MetadataId WHERE entitymetadata.LogicalName = '{0}'";
sql = string.Format(sql, "contact");
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
var results = command.ExecuteReader();
while (results.Read())
{
for (int i = 0; i < results.FieldCount; i++)
{
Console.Write("field " + i.ToString() + ": ");
var value = results[i];
if (value == null)
{
Console.Write("NULL");
}
else
{
Console.Write(value);
}
Console.Write(',');
}
Console.WriteLine("");
}
}
}
public void Should_Be_Able_To_Create_A_New_Entity(string sqlFormatString)
{
// create a random name for the entity. We use half a guid because names cant be too long.
string randomEntityName = "createtest" + Guid.NewGuid().ToString().Replace("-", "").Remove(0, 16);
var sql = string.Format(sqlFormatString, DefaultPublisherPrefix, randomEntityName);
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
base.ExecuteCrmNonQuery(sql, -1);
}
}
public void Should_Be_Able_To_Add_A_New_Column(string sqlFormatString)
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
string lookupToEntity = "contact";
var sql = string.Format(sqlFormatString, TestEntityName, DefaultPublisherPrefix, lookupToEntity);
// Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
base.ExecuteCrmNonQuery(sql, -1);
}
}
public void Should_Be_Able_To_Add_A_New_Column(string sqlFormatString)
{
// create a random name for the entity. We use half a guid because names cant be too long.
// string attributeSchemaName = "boolField";
string lookupToEntity = "contact";
var sql = string.Format(sqlFormatString, TestEntityName, DefaultPublisherPrefix, lookupToEntity);
// Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
// Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
base.ExecuteCrmNonQuery(sql, -1);
}
}
private static DbCommand CreateCommand(CrmDbConnection connection, string commandText, int? commandTimeout)
{
Debug.Assert(connection != null);
//if (string.IsNullOrEmpty(commandText))
//{
// // SqlCommand will complain if the command text is empty
// //commandText = Environment.NewLine;
//}
// throw new NotImplementedException();
var command = connection.CreateCommand();
command.CommandText = commandText;
// var command = new CrmDbConnection(commandText, connection);
if (commandTimeout.HasValue)
{
command.CommandTimeout = commandTimeout.Value;
}
return command;
}
public void Should_Be_Able_To_Create_A_New_Entity(string sqlFormatString)
{
// create a random name for the entity. We use half a guid because names cant be too long.
string randomEntityName = "createtest" + Guid.NewGuid().ToString().Replace("-", "").Remove(0, 16);
var sql = string.Format(sqlFormatString, DefaultPublisherPrefix, randomEntityName);
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
base.ExecuteCrmNonQuery(sql, -1);
}
}
public void Experiment_For_Selecting_Plugins()
{
var sql = string.Format("Select * from pluginassembly");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetName(i));
Console.Write("=");
Console.Write(reader[i]);
Console.WriteLine();
}
Console.WriteLine("*****");
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
/// <summary>
/// Deetes a publisher from Crm.
/// </summary>
/// <param name="publisherId"></param>
protected virtual void DeletedPublisher(Guid publisherId)
{
var deleteSqlFormatString = "DELETE FROM Publisher WHERE PublisherId = '{0}'";
var deleteSql = string.Format(deleteSqlFormatString, publisherId);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
// Create publiseher.
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + deleteSql);
command.CommandText = deleteSql;
var result = command.ExecuteNonQuery();
if (result != -1)
{
Console.WriteLine("Deleted publisher id: " + publisherId);
}
}
}
public void Experiment_For_Selecting_Version_Number_Greater_Than()
{
// var sql = string.Format("Select C.firstname, C.lastname From contact Where firstname Like '%ax%' ");
var sql = string.Format("SELECT TOP 10 contactid, firstname, lastname, versionnumber FROM contact WHERE versionnumber NOT NULL ORDER BY versionnumber DESC");
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
var versionNumber = (long)reader[3];
Console.WriteLine(string.Format("{0} {1} {2} {3}", contactId, firstName, lastName, versionNumber.ToString()));
}
//while (reader.Read())
//{
//}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
public void Should_Be_Able_To_Create_A_New_Entity_And_Then_Alter_It_In_Single_Batch()
{
// create a random name for the entity. We use half a guid because names cant be too long.
string createEntitySqlFormatString = "CREATE TABLE {0}{1} ({0}{1}id UNIQUEIDENTIFIER PRIMARY KEY, {0}{1}name NVARCHAR(125));";
string randomEntityName = "createtest" + Guid.NewGuid().ToString().Replace("-", "").Remove(0, 16);
var sql = string.Format(createEntitySqlFormatString, DefaultPublisherPrefix, randomEntityName);
string alterEntitySql = string.Format("ALTER TABLE {0}{1} ADD {0}MyBool BIT", DefaultPublisherPrefix, randomEntityName);
sql = sql + alterEntitySql;
Console.WriteLine(sql);
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
var reader = command.ExecuteReader();
Assert.That(reader.NextResult());
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader[i]);
}
}
}
}
public DataTable GetViewColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
DataTable dataTable = new DataTable("ViewColumns");
dataTable.Locale = CultureInfo.InvariantCulture;
dataTable.Columns.AddRange(
new DataColumn[]
{
new DataColumn("VIEW_CATALOG"),
new DataColumn("VIEW_SCHEMA"),
new DataColumn("VIEW_NAME"),
new DataColumn("TABLE_CATALOG"),
new DataColumn("TABLE_SCHEMA"),
new DataColumn("TABLE_NAME"),
new DataColumn("COLUMN_NAME")
});
return dataTable;
}
public DataTable GetUsers(CrmDbConnection crmDbConnection, string[] restrictions)
{
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = "SELECT su.systemuserid, su.fullname, su.domainname, su.createdon, su.modifiedon FROM systemuser su";
if (restrictions != null && restrictions.Any())
{
string userName = restrictions[0];
command.CommandText = string.Format("{0} WHERE su.fullname = {1}", command.CommandText, userName);
}
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
return dataTable;
}
public DataTable GetUniqueKeys(CrmDbConnection crmDbConnection, string[] restrictions)
{
// throw new NotImplementedException();
string entityName = GetRestrictionOrNull(0, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(entityName);
string constraintName = GetRestrictionOrNull(1, restrictions);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT * FROM entitymetadata";
if (hasEntityFilter || hasConstraintNameFilter)
{
commandText += " WHERE";
if (hasEntityFilter)
{
commandText += " (LogicalName = '" + entityName + "')";
}
//if (hasEntityFilter && hasConstraintNameFilter)
//{
// commandText += " AND ";
//}
//if (hasConstraintNameFilter)
//{
// commandText += " (SchemaName = '" + constraintName + "')";
//}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// dataTable.AsDataView().RowFilter = "ReferencingEntity = '" + entityName +
dataTable.Columns.Add("CONSTRAINT_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("CONSTRAINT_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "'PK__' + LogicalName + '_' + IsNull(PrimaryIdAttribute, LogicalName + 'id')").SetOrdinal(2);
// dataTable.Columns["SchemaName"].ColumnName = "CONSTRAINT_NAME";
// dataTable.Columns["CONSTRAINT_NAME"].SetOrdinal(2);
// dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "''");
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns.Add("TABLE_NAME", typeof(string), "LogicalName").SetOrdinal(5);
// dataTable.Columns["LogicalName"].ColumnName = "TABLE_NAME";
// dataTable.Columns["TABLE_NAME"].SetOrdinal(5);
dataTable.Columns.Add("CONSTRAINT_TYPE", typeof(string), "'PRIMARY KEY'").SetOrdinal(6);
dataTable.Columns.Add("IS_DEFERRABLE", typeof(string), "'NO'").SetOrdinal(7);
dataTable.Columns.Add("INITIALLY_DEFERRED", typeof(string), "'NO'").SetOrdinal(8);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "CONSTRAINT_NAME = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
//else
//{
// dataTable = filteredView.ToTable(true);
//}
}
public void Should_Be_Able_To_Get_Indexes_For_A_Table_And_ConstraintName(string tableName, string constraintName)
{
// Arrange
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var restrictions = new string[] { null, null, tableName, constraintName };
// Act
var collection = sut.GetIndexes(conn, restrictions);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Rows.Count, Is.EqualTo(1));
foreach (DataRow row in collection.Rows)
{
//<constraint_catalog>PortalDarrellDev</constraint_catalog>
//<constraint_schema>dbo</constraint_schema>
//<constraint_name>PK__Table__3214EC07326C5B6A</constraint_name>
//<table_catalog>PortalDarrellDev</table_catalog>
//<table_schema>dbo</table_schema>
//<table_name>Table</table_name>
//<index_name>PK__Table__3214EC07326C5B6A</index_name>
//<type_desc>CLUSTERED</type_desc>
var val = AssertColVal(collection, row, "constraint_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "constraint_schema");
Assert.That(val, Is.EqualTo("dbo"));
var connName = AssertColVal(collection, row, "constraint_name");
//Assert.IsFalse(string.IsNullOrEmpty((string)connName));
Assert.That((string)connName, Is.EqualTo(constraintName));
Console.WriteLine(constraintName);
val = AssertColVal(collection, row, "table_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "table_schema");
Assert.That(val, Is.EqualTo("dbo"));
val = AssertColVal(collection, row, "table_name");
Assert.That(val, Is.EqualTo(tableName));
Console.Write(" - ");
Console.Write(val);
val = AssertColVal(collection, row, "index_name");
Assert.That(val, Is.EqualTo(constraintName));
val = AssertColVal(collection, row, "type_desc");
Assert.That(val, Is.EqualTo("CLUSTERED"));
}
}
}
public void Should_Be_Able_To_Get_IndexColumns_For_Table_And_ColumnName(string tableName, string columnName)
{
// Arrange
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var restrictions = new string[] { null, null, tableName, null, columnName };
// Act
var collection = sut.GetIndexColumns(conn, restrictions);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Rows.Count, Is.GreaterThan(0));
foreach (DataRow row in collection.Rows)
{
// <IndexColumns>
// <constraint_catalog>PortalDarrellDev</constraint_catalog>
// <constraint_schema>dbo</constraint_schema>
// <constraint_name>PK__tmp_ms_x__3214EC0737311087</constraint_name>
// <table_catalog>PortalDarrellDev</table_catalog>
// <table_schema>dbo</table_schema>
// <table_name>Table</table_name>
// <column_name>Id</column_name>
// <ordinal_position>1</ordinal_position>
// <KeyType>36</KeyType>
// <index_name>PK__tmp_ms_x__3214EC0737311087</index_name>
//</IndexColumns>
var val = AssertColVal(collection, row, "constraint_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "constraint_schema");
Assert.That(val, Is.EqualTo("dbo"));
var constraintName = AssertColVal(collection, row, "constraint_name");
Assert.IsFalse(string.IsNullOrEmpty((string)constraintName));
Assert.That((string)constraintName, Is.StringStarting("PK__"));
Console.WriteLine(constraintName);
val = AssertColVal(collection, row, "table_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "table_schema");
Assert.That(val, Is.EqualTo("dbo"));
val = AssertColVal(collection, row, "table_name");
Assert.That(val, Is.EqualTo(tableName));
Console.WriteLine(val);
val = AssertColVal(collection, row, "column_name");
Assert.That(val, Is.EqualTo(columnName));
val = AssertColVal(collection, row, "ordinal_position");
Assert.That(val, Is.Not.EqualTo(default(int)));
val = AssertColVal(collection, row, "KeyType");
Assert.That(val, Is.EqualTo(36)); // unique identifier.
val = AssertColVal(collection, row, "index_name");
Assert.That(val, Is.EqualTo(constraintName));
}
}
}
public DataTable GetTables(CrmDbConnection crmDbConnection, string[] restrictions)
{
// table_catalog
//table_schema
//table_name
//table_type
//Type of table. Can be VIEW or BASE TABLE.
var command = new CrmDbCommand(crmDbConnection);
string commandText = "SELECT * FROM EntityMetadata";
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string tableName = GetRestrictionOrNull(2, restrictions);
string tableType = GetRestrictionOrNull(3, restrictions); // doesn't matter currently what tabletype restriction is specified, we only return "base tables" not views.
if (catalog != null && catalog.ToLowerInvariant() != crmDbConnection.ConnectionInfo.OrganisationName.ToLowerInvariant())
{
// we only support the catalog currently connected to, can't query accross other catalogs.
throw new ArgumentException("invalid catalog restriction. no such catalog.");
}
if (schema != null && schema.ToLowerInvariant() != DefaultSchema.ToLowerInvariant())
{
// we only support a single schema "dbo".
throw new ArgumentException("invalid schema restriction. no such schema.");
}
if (!string.IsNullOrEmpty(tableName))
{
commandText = commandText + " WHERE LogicalName = '" + tableName + "'";
}
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["logicalname"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(2);
dataTable.Columns.Add("TABLE_TYPE", typeof(string), "'BASE TABLE'").SetOrdinal(3);
return dataTable;
}
public DataTable GetColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
//TABLE_CATALOG
//TABLE_SCHEMA
//TABLE_NAME
//COLUMN_NAME
//ORDINAL_POSITION
//COLUMN_DEFAULT
//IS_NULLABLE
//DATA_TYPE
//CHARACTER_MAXIMUM_LENGTH
//CHARACTER_OCTET_LENGTH
//NUMERIC_PRECISION
//NUMERIC_PRECISION_RADIX
//NUMERIC_SCALE
//DATETIME_PRECISION
//CHARACTER_SET_CATALOG
//CHARACTER_SET_SCHEMA
//CHARACTER_SET_NAME
//COLLATION_CATALOG
//IS_SPARSE
//IS_COLUMN_SET
//IS_FILESTREAM
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string entityName = GetRestrictionOrNull(2, restrictions);
string attributeName = GetRestrictionOrNull(3, restrictions);
bool hasEntityFilter = false;
bool hasAttributeFilter = false;
if (catalog != null && catalog.ToLowerInvariant() != crmDbConnection.ConnectionInfo.OrganisationName.ToLowerInvariant())
{
// we only support the catalog currently connected to, can't query accross other catalogs.
throw new ArgumentException("invalid catalog restriction. no such catalog.");
}
if (schema != null && schema.ToLowerInvariant() != DefaultSchema.ToLowerInvariant())
{
// we only support a single schema "dbo".
throw new ArgumentException("invalid schema restriction. no such schema.");
}
hasEntityFilter = !string.IsNullOrEmpty(entityName);
hasAttributeFilter = !string.IsNullOrEmpty(attributeName);
var commandText = "SELECT entitymetadata.PrimaryIdAttribute, attributemetadata.* FROM entitymetadata INNER JOIN attributemetadata ON entitymetadata.MetadataId = attributemetadata.MetadataId ";
if (hasEntityFilter || hasAttributeFilter)
{
commandText += "WHERE ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + entityName + "')";
}
if (hasEntityFilter && hasAttributeFilter)
{
commandText += " AND ";
}
if (hasAttributeFilter)
{
commandText += " (attributemetadata.LogicalName = '" + attributeName + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["entitylogicalname"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(2);
dataTable.Columns["logicalname"].ColumnName = "COLUMN_NAME";
dataTable.Columns["COLUMN_NAME"].SetOrdinal(3);
dataTable.Columns["columnnumber"].ColumnName = "ORDINAL_POSITION";
dataTable.Columns["ORDINAL_POSITION"].SetOrdinal(4);
// dataTable.Columns["defaultvalue"].ColumnName = "COLUMN_DEFAULT";
dataTable.Columns.Add("COLUMN_DEFAULT", dataTable.Columns["defaultvalue"].DataType, "IIF([defaultvalue] = '-1', '', IIF([defaultvalue] = 'TRUE', '((' + 1 + '))', IIF([defaultvalue] = 'FALSE', '((0))', '((' + [defaultvalue] + '))')))").SetOrdinal(5);
// dataTable.Columns["COLUMN_DEFAULT"].SetOrdinal(5);
// dataTable.Columns["isnullable"].ColumnName = "IS_NULLABLE";
dataTable.Columns.Add("IS_NULLABLE", typeof(string), "IIF([isnullable] = True, 'YES', 'NO')").SetOrdinal(6);
//dataTable.Columns["IS_NULLABLE"].SetOrdinal(6);
dataTable.Columns["datatype"].ColumnName = "DATA_TYPE";
dataTable.Columns["DATA_TYPE"].SetOrdinal(7);
// dataTable.Columns["attributemetadata.length"].ColumnName = "character_maximum_length";
dataTable.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int), "IIF(data_type = 'nvarchar', [maxlength], NULL)").SetOrdinal(8);
dataTable.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(int), "IIF(data_type ='nvarchar',ISNULL(character_maximum_length, 0) * 2, character_maximum_length)").SetOrdinal(9);
dataTable.Columns["numericprecision"].ColumnName = "NUMERIC_PRECISION";
dataTable.Columns["NUMERIC_PRECISION"].SetOrdinal(10);
dataTable.Columns["numericprecisionradix"].ColumnName = "NUMERIC_PRECISION_RADIX";
dataTable.Columns["NUMERIC_PRECISION_RADIX"].SetOrdinal(11);
dataTable.Columns["numericscale"].ColumnName = "NUMERIC_SCALE";
dataTable.Columns["NUMERIC_SCALE"].SetOrdinal(12);
dataTable.Columns.Add("DATETIME_PRECISION", typeof(int), "IIF(data_type = 'datetime', 3, NULL)").SetOrdinal(13);
dataTable.Columns.Add("CHARACTER_SET_CATALOG", typeof(string), "NULL").SetOrdinal(14);
dataTable.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string), "NULL").SetOrdinal(15);
dataTable.Columns.Add("CHARACTER_SET_NAME", typeof(string), "IIF(data_type ='nvarchar', 'UNICODE', NULL)").SetOrdinal(16);
dataTable.Columns.Add("COLLATION_CATALOG", typeof(string), "NULL").SetOrdinal(17);
dataTable.Columns.Add("IS_SPARSE", typeof(bool), "false").SetOrdinal(18);
dataTable.Columns.Add("IS_COLUMN_SET", typeof(bool), "false").SetOrdinal(19);
dataTable.Columns.Add("IS_FILESTREAM", typeof(bool), "false").SetOrdinal(20);
return dataTable;
}
public DataTable GetForeignKeys(CrmDbConnection crmDbConnection, string[] restrictions)
{
// throw new NotImplementedException();
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string constraintTable = GetRestrictionOrNull(2, restrictions);
string constraintName = GetRestrictionOrNull(3, restrictions);
// string entityName = GetRestrictionOrNull(0, restrictions);
// string constraintName = GetRestrictionOrNull(1, restrictions);
bool hasConstraintTableFilter = !string.IsNullOrEmpty(constraintTable);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT o.* FROM entitymetadata e INNER JOIN onetomanyrelationshipmetadata o ON e.MetadataId = o.MetadataId ";
if (hasConstraintTableFilter || hasConstraintNameFilter)
{
commandText += "WHERE ";
if (hasConstraintTableFilter)
{
commandText += " (e.LogicalName = '" + constraintTable + "') AND (o.referencingentity = '" + constraintTable + "')";
}
if (hasConstraintTableFilter && hasConstraintNameFilter)
{
commandText += " AND ";
}
if (hasConstraintNameFilter)
{
commandText += " (o.SchemaName = '" + constraintName + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// dataTable.AsDataView().RowFilter = "ReferencingEntity = '" + entityName +
dataTable.Columns.Add("CONSTRAINT_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("CONSTRAINT_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "o.schemaname").SetOrdinal(2);
//dataTable.Columns["o.schemaname"].ColumnName = "CONSTRAINT_NAME";
//dataTable.Columns["CONSTRAINT_NAME"].SetOrdinal(2);
// dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "''");
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["o.referencingentity"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(5);
dataTable.Columns.Add("CONSTRAINT_TYPE", typeof(string), "'FOREIGN KEY'").SetOrdinal(6);
dataTable.Columns.Add("IS_DEFERRABLE", typeof(string), "'NO'").SetOrdinal(7);
dataTable.Columns.Add("INITIALLY_DEFERRED", typeof(string), "'NO'").SetOrdinal(8);
//if (hasEntityFilter)
//{
// filteredView.RowFilter = "TABLE_NAME = '" + entityName + "'";
//}
//dataTable = filteredView.ToTable(true);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "CONSTRAINT_NAME = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
//else
//{
// dataTable = filteredView.ToTable(true);
//}
return dataTable;
}
public DataTable GetDataSourceInfo(CrmDbConnection connection)
{
// http://msdn.microsoft.com/en-us/library/ms254501%28v=vs.110%29.aspx
DataTable dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
dataTable.Columns.Add(DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.DataSourceProductName, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersion, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersionNormalized, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.GroupByBehavior, typeof(GroupByBehavior));
dataTable.Columns.Add(DbMetaDataColumnNames.IdentifierPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.IdentifierCase, typeof(IdentifierCase));
dataTable.Columns.Add(DbMetaDataColumnNames.OrderByColumnsInSelect, typeof(bool));
dataTable.Columns.Add(DbMetaDataColumnNames.ParameterMarkerFormat, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.ParameterMarkerPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.ParameterNameMaxLength, typeof(int));
dataTable.Columns.Add(DbMetaDataColumnNames.ParameterNamePattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierCase, typeof(IdentifierCase));
dataTable.Columns.Add(DbMetaDataColumnNames.StatementSeparatorPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.StringLiteralPattern, typeof(string));
dataTable.Columns.Add(DbMetaDataColumnNames.SupportedJoinOperators, typeof(SupportedJoinOperators));
dataTable.Columns.Add("ColumnAliasSupported", typeof(bool));
dataTable.Columns.Add("TableAliasSupported", typeof(bool));
dataTable.Columns.Add("SchemaSupported", typeof(bool));
dataTable.Columns.Add("CatalogSupported", typeof(bool));
DataRow dataRow = dataTable.NewRow();
dataRow[DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern] = @"\.";
dataRow[DbMetaDataColumnNames.DataSourceProductName] = "Dynamics CRM";
dataRow[DbMetaDataColumnNames.DataSourceProductVersion] = connection.ServerVersion;
dataRow[DbMetaDataColumnNames.DataSourceProductVersionNormalized] = connection.ServerVersion;
dataRow[DbMetaDataColumnNames.GroupByBehavior] = GroupByBehavior.Unrelated;
dataRow[DbMetaDataColumnNames.IdentifierPattern] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
// dataRow[DbMetaDataColumnNames.IdentifierPattern] = @"([\p{L}:?@#_][\p{L}\p{N}@#$_]*)|(""(\.|"""")+"")|(\[[^\]]+\])"; // [A-Za-z0-9_#$]
dataRow[DbMetaDataColumnNames.IdentifierCase] = IdentifierCase.Insensitive;
dataRow[DbMetaDataColumnNames.OrderByColumnsInSelect] = false;
dataRow[DbMetaDataColumnNames.ParameterMarkerFormat] = "@{0}";
dataRow[DbMetaDataColumnNames.ParameterMarkerPattern] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
//dataRow[DbMetaDataColumnNames.ParameterMarkerPattern] = "(@[A-Za-z0-9_$#]*)";
dataRow[DbMetaDataColumnNames.ParameterNameMaxLength] = 128;
dataRow[DbMetaDataColumnNames.ParameterNamePattern] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)"; // \p{Lu}\p{Ll}\p{Lt}\p{Lm}\p{Lo}\p{Nl}\p{Nd}
dataRow[DbMetaDataColumnNames.QuotedIdentifierPattern] = @"(([^\[]|\]\])*)";
// dataRow[DbMetaDataColumnNames.QuotedIdentifierPattern] = @"\[(.*?)\]"; //TODO THIS IS WRONG AS IT MATCHES THE QUOTES AND I NEED TO EXLCUDE THEM..;
dataRow[DbMetaDataColumnNames.QuotedIdentifierCase] = IdentifierCase.Insensitive;
dataRow[DbMetaDataColumnNames.StatementSeparatorPattern] = @";";
dataRow[DbMetaDataColumnNames.StringLiteralPattern] = @"'(([^']|'')*)'"; //TODO THIS IS WRONG AS IT MATCHES THE QUOTES AND I NEED TO EXLCUDE THEM..;
dataRow[DbMetaDataColumnNames.SupportedJoinOperators] = SupportedJoinOperators.Inner | SupportedJoinOperators.LeftOuter;
#region probably none of this needed
//dataTable.Columns.Add("IdentifierOpenQuote", typeof(string));
//dataTable.Columns.Add("IdentifierCloseQuote", typeof(string));
//dataTable.Columns.Add("SupportsAnsi92Sql", typeof(bool));
//dataTable.Columns.Add("SupportsQuotedIdentifierParts", typeof(bool));
//dataTable.Columns.Add("ParameterPrefix", typeof(string));
//dataTable.Columns.Add("ParameterPrefixInName", typeof(bool));
dataRow["ColumnAliasSupported"] = false;
dataRow["TableAliasSupported"] = true;
dataRow["SchemaSupported"] = true;
dataRow["CatalogSupported"] = true;
//dataTable.Columns.Add("TableSupported", typeof(bool));
//dataTable.Columns.Add("UserSupported", typeof(bool));
//dataTable.Columns.Add("SupportsVerifySQL", typeof(bool));
//dataRow["IdentifierOpenQuote"] = "[";
//dataRow["IdentifierCloseQuote"] = "]";
//dataRow["SupportsAnsi92Sql"] = false;
//dataRow["SupportsQuotedIdentifierParts"] = true;
//dataRow["ParameterPrefix"] = "@";
//dataRow["ParameterPrefixInName"] = true;
//dataRow["TableSupported"] = true;
//dataRow["UserSupported"] = false;
//dataRow["SupportsVerifySQL"] = false;
#endregion
dataTable.Rows.Add(dataRow);
return dataTable;
}
public DataTable GetViews(CrmDbConnection crmDbConnection, string[] restrictions)
{
DataTable dataTable = new DataTable("Views");
dataTable.Locale = CultureInfo.InvariantCulture;
dataTable.Columns.AddRange(
new DataColumn[]
{
new DataColumn("TABLE_CATALOG"),
new DataColumn("TABLE_SCHEMA"),
new DataColumn("TABLE_NAME"),
new DataColumn("CHECK_OPTION"),
new DataColumn("IS_UPDATABLE")
});
return dataTable;
// throw new NotImplementedException();
}
public DataTable GetDatabases(CrmDbConnection crmDbConnection, string[] restrictions)
{
// database_name (System.String) dbid (System.Int16) create_date (System.DateTime)
DataTable dataTable = new DataTable("Databases");
dataTable.Locale = CultureInfo.InvariantCulture;
dataTable.Columns.AddRange(
new DataColumn[]
{
new DataColumn("database_name"),
new DataColumn("dbid",typeof(Int16)),
new DataColumn("create_date", typeof(DateTime)),
new DataColumn("organisationid",typeof(Guid)),
new DataColumn("serverversion")
});
dataTable.Rows.Add(crmDbConnection.ConnectionInfo.OrganisationName, 1, DateTime.UtcNow, crmDbConnection.ConnectionInfo.OrganisationId, crmDbConnection.ConnectionInfo.ServerVersion);
return dataTable;
}
public void Write_Schema_Collections_To_Html_Files()
{
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
WriteDataTableToHtmlFile("MetaDataCollections", sut.GetMetadataCollections());
WriteDataTableToHtmlFile("Restrictions", sut.GetRestrictions());
WriteDataTableToHtmlFile("DataSourceInformation", sut.GetDataSourceInfo(conn));
//WriteDataTableToHtmlFile("DataTypes", connection);
WriteDataTableToHtmlFile("ReservedWords", sut.GetReservedWords());
// WriteDataTableToHtmlFile("Databases", sut.getdata);
// WriteDataTableToHtmlFile("Schemata", sut.GetSchema(conn, "Schemata", null));
WriteDataTableToHtmlFile("Tables", sut.GetTables(conn, null));
WriteDataTableToHtmlFile("Columns", sut.GetColumns(conn, null));
WriteDataTableToHtmlFile("Views", sut.GetViews(conn, null));
WriteDataTableToHtmlFile("Users", sut.GetUsers(conn, null));
WriteDataTableToHtmlFile("Indexes", sut.GetIndexes(conn, null));
WriteDataTableToHtmlFile("IndexColumns", sut.GetIndexColumns(conn, null));
// WriteDataTableToHtmlFile("Constraints", sut.get);
// WriteDataTableToHtmlFile("PrimaryKey", sut.pr();
// WriteDataTableToHtmlFile("UniqueKeys", sut.Get);
WriteDataTableToHtmlFile("ForeignKeys", sut.GetForeignKeys(conn, null));
WriteDataTableToHtmlFile("UniqueKeys", sut.GetUniqueKeys(conn, null));
// WriteDataTableToHtmlFile("ConstraintColumns", sut.get);
}
}
public DataTable GetForeignKeyColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintname = GetRestrictionOrNull(3, restrictions);
string columnname = GetRestrictionOrNull(4, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasColumnFilter = !string.IsNullOrEmpty(columnname);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintname);
string commandText = "SELECT o.* FROM entitymetadata e INNER JOIN onetomanyrelationshipmetadata o ON e.MetadataId = o.MetadataId";
if (hasEntityFilter || hasColumnFilter || hasConstraintNameFilter)
{
commandText += " WHERE ";
if (hasEntityFilter)
{
commandText += " (e.LogicalName = '" + table + "') AND (o.ReferencingEntity = '" + table + "')";
}
if (hasEntityFilter && (hasConstraintNameFilter || hasColumnFilter))
{
commandText += " AND ";
}
if (hasConstraintNameFilter)
{
commandText += " (o.SchemaName = '" + constraintname + "')";
}
if (hasConstraintNameFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (o.ReferencingAttribute = '" + columnname + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// need to get columns seperately then join to get column ordinal
commandText = "SELECT entitymetadata.LogicalName, a.LogicalName, a.ColumnNumber FROM entitymetadata JOIN attributemetadata a on entitymetadata.MetadataId = a.MetadataId";
if (hasEntityFilter || hasColumnFilter)
{
commandText += "AND ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + table + "')";
}
if (hasEntityFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (a.LogicalName = '" + columnname + "')";
}
}
//var columnsCommand = new CrmDbCommand(crmDbConnection);
//columnsCommand.CommandText = commandText;
//var columnsAdaptor = new CrmDataAdapter(columnsCommand);
//var columnsDatatable = new DataTable();
//columnsDatatable.Locale = CultureInfo.InvariantCulture;
//columnsAdaptor.Fill(columnsDatatable);
//DataSet fkInfoDataset = new DataSet();
//fkInfoDataset.Tables.Add(dataTable);
//fkInfoDataset.Tables.Add(columnsDatatable);
//var parentCols = new DataColumn[] { dataTable.Columns["e.LogicalName"], dataTable.Columns["o.ReferencingAttribute"] };
//var childCols = new DataColumn[] { columnsDatatable.Columns["LogicalName"], columnsDatatable.Columns["a.LogicalName"] };
//DataRelation columnInfoRelation = new DataRelation("columnJoin", parentCols, childCols);
//fkInfoDataset.Relations.Add(columnInfoRelation);
//var view = dataTable.AsDataView();
//view.j
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("constraint_name", typeof(string), "o.schemaname").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["o.ReferencingEntity"].ColumnName = "table_name";
dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns.Add("column_name", typeof(string), "o.ReferencingAttribute").SetOrdinal(6);
dataTable.Columns.Add("ordinal_position", typeof(int)).SetOrdinal(7); // TODO: FIX THIS ORDINAL!
// dataTable.Columns["a.columnnumber"].ColumnName = "ordinal_position";
// dataTable.Columns["ordinal_position"].SetOrdinal(7);
dataTable.Columns.Add("constraint_type", typeof(string), "'FOREIGN KEY'").SetOrdinal(8);
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(9);
//foreach (DataRow item in dataTable.Rows)
//{
// var columnName = (string)item["column_name"];
// var columnAttInfos = item.GetChildRows(columnInfoRelation);
// if (columnAttInfos == null || !columnAttInfos.Any())
// {
// throw new InvalidOperationException("Could not find column attribute information for a one to many relationsnip column: " + columnName);
// }
// var singleColumnInfo = columnAttInfos.Single();
// item["ordinal_position"] = (int)singleColumnInfo["a.columnnumber"];
// // var columnResult = columnsDatatable.Rows.Find(myUserID);
//}
// dataTable.Columns.Add("constraint_name", typeof(string), "'FK__' + IsNull(table_name, '') + '_' + IsNull(column_name, PrimaryIdAttribute)").SetOrdinal(2);
// dataTable.Columns["column_name"].SetOrdinal(6);
// dataTable.Columns.Add("KeyType", typeof(Byte), "36").SetOrdinal(8); // 36 = uniqueidentitifer datatype - all pk indexes in crm are uniqueidentifiers.
// dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'");
//var filteredView = dataTable.AsDataView();
//filteredView.RowFilter = "column_name = PrimaryIdAttribute"; // necessary due to #68
//dataTable = filteredView.ToTable(true);
//if (hasConstraintNameFilter)
//{
// var filteredView = dataTable.AsDataView();
// filteredView.RowFilter = "constraint_name = '" + constraintname + "'";
// dataTable = filteredView.ToTable(true);
//}
return dataTable;
}
public void Should_Be_Able_To_Get_A_Table(string tableName)
{
// Arrange
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var restrictions = new string[] { null, null, tableName, null };
// Act
var collection = sut.GetTables(conn, restrictions);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Rows.Count, Is.EqualTo(1));
var firstRow = collection.Rows[0];
var col = collection.Columns["table_catalog"];
Assert.That(col, Is.Not.Null);
Assert.That(firstRow[col], Is.EqualTo(conn.ConnectionInfo.OrganisationName));
col = collection.Columns["table_schema"];
Assert.That(col, Is.Not.Null);
Assert.That(firstRow[col], Is.EqualTo("dbo"));
col = collection.Columns["table_name"];
Assert.That(col, Is.Not.Null);
Assert.That(firstRow[col], Is.EqualTo(tableName));
col = collection.Columns["table_type"];
Assert.That(col, Is.Not.Null);
Assert.That(firstRow[col], Is.EqualTo("BASE TABLE"));
}
}
public DataTable GetIndexes(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintName = GetRestrictionOrNull(3, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT LogicalName, PrimaryIdAttribute FROM entitymetadata ";
if (hasEntityFilter || hasConstraintNameFilter)
{
commandText += "WHERE ";
if (hasEntityFilter)
{
commandText += " (LogicalName = '" + table + "')";
}
//if (hasEntityFilter && hasConstraintNameFilter)
//{
// commandText += " AND ";
//}
//if (hasConstraintNameFilter)
//{
// commandText += " (PrimaryIdAttribute = '" + constraintName + "')";
//}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// <Indexes>
// <constraint_catalog>PortalDarrellDev</constraint_catalog>
// <constraint_schema>dbo</constraint_schema>
// <constraint_name>PK__tmp_ms_x__3214EC0737311087</constraint_name>
// <table_catalog>PortalDarrellDev</table_catalog>
// <table_schema>dbo</table_schema>
// <table_name>Table</table_name>
// <index_name>PK__tmp_ms_x__3214EC0737311087</index_name>
// <type_desc>CLUSTERED</type_desc>
//</Indexes>
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("constraint_name", typeof(string), "'PK__' + [LogicalName] + '_' + [PrimaryIdAttribute]").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns.Add("table_name", typeof(string), "[LogicalName]").SetOrdinal(5);
//dataTable.Columns["LogicalName"].ColumnName = "table_name";
// dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(6);
dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'").SetOrdinal(7);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "constraint_name = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
}
public void Should_Be_Able_To_Get_Columns(string tableName)
{
// Arrange
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var restrictions = new string[] { null, null, tableName };
// Act
var collection = sut.GetColumns(conn, restrictions);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Rows.Count, Is.GreaterThan(0));
foreach (DataRow row in collection.Rows)
{
var val = AssertColVal(collection, row, "table_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "table_schema");
Assert.That(val, Is.EqualTo("dbo"));
val = AssertColVal(collection, row, "table_name");
Assert.That(val, Is.EqualTo(tableName));
val = AssertColVal(collection, row, "column_default");
val = AssertColVal(collection, row, "is_nullable");
string dataType = (string)AssertColVal(collection, row, "data_type");
var charMaxLength = AssertColVal(collection, row, "character_maximum_length");
var octetVal = AssertColVal(collection, row, "character_octet_length");
if (dataType == "nvarchar")
{
var charMax = (int)charMaxLength;
var octetMax = (int)octetVal;
Assert.That(octetMax, Is.EqualTo(charMax * 2));
}
else
{
Assert.That(charMaxLength, Is.EqualTo(DBNull.Value));
Assert.That(octetVal, Is.EqualTo(DBNull.Value));
}
val = AssertColVal(collection, row, "numeric_precision");
val = AssertColVal(collection, row, "numeric_precision_radix");
val = AssertColVal(collection, row, "numeric_scale");
val = AssertColVal(collection, row, "datetime_precision");
if (dataType == "datetime")
{
Assert.That(val, Is.EqualTo(3));
}
else
{
Assert.That(val, Is.EqualTo(DBNull.Value));
}
val = AssertColVal(collection, row, "character_set_catalog");
val = AssertColVal(collection, row, "character_set_schema");
var charSetName = AssertColVal(collection, row, "character_set_name");
if (dataType == "nvarchar")
{
Assert.That(charSetName, Is.EqualTo("UNICODE"));
}
else
{
Assert.That(charSetName, Is.EqualTo(DBNull.Value));
}
val = AssertColVal(collection, row, "collation_catalog");
val = AssertColVal(collection, row, "is_sparse");
Assert.That(val, Is.EqualTo(false));
val = AssertColVal(collection, row, "is_column_set");
Assert.That(val, Is.EqualTo(false));
val = AssertColVal(collection, row, "is_filestream");
Assert.That(val, Is.EqualTo(false));
}
}
}
public DataTable GetSchema(CrmDbConnection crmDbConnection, string collectionName, string[] restrictions)
{
DataTable result = null;
if (restrictions == null)
{
restrictions = new string[] { "" };
}
switch (collectionName.ToLower())
{
case "metadatacollections":
result = GetMetadataCollections();
break;
case "datasourceinformation":
result = GetDataSourceInfo(crmDbConnection);
break;
case "reservedwords":
result = GetReservedWords();
break;
case "databases":
result = GetDatabases(crmDbConnection, restrictions);
break;
case "datatypes":
result = GetDataTypes();
break;
case "restrictions":
result = GetRestrictions();
break;
case "tables":
result = GetTables(crmDbConnection, restrictions);
break;
case "columns":
result = GetColumns(crmDbConnection, restrictions);
break;
case "views":
result = GetViews(crmDbConnection, restrictions);
break;
case "viewcolumns":
result = GetViewColumns(crmDbConnection, restrictions);
break;
case "indexes":
result = GetIndexes(crmDbConnection, restrictions);
break;
case "indexcolumns":
result = GetIndexColumns(crmDbConnection, restrictions);
break;
case "foreignkeys":
result = GetForeignKeys(crmDbConnection, restrictions);
break;
case "foreignkeycolumns":
result = GetForeignKeyColumns(crmDbConnection, restrictions);
break;
case "users":
result = GetUsers(crmDbConnection, restrictions);
break;
case "uniquekeys":
result = GetUniqueKeys(crmDbConnection, restrictions);
break;
case "uniquekeycolumns":
result = GetUniqueKeyColumns(crmDbConnection, restrictions);
break;
//case "constraints":
//case "primarykey":
//case "constraintcolumns":
// break;
default:
throw new ArgumentOutOfRangeException("collectionName", collectionName, "Invalid collection name");
// }
}
return result;
}
public void Should_Be_Able_To_Get_DataSourceInfo()
{
// Arrange
var sut = new SchemaCollectionsProvider();
// Act
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var collection = sut.GetDataSourceInfo(conn);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Columns.Count, Is.AtLeast(17));
}
}
public DataTable GetUniqueKeyColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintname = GetRestrictionOrNull(3, restrictions);
string columnname = GetRestrictionOrNull(4, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasColumnFilter = !string.IsNullOrEmpty(columnname);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintname);
string commandText = "SELECT entitymetadata.PrimaryIdAttribute, entitymetadata.LogicalName, a.LogicalName, a.ColumnNumber FROM entitymetadata JOIN attributemetadata a on entitymetadata.MetadataId = a.MetadataId WHERE (a.isprimaryid = @isPrimaryId)";
if (hasEntityFilter || hasColumnFilter)
{
commandText += "AND ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + table + "')";
}
if (hasEntityFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (a.LogicalName = '" + columnname + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
var param = command.CreateParameter();
param.DbType = DbType.Boolean;
param.Direction = ParameterDirection.Input;
param.ParameterName = "@isPrimaryId";
param.Value = true;
command.Parameters.Add(param);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["LogicalName"].ColumnName = "table_name";
dataTable.Columns.Add("column_name", typeof(string), "IsNull([a.LogicalName], [PrimaryIdAttribute])");
dataTable.Columns.Add("constraint_name", typeof(string), "'PK__' + IsNull(table_name, '') + '_' + IsNull(column_name, PrimaryIdAttribute)").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns["column_name"].SetOrdinal(6);
dataTable.Columns["a.columnnumber"].ColumnName = "ordinal_position";
dataTable.Columns["ordinal_position"].SetOrdinal(7);
dataTable.Columns.Add("constraint_type", typeof(string), "'PRIMARY KEY'").SetOrdinal(8);
// dataTable.Columns.Add("KeyType", typeof(Byte), "36").SetOrdinal(8); // 36 = uniqueidentitifer datatype - all pk indexes in crm are uniqueidentifiers.
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(9);
// dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'");
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "column_name = PrimaryIdAttribute"; // necessary due to #68
dataTable = filteredView.ToTable(true);
if (hasConstraintNameFilter)
{
filteredView = dataTable.AsDataView();
filteredView.RowFilter = "constraint_name = '" + constraintname + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
}
public void Should_Be_Able_To_Get_ForeignKeys_For_A_Table(string tableName)
{
// Arrange
var sut = new SchemaCollectionsProvider();
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
var restrictions = new string[] { null, null, tableName, null };
// Act
var collection = sut.GetForeignKeys(conn, restrictions);
// Assert
Assert.That(collection, Is.Not.Null);
Assert.That(collection.Columns, Is.Not.Null);
Assert.That(collection.Rows.Count, Is.GreaterThan(0));
foreach (DataRow row in collection.Rows)
{
var val = AssertColVal(collection, row, "constraint_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "constraint_schema");
Assert.That(val, Is.EqualTo("dbo"));
val = AssertColVal(collection, row, "constraint_name");
Assert.IsFalse(string.IsNullOrEmpty((string)val));
Console.WriteLine(val);
val = AssertColVal(collection, row, "table_catalog");
Assert.That(val, Is.EqualTo(conn.ConnectionInfo.OrganisationName));
val = AssertColVal(collection, row, "table_schema");
Assert.That(val, Is.EqualTo("dbo"));
val = AssertColVal(collection, row, "table_name");
Assert.That(val, Is.EqualTo(tableName));
Console.WriteLine(tableName);
val = AssertColVal(collection, row, "constraint_type");
Assert.That(val, Is.EqualTo("FOREIGN KEY"));
val = AssertColVal(collection, row, "is_deferrable");
Assert.That(val, Is.EqualTo("NO"));
val = AssertColVal(collection, row, "initially_deferred");
Assert.That(val, Is.EqualTo("NO"));
}
}
}
