Parameters To AI Scripted Objects With LINQ – Getting Data From The Database

But First A Word About Parameters

The growth rates and reproduction rates of the bugs and plants will have to be adjusted so that an ecological balance is attained, and of course so that the total number of primitives doesn’t exceed what the parcel can handle. The idea is to observe how the ecological community functions over time, and tweak a series of parameters in the different AI species so that the balance is maintained. At this point, we don’t want to have to define each and every parameter, but it is clear that at the least there will be one for growth rate, and probably one for average life length. We will start with those, and indicate how more can be added as they are identified.

The Database Schema

This is bound to evolve, but we can start with one table for the plant species parameters and define it as follows:

Table name: PlantParameters

Fields:

pk smallint identity = true
growthRate float
avgLifeSpan float

Mapping The Data In LINQ

Each table to be accessed will be mapped to a class. Attributes will be added to the class name as well as to the fields in the class to effect the mapping. The corresponding class is:

[Table (Name="dbo.PlantParameters")]
public class PlantParameters
{
[Column (Name="pk", IsPrimaryKey=true)]
public int pk {get, private set; }

[Column (Name="growthRate")]
public double growthRate { get; set; }

[Column (Name="avgLifeSpan")]
public double avgLifeSpan { get; set; }
}

The attribute in square brackets above the class declaration defines this class as a class which is to be mapped to the table PlantParameters. It is not required that the class have the same name as the table, but it is a good means of keeping track of things. Similarly, it is not necessary to name the fields in the class the same as the table fields, but it also helps track the data as it flows from the database to the AI scripted objects. Note that in LINQ the get and set have implied private variables, so it is not necessary to declare and name them. Also note that in the case of the primary key, you see private set. The primary key is an identity type which the database will automatically increment as rows are added, so we do not want the program changing the value. This class is now set up to receive data.

You may have noticed that in SQL Server, the two parameters are declared to be of type float, while the class fields to which they will map are declared as doubles. If you declare the class fields to be floats, a run time exception will be thrown. Welcome to Microsoft!

Connecting To The Database

We will use a .NET Framework class called DataContext to make the connection. An instance of it will be created, with the connection string given as an argument to the constructor. It is the same connection string format you probably used with the SqlConnection class in ADODB. The one shown below is just the one we use in our own database.

string connectionString=@”Data Source=root-PC\rootsqlserver;” + “Initial Catalog=SLAIdata;Integrated Security=True”;
DataContext dc = new DataContext(connectionString);
Table<PlantParameters> parameters
= dc.GetTable<PlantParameters>();

In the connection string, the first parameter is the server name/SQL Server instance where you store your data. The Initial Catalog parameter is the name of the database which contains the table. Of course, use your own parameter values. The second line of code initiates the connection and accesses all the table rows, putting them into the typed collection parameters. The contents of the table are now in memory to be accessed as required.

Preparing The Data For XML-RPC Transmission

An XML-RPC transmission to a scripted object provides for three arguments: (1) the channel for the transmission, (2) a string argument, and (3) an integer argument. The plan is to concatenate all parameter values together as a comma separated string, allowing the script on the receiving end to sort it out. This works well, since LSL has provisitions for converting a comma separated string into an LSL list type, and there are several LSL commands for list manipulations. For our purposes now, we need LINQ to access the data acquired from the database and format it into a comma separated string. We will use a LINQ query expression.

We would like to have a history of parameter sets supplied to the AI scripted objects. We could add a time stamp indicating when the latest set of parameters was transmitted. In an effort to keep it simple, the latest parameters will simply be added to the database, with the latest set having the highest value for the primary key. Consequently, we want to extract that set when transmitting parameters. Following the code above which gets the table contents into the Table<PlantParameters> type, we add the following four lines of code.

var query = from p in parameters orderby p.pk ascending select p;
var parameterList = query.ToList();
var lastParameter = parameterList.Last();
string parameterString = lastParameter.growthRate + “, ” + lastParameter.avgLifeSpan + “, ” + lastParameter.pk;

Notice how the first of these lines looks sort of like SQL but seems to be out of order. This is a LINQ query expression. The first two lines work together to extract all rows of the table parameters and insert them into the List object parameterList. The LINQ key work var is all the compiler needs to be told to deduce the data type for the variable query in the first line of code as well as the variable parameterList in the second code line, and lastParameter in the third line.

LINQ uses delayed queries, so that calling query.ToList() forces the query to be performed and the result placed in a list. Calling Last() on the list object gets the last entry. Note that the results of the query are sorted in ascending order by the primary key. As in SQL, an ascending sort is the default, although placing ascending in the query expression is simply a reminder that it is indeed an ascending sort.

The LINQ code shown may not necessarily be the most efficient, but it is expected that the parameter table will not be extensive over the course of the project. The time to access the entire table then get the latest entry after the entire table is returned is small compared to the time it will take to pass the parameters via XML-RPC.

So, What’s Next?

The extensions to the approach above should be self-evident. Additional parameters to be passed would result in more fields in the table, and more fields declared in the corresponding mapped class. Different parameter sets call for more similarly constructed tables. In all cases, the result will be a comma separated string of values. In the next installment we will see how that string gets shipped to a scripted object in LSL.