Is there any way to write dynamic queries for select, insert, update instead of writing hard coded query in every page in asp.net c#

Question

User703311911 posted

I am new to asp.net. I just want to know that is there any way to write dynamic queries for select, insert, update etc instead of writing hard coded query in every page in asp.net c# so that we need not to change query in every page if needed. Please guide as i am new in asp.net.

here is my code for insert. Connection string is imported from helper.cs file.

public void insertmessages() {

string query = "insert into emailtable( receiver,username,subject,message)" + "values(@receiver,@username,@subject,@message)";

        helper NewDB = new helper(); // db class is instantiated...
        SqlConnection newCon;


        newCon = NewDB.GetConn();

        newCon.Open();
        SqlCommand cmd = new SqlCommand(query, newCon);

        cmd.Parameters.AddWithValue("@receiver", SqlDbType.VarChar).Value = receiver1;
        cmd.Parameters.AddWithValue("@username", SqlDbType.VarChar).Value = username1;
        cmd.Parameters.AddWithValue("@subject", SqlDbType.VarChar).Value = subject1;
        cmd.Parameters.AddWithValue("@message", SqlDbType.VarChar).Value = message1;

        cmd.ExecuteNonQuery();

        newCon.Close();
}

Answer 1

User-573138384 posted

You can use sp_execute for dynamically executing queries. But its advised not use until and unless very necessary. In your case I dont see any need for dynamic SQL. Why don't you use stored procs for this?

Using stored procedure is always a better practice instead of writing inline queries. SPs prevents SQL Injection. Start using them.

http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson07

https://support.microsoft.com/en-us/kb/310070

Answer 2

User36583972 posted

Hi ibraruet,

From your description, I suggest you can use SqlHelper. You can also custom extension methods to support your needs.

SqlHelper is a class which is used in the data access layer which is contain different method that be help us to perform CRUD operations. You will just create an object of this class and you can use it in your application.

Please refer the following information, I hope this will help you.

1: An ADO.NET SQL Helper Class:

http://www.blackbeltcoder.com/Articles/ado/an-ado-net-sql-helper-class

2: Examining the Data Access Application Block :

http://www.4guysfromrolla.com/articles/070203-1.aspx

Best Regards,

Yohann Lu