Nrthugu

I am working on a Asp.Net website to insert data in the database. After reading different articles on internet where it is suggested that we should use a parametrized query as it prevents from SQL Injection attacks.

So I am wondering which way is better:

Create a Stored Procedure with parameters in the database and then call it in the button click event to insert the data in the database e.g.

CREATE PROCEDURE AddInfraction

@Description varchar(255), @Penalty money, @Points int

AS

  BEGIN

       INSERT INTO Infractions (Description, Penalty, Points)

       VALUES (@Description, @Penalty, @Points)

  END



using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))

        {

            using (SqlCommand command = new SqlCommand("AddInfraction"))

            {

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("Description", Description.Text);

                command.Parameters.AddWithValue("Penalty", Convert.ToInt16(Penalty.Text));

                command.Parameters.AddWithValue("Points", Convert.ToInt16(Points.Text));

                connection.Open();

                queryResult = command.ExecuteNonQuery();

                if (queryResult == 0)

                {

                    return;

                }

            }

            connection.Close();

        }

Or maybe first by adding the ADO.NET Entity Data Model in the database and then in the button click event creating the object of the data model recently added, and then call the particular stored procedure and inserting the data in the database.

using (ETrafficChallanSystemEntities eTrafficChallanSystemEntities = new ETrafficChallanSystemEntities())

{

  eTrafficChallanSystemEntities.AddInfraction(Description.Text, 

  Convert.ToInt16(Penalty.Text), Convert.ToInt16(Points.Text));

}

Which one would be the best way to insert data in the database.

Both the methods are good to insert data in the database. Using a parameterized query is always a good option. I would suggest using the second method as you have already added the ADO.NET Entity Model in your project.

as mentioned, this should be your preference. however with the recent trend of microservices patterns, it's likely better to tie less of your application logic to a database product. really depends on what the application is for and how often the backend will shift

There is not an absolute answer to this question. Any program(Application) has own requirement, but I think about some points on using each method.

A) Using Stored Procedures Pros:

• For the first time run is slow but for next times is so fast.


• Data Processing logic is unique and integrate, So changing that is
  so easy, understandable, fast, liable and clean.


• Migrating from one IDE/Language/Maybe Framework to another is easy because most business logics are in DB layer, So many of cod changing converting stored procedure call in the new language.

B) Using the Entity Data Model Pros:

• Didn't spend the time to write safe and powerful Stored procedures.




• Data Processing logic can be changed slowly and through the coding
  progress.




• Having meaningful and clear sight of DB entities(Tables/Views and
  relations) In coding time.




• can be changed DataBase architecture in some scenarios such as
  Code-First programming.

After all, I think Maybe better to keep Entity Model way for the most changeable/front-end related/little process section and Using Stored Procedures for persistent/Deep Backend related/Huge and multi-process sections.

I don't recommend using Stored Procedure for simple read/write queries, for the following reasons:

• Some logic is implemented in the database and is outside your source control


• Harder to maintain


• More verbosity for the project

For your case Entity Framework (Microsoft Database Framework) can cover almost 90% of cases.

Of if you want more control over your Sql Queries, you can use a Query Builder like Sql Kata (I am the author of this library)