Mapping Db Table to C# Class

Oct 21, 2009 at 1:39 PM

Hi there,

first let me congratulate you for a fantastic job, i would like to ask if is possible to map a Table, lets say TableName CUSTOMERS with columns CUST_ID,CUST_NAME, to a class with Name CUSTOMERS and properties CUST_ID,Name.

I tried to use the IQToolkit.Mapping.Column attribute but when tried to run a query i got the following error.

"SQL: Column 'NAME' is not found."

Is there a way to do this??

 

thnx

Nassos 

Coordinator
Oct 22, 2009 at 10:15 AM
Edited Oct 22, 2009 at 10:17 AM

There are a couple different ways to map your class to the table.  Here is an example using the Column Attribute.

 

using System;
using System.Collections.Generic;
using System.Linq;
using IQToolkit;
using IQToolkit.Data.Mapping;
using LinqToVfp;

namespace WindowsFormsApplication1 {
    static class Program {
        [STAThread]
        static void Main() {
            DataContext context = new DataContext(@"c:\Customers.DBF");
            List<Customers> customers = context.Customers.ToList();
        }

        public class DataContextAttributes : DataContext {
            public DataContextAttributes(string connectionString)
                : base(connectionString) {
            }

            [Table(Name = "Customers")]
            [Column(Member = "CUST_ID")]
            [Column(Member = "Name", Name = "CUST_NAME")] // this maps the Customers class "Name" property to the Customers CUST_NAME field
            public override IEntityTable<Customers> Customers {
                get { return base.Customers; }
            }
        }

        public class DataContext : AVfpDatabaseContainer {
            public DataContext(string connectionString)
                : base(connectionString, typeof(DataContextAttributes).FullName) {
            }

            public virtual IEntityTable<Customers> Customers {
                get { return this.Provider.GetTable<Customers>("Customers"); }
            }
        }

        public class Customers {
            public string CUST_ID { get; set; }
            public string Name { get; set; }
        }
    }
}

Nov 11, 2009 at 3:12 PM
Edited Nov 11, 2009 at 3:13 PM

I see that you are manually creating the Customers class to basically mirror the fields in the DBF table, right? And something similar with the attributes on the IEntityTable<Customers> Customers property in the DataContextAttributes class.

Wouldn't it be cool to write a VFP program would code-generate the C# class definitions and IEntityTable<T> methods for each DBF table in a directory?

 It could code genereate the classes in a .cs file with partial classes, and add an access method for each table. It would be a good starting point for a DAL at least.

Templating this kind of stuff should be pretty easy to do, especially in VFP.

Coordinator
Nov 12, 2009 at 11:31 AM
Edited Nov 12, 2009 at 1:50 PM

I see that you are manually creating the Customers class to basically mirror the fields in the DBF table, right?

The Customers class doesn't need to mirror the table as long as you are using the Attribute Mapping or Xml Mapping.  These mappings will allow you to map the Class Property to the Table Field. 

 

Wouldn't it be cool to write a VFP program would code-generate the C# class definitions and IEntityTable<T> methods for each DBF table in a directory?

I considered doing something like this when I first started using LINQ to VFP.  But then I thought that I would end up needing to modify the classes to much to make it worth writing the script.  (I'm dealing with a lot of poorly named tables and fields... so my classes would rarely have the same names)

 

On a side note, I've decided to move away from using Attribute Mapping.  Having to keep the DataContextAttributes class in sync with the DataContext class really seemed annoying to me.  Plus, I'm trying to move towards Test Driven Development and the AVfpDatabaseContainer was not flexible enough.  Now I'm using a Repository and Xml Mapping.

Here is a reworked example:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using LinqToVfp;

namespace RepositoryExample {
    static class Program {
        [STAThread]
        static void Main() {
            AppDataContext context = new AppDataContext(@"c:\Customers.dbf");
            List<Customer> customers = context.List<Customer>().ToList();
        }

        public class AppDataContext : IQToolkitContrib.DataContext {
            public AppDataContext(string connectionString)
                : this(CreateRepository(connectionString)) {
            }

            public AppDataContext(IQToolkitContrib.IRepository respository)
                : base(respository) {
            }

            private static IQToolkitContrib.DbEntityRepository CreateRepository(string connectionString) {
                VfpQueryProvider provider = VfpQueryProvider.Create(connectionString, null);

                string xml;

                // path of the xml file in the dll
                string xmlPath = Path.GetFileNameWithoutExtension(typeof(AppDataContext).FullName) + ".Mapping.xml";

                // read the "Embedded Resource" Mapping.xml
                using (StreamReader streamReader = new StreamReader(typeof(AppDataContext).Assembly.GetManifestResourceStream(xmlPath))) {
                    xml = streamReader.ReadToEnd();
                }

                // set XmlMapping on the provider
                provider = provider.New(VfpXmlMapping.FromXml(provider.Language, xml));

#if DEBUG
                provider.Log = new IQToolkitContrib.DebuggerWriter();
#endif

                return new IQToolkitContrib.DbEntityRepository(provider);
            }
        }

        public class Customer {
            public string CustomerId { get; set; }
            public string Name { get; set; }
        }

    }
}

 

Mapping.xml:

 

<?xml version="1.0" encoding="utf-8" ?>
<map>
	<Entity Id="Customer">
		<Table Name="Customers" />
		<Column Member = "CustomerId" Name="CUST_ID" IsPrimaryKey = "true" IsGenerated="false" />
		<Column Member = "Name" Name="Cust_Name" />
	</Entity>
</map>

 

 

 

Coordinator
Dec 29, 2009 at 11:02 PM
Edited Dec 30, 2009 at 10:15 AM

I finally got around to writing a code generator application.  You can get it here if you are still interested.  (documentation pending...)

 

Jan 5, 2010 at 6:55 PM
Edited Jan 5, 2010 at 7:07 PM

Thanks for the wonderful tool.

I am experiencing some difficulties when using it.

When I choose 'vfpEntityRepository' as 'Data Context Settings'
choose 'xmlMapping' as 'Mapping settings'
and compile, I get the following error(s)

The type or namespace name 'VFPPosAttributes' could not be found (are you missing a using directive or an assembly reference?) 

BTW is it possible to select 'All' tables or select 'All columns' of a table?

Coordinator
Jan 5, 2010 at 7:06 PM
Edited Jan 5, 2010 at 8:35 PM

I'm not sure what is missing... can you send me a copy of the project?

Jan 5, 2010 at 7:13 PM

Thanks.

 

For the first error 'Cannot convert type ...' I fixed it by using latest LinqToVfp.dll .Sorry for bother

 

For error 'type or namespace name 'VFPPosAttributes' could not be found'

the context file generated as :

 

using System;
using IQToolkit;
using IQToolkit.Data.Common;
using IQToolkitContrib;
using LinqToVfp;

namespace DAL {
	public partial class VFPPos {
		private VfpQueryProvider provider;	
        		
        public VFPPos(string connectionString) {
            this.provider = VfpQueryProvider.Create(connectionString, typeof(VFPPosAttributes).FullName);
            this.provider.Log = new DebuggerWriter();
       }

        public virtual IEntityTable<at_audit> at_audits {
            get { return this.provider.GetTable<at_audit>("at_audit"); }
        }
	}
}

 

 Looks like class VFPPosAttributes is not generated which is kind of correct because XMLmapping is chosen.

Coordinator
Jan 5, 2010 at 7:50 PM
Edited Jan 5, 2010 at 8:35 PM

No bother... I'm glad to see someone is actually using this stuff.

The attribute issue is due to the way I designed the templates. The VfpRepository template was designed to work with the XmlMapping template and the VfpEntityProvider template was designed to work with the AttributeMapping template. But you are not limited to this setup. The templates are included with the application. So you can modify the templates as you see fit.


... and I'll be sure to add the ability to select all on the next update.

Jan 5, 2010 at 9:05 PM
Edited Jan 5, 2010 at 10:08 PM

The error described below has been fixed by correcting the varioius IQ*.dll references.

 Hello it's me again

I am getting error on the bold line below

 

        static void Main()
        {
            VFPPos context = new VFPPos(@"c:\pos\data\vpos.dbc");
            List<at_audit> at_audits = context.List<at_audit>().ToList();

        }
the erro and stack trace is as below :

"Method not found: 'Void IQToolkit.Query`1..ctor(System.Linq.IQueryProvider, System.Type)'."
 
"   at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)\r\n   
at System.RuntimeMethodHandle.InvokeConstructor(Object[] args, SignatureStruct signature, RuntimeTypeHandle declaringType)\r\n  
at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)\r\n  
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)\r\n  
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)\r\n  
at System.Activator.CreateInstance(Type type, Object[] args)\r\n 
at LinqToVfp.VfpQueryProvider.CreateTable(MappingEntity entity)\r\n  
at LinqToVfp.VfpQueryProvider.GetTable(MappingEntity entity)\r\n  
at IQToolkit.Data.Common.DbEntityProviderBase.GetTable(Type type, String tableId)\r\n  
at IQToolkit.Data.Common.DbEntityProviderBase.GetTable[T](String tableId)\r\n  
at IQToolkitContrib.DbEntityRepository.GetEntityTable[T]()\r\n  
at IQToolkitContrib.DbEntityRepository.List[T]()\r\n  
at IQToolkitContrib.DataContext.List[T]()\r\n  
at RepositoryExample.Program.Main() in C:\\tmp\\vfptest\\vfptest\\vfptest\\Program.cs:line 18\r\n  
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)\r\n  
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)\r\n  
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n  
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n  
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n  
at System.Threading.ThreadHelper.ThreadStart()
Feb 4, 2010 at 3:14 AM

Great to see you wrote a generator. I hope to get some time to download it and run it against my tables. I'd love to see my dbf tables automatically gnerated as C# classes. Cool stuff here.

Feb 13, 2011 at 11:54 PM

(I added this as a comment in your blog but likely failed to save there)

Hi,
First of all congratulations on your cool LinqToVFP. I had done WCF service before using VFP COM but you know I simply don't like it and it is next to impossible to debug. In a new project I am going to use WCF RIA services and while thinking about using datasets on the service I saw your kit. In about half an hour I was able to read your blog, check samples etc and create and run a datacontext against testdata:) Very cool.

For this IN style query, I had the exact same problem when I was using VFPOLEDB to get the data. My initial solution was same as yours that creates a temp table and inserts data into it. Later I thought, it would be much more effective if instead I could send over a 'cursor'. I cannot send a cursor but I can send an XML string as a parameter, so I sent the 'list of IDs' as an XML data, convert to cursor directly in VFP SQL select. Here is the basic SQL:

select * from bigTable ;
where FKid in ;
(select id from ;
(IIF(XMLTOCURSOR(?m.lcIdXML,"myIdList") > 0, "myIdList", "")) )

Here is a more detailed explanation:

http://www.foxite.com/archives/in-query-to-vfp-using-oledb-0000164535.htm

PS: Used the generator too:) Cool it doesn't need special documentation. Likely I will end using your tools in my project.

Coordinator
Feb 14, 2011 at 1:34 AM

Thanks for the good suggestion.  I considered creating the temp tables less than ideal so I’m glad you provided me with a better option.  I’ll have to see if I can work this idea into the LINQtoVFP code.