LINQ tutorial Part -V

store procedures output variable & Functions work with LINQ:-

 

Hello Friends,

Today we will discuss how to handle the function and procedure that have output variable or the procedure that having scalar value.

For that just create a dbml file same as we talked in our privious discussion. On the Method panel just drop all the function and procedures.

Let start with a function

Suppose we have a function in sql which gives us out put in Upper case which is as below.

 

 if exists (select * from dbo.sysobjects so where id = object_id(N‘[dbo].[fn_MakeUpper]’) and so.xtype = ‘FN’)

drop function [dbo].[fn_MakeUpper]
GO 

CREATE FUNCTION fn_MakeUpper(@strInput AS VARCHAR(50))
RETURNS VARCHAR(50)
AS

BEGIN

RETURN(UPPER(@strInput))

END

GO

SET

QUOTED_IDENTIFIER OFF

GO

 

SET ANSI_NULLS ON
GO

 

Now drop this function on Method Panel of dbml. This function can we be use as below

Dim db as new MyDataBaseContext()

Dim lnq = from c in db.customers SELECT new with {.name= db.fn_MakeUpper(c.strFirstName)}

In the above manner we can use function.

Now we are taking example of output parameter parameter with store procedures suppose we have a output parameter in store procedure that will return total no of rows then.

We do that in following manner.

CREATE PROCEDURE 
@intTotalRecord AS int OUTPUT

Proc_tblProductReadWithCount

ASSELECT

 

lngId,

strCode,

strProductName,

lngProductPackingTypeId,

lngProductTypeId,

lngProductManufacturerId,

strRemark,

blnActive

FROM

tblProduct 

 

SELECT @intTotalRecord= COUNT(*) FROM tblProductGo 

1. drop the store procedure in method panel of database context dbml.

Now you will get method proc_tblProductReadWithCount(byref intTotalRecord As integer?)

To use this you have to declare a nullable integer first which is as follows.

Dim intA as integer ?

The “?” mark represet here that the variable can be nullable.

Dim

db As New BillMasterDataContext

Dim intA As Integer?
Dim lnq = From c In db.Proc_tblProductReadWithCount(intA) Select
cMe.lblText.Text = intA.Tostring()

In this way we can use output variable.

I hope you understand what I want to say

I will upload bunch of code related to article on

 

http://www.indiandotnet.tk in forth coming weeks.

Once again Thanks from

 your host

Rajat Jaiswal

Keep coding and share Knowledge

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s