C# - App - SQL parametes questions

[maxx233]

Hello,
After doing a couple small projects where I concatenated my SQL queries, I decided I needed to finally bite the bullet and do this the right way with parameters.

What I have so far is code that can insert to the DB, and gets the ID returned of the row that was just inserted. That DBID gets stored for later use by another part of my program.

So my question is, if I want to do a "SELECT someColumn FROM someTable WHERE DBID=theOneFromEarlier", is it correct to somehow do a SQL parameter on the DBID I'm passing, or at this point is it OK to simply concatenate that variable into my SQL statement?

If passing DBID as a parameter is still correct, how is that done when my end result is reading data? I know I could do it the same way I returned the DBID from my INSERT statement (Setting up a new SqlParameter, setting it's direction to output, etc) but this seems like I *lot* of overhead if I were setting it up for 15 fields I needed returned?

If it's standard practice to concatenate at this point, I can do that fine and get myself a DataReader as I was doing before with no problems. Any advice is much appreciated!

Maxx

[kenobewan]

You can select the max id in a query after you run the insert statement. I assume you execute your queries in try catch block. HTH.

[Plater]

You can use the DBID as a parameter in your 2nd query if you want, but instead of using .ExecuteNonQuery(), you would probably want to use an SqlDataAdapter and have it .Fill() a DataTable for you.

[krille]

You can use concat for SQL if u make it safe from sql-injections wich isnt that hard... I usally just make an static method in my DB class.

all you need to do is put ' around string and remove ' inside of string so this will work out:

" ' " + strInput.Replace(" ' ","") + " ' " (dont use white space in replace just did that now so you could read it)

Cheers
Krille

[Plater]

If you wanted to keep the ' in data (sometimes it's important) you would escape it with '' (that is two single quotes, not a double quote)

[maxx233]

For Google sake, here was the final solution I decided works for me:

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText =
"SELECT vendorName,vendorContact FROM venderList WHERE ID=@DBID;";
myCommand.Parameters.Add("@DBID", SqlDbType.Int).Value = myDBID;
SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();

Hope that helps anyone else looking for this sort of thing. If anyone else has anything to add/correct, feel free. Thanks for the help everyone!

Maxx