Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

C# Return Complete SQL Query and Values?

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline
Suggested Courses

916 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