Parametrized Query in Visual C# 2005

Parametrized Query in Visual C# 2005

• Nilesh Patel

  you can still use a dataAdapter and SQLCommand in VS2005 and fill the typed dataset.....

  i just don't think it is possible to dynamically set the SQL string for a tableAdapter as the tableAdapter is built at design time.

  You would use a stored procedure to solve your problem by accepting a paramter of varchar that accepts your SQL WHERE clause and appends it to a SQL string in the stored procedure itself then runs as a ExecuteSQL or Oracle equivalent. Problem with this is that it is viewed as a security vulnerability with regards to SQL injection. And having a stored procedure like this doesn't buy you any time as it still has to be recompiled everytime you run it.

  
  

• JohnnyP34

  Hello,

  I meant my query which is actually added to my TableAdapter.

  Thanks

  
  

• IgorP

  Thanks Koder and devstuff for your helps,

  As I understood, TableAdapters aren't capable to provide me with some properties like OracleDataAdapter. By OracleDataAdapter i can change my SQL according to what user enters, so i can restructure it and i'm not limited to passing just parameters. For instance I can have:

  oracleDataAdapter1.SelectCommand.CommandText = "Select * From Mytable1,Mytable2,Mytable3 Where ..........";

  Thanks anyways for your help.

  
  

• Ross B.

  Sorry M,

  I have no idea either.  I never use the tableAdapters.  I much prefer the old DataAdapter, SQLConnection, SQLCommand way of doing things. 

  I think tableAdapters are compiled/created at design time, which is why you have to use the wizards to fill them.  Unless you start monkeying about with reflection emit and compile you own class at runtime, i would personally use the old methods....

  I would just keep the existing dataset and create a SQLCommand and DataAdapter to fill it

  But these are my thoughts and what do i know - i'm just a monkey....

  Sorry M.....

   

  
  

• pappascd

  You have two options here, you can make a stored procedure or you (what I think you are after) can add a fillby method.
  You can do this by bindingSource on the form and clicking the little [>] arrow that appears and then add query. This will load a dialog box where you want to click Query Builder (Bottom Right). I warn you know it is very easy to over click next and miss this option completely.
  
  Once in there you find the column for which you want to filter by and put in @myVar where myVar is a subtable name for the variable your filtering by. Hit OK, and you get taken back to the dialog box. From there keep hitting next and you will get a query tool bar put on the form. You can use this if you want but I normally just delete it.
  
  The good thing is that it makes the code for the new query for you. Open the source code for the form (double click the forms title bar in design mode) and scroll to the very bottom and there will be the code to fill the dataset will the new filtered results.
  Move this to where ever you want but you will want to change the passed variable to one of your own. :)
  
  Hope that helps.
  
  
  

• zr119_62

  Hi-

  TableAdapters do not provide the way to dynamically change the columns list in "Select * From" based on input.  The reason why is this may change the schema of the dataset and break updates.

  TableAdapters are however just a wrapper over the ordinary DataAdapter (or OracleDataAdapter) in your case.  You can extend the TableAdapter in your own Partial Class to add a custom query method, and inside the method you can get at the internal DataAdapter to reconfigure it. You have full control over this DataAdapter just like you did with VS 2003.   

  One example of customizing the TableAdapter is shown here:

  http://blogs.msdn.com/smartclientdata/archive/2006/08/09/693113.aspx

  What you'd want to do instead is define your own query method that alters the internal adapter commands based on parameters. 

  Another alternative (mentioned above) is to write a Stored Procedure that changes its output based on the input.  Again you just have to be careful because the data schema might change in an incompatible way.  If you run into problems there you might want to return a generic DataTable from your custom method.

  Let me know if you get stuck.

  Thanks,

  Paul

  VB Team

   

  
  

• Mark Freeman

  Hi again,
  Maybe the thing you are looking for is in fact the dataset design view. If you double click your dataset in the solution explorer it will load the dataset designer where you can set the kind of parameters you are talking about.You can change and edit the default select, update and delete parameters. Both in the parameters collection that is expected and the parameters passed to the query ( Image 0.0 ).
  
  Having never worked with dataapadapters I am not sure what you are after but I am sure I must of done a similar problem my self at some point.
  
  Hope this helps, if not then maybe explain the what your trying to do and what you would like out of the database. :)
  
  
  
  
  

• ReneeC

  I should also add that you can do almost everything you can in a tableAdapter that you can in a stored procedure.
  
  
  

• RAYMOND KNIGHT

  sorry to post again, just read koder monkey maybe he understands better. Seems to be the answer.
  
  

• gm64

  Thanks so much Koder,

  Suppose that I write my WHERE clause according to search criteria that user enters on the form, and I have a Query in my TableAdapter. How do I attach this WHERE clause to my SQL in this query.

  I don't understand how you use SPs and Functions in such situations.

  I have a deadline, and i don't want to go back to Visual C# 2003 and use DataAdapter. So, your fast response would be tremendously appreciated.

  Thanks again

  
  

• sagittarian

  given query
  

• Thegamingchoice

  Hello,

  Thanks. I don't know how to use stored procedure in this case and I believe BINDING SOURCE has just FILTER property for my purpose, which has limited functionalities.

  I'm not sure if you exactly understood my problem or not. As Monkey said, I 'm looking for something like SQLCommand Property in DATAADAPTERS (in Visual C# 2003) for my TABLEADAPTER (in Visual C# 2005) to fill my dataset by it

  Thanks

  
  

• s_ruchit

  sorry, i want to help, i'm just not understanding the probem. You say you create a dynamic SQL string depending on conditions within your code

  the only part of your SQL query that would change - i would expect - is your WHERE clause.

  Could you give an example of what is preventing you from building a dynamic SQL string

  I have overcome challenges like this in the past, but all these problems disappeared when i begain using stored procedures and functions.

  k

  
  
• Parametrized Query in Visual C# 2005