Discuss "Parameterized Dynamic Query" in the .Net Development forum.

Shem · #1 (**permalink**) June 6th, 2008, 09:47 AM

hi, i need to convert this example as it uses a stored procedure and i won't.

Code:

 Dim myCommand = New MySqlCommand(QueryProjectsDB.Save(myProjects.Idkey), myConnection)
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Parameters.AddWithValue("@id", myProjects.Idkey)
                myCommand.Parameters.AddWithValue("@street", myProjects.TheDate)
                myCommand.Parameters.AddWithValue("@houseNumber", myProjects.Name)
                myCommand.Parameters.AddWithValue("@zipCode", myProjects.ProjectNumber)
                myCommand.Parameters.AddWithValue("@city", myProjects.Description)
                Dim returnValue As DbParameter
                returnValue = myCommand.CreateParameter
                returnValue.Direction = ParameterDirection.ReturnValue
                myCommand.Parameters.Add(returnValue)
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                result = Convert.ToInt32(returnValue.Value)
                myConnection.Close()

Thanks

Shem

jmurrayhead · #2 (**permalink**) June 6th, 2008, 09:57 AM

Hmmm...something about this doesn't quite look right. Why are you calling your DAL in your MySQLCommand?

And why won't you use a Stored Procedure?

Anyway, change CommandType.StoredProcedure to CommandType.CommandText, create a dynamic SQL string, but replace the variables in the string (i.e. @id) to a question mark.

Code:

Dim sql As String
sql = "INSERT INTO yourtable (id, street, housenumber, ...) VALUES(?,?,?, ...)"
Dim myCommand = New MySqlCommand(sql, myConnection)
                myCommand.CommandType = CommandType.CommandText                
                myCommand.Parameters.AddWithValue("@id", myProjects.Idkey)
                myCommand.Parameters.AddWithValue("@street", myProjects.TheDate)
                myCommand.Parameters.AddWithValue("@houseNumber", myProjects.Name)
                myCommand.Parameters.AddWithValue("@zipCode", myProjects.ProjectNumber)
                myCommand.Parameters.AddWithValue("@city", myProjects.Description)
                Dim returnValue As DbParameter
                returnValue = myCommand.CreateParameter
                returnValue.Direction = ParameterDirection.ReturnValue
                myCommand.Parameters.Add(returnValue)
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                result = Convert.ToInt32(returnValue.Value)
                myConnection.Close()

If you're calling your DAL like that, then it's completely wrong. In fact, all of this code above, should BE IN your DAL.

Shem · #3 (**permalink**) June 6th, 2008, 10:09 AM

In My DAL i have a class that holds my queries, i did this for the fact that
if i need to call data with diff filters, i simply just call the right query, and don't have to rebuild the function that talks to the db and gets the results.

But as you said, why not use a stored procedure, well it's easier for me or some
other developer to quickly write a query and save it in the query class and use it in where he needs it and then doesn't need to re-write anything else.

Data collection will always be the same from a specific table, but the filtering
that data can always change, get my drift.

Shem

Shem · #4 (**permalink**) June 6th, 2008, 10:12 AM

Quote:

Originally Posted by jmurrayhead

If you're calling your DAL like that, then it's completely wrong. In fact, all of this code above, should BE IN your DAL.

IT is dude

Wolffy · #5 (**permalink**) June 6th, 2008, 10:20 AM

JMH got in here while I was typing out an example, but I'll post it anyway:

Code:

Dim sqlStr as String = "INSERT INTO someTable " + _
         "Values(@id, @street, @houseNumber, @zipCode, @city)"

Dim myCommand = New MySqlCommand(sqlStr, myConnection)
 
myCommand.Parameters.AddWithValue("@id", myProjects.Idkey)
myCommand.Parameters.AddWithValue("@street", myProjects.TheDate)
myCommand.Parameters.AddWithValue("@houseNumber", myProjects.Name)
myCommand.Parameters.AddWithValue("@zipCode", myProjects.ProjectNumber)
myCommand.Parameters.AddWithValue("@city", myProjects.Description)

Dim rowsAffected as Int32

myConnection.Open()
rowsAffected = smyCommand.ExecuteNonQuery()
myConnection.Close()

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Query Counts	Jaykappy	Microsoft Access	13	May 14th, 2008 01:39 PM
SQL VBA Query	Jaykappy	Microsoft Access	12	May 8th, 2008 09:30 AM
Using Dropdown List Value in Database SQL Query	richyrich	Classic ASP	0	April 2nd, 2008 10:05 AM

The Following 2 Users Say Thank You to Wolffy For This Useful Post:
jmurrayhead (June 6th, 2008), Shem (June 6th, 2008)

Discuss "Parameterized Dynamic Query" in the .Net Development forum.

.Net Development - Learn about the Microsoft.Net framework and how to create powerful web-based (ASP.net) and client-based (Windows Forms) applications utilizing various languages such as C#, VB.Net, J# and others.