Best way to store methods in a database?
-
Let's assume we have methods that calculates something:
public int calculateSomething1(int param) { int calculatedValue; ...Calculations... return calculatedValue; } public int calculateSomething2(int param) { int calculatedValue; ...Calculations... return calculatedValue; }What would be the best way to store these in a database (we can assume SQL Server)? Would it be to put the methods in separate dll:s and then insert all the bytes inside the dll into the database and then load them as dynamic dll:s in RAM? Or something else? Inserting the raw source code as varchar and then compile it in runtime is too slow.
-
Let's assume we have methods that calculates something:
public int calculateSomething1(int param) { int calculatedValue; ...Calculations... return calculatedValue; } public int calculateSomething2(int param) { int calculatedValue; ...Calculations... return calculatedValue; }What would be the best way to store these in a database (we can assume SQL Server)? Would it be to put the methods in separate dll:s and then insert all the bytes inside the dll into the database and then load them as dynamic dll:s in RAM? Or something else? Inserting the raw source code as varchar and then compile it in runtime is too slow.
-
Let's assume we have methods that calculates something:
public int calculateSomething1(int param) { int calculatedValue; ...Calculations... return calculatedValue; } public int calculateSomething2(int param) { int calculatedValue; ...Calculations... return calculatedValue; }What would be the best way to store these in a database (we can assume SQL Server)? Would it be to put the methods in separate dll:s and then insert all the bytes inside the dll into the database and then load them as dynamic dll:s in RAM? Or something else? Inserting the raw source code as varchar and then compile it in runtime is too slow.
arnold_w wrote:
Inserting the raw source code as varchar and then compile it in runtime is too slow.
Compile it on first execution (or save), save the resulting assembly in a database-blob. Could easily be timestamped, to see if the source has been altered. Do consider that sometimes compilations fail due to errors in code.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
It's for register maps and register value loop-up for different products and it would be nice if only the database (not the .exe-file) needs to be updated as we add new products and registers. For example, let's assume a register called MONTH, this register would need this table to convert from an integer to a user-friendly string: 1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December Now, let's a assume a different register called YEAR_MONTH_AND_DATE, all stored within the same integer, image how big that lookup table would be. Instead, it would be much easier to just a have method to do the conversion:
public string convertYearMonthAndDate(UInt32 yearMonthAndDate)
{
UInt16 year = (UInt16)(yearMonthAndDate >> 16);
byte month = (byte) (yearMonthAndDate >> 8);
byte date = (byte) (yearMonthAndDate >> 0);
string monthString = "";
switch (month)
{
case 1: monthString = "January"; break;
case 2: monthString = "February"; break;
case 3: monthString = "March"; break;
case 4: monthString = "April"; break;
case 5: monthString = "May"; break;
case 6: monthString = "June"; break;
case 7: monthString = "July"; break;
case 8: monthString = "August"; break;
case 9: monthString = "September"; break;
case 10: monthString = "October"; break;
case 11: monthString = "November"; break;
case 12: monthString = "December"; break;
}
return monthString + " " + date + ", " + year;
} -
It's for register maps and register value loop-up for different products and it would be nice if only the database (not the .exe-file) needs to be updated as we add new products and registers. For example, let's assume a register called MONTH, this register would need this table to convert from an integer to a user-friendly string: 1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December Now, let's a assume a different register called YEAR_MONTH_AND_DATE, all stored within the same integer, image how big that lookup table would be. Instead, it would be much easier to just a have method to do the conversion:
public string convertYearMonthAndDate(UInt32 yearMonthAndDate)
{
UInt16 year = (UInt16)(yearMonthAndDate >> 16);
byte month = (byte) (yearMonthAndDate >> 8);
byte date = (byte) (yearMonthAndDate >> 0);
string monthString = "";
switch (month)
{
case 1: monthString = "January"; break;
case 2: monthString = "February"; break;
case 3: monthString = "March"; break;
case 4: monthString = "April"; break;
case 5: monthString = "May"; break;
case 6: monthString = "June"; break;
case 7: monthString = "July"; break;
case 8: monthString = "August"; break;
case 9: monthString = "September"; break;
case 10: monthString = "October"; break;
case 11: monthString = "November"; break;
case 12: monthString = "December"; break;
}
return monthString + " " + date + ", " + year;
} -
Both of those methods look broadly similar but redundant. If you store dates as proper date types in the database then you can use the standard .NET classes to get them printed in clear text.
It was just an example and I choose a simple example to make it easy to understand. If you really want to look at what the registers look like in real life then have a look in this reference manual: https://www.st.com/resource/en/reference_manual/dm00031020-stm32f405-415-stm32f407-417-stm32f427-437-and-stm32f429-439-advanced-arm-based-32-bit-mcus-stmicroelectronics.pdf[^]
-
About 40 years ago, when I was still a student, we had this database system where you could define triggers in the database for doing all sorts of integrity checks etc. on the database, independently of the application. This was a realational database, but using an extended variant of Pascal to address the tables: What would be WHERE criteria in SQL was indexing expressions in the database tables, etc. You didn't adrress the database as an external entity, but rather declared you application as an entity in the database: The databse tables were defined in a layer conceptually "outside" the program entity, encompassing it. Tables were defined using the same syntax as Pascal arrays, but being "outside" the program, they had a lifetime scope beyond the execution of the program; they looked just like other arrays, but were persistent, and had extended indexing facilities and other table operations. This compiler was based on the classical P4 Pascal compiler. So when you compiled a database routine, the P4 code was stored in the database, available to any program using this database as an outer shell. The routines were instruction set and OS independent; the database (with its embedded routines) could be accessed from any OS or machine architecture that offered a P4 interpreter. I believe that this project started around 1975-76, so it was quite early for this kind of architecture.
-
About 40 years ago, when I was still a student, we had this database system where you could define triggers in the database for doing all sorts of integrity checks etc. on the database, independently of the application. This was a realational database, but using an extended variant of Pascal to address the tables: What would be WHERE criteria in SQL was indexing expressions in the database tables, etc. You didn't adrress the database as an external entity, but rather declared you application as an entity in the database: The databse tables were defined in a layer conceptually "outside" the program entity, encompassing it. Tables were defined using the same syntax as Pascal arrays, but being "outside" the program, they had a lifetime scope beyond the execution of the program; they looked just like other arrays, but were persistent, and had extended indexing facilities and other table operations. This compiler was based on the classical P4 Pascal compiler. So when you compiled a database routine, the P4 code was stored in the database, available to any program using this database as an outer shell. The routines were instruction set and OS independent; the database (with its embedded routines) could be accessed from any OS or machine architecture that offered a P4 interpreter. I believe that this project started around 1975-76, so it was quite early for this kind of architecture.
-
Let's assume we have methods that calculates something:
public int calculateSomething1(int param) { int calculatedValue; ...Calculations... return calculatedValue; } public int calculateSomething2(int param) { int calculatedValue; ...Calculations... return calculatedValue; }What would be the best way to store these in a database (we can assume SQL Server)? Would it be to put the methods in separate dll:s and then insert all the bytes inside the dll into the database and then load them as dynamic dll:s in RAM? Or something else? Inserting the raw source code as varchar and then compile it in runtime is too slow.
Maybe as SQLCLR functions? SQL Server Common Language Runtime Integration - ADO.NET | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
"These people looked deep within my soul and assigned me a number based on the order in which I joined" - Homer
-
Maybe as SQLCLR functions? SQL Server Common Language Runtime Integration - ADO.NET | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
That's what I was going to suggest, but that kinda hides functionality, and if you're going to create those assemblies, why not just put them in your app? We use them where I work, but they were implemented years ago, and I'm not even sure where the source code is.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
That's what I was going to suggest, but that kinda hides functionality, and if you're going to create those assemblies, why not just put them in your app? We use them where I work, but they were implemented years ago, and I'm not even sure where the source code is.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013#realJSOP wrote:
if you're going to create those assemblies, why not just put them in your app?
Because there may be many apps. You may want some functions to carry out e.g. consistency checking (beyond what you can set up in the SQL schema) regardless of which application is used. Think of such code as belonging to the schema, not to the data.
