4

Possible Duplicate:
How to get the generated SQL-Statement from a SqlCommand-Object?

I would like to know how to retrieve the SQL query that was executed by a Table Adapter for logging purposes.

For example:

Input:

RandomTableAdapter tableAdapter = new RandomTableAdapter();
tableAdapter.Insert("val","val","val","val");

Output:

What I would like to log is the insert statement, which is generated by the table adapter in order to execute the insert command.

Insert INTO RandomTable VALUES ('val', 'val', 'val', 'val');

How can I do this? Does anyone have any suggestions regarding this?

Community
  • 1
  • 1
Toret
  • 91
  • 1
  • 14

1 Answers1

0

You can use this workaround with replace ParameterName with its value but it not good because you need to manage value formatting by your own, and it can be slow
and you will need to get parameters after Insert is executed

code:

var usersTableAdapter = new testDataSetTableAdapters.usersTableAdapter();


            usersTableAdapter.Insert(4, "home", "origin", "email@host.com", "realname", "spec", 1, "username", "usernick", "whereform");
            var cmd = usersTableAdapter.Adapter.InsertCommand;
            var text = cmd.CommandText;
            var sb = new StringBuilder(text);
            foreach (SqlParameter cmdParam in cmd.Parameters)
            {
                if (cmdParam.Value is string)
                    sb.Replace(cmdParam.ParameterName, string.Format("'{0}'", cmdParam.Value));
                else
                    sb.Replace(cmdParam.ParameterName, cmdParam.Value.ToString());
            }
            Console.WriteLine(sb.ToString());
Alexander V.
  • 1,518
  • 14
  • 14
  • Thank you, it gives me a starting point. I will see what I can do with the sample you provided. Much appreciated. – Toret May 28 '12 at 10:39