C＃：SQL查询生成器类

Question

我在哪里可以找到一个好的SQL查询生成器类。我只需要一个简单的类来构建一个SQL字符串，就是这样。我将需要它用于C＃和MySql。我真的不需要像Linq或NHibernate这样的东西。谢谢

Answer 1

你可以使用框架类CommandBuilder。退房：

http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

Answer 2

如果您使用的是.NET 4，不介意使用动态可以使用Massive，通过罗布科纳创建的，这个单一文件数据库不需要的DLL，只是下降的Massive.cs文件，你准备好了。

您可以使用Massive来构建像这样的查询。

//grab all the products 
var products = table.All(); 
//Or 
var productsFour = table.All(columns: "ProductName as Name", where: "WHERE [email protected]",args: 4); 

根据需要还可以运行即席查询：

var result = tbl.Query("SELECT * FROM Categories"); 

Answer 3

我用这个code..It逃脱串过我希望它能帮助：

class Mysql 
{ 
    public static string INSERT(string INTO, NameValueCollection VALUES) 
    { 
     string queryString = "INSERT INTO " + INTO + " ("; 
     for (int i = 0; i < VALUES.Count; i++) 
     { 
      queryString += VALUES.Keys[i] + (i + 1 == VALUES.Count ? "" : ","); 
     } 
     queryString += ") VALUES ("; 

     for (int i = 0; i < VALUES.Count; i++) 
     { 
      queryString += Escape(VALUES[VALUES.Keys[i]]) + (i + 1 == VALUES.Count ? ("") : (",")); 
     } 
     queryString += ");"; 
     return queryString; 
    } 
    public static string DELETE(string FROM, NameValueCollection WHERE) 
    { 

     string queryString = "DELETE FROM " + FROM + " WHERE"; 
     for (int i = 0; i < WHERE.Count; i++) 
     { 
      queryString += " " + WHERE.Keys[i] + "=" + Escape(WHERE[WHERE.Keys[i]]); 

     } 

     queryString += ";"; 
     return queryString; 
    } 
    public static string UPDATE(string UPDATE, NameValueCollection SET, NameValueCollection WHERE) 
    { 

     string queryString = "UPDATE " + UPDATE + " SET"; 
     for (int i = 0; i < SET.Count; i++) 
     { 
      queryString += " " + SET.Keys[i] + "=" + data.Escape(SET[SET.Keys[i]]) + (i + 1 == SET.Count ? ("") : (",")); 

     } 
     queryString += " WHERE"; 
     for (int i = 0; i < WHERE.Count; i++) 
     { 
      queryString += " " + WHERE.Keys[i] + "=" + data.Escape(WHERE[WHERE.Keys[i]]); 

     } 
     queryString += ";"; 
     return queryString; 

    } 
    public static string SELECT(string[] SELECT, string FROM, NameValueCollection WHERE) 
    { 

     string queryString = "SELECT "; 

     for (int i = 0; i < SELECT.Length; i++) 
     { 
      queryString += SELECT[i] + (i + 1 == SELECT.Length ? ("") : (",")); 

     } 
     queryString += " FROM " + FROM + " WHERE "; 
     for (int i = 0; i < WHERE.Count; i++) 
     { 
      queryString += " " + WHERE.Keys[i] + "=" + Escape(WHERE[WHERE.Keys[i]]); 

     } 

     queryString += ";"; 
     return queryString; 

    } 
    public static string Escape(string input) 
    { 
     using (var writer = new StringWriter()) 
     { 
      using (var provider = CodeDomProvider.CreateProvider("CSharp")) 
      { 
       provider.GenerateCodeFromExpression(new CodePrimitiveExpression(input), writer, null); 
       return writer.ToString(); 
      } 
     } 
    } 
} 

您使用它是这样的：

 NameValueCollection nvc_for_SET_and_VALUES=new NameValueCollection(); 
     NameValueCollection nvc_for_WHERE= new NameValueCollection(); 
     nvc_for_WHERE.Add("arg1","value1"); 
     nvc_for_WHERE.Add("AND arg2","value2"); 
     nvc_for_WHERE.Add("OR arg2","value3"); 
     nvc_for_SET_and_VALUES.Add("arg", "value"); 
     nvc_for_SET_and_VALUES.Add("arg2", "value2"); 
     string[] fieldsToSelect= { "arg1", "arg2" }; 
     Mysql.DELETE("mytable", nvc_for_WHERE); 
     Mysql.INSERT("mytable", nvc_for_SET_and_VALUES); 
     Mysql.SELECT(fieldsToSelect, "mytable", nvc_for_WHERE); 
     Mysql.UPDATE("mytable", nvc_for_SET_and_VALUES, nvc_for_WHERE); 

Answer 4

*·哈迪，W第i个DbExtensions您的样品可以是这样的：

public static string InsertQuery(string into, NameValueCollection values) 
    { 
     var query = SQL 
      .INSERT_INTO(into + " (" + 
         String.Join(" ,", values.Keys.Cast<String>().ToArray()) + 
         ")") 
      .VALUES(values.Keys.Cast<String>().Select(key => values[key])); 

     return query.ToString(); 
    } 

Answer 5

由于谷歌使我这个网页， 我建议SqlKata，一个简单但功能强大的SqlQuery类生成器，即支持嵌套有条件的地方，子查询和连接。

目前，它拥有SQLSERVER，MySQL和PostgreSQL

编译器

var query = new Query("Users") 
    .LeftJoin("Countries", "Users.CountryId", "Countries.Id") 
    .Where("Status", "blocked") 
    .OrWhereIn("Id", new [] {10, 11, 12}) 
    .OrWhere("LastLogin", ">", DateTime.UtcNow.AddMonths(-5));