c# sql insert with a lot of parameters

Question

Is there an elegant way to write an insert statement, which has a lot of parameters? I have like 20 flags that need to be saved in the DB, plus other information that also require to be inserted, along with those 20 flags. Writing a method with 30 parameters for that insert is a waste of time, but so far I haven't come up with other ideas, that might work.

In what way is it a "waste of time"? It might be an ugly design choice, but I'm not sure how it's wasting time. — Adam Robinson, Apr 25 '12 at 14:18
I think you are looking for an ORM. I would opt for this if possible. — Adrian Iftode, Apr 25 '12 at 14:25
When you say "flags" do you possibly mean an implementation that could be represented by `[Flags] enum MyFlags { ... }`? If so, you could easily represent those 20 flags with one variable. — Mike Guthrie, Apr 25 '12 at 14:34
By flags I mean a bool variable, and no, those will be stored separately, I can't make one variable. — Rocshy, Apr 25 '12 at 14:42
It sounds like you could use enum flags to accomplish what you want. It is a single parameter to pass. Flags exists for true, not for false. It does mean you have to add more logic for bitwise operations to construct and deconstruct the parameter, however. Read over [Enumeration Types as Bit Flags](http://msdn.microsoft.com/en-us/library/cc138362.aspx). — Mike Guthrie, Apr 25 '12 at 15:00

score 6 · Accepted Answer · answered Apr 25 '12 at 14:18

6

You can put all these parameters in an object and pass that object to the method.

answered Apr 25 '12 at 14:18

Sachin Kainth

45,256
81
201
304

score 3 · Answer 2 · answered Apr 25 '12 at 14:18

3

A standard approach is to create a class representing your entity and then pass around an instance of that class.

This makes it easy to pass to other methods that may do validation, presentation, etc., as well as persistence.

answered Apr 25 '12 at 14:18

D'Arcy Rittich

167,292
40
290
283

score 1 · Answer 3 · answered Apr 25 '12 at 14:27

1

Agree with Sachin, encapsulate the list of SqlParameters that you need inside a class and

then pass the instance to the datalayer for further processing of the SQL queries.

answered Apr 25 '12 at 14:27

this-Me

2,139
6
43
70

score 1 · Answer 4 · answered Apr 25 '12 at 14:27

You could create an Interface that represents type of object that can be inserted in you database such as Iinsertable that expose a method such as getFields. With that, you create entities that implement this interface.

With those entities, in you database layer you can create a generic method that accept a Iinsertable as a parameter, you that query all the fields to be inserted with the getFiels method in a loop. This will make your code reusable for every type of entities and it will be clean as hell.

score 0 · Answer 5 · edited May 23 '17 at 10:25

Use my SqlBuilder class. It lets you write paramaterized queries without ever having to worry about creating the parameters, or what they're called.

var bldr = new SqlBuilder( myCommand );
bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId, SqlDbType.Int);
//or
bldr.Append("SELECT * FROM CUSTOMERS NAME LIKE ").FuzzyValue(myName, SqlDbType.NVarChar);

You can have as many flags as you like. Just call Value() each time you want to include a runtime value in your query. The creation of parameters is hidden, and you're just left with a neat SQL query.

the code of the SqlBuilder class is here

c# sql insert with a lot of parameters

5 Answers5