C# SqlStatement类代码示例

        /// <summary>
        /// Builds the statements that gets back the IDs for the inserted statement
        /// </summary>
        /// <param name="table"></param>
        /// <param name="autoPKColumn">Auto-generated PK columns for reference (i.e. AUTO_INCREMENT)</param>
        /// <param name="inputPKColumns">PK columns for reference</param>
        /// <param name="inputPKValues">PK values for reference</param>
        /// <param name="outputParameters">Expected output parameters</param>
        /// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
        /// <returns></returns>
        public virtual SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> pkColumns, IList<SqlStatement> pkValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions)
        {
            if (autoPKColumn.Count == outputParameters.Count)
                return "SELECT @@IDENTITY";

            var insertIds = new SqlStatementBuilder("SELECT ");
            insertIds.AppendFormat(" ({0})", SqlStatement.Join(", ", outputColumns));
            insertIds.Append(" FROM ");
            insertIds.Append(table);
            insertIds.Append(" WHERE ");
            bool valueSet = false;
            if (autoPKColumn.Count > 0)
            {
                insertIds.AppendFormat("{0} = @@IDENTITY", autoPKColumn[0]);
                valueSet = true;
            }
            for (IEnumerator<SqlStatement> column = pkColumns.GetEnumerator(), value = pkValues.GetEnumerator(); column.MoveNext() && value.MoveNext();)
            {
                if (valueSet)
                    insertIds.Append(" AND ");
                insertIds.AppendFormat("{0} = {1}", column.Current, value.Current);
                valueSet = true;
            }
            return insertIds.ToSqlStatement();
        }

 public UpsertQuery(DataContext dataContext, SqlStatement sql, SqlStatement idQuerySql, IList<ObjectInputParameterExpression> inputParameters,
     IList<ObjectOutputParameterExpression> outputParameters, IList<ObjectInputParameterExpression> primaryKeyParameters)
     : base(dataContext, sql,inputParameters)
 {
     OutputParameters = outputParameters;
     PrimaryKeyParameters = primaryKeyParameters;
     IdQuerySql = idQuerySql;
 }

		private SqlResult GetRawTableData()
		{
			SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(PHNullableType) );
			SqlStatement stmt = sb.GetStatement( true );
			// override generated statement to make sure we execute this without any type association
			stmt = new SqlStatement( StatementType.Select, Broker.Provider.GetCommand(), stmt.Sql );
			SqlResult sr = stmt.Execute();
			return sr;
		}

 public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
 {
     sql.Sql += ";SELECT LAST_INSERT_ID()";
     using (sql)
     {
         var rez = sql.ExecuteScalar();
         return new LastInsertId(rez);
     }
 }

        //public override string  GetInsert(string table, IList<string> inputColumns, IList<string> inputValues)
        //{
        //     return "BEGIN " + base.GetInsert(table, inputColumns, inputValues);
        //}

        public override SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions)
        {
            // no parameters? no need to get them back
            if (outputParameters.Count == 0)
                return "";
            // otherwise we keep track of the new values
            return SqlStatement.Format("SELECT {0} INTO {1} FROM DUAL",
                SqlStatement.Join(", ", (from outputExpression in outputExpressions select outputExpression.Replace(".NextVal", ".CurrVal", true)).ToArray()),
                SqlStatement.Join(", ", outputParameters.ToArray()));
        }

        public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
        {
            sql.Sql += ";Select SCOPE_IDENTITY() as id";

            using (sql)
            {
                var rez = sql.ExecuteScalar();
                return new LastInsertId(rez);
            }
        }

 public override SqlStatement ReviewSelectSql(SelectExpression select, SqlStatement sql)
 {
     const string ReadLockTemplate = "{0} \r\n FOR SHARE;";
       const string WriteLockTemplate = "{0} \r\n FOR UPDATE;";
       var flags = select.CommandInfo.Flags;
       if (flags.IsSet(LinqCommandFlags.ReadLock))
     return string.Format(ReadLockTemplate, sql);
       if (flags.IsSet(LinqCommandFlags.WriteLock))
     return string.Format(WriteLockTemplate, sql);
       return sql;
 }

 public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
 {
     if (!string.IsNullOrEmpty(idKey))
        {
        sql.Sql += (" returning "+EscapeName(idKey));
        }
     using(sql)
     {
         return new LastInsertId(sql.ExecuteScalar());
     }
 }

        /// <summary>
        /// Builds an insert clause
        /// </summary>
        /// <param name="table">Table name</param>
        /// <param name="inputColumns">Columns to be inserted</param>
        /// <param name="inputValues">Values to be inserted into columns</param>
        /// <returns></returns>
        public virtual SqlStatement GetInsert(SqlStatement table, IList<SqlStatement> inputColumns, IList<SqlStatement> inputValues)
        {
            if (inputColumns.Count == 0)
                return SqlStatement.Empty;

            var insertBuilder = new SqlStatementBuilder("INSERT INTO ");
            insertBuilder.Append(table);
            insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputColumns));
            insertBuilder.Append(" VALUES");
            insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputValues));
            return insertBuilder.ToSqlStatement();
        }

 public ResolvedSubStatementTableInfo (string tableAlias, SqlStatement sqlStatement)
 {
   ArgumentUtility.CheckNotNullOrEmpty ("tableAlias", tableAlias);
   ArgumentUtility.CheckNotNull ("sqlStatement", sqlStatement);
   
   _sqlStatement = sqlStatement;
   _tableAlias = tableAlias;
   
   var streamedSequenceInfo = sqlStatement.DataInfo as StreamedSequenceInfo;
   if (streamedSequenceInfo == null)
     throw new ArgumentException ("For a statement to be used as a table, it must return a sequence of items.", "sqlStatement");
   _itemType = streamedSequenceInfo.ResultItemType;
 }

    public ResolvedJoinedGroupingTableInfo (
        string tableAlias, 
        SqlStatement sqlStatement, 
        SqlGroupingSelectExpression associatedGroupingSelectExpression,
        string groupSourceTableAlias)
      : base (tableAlias, sqlStatement)
    {
      ArgumentUtility.CheckNotNull ("associatedGroupingSelectExpression", associatedGroupingSelectExpression);
      ArgumentUtility.CheckNotNull ("groupSourceTableAlias", groupSourceTableAlias);

      _associatedGroupingSelectExpression = associatedGroupingSelectExpression;
      _groupSourceTableAlias = groupSourceTableAlias;
    }

 public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit)
 {
     // return string.Format("SELECT FIRST {0} FROM ({1})", limit, select);
     var trimSelect = "SELECT ";
     if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect))
     {
         var selectBuilder = new SqlStatementBuilder(select);
         var remaining = select[0].Sql.Substring(trimSelect.Length);
         selectBuilder.Parts[0] = new SqlLiteralPart(remaining);
         return SqlStatement.Format("SELECT FIRST {0} {1}", limit, selectBuilder.ToSqlStatement());
     }
     throw new ArgumentException("Invalid SELECT format");
 }

 public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
 {
     if (idKey != null)
     {
         sql.Sql += ";SELECT last_insert_rowid()";
         using (sql)
         {
             var rez = sql.ExecuteScalar();
             return new LastInsertId(rez);
         }
     }
     sql.Execute();
     return LastInsertId.Empty;
 }

        public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
        {
            //sql.Sql += ";Select @@IDENTITY as id";
            using (sql)
            {
                sql.Execute();
                using (var idquery = new SqlStatement(sql.Db))
                {
                    idquery.SetSql("select @@IDENTITY as id");
                    var rez = idquery.ExecuteScalar();
                    return new LastInsertId(rez);
                }

            }
        }

 protected override SqlStatement GetLiteralEqual(SqlStatement a, SqlStatement b)
 {
     // PostgreSQL return NULL (and not a boolean) for every comparaison involving
     // a NULL value, unless the operator used is "IS" (or "IS NOT"). Also,
     // using those two operators when the right-hand value is not a literal
     // NULL is an error. The only possibility is to explicitly check for NULL
     // literals and even swap the operands to make sure NULL gets to the
     // right place.
     
     if (b.Count == 1 && b[0].Sql == "NULL")
         return SqlStatement.Format("{0} IS {1}", a, b);
     else if (a.Count == 1 && a[0].Sql == "NULL")
         return SqlStatement.Format("{0} IS {1}", b, a);
     else
         return SqlStatement.Format("{0} = {1}", a, b);
 }