title: Reading a dbase table design
body: <p>I'm reading a dBase database and then sending that table to a SQL Server 2008.</p>
  
  <p>My problem now is how am i able to get the column type correctly, right now i'm getting varchar(255) on all character types.</p>
  
  <p>I know i did this once long time ago but cant remember how i did it.</p>
  
  <p>Export from dBase database to a dataset:</p>
  
  <pre><code> 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
          }
  }
  </code></pre>
  
  <p>And then to MSSQL Server:</p>
  
  <pre><code>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 &lt; 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 &lt; rader; row++)
          {
              sbInsert.Append(Environment.NewLine + "Insert Into " + tableName);
  
              sbInsert.Append(Environment.NewLine + " Values (");
              for (int col = 0; col &lt; 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;
      }
  }
  </code></pre>