NEW DbfFieldType USING EVAL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WhitePage.Data;
namespace TestDbfRecord
{
  class Program
  {
    static void Main(string[] args)
    {
      //NEW DbfFieldType
      //USING  EVAL
      //create table
      List<DbfFieldInfo> fields = new List<DbfFieldInfo>();

      fields.Add( new DbfFieldInfo("nickname", DbfFieldType.Character ,100 ));
      fields.Add( new DbfFieldInfo("note", DbfFieldType.Memo, 0));
      fields.Add(new DbfFieldInfo("DT", 'D', 8, 0));
      fields.Add(new DbfFieldInfo("NUM", 'N', 6, 2));
      fields.Add(new DbfFieldInfo("LOGI", 'L', 1, 0));

      DbfFieldInfo field = new DbfFieldInfo();
      field = new DbfFieldInfo();
      field.Name = "note";
      field.Type = DbfFieldType.Memo;
      fields.Add(field);

      string file = AppDomain.CurrentDomain.BaseDirectory +"\\demo.dbf";
      using (DbfRecordset rs = DbfRecordset.Create(file, fields))
      {
        eval(rs, "iif(1+1 > 2 , .T. , .F.)");
        eval(rs, ".t. and .t.");
        eval(rs, ".t. .and. .t.");
        eval(rs, ".t. .or. .F.");
        eval(rs, "-1");
        eval(rs, "--1");
        eval(rs, "---1");
        eval(rs, "----1 + -2");
        eval(rs, "! .t. ");
        eval(rs, "not .t. ");
        eval(rs, ".not. .t. ");
        eval(rs, "'abc' == 'abc'");
        eval(rs, "'abc' == 'ab'");
        eval(rs, "'abc' > 'ab'");
        eval(rs, " 200.92 >= 1000.23433");
        eval(rs, " 200.92 != 1000.23433");
        eval(rs, " 'nick' $ 'i'");
        eval(rs, " 'i' $ 'nick'");
        eval(rs, "UPPER('qwerty')");
        eval(rs, "str(100.23) + str(12.34)");
        eval(rs, "'[' + str(12.344, 10) + ']'");
        eval(rs, "'[' + str(12.344, 10,2) + ']'");
        eval(rs, "right('1234567',3)");
        eval(rs, "left('1234567',3)");
        eval(rs, "QUARTER(CTOD('11/12/2012'),1)");
        eval(rs, "day(CTOD('11/12/2012'))");
        eval(rs, "month(CTOD('11/12/2012'))");
        eval(rs, "CTOD('11/12/2012')");
        eval(rs, "STOD('20120131')");
        eval(rs, "empty('20120131')");
        eval(rs, "empty('')");
        eval(rs, "trim(' rwytuwyrt   ')");
        eval(rs, "ltrim(' rwytuwyrt   ')");
        eval(rs, "alltrim(' rwytuwyrt   ')");
        eval(rs, "'[' + padR('123',10) + ']'");
        eval(rs, "'[' + padR('123',10,'W') + ']'");
        eval(rs, "'[' + padL('123',10) + ']'");
        eval(rs, "'[' + padL('123',10,'W') + ']'");
        eval(rs, "soundex('word')");
        eval(rs, "len('word')");
        eval(rs, "val('12.34')");
        eval(rs, "abs(-12.34)");
        Random ran = new Random(1000);
        int recCount = 10;
       
        for (int i = 1; i <= recCount; i++)
        {
          int id = ran.Next(0,1000);
          string nick = "Jon ID :" +  id.ToString();
          System.Diagnostics.Debug.Print(nick);
          rs.AddNew(); // append black
          rs["nickname"] = nick;
          rs.Update(); // commit changes
        }
        rs.ForEach(r =>
          {
            eval(r, "substr(nickname,2,2) + upper(nickname)");
            return false; // only once
          });
        rs.Close();
      }
    }
    static void eval(DbfRecordset rs, string expr)
    {
      object value = rs.Eval(expr);
      System.Diagnostics.Debug.Print( expr + " : " + value.ToString()  );
    }
  }
}

USING MEMO (DBT) and ForEach

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WhitePage.Data;

namespace TestDbfRecord
{
  class Program
  {
    static void Main(string[] args)
    {
      //USING  MEMO (DBT) and ForEach
      //create table
      List<DbFieldInfo> fields = new List<DbFieldInfo>();
      DbFieldInfo field = new DbFieldInfo();
      field.Name = "nickname";
      field.Type = 'C';
      field.Len = 100;
      fields.Add(field);
      field = new DbFieldInfo();
      field.Name = "note";
      field.Type = 'M';
      fields.Add(field);

      string file = AppDomain.CurrentDomain.BaseDirectory +"\\demo.dbf";
      using (DbfRecordset rs = DbfRecordset.Create(file, fields))
      {
        Random ran = new Random(1000);
        int recCount = 10;
       
        for (int i = 1; i <= recCount; i++)
        {
          int id = ran.Next(0,1000);
          string nick = "Jon ID :" +  id.ToString();
          System.Diagnostics.Debug.Print(nick);
          rs.AddNew(); // append black
          rs["nickname"] = nick;
          rs["note"] = @"
fsog[apiofsdug[ opiug oiuergpo
e prioue[u[etguo[ieutg[oitroieutoi ogjdf;lkjg;lkdsf
dsopfgih[posdi h[podigh[psodgih
sdgoih[posdgihpo";
          rs.Update(); // commit changes
        }
        rs.ForEach(r =>
          {
            System.Diagnostics.Debug.Print(rs["nickname"].ToString());
            System.Diagnostics.Debug.Print(rs["note"].ToString());
            return true;
          });
        rs.Close();
      }
    }
  }
}

First Demo Using Of DBF CDX.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WhitePage.Data;
namespace TestDbfRecord
{
  class Program
  {
    static void Main(string[] args)
    {
      //create table
      List<DbFieldInfo> fields = new List<DbFieldInfo>();
      DbFieldInfo field = new DbFieldInfo();
      field.Name = "nickname";
      field.Type = 'C';
      field.Len = 100;
      fields.Add(field);
      field = new DbFieldInfo();
      field.Name = "dt";
      field.Type = 'D';
      fields.Add(field);

      string file = AppDomain.CurrentDomain.BaseDirectory +"\\demo.dbf";
      using (DbfRecordset rs = DbfRecordset.Create(file, fields))
      {
        rs.CreateIndex("inick", "nickname", null);
        rs.CreateIndex("idt", "dt", null);
        Random ran = new Random(1000);
        int recCount = 10;
       
        for (int i = 1; i <= recCount; i++)
        {
          int days = ran.Next(0,1000);
          string nick = "Jon:" +  days.ToString();
          System.Diagnostics.Debug.Print(nick);
          rs.AddNew(); // append black
          rs["nickname"] = nick;
          rs["dt"] = DateTime.Now.AddDays(days);
          rs.Update(); // commit changes
        }
        // aplay index tag inick - sorted by nickname
        rs.Index = "inick";
        System.Diagnostics.Debug.Print("--- sorted by nickname ----");
        rs.MoveFirst();
        while (!rs.Eof)
        {
          System.Diagnostics.Debug.Print(rs["nickname"].ToString());
          rs.MoveNext();
        }
        rs.Index = "idt"; // aplay index tag idt
        System.Diagnostics.Debug.Print("--- sorted by nickname ----");
        rs.MoveFirst();
        while (!rs.Eof)
        {
          System.Diagnostics.Debug.Print(rs["nickname"].ToString() + " " +  rs["dt"].ToString());
          rs.MoveNext();
        }
        rs.Close();
      }
    }
  }
}

New Life of DBF file format. First article.

Hello to everyone. I developed "dll" to work with dbf file format that allows you to work with dbf files without VFPOLEDB provider and now you dont need to use ODBC driver. This utility is perfectly works at x64 OS. For first 100 customers it will costs just 1$. You can download trial version of the product from site  http://baybaksoft.ucoz.com/index/0-29

Migrating from Visual Fox Pro to Sql Server. (Converting dbf to MS Sql Server etc) Second edition. Creating dbf file and editing data.

    Here is the example how we can easy create and edit data in our dbf file.  This dll was the basis for my "Dbf2Any" programm. Wich can convert dbf files to MS Sql Server, PostgreSql. At this moment at the work converting dbf to Oracle and MySql.
Here we will be using datagrid in virtual mode.
I think that this example is clear enough,  but if anyone will have questions please write me.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using  DbfCore;



string[] Fields = new string[] { "Fld1", "Fld2", "Fld3", "Fld4", "Fld5",
                                    "Fld6", "Fld7","Fld8", "Fld9", "Fld10" };


List<DbFieldInfo> fieldsList = new List<DbFieldInfo>();
    DbfRecordset _recSet;

    string fileName = Application.StartupPath + "\\test.dbf";


 private void OpenCreateRecordSet()
    {
      if (System.IO.File.Exists(fileName))
      {
        _recSet = new DbfRecordset(fileName);
        for (int i = 0; i < _recSet.FieldCount; i++)
        {
          this.dataGridView1.Columns.Add(_recSet.Fields[i].Name, _recSet.Fields[i].Name + " Type(" + _recSet.Fields[i].Type + ")");
        }
      }
      else
      {
        for (int i = 0; i < 10; i++)
        {
          DbFieldInfo dbFldInfo = new DbFieldInfo(Fields[i], 'C', 20);
          fieldsList.Add(dbFldInfo);
        }
        _recSet = DbfRecordset.Create(fileName, fieldsList);
      }
    }


private void SetupGrid()
    {
      if (_recSet == null)
      {
        return;
      }

      this.dataGridView1.RowCount = _recSet.RecordCount;
      this.dataGridView1.CellValueNeeded += new DataGridViewCellValueEventHandler(dataGridView1_CellValueNeeded);    
      this.dataGridView1.CellValuePushed += new DataGridViewCellValueEventHandler(dataGridView1_CellValuePushed);
      this.dataGridView1.NewRowNeeded += new DataGridViewRowEventHandler(dataGridView1_NewRowNeeded);
    }


 void dataGridView1_CellValuePushed(object sender, DataGridViewCellValueEventArgs e)
    {
      _recSet.Move(e.RowIndex + 1);
      _recSet.Edit();
      _recSet[e.ColumnIndex + 1] = e.Value;
      _recSet.Update();
    }

    void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
    {
      _recSet.Move(e.RowIndex + 1);
      if (_recSet.Fields[e.ColumnIndex].Type == 'C')
      {
        e.Value = _recSet[e.ColumnIndex + 1].ToString().Trim();
      }
      else
      {
        e.Value = _recSet[e.ColumnIndex  + 1];
      }
    }


 private void btnAddRecord_Click(object sender, EventArgs e)
    {
      _recSet.AddNew();
      _recSet.Update();
      this.dataGridView1.RowCount = _recSet.RecordCount;
    }

Migrating from Visual Fox Pro to Sql Server. (Converting dbf to MS Sql Server etc))

Hello to everyone again. Today I will start new posts, with beautifull name "Migrating from VFP to MS SQL Server etc".
 Many years ago I was need to migrate from dbf format to MS Sql Server and PostgreSQL. I'm made some researches. After that I  wrote utility that uses VFPOLEDB Provider and everything worked fine, to the moment when 64 bit system began to appear. So I was need to find new solution. Because this proveder works very bad under the 64 bit systems. So I created my own dll, that allows you to View, Manage, work with Indexes (full work with dbf). At the next posts I will show to you how it works.

Transfering schema and data (SQL Server ), without SMO. Using only System.Data.SqlClient. Second part (Transfering data)

Hello to everyone. Here is the second part of our transfer methods without using SMO dlls.
I have new speed results. Something around 15 seconds. It's faster than previous method (using SMO which was 31 second). If you will have any questions please add comments or send me email. At next post I will write about migration data and structures from DBF  to the SQL Server. (x64 full support) Thanks.

 private void TestFunction2(string dbNameFrom, string dbNameTo)
    {
      string cnnString = "server=" + Server + ";user id=" + UserId
            + ";password=" + Password + ";Initial Catalog = " + dbNameFrom
            + ";Connect Timeout=30;Integrated Security=False;";

      DataTable tbData = null;
      using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(cnnString))
      {
        cnn.Open();
        for (int i = 0; i < dataTables.Length; i++)
        {
          string tbName = dataTables[i];
          string cmdText = " select * from " + tbName;
          tbData = new DataTable(tbName);
          using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmdText, cnn))
          {
            adapter.Fill(tbData);
          }       
          Insertrecord(tbData, dbNameTo);
          tbData = null;
        }
      }
    }



private void Insertrecord(DataTable tb, string dbNameTo)
    {
      string cnnStringTo = "server=" + Server + ";user id=" + UserId
            + ";password=" + Password + ";Initial Catalog = " + dbNameTo
            + ";Connect Timeout=30;Integrated Security=False;";

          using (System.Data.SqlClient.SqlConnection cnnTo = new System.Data.SqlClient.SqlConnection(cnnStringTo))
          {
            cnnTo.Open();

            string ccm = "delete from "+ tb.TableName;
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(ccm, cnnTo))
            {
              int result = cmd.ExecuteNonQuery();
              string rr = "";
              GenerateInsertStatement(ref rr, tb);
              cmd.CommandText = rr;
              for (int j = 0; j < tb.Rows.Count; j++)
              {
                for (int i = 0; i < tb.Columns.Count; i++)
                {
                  object val = tb.Rows[j][i];
                  cmd.Parameters.AddWithValue("@" + tb.Columns[i].ColumnName, val);
                }
                int res = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
              }
             
            }
          }    
    }