public void DbColumn_DataTypeName_WhenVarCharWithMaxLength_ExpectVarCharOfTen()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.VarChar, MaxLength = 10 };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "varchar (10)");
}
public void DbColumn_Constructor_WhenEmptyConstuctor_ExpectDefaultProperties()
{
//------------Setup for test--------------------------
//------------Execute Test---------------------------
var dbColumn = new DbColumn();
//------------Assert Results-------------------------
Assert.IsFalse(dbColumn.IsNullable);
Assert.IsFalse(dbColumn.IsAutoIncrement);
Assert.AreEqual(0, dbColumn.MaxLength);
Assert.IsNull(dbColumn.DataType);
Assert.IsNull(dbColumn.ColumnName);
}
public void DbColumn_Constructor_WhenUsingDbTable_ExpectAllPropertiesTransfered()
{
//------------Setup for test--------------------------
DataColumn dc = new DataColumn { AllowDBNull = true, AutoIncrement = false, ColumnName = "myColumn", MaxLength = 20, DataType = typeof(string) };
//------------Execute Test---------------------------
var dbColumn = new DbColumn(dc);
//------------Assert Results-------------------------
Assert.IsTrue(dbColumn.IsNullable);
Assert.IsFalse(dbColumn.IsAutoIncrement);
Assert.AreEqual(20, dbColumn.MaxLength);
Assert.AreEqual(typeof(string), dbColumn.DataType);
StringAssert.Contains("myColumn", dbColumn.ColumnName);
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace)
{
if(values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string database = null;
string tableName = null;
string schema = null;
StringBuilder tmp;
values.TryGetValue("Database", out tmp);
if(tmp != null)
{
database = tmp.ToString();
}
values.TryGetValue("TableName", out tmp);
if(tmp != null)
{
tableName = tmp.ToString();
}
values.TryGetValue("Schema", out tmp);
if(tmp != null)
{
schema = tmp.ToString();
}
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if(string.IsNullOrEmpty(database))
{
var res = new DbColumnList("No database set.");
Dev2Logger.Log.Debug("No database set.");
return serializer.SerializeToBuilder(res);
}
if(string.IsNullOrEmpty(tableName))
{
var res = new DbColumnList("No table name set.");
Dev2Logger.Log.Debug("No table name set.");
return serializer.SerializeToBuilder(res);
}
Dev2Logger.Log.Info(String.Format("Get Database Columns For Table. Database:{0} Schema:{1} Table{2}" ,database,schema,tableName));
try
{
var dbSource = serializer.Deserialize<DbSource>(database);
var runtTimedbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID);
DataTable columnInfo;
switch (dbSource.ServerType)
{
case enSourceType.MySqlDatabase:
{
using (var connection = new MySqlConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
var sql = @"select * from " + tableName.Trim('"').Replace("[","").Replace("]","") + " Limit 1 ";
using (var sqlcmd = new MySqlCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
default:
{
using (var connection = new SqlConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
// GUTTED TO RETURN ALL REQUIRED DATA ;)
if (schema == null)
{
schema = string.Empty;
}
var sql = @"select top 1 * from " + schema.Trim('"') + "." + tableName.Trim('"');
using (var sqlcmd = new SqlCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
}
var dbColumns = new DbColumnList();
if(columnInfo != null)
{
foreach(DataRow row in columnInfo.Rows)
{
var columnName = row["ColumnName"] as string;
var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"];
var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"];
var dbColumn = new DbColumn { ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity };
SqlDbType sqlDataType;
var typeValue = dbSource.ServerType == enSourceType.SqlDatabase? row["DataTypeName"] as string:((Type)row["DataType"]).Name;
if(Enum.TryParse(typeValue, true, out sqlDataType))
{
dbColumn.SqlDataType = sqlDataType;
}
var columnLength = row["ColumnSize"] is int ? (int)row["ColumnSize"] : -1;
dbColumn.MaxLength = columnLength;
dbColumns.Items.Add(dbColumn);
}
}
return serializer.SerializeToBuilder(dbColumns);
}
catch(Exception ex)
{
Dev2Logger.Log.Error(ex);
var res = new DbColumnList(ex);
return serializer.SerializeToBuilder(res);
}
}
public void DbColumn_DataTypeName_WhenCharWithNoMaxLength_ExpectCharOfZero()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.Char };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "char (0)");
}
public void DbColumn_DataTypeName_WhenXmlNoMaxLength_ExpectXml()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.Xml };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "xml");
}
public void DbColumn_DataTypeName_WhenVarBinaryNoMaxLength_ExpectVarBinary()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.VarBinary };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "varbinary (0)");
}
public void DbColumn_DataTypeName_WhenUniqueIdentifierNoMaxLength_ExpectUniqueIdentifier()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.UniqueIdentifier };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "uniqueidentifier");
}
public void DbColumn_DataTypeName_WhenTinyIntNoMaxLength_ExpectTinyInt()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.TinyInt };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "tinyint");
}
public void DbColumn_DataTypeName_WhenSctructedNoMaxLength_ExpectStructured()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.Structured };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "structured");
}
public void DbColumn_DataTypeName_WhenSmallMoneyNoMaxLength_ExpectSmallMoney()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.SmallMoney };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "smallmoney");
}
public void DbColumn_DataTypeName_WhenDateTimeOffsetNoMaxLength_ExpectDateTimeOffset()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.DateTimeOffset };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "datetimeoffset");
}
public void DbColumn_DataTypeName_WhenBinaryMaxLength_ExpectBinaryTen()
{
//------------Setup for test--------------------------
var dbColumn = new DbColumn { SqlDataType = SqlDbType.Binary, MaxLength = 10 };
//------------Execute Test---------------------------
var result = dbColumn.DataTypeName;
//------------Assert Results-------------------------
StringAssert.Contains(result, "binary (10)");
}