ADO.NET SQL query statement

Hot Topic

Collection events
Web Service client and imported common types
Connection not Alive
Source for Process.Start
Failed to access IIS metabase
Why Main must be static in C#?
Excel interop - crash on dispose call
Using my.log.writeExcption method to write to event log in a web service
Check conflict before update datas using a DataAdapter ?
Can't get a TCPClient Connection working

.NET Development

When is SQL Express Beta 2 Available.
HTTP Post with HttpWebRequest
Execute a list of SqlCommands
Slow regexp for first time match
MS SQL 2005
ISerializationSurrogate.SetObjectData and base classes
Empty rows appearing in typed dataset
Advice on Parameter problem with Odbc call returning multiple result sets
TCPListener & TCPClient (Solved, last post is the sourcecode)
Multilingual Web Sites and Globalization

ADO.NET SQL query statement

I seem to remember reading somewhere that ADO.NET Whidbey allows us to have a sort of parameterized text query.  In other words, I could use  CommandType.Text and say "SELECT * FROM myTable WHERE ID = ", but then have the paramater passed as a SqlParameter the way it would work if I had used CommandType.StoredProcedure instead of a text query.

Am I hallucinating this   If not, can anyone point me to more info on it    This approach seems useful in some cases if it guards against SQL injection attacks the way that stored procs do.   I hate having 150 one-line stored procs for "delete by unique ID" operations and the like.

Answer this question

ADO.NET SQL query statement

  • NomadaPT

    Thanks, guys!   Exactly what I needed!

  • ALZDBA

    Chaps,

    Any idea what this would look like under Visual Studio Visual Basic

    Not sure I need the complexity to solve this problem, however:

    I am trying to implement a "loop" that will iterate through a child table, extracting values one row at a time.  Once a row is extracted this row is marked as "used".

    a) Find next Unassigned values from TableTwo that are linked to TableOneID
    b) Read related values from the row
    c) Do processing on values from this row
    d) Mark row as Assigned
    e) Repeat until no unassigned rows for this TableOneID

    Is there a simple way to do this with SQL or do I need to create Reader



  • wackyspat

    You don't need Whidbey to used parameterized T-SQL queries - it works in ADO.NET 1.1 (and I believe 1.0, though I haven't tested it). The following example shows how to do it via SqlClient. Other providers, such as OleDb, don't understand named parameters (i.e. "@orderId") and force you to use positional parameters denoted by " " in your query.


    SqlConnection conn = null;
    SqlDataReader reader = null;
    try {
     conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=(local);");
     conn.Open();
     SqlCommand cmd = new SqlCommand("SELECT * FROM Orders WHERE OrderID = @orderId", conn);
     cmd.CommandType = CommandType.Text;
     cmd.Parameters.Add("@orderId", 10248);
     reader = cmd.ExecuteReader();
     while(reader.Read()) {
     for(int i=0; i<reader.FieldCount; i++) {
     Console.Write("{0}\t", reader//emoticons/emotion-55.gif" alt="Idea" />);
     }
     Console.WriteLine();
     }
    } finally {
     if(reader != null) {
     reader.Close();
     }
     if(conn != null) {
     conn.Close();
     }
    }
     


     



  • Alwin

  • ADO.NET SQL query statement
Answer this question