MkWikiEnSysOp: Added category PostgreSQL

2022-06-05T19:02:42Z

Added category PostgreSQL

← Older revision		Revision as of 19:02, 5 June 2022
Line 64:		Line 64:
	[[Category:Linux]]		[[Category:Linux]]
	[[Category:Programming]]		[[Category:Programming]]
			[[Category:PostgreSQL]]

MkWikiEnSysOp: /* Insert data to table with variables */

2020-02-06T06:59:34Z

Insert data to table with variables

← Older revision		Revision as of 06:59, 6 February 2020
Line 24:		Line 24:
	=== Insert data to table with variables ===		=== Insert data to table with variables ===

	It is best practice to use variables (also called bind variables). '''Do not consider''' concatenating a string and build a query string yourself! This makes your application vulnerable to security problems, namely SQL injection. Ask your favorite search engine about it, ~~that~~ are millions of articles addressing that.		It is best practice to use variables (also called bind variables). '''Do not consider''' concatenating a string and build a query string yourself! This makes your application vulnerable to security problems, namely SQL injection. Ask your favorite search engine about it, there are millions of articles addressing that.

	int ra = -1;		int ra = -1;

MkWikiEnSysOp: 3 revisions imported

2018-06-28T17:19:36Z

3 revisions imported

New page

This article is about developing database applications in C# using PostgreSQL. I developed such an application to transfer the consumption values of my smart meter from a comma seperated values (CSV) file to a PostgreSQL database.

== Packages ==

* pgadmin3 - this is for convenient working with PostgreSQL
* libmono-npgsql4.0-cil (Mono Npgsql library (for CLI 4.0) - installs the bindings needed for developing a C# application that talks to PostgreSQL)

== MonoDevelop ==

In a C# solution, "Npgsql.dll" - usually found in /usr/lib/mono/4.0/Npgsql.dll - must be added.

== C# examples ==

=== Opening connection ===

var connString = @"server=localhost;userid=smartmeter;password=SECRET;database=smartmeter;";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
// Server version can be found in conn.ServerVersion
// Do something with connection
}

=== Insert data to table with variables ===

It is best practice to use variables (also called bind variables). '''Do not consider''' concatenating a string and build a query string yourself! This makes your application vulnerable to security problems, namely SQL injection. Ask your favorite search engine about it, that are millions of articles addressing that.

int ra = -1;
using (var insertCommand = conn.CreateCommand()) {
insertCommand.Connection = conn;
insertCommand.CommandText = "insert into meteringvalues " + "(devicenr, tariff, source, sourcetype, \"timestamp\", value) values " + "(:devicenr, :tariff, :source, :sourcetype, :timestamp, :value)";
insertCommand.Parameters.Add("devicenr", NpgsqlTypes.NpgsqlDbType.Varchar, 20).Value = DeviceNr;
insertCommand.Parameters.Add("tariff", NpgsqlTypes.NpgsqlDbType.Bigint).Value = Tariff;
insertCommand.Parameters.Add("source", NpgsqlTypes.NpgsqlDbType.Bigint).Value = Source;
insertCommand.Parameters.Add("sourcetype", NpgsqlTypes.NpgsqlDbType.Bigint).Value = SourceType;
insertCommand.Parameters.Add("timestamp", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = Timestamp;
insertCommand.Parameters.Add("value", NpgsqlTypes.NpgsqlDbType.Double).Value = value;
insertCommand.Prepare ();
int ra = insertCommand.ExecuteNonQuery ();
}

However, there is one caveat of that solution: If you change your database schema, you also have to change your application's code. It is better to write a stored procedure that finally inserts the data to the destination table. This also enables you to check values, transform them, dispatch them to different tables and so on '''in the database''' (hence don't having the need to change the application which requires deploying it again).

To complete that example, here is the definition of the table:

CREATE TABLE meteringvalues
(
devicenr character(20),
tariff bigint,
source bigint,
sourcetype bigint,
"timestamp" timestamp without time zone,
value double precision
)

== Help ==

These web pages were helpful:

* [https://help.ubuntu.com/community/PostgreSQL PostgreSQL in Ubuntu Help]
* [http://www.sqlines.com/postgresql/npgsql_dotnet PostgreSQL and C#]
* [http://www.npgsql.org/doc/manual-2.2.html npgsql User Manual]

[[Category:Linux]]
[[Category:Programming]]

CSharp PostgreSQL - Revision history

MkWikiEnSysOp: Added category PostgreSQL

MkWikiEnSysOp: /* Insert data to table with variables */

MkWikiEnSysOp: 3 revisions imported