为什么不能MySQL连接器.. NET正确地将类型映射为c#类型
本文关键字：类型 映射 正确地 不能 MySQL 连接器 NET 为什么 | 更新日期: 2023-09-27 18:09:30
MySQL表定义:
CREATE TABLE `table` (
`id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment ID',
`data_provider_id` tinyint(1) unsigned NOT NULL COMMENT 'Data provider ID',
`is_active` bit(1) NOT NULL DEFAULT b'1' COMMENT '0 = retired source',
PRIMARY KEY (`id`),
KEY `DATA_PROVIDER_ID` (`data_provider_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
public static class MySqlConnector
{
public static DataTable QueryDatabase(
MySqlConnection mysqlConnection,
string mysqlQuery)
{
MySqlCommand mysqlCommand = new MySqlCommand(mysqlQuery, mysqlConnection);
MySqlDataReader mysqlDataReader = mysqlCommand.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(mysqlDataReader);
return dataTable;
}
}
…
mysqlQuery = @"
SELECT
`id`,
`data_provider_id`,
`is_active`
FROM
`{0}`;
";
mysqlQuery = String.Format(mysqlQuery, tableName);
DataTable dt = MySqlConnector.QueryDatabase(mysqlConnection, mysqlQuery);
现在，当我在dt字段上调用GetType()时，它说id是Int32(而不是Byte)， data_provider_id是Byte(令人惊讶的正确)，is_active是Uint64(而不是Boolean)。
情况更糟。我有另一个表，其中id字段是int(1) unsigned, QueryDatabase()仍然返回它作为一个有符号整型。
PowerShell快速测试代码:
# Importing MySQL Connector.NET DLL.
$sMySqlServer = "server"
$sPath = "''{0}'Connector.NET 6.9.7'Assemblies'v4.5'MySql.Data.dll" `
-f $sMySqlServer
Add-Type -Path $sPath
# Creating MySQL connection.
$sMySqlUser = "user"
$sMySqlPassword = "password"
$sMySqlDatabase = "test"
$sMySqlConnectionString = "server={0};uid={1};pwd={2};database={3};" `
-f $sMySqlServer, $sMySqlUser, $sMySqlPassword, $sMySqlDatabase
$oMySqlConnection = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$oMySqlConnection.ConnectionString = $sMySqlConnectionString
$oMySqlConnection.Open()
# Getting raw data.
$sMySqlQuery = "SELECT * FROM `table` ;"
$oMySqlCommand = New-Object -TypeName `
MySql.Data.MySqlClient.MySqlCommand($sMySqlQuery, $oMySqlConnection)
$oMySqlDataReader = $oMySqlCommand.ExecuteReader()
$oDataTable = New-Object -TypeName System.Data.DataTable
$oDataTable.Load($oMySqlDataReader)
$oDataTable | Get-Member
TypeName: System.Data.DataRow
Name MemberType Definition
---- ---------- ----------
AcceptChanges Method void AcceptChanges()
BeginEdit Method void BeginEdit()
CancelEdit Method void CancelEdit()
ClearErrors Method void ClearErrors()
Delete Method void Delete()
EndEdit Method void EndEdit()
Equals Method bool Equals(System.Object obj)
GetChildRows Method System.Data.DataRow[] GetChildRows(string relat...
GetColumnError Method string GetColumnError(int columnIndex), string ...
GetColumnsInError Method System.Data.DataColumn[] GetColumnsInError()
GetHashCode Method int GetHashCode()
GetParentRow Method System.Data.DataRow GetParentRow(string relatio...
GetParentRows Method System.Data.DataRow[] GetParentRows(string rela...
GetType Method type GetType()
HasVersion Method bool HasVersion(System.Data.DataRowVersion vers...
IsNull Method bool IsNull(int columnIndex), bool IsNull(strin...
RejectChanges Method void RejectChanges()
SetAdded Method void SetAdded()
SetColumnError Method void SetColumnError(int columnIndex, string err...
SetModified Method void SetModified()
SetParentRow Method void SetParentRow(System.Data.DataRow parentRow...
ToString Method string ToString()
Item ParameterizedProperty System.Object Item(int columnIndex) {get;set;},...
data_provider_id Property byte data_provider_id {get;set;}
id Property int id {get;set;}
is_active Property uint64 is_active {get;set;}
SSDD
虽然这不是一个完整的解决方案，但它把我推向了正确的方向。
DataTable.Load()中的类型转换确实有些损坏，因此您需要在调用Load()之前向DataTable添加基本模式。下面的可能不是完美的，但它对我有用:
private static void TranslateSchema(
DataTable dataTable,
MySqlDataReader mysqlDataReader)
{
string columnName;
Type columnType;
mysqlDataReader.Read();
for (int i = 0; i < mysqlDataReader.FieldCount; i++)
{
columnName = mysqlDataReader.GetName(i);
columnType = mysqlDataReader.GetFieldType(i);
dataTable.Columns.Add(columnName, columnType);
}
}
bit(1)仍然是UInt64，但我可以忍受。
使用的是什么版本的Connector/Net ?在6.9.7版本中，我无法重现此问题。
MySQL:CREATE TABLE `table` (
`id` TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment ID',
`data_provider_id` TINYINT(1) UNSIGNED NOT NULL COMMENT 'Data provider ID',
`is_active` BIT(1) NOT NULL DEFAULT b'1' COMMENT '0 = retired source',
`id_temp` INT(1) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `DATA_PROVIDER_ID` (`data_provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
...
DataTable schema = dr.GetSchemaTable();
...
`id` -> System.Byte
`data_provider_id` -> System.Byte
`is_active` -> System.UInt64
`id_temp` -> System.UInt32
6.2.3.1集合
...
DataTable table = mysqlConnection.GetSchema("DataTypes");
...
TypeName = BIT
...
CreateFormat = BIT
...
DataType = System.UInt64
...
============================
TypeName = TINY INT
...
CreateFormat = TINYINT UNSIGNED
...
DataType = System.Byte
...
============================
TypeName = INT
...
CreateFormat = INT UNSIGNED
...
DataType = System.UInt32
...