1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Web;
   5:  using System.Web.Configuration;
   6:   
   7:  using System.Data.OleDb;
   8:  using System.Data.SqlClient;
   9:  using System.Data;
  10:   
  11:  // --- By Matt Slay          posted March 3, 2010 
  12:  //
  13:  //     ADO.Net Data Access methods and samples for accessing Foxpro DBF tables
  14:  //
  15:  // --- Basic Methods on this class ----------------------------------------------------
  16:  //
  17:  //  public OleDbConnection CreateConnection(string dbConnectionString
  18:  //  public OleDbCommand CreateSqlCommand(string CommandString)
  19:  //  public OleDbDataAdapter CreateAdapter(String cmd)
  20:  //
  21:  //  Now, with these basic service methods nicely in place on this class, the only method
  22:  //  you really have to call in order to perform a query is CreateAdapter() and pass
  23:  //  in a Select statement as a string. It will return a DataTable.
  24:  //  See the GetCustomersWithOpenOrders() sample below for a typical usauge.
  25:  //
  26:  //  Also, for a sample of a Sql query that uses a parameter, see the GetJob() sample method.
  27:  //
  28:  //  You can also learn some important details about using parameters with VfpOleDb here:
  29:  //  http://www.west-wind.com/WebLog/posts/660586.aspx
  30:   
  31:   
  32:  namespace LM.Data.ADO.OleDb
  33:  {
  34:   
  35:      public class LMDALBaseClass
  36:      {
  37:          public string dbConnectionString = WebConfigurationManager.ConnectionStrings["VFPDBFConnectionString"].ConnectionString;
  38:          public OleDbConnection Conn;
  39:   
  40:          public LMDALBaseClass() //Constructor
  41:          {
  42:              Conn = CreateConnection(dbConnectionString);
  43:          }
  44:   
  45:   
  46:          public OleDbConnection CreateConnection(string dbConnectionString)
  47:          {
  48:              return new OleDbConnection(dbConnectionString);
  49:          }
  50:   
  51:          public OleDbCommand CreateSqlCommand(string CommandString) 
  52:          {
  53:              var cmd = new OleDbCommand();
  54:              cmd.CommandText = CommandString;
  55:              cmd.Connection = Conn;
  56:   
  57:              return cmd;
  58:          }
  59:   
  60:          public OleDbDataAdapter CreateAdapter(String cmd)
  61:          {
  62:              var Cmd = new OleDbCommand();
  63:              return new OleDbDataAdapter(cmd, Conn);
  64:          }
  65:   
  66:   
  67:          
  68:     //  A basic sample of a Select with a Join... 
  69:          public DataTable GetCustomersWithOpenOrders()
  70:          {
  71:   
  72:              DataSet ds = new DataSet();
  73:              string cmd = @"Select custno, MAX(company) as name, COUNT(job_num) as count,
  74:                                    max(phone) as phone, max(faxno) as faxno
  75:                             From Job_imfo
  76:                             Inner Join customer_source on job_imfo.cust_num=customer_source.custno
  77:                             Where status='A'
  78:                             Group By custno
  79:                             Order By name";
  80:              var adapter = CreateAdapter(cmd);
  81:              adapter.Fill(ds);
  82:              
  83:            return ds.Tables[0];
  84:          }
  85:   
  86:   
  87:   
  88:   //  And now for a sample that uses parameters...
  89:   //  You can also learn some important details about using parameters with VfpOleDb here:
  90:   //  http://www.west-wind.com/WebLog/posts/660586.aspx
  91:   
  92:          public DataTable GetJob(string JobNo)
  93:          {
  94:              if (JobNo != null)
  95:              {
  96:                  DataSet ds = new DataSet();
  97:   
  98:                  string sqlstring = @"Select * from Job_imfo Where job_num = ?";
  99:   
 100:                  var JobAdapter = CreateAdapter(sqlstring);
 101:                  JobAdapter.SelectCommand.Parameters.AddWithValue("?", JobNo);
 102:                  JobAdapter.Fill(ds);
 103:                  return ds.Tables[0];
 104:              }
 105:              else
 106:              {
 107:                  return null;
 108:              }
 109:   
 110:          }
 111:   
 112:   
 113:   
 114:      }
 115:   
 116:  }