public void DmTable_Clone_Copy() { var tbl = new DmTable("ServiceTickets"); var id = new DmColumn <Guid>("ServiceTicketID"); tbl.Columns.Add(id); var key = new DmKey(new DmColumn[] { id }); tbl.PrimaryKey = key; tbl.Columns.Add(new DmColumn <string>("Title")); tbl.Columns.Add(new DmColumn <string>("Description")); tbl.Columns.Add(new DmColumn <int>("StatusValue")); tbl.Columns.Add(new DmColumn <int>("EscalationLevel")); tbl.Columns.Add(new DmColumn <DateTime>("Opened")); tbl.Columns.Add(new DmColumn <DateTime>("Closed")); tbl.Columns.Add(new DmColumn <int>("CustomerID")); var st = tbl.NewRow(); st["ServiceTicketID"] = Guid.NewGuid(); st["Title"] = "Titre AER"; st["Description"] = "Description 2"; st["EscalationLevel"] = 1; st["StatusValue"] = 2; st["Opened"] = DateTime.Now; st["Closed"] = null; st["CustomerID"] = 1; tbl.Rows.Add(st); var tbl2 = tbl.Clone(); Assert.NotSame(tbl2, tbl); Assert.Empty(tbl2.Rows); Assert.Equal(8, tbl2.Columns.Count); var tbl3 = tbl.Copy(); Assert.NotSame(tbl3, tbl2); Assert.NotSame(tbl3, tbl); Assert.Equal(8, tbl3.Columns.Count); Assert.NotEmpty(tbl3.Rows); }
internal void ReadSchemaIntoDmSet(DmSet ds) { var dmTableSurrogateArray = this.Tables; for (int i = 0; i < dmTableSurrogateArray.Count; i++) { DmTableSurrogate dmTableSurrogate = dmTableSurrogateArray[i]; DmTable dmTable = new DmTable(); dmTableSurrogate.ReadSchemaIntoDmTable(dmTable); dmTable.Culture = new CultureInfo(dmTableSurrogate.CultureInfoName); dmTable.CaseSensitive = dmTableSurrogate.CaseSensitive; dmTable.TableName = dmTableSurrogate.TableName; ds.Tables.Add(dmTable); } if (this.Relations != null && this.Relations.Count > 0) { foreach (var dmRelationSurrogate in this.Relations) { DmColumn[] parentColumns = new DmColumn[dmRelationSurrogate.ParentKeySurrogates.Length]; DmColumn[] childColumns = new DmColumn[dmRelationSurrogate.ChildKeySurrogates.Length]; for (int i = 0; i < parentColumns.Length; i++) { var columnName = dmRelationSurrogate.ParentKeySurrogates[i].ColumnName; var tableName = dmRelationSurrogate.ParentKeySurrogates[i].TableName; parentColumns[i] = ds.Tables[tableName].Columns[columnName]; columnName = dmRelationSurrogate.ChildKeySurrogates[i].ColumnName; tableName = dmRelationSurrogate.ChildKeySurrogates[i].TableName; childColumns[i] = ds.Tables[tableName].Columns[columnName]; } DmRelation relation = new DmRelation(dmRelationSurrogate.RelationName, parentColumns, childColumns); ds.Relations.Add(relation); } } }
internal static (byte length, byte scale) GetSqlTypePrecision(this DmColumn column) { string sizeString = string.Empty; switch (column.DbType) { case DbType.Decimal: case DbType.Double: case DbType.Single: case DbType.VarNumeric: if (!column.PrecisionSpecified || !column.ScaleSpecified) { break; } return(column.Precision, column.Scale); } return(0, 0); }
public void DmTable_ImportRow() { var tbl2 = new DmTable(); var id = new DmColumn <Guid>("ServiceTicketID"); tbl2.Columns.Add(id); var key = new DmKey(new DmColumn[] { id }); tbl2.PrimaryKey = key; tbl2.Columns.Add(new DmColumn <string>("Title")); tbl2.Columns.Add(new DmColumn <string>("Description")); tbl2.Columns.Add(new DmColumn <int>("StatusValue")); tbl2.Columns.Add(new DmColumn <int>("EscalationLevel")); tbl2.Columns.Add(new DmColumn <DateTime>("Opened")); tbl2.Columns.Add(new DmColumn <DateTime>("Closed")); tbl2.Columns.Add(new DmColumn <int>("CustomerID")); var st = tbl2.NewRow(); st["ServiceTicketID"] = Guid.NewGuid(); st["Title"] = "Titre AER"; st["Description"] = "Description 2"; st["EscalationLevel"] = 1; st["StatusValue"] = 2; st["Opened"] = DateTime.Now; st["Closed"] = null; st["CustomerID"] = 1; tbl2.Rows.Add(st); // Importing into tbl var st2 = this.tbl.ImportRow(st); // acceptchanges to change the row state this.tbl.AcceptChanges(); Assert.NotEqual(st.RowState, st2.RowState); // making change to be sure st2["CustomerID"] = 2; Assert.Equal(2, st2["CustomerID"]); Assert.Equal(1, st["CustomerID"]); }
/// <summary> /// Copies the table schema from a DmTableSurrogate object into a DmTable object. /// </summary> public void ReadSchemaIntoDmTable(DmTable dt) { if (dt == null) { throw new ArgumentNullException("dt", "DmTable"); } dt.TableName = this.TableName; dt.Culture = new CultureInfo(this.CultureInfoName); dt.Schema = this.Schema; dt.CaseSensitive = this.CaseSensitive; dt.OriginalProvider = this.OriginalProvider; dt.SyncDirection = this.SyncDirection; dt.TrackingTablesPrefix = this.TrackingTablesPrefix; dt.TrackingTablesSuffix = this.TrackingTablesSuffix; dt.StoredProceduresPrefix = this.StoredProceduresPrefix; dt.StoredProceduresSuffix = this.StoredProceduresSuffix; dt.TriggersPrefix = this.TriggersPrefix; dt.TriggersSuffix = this.TriggersSuffix; for (int i = 0; i < this.Columns.Count; i++) { DmColumn dmColumn = this.Columns[i].ConvertToDmColumn(); dt.Columns.Add(dmColumn); } if (this.PrimaryKeys != null && this.PrimaryKeys.Count > 0) { DmColumn[] keyColumns = new DmColumn[this.PrimaryKeys.Count]; for (int i = 0; i < this.PrimaryKeys.Count; i++) { string columnName = this.PrimaryKeys[i]; keyColumns[i] = dt.Columns.First(c => dt.IsEqual(c.ColumnName, columnName)); } DmKey key = new DmKey(keyColumns); dt.PrimaryKey = key; } }
public string CreatePkCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY ("); for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++) { DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i]; var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString(); stringBuilder.Append(quotedColumnName); if (i < this.tableDescription.PrimaryKey.Columns.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append(")"); return(stringBuilder.ToString()); }
public override bool IsValid(DmColumn columnDefinition) { switch (columnDefinition.OriginalTypeName.ToLowerInvariant()) { case "number": case "date": case "datetime": case "char": case "datetime2": case "nchar": case "nvarchar": case "nvarchar2": case "varchar2": case "timestamp": case "blob": case "clob": case "varchar": return(true); } return(false); }
private SqlCommand BuildPkCommand() { string[] localName = new string[] { }; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{tableName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY("); for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++) { DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i]; var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString(); stringBuilder.Append(quotedColumnName); if (i < this.tableDescription.PrimaryKey.Columns.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append(")"); return(new SqlCommand(stringBuilder.ToString())); }
public void DmRow_Add_Rows() { var tbl = new DmTable("ServiceTickets"); var idColumn = new DmColumn <Guid>("ServiceTicketID"); tbl.Columns.Add(idColumn); var key = new DmKey(new DmColumn[] { idColumn }); tbl.PrimaryKey = key; var titleColumn = new DmColumn <string>("Title"); tbl.Columns.Add(titleColumn); var statusValueColumn = new DmColumn <int>("StatusValue"); tbl.Columns.Add(statusValueColumn); var openedColumn = new DmColumn <DateTime>("Opened"); tbl.Columns.Add(openedColumn); var st = tbl.NewRow(); Assert.Null(st[0]); Assert.Null(st[1]); var id = Guid.NewGuid(); var dateNow = DateTime.Now; st["ServiceTicketID"] = id; st["Title"] = "Titre AER"; st["StatusValue"] = 2; st["Opened"] = dateNow; tbl.Rows.Add(st); Assert.Equal(id, st["ServiceTicketID"]); Assert.Equal("Titre AER", st["Title"]); Assert.Equal(2, st["StatusValue"]); Assert.Equal(dateNow, st["Opened"]); }
/// <summary> /// Converts to unicode Sql server char (nchar) /// </summary> private static DmColumn MySql_Char_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); // a char(0) is possible in mysql if (mySqlColumn.MaxLength == 0) { sqlColumn.MaxLength = 1; } else { sqlColumn.MaxLength = mySqlColumn.MaxLength >= 1 ? mySqlColumn.MaxLength : -1; } // set if it's a fixed size string sqlColumn.DbType = sqlColumn.MaxLength > 0 ? DbType.StringFixedLength : DbType.String; sqlColumn.OriginalDbType = "SqlDbType.NChar"; sqlColumn.OriginalTypeName = "nchar"; return(sqlColumn); }
private SqlCommand BuildPkCommand() { string[] localName = new string[] { }; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"ALTER TABLE {tableName.QuotedString} ADD CONSTRAINT [PK_{tableName.ObjectName}] PRIMARY KEY("); for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++) { DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i]; var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString; stringBuilder.Append(quotedColumnName); if (i < this.tableDescription.PrimaryKey.Columns.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append(")"); return(new SqlCommand(stringBuilder.ToString())); }
internal static MySqlParameter GetMySqlParameter(this DmColumn column) { MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata(); var parameterName = ParserName.Parse(column).Unquoted().Normalized().ToString(); MySqlParameter sqlParameter = new MySqlParameter { ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}", DbType = column.DbType, IsNullable = column.AllowDBNull }; (byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.MaxLength, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType); if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double || sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0) { sqlParameter.Precision = precision; if (scale > 0) { sqlParameter.Scale = scale; } } else if (column.MaxLength > 0) { sqlParameter.Size = (int)column.MaxLength; } else if (sqlParameter.DbType == DbType.Guid) { sqlParameter.Size = 36; } else { sqlParameter.Size = -1; } return(sqlParameter); }
private void AssertIsEqual(DmColumn c, DmColumn d) { Assert.Equal(c.AllowDBNull, d.AllowDBNull); Assert.Equal(c.IsAutoIncrement, d.IsAutoIncrement); Assert.Equal(c.ColumnName, d.ColumnName); Assert.Equal(c.DataType, d.DataType); Assert.Equal(c.DbType, d.DbType); Assert.Equal(c.DefaultValue, d.DefaultValue); Assert.Equal(c.IsValueType, d.IsValueType); Assert.Equal(c.MaxLength, d.MaxLength); Assert.Equal(c.Ordinal, d.Ordinal); Assert.Equal(c.Precision, d.Precision); Assert.Equal(c.PrecisionSpecified, d.PrecisionSpecified); Assert.Equal(c.IsReadOnly, d.IsReadOnly); Assert.Equal(c.Scale, d.Scale); Assert.Equal(c.ScaleSpecified, d.ScaleSpecified); Assert.Equal(c.IsUnique, d.IsUnique); Assert.Equal(c.IsUnsigned, d.IsUnsigned); Assert.Equal(c.IsUnicode, d.IsUnicode); Assert.Equal(c.IsCompute, d.IsCompute); Assert.Equal(c.Table.TableName, d.Table.TableName); }
public string CreatePkCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append($"ALTER TABLE {trackingName.QuotedString} ADD CONSTRAINT [PK_{trackingName.UnquotedStringWithUnderScore}] PRIMARY KEY ("); for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++) { DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i]; var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString; stringBuilder.Append(quotedColumnName); if (i < this.tableDescription.PrimaryKey.Columns.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append(")"); return(stringBuilder.ToString()); }
/// <summary> /// Constructs a DmColumn object based on a DmColumnSurrogate object. /// </summary> public DmColumn ConvertToDmColumn() { DmColumn dmColumn = DmColumn.CreateColumn(this.ColumnName, DmUtils.GetTypeFromAssemblyQualifiedName(this.DataType)); dmColumn.dbTypeAllowed = this.dbTypeAllowed; if (dmColumn.dbTypeAllowed) { dmColumn.DbType = (DbType)this.DbType; } dmColumn.AllowDBNull = this.AllowDBNull; dmColumn.ReadOnly = this.ReadOnly; dmColumn.MaxLength = this.MaxLength; dmColumn.AutoIncrement = this.AutoIncrement; dmColumn.Precision = this.Precision; dmColumn.PrecisionSpecified = this.PrecisionSpecified; dmColumn.Scale = this.Scale; dmColumn.ScaleSpecified = this.ScaleSpecified; dmColumn.Unique = this.Unique; dmColumn.SetOrdinal(this.Ordinal); return(dmColumn); }
/// <summary> /// if scale is sup to 0 then return numeric otherwise, return a float /// </summary> private static DmColumn MySql_Double_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = -1; if (mySqlColumn.Scale > 0) { sqlColumn.DbType = DbType.VarNumeric; sqlColumn.OriginalDbType = "SqlDbType.Numeric"; sqlColumn.OriginalTypeName = "numeric"; } else { sqlColumn.DbType = DbType.Double; // if we convert from double, the float will be 53 or if we convert from float it will be 24 sqlColumn.Precision = mySqlColumn.OriginalTypeName.ToLowerInvariant() == "double" ? (byte)53 : (byte)24; sqlColumn.Scale = 0; sqlColumn.OriginalDbType = "SqlDbType.Float"; sqlColumn.OriginalTypeName = "float"; } return(sqlColumn); }
/// <summary> /// Converts to Varbinary(N) /// </summary> private static DmColumn MySql_VarBinary_To_SqlServer(DmColumn mySqlColumn) { var maxlen = mySqlColumn.MaxLength; // check length. // Exception on 0 and go to Varbinary(max) if > 8000 if (maxlen == 0) { maxlen = 1; } else if (maxlen > 8000) { maxlen = -1; } var sqlColumn = mySqlColumn.Clone(); sqlColumn.DbType = DbType.Binary; sqlColumn.MaxLength = maxlen; sqlColumn.OriginalDbType = "SqlDbType.VarBinary"; sqlColumn.OriginalTypeName = "varbinary"; return(sqlColumn); }
internal static MySqlParameter GetMySqlParameter(this DmColumn column) { MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata(); MySqlParameter sqlParameter = new MySqlParameter { ParameterName = $"in{column.ColumnName}", DbType = column.DbType, IsNullable = column.AllowDBNull }; (byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType); if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double || sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0) { sqlParameter.Precision = precision; if (scale > 0) { sqlParameter.Scale = scale; } } else if (column.MaxLength > 0) { sqlParameter.Size = (int)column.MaxLength; } else if (sqlParameter.DbType == DbType.Guid) { sqlParameter.Size = 36; } else { sqlParameter.Size = -1; } return(sqlParameter); }
internal static NpgsqlParameter GetPostgreSqlParameter(this DmColumn column) { var mySqlDbMetadata = new PostgreSqlDbMetadata(); var sqlParameter = new NpgsqlParameter { ParameterName = $"{PostgreSqlBuilderProcedure.PgsqlPrefixParameter}{column.ColumnName}", DbType = column.DbType, IsNullable = column.AllowDBNull }; (byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.Precision, column.Scale, column.Table.OriginalProvider, PostgreSqlSyncProvider.ProviderType); if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double || sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0) { sqlParameter.Precision = precision; if (scale > 0) { sqlParameter.Scale = scale; } } else if (column.MaxLength > 0) { sqlParameter.Size = column.MaxLength; } else if (sqlParameter.DbType == DbType.Guid) { //sqlParameter.Size = 36; } else { sqlParameter.Size = -1; } return(sqlParameter); }
public override bool IsValid(DmColumn columnDefinition) { switch (columnDefinition.OriginalTypeName.ToLowerInvariant()) { case "bigint": case "binary": case "bit": case "char": case "date": case "datetime": case "datetime2": case "datetimeoffset": case "decimal": case "float": case "int": case "money": case "nchar": case "numeric": case "nvarchar": case "real": case "smalldatetime": case "smallint": case "smallmoney": case "sql_variant": case "variant": case "time": case "timestamp": case "tinyint": case "uniqueidentifier": case "varbinary": case "varchar": case "xml": return(true); } return(false); }
public DmRelationTests() { _set = new DmSet(); clientTable = new DmTable("Client"); clientTypeTable = new DmTable("TypeClient"); _set.Tables.Add(clientTable); _set.Tables.Add(clientTypeTable); DmColumn Col0 = new DmColumn <int>("ClientId"); DmColumn Col1 = new DmColumn <int>("ClientType"); DmColumn Col2 = new DmColumn <String>("ClientName"); clientTable.Columns.Add(Col0); clientTable.Columns.Add(Col1); clientTable.Columns.Add(Col2); clientTable.PrimaryKey = new DmKey(Col0); DmColumn Col3 = new DmColumn <int>("TypeId"); DmColumn Col4 = new DmColumn <string>("TypeName"); clientTypeTable.Columns.Add(Col3); clientTypeTable.Columns.Add(Col4); clientTypeTable.PrimaryKey = new DmKey(Col3); }
internal static string GetSqlTypePrecisionString(this DmColumn column) { string sizeString = string.Empty; switch (column.DbType) { case DbType.AnsiString: case DbType.AnsiStringFixedLength: case DbType.Binary: case DbType.String: case DbType.StringFixedLength: if (column.MaxLength > 0) { sizeString = $"({column.MaxLength})"; } else { sizeString = $"(MAX)"; } break; case DbType.Decimal: case DbType.Double: case DbType.Single: case DbType.VarNumeric: if (!column.PrecisionSpecified || !column.ScaleSpecified) { break; } sizeString = $"({ column.Precision}, {column.Scale})"; break; } return(sizeString); }
public override bool IsValid(DmColumn columnDefinition) { switch (columnDefinition.OriginalTypeName.ToLowerInvariant()) { case "int": case "int4": case "integer": case "int8": case "int2": case "bigint": case "smallint": case "serial": case "bigserial": case "float8": case "float4": case "real": case "smallserial": case "serial2": case "serial4": case "varchar": case "char": case "text": case "bit": case "bytea": case "xml": case "timestamp": case "date": case "time": case "uuid": case "character varying": case "timestamp without time zone": case "time without time zone": return(true); } return(false); }
private SqlMetaData GetSqlMetadaFromType(DmColumn column) { SqlMetaData smd = null; var sqlDbType = column.GetSqlDbType(); var dbType = column.DbType; var precision = column.GetSqlTypePrecision(); int maxLength = column.MaxLength; if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar) { maxLength = column.MaxLength <= 0 ? ((sqlDbType == SqlDbType.NVarChar) ? 4000 : 8000) : column.MaxLength; return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength)); } if (column.DataType == typeof(char)) { return(new SqlMetaData(column.ColumnName, sqlDbType, 1)); } smd = new SqlMetaData(column.ColumnName, sqlDbType); return(smd); }
public List <DmColumn> GetTableDefinition() { List <DmColumn> columns = new List <DmColumn>(); // Get the columns definition var dmColumnsList = PostgreSqlManagementUtils.ColumnsForTable(_sqlConnection, _sqlTransaction, TableName); var postgreSqlDbMetadata = new PostgreSqlDbMetadata(); foreach (var c in dmColumnsList.Rows.OrderBy(r => (int)r["ordinal_position"])) { var typeName = c["data_type"].ToString(); var name = c["column_name"].ToString(); // Gets the datastore owner dbType var datastoreDbType = (NpgsqlDbType)postgreSqlDbMetadata.ValidateOwnerDbType(typeName, false, false); // once we have the datastore type, we can have the managed type Type columnType = postgreSqlDbMetadata.ValidateType(datastoreDbType); var dbColumn = DmColumn.CreateColumn(name, columnType); dbColumn.OriginalTypeName = typeName; dbColumn.SetOrdinal(Convert.ToInt32(c["ordinal_position"])); var maxLengthLong = c["character_octet_length"] != DBNull.Value ? Convert.ToInt64(c["character_octet_length"]) : 0; dbColumn.MaxLength = maxLengthLong > Int32.MaxValue ? Int32.MaxValue : (Int32)maxLengthLong; dbColumn.Precision = c["numeric_precision"] != DBNull.Value ? Convert.ToByte(c["numeric_precision"]) : (byte)0; dbColumn.Scale = c["numeric_scale"] != DBNull.Value ? Convert.ToByte(c["numeric_scale"]) : (byte)0; dbColumn.AllowDBNull = (String)c["is_nullable"] != "NO"; dbColumn.AutoIncrement = typeName.Contains("serial"); dbColumn.IsUnsigned = false; columns.Add(dbColumn); } return(columns); }
public void AddFilterColumn(DmColumn filterColumn) { bool alreadyOpened = this.connection.State == ConnectionState.Open; try { using (var command = new SqlCommand()) { if (!alreadyOpened) { this.connection.Open(); } if (this.transaction != null) { command.Transaction = this.transaction; } command.CommandText = this.AddFilterColumnCommandText(filterColumn); command.Connection = this.connection; command.ExecuteNonQuery(); } } catch (Exception ex) { Debug.WriteLine($"Error during CreateIndex : {ex}"); throw; } finally { if (!alreadyOpened && this.connection.State != ConnectionState.Closed) { this.connection.Close(); } } }
private SqlMetaData GetSqlMetadaFromType(DmColumn column) { var dbType = column.DbType; var precision = column.Precision; int maxLength = column.MaxLength; SqlDbType sqlDbType = (SqlDbType)this.sqlMetadata.TryGetOwnerDbType(column.OriginalDbType, column.DbType, false, false, this.TableDescription.OriginalProvider, SqlSyncProvider.ProviderType); // TODO : Since we validate length before, is it mandatory here ? if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar) { maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? 8000 : Convert.ToInt32(column.MaxLength); maxLength = sqlDbType == SqlDbType.NVarChar ? Math.Min(maxLength, 4000) : Math.Min(maxLength, 8000); return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength)); } if (column.DataType == typeof(char)) { return(new SqlMetaData(column.ColumnName, sqlDbType, 1)); } if (sqlDbType == SqlDbType.Char || sqlDbType == SqlDbType.NChar) { maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? (sqlDbType == SqlDbType.NChar ? 4000 : 8000) : Convert.ToInt32(column.MaxLength); return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength)); } if (sqlDbType == SqlDbType.Binary || sqlDbType == SqlDbType.VarBinary) { maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? 8000 : Convert.ToInt32(column.MaxLength); return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength)); } return(new SqlMetaData(column.ColumnName, sqlDbType)); }
public string CreateTableCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"CREATE TABLE {trackingName.FullQuotedString} ("); // Adding the primary key foreach (DmColumn pkColumn in this.tableDescription.PrimaryKey.Columns) { var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`").FullQuotedString; var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.DbType, false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var unQuotedColumnType = new ObjectNameParser(columnTypeString, "`", "`").FullUnquotedString; var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.DbType, false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var columnType = $"{unQuotedColumnType} {columnPrecisionString}"; stringBuilder.AppendLine($"{quotedColumnName} {columnType} NOT NULL, "); } // adding the tracking columns stringBuilder.AppendLine($"`create_scope_id` VARCHAR(36) NULL, "); stringBuilder.AppendLine($"`update_scope_id` VARCHAR(36) NULL, "); stringBuilder.AppendLine($"`create_timestamp` BIGINT NULL, "); stringBuilder.AppendLine($"`update_timestamp` BIGINT NULL, "); stringBuilder.AppendLine($"`timestamp` BIGINT NULL, "); stringBuilder.AppendLine($"`sync_row_is_tombstone` BIT NOT NULL default 0 , "); stringBuilder.AppendLine($"`last_change_datetime` DATETIME NULL, "); if (this.Filters != null && this.Filters.Count > 0) { foreach (var filter in this.Filters) { var columnFilter = this.tableDescription.Columns[filter.ColumnName]; if (columnFilter == null) { throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}"); } var isPk = this.tableDescription.PrimaryKey.Columns.Any(dm => this.tableDescription.IsEqual(dm.ColumnName, filter.ColumnName)); if (isPk) { continue; } var quotedColumnName = new ObjectNameParser(columnFilter.ColumnName, "`", "`").FullQuotedString; var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(columnFilter.OriginalDbType, columnFilter.DbType, false, false, columnFilter.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var unQuotedColumnType = new ObjectNameParser(columnTypeString, "`", "`").FullUnquotedString; var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(columnFilter.OriginalDbType, columnFilter.DbType, false, false, columnFilter.MaxLength, columnFilter.Precision, columnFilter.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType); var columnType = $"{unQuotedColumnType} {columnPrecisionString}"; var nullableColumn = columnFilter.AllowDBNull ? "NULL" : "NOT NULL"; stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, "); } } stringBuilder.Append(" PRIMARY KEY ("); for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++) { DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i]; var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`").QuotedObjectName; stringBuilder.Append(quotedColumnName); if (i < this.tableDescription.PrimaryKey.Columns.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append("))"); return(stringBuilder.ToString()); }
public string ScriptPopulateNewFilterColumnFromBaseTable(DmColumn filterColumn) { throw new NotImplementedException(); }
public override bool ValidateIsReadonly(DmColumn columnDefinition) { return(columnDefinition.OriginalTypeName.ToLowerInvariant() == "timestamp"); }