C # using operator overloading to generate dynamic SQL statements

 

C # provides the operator overload function, but this function is not used in many cases. I believe many C # developers have learned about this function, but I believe it is rarely used. why do I need to overload the operators to generate SQL statements instead of using Linq? Its purpose is also very simple to use and flexible. First, let's take a look at how many operators can be reloaded: +,-, *,/, %, &, |, ^, <, >>= ,! =, <,>, <=,> =

 

There seems to be quite a lot of data. It should be able to meet the corresponding needs of SQL. First, sort out a corresponding relationship.

 

C # SQL

===

! = <>

>

>=> =

<

<=< =

& And

| Or

 

In general, the basics are almost the same, but the like, in, and so on are definitely less, which can be implemented using functions or one technique.

 

Since operators are overloaded, it is necessary to recreate an object for its basic implementation. Its function is similar to the field in SQL.

 

 

Public class FieldInfo

{

Public FieldInfo (string table, string name)

{

DBContext. Init ();

MTable = table;

MName = name;

}

Private string mTable;

Public string Table

{

Get

{

Return mTable;

}

}

Private string mName;

Public string Name

{

Get

{

Return mName;

}

}

}

It is enough to express the type and above description of a field. For some table names and field names, the basic functions are implemented using functions before the operator overload is implemented. It is okay to simply call the operator overload method.

 

 

Public Expression Eq (object value)

{

String p = Expression. GetParamName ();

Expression exp = new Expression ();

Exp. SqlText. Append (string. Format ("{0 }=@{ 1}", Name, p ));

Exp. Parameters. Add (new Command. Parameter {Name = p,

Value = Mappings. PropertyCastAttribute. CastValue (Table, Name, value )});

Return exp;

}

Public Expression LtEq (object value)

{

String p = Expression. GetParamName ();

Expression exp = new Expression ();

Exp. SqlText. Append (string. Format ("{0 }<=@{ 1}", Name, p ));

Exp. Parameters. Add (new Command. Parameter {Name = p,

Value = Mappings. PropertyCastAttribute. CastValue (Table, Name, value)

});

Return exp;

}

Public Expression Lt (object value)

{

String p = Expression. GetParamName ();

Expression exp = new Expression ();

Exp. SqlText. Append (string. Format ("{0} <@ {1}", Name, p ));

Exp. Parameters. Add (new Command. Parameter {Name = p,

Value = Mappings. PropertyCastAttribute. CastValue (Table, Name, value)

});

Return exp;

}

You can simply describe the implementation of several methods. For other implementations, the principle is the same.

 

 

Public static Expression operator = (FieldInfo field, object value)

{

If (value = null)

Return field. IsNull ();

If (value is System. Collections. IEnumerable & value. GetType ()! = Typeof (string ))

Return field. In (System. Collections. IEnumerable) value );

Return field. Eq (value );

}

Public static Expression operator! = (FieldInfo field, object value)

{

If (value = null)

Return field. IsNotNull ();

If (value is System. Collections. IEnumerable & value. GetType ()! = Typeof (string ))

Return field. NotIn (System. Collections. IEnumerable) value );

Return field. NotEq (value );

}

Public static Expression operator> (FieldInfo field, object value)

{

Return field. Gt (value );

}

Public static Expression operator> = (FieldInfo field, object value)

{

Return field. GtEq (value );

}

Public static Expression operator <(FieldInfo field, object value)

{

Return field. Lt (value );

}

Public static Expression operator <= (FieldInfo field, object value)

{

Return field. LtEq (value );

}

The work is done here, but it seems that something is missing... it does not seem to have implemented & |; because these two operations are not comparison operators, they are not implemented by FieldInfo objects. in the above Code, each comparison operation returns an Expression object, and then & | is implemented by it.

 

Public static Expression operator & (Expression exp1, Expression exp2)

{

If (exp1 = null | exp1.SqlText. Length = 0)

Return exp2;

If (exp2 = null | exp2.SqlText. Length = 0)

Return exp1;

 

Expression exp = new Expression ();

Exp. SqlText. Append ("(");

Exp. SqlText. Append (exp1.ToString ());

 

Exp. SqlText. Append (")");

Exp. Parameters. AddRange (exp1.Parameters );

Exp. SqlText. Append ("and (");

Exp. SqlText. Append (exp2.SqlText. ToString ());

Exp. SqlText. Append (")");

Exp. Parameters. AddRange (exp2.Parameters );

Return exp;

}

Public static Expression operator | (Expression exp1, Expression exp2)

{

If (exp1 = null | exp1.SqlText. Length = 0)

Return exp2;

If (exp2 = null | exp2.SqlText. Length = 0)

Return exp1;

Expression exp = new Expression ();

Exp. SqlText. Append ("(");

Exp. SqlText. Append (exp1.ToString ());

Exp. SqlText. Append (")");

Exp. Parameters. AddRange (exp1.Parameters );

Exp. SqlText. Append ("or (");

Exp. SqlText. Append (exp2.SqlText. ToString ());

Exp. SqlText. Append (")");

Exp. Parameters. AddRange (exp2.Parameters );

Return exp;

 

}

You can obtain the complete code from https://smarkdata.svn.codeplex.com/svn/smark/smark.data/smark.data/expression.cs.

 

The implementation code is complete, so let's take a look at the effects of different queries:

 

SQL:

 

Select * from customer where region = 'uk'

 

C #

 

(Customer. Region = 'uk '). List <Customer> ()

 

SQL:

 

Select * from Orders where OrderDate> '2014-7-8 'and OrderDate <'2014-8-8'

 

C #

 

(Order. OrderDate> "1998-7-8" & Order. OrderDate <"1998-8-8"). List <Orders> ()

 

SQL:

 

Select * from Orders where CustomerID in ('2', '5', '6 ')

 

C #

 

(Orders. CustomerID = new [] {"2", "5", "6"}). List <Orders> ();

 

SQL:

 

Select * from Orders where CustomerID in (select customerid from customer where region = 'uk ')

 

C #

 

(Orders. CustomerID = Customer. CustomerID [Customer. Region = "UK"]). List <Orders> ();

 

From the code above, we can see that the degree of freedom is very high because of heavy load. For the = Operator, we can replace many SQL comparison operations such as: =, in, in (select, of course, the author's imagination can also be used.

 

Since it is a custom writing implementation, the dynamic combination of conditions is certainly much more flexible than that of Linq:

 

 

Expression exp;

If ()

Exp & = order. id = "";

If (B)

Exp & = order. customerid = customer. customerid [customer. region = "B"]

Author: smark