CODEWITHSUNDEEP
sqlasp.netsql-server
user3340627
user3340627

Reputation: 3143

Creating a dynamic number of parameters for Insert based on User's input

I'm building my application in 3-tier architecture. In my DataAccess class i got a function for inserting a Person into the database.

Person table has up to 81 columns in the database. The user may not insert all the data in these columns initially, so he might only fill 10 or 20 fields.

Is there a way i can make my Insert Function in the DataAccess class accept dynamic number of parameters so that it only passes those which have data and not be forced to wait for 81 parameters and try to insert 81 parameters each time ?

Upvotes: 0

Views: 120

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

A very good option when writing sprocs that insert rows into a single table is to use Table-Valued Parameters.

First, you need to create a table type that reflects the table into which you want to insert new records, e.g.

CREATE TYPE dbo.PersonType AS TABLE
    ( PersonID int, LastName nvarchar(50), FirstName nvarchar(50), ... etc )

Then, using this table type you can write a sproc that accepts table-valued parameters based on that type, e.g.:

CREATE PROCEDURE dbo.usp_InsertPerson (@tvpNewPersons dbo.PersonType READONLY)
AS 
BEGIN
    INSERT INTO Person (PersonID, LastName, FirstName)
    SELECT PersonID, LastName, FirstName
    FROM @tvpNewPersons
END

Once you have this infrastructure in place, then you can just pass as parameter to the Insert method of your DataAccess class a single parameter, i.e. a strongly-type DataTable that reflects the real sql table(btw you can even manually create such a DataTable).

This is how your Insert method would look like:

public void Insert(PersonDataTable dtPersons)
{

   // ... build connection object here with 'using' block 

   // Create command that executes stored procedure
   SqlCommand command = new SqlCommand("usp_InsertPerson", connection);
   command.CommandType = System.Data.CommandType.StoredProcedure;            

   SqlParameter tvpParameter = command.Parameters.AddWithValue("@tvpNewPersons", dtPersons);
   tvpParameter.SqlDbType = SqlDbType.Structured;

   // now execute command, etc ...
}

PersonDataTable is the type of the typed data table that you need to create.

Upvotes: 0

Sandeep
Sandeep

Reputation: 1210

Assign default value to your parameters in store procedure so that you do not have to pass all parameters from code. See below:

CREATE PROCEDURE USP_PERSON_INSERT  
 @FirstName VARCHAR(100),
 @LastName VARCHAR(100) = NULL
AS

Upvotes: 1

user230910
user230910

Reputation: 2372

A quick and dirty solution would be to pass in a dictionary of which has the name and value of the items you want to change:

public void Upsert(Dictionary<string, object> KeyValues, Dictionary<string, object> Changes)
{
    // insert logic here
}

PS: Take note that many ORM's solve this problem for you, go and google Entity Framework for example..

Upvotes: 1

Related Questions