Hi,

I have a strange scenery in my application where I can´t bring the primary keys.

How to list all the others fields of the table without the Primary key, using TDL notation?

Please elaborate more.

And check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7723

Walaa wrote:

Please elaborate more.

And check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7723

Ok.

I have the code below:

public override int Add(<[ Foreach EntityField Comma ]><[ TypeOfField ]> <[ EntityFieldName ]> <[ NextForeach ]>, IDbTransaction dbTransction)
{
        int vretorno;

        SqlConnection dbCnn;
        if (!(dbTransction == null))
        {
            dbCnn = ((SqlTransaction)(dbTransction)).Connection;
        }
        else
        {
            dbCnn = new SqlConnection(conectionString);
            dbCnn.Open();
        }
        // StrSqlCommand recebe o nome da procedure ou o comando SQL propriamente dito.
        string StrSqlCommand = "U_sp_<[ CurrentEntityName ]>";
        SqlCommand dbCommand = new SqlCommand(StrSqlCommand, dbCnn);

        <[If HasFields]><[ Foreach EntityField CrLf ]>  
        dbCommand.Parameters.Add("@<[ EntityFieldName ]>", SqlDbType.<[SourceColumnDbType]>);
        dbCommand.Parameters["@<[ EntityFieldName ]>"].Direction = ParameterDirection.Input;
        dbCommand.Parameters["@<[ EntityFieldName ]>"].Value = <[ EntityFieldName ]>;           
        <[ NextForeach ]><[EndIf]>

        dbCommand.CommandType = CommandType.StoredProcedure;
        if (!(dbTransction == null))
        {
            dbCommand.Transaction = ((SqlTransaction)(dbTransction));
            vretorno = dbCommand.ExecuteNonQuery();
        }
        else
        {
            vretorno = dbCommand.ExecuteNonQuery();
            dbCnn.Close();
        }
        return vretorno;
}

My boss asked me to exclude the primary keys from the fields list generated by this piece of code.

I don't know well all the TDL tags from Template Studio, I tried some tags combinations to exclude the primary keys from the range of Entities but I didn't get.

May I ask why do you (or your boss) want to execlude the PKs?

As it's not a good idea at all. Please refer to the LLBLGen Pro manual: "Best practises -> Database best practises"

Walaa wrote:

May I ask why do you (or your boss) want to execlude the PKs?

As it's not a good idea at all. Please refer to the LLBLGen Pro manual: "Best practises -> Database best practises"

I agree it's not a good idea, but he (the boss) want to use conventional sql queries, not stored procedures. Te primary keys will be Identity (auto increment) in some tables, so I can't pass the primary key as parameter to the query, because Sql Server will return a error.

This template will be used in some sceneries, in others the primary key will be passed to the query string and the code above will be the choice.

Please help me to solve this problem.

You can use an <[ If IsPrimaryKey ]> statement inside the foreach loop to exclude / check on PK fields. and act accordingly.

Otis wrote:

You can use an <[ If IsPrimaryKey ]> statement inside the foreach loop to exclude / check on PK fields. and act accordingly.

Thank you!

The solution:

<[If HasFields]><[ Foreach EntityField CrLf ]> <[ If IsPrimaryKey ]> // The primary key <[ EntityFieldName ]> was removed. <[ Else ]> dbCommand.Parameters.Add("@<[ EntityFieldName ]>", SqlDbType.<[SourceColumnDbType]>); dbCommand.Parameters["@<[ EntityFieldName ]>"].Direction = ParameterDirection.Input; dbCommand.Parameters["@<[ EntityFieldName ]>"].Value = <[ EntityFieldName ]>; <[ EndIf ]> <[ NextForeach ]><[EndIf]>

My English is not very good, being like this please ignores my foolish mistakes. Thank you.

Sorry I've deleted your question. Would you please re-post it?

The thread appeared to me having your message repeated, so I thought you have accidently re-posted the same message twice, so I deleted the first one of them, to find out that both which appeared to me got deleted.

Hi,

Few months ago I was trying to remove the primary key from my query's parameters, so with some help of you guys I did. However I was using stored procedures, but I was forced to build the sql string into my code, also without the primary key.

When I tried to remove the PK, I got a problem.

Example:

Code: string sqlCommand = "INSERT INTO <[ CurrentEntityName ]> " + "<[If HasFields ]><[ Foreach EntityField Comma ]><[ If Not IsPrimaryKey ]><[ EntityFieldName ]><[ EndIf ]><[ NextForeach ]><[EndIf]> " + "VALUES " + "(<[If HasFields]><[ Foreach EntityField Comma ]><[ If Not IsPrimaryKey ]>@<[ EntityFieldName ]><[ EndIf ]><[ NextForeach ]><[EndIf]>)";

The Result:

Code:

string sqlCommand = "INSERT INTO Bank " + ", BankID, Name" + "VALUES " + "(, @BankID, @Name)";

The comma is controlled by the ForEach loop and any expression inside the loop is not verified before insert or not the comma. In this case the comma is always printed.

You have some suggestion to solve this problem using TDL tags?

Wondering I realize that a simple way to solve this problem could be add Not into the ForEach loop like <[ ForEach Not PrimaryKeyEntityField ]>, then the comma will be properly printed.

Cheers.

Hmm, very interesting problem... I always could get away with this, but your particular issue indeed requires an approach that's not possible with current TDL, as the IF statement is simply executed by the for loop and thus it emits a comma.

I don't really like the NOT in the foreach header as the foreach simply loops over the specified set. A 'not' then doesnt' make sense, at least I think it doesn't.

Is this template pretty big? Otherwise, you could decide to write an .lpt template instead. We added .lpt templates to overcome limitations in TDL. You can use lpt templates as include templates in TDL templates.

I will try rewrite this template in .lpt

Thanks.