Updating or inserting into a normalized SQL Database

Question

I have a general question. When inserting or updating into a normalized table, can you use a regular insert or update SQL statement or is this where you would need to use Stored Procedures? I am writing an MVC application and decided not to use EF or LINQ so I can learn SQL at the same time while making it.

You can use regular insert or update regardless of whether your table is normalized or not. — Sergey Kalinichenko, Dec 30 '13 at 01:38

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

Yes. You can use a regular insert or update. Think of stored procedures as aliases for SQL statements. Take a look at this: What is a stored procedure?

edited May 23 '17 at 12:03

Community

1
1

answered Dec 30 '13 at 01:40

brunch875

839
5
15

score 0 · Answer 2 · answered Dec 30 '13 at 01:49

You can use either. Using the System.Data.SqlClient namespace for SQL Server is best from memory and performance. EF allows you to specify stored procedures for all operations, that would be a simple step in the right direction. The only experience you'd lack would be in connection/state management.

Invoking a SELECT/INSERT/UPDATE/DELETE etc... statement directly from code is definitely possible however for consistency sake and maintenance reasons I would default to stored procedures for all CRUD operation...

Good syntactical structure:

using( System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection("<<connection string>>)
{
     using(System.Data.Common.DbCommand cmd = conn.CreateCommand())
     {
         cmd.CommandType = <Choose either TEXT or StoredProcedure>;
         cmd.Text = "<T-SQL or name of sproc:"dbo.ProcedureName">;
         using( System.Data.Common.DbDataReader rdr = cmd.ExecuteReader() )
         {
              //if only reading 1 row
              if( rdr.Read() )  // the .Read() method will read row by row
              {

                  string s = (string) rdr["Column"];  //<-- best to always check for nulls.
              }

         }
}

You must always take care of DB connections, ensuring that all connections are closed/disposed is something you must do.

The using structured code shown above is a good model especially for OLEDB commands, however for ADO.NET I pretty much only wrap Connections and Readers in USING statements.

Updating or inserting into a normalized SQL Database

2 Answers2