is it me or is this useless

[thefox149]

I am new to asp.net I am really enjoying it however I have found something very inefficent when using sql server.

I have a dataset now I can poulate this in many ways but for this argument I will use 2

a sql string and calling a stored procedure
1.
sSQL = "SELECT * FROM tblCustomers WHERE Etc"
dapCustomersedit = New SqlDataAdapter("sSQL", cnn)
dsCustomersedit = New DataSet()
dapCustomersedit.Fill(sCustomersedit,"sp_customerdetails")
now I can do anything call the .update what ever. with few lines of code

Now when I read about sql inject ..I worry so I use stored procedures instead.

dapCustomersedit = New SqlDataAdapter("sp_customerdetails", cnn)
dapCustomersedit.SelectCommand.CommandType = CommandType.StoredProcedure
dapCustomersedit.SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int, 40))
dapCustomersedit.SelectCommand.Parameters("@CustID").Value = Session("sCustID")
dsCustomersedit = New DataSet()
dapCustomersedit.Fill(dsCustomersedit, "sp_customerdetails")

now I wanna update..so now I have to call another stored procedure this time an update sql and I have to pass and set every field with parameters on the stored procedure that heaps more code. I know you should have a layer of abstraction from the database but there must be a better way to update the stored procedure and what about code maintence if you add an extra field that you wish to pass a parameter to you have to do it in two places the stored procedure and the procedure in the code behind...is there another way or am I missing somthing?

My cat's name is sprinkles
-Ralph Wigam

 

[vbkris]

hi,

this is inherent in any approach that you take. assuming you dont use stored procedures, you will be writing your update stmts directly in ASP.NET, even there if you want to change something, there will be a code change...

Known is handfull, Unknown is worldfull

 

[thefox149]

true but if i use this
Select * FROM tblCustomers where custID = something

I have read/write the dataset and table adapter will take care of data updates. I know I can fill a dataset from a stored procedure however I can't just go tableadapter.update(storedprocedure) can I

My cat's name is sprinkles
-Ralph Wigam

 

[vbkris]

hmm,

interesting point. but then i guess that is the price that you pay for security.

however since you have qouted that the only reason that you are going for an SP is for protection against injection try taking a look at this thread.

thread855-1212720


Known is handfull, Unknown is worldfull

 

[chrissie1]

I have read/write the dataset and table adapter will take care of data updates. I know I can fill a dataset from a stored procedure however I can't just go tableadapter.update(storedprocedure) can I

Yes you can. But the question is, is this good practice? And the answer is no.

If you have a good layering then you should only need to change little. If you use nhibernate or any other Object-relational mapping then you only have to worry about one thing.

If you have select statements in a form then you know you are on the wrong track.



Christiaan Baes
Belgium

"My new site" - Me

 

[thefox149]

what about objects i can create a new dataset object. pass parameters to that ...then I can bind my controls to that only ..i am not too familar with them. I suppose the ojects looks to do the same thing add a layer inbetween the app and the dbase hmm I keep tossing and turning on the correct (doesn't look like there is such a thing) data access method

My cat's name is sprinkles
-Ralph Wigam

 

[vbkris]

>>Yes you can. But the question is, is this good practice? And the answer is no

you can use the same stored procedure to update back the data???

Known is handfull, Unknown is worldfull

 

[chrissie1]

I was talking about this.

tableadapter.update(storedprocedure)

You just set the corresponding SP's in the insert/delete/updatecommands and the update will work.

I only think you can use the same SP if it is a simple select

BTW NEVER USE SELECT *

Christiaan Baes
Belgium

"My new site" - Me

 

[vbkris]

>>I only think you can use the same SP if it is a simple select

i dont think even that is possible. but again, i say that because:
The Adapter knows the source only as an SP.



Known is handfull, Unknown is worldfull