Solved

C# Return Complete SQL Query and Values?

Posted on 2012-03-15
5
304 Views
Last Modified: 2012-03-17
For the purpose of troubleshooting, I would like to return the complete SQL syntax and values to a label for either desktop or web page. I tried using sqldataadapter and  commandbuilder and still only return the sql syntax and no values.

[Query]

private void InsertTable(string lastname, string midname, string firstname)
{
      string sql = " Insert Into Table1 (col1, col2, col3) values ('" + lastname + "', '" + midname + "', '" + firstname + "')";

     SqlConnection cn = new SqlConnection(cnPath);
     SqlDataAdapter da = new SqlDataAdapter(sql, cn);
     cn.Open();
     da.SelectCommand.ExecuteNonQuery();    

     label1.Text = da.InsertCommand.CommandText;

     cn.Close();
}

[Current Return]
Insert Into Table1 (col1, col2, col3) values ('lastname', 'midname', 'firstname')

[Desired Return]
Insert Into Table1 (col1, col2, col3) values (Doe, J, Jane)

Help!
0
Comment
Question by:pointeman
5 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 166 total points
ID: 37724482
if you are using a SQL database, start a profiler, you will see all the real queries that are sent to the database.
0
 
LVL 12

Accepted Solution

by:
Anuradha Goli earned 167 total points
ID: 37724572
This is the sample code I worked based on your conditions, review and do changes to relate to your database.

 
protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        InsertTable("Testdata", "Testprice", "TestDesc");
    }
    private void InsertTable(string Name, string Price, string Desc)
    {
        string sql = " Insert Into tblProduct (Name,Price,Desc) values ('" + Name + "', '" + Price + "', '" + Desc + "')";

        SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter(sql, cn);
        cn.Open();
        SqlCommand com = new SqlCommand();
        com.Connection = cn;
        da.InsertCommand = com;
        da.InsertCommand.CommandText = sql;
        da.InsertCommand.CommandType = CommandType.Text;
        da.InsertCommand.ExecuteNonQuery();
        label1.Text = da.InsertCommand.CommandText;

        cn.Close();
    }

Open in new window



Web.config

<connectionStrings>
  <add name="ConnectionString2" connectionString="Data Source=SQLDB1210;Initial Catalog=SampleDB;Persist Security Info=True;User ID=sa;Password=Password;MultipleActiveResultSets=False;Packet Size=4096;Application Name=&quot;Microsoft SQL Server Management Studio&quot;"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

Open in new window

0
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 167 total points
ID: 37724668
You are using string concatenation to build your queries (almost always a bad idea), so you should see the actual values stored in the string. If you are seeing the literal value of "lastname" output to the variable holding the query, then that means the lastname actually has the value "lastname" contained within it.

Now, if you were instead using parameterized queries (the better practice) to submit queries, then you would not see the values substituted, and you would have to do something like what emoreau mentioned.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 37728014
0
 

Author Closing Comment

by:pointeman
ID: 37732649
I am using parameterized queries although I didn't mention it, oops. It's a new concept and not learning how secure they really are.

I split the points because you all have excellent info. thx
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question