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()
{
SqlCommand myCommand = new SqlCommand(); //COULD BE = new SqlCommand(“storedProcedureName”)
myCommand.CommandType = CommandType.Text; //COULD BE = CommandType.StoredProcedure
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