protected override Guesser GetGuesser(DatabaseTypeRequest request)
{
return(new Guesser(request)
{
ExtraLengthPerNonAsciiCharacter = OracleTypeTranslater.ExtraLengthPerNonAsciiCharacter
});
}
public override void HandleRequest(Request request)
{
if (request is DatabaseTypeRequest)
{
DatabaseTypeRequest db_request = request as DatabaseTypeRequest;
DatabaseConnectionString db_conn_string = new DatabaseConnectionString(db_request.DeviceName);
DatabaseType db_type = new DatabaseType(db_request.DeviceName);
try
{
Database db = new Database();
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
db_request.ConnectionString = db_conn_string.GetValue(conn);
db_request.DatabaseType = db_type.GetAsDatabaseType(conn);
db_request.Handled();
}
}
catch (Exception)
{
}
}
}
private static DatabaseTypeRequest Conflate(List <DatabaseTypeRequest> vrs)
{
var toReturn = vrs.First();
foreach (DatabaseTypeRequest newType in vrs)
{
Type t = toReturn.CSharpType;
if (toReturn.CSharpType != newType.CSharpType)
{
if (
(toReturn.CSharpType == typeof(UInt16) || toReturn.CSharpType == typeof(Int16)) && //some tags e.g. SmallestValidPixelValue can be either ushort or short
newType.CSharpType == typeof(UInt16) || newType.CSharpType == typeof(Int16))
{
t = typeof(Int32); //if they are being all creepy about it just use an int that way theres definetly enough space
}
else
{
throw new Exception("Incompatible Types '" + toReturn.CSharpType + "' and '" + newType.CSharpType + "'");
}
}
toReturn = new DatabaseTypeRequest(
t,
Conflate(toReturn.MaxWidthForStrings, newType.MaxWidthForStrings),
DecimalSize.Combine(toReturn.DecimalPlacesBeforeAndAfter, newType.DecimalPlacesBeforeAndAfter));
}
return(toReturn);
}
protected void GetDBInfo()
{
DatabaseTypeRequest request = new DatabaseTypeRequest("Database Size Request", Context.ID.Name);
RequestBus.Instance.MakeRequest(request);
if (request.IsHandled)
{
DBType = request.DatabaseType;
ConnectionString = request.ConnectionString;
}
}
public void Test_CSharpToDbType_StringMax(DatabaseType type, string expectedType)
{
var cSharpType = new DatabaseTypeRequest(typeof(string), 10000000, null);
//Does a request for a max length string give the expected data type?
Assert.AreEqual(expectedType, _translaters[type].GetSQLDBTypeForCSharpType(cSharpType));
//Does the TypeTranslater know that this datatype has no limit on characters?
Assert.AreEqual(int.MaxValue, _translaters[type].GetLengthIfString(expectedType));
//And does the TypeTranslater know that this datatype is string
Assert.AreEqual(typeof(string), _translaters[type].GetCSharpTypeForSQLDBType(expectedType));
}
public void Test_Max_WithUnicode()
{
var max = DatabaseTypeRequest.Max(
new DatabaseTypeRequest(typeof(string), 1, null)
{
Unicode = true
},
new DatabaseTypeRequest(typeof(string), 2, null)
);
Assert.AreEqual(2, max.Width);
Assert.IsTrue(max.Unicode, "If either arg in a Max call is Unicode then the resulting maximum should be Unicode=true");
}
public void ExampleUsage_Types()
{
var tag = DicomDictionary.Default["PatientAddress"];
DatabaseTypeRequest type = DicomTypeTranslater.GetNaturalTypeForVr(tag.DictionaryEntry.ValueRepresentations, tag.DictionaryEntry.ValueMultiplicity);
Assert.AreEqual(typeof(string), type.CSharpType);
Assert.AreEqual(64, type.MaxWidthForStrings);
TypeTranslater tt = new MicrosoftSQLTypeTranslater();
Assert.AreEqual("varchar(64)", tt.GetSQLDBTypeForCSharpType(type));
tt = new OracleTypeTranslater();
Assert.AreEqual("varchar2(64)", tt.GetSQLDBTypeForCSharpType(type));
}
private string GetDestinationDatabaseType(ConcreteColumn col)
{
//Make sure we know if we are going between database types
var fromDbType = _destinationDatabase.Server.DatabaseType;
var toDbType = col.ColumnInfo.TableInfo.DatabaseType;
if (fromDbType != toDbType)
{
var fromSyntax = col.ColumnInfo.GetQuerySyntaxHelper();
var toSyntax = _destinationDatabase.Server.GetQuerySyntaxHelper();
DatabaseTypeRequest intermediate = fromSyntax.TypeTranslater.GetDataTypeRequestForSQLDBType(col.ColumnInfo.Data_type);
return(toSyntax.TypeTranslater.GetSQLDBTypeForCSharpType(intermediate));
}
return(col.ColumnInfo.Data_type);
}
private bool IsValidLength(IDataLoadEventListener listener, DicomTag tag, string value)
{
lock (_oDictLock)
{
if (!_maxTagLengths.ContainsKey(tag))
{
DatabaseTypeRequest type = DicomTypeTranslater.GetNaturalTypeForVr(
tag.DictionaryEntry.ValueRepresentations, tag.DictionaryEntry.ValueMultiplicity);
_maxTagLengths.Add(tag, type.MaxWidthForStrings ?? -1);
}
//we don't think it's a string or the string is a fine length
if (_maxTagLengths[tag] <= 0 || value.Length <= _maxTagLengths[tag])
{
return(true);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning,
"Found value '" + value + "' that was too long for it's VR (" + tag + "). Max length was " +
_maxTagLengths[tag] + " supplied value was length " + value.Length));
}
return(false);
}
public DatabaseColumnRequest(string columnName, DatabaseTypeRequest typeRequested, bool allowNulls = true)
{
ColumnName = columnName;
TypeRequested = typeRequested;
AllowNulls = allowNulls;
}
protected DilutionOperation(DatabaseTypeRequest expectedDestinationType)
{
ExpectedDestinationType = expectedDestinationType;
}
/// <summary>
/// Creates and runs an ALTER TABLE SQL statement that adds a new column to the table
/// </summary>
/// <param name="name">The unqualified name for the new column e.g. "MyCol2"</param>
/// <param name="type">The data type for the new column</param>
/// <param name="allowNulls">True to allow null</param>
/// <param name="timeoutInSeconds">The length of time to wait in seconds before giving up (See <see cref="DbCommand.CommandTimeout"/>)</param>
public void AddColumn(string name, DatabaseTypeRequest type, bool allowNulls, int timeoutInSeconds)
{
AddColumn(name, Database.Server.GetQuerySyntaxHelper().TypeTranslater.GetSQLDBTypeForCSharpType(type), allowNulls, timeoutInSeconds);
}
/// <summary>
/// Creates and runs an ALTER TABLE SQL statement that adds a new column to the table
/// </summary>
/// <param name="name">The unqualified name for the new column e.g. "MyCol2"</param>
/// <param name="type">The data type for the new column</param>
/// <param name="allowNulls">True to allow null</param>
/// <param name="timeoutInSeconds">The length of time to wait in seconds before giving up (See <see cref="DbCommand.CommandTimeout"/>)</param>
public void AddColumn(string name, DatabaseTypeRequest type, bool allowNulls, int timeoutInSeconds)
{
AddColumn(name, type, allowNulls, new DatabaseOperationArgs {
TimeoutInSeconds = timeoutInSeconds
});
}
public DiscoveredTable CreateTable(CreateTableArgs args)
{
var typeDictionary = new Dictionary <string, DataTypeComputer>(StringComparer.CurrentCultureIgnoreCase);
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
List <DatabaseColumnRequest> customRequests = args.ExplicitColumnDefinitions != null
? args.ExplicitColumnDefinitions.ToList()
: new List <DatabaseColumnRequest>();
if (args.DataTable != null)
{
//If we have a data table from which to create the table from
foreach (DataColumn column in args.DataTable.Columns)
{
//do we have an explicit overriding column definition?
DatabaseColumnRequest overriding = customRequests.SingleOrDefault(c => c.ColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase));
//yes
if (overriding != null)
{
columns.Add(overriding);
customRequests.Remove(overriding);
//Type reqeuested
var request = overriding.TypeRequested;
//Type is for an explicit Type e.g. datetime
if (request == null)
{
if (!string.IsNullOrWhiteSpace(overriding.ExplicitDbType))
{
var tt = args.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
request = new DatabaseTypeRequest(
tt.GetCSharpTypeForSQLDBType(overriding.ExplicitDbType),
tt.GetLengthIfString(overriding.ExplicitDbType),
tt.GetDigitsBeforeAndAfterDecimalPointIfDecimal(overriding.ExplicitDbType));
}
else
{
throw new Exception("explicitColumnDefinitions for column " + column + " did not contain either a TypeRequested or ExplicitDbType");
}
}
typeDictionary.Add(overriding.ColumnName, GetDataTypeComputer(request));
}
else
{
//no, work out the column definition using a datatype computer
DataTypeComputer computer = GetDataTypeComputer(column);
typeDictionary.Add(column.ColumnName, computer);
columns.Add(new DatabaseColumnRequest(column.ColumnName, computer.GetTypeRequest(), column.AllowDBNull)
{
IsPrimaryKey = args.DataTable.PrimaryKey.Contains(column)
});
}
}
}
else
{
//If no DataTable is provided just use the explicitly requested columns
columns = customRequests;
}
if (args.Adjuster != null)
{
args.Adjuster.AdjustColumns(columns);
}
//Get the table creation SQL
string bodySql = GetCreateTableSql(args.Database, args.TableName, columns.ToArray(), args.ForeignKeyPairs, args.CascadeDelete, args.Schema);
//connect to the server and send it
var server = args.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
ExecuteBatchNonQuery(bodySql, con);
}
//Get reference to the newly created table
var tbl = args.Database.ExpectTable(args.TableName, args.Schema);
//unless we are being asked to create it empty then upload the DataTable to it
if (args.DataTable != null && !args.CreateEmpty)
{
tbl.BeginBulkInsert().Upload(args.DataTable);
}
args.OnTableCreated(typeDictionary);
return(tbl);
}
protected virtual DataTypeComputer GetDataTypeComputer(DatabaseTypeRequest request)
{
return(new DataTypeComputer(request));
}
protected override DataTypeComputer GetDataTypeComputer(DatabaseTypeRequest request)
{
return(new DataTypeComputer(request, OracleTypeTranslater.ExtraLengthPerNonAsciiCharacter));
}
public void Performance_Decimals()
{
var culture = new CultureInfo("en-gb");
List <string> inputs = new List <string>();
var r = new Random(500);
for (int i = 0; i < 1_000_000; i++)
{
inputs.Add((r.NextDouble() * 1000.0).ToString("F"));
}
var decider = new DecimalTypeDecider(new CultureInfo("en-GB"));
var req = new DatabaseTypeRequest(null);
Stopwatch sw = new Stopwatch();
sw.Start();
foreach (string s in inputs)
{
decider.IsAcceptableAsType(s, req);
}
sw.Stop();
Console.WriteLine($"DatabaseTypeRequest.IsAcceptableAsType:{sw.ElapsedMilliseconds} ms");
sw.Restart();
var g = new Guesser();
foreach (string s in inputs)
{
g.AdjustToCompensateForValue(s);
}
sw.Stop();
Console.WriteLine($"Guesser.AdjustToCompensateForValue:{sw.ElapsedMilliseconds} ms");
sw.Restart();
foreach (string s in inputs)
{
s.To <decimal>(culture);
}
sw.Stop();
Console.WriteLine($"To<decimal>:{sw.ElapsedMilliseconds} ms");
sw.Restart();
foreach (string s in inputs)
{
decimal.TryParse(s, NumberStyles.Any, culture, out _);
}
sw.Stop();
Console.WriteLine($"Parse:{sw.ElapsedMilliseconds} ms");
}
private DataTable GetSubstitutionsForANOEquivalents(DataTable table, bool previewOnly)
{
using (var con = (SqlConnection)_server.GetConnection())
{
con.InfoMessage += _con_InfoMessage;
if (table.Rows.Count == 0)
{
return(table);
}
try
{
SqlTransaction transaction = null;
if (previewOnly)
{
bool mustPush = !_anoTable.IsTablePushed();
con.Open();
transaction = con.BeginTransaction();//if it is preview only we will use a transaction which we will then rollback
if (mustPush)
{
var cSharpType =
new DatabaseTypeRequest(table.Columns[0].DataType,
_anoTable.NumberOfIntegersToUseInAnonymousRepresentation
+ _anoTable.NumberOfCharactersToUseInAnonymousRepresentation);
//we want to use this syntax
var syntaxHelper = _server.Helper.GetQuerySyntaxHelper();
//push to the destination server
_anoTable.PushToANOServerAsNewTable(
//turn the csharp type into an SQL type e.g. string 30 becomes varchar(30)
syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(cSharpType),
new ThrowImmediatelyCheckNotifier(), con, transaction);
}
}
string substituteForANOIdentifiersProc = SubstitutionStoredprocedure;
SqlCommand cmdSubstituteIdentifiers = new SqlCommand(substituteForANOIdentifiersProc, con);
cmdSubstituteIdentifiers.CommandType = CommandType.StoredProcedure;
cmdSubstituteIdentifiers.CommandTimeout = 500;
cmdSubstituteIdentifiers.Transaction = transaction;
cmdSubstituteIdentifiers.Parameters.Add("@batch", SqlDbType.Structured);
cmdSubstituteIdentifiers.Parameters.Add("@tableName", SqlDbType.VarChar, 500);
cmdSubstituteIdentifiers.Parameters.Add("@numberOfIntegersToUseInAnonymousRepresentation", SqlDbType.Int);
cmdSubstituteIdentifiers.Parameters.Add("@numberOfCharactersToUseInAnonymousRepresentation", SqlDbType.Int);
cmdSubstituteIdentifiers.Parameters.Add("@suffix", SqlDbType.VarChar, 10);
//table valued parameter
cmdSubstituteIdentifiers.Parameters["@batch"].TypeName = "dbo.Batch";
cmdSubstituteIdentifiers.Parameters["@batch"].Value = table;
cmdSubstituteIdentifiers.Parameters["@tableName"].Value = _anoTable.TableName;
cmdSubstituteIdentifiers.Parameters["@numberOfIntegersToUseInAnonymousRepresentation"].Value = _anoTable.NumberOfIntegersToUseInAnonymousRepresentation;
cmdSubstituteIdentifiers.Parameters["@numberOfCharactersToUseInAnonymousRepresentation"].Value = _anoTable.NumberOfCharactersToUseInAnonymousRepresentation;
cmdSubstituteIdentifiers.Parameters["@suffix"].Value = _anoTable.Suffix;
SqlDataAdapter da = new SqlDataAdapter(cmdSubstituteIdentifiers);
DataTable dtToReturn = new DataTable();
da.Fill(dtToReturn);
if (previewOnly)
{
transaction.Rollback();
}
return(dtToReturn);
}
catch (Exception e)
{
throw new Exception(SubstitutionStoredprocedure + " failed to complete correctly: " + e);
}
}
}
public virtual string GetParameterDeclaration(string proposedNewParameterName, DatabaseTypeRequest request)
{
return(GetParameterDeclaration(proposedNewParameterName, TypeTranslater.GetSQLDBTypeForCSharpType(request)));
}
protected virtual Guesser GetGuesser(DatabaseTypeRequest request)
{
return(new Guesser(request));
}
/// <summary>
/// Creates and runs an ALTER TABLE SQL statement that adds a new column to the table
/// </summary>
/// <param name="name">The unqualified name for the new column e.g. "MyCol2"</param>
/// <param name="type">The data type for the new column</param>
/// <param name="allowNulls">True to allow null</param>
public void AddColumn(string name, DatabaseTypeRequest type, bool allowNulls, DatabaseOperationArgs args)
{
AddColumn(name, Database.Server.GetQuerySyntaxHelper().TypeTranslater.GetSQLDBTypeForCSharpType(type), allowNulls, args);
}
public void Test_CSharpToDbType_String10(DatabaseType type, string expectedType)
{
var cSharpType = new DatabaseTypeRequest(typeof(string), 10, null);
Assert.AreEqual(expectedType, _translaters[type].GetSQLDBTypeForCSharpType(cSharpType));
}
