public override string GetNativeDbTypeName(IDataParameter para) { MySqlParameter mysqlPara = para as MySqlParameter; MySqlDbType dbType = mysqlPara.MySqlDbType; if (dbType == MySqlDbType.Int32) { return("INT"); } else { return(dbType.ToString()); } }
public override string GetCommand(Column column, string parameterName, System.Data.ParameterDirection parameterDirection, DataRowVersion dataRowVersion, bool isNullable) { MySqlDbType mySqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), column.OriginalSQLType); return(string.Format("MySql.Data.MySqlClient.MySqlParameter(\"{0}\", MySql.Data.MySqlClient.MySqlDbType.{1}, {2}, System.Data.ParameterDirection.{3}, {4}, (System.Byte)({5}), (System.Byte)({6}), \"{7}\", System.Data.DataRowVersion.{8}, null)", parameterName, /*The name of the parameter*/ mySqlDbType.ToString(), /*One of the OleDbType values*/ column.Length.ToString(), /*The length of the parameter*/ parameterDirection.ToString(), /*One of the ParameterDirection values*/ isNullable.ToString(), column.Precision.ToString(), /*The total number of digits to the left and right of the decimal point to which Value is resolved*/ column.Scale.ToString(), /*The total number of decimal places to which Value is resolved*/ column.Name, /*The name of the source column*/ dataRowVersion.ToString() )); }
public override string GetNativeDbTypeName(IDataParameter para) { MySqlParameter mysqlPara = para as MySqlParameter; MySqlDbType dbType = mysqlPara.MySqlDbType; //类型和名字转换,请参考:http://stackoverflow.com/questions/1230203/how-can-i-determine-the-column-type-represented-by-a-mysql-net-connectors-mysq switch (dbType) { case MySqlDbType.String: return("CHAR"); case MySqlDbType.NewDecimal: return("NUMERIC"); case MySqlDbType.Byte: case MySqlDbType.UByte: return("TINYINT"); case MySqlDbType.Int16: case MySqlDbType.UInt16: return("SMALLINT"); case MySqlDbType.Int24: case MySqlDbType.UInt24: return("MEDIUMINT"); case MySqlDbType.Int32: case MySqlDbType.UInt32: return("INT"); case MySqlDbType.Int64: case MySqlDbType.UInt64: return("BIGINT"); case MySqlDbType.Float: return("REAL"); default: return(dbType.ToString()); } }
public static DbType MySqlDbType2DbType(MySqlDbType mySqlDbType_in) { switch (mySqlDbType_in) { case MySqlDbType.Float: { return DbType.Double; } case MySqlDbType.Double: { return DbType.Double; } case MySqlDbType.Decimal: { return DbType.Decimal; } case MySqlDbType.Date: { return DbType.Date; } case MySqlDbType.Datetime: { return DbType.DateTime; } case MySqlDbType.Timestamp: { return DbType.DateTime; } // ToDos: here! check if appropriate case MySqlDbType.Time: { return DbType.Time; } case MySqlDbType.Year: { return DbType.DateTime; } // ToDos: here! check if appropriate // case MySqlDbType.TinyBlob: // case MySqlDbType.Blob: // case MySqlDbType.MediumBlob: // case MySqlDbType.LongBlob: case MySqlDbType.VarChar: { return DbType.String; } // ToDos: here! check if appropriate // case MySqlDbType.Geometry: { return DbType.Geometry; } case MySqlDbType.Bit: { return DbType.Boolean; } // ToDos: here! check if appropriate case MySqlDbType.Byte: { return DbType.Byte; } case MySqlDbType.Int16: { return DbType.Int16; } // case MySqlDbType.Int24: { return DbType.Int24; } case MySqlDbType.Int32: { return DbType.Int32; } case MySqlDbType.Int64: { return DbType.Int64; } #region default: throw new Exception("..."); default: { throw new Exception(string.Format( "undefined variable type: {0}", mySqlDbType_in.ToString() )); } #endregion } }
public void AddProfileColumn( string Name, MySqlDbType columnType, int size) { // get column type... string type = columnType.ToString(); if ( type.Contains("DateTime".ToLower() ) ) { type = "TIMESTAMP"; } if ( type.Contains("VarChar") ) { type = "VARCHAR"; } if ( type.Contains("Int32") ) { type = "INT"; } if ( size > 0 ) { type += "(" + size.ToString() + ")"; } string sql = String.Format( "ALTER TABLE {0} ADD `{1}` {2};", MsSqlDbAccess.GetObjectName( "prov_Profile" ), Name, type ); using ( MySqlCommand cmd = new MySqlCommand( sql ) ) { cmd.CommandType = CommandType.Text; _msSqlDbAccess.ExecuteNonQuery( cmd) ; } }
internal static DbType ConvertFromMySqlDbType(MySqlDbType dbtype) { switch (dbtype) { case MySqlDbType.Decimal: return(DbType.Decimal); case MySqlDbType.Byte: return(DbType.Byte); case MySqlDbType.Int16: return(DbType.Int16); case MySqlDbType.Int24: return(DbType.Int32); case MySqlDbType.Int32: return(DbType.Int32); case MySqlDbType.Int64: return(DbType.Int64); case MySqlDbType.Float: return(DbType.Single); case MySqlDbType.Double: return(DbType.Double); case MySqlDbType.Timestamp: return(DbType.DateTimeOffset); case MySqlDbType.Date: return(DbType.Date); case MySqlDbType.Time: return(DbType.Time); case MySqlDbType.DateTime: return(DbType.DateTime); case MySqlDbType.Year: return(DbType.Int16); case MySqlDbType.Newdate: return(DbType.DateTime2); case MySqlDbType.VarString: return(DbType.String); case MySqlDbType.Bit: return(DbType.Boolean); case MySqlDbType.JSON: return(DbType.String); case MySqlDbType.NewDecimal: return(DbType.Decimal); case MySqlDbType.Enum: return(DbType.Int16); case MySqlDbType.Set: return(DbType.Object); case MySqlDbType.TinyBlob: return(DbType.Binary); case MySqlDbType.MediumBlob: return(DbType.Binary); case MySqlDbType.LongBlob: return(DbType.Binary); case MySqlDbType.Blob: return(DbType.Binary); case MySqlDbType.VarChar: return(DbType.StringFixedLength); case MySqlDbType.String: return(DbType.String); case MySqlDbType.Geometry: return(DbType.Binary); case MySqlDbType.UByte: return(DbType.SByte); case MySqlDbType.UInt16: return(DbType.UInt16); case MySqlDbType.UInt24: return(DbType.UInt32); case MySqlDbType.UInt32: return(DbType.UInt32); case MySqlDbType.UInt64: return(DbType.UInt64); case MySqlDbType.Binary: return(DbType.Binary); case MySqlDbType.VarBinary: return(DbType.Binary); case MySqlDbType.TinyText: return(DbType.String); case MySqlDbType.MediumText: return(DbType.String); case MySqlDbType.LongText: return(DbType.String); case MySqlDbType.Text: return(DbType.String); case MySqlDbType.Guid: return(DbType.Guid); } throw new InvalidCastException("Never reached. " + dbtype.ToString()); }
/// <summary> /// The add profile column. /// </summary> /// <param name="name"> /// The name. /// </param> /// <param name="columnType"> /// The column type. /// </param> /// <param name="size"> /// The size. /// </param> public static void AddProfileColumn([NotNull] string name, MySqlDbType columnType, int size) { // get column type... string type = columnType.ToString(); if (size > 0) { type += "(" + size + ")"; } if (type.IndexOf("Int32") >= 0) { type = "INT"; } string sql = "ALTER TABLE {0} ADD {1} {2}".FormatWith( MsSqlDbAccess.GetObjectName("UserProfile"), name, type); using (var cmd = MsSqlDbAccess.GetCommand(sql, true)) { cmd.CommandType = CommandType.Text; MsSqlDbAccess.Current.ExecuteNonQuery(cmd); } }
public override string ToQueryString(QueryFormatOptions formatFlags) { if (this.runtimeOnlyType != null) { return(this.runtimeOnlyType.ToString()); } var builder = new StringBuilder(); switch (this.sqlDbType) { case MySqlDbType.Byte: case MySqlDbType.Int16: case MySqlDbType.Int24: case MySqlDbType.Int32: case MySqlDbType.Int64: case MySqlDbType.Timestamp: case MySqlDbType.Date: case MySqlDbType.Time: case MySqlDbType.DateTime: case MySqlDbType.Year: case MySqlDbType.Enum: case MySqlDbType.Set: case MySqlDbType.TinyBlob: case MySqlDbType.MediumBlob: case MySqlDbType.LongBlob: case MySqlDbType.Blob: case MySqlDbType.UByte: case MySqlDbType.UInt16: case MySqlDbType.UInt24: case MySqlDbType.UInt32: case MySqlDbType.UInt64: case MySqlDbType.TinyText: case MySqlDbType.MediumText: case MySqlDbType.LongText: builder.Append(sqlDbType.ToString()); break; case MySqlDbType.Float: case MySqlDbType.Double: builder.Append(sqlDbType); if (this.precision != 0) { builder.Append("("); builder.Append(this.precision); if (this.scale != 0) { builder.Append(","); builder.Append(this.scale); } builder.Append(")"); } break; case MySqlDbType.VarString: case MySqlDbType.Binary: case MySqlDbType.VarBinary: case MySqlDbType.Text: builder.Append(this.sqlDbType); if (!this.size.HasValue || ((this.size == 0) || ((formatFlags & QueryFormatOptions.SuppressSize) != QueryFormatOptions.None))) { break; } builder.Append("("); if (this.size != -1) { builder.Append(this.size); break; } builder.Append("MAX"); break; } return(builder.ToString()); #region MyRegion // switch (this.sqlDbType) // { // case SqlDbType.BigInt: // case SqlDbType.Bit: // case SqlDbType.DateTime: // case SqlDbType.Image: // case SqlDbType.Int: // case SqlDbType.Money: // case SqlDbType.NText: // case SqlDbType.UniqueIdentifier: // case SqlDbType.SmallDateTime: // case SqlDbType.SmallInt: // case SqlDbType.SmallMoney: // case SqlDbType.Text: // case SqlDbType.Timestamp: // case SqlDbType.TinyInt: // case SqlDbType.Xml: // case SqlDbType.Udt: // builder.Append(this.sqlDbType.ToString()); // goto Label_021D; // case SqlDbType.Binary: // case SqlDbType.Char: // case SqlDbType.NChar: // builder.Append(this.sqlDbType); // if ((formatFlags & QueryFormatOptions.SuppressSize) == QueryFormatOptions.None) // { // builder.Append("("); // builder.Append(this.size); // builder.Append(")"); // } // goto Label_021D; // case SqlDbType.Decimal: // case SqlDbType.Float: // case SqlDbType.Real: // builder.Append(this.sqlDbType); // if (this.precision != 0) // { // builder.Append("("); // builder.Append(this.precision); // if (this.scale != 0) // { // builder.Append(","); // builder.Append(this.scale); // } // builder.Append(")"); // } // goto Label_021D; // case SqlDbType.NVarChar: // case SqlDbType.VarBinary: // case SqlDbType.VarChar: // builder.Append(this.sqlDbType); // if (!this.size.HasValue || ((this.size == 0) || ((formatFlags & QueryFormatOptions.SuppressSize) != QueryFormatOptions.None))) // { // goto Label_021D; // } // builder.Append("("); // if (this.size != -1) // { // builder.Append(this.size); // break; // } // builder.Append("MAX"); // break; // case SqlDbType.Variant: // builder.Append("sql_variant"); // goto Label_021D; // default: // goto Label_021D; // } // builder.Append(")"); //Label_021D: // return builder.ToString(); #endregion }
public override DataDomain BuildDomain() { DataDomain domain = new DataDomain(this.Driver.DomainUrl); using (MySqlConnection connection = this.Driver.OpenConnection() as MySqlConnection) { string commandTextTables = string.Format("SELECT Table_Name FROM information_schema.TABLES WHERE Table_Schema=\"{0}\" AND Table_Type=\"Base Table\"", this.Driver.DomainUrl.Database); MySqlCommand command = new MySqlCommand(commandTextTables, connection); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string tableName = reader.GetString(0); Table table = new Table(tableName); domain.AddTable(table); } } string commandTextColumns = string.Format("SELECT table_catalog,table_schema,table_name,column_name,is_nullable,data_type,extra,column_type,column_key,character_maximum_length,numeric_precision,numeric_scale FROM information_schema.COLUMNS WHERE table_schema=\"{0}\"", this.Driver.DomainUrl.Database); command = new MySqlCommand(commandTextColumns, connection); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string tableName = reader.GetString(2); //"TABLE_NAME" Table table = domain[tableName]; if (table != null) { Column column = new Column(); column.Name = reader.GetString(3); //"COLUMN_NAME" string dataType = reader.GetString(5); //"DATA_TYPE" MySqlDbType mySqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), dataType); column.OriginalSQLType = mySqlDbType.ToString(); if (_sqlTypesDictionary.Contains(dataType)) { column.SqlType = (SqlType)_sqlTypesDictionary[dataType]; } else { column.SqlType = SqlType.Unknown; } if (_netDataTypes.ContainsKey(column.SqlType)) { column.NetDataType = (string)_netDataTypes[column.SqlType]; column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string); } else { column.NetDataType = "unknown"; } if ((column.SqlType == SqlType.Char) || (column.SqlType == SqlType.AnsiChar) || (column.SqlType == SqlType.VarChar) || (column.SqlType == SqlType.AnsiVarChar) || (column.SqlType == SqlType.Text) || (column.SqlType == SqlType.Binary) || (column.SqlType == SqlType.VarBinary)) { column.Length = reader.GetInt32(9); //"CHARACTER_MAXIMUM_LENGTH"; } else if (column.SqlType == SqlType.Decimal) { column.Precision = reader.GetByte(10); //numeric_precision column.Scale = reader.GetInt32(11); //"NUMERIC_SCALE" } if ((column.SqlType == SqlType.Binary) || (column.SqlType == SqlType.VarBinary)) { column.IsBlob = true; } if (column.Length == -1) { switch (column.SqlType) { case SqlType.VarChar: column.SqlType = SqlType.VarCharMax; column.Length = 0; break; case SqlType.AnsiVarChar: column.SqlType = SqlType.AnsiVarCharMax; column.Length = 0; break; case SqlType.VarBinary: column.SqlType = SqlType.VarBinaryMax; column.Length = 0; break; default: break; } } if (!reader.IsDBNull(4)) //IS_NULLABLE { string tmp = reader.GetString(4); column.IsNullable = ("yes" == tmp.ToLower()); } if (!reader.IsDBNull(6)) //"extra" { string tmp = reader.GetString(6); column.IsIdentity = ("auto_increment" == tmp.ToLower()); } table.AddColumn(column); } } } string commandTextKeys = string.Format("SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA and kcu.TABLE_NAME = tc.TABLE_NAME WHERE ((tc.CONSTRAINT_TYPE = 'PRIMARY KEY') and (kcu.TABLE_SCHEMA =\"{0}\" )) order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION", this.Driver.DomainUrl.Database); command = new MySqlCommand(commandTextKeys, connection); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string tableName = reader.GetString(1); //"TABLE_NAME" Table table = domain[tableName]; if (table != null) { string columnName = reader.GetString(4); //"COLUMN_NAME" Column column = table[columnName]; if (column != null) { column.IsPrimaryKey = true; } } } } } return(domain); }
private static object GetEnumValue(object value, MySqlDbType mySqlDbType) { if (value == null) { return(DBNull.Value); } object v = null; switch (mySqlDbType) { case MySqlDbType.Bit: { v = Convert.ToBoolean(value); break; } case MySqlDbType.Byte: { v = Convert.ToByte(value); break; } case MySqlDbType.Int32: { v = Convert.ToInt32(value); break; } case MySqlDbType.Int64: { v = Convert.ToInt64(value); break; } case MySqlDbType.Int16: { v = Convert.ToInt16(value); break; } default: { throw new InvalidCastException(string.Format("Value of {0} cannot be converted to {1}", value.GetType().Name, mySqlDbType.ToString())); } } return(v); }