PetaPoco对数据库的操作直接使用SQL语句，在代码中进行调用既可以直接传递SQL语句，也可以使用提供的SQL类来获取到SQL语句进行操作，直接传递SQL语句在内部实现中也是封装成Sql类传递到底层来操作的。Sql类实际上就是对SQL语句的一种封装，使你能够向操作类的方法一样来使用SQL语句。比如，
1 var sql=Sql.Builder.Select(“*”).From(“person”).Where(“ID=@0”,”1345”) 2 var sqlStr=sql.SQL;//select * from person where ID=’1345’;
Sql类采用函数式编程的思想，对SQL语句进行了抽象。当然，如果觉得这种表达方式不好，也可以直接传递SQL语句。
备注：使用Sql类时，如果传递参数，则在sql语句中@X进行占位，每次调用一个方法都从0开始，依次增加。
以下是对Sql类的详细描述：
1 /// <summary> 2 /// SQL语句类 3 /// </summary> 4 public class Sql 5 { 6 /// <summary> 7 /// 初始SQL语句 8 /// </summary> 9 private string mInitialSQL; 10 11 /// <summary> 12 /// 初始SQL参数 13 /// </summary> 14 private object[] mInitialArgs; 15 16 /// <summary> 17 /// 最终SQL语句 18 /// </summary> 19 private string mFinalSQL; 20 21 /// <summary> 22 /// 最终SQL参数 23 /// </summary> 24 private object[] mFinalArgs; 25 26 /// <summary> 27 /// right-hand-side 引用，理解为后继 28 /// </summary> 29 private Sql mRHS; 30 31 /// <summary> 32 /// 获取实例 33 /// </summary> 34 public static Sql Builder 35 { 36 get { return new Sql(); } 37 } 38 39 /// <summary> 40 /// 获取SQL语句 41 /// </summary> 42 public string SQL 43 { 44 get 45 { 46 Build(); 47 return mFinalSQL; 48 } 49 } 50 51 /// <summary> 52 /// 获取构建SQL语句的参数 53 /// </summary> 54 public object[] Arguments 55 { 56 get 57 { 58 Build(); 59 return mFinalArgs; 60 } 61 } 62 63 /// <summary> 64 /// 默认构造器 65 /// </summary> 66 public Sql() 67 { 68 } 69 70 /// <summary> 71 /// 构造函数 72 /// </summary> 73 /// <param name="sql">SQL语句</param> 74 /// <param name="args">SQL语句对应的参数</param> 75 public Sql(string sql, params object[] args) 76 { 77 mInitialSQL = sql; 78 mInitialArgs = args; 79 } 80 81 private void Build() 82 { 83 //只需要构建一次即可 84 if (mFinalSQL != null) 85 return; 86 //根据SQL语句和参数构建最终语句 87 var sb = new StringBuilder(); 88 var args = new List<object>(); 89 Build(sb, args, null); 90 mFinalSQL = sb.ToString(); 91 mFinalArgs = args.ToArray(); 92 } 93 94 /// <summary> 95 /// 向当前SQL实例添加后继SQL实例 Append another SQL builder instance to the right-hand-side of this SQL builder 96 /// </summary> 97 /// <param name="sql">后继SQL实例</param> 98 /// <returns>返回对象自身</returns> 99 public Sql Append(Sql sql) 100 { 101 //若当前对象包括后继对象，则向其后继对象添加后继对象 102 if (mRHS != null) 103 mRHS.Append(sql); 104 else 105 mRHS = sql; 106 mFinalSQL = null; 107 return this; 108 } 109 110 /// <summary> 111 /// 向当前SQL实例添加SQL语句和对应参数 112 /// </summary> 113 /// <param name="sql">SQL语句</param> 114 /// <param name="args">对应参数</param> 115 /// <returns>返回对象自身</returns> 116 public Sql Append(string sql, params object[] args) 117 { 118 return Append(new Sql(sql, args)); 119 } 120 121 /// <summary> 122 /// 判断SQL语句是否与参数表示类型相同 123 /// </summary> 124 /// <param name="sql">SQL帮助类</param> 125 /// <param name="sqltype">SQL类型</param> 126 /// <returns></returns> 127 private static bool Is(Sql sql, string sqltype) 128 { 129 return sql != null && sql.mInitialSQL != null && sql.mInitialSQL.StartsWith(sqltype, StringComparison.InvariantCultureIgnoreCase); 130 } 131 132 /// <summary> 133 /// 构建SQL语句 134 /// </summary> 135 /// <param name="sb">StringBuilder保存Sql语句</param> 136 /// <param name="args">SQL参数</param> 137 /// <param name="lhs">SQL实例</param> 138 private void Build(StringBuilder sb, List<object> args, Sql lhs) 139 { 140 if (!string.IsNullOrEmpty(mInitialSQL)) 141 { 142 if (sb.Length > 0) 143 { 144 sb.Append(" "); 145 } 146 var sql = ParametersHelper.ProcessParams(mInitialSQL, mInitialArgs, args); 147 //累加where条件 148 if (Is(lhs, "WHERE ") && Is(this, "WHERE ")) 149 sql = "AND " + sql.Substring(6); 150 //累加Order by 条件 151 if (Is(lhs, "ORDER BY ") && Is(this, "ORDER BY ")) 152 sql = ", " + sql.Substring(9); 153 //累加set语句 154 if (Is(lhs, "SET ") && Is(this, "SET ")) 155 sql = ", " + sql.Substring(4); 156 157 sb.Append(sql); 158 } 159 //处理当前实例的后继实例（递归） 160 if (mRHS != null) 161 mRHS.Build(sb, args, this); 162 } 163 164 /// <summary> 165 /// 向SQL实例添加set语句 166 /// </summary> 167 /// <param name="sql">update中的set语句，形如"{field} = {value}" </param> 168 /// <param name="args">可选参数</param> 169 /// <returns>当前SQL实例</returns> 170 public Sql Set(string sql, params object[] args) 171 { 172 return Append(new Sql("SET " + sql, args)); 173 } 174 175 /// <summary> 176 /// 向SQL实例添加where语句 177 /// </summary> 178 /// <param name="sql">where语句</param> 179 /// <param name="args">可选参数</param> 180 /// <returns>当前SQL实例</returns> 181 public Sql Where(string sql, params object[] args) 182 { 183 return Append(new Sql("WHERE (" + sql + ")", args)); 184 } 185 186 /// <summary> 187 /// 向SQL实例添加order by语句 188 /// </summary> 189 /// <param name="columns">待排序的列名称</param> 190 /// <returns>当前SQL实例</returns> 191 public Sql OrderBy(params object[] columns) 192 { 193 return Append(new Sql("ORDER BY " + string.Join(", ", (from x in columns select x.ToString()).ToArray()))); 194 } 195 196 /// <summary> 197 /// 向SQL实例添加select语句 198 /// </summary> 199 /// <param name="columns">查询的结果列名称</param> 200 /// <returns>当前SQL实例</returns> 201 public Sql Select(params object[] columns) 202 { 203 return Append(new Sql("SELECT " + string.Join(", ", (from x in columns select x.ToString()).ToArray()))); 204 } 205 206 /// <summary> 207 /// 向SQL实例添加查询表（多个表相互连接，数量指数级增长） 208 /// </summary> 209 /// <param name="tables">查询的表名称</param> 210 /// <returns>当前SQL实例</returns> 211 public Sql From(params object[] tables) 212 { 213 return Append(new Sql("FROM " + string.Join(", ", (from x in tables select x.ToString()).ToArray()))); 214 } 215 216 /// <summary> 217 /// 向SQL实例添加group by语句 218 /// </summary> 219 /// <param name="columns">待分组列名称</param> 220 /// <returns>当前SQL实例</returns> 221 public Sql GroupBy(params object[] columns) 222 { 223 return Append(new Sql("GROUP BY " + string.Join(", ", (from x in columns select x.ToString()).ToArray()))); 224 } 225 226 /// <summary> 227 /// 数据表连接 228 /// </summary> 229 /// <param name="joinType">连接类型</param> 230 /// <param name="table">待连接表名称</param> 231 /// <returns>SqlJoinClause实例</returns> 232 private SqlJoinClause Join(string joinType, string table) 233 { 234 return new SqlJoinClause(Append(new Sql(joinType + table))); 235 } 236 237 /// <summary> 238 /// 数据表内连接 239 /// </summary> 240 /// <param name="table">待连接表名称</param> 241 /// <returns>SqlJoinClause实例</returns> 242 public SqlJoinClause InnerJoin(string table) 243 { 244 return Join("INNER JOIN ", table); 245 } 246 247 /// <summary> 248 /// 数据表外连接 249 /// </summary> 250 /// <param name="table">待连接表名称</param> 251 /// <returns>SqlJoinClause实例</returns> 252 public SqlJoinClause LeftJoin(string table) 253 { 254 return Join("LEFT JOIN ", table); 255 } 256 257 /// <summary> 258 /// 获取最终的SQL语句 259 /// </summary> 260 public override string ToString() 261 { 262 return SQL; 263 } 264 } 265 266 /// <summary> 267 /// SQL连接语句 268 /// </summary> 269 public class SqlJoinClause 270 { 271 /// <summary> 272 /// 待连接的SQL对象 273 /// </summary> 274 private readonly Sql _sql; 275 276 /// <summary> 277 /// 构造函数 278 /// </summary> 279 /// <param name="sql"></param> 280 public SqlJoinClause(Sql sql) 281 { 282 _sql = sql; 283 } 284 285 /// <summary> 286 /// 向SQL对象添加表连接条件 287 /// </summary> 288 /// <param name="onClause">表连接条件</param> 289 /// <param name="args">可选参数</param> 290 /// <returns>SQL对象</returns> 291 public Sql On(string onClause, params object[] args) 292 { 293 return _sql.Append("ON " + onClause, args); 294 } 295 }