Can i dynamically create a sql query statement in a method?

Question

Hi Is it possible to create? What are the pros and cons?

Suma M · Answer

Ashish An example will be good for me.. As i am a beginner understanding things about ado is a bit complex. How to use parameterized queries in this method?.

Ashish Shukla · Answer

Yes the approach you are saying is correct.The correct approach of creating data access layer is to create a common class that should have the methods for fetching as well as updating the database. Following steps should help you implement the approach you have said above 1) Create a data access class having the methods for fetching updating and deleting records.These should be common methods. It is better if you create stored procedures corresponding to these methods. 2) Create the entity classes representing the entities or tables in the database.This class should have properties you have mentioned above. 3) Create an instance of the data access class in the step one above.Create the instances of the entity class in the step 2 above and pass the instance of the entity class to the methods of the data access class.You can also assign the result of the data access class method to an entity class instance if you are fetching values.

Suma M · Answer

Yes in c# i want to have a method for insertingupdatingdeleting records to SQL table.. I was thinking of creating a class which will have properties to set the column name and column values..Is it still not a proper approach with regards to security.. If not can you please guide me to writing what i am thinking of as i am a beginner

Ashish Shukla · Answer

I assume you are asking how to create sql statements in c# code.We can definitely create sql methods dynamically.As it is easy to append stringsso if we have number of parameters that needs to be passed to the sql string then it is easy to create sql statements in this way.Though this is not the proper approach since if we create the procedures for sql statments then it is cached and execution is faster if it needs to be executed later. Following is an example: string sql = select * from table1 where name=' + name + ; Another disadvantage is sql injection as the end user can easily manipulate the sql statement strings by giving the wrong inputs intentionally. So the user can manipulate the sql statment to delete a database table or perform other harmful action.