Wednesday, March 8, 2017

Create database and content using psql





This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html


Sunday, February 26, 2017

Start/Stop/Get details for Windows Services

If you are busy with multiple assignments and cater needs from multiple projects, or may be own a laptop with very little resources, you may want to manipulate windows services active on your laptop by Windows PowerShell.
For example, you may keep handy a .csv targeting one assignment, when you start working on that assignment simply stop other bulky windows services and activate the one you are interested in.

Some helpful commands:


#Get-Service | select-object  status  -unique
#Get-Service | where {$_.status -eq "Running"} 

# get all windows services running:
Get-Service | select Name, CanPauseAndContinue, CanShutdown, CanStop, DisplayName, MachineName, ServiceName, ServiceHandle, Status, ServiceType, StartType, Site, Container, @{Name=’RequiredServices’;Expression={[string]::join(“;”, ($_.RequiredServices.Name))}} ,  @{Name=’DependentServices’;Expression={[string]::join(“;”, ($_.DependentServices.Name))}} ,    @{Name=’ServicesDependedOn’;Expression={[string]::join(“;”, ($_.ServicesDependedOn.Name))}} | Export-Csv c:\gm\servs.csv;

#After analysis of output say, you want to start 3 of them and stop 2, but leave one untouched still be in the csv, create a csv something like:
#(consider the dependencies listed in output above to decide order)
#"Name","Start","Stop"
#"MSSQL$HK2012SQL","TRUE","FALSE"
#"SQLAgent$HK2012SQL","TRUE","FALSE"
#"postgresql-x64-9.5","TRUE","FALSE"
#"wuauserv","FALSE","TRUE"
#"wlidsvc","FALSE","TRUE"
#"RpcSs","FALSE","FALSE"

import-csv "C:\gm\input.csv" | foreach-object { write-host $_.Name; if([bool]::Parse($_.Start)){ Start-Service $_.Name}; if([bool]::Parse($_.Stop)){ Stop-Service $_.Name};}

sample commands at :  bitbucket.org/hemantup/orm/src/HEAD/Scripts/services.txt

But if you are more into security and want control over what is happening over your laptop above mentioned might not be sufficient.
You might need to to deep dive into processes like :

Get-Process | Export-csv "c:\gm\pr.csv"

Friday, February 17, 2017

LLBLGenPro Lite with PostgreSQL and .Net

In this example I used:

LLBLGenPro Lite Version 5.1 (5.1.2) RTM - LITE  Build Date 24-Jan-2017, Licensee: LITE user
Npgsql -Version 3.1.10
CsvHelper -Version 2.16.3
log4net -Version 2.0.7
Visual Studio 2012 with Package Manager Console Host Version 2.8.60318.667

Few tips:

1. Generic part of adapter source code and DB Specific part ( Projects LLBLGenProLite51DBSpecific & LLBLGenProLite51Generic) were created following "LLBLGen Pro Runtime Framework v5.1 Documentation" documentation at llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/index.htm
bitbucket.org/hemantup/orm/src/upgrade3/LLBLGenProLite51/LLBLGenProLite51.llblgenproj is the saved project of LLBLGenPro.exe which may be used to regenerate the classes later, if need be.
llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/Tutorials%20And%20Examples/tutorial_createproject.htm

2. The only tweak I had to do to generate the source code (on the top the instructions as mentioned in online help guide "LLBLGen Pro Runtime Framework v5.1 Documentation):
a) Put Npgsql.dll in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v5.1\
b) Some of you might also have to put DbProviderFactories entry under LLBLGenPro_x86.exe.config.
Otherwise you may get error:
Exception details:
=====================
Message: Failed to find or load the registered .Net Framework Data Provider.
Source: System.Data
Stack trace: 
   at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
   at SD.LLBLGen.Pro.DBDriverCore.DBDriverBase.GetDbProviderFactory()
   at SD.LLBLGen.Pro.DBDriverCore.ConnectionDataBase.get_FactoryName()
   at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.<LoadDriverAndConnectionControl>b__16_1()
   at SD.LLBLGen.Pro.Gui.Classes.ApplicationIdleDispatcher.PerformWork()
   at SD.LLBLGen.Pro.Gui.Classes.ApplicationIdleDispatcherSingleton.Application_Idle(Object sender, EventArgs e)
   at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FDoIdle(Int32 grfidlef)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at SD.LLBLGen.Pro.Gui.Classes.GuiController.PerformAddMetaDataFromDatabaseAction()
   at SD.LLBLGen.Pro.Gui.Controls.ProjectExplorer._mainBarManager_ItemClick(Object sender, ItemClickEventArgs e)
   at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
   at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)
   at DevExpress.XtraBars.BarItemLink.OnLinkClick()
   at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
   at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
   at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at DevExpress.XtraBars.Controls.CustomControl.WndProc(Message& msg)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Inner exception: <null>

3. In the Console project I created, I had to make DbProviderFactories entry in app.config in addition to installing Npgsql.

For other details, you may refer to comments in bitbucket.org/hemantup/orm/src/upgrade3/LLBLGenProLite51/LLBLGenProLite51Console/Program.cs in sample code.



Working sample code is available at bitbucket.org/hemantup/orm/src/upgrade3/LLBLGenProLite51/






This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html



Thursday, February 16, 2017

Symbiotic_Data_Provider_PostgreSql_x64 with PostgreSQL and .Net

In this example I used:
Symbiotic_x64 -Version 4.0.4.1
Symbiotic_Data_Provider_PostgreSql_x64 -Version 2.0.0
Npgsql -Version 3.1.10
CsvHelper -Version 2.16.3
log4net -Version 2.0.7
test_table.cs using Symbiotic Code Generator

Working sample code is available at bitbucket.org/hemantup/orm/src/upgrade3/SymbioticDataProviderPostgreSql2/





Wednesday, February 15, 2017

Uni.ORM with PostgreSQL and .Net

Uni.ORM with PostgreSQL and .Net

In this example I used:

Npgsql -Version 3.1.10

CsvHelper -Version 2.16.3

log4net -Version 2.0.7

Uni.Extensions -Version 1.1.8 (  if you use Uni.Extensions 1.1.9, along with Uni.ORM 1.4.4, it will give error Field not found: 'Uni.Extensions.UniExtensions.stringType'.)

Uni.ORM -Version 1.4.4

Working sample code is available at

bitbucket.org/hemantup/orm/src/upgrade3/UniORM144/
bitbucket.org/hemantup/orm/src/upgrade3/Scripts/apps/10/



Probably you need to be careful about the active sessions with this ORM:







I got below mentioned error, if used more than 20 execution cycles with above said custom utility


Npgsql.NpgsqlException was unhandled
  HResult=-2147467259
  Message=The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)
  Source=Npgsql
  ErrorCode=-2147467259
  StackTrace:
       at Npgsql.ConnectorPool.WaitForTask(Task task, NpgsqlTimeout timeout)
       at Npgsql.ConnectorPool.Allocate(NpgsqlConnection conn, NpgsqlTimeout timeout)
       at Npgsql.NpgsqlConnection.OpenInternal()
       at Npgsql.NpgsqlConnection.Open()
       at Uni.Orm.UniOrm.NewConnection()
       at Uni.Orm.UniOrm.ExecuteScalar(CommandType commandType, String schema, String package, String commandText, Options options, Object[] args)
       at Uni.Orm.UniOrm.ExecuteScalar[T](CommandType commandType, String schema, String package, String commandText, Options options, Object[] args)
       at Uni.Orm.UniOrm.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
       at CallSite.Target(Closure , CallSite , Object , String , String , String , Options , Object[] )
       at Uni.Orm.UniOrm.Count(String schema, String table, String where, Options options, Object[] args)
       at UniORM144.UniORM144Test.Main(String[] args) in c:\891\orm\orm\UniORM144\Program.cs:line 114
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:






This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Tuesday, February 14, 2017

i-nercya EntityLite with PostgreSQL and .Net

In this example I used:
Npgsql -Version 3.1.10
EntityLite -Version 1.12.1
DataLayer.cs  from hemantrohtak.blogspot.com/2017/02/generate-datalayer-for-i-nercya.html
CsvHelper -Version 2.16.3
log4net -Version 2.0.7

Working sample code is available at bitbucket.org/hemantup/orm/src/upgrade3/InercyaEntityLite1121/





This is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Generate datalayer for i-nercya EntityLite 1.12.1 with PostgreSQL and .Net

I tried to install EntityLite 1.12.1 and Npgsql 3.1.9 for this. [ in .Net 4.5 targeted console app] But ended up with error:
Error 2 Running transformation: System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.
   at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.get_Factory()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.OpenConnection()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.SetViewsByEntity()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.Initialize()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.Render(DataLayerGeneration generation)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.TransformText() C:\891\i_nercya_EntityLite_1121_generate\DataLayer.tt 1 1 i_nercya_EntityLite_1121_generate

In case of other ORM's similar error was resolved by DbProviderFactories entry in app.config of the same project. But in case of  EntityLite 1.12.1, I had to put   Npgsql 3.1.9 dll in gac and make DbProviderFactories entry in devenv.exe.config.

But still my datalayer was not generated. Now I got the error:

Error 2 Running transformation: System.InvalidCastException: Specified cast is not valid.
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchema(String tableOrView, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchemaUnion(EntitySetting entitySetting, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.SetFieldsMetadataByEntity()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.Initialize()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.Render(DataLayerGeneration generation)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.TransformText() C:\891\inercyaEntityLite1121\DataLayer.tt 1 1 inercyaEntityLite1121


EntityLite 1.12.1 was released back in September 15, 2015 and targeting .NETFramework 3.5 Client. This could have been the culprit for Specified cast is not valid. So I removed previously installed dll's in GAC and DbProviderFactories entries in devenv.exe.config. If you follow step by step actions mentioned below, you may avoid such errors I faced as mentioned above:


0. create a database ,a table as mentioned in comment section of bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs


1. Create a project targeting .Net 3.5:


2. In this project if you try to install npgsql anything above 2.2.7, you get error like as mentioned below:

PM> Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
Installing 'Npgsql 3.0.0-beta0001'.
Successfully installed 'Npgsql 3.0.0-beta0001'.
Adding 'Npgsql 3.0.0-beta0001' to i_nercya_EntityLite_1121_generate.
Uninstalling 'Npgsql 3.0.0-beta0001'.
Successfully uninstalled 'Npgsql 3.0.0-beta0001'.
Install failed. Rolling back...
Install-Package : Could not install package 'Npgsql 3.0.0-beta0001'. You are trying to install this package into a project that targets '.NETFramework,Version=v3.5', but the package does not
contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.At line:1 char:1
+ Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], InvalidOperationException
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand


So you have to go for Npgsql 2.2.7

PM> Install-Package Npgsql -Version 2.2.7
Installing 'Npgsql 2.2.7'.
Successfully installed 'Npgsql 2.2.7'.
Adding 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.
Successfully added 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.

3. Now go for EntityLite 1.12.1 ( dependencies are resolved here):

PM> install-Package EntityLite -Version 1.12.1
Attempting to resolve dependency 'EntityLite.Core (= 1.12.1)'.
Attempting to resolve dependency 'NLog (= 2.1.0)'.
Attempting to resolve dependency 'Microsoft.SqlServer.Types (= 10.50.1600.1)'.
Installing 'NLog 2.1.0'.
Successfully installed 'NLog 2.1.0'.
Installing 'Microsoft.SqlServer.Types 10.50.1600.1'.
Successfully installed 'Microsoft.SqlServer.Types 10.50.1600.1'.
Installing 'EntityLite.Core 1.12.1'.
Successfully installed 'EntityLite.Core 1.12.1'.
Installing 'EntityLite 1.12.1'.
Successfully installed 'EntityLite 1.12.1'.
Adding 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Successfully added 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Adding 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.

4. Retrieve the full name of dll's:
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll
Code for retrieving name is in bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs

5. Install these two dll's in gac:
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll"

6.1 In C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe.config ( or the respective config of devenv.exe, as per your installation, you may get path for devenv.exe by viewing the properties of VS shortcut.) make DbProviderFactories entry like:
 <system.data>
        <DbProviderFactories>
            .
.
.
<remove invariant="Npgsql" />
.
.
.
 <add name="PostgreSQL Data Provider"
           invariant="Npgsql"
           description=".Net Data Provider for PostgreSQL"
           type="Npgsql.NpgsqlFactory, Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
        </DbProviderFactories>
    </system.data>

Just make sure you use correct assembly fullname as retrieved in step 4
6.2 Restart Visual studio

7. Copy paste code similar to as mentioned in the attached code's bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.tt. Right click on this tt file and click "Run Custom Tool".
Save DataLayer.cs generated for later use in a separate folder for future use.
8.1 Close Visual studio.
8.2 Remove DbProviderFactories customization you did in 6.1
8.3 Remve the two dll's as in step 5, from gac
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Mono.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756"


 So, bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.cs generated in step 7 is your output of this activity.

Full code available at:
bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/