1

I want to build my own mysql class. But I'm relative new to c#.

So in my thoughts I would like to build some like:

mySqlTool.select("a,b,c").from("foo").where("x=y");

I dont know how this is called and actually I dont really know if this is even possible. My google search ended with no real result.

So my questions are:

Is it possible to do some like my sample above?

Dwza
  • 6,494
  • 6
  • 41
  • 73
  • 4
    You want LINQ + MySQL... look at [this question](http://stackoverflow.com/questions/1469100/linq-to-mysql-what-is-the-best-option). – vbnet3d Dec 28 '16 at 15:51
  • Build class. Each method returns itself. – TyCobb Dec 28 '16 at 15:51
  • If you're set on submethods as you name them. Then extension methods are your friend. See my answer below. – pim Dec 28 '16 at 15:56

4 Answers4

3

Creating a fluent api isn't overly complicated. You simply return the current instance of the class from each method which allows them to be chained the way that you specify in your post:

public class MySqlTool
{
    private string _fields, _table, _filters;

    public MySqlTool Select(string fields)
    {
        _fields = fields;
        return this;
    }

    public MySqlTool From(string table)
    {
        _table = table;
        return this;
    }

    public MySqlTool Where(string filters)
    {
        _filters = filters;
        return this;
    }

    public Results Execute()
    {
        // build your query from _fields, _table, _filters
        return results;
    }
}

Would allow for you to run a query like:

var tool = new MySqlTool();
var results = tool.Select("a, b, c").From("someTable").Where("a > 1").Execute();
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • This would allow the methods to be in any order, maybe override what has already been said etc. Would not really work well in this case in my opinion. – Sami Kuhmonen Dec 28 '16 at 15:57
  • @SamiKuhmonen - Order doesn't matter. You just need to verify that the tool has all of the necessary data when calling `Execute`. – Justin Niessner Dec 28 '16 at 15:58
  • I think you're misrepresenting the level of complication of building a fluent interface that isn't brittle, especially from someone who is brand new at C#. Sami's comment, for example is one reason. – rory.ap Dec 28 '16 at 15:58
  • So order matters because you have to call something at a specific place. Or what about calling select seven times with where somewhere in between or not? This only works if the person using it also wrote it and then it doesn't really provide anything you couldn't do with just a single method. – Sami Kuhmonen Dec 28 '16 at 16:01
  • 1
    @rory.ap - The OP hasn't said anything about requiring order or that methods can't be called again. If that's the case, order can easily be enforced by introducing different interfaces for each step of the process. Again, not overly complicated. – Justin Niessner Dec 28 '16 at 16:01
  • 1
    @SamiKuhmonen - You're making assumptions about behavior that the OP never mentioned. My example is overly simple because I'm not going to re-write Entity Framework for a Stack Overflow question. Adding complexity isn't difficult either, but a simple starting point is good. – Justin Niessner Dec 28 '16 at 16:03
  • @JustinNiessner `Results` where does this come from ? or is this a typo and it should mean `public MySqlTool Execute()` ? – Dwza Dec 28 '16 at 16:19
  • @Dwza - `Results` is just a placeholder for whatever type is returned by actually executing the query. – Justin Niessner Dec 28 '16 at 16:20
  • @JustinNiessner i know this is unliked but could you may provide me with a link or some kind of information how to set the methods in required order?!? :D just if you in mood. – Dwza Dec 28 '16 at 18:16
  • SQL requires the clauses to be in a specific order; this chaining does not prevent ordering errors. – Rick James Dec 30 '18 at 04:36
1

It looks like you are trying to do something similar to Linq to SQL, but with MySQL instead. There is a project called LinqConnect that does this. I have no affiliation with them.

You can use it like this (from a LinqConnect tutorial):

CrmDemoDataContext context = new CrmDemoDataContext();
var query = from it in context.Companies
    orderby it.CompanyID
    select it;

foreach (Company comp in query)
    Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country);

Console.ReadLine();
Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
  • 1
    nice to know but shareware so ill stick with my mind to do it by my self :D but thanks – Dwza Dec 28 '16 at 16:01
1

I'm not a big fan of it personally, but it can be useful if you're just learning. Linq2SQL can give you that functionality somewhat out of the box.

If you're looking to accomplish this yourself. You'll want to turn to extension methods. A feature within c# that will allow you to extend your classes using static methods, operating on the base.

My recommendation if you're doing your own data-access layer, then you should use something like dapper (built/used by the crew at StackOverflow). Which offers you a simple wrapper around the base connection to the database. It offers simple ORM functionality and does parameterization for you.

I would also recommend strongly that you encapsulate your intensive queries within Stored Procedures.

pim
  • 12,019
  • 6
  • 66
  • 69
1

What you're looking to do is design a fluent interface, and it's somewhat of an advanced concept for someone who is just learning C#. I would suggest you stick to the basics until you have a little more experience.

More importantly, there are already existing data adapters built into the .NET FCL and also third-party adapters like this one that are more suitable and already do what you're trying to do using "LINQ to (database)". I wouldn't reinvent the wheel if I were you.

rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • im not totaly new... even doe if i feel like :D searching for this to get deeper but sometimes its hard to search for something when you dont know what you searching for :D – Dwza Dec 28 '16 at 15:54