使用OleDbParameter创建用于访问的表 [英] Create Table for Access using OleDbParameter

问题描述

我目前正在将表从SQL Server重建为通过C#访问.

I'm currently rebuilding tables from SQL Server to Access via C#.

为此，我要获取SQL Server中使用的数据类型，并将它们映射到OleDbType对象.

For that I'm getting the data types used in SQL Server and mapping them to OleDbType objects.

不幸的是，每次我尝试执行Access语句时，都会抛出一个异常，表明我的创建表"-语句中存在语法错误.我猜这是因为我只是将映射的数据类型添加为文本而不是OleDbParameters.

Unfortunately, every time I'm trying to execute my statement for Access an exception will be thrown that there is a syntax error in my "Create Table" - statement. I'm guessing this is because I just add the mapped data types as text and not as OleDbParameters.

是否可以创建OleDbParameter-包含创建表"的列名和数据类型的对象-语句?

Is there a way to create OleDbParameter - objects containing the column name and datatype for "Create Table" - statements?

String accessConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data 
Source=" + filepath;

using (OleDbConnection accessConnection = new OleDbConnection(accessConnectionString))
{
     ADOX.Catalog cat = new ADOX.Catalog();
     cat.Create(accessConnectionString);
     OleDbCommand oleCommand = new OleDbCommand();
     oleCommand.Connection = accessConnection;
     oleCommand.CommandType = CommandType.Text;
     accessConnection.Open();

     String columnsCommandText = "(";

     for (int i = 0; i < reader.GetSchemaTable().Rows.Count; i++) // reader contains data from SQL Server 
     {
         var column = reader.GetName(i); // name of attribute
         SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[i]["ProviderType"]; // data type
         var accessType = SQLAccessMapper.MapDataTypes(type);
         columnsCommandText +=  " " + column + " " + accessType + ",";
     }

     columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
     columnsCommandText += ")";

     oleCommand.CommandText = "CREATE TABLE " + tablename + columnsCommandText;

     oleCommand.ExecuteNonQuery(); // Exception

映射器:

static class SQLAccessMapper
{
    public static OleDbType MapDataTypes(SqlDbType sqlDataType)
    {
        switch (sqlDataType)
        {
            case SqlDbType.Int:
                return OleDbType.Integer;
            case SqlDbType.SmallInt:
                return OleDbType.SmallInt;
            case SqlDbType.TinyInt:
                return OleDbType.TinyInt;
            case SqlDbType.Decimal:
                return OleDbType.Decimal;
            case SqlDbType.Float:         
            case SqlDbType.Real:
                return OleDbType.Single;

            case SqlDbType.BigInt:
                return OleDbType.BigInt;
            case SqlDbType.Char:
                return OleDbType.Char;
            case SqlDbType.NChar:
                return OleDbType.WChar;
            case SqlDbType.NText:                
            case SqlDbType.NVarChar:
            case SqlDbType.Text:
                return OleDbType.VarWChar;

            case SqlDbType.VarChar:
                return OleDbType.VarChar;
            case SqlDbType.Time:
                return OleDbType.DBTime;

            case SqlDbType.Date:
                return OleDbType.DBDate;

            case SqlDbType.DateTime:
            case SqlDbType.DateTime2:                  
            case SqlDbType.DateTimeOffset:                   
            case SqlDbType.SmallDateTime:
            case SqlDbType.Timestamp:
                return OleDbType.DBTimeStamp;

            case SqlDbType.Binary:
                return OleDbType.Binary;
            case SqlDbType.VarBinary:
                return OleDbType.VarBinary;

            case SqlDbType.Money:   
            case SqlDbType.SmallMoney:
                return OleDbType.Currency;

            case SqlDbType.Bit:
                return OleDbType.Boolean;
            default: return OleDbType.Error;
        }

    }

}

示例Create Table语句:

CREATE TABLE GrTable(
GrtId Integer, 
CaseId Integer, 
GrDrg VarChar, 
GrDrgText VarWChar, 
Mdc VarChar, 
MdcText VarWChar, 
GrPartition VarChar, 
Baserate Decimal, 
LosUsed Integer, 
Htp Integer, 
PricePerDay Decimal, 
Ltp Integer, 
LtpPricePerDay Decimal, 
AverLos Decimal, 
AverlosPricePerDay Decimal, 
Eff Decimal,
Std Decimal,
Adj Decimal, 
Gst VarChar, 
Pccl Integer,
PriceEff Decimal,
PriceStd Decimal, 
PriceAdj Decimal, 
DaysExcHtp Integer,
DaysBelowLtp Integer, 
DaysBelowAverLos Integer, 
TotalPrice Decimal,
BaseratePeriod VarChar)

推荐答案

您的主要问题是在MapDataTypes中.您应该返回MS-Access引擎期望的字符串，而不要像现在那样依赖于将一般枚举SqlDbType自动转换为字符串.

Your main problem is in the MapDataTypes. You should return the string expected by the MS-Access engine and not rely on the automatic conversion of an the general enum SqlDbType to a string as you do now.

例如.对于类型为VarWChar的列，您需要传递字符串"TEXT"，后跟字段的大小，而对于整数类型的字段，则需要字符串"INT"

For example. For a column of type VarWChar you need to pass the string "TEXT" followed by the size of the field, while, for a field of type integer, you need the string "INT"

public static string MapDataTypes(SqlDbType sqlDataType)
{
    switch (sqlDataType)
    {
        case SqlDbType.Int:
            return "INT";
        case SqlDbType.VarChar:
            return "TEXT(80)";

        ... AND SO ON FOR EVERY POSSIBLE TYPE

   }
}

这当然会引入TEXT字段的大小问题，但是您可以从用于在名为ColumnSize的列中查找类型的同一GetSchemaTable中检索它，并将此大小传递给MapDataTypes方法-

This, of course, introduces the size problem for the TEXT fields, but you can retrieve it from the same GetSchemaTable used to find the type in the column named ColumnSize and pass this size to the MapDataTypes method-

public static string MapDataTypes(SqlDbType sqlDataType, int size)
{
    switch (sqlDataType)
    {
        case SqlDbType.Int:
            return "INT";
        case SqlDbType.VarChar:
            return "TEXT(" + size + )";
        .....


   }
}

您可以在中找到一些允许的类型SQL数据类型

这篇关于使用OleDbParameter创建用于访问的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！