CODEWITHSUNDEEP
ado.net
Chandan
Chandan

Reputation: 1504

Generic way to insert some data into a table

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.

Upvotes: 0

Views: 1050

Answers (3)

winner_joiner
winner_joiner

Reputation: 14845

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.

Upvotes: 0

Praveen
Praveen

Reputation: 56519

Have you heard about , 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.

Upvotes: 1

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

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.

Upvotes: 1

Related Questions