Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

C# Return Complete SQL Query and Values?

Posted on 2012-03-15
5
Medium Priority
?
339 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 664 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 668 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 668 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month12 days, 8 hours left to enroll

580 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