CMD Param and SelectList

Jun 3, 2008 at 1:36 AM
I am not to familiar with command parameters. Can someone give me a tip on using the folowing.

List<Gallery> GaleryDataAccess.SelectList(string sql, System.Data.CommandType commandType, System.Data.SqlClient.SqlParameters[] parameters);

Lets say I want to SELECT * FROM Gallery WHERE Catagory = 'Missionaries'

Thanks for any help
Coordinator
Jun 8, 2008 at 4:32 AM


ad5qa wrote:
I am not to familiar with command parameters. Can someone give me a tip on using the folowing.

List<Gallery> GaleryDataAccess.SelectList(string sql, System.Data.CommandType commandType, System.Data.SqlClient.SqlParameters[] parameters);

Lets say I want to SELECT * FROM Gallery WHERE Catagory = 'Missionaries'

Thanks for any help


Hi ad5qa,

If you just want to execute your SQL statement as-is, you can call the SelectList method like this:

List<Gallery> myList = dataAccessInstance.SelectList("SELECT * FROM Gallery WHERE Category='Missionaries'", CommandType.Text, new SqlParameter[] { });

Another way to use it would be to create a parameter for the value you want to pass to the query, like this:
SqlParameter p = new SqlParameter("@Category", "Missionaries");
List<Gallery> myList = dataAccessInstance.SelectList("SELECT * FROM Gallery WHERE Category=@Category", CommandType.Text, new SqlParameter[] { p });

I hope that clarifies it for you. If you have any questions, please feel free to reply to this thread.

Thanks.
-Jason

Jun 9, 2008 at 12:41 AM
Very nice, thanks for the example. How about to edit a record?

Would I just modify the parameters? I cant try it out right now but I thought I would ask for the est method. I am sure someone else would like a walkthru also, so this is a great forum for some FAQ.

SqlParameter p = new SqlParameter("@id", "4");
string sql = "UPDATE myTable SET active = 'Y' WHERE ID = @id ";
List<Gallery> myList = dataAccessInstance.SelectList(sql, CommandType.Text, new SqlParameter[] { p });
Coordinator
Jun 9, 2008 at 2:14 AM


ad5qa wrote:
Very nice, thanks for the example. How about to edit a record?

Would I just modify the parameters? I cant try it out right now but I thought I would ask for the est method. I am sure someone else would like a walkthru also, so this is a great forum for some FAQ.

SqlParameter p = new SqlParameter("@id", "4");
string sql = "UPDATE myTable SET active = 'Y' WHERE ID = @id ";
List<Gallery> myList = dataAccessInstance.SelectList(sql, CommandType.Text, new SqlParameter[] { p });



Hi ad5qa,

Usually, you would update an item with the other methods provided in the data access class. The idea is to use the strongly typed objects to move your records around your application and save them back to the database. The best idea I have for you is something like this:

//get the record from the database
Gallery gal = dataAccessInstance.Select(4); //assume you choose ID as the key for your row
gal.active = "Y";
//save the record back to the database
gal = dataAccessInstance.Save(gal);

All of the work is already done for you in the Select and Save methods. The only time you should need to pass your own SQL statements through the data access class are when you are doing something other than selecting by ID/Key or doing some other non-CRUD operation. I only added the SelectList method so that the data access class could be expanded easily. You might create your own stored procedure that still returns a full set of records, but performs some other logic, too. That is when you would use the SelectList method.

If that's as clear as mud, please reply.

Thanks.
-Jason
Jul 10, 2008 at 2:51 AM
Sweet, thanks for all the help.

I removed all the parameters in the select method except for id...

public

 

Announcements Select(int id)

I do my stuff with the ID then Save it back using the same ID #18. However, it creates a new item rather then updating. I will build an update statement be hand unless there is something I am missing. Actually to save time I will add then delete the previous if no errors occur.

Thanks.
Tom

 

Coordinator
Jul 10, 2008 at 3:25 AM

Hi Tom,

I'm unsure why you would only be able to insert. Did you re-generate the code and sql stored procedures or did you just take the parameters out of the Select method? You need to re-generate the code instead of just editing the methods after generation. You should also recreate the stored procedures in the database once they are generated correctly.

While the wizard is running, you should edit the KeyColumns collection for the table so that it only includes the ID column. I believe that will fix your problem once you deploy the newly generated stored procedures to your database.

Thanks.

-Jason

 

Oct 30, 2008 at 4:23 PM
Edited Oct 30, 2008 at 4:25 PM
I can see where the generated code would only perform an insert instead of an update. The Save procedure checks to see if a record exists for all parameters. If any one of those parameters were to change, which it would if you were updating the object properties and called the Save method, it would fall down to insert code in the stored proc.

Also, on SQL generation, any field that is text, ntext,  or an image should not be used in direct comparisons as it will throw an error when trying to create the procedures in the database.

Outside of these two issues the work is a great timesaver.

Thanks
Coordinator
Nov 6, 2008 at 2:07 AM
Hi. I'm sorry about the late response.

Yes, a common problem is that if you do not choose your key columns / remove those columns that are not key columns, you will get an sql script that uses all of the columns to select and save by. This is, quite frankly, useless. You MUST remove the columns that you do not want to use as keys so that the code that is generated will behave the way it should.

Thanks.
-Jason