0

i just tried to pass table type as a parameter in function of sql server

Scenario : i Have customer and sales tables.. When i put an entry in sales form that has to affect both tables. So i decided to create a function to pass both table values as a type and execute the process in it.But i couldn't. In this case Customer has one record and Sales has one or more records. Now i need to know how to declare function and how to execute it in C#.net.

customer 

cus_id                                
cus_name                              
cus_totpurchasecost

Sales 

cus_id              
sales_id         
item_id                  
item_qty               
item_cost

customer(1,'ddddd',10000) 

sales(1,1,2,2000)          
sales(1,2,6,1000)

Thanks in Advance

Mostafiz
  • 7,243
  • 3
  • 28
  • 42
selvin
  • 13
  • 3
  • What have you tried? Can you show us the function you tried to execute? Did you receive an error? – tobypls Apr 28 '16 at 07:22
  • You don't have to define a DB function, you can do this all from the C# code, that way you are in control of creating and inserting the records into your database. Creating a function and passing it a type seems like a bad idea. – progrAmmar Apr 28 '16 at 07:24
  • 1
    Possible duplicate of [How to pass table value parameters to stored procedure from .net code](http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) – Maximilian Riegler Apr 28 '16 at 07:26

2 Answers2

0

You have a few options here.

You could create a Stored Procedure to add your records to applicable tables (not a function) and then use ADO.NET to execute it. This is the "old school" approach and there are many examples online.

Commonly, you would create a stored procedure for each of the CRUD operations (Create, Read, Update, Delete) and then have methods in your C# code that call these Store Procs parsing the required params.

This is "safer" than constructing the SQL code in your C# code as it avoids the risk of SQL injection attacks (although not entirely).

See:

  1. How to create a Stored Procedure
  2. ADO.NET Basics

The other way would be to use an ORM (Object Relation Mapper) or MicroORM framework to make this work a bit easier. This is considered the "newer" way to do things.

See:

  1. Entity Framework (ORM)
  2. PetaPoco (MicroORM)
lukejkw
  • 1,054
  • 13
  • 19
-1

you can use cursor in side that function for row by row operation.

user3639616
  • 11
  • 1
  • 3