1. Tim Robertson
  2. .NET DataStore
  3. Wednesday, 11 August 2021 21:44 PM UTC

I am trying to move a PowerBuilder process that is almost all Computational Business Logic.  I am starting from a Command Line Tool C# project rather than a WebServer (RestAPI) and have harvested over the Business Logic and Data Models, pretty well.  In all of the Tutorials that I can find, the starting point is an ASP.NET project and it's seems to me that the Database Connection to SQLServer is being done by "magic" middleware of the ASP.NET project.  I did find some references in the SnapObjects Documentation for getting a connection SqlServerDataContext.SqlServerDataContext() but I'm stuck using the example finding how to have  SqlServerConnection visible in the code

... new SqlServerConnection(CONNECTION_STRING) 

I am new to PowerBuilder and .NET, so trying to start with just getting a Database Connection to the
SQLServer Database (which I am able to connect to in order to convert the PowerBuilder to C# on my Dev box.

Any References to Full Examples or a Tutorial using DataStore that is NOT using ASP.NET would really help.

Accepted Answer
Daryl Foster Accepted Answer Pending Moderation
  1. Thursday, 12 August 2021 00:51 AM UTC
  2. .NET DataStore
  3. # Permalink

Hi Tim,

Here is an example console application I did last year to submit a bug report to Appeon. I can't remember what version of snap develop I used, but I know at the time it worked. It uses SqlExecutor and SqlModelMapper, but the database concepts are still the same.  It uses two files DefaultDataContext.cs and Program.cs, but DefaultDataContext could have been replaced with SqlServerDataContext. I've just used a string to hardcode in the database connection string which connects to the AdventureWorks2012 database, but of course you could read that from a config file.

 

DefaultDataContext.cs

using System;
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;

namespace ConsoleApp1
{
    public class DefaultDataContext : SqlServerDataContext
    {
        public DefaultDataContext(string connectionString)
            : this(new SqlServerDataContextOptions<DefaultDataContext>(connectionString))
        {
        }

        public DefaultDataContext(IDataContextOptions<DefaultDataContext> options)
            : base(options)
        {
        }
        
        public DefaultDataContext(IDataContextOptions options)
            : base(options)
        {
        }
    }
}

 

Program.cs

using System;
using System.Linq;
using ConsoleApp1.Models;
using SnapObjects.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connection = "Data Source = localhost,1433; Initial Catalog = AdventureWorks2012; Integrated Security = True; Pooling = True; Min Pool Size = 0; Max Pool Size = 100; MultipleActiveResultSets = False; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite";
            var dataContext = new DefaultDataContext(connection);

            ISqlQueryBuilder queryBuilder =
                ModelSqlBuilder.GetBuilder<location>(dataContext).QueryBuilder;

            // Show the original query
            string sql = queryBuilder.ToSqlString(dataContext);
            Console.WriteLine("Original Sql\r\n{0}'\r\n", sql);

            // Run the original query
            var executorResults = dataContext.SqlExecutor.Select<location>(queryBuilder).ToList();          
            var mapperResults = dataContext.SqlModelMapper.Load<location>(queryBuilder).ToList();
            Console.WriteLine("SqlExecutor Records Returned = {0}", executorResults.Count());
            Console.WriteLine("SqlModelMapper Records Returned = {0}\r\n", mapperResults.Count());

            // Modify the query
            queryBuilder.Where("location.locationid", SqlBinaryOperator.Equals, "7");

            // Show the modified query
            sql = queryBuilder.ToSqlString(dataContext);
            Console.WriteLine("Modified Sql\r\n{0}\r\n", sql);
            
            // Run the modified query
            executorResults = dataContext.SqlExecutor.Select<location>(queryBuilder).ToList();
            mapperResults = dataContext.SqlModelMapper.Load<location>(queryBuilder).ToList();
            
            Console.WriteLine("SqlExecutor Records Returned = {0}", executorResults.Count());
            Console.WriteLine("SqlModelMapper Records Returned = {0}", mapperResults.Count());

            Console.ReadKey();
        }
    }
}

 

In an ASP.Net project the database data context is automagically made available through dependency injection. I think you can also implement dependency injection in a console application, but the easiest way is to create your database context in the main program and pass it into the objects you create via their constructor.  Here is some code snippets which show what I mean.

 

// A class that uses a datacontext
public class ProcessingService
{
    private readonly DefaultDataContext _dataContext;
        
    public ProcessingService(DefaultDataContext dataContext)
    {
        _dataContext = dataContext;
    }

    public int DoSomething()
    {
        // Some code that does something with _dataContext
    }

    ...... Other methods which use the _dataContect to get data from the database
}


// The main program which creates the datacontext
static void Main(string[] args)
{

    string connection;

     // get the connection string here (from config file)

    var dataContext = new DefaultDataContext(connection);


    ProcessingService process = new ProcessingService(dataContext);

    var result = process.DoSomething();

    // do something with the result
}

 

 

 

 

 

Comment
  1. Tim Robertson
  2. Friday, 13 August 2021 20:30 PM UTC
I think this matches exactly what I was hoping to find. Thanks!
  1. Helpful
  1. Daryl Foster
  2. Friday, 13 August 2021 21:11 PM UTC
No worries. Glad I could help. Good luck with your project.
  1. Helpful
There are no comments made yet.
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 11 August 2021 21:57 PM UTC
  2. .NET DataStore
  3. # 1

Hi Tim,

In order to create a .NET DataStore all you need is a Database Context, for which in turn you need a connection string. This connection string can be read from a file or input manually.

In our examples, the connection string is read from the appsettings.json file through the Configuration helper class instance. But if you are using .NET DataStore outside of a ASP.NET context, you can do a similar process (inputting the connection string manually or loading it from a file)

For example:

using (DefaultDataContext context = new DefaultDataContext("ConnectionString"))
{
         var dataWindow = new DataStore<Dlist_Admin>(context);
               
         dataWindow.Retrieve();
}

 

I know this is not a complete and comprehensive example, but still hope this helps you.

Regards,
Francisco

 

Comment
  1. Tim Robertson
  2. Thursday, 12 August 2021 00:41 AM UTC
Francisco,



Thanks, I'll give this a Try. The "DefaultDataContext" in this case is the DataContext that I generated in my project,, I assume and the "using" in this case is the bounding of the connection (so it gets cleaned up after I'm done -- again, if I understand this correctly. I think it really gets me started.
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 11 August 2021 21:57 PM UTC
  2. .NET DataStore
  3. # 2

The .NET DataStore uses the DataContext of SnapObjects to make database connection.  This is requirement to use .NET DataStore or migrate any PB business logic to C#.

As far as doing a non-visual class instead of REST API, I suggest studying the Razor Pages example.  I guess it's not exactly what you are trying to do, but it doesn't use REST API and is only example I can think of to provide you.

 

Comment
  1. Tim Robertson
  2. Thursday, 12 August 2021 00:43 AM UTC
Armeen,

So, Actually, there is no ASP.NET at all needed by my Application, it's a Background Executables (Command Line), but I'll read through the Razor Pages example to see if it gives me additional insight. Thank you!
  1. Helpful
  1. Armeen Mazda @Appeon
  2. Thursday, 12 August 2021 01:13 AM UTC
Yes, I understood you want to do non-visual project. I just didn’t have exact example matching. Hopefully this example and other feedback from Appeon community get you in right direction.
  1. Helpful
  1. Tim Robertson
  2. Friday, 13 August 2021 20:28 PM UTC
Yes. I appreciate the help and will include digging into the Razor Pages example as well! Thanks,
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.