Tejana

Stored Procedures in LightSwitch

Posted in .net, LightSwitch, Microsoft, Software, SQL, Visual Studio by tejana on December 23, 2010

 

Demonstration of simple stored procedure in Microsoft LightSwitch beta 1, using SqlDataReader with connection string. My last posting did not show the details of a simple implementation of stored procedures in a Microsoft LightSwitch. Instead I used a code generation tool and left out the details of the connection string and calling the stored procedure. In this post I will show a simple detailed example. In fact I will use a simple select text instead of a stored procedure but the example could easily be changed to a stored procedure. This example uses a Data Reader for more information see DataReader vs DataSet. For more information about LightSwitch data see: The Anatomy of a LightSwitch Application Part 4 – Data Access and Storage

My last post shows how to create a new empty Microsoft LightSwitch Project and add a RIA services library 1 to the solution. To the RIA services library web project add a new item of type DomainService named DomainService1. Into the DomainService1.cs insert:

 

using System.Data;
using System.Data.SqlClient;

namespace RIAServicesLibrary1.Web
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ServiceModel.DomainServices.Server;

    public class JournalEntries
    {
        [Key]
        public int Id
        { get; set; }

        public string Account
        { get; set; }

        public byte Year
        { get; set; }

        public byte Period
        { get; set; }

        public DateTime DateTrans
        { get; set; }

        public decimal DebitAmount
        { get; set; }

        public decimal CreditAmount
        { get; set; }

        public string DocumentNumber
        { get; set; }

        public string Reference
        { get; set; }

        public string UserId
        { get; set; }

        public DateTime DatePosted
        { get; set; }

        public byte JournalType
        { get; set; }

        public short JournalNumber
        { get; set; }

        public string Source
        { get; set; }

        public bool Accrual
        { get; set; }

        public bool Correction
        { get; set; }

        public bool Consolidated
        { get; set; }

        public bool Up
        { get; set; }

        public bool Down
        { get; set; }

        public static JournalEntries Create(IDataRecord record)
        {
            return new JournalEntries
                       {
                           Id = (int) record["id"],
                           Account = (string) record["Account"],
                           Year = (byte) record["Year"],
                           Period = (byte) record["Period"],
                           DateTrans = (DateTime) record["DateTrans"],
                           DebitAmount = (decimal) record["DebitAmount"],
                           CreditAmount =
                                        (decimal) record["CreditAmount"],
                           DocumentNumber =
                                       (string) record["DocumentNumber"],
                           Reference = (string) record["Reference"],
                           UserId = (string) record["UserId"],
                              //INITIALIZE NULLS
                           DatePosted = new DateTime(1753, 1, 1),
                           JournalType = 1,
                           JournalNumber = 1,
                           Source = (string)record["Source"],
                           Accrual = (bool)record["Accrual"],
                           Correction = (bool)record["Correction"],
                           Up = (bool)record["Up"],
                           Down = (bool)record["Down"]
                       };
        }
    }

public static class JournalSource
    {
        public static IEnumerable<JournalEntries> AllJournalEntries()
        {
            //could be new SqlCommand("storedProcedureName")
            SqlCommand myCommand = new SqlCommand();
            //could be     CommandType.StoredProcedure;
            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText =
                "Select * From GL_JournalEntries";
            using(SqlConnection myConnection = new SqlConnection(
"Data Source=(local);Initial Catalog=TejanaA030;Integrated Security=SSPI;"))
            {
                myCommand.Connection = myConnection;
                    myConnection.Open();
                    SqlDataReader dr = myCommand.ExecuteReader();

                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            yield return JournalEntries.Create(dr);
                        }
                    }
                    dr.Close();
            }
        }
    }

    // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccess custom attribute to this class to
    // TODO: expose this DomainService to clients.
    public class DomainService1 : DomainService
    {
        [Query(IsDefault = true)]
        public IEnumerable<JournalEntries> GetJournalEntries()
        {
            return JournalSource.AllJournalEntries();
        }
    }
}

Return to the LightSwitch project and add a data source of type RIA service and add a screen for displaying the data (see last posting for details). The above code assumes you have a SQL data table with data:

 

/****** Object:  Table [dbo].[GL_JournalEntries]    Script Date: 12/23/2010 08:25:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GL_JournalEntries](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Account] [char](7) NOT NULL,
    [Year] [tinyint] NOT NULL,
    [Period] [tinyint] NOT NULL,
    [DateTrans] [date] NOT NULL,
    [DebitAmount] [decimal](19, 5) NOT NULL,
    [CreditAmount] [decimal](19, 5) NOT NULL,
    [DocumentNumber] [char](20) NOT NULL,
    [Reference] [char](35) NOT NULL,
    [UserId] [nvarchar](256) NOT NULL,
    [DatePosted] [date] NULL,
    [JournalType] [tinyint] NULL,
    [JournalNumber] [smallint] NULL,
    [Source] [char](10) NOT NULL,
    [Accrual] [bit] NOT NULL,
    [Correction] [bit] NOT NULL,
    [Consolidated] [bit] NOT NULL,
    [Up] [bit] NOT NULL,
    [Down] [bit] NOT NULL,
CONSTRAINT [PK_GL_JournalEntries] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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

%d bloggers like this: