CODEWITHSUNDEEP
c#sqlwinforms
Rocshy
Rocshy

Reputation: 3509

c# sql insert with a lot of parameters

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.

Upvotes: 4

Views: 557

Answers (5)

bbsimonbb
bbsimonbb

Reputation: 29020

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

Upvotes: 0

Machinegon
Machinegon

Reputation: 1885

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.

Upvotes: 1

this-Me
this-Me

Reputation: 2145

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.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171539

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.

Upvotes: 3

Sachin Kainth
Sachin Kainth

Reputation: 46760

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

Upvotes: 6

Related Questions