Solved

C# Return Complete SQL Query and Values?

Posted on 2012-03-15
5
297 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 69

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 74

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now