Insert into

Jan 30, 2011 at 9:33 PM
Hi
I am attempting to do an insert at start-up. I am using the following code which compiles with no errors but also no insert.
Any help would be appreciated.
SharpMap v 0.9, C# on Windows.
Thanks in advance.
Bob
try
{
using (SqlCommand cmd = new SqlCommand("Insert into p_id.image (logothe_geom, description, text_)"
+ " select st_centroid (graphics.utilities_dgm.the_geom), ('Logo'), ('PDW')"
+ " From graphics.utilities_dgm"
+ " Where graphics.utilities_dgm.utilities_description = 'Base'")) ;
}
catch
{
Console.WriteLine("Count not insert");
Coordinator
Jan 31, 2011 at 7:43 AM

I cannot see you are assigning an open SqlConnection to your SqlCommand.

Hth FObermaier

Feb 3, 2011 at 11:11 PM

Hi FObermaier

 

    using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
               
                {
                    string insertString = @"Insert into p_id.image (logothe_geom, description,  text_)"
                           + " select st_centroid (graphics.utilities_dgm.the_geom), ('Logo'), ('PDW')"
                           + " From graphics.utilities_dgm"
                           + " Where graphics.utilities_dgm.utilities_description = 'Base'";

                    NpgsqlCommand command = new NpgsqlCommand(insertString, conn);
                    {
                        conn.Open();
                        command.ExecuteNonQuery();

 

 However I am getting a "Could not load file assembley. error which follows -

Perhaps you could confirm regarding the SQLCommand and if I need the Npgsql.dll you might suggest a solution.

Bob

System.IO.FileLoadException was unhandled
  Message="Could not load file or assembly 'Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. 
The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)"
  Source="SharpMap.Extensions"
  FileName="Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
  FusionLog="=== Pre-bind state information ===\r\nLOG: User = Bob-PC\\Bob\r\nLOG: DisplayName = Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7\n
 (Fully-specified)\r\nLOG: Appbase = file:///C:/Users/Bob/Documents/Visual Studio 2008/Projects/Data Graphics/bin/x86/Release/\r\nLOG: Initial PrivatePath = NULL\r\nCalling assembly : 
SharpMap.Extensions, Version=0.9.3986.16898, Culture=neutral, PublicKeyToken=null.\r\n===\r\nLOG: This bind starts in default load context.\r\nLOG: Using application configuration file: 
C:\\Users\\Bob\\Documents\\Visual Studio 2008\\Projects\\Data Graphics\\bin\\x86\\Release\\Data Graphics.vshost.exe.config\r\nLOG: Using machine configuration file from C:\\Windows\\Microsoft.NET\\Framework\\v2.0.50727\\config\\machine.config.\r\nLOG: Post-policy reference: Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7\r\nLOG: Attempting download of new URL file:///C:/Users/Bob/Documents/Visual Studio 2008/Projects/Data Graphics/bin/x86/Release/Npgsql.DLL.\r\nWRN: Comparing the assembly name resulted in the mismatch: Build Number\r\nERR: Failed to complete setup of assembly (hr = 0x80131040). Probing terminated.\r\n"
  StackTrace:
       at SharpMap.Data.Providers.PostGIS.ExecuteIntersectionQuery(BoundingBox bbox, FeatureDataSet ds)
       at SharpMap.Layers.LabelLayer.Render(Graphics g, Map map) in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Layers\LabelLayer.cs:line 387
       at SharpMap.Map.RenderMap(Graphics g) in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Map\Map.cs:line 211
       at SharpMap.Map.GetMap() in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Map\Map.cs:line 163
       at Demo.frmGraphics.RefreshMap() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Graphics.cs:line 224
       at Demo.frmGraphics..ctor() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Graphics.cs:line 173
       at Demo.Program.Main() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

I found out that the SQLCommand isn’t used for inserting into a remote PostgreSQL database. 


So I attempted to use the Npgsql.dll. Here’s the code I am using without the exception handling-
Coordinator
Feb 4, 2011 at 6:59 AM

If you want to access Postgres database, you cannot use SqlCommand and SqlConnection.

You can consider using OleDb, Npgsql or Devarts Postgres provider. I'd stick with the one you use from within SharpMap.

AFAIK, if you use Npgsql, you also must provide access -maybe even refernece- to Mono.Security. Also there are some policy assemblies related to Npgsql that you might need to provide.

You can find all the Npgsql related files in the current ExternalReferences\ReferencesForSharpMapExtensions folder.

Hth FObermaier

Feb 4, 2011 at 7:06 AM
Hi FObermaier
I answered via the discussion board but the message got jumbled when I either inserted or posted.
I found out that SQLCommand isn’t used for inserting into a remote Postgresql database. Is this right?
So I attempted to use the Npgsql.dll. Here’s the code I have developed without the exception handling which I want to do as a second step -
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
string insertString = @"Insert into p_id.image (logothe_geom, description, text_)"
+ " select st_centroid (graphics.utilities_dgm.the_geom), ('Logo'), ('PDW')"
+ " From graphics.utilities_dgm"
+ " Where graphics.utilities_dgm.utilities_description = 'Base'";
NpgsqlCommand command = new NpgsqlCommand(insertString, conn);
{
conn.Open();
command.ExecuteNonQuery();
However this gives me a “Could not load file assembly “error.
Any help will be appreciated.
Bob
Npgsql error message -
System.IO.FileLoadException was unhandled
Message="Could not load file or assembly 'Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)"
Source="SharpMap.Extensions"
FileName="Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
FusionLog="=== Pre-bind state information ===\r\nLOG: User = Bob-PC\\Bob\r\nLOG: DisplayName = Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7\n (Fully-specified)\r\nLOG: Appbase = file:///C:/Users/Bob/Documents/Visual Studio 2008/Projects/Data Graphics/bin/x86/Release/\r\nLOG: Initial PrivatePath = NULL\r\nCalling assembly : SharpMap.Extensions, Version=0.9.3986.16898, Culture=neutral, PublicKeyToken=null.\r\n===\r\nLOG: This bind starts in default load context.\r\nLOG: Using application configuration file: C:\\Users\\Bob\\Documents\\Visual Studio 2008\\Projects\\Data Graphics\\bin\\x86\\Release\\Data Graphics.vshost.exe.config\r\nLOG: Using machine configuration file from C:\\Windows\\Microsoft.NET\\Framework\\v2.0.50727\\config\\machine.config.\r\nLOG: Post-policy reference: Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7\r\nLOG: Attempting download of new URL file:///C:/Users/Bob/Documents/Visual Studio 2008/Projects/Data Graphics/bin/x86/Release/Npgsql.DLL.\r\nWRN: Comparing the assembly name resulted in the mismatch: Build Number\r\nERR: Failed to complete setup of assembly (hr = 0x80131040). Probing terminated.\r\n"
StackTrace:
at SharpMap.Data.Providers.PostGIS.ExecuteIntersectionQuery(BoundingBox bbox, FeatureDataSet ds)
at SharpMap.Layers.LabelLayer.Render(Graphics g, Map map) in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Layers\LabelLayer.cs:line 387
at SharpMap.Map.RenderMap(Graphics g) in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Map\Map.cs:line 211
at SharpMap.Map.GetMap() in C:\Users\Bob\Documents\Lean Dog\SharpMap New\SharpMap\Map\Map.cs:line 163
at Demo.frmGraphics.RefreshMap() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Graphics.cs:line 224
at Demo.frmGraphics..ctor() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Graphics.cs:line 173
at Demo.Program.Main() in C:\Users\Bob\Documents\Visual Studio 2008\Projects\Data Graphics\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at System.Activator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
From: [email removed]
Sent: Monday, January 31, 2011 12:43 AM
To: [email removed]
Subject: Re: Insert into [SharpMap:243914]

From: FObermaier

I cannot see you are assigning an open SqlConnection to your SqlCommand.

Hth FObermaier

Feb 4, 2011 at 7:13 AM
Our messages crossed.
Is the Devarts Postgres provider the same as PostGIS provider.
I played around with using the PostGIS provider as I am using it for the layers, but it didn’t seem to have a method of executing an insert.
Perhaps you could point me to an example?
Bob
From: [email removed]
Sent: Thursday, February 03, 2011 11:59 PM
To: [email removed]
Subject: Re: Insert into [SharpMap:243914]

From: FObermaier

If you want to access Postgres database, you cannot use SqlCommand and SqlConnection.

You can consider using OleDb, Npgsql or Devarts Postgres provider. I'd stick with the one you use from within SharpMap.

AFAIK, if you use Npgsql, you also must provide access -maybe even refernece- to Mono.Security. Also there are some policy assemblies related to Npgsql that you might need to provide.

You can find all the Npgsql related files in the current ExternalReferences\ReferencesForSharpMapExtensions folder.

Hth FObermaier

Coordinator
Feb 4, 2011 at 7:33 AM

Hello Bob,

the postgis provider is sharpmap specific. It internally uses the Npgsql library to connect to the postgres/postgis database.

You could change the sharpmap provider to use devart postgres connectivity instead of npgsql.

You say that you can display vector layers with sharpmap using its postgis provider, but cannot execute the above code? If that is true, maybe you are referencing a different Npgsql library than sharpmap does. Try setting both to the same (latest) version.

Hth FObermaier

Feb 4, 2011 at 10:58 PM
Here is the closest I have been by using the PostGIS connection that I have already employed.
The PostGIS (from Metadta doesn’t mention anything like a Command so I am attempting to use PGCommand.
But this gives me an “invalid arguments'” error.
PostGIS conn = new PostGIS(connectionString, "p_id.image", "public");
string insertString = ("Insert into p_id.image (logothe_geom, description, text_)"
+ " select st_centroid (graphics.utilities_dgm.the_geom), ('Logo'), ('PDW')"
+ " From graphics.utilities_dgm"
+ " Where graphics.utilities_dgm.utilities_description = 'Base'");
PgCommand cmd = new PgCommand(insertString, conn);
conn.Open();
cmd.Prepare();
cmd.ExecuteNonQuery();
}
Bob
From: [email removed]
Sent: Friday, February 04, 2011 12:33 AM
To: [email removed]
Subject: Re: Insert into [SharpMap:243914]

From: FObermaier

Hello Bob,

the postgis provider is sharpmap specific. It internally uses the Npgsql library to connect to the postgres/postgis database.

You could change the sharpmap provider to use devart postgres connectivity instead of npgsql.

You say that you can display vector layers with sharpmap using its postgis provider, but cannot execute the above code? If that is true, maybe you are referencing a different Npgsql library than sharpmap does. Try setting both to the same (latest) version.

Hth FObermaier

Coordinator
Feb 7, 2011 at 7:29 AM

The PgConnection/PgCommand is from another library that provides access to PostgreSQL databases.

Could you please query version of Npgsql that you use in your application and the one that is referenced from sharpmap.

If there is a difference, than use the newer version for both.

Hth FObermaier