Tech Off Thread

1 post

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Query in Azure Table

Back to Forum: Tech Off
  • User profile image
    RajDey

    1)      JOIN in Azure Table

    I am having RDBMS table name like SubscriptionFeatures, SubscriptionGroups, SubscriptionPlans,

    Subscriptions, Roles etc.

    Please find details below

    /****** Object:  Table [dbo].[SubscriptionPlan]    Script Date: 05/09/2012 15:14:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[SubscriptionPlan](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [nvarchar](256) NOT NULL,

          [Description] [nvarchar](max) NULL,

          [Status] [char](1) NOT NULL, 

     CONSTRAINT [PK_SubscriptionPlan] 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

    /****** Object:  Table [dbo].[Subscription]    Script Date: 05/09/2012 15:14:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Subscription](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [FromDate] [datetime] NULL,

          [ToDate] [datetime] NULL,

          [Status] [char](1) NOT NULL,

          [DateOfSubscription] [datetime] NOT NULL,

          [TimeZone] [time](7) NOT NULL,     

          [SubscriptionPlan_Id] [int] NOT NULL,

     CONSTRAINT [PK_Subscription] 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

    /****** Object:  Table [dbo].[SubscriptionGroup]    Script Date: 05/09/2012 15:14:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[SubscriptionGroup](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [nvarchar](256) NOT NULL,

          [Description] [nvarchar](max) NULL,

          [SubscriptionPlan_Id] [int] NOT NULL,

     CONSTRAINT [PK_SubscriptionGroup] 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

    /****** Object:  Table [dbo].[SubscriptionFeature]    Script Date: 05/09/2012 15:14:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[SubscriptionFeature](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [nvarchar](256) NOT NULL,

          [IsOptional] [bit] NOT NULL,

          [Description] [nvarchar](max) NULL,

          [SubscriptionGroup_Id] [int] NOT NULL    

    )

    GO

    /****** Object:  Table [dbo].[Role]    Script Date: 05/09/2012 15:14:20 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Role](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [nvarchar](256) NOT NULL,

          [Description] [nvarchar](max) NOT NULL,

          [Status] [char](1) NOT NULL,

          [Subscription_Id] [int] NOT NULL,

     CONSTRAINT [PK_Role] 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

    /****** Object:  ForeignKey [FK_SubscriptionRole]    Script Date: 05/09/2012 15:14:20 ******/

    ALTER TABLE [dbo].[Role]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionRole] FOREIGN KEY([Subscription_Id])

    REFERENCES [dbo].[Subscription] ([Id])

    GO

    /****** Object:  ForeignKey [FK_SubscriptionPlanSubscription]    Script Date: 05/09/2012 15:14:20 ******/

    ALTER TABLE [dbo].[Subscription]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionPlanSubscription] FOREIGN KEY([SubscriptionPlan_Id])

    REFERENCES [dbo].[SubscriptionPlan] ([Id])

    GO

    /****** Object:  ForeignKey [FK_SubscriptionPlanSubscriptionGroup]    Script Date: 05/09/2012 15:14:20 ******/

    ALTER TABLE [dbo].[SubscriptionGroup]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionPlanSubscriptionGroup] FOREIGN KEY([SubscriptionPlan_Id])

    REFERENCES [dbo].[SubscriptionPlan] ([Id])

    GO

    /****** Object:  ForeignKey [FK_SubscriptionGroupSubscriptionFeature]    Script Date: 05/09/2012 15:14:20 ******/

    ALTER TABLE [dbo].[SubscriptionFeature]  WITH CHECK ADD  CONSTRAINT [FK_SubscriptionGroupSubscriptionFeature] FOREIGN KEY([SubscriptionGroup_Id])

    REFERENCES [dbo].[SubscriptionGroup] ([Id])

     

    Currently I am having below query with join among different tables to get required data.

     

    var subscriptionFeatures = (from sf in context.SubscriptionFeatures

                                                from sg in context.SubscriptionGroups

                                                where sf.SubscriptionGroup.Id == sg.Id

                                                from sp in context.SubscriptionPlans

                                                where sg.SubscriptionPlan.Id == sp.Id

                                                from s in context.Subscriptions

                                                where s.SubscriptionPlan.Id == sp.Id

                                                from r in context.Roles

                                                where r.Subscription.Id == s.Id

                                                where r.Id == roleId

                                                where r.Status == "Y" && s.Status == "Y" &&    

                                                sp.Status == "Y"

                                                select new

                                                {

                                                    SubscriptionFeatureId = sf.Id,

                                                    SubscriptionFeatureName = sf.Name,

                                                    Description = sf.Description,

                                                    IsOptional = sf.IsOptional,

                                                    SubscriptionGroupId = sg.Id,

                                                    SubscriptionGroupName = sg.Name,

                                                    SubscriptionPlanId = sp.Id,

                                                    SubscriptionPlanName = sp.Name

                                                }); 

    This is my azure table structure.

    Can you please send me the code snippet to get the same result set using my below mentioned azure tables

     

    Field Name

    PK

    RK

    Kind

    Feature_Id

    Name

    Description

    Status

    FromDate

    ToDate

    DateOfSubscription

    subscriptionPlan

    subscriptionPlan

    sp_GUID1

    sp

     

    Music Store Basic

    Basic Plan

    Y

     

     

     

    subscriptionPlan

    sp_GUID2

    sp

     

    Music Store Professional

    Music Store Professional

    Y

     

     

     

    subscriptionPlan

    sp_GUID3

    sp

     

    Music Store Ultimate

    Music Store Ultimate

    Y

     

     

     

    subscriptionPlan

    sp_GUID4

    sp

     

    Silver

    Silver Plan

    Y

     

     

     

    subscriptionPlan

    sp_GUID5

    sp

     

    Gold

    Golden Plan

    Y

     

     

     

    subscriptionPlan

    sp_GUID6

    sp

     

    Diamond

    Diamond Plan

    Y

     

     

     

    Subscription

    sp_GUID1

    s_GUID1

    s

     

     

     

     

    3/7/2011 0:00

    3/7/2036 0:00

    3/7/2011 0:00

    sp_GUID1

    s_GUID2

    s

     

     

     

     

    3/7/2011 0:00

    3/7/2036 0:00

    3/7/2011 0:00

    sp_GUID1

    s_GUID3

    s

     

     

     

     

    3/7/2011 0:00

    3/7/2036 0:00

    3/7/2011 0:00

    sp_GUID1

    s_GUID4

    s

     

     

     

     

    3/31/2011 0:00

    4/30/2011 0:00

    3/31/2011 12:36

    sp_GUID2

    s_GUID5

    s

     

     

     

     

    6/15/2011 0:00

    5/31/2013 0:00

    6/15/2011 10:09

    sp_GUID3

    s_GUID6

    s

     

     

     

     

    6/17/2011 0:00

    6/30/2011 0:00

    6/17/2011 4:42

    subscriptionGroup

    sp_GUID1

    sg_GUID1

    sg

     

    Shopping Cart

    Shopping Cart

    Y

     

     

     

    sp_GUID1

    sg_GUID2

    sg

     

    Shopping Cart

    Shopping Cart

    Y

     

     

     

    sp_GUID2

    sg_GUID3

    sg

     

    Default Group

    Default Group

    Y

     

     

     

    sp_GUID3

    sg_GUID4

    sg

     

    Book

    Book Page Group

    Y

     

     

     

    subscriptionFeature

    sg_GUID1

    sf_GUID1

    sf

     

     

    Home Page

    Y

     

     

     

    sg_GUID1

    sf_GUID2

    sf

    f_GUID1

    About

    About Page

    Y

     

     

     

    sg_GUID2

    sf_GUID3

    sf

    f_GUID1

    View Book

    Book Page

    Y

     

     

     

    sg_GUID3

    sf_GUID4

    sf

    f_GUID2

    Edit Book

    Edit Book

    Y

     

     

     

    sg_GUID4

    sf_GUID5

    sf

    f_GUID3

    Login to application

    Login to application

    Y

     

     

     

               

    Field Name

    PK

    RK

    Name

    Description

    Status

         

    Role

    s_GUID1

    R_GUID1

    Administrator

    Administrator

    Y

         

    s_GUID1

    R_GUID2

    Administrator

    Administrator

    Y

         

    s_GUID2

    R_GUID3

    Administrators

    Music Store Administrators

    Y

         

     

     

    2)      I am not getting any code snippet for EGT Azure Table. If possible please send code snippet to use EGT.

     

     

    Below mentioned code is working fine

    public bool UpdateSubscriptionFeature(SubscriptionFeature subscriptionFeature)

            {

                bool status = false;

                try

                {

                    string AzureTableName;

                    AzureTableName = "WASubscriptionDtls";//Windows Azure Table Name

                    DataSource objDataSource;

                    objDataSource = new DataSource(AzureTableName);

     

                    WASubscriptionDtls subscriptionFeatureEntity = (from sf in

      objDataSource.WASubscriptions()

      where sf.Kind == DataModelConstant.subscriptionFeature                                                                                                                                 

      select sf).FirstOrDefault();

     

                    WASubscriptionDtls featureEntity = (from f in 

                    objDataSource.WASubscriptions()

                    where f.Kind == DataModelConstant.subscriptionPlan && f.Status == "Y"

                    select f).FirstOrDefault();

     

                    if (subscriptionFeatureEntity != null)

                    {

                        subscriptionFeatureEntity.Name = subscriptionFeature.Name;

                 subscriptionFeatureEntity.Description = 

                 subscriptionFeature.Description;

                        subscriptionFeatureEntity.IsOptional =

                        subscriptionFeature.IsOptional;

                        objDataSource.Update(subscriptionFeatureEntity);

                        objDataSource._serviceContext.SaveChangesWithRetries();

     

                        if (featureEntity != null)

                        {

                            featureEntity.Name = subscriptionFeature.Feature.Name;

                            featureEntity.Code = subscriptionFeature.Feature.Code;                        

                            objDataSource._serviceContext.UpdateObject(featureEntity);

                            objDataSource._serviceContext.SaveChangesWithRetries();

                        }

                    }

                   status = true;

                }

                catch (Exception ex)

                {

                }

                return status;

            }

     

    Error coming once we are using ExecuteBatch

     

    public bool UpdateSubscriptionFeature(SubscriptionFeature subscriptionFeature)

            {

                bool status = false;

                try

                {

                    string AzureTableName;

                    AzureTableName = "WASubscriptionDtls";//Windows Azure Table Name

                    DataSource objDataSource;

                    objDataSource = new DataSource(AzureTableName);

     

                    WASubscriptionDtls subscriptionFeatureEntity = (from sf in objDataSource.WASubscriptions()

                    where sf.Kind == DataModelConstant.subscriptionFeature   select sf).FirstOrDefault();

     

                    WASubscriptionDtls featureEntity = (from f in objDataSource.WASubscriptions()

                    where f.Kind == DataModelConstant.subscriptionPlan && f.Status == "Y"

                    select f).FirstOrDefault();

     

                    if (subscriptionFeatureEntity != null)

                    {

                        subscriptionFeatureEntity.Name = subscriptionFeature.Name;

                        subscriptionFeatureEntity.Description = subscriptionFeature.Description;

                        subscriptionFeatureEntity.IsOptional = subscriptionFeature.IsOptional;

                        objDataSource.Update(subscriptionFeatureEntity);

                        if (featureEntity != null)

                        {

                            featureEntity.Name = subscriptionFeature.Feature.Name;

                            featureEntity.Code = subscriptionFeature.Feature.Code;                       

                            objDataSource._serviceContext.UpdateObject(featureEntity);                       

                        }

                    }                objDataSource._serviceContext.ExecuteBatch();

                    status = true;

                }            catch (Exception ex)

                {

                }            return status;        }

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.