/// <summary> /// bit type in my sql could have a precision, specifying how much bits I can store /// for example : bit(4) can store a value from (mysql syntax) b'0000' to b'1111' (so 0 to 15) /// so if bit precision inf or eq to 1 so we have a sql server bit, otherwise, we have a binary data /// </summary> private static DmColumn MySql_Bit_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); var precision = mySqlColumn.Precision; if (precision <= 1) { sqlColumn.DataType = typeof(Boolean); sqlColumn.Precision = 1; sqlColumn.MaxLength = 1; sqlColumn.DbType = DbType.Boolean; sqlColumn.OriginalDbType = "SqlDbType.Bit"; sqlColumn.OriginalTypeName = "bit"; return(sqlColumn); } // getting the ceiling value to get the binary length we need var binaryLength = Convert.ToInt32(Math.Ceiling(precision / 8d)); sqlColumn.DataType = typeof(Byte[]); sqlColumn.Precision = 0; sqlColumn.MaxLength = binaryLength; sqlColumn.DbType = DbType.Binary; sqlColumn.OriginalDbType = "SqlDbType.Binary"; sqlColumn.OriginalTypeName = "binary"; return(sqlColumn); }
/// <summary> /// Convert a Sql column to MySql column /// </summary> private static DmColumn GetMySqlColumnFromSql(DmColumn sqlColumn) { var mySqlColumn = sqlColumn.Clone(); mySqlColumn.OriginalTypeName = "mysql"; // get the sql type (varchar, int, bigint and so on ..) var oType = sqlColumn.OriginalTypeName.ToLowerInvariant(); if (oType == "int") { return(mySqlColumn); } if (oType == "bigint") { return(mySqlColumn); } if (oType == "smallint") { return(mySqlColumn); } if (oType == "tinyint") { // tinyint is unsigned in SQL Server // mySql can store a tinyint to 255 if set as unsigned mySqlColumn.IsUnsigned = true; return(mySqlColumn); } return(mySqlColumn); }
/// <summary> /// Converts to int if [signed] else to bigint if [unsigned] && type is "int" /// </summary> private static DmColumn MySql_Int_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.Scale = 0; sqlColumn.Precision = 10; // if it's an int (not mediumint) check if unsigned if (mySqlColumn.IsUnsigned && mySqlColumn.OriginalTypeName.ToLowerInvariant() != "mediumint") { sqlColumn.MaxLength = 8; sqlColumn.DbType = DbType.Int64; sqlColumn.OriginalDbType = "SqlDbType.BigInt"; sqlColumn.OriginalTypeName = "bigint"; } else { sqlColumn.MaxLength = 4; sqlColumn.DbType = DbType.Int32; sqlColumn.OriginalDbType = "SqlDbType.Int"; sqlColumn.OriginalTypeName = "int"; } return(sqlColumn); }
/// <summary> /// Convert to NVarchar (max if len > 4000) /// </summary> private static DmColumn MySql_Varchar_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); var maxlen = mySqlColumn.MaxLength; // check length. // Exception on 0 and go to NVachar(max) if > 4000 if (maxlen == 0) { maxlen = 1; } else if (maxlen > 4000) { maxlen = -1; } sqlColumn.MaxLength = maxlen; sqlColumn.Precision = 0; sqlColumn.Scale = 0; sqlColumn.DbType = DbType.String; sqlColumn.OriginalDbType = "SqlDbType.NVarchar"; sqlColumn.OriginalTypeName = "nvarchar"; return(sqlColumn); }
private static DmColumn MySql_Date_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = 1; sqlColumn.DbType = DbType.Date; sqlColumn.OriginalDbType = "SqlDbType.Date"; sqlColumn.OriginalTypeName = "date"; return(sqlColumn); }
/// <summary> /// Binary is pretty the same. Just be careful a minimum Maxlength (1) is set /// </summary> private static DmColumn MySql_Binary_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.DbType = DbType.Binary; sqlColumn.maxLength = Math.Min(mySqlColumn.MaxLength, 1); sqlColumn.OriginalDbType = "SqlDbType.Binary"; sqlColumn.OriginalTypeName = "binary"; return(sqlColumn); }
/// <summary> /// Converts to Varbinary(Max) /// </summary> private static DmColumn MySql_Blob_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.DbType = DbType.Binary; sqlColumn.maxLength = -1; sqlColumn.OriginalDbType = "SqlDbType.VarBinary"; sqlColumn.OriginalTypeName = "varbinary"; return(sqlColumn); }
/// <summary> /// Bigint is the same. Just the max length (octet length) from Sql Server could be useful /// Even if the MySql Bigint is unsigned, we get bigint /// </summary> private static DmColumn MySql_BigInt_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = 8; sqlColumn.DbType = DbType.Int64; sqlColumn.OriginalDbType = "SqlDbType.BigInt"; sqlColumn.OriginalTypeName = "bigint"; sqlColumn.IsUnsigned = false; return(sqlColumn); }
private static DmColumn MySql_TimeStamp_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = 8; sqlColumn.Precision = 0; sqlColumn.Scale = 0; sqlColumn.DbType = DbType.UInt64; sqlColumn.OriginalDbType = "SqlDbType.BigInt"; sqlColumn.OriginalTypeName = "bigint"; return(sqlColumn); }
/// <summary> /// Convert datetime to sql datetime2 (https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017) /// </summary> private static DmColumn MySql_Datetime_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = 8; sqlColumn.Precision = 27; sqlColumn.Scale = 7; sqlColumn.DbType = DbType.DateTime2; sqlColumn.OriginalDbType = "SqlDbType.DateTime2"; sqlColumn.OriginalTypeName = "datetime2"; return(sqlColumn); }
private static DmColumn MySql_Decimal_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = -1; sqlColumn.Precision = Math.Min((byte)38, mySqlColumn.Precision); sqlColumn.Scale = Math.Min((byte)30, mySqlColumn.Scale); sqlColumn.DbType = DbType.Decimal; sqlColumn.OriginalDbType = "SqlDbType.Decimal"; sqlColumn.OriginalTypeName = "decimal"; return(sqlColumn); }
private static DmColumn MySql_Text_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = -1; sqlColumn.Precision = 0; sqlColumn.Scale = 0; sqlColumn.DbType = DbType.String; sqlColumn.OriginalDbType = "SqlDbType.NVarchar"; sqlColumn.OriginalTypeName = "nvarchar"; return(sqlColumn); }
/// <summary> /// Converts to smallint /// </summary> private static DmColumn MySql_Tinyint_To_SqlServer(DmColumn mySqlColumn) { var sqlColumn = mySqlColumn.Clone(); sqlColumn.MaxLength = -1; sqlColumn.Precision = 0; sqlColumn.Scale = 0; sqlColumn.DbType = DbType.Int16; sqlColumn.OriginalDbType = "SqlDbType.SmallInt"; sqlColumn.OriginalTypeName = "smallint"; return(sqlColumn); }
private static DmColumn ToSqliteInt(DmColumn mySqlColumn) { var column = mySqlColumn.Clone(); column.MaxLength = -1; column.Scale = 0; column.Precision = 0; column.DbType = DbType.Int64; column.OriginalDbType = "DbType.Int64"; column.OriginalTypeName = "integer"; return(column); }
private static DmColumn ToSqliteBlob(DmColumn mySqlColumn) { var column = mySqlColumn.Clone(); column.MaxLength = -1; column.Scale = 0; column.Precision = 0; column.DbType = DbType.Binary; column.OriginalDbType = "DbType.Binary"; column.OriginalTypeName = "binary"; return(column); }
private static DmColumn ToSqliteDate(DmColumn mySqlColumn) { var column = mySqlColumn.Clone(); column.MaxLength = -1; column.Scale = 0; column.Precision = 0; column.DbType = DbType.DateTime; column.OriginalDbType = "DbType.DateTime"; column.OriginalTypeName = "datetime"; return(column); }
private static DmColumn ToSqliteText(DmColumn mySqlColumn) { var column = mySqlColumn.Clone(); // No need to precise max length for Sqlite column.MaxLength = -1; column.Scale = 0; column.Precision = 0; column.DbType = DbType.String; column.OriginalDbType = "DbType.String"; column.OriginalTypeName = "text"; return(column); }
private static DmColumn ToSqliteNumeric(DmColumn mySqlColumn) { var column = mySqlColumn.Clone(); column.MaxLength = -1; // no need to specify scale and precision for Sqlite column.Scale = 0; column.Precision = 0; column.DbType = DbType.VarNumeric; column.OriginalDbType = "DbType.VarNumeric"; column.OriginalTypeName = "numeric"; return(column); }
/// <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); }
/// <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); }