读取数据库表设计

Question

我正在读取 dBase 数据库，然后将该表发送到 SQL Server 2008。

我现在的问题是如何正确获取列类型，现在我在所有字符类型上获取 varchar(255) 。

我知道我很久以前就做过一次，但不记得我是怎么做到的。

从 dBase 数据库导出到数据集：

 public DataSet getDataSetFromDBF(string path, string fileName)
 {
        OdbcConnection conn = new OdbcConnection(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277");
        String query = @"SELECT * FROM " + @"C:\spcs\" + fileName.ToString() + ".dbf";
        try
        {
            OdbcCommand MyCommand = new OdbcCommand(query, conn);
            OdbcDataAdapter adapter = new OdbcDataAdapter(MyCommand); 
            DataSet ds = new DataSet();                
            adapter.FillSchema(ds, SchemaType.Mapped);
            conn.Open();
            adapter.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            exceptionLog(ex);
            return null
        }
}

然后导出到 MSSQL Server：

private bool createSqlQueryFromDbfFile(string fileName)
{
    StringBuilder sb = new StringBuilder();
    string query = "";
    string pathToDB = @"c:\spcs\";
    string tableName = fileName.Replace(".dbf", "");
    sb.Append("IF OBJECT_ID('" + tableName + "', 'U') IS NOT NULL " + Environment.NewLine);
    sb.Append("DROP TABLE " + tableName + ";" + Environment.NewLine);
    sb.Append("CREATE TABLE " + tableName + Environment.NewLine);
    sb.Append("(" + Environment.NewLine);
    webHallDB.DAL db = new webHallDB.DAL();        
    try
    {
        DataSet ds = db.getDataSetFromDBF(pathToDB, tableName);

        int kolumner = ds.Tables[0].Columns.Count;

        string[] kolumnNamn = new string[kolumner];
        bool primaryKeySet = false;
        for (int i = 0; i < kolumner; i++)
        {
            if (primaryKeySet == false)
            {
                sb.Append("id int PRIMARY KEY IDENTITY," + Environment.NewLine);
                primaryKeySet = true;
            }
            string kolumnTyp = getColumnType(ds.Tables[0].Columns[i]);
            // Sista kolumnen
            if (i == kolumner - 1)
            {

                kolumnNamn[i] = ds.Tables[0].Columns[i].ColumnName;
                sb.Append(ds.Tables[0].Columns[i].ColumnName + " " + kolumnTyp + ");" + Environment.NewLine);
            }
            else
            {
                kolumnNamn[i] = ds.Tables[0].Columns[i].ColumnName;
                sb.Append(ds.Tables[0].Columns[i].ColumnName + " " + kolumnTyp + "," + Environment.NewLine);
            }
        }            

        int rader = ds.Tables[0].Rows.Count;
        DateTime startTid = DateTime.Now;

        StringBuilder sbInsert = new StringBuilder();

        for (int row = 0; row < rader; row++)
        {
            sbInsert.Append(Environment.NewLine + "Insert Into " + tableName);

            sbInsert.Append(Environment.NewLine + " Values (");
            for (int col = 0; col < kolumner; col++)
            {
                if (col == (kolumner - 1))
                {
                    sbInsert.Append("'" + changeSpecialCharacters(ds.Tables[0].Rows[row][col].ToString()) + "');");
                }
                else
                {
                    sbInsert.Append("'" + changeSpecialCharacters(ds.Tables[0].Rows[row][col].ToString()) + "', ");
                }
            }
        }

        query = sb.ToString();
        string insertQuery = sbInsert.ToString();

        DateTime slutTid = DateTime.Now;

        db.executeQuery(query);
        if (db.executeQuery(insertQuery))
        {
            slutTid = DateTime.Now;
            logToGui("Tid för att köra query: " + (slutTid - startTid).TotalSeconds);
            return true;
        }
        else
        {
            slutTid = DateTime.Now;
            logToGui("Tid för att köra query: (failed)" + (slutTid - startTid).TotalSeconds);
            return false;
        }
    }
    catch (Exception ex)
    {
        logToGui("createSqlQueryFromDbfFile misslyckades!");
        logToGui(ex.Message);
        return false;
    }
}

Answer 1

我设法弄清楚：

adapter.FillSchema(ds, SchemaType.Mapped); 

然后我能够使用 DataColumn.MaxLength。