Generic way to insert some data into a table

Question

I have always been using nhibernate ORM for inserting data to sql tables from application. But recently I tried reading on Ado.net and found suggestion to use stored proc instead of sqlcommand.executenonQuery().

In that case, every table insertion will need a different stored proc . A 100 table application will need 100 Stored procs. Is my understanding correct or is there a better way of doing it in a more generic way?

Please suggest.

Yuriy Galanter · Answer 1 · 2013-06-19T04:07:42.990

A simple one-liner command can be an INSERT given directly in .NET code via parameterized Command class. Something like:

using (SqlConnection sqlConn = new SqlConnection(connectionString)) {
        using (SqlCommand sqlCmd = new SqlCommand("INSERT INTO MyTable (Field1, Field2) VALUES (@Param1, @Param2)", sqlConn)) {
            sqlCmd.Parameters.AddWithValue("@Param1", someValue1);  
            sqlCmd.Parameters.AddWithValue("@Param2", someValue2); 
            sqlConn.Open();  
            sqlCmd.ExecuteNonQuery();  
        }  
    }

So it doesn't have to be a stored proc for every command. You can have a class or classes dedicated to DB access only (db access layer) and populate it with various methods to read/write from DB. You can even have a generic method that automatically derives parameters for INSERT/UPDATE commands.

Of course if it's more than 1-2 commands or some logic is involved - that asks for a stored procedure.

Btw, this is my personal opinion, but i think ORMs are evil.

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

1

Have you heard about dapper, a powerful tool to execute a query and map the results to a strongly typed List. Dapper also support stored procedures, check this out.

Example:

dbConnection.Query<return type>("yourSP", parameters, 
        commandType: CommandType.StoredProcedure).First();

Also take some time to check this SO question.

edited May 23 '17 at 12:16

Community

1
1

answered Jun 19 '13 at 04:09

Praveen

55,303
33
133
164

score 0 · Accepted Answer · edited Oct 09 '16 at 11:16

0

Personally I would use an ORM if I have more than 5 different tables to select and/or insert into. Why should you walk 100 miles if the bus stop is right infront of the door?

That said the ORM is a generic way to access data. If you would want to code everything by hand, you could surely write stored procedures with optional parameters, but I don't recomend it.

edited Oct 09 '16 at 11:16

marc_s

732,580
175
1,330
1,459

answered Jun 19 '13 at 04:21

winner_joiner

12,173
4
36
61

Thanks for the suggestion. I thought I was missing some thing when i read "Use stored procedure for inserting data" and there is some generic stored proc that can be written to do it. – Chandan Jun 19 '13 at 04:31

Generic way to insert some data into a table

3 Answers3