StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Connection Wizard or Hardcoding datasource

RE: Connection Wizard or Hardcoding datasource

StrataFrame Team — Mon, 21 Jan 2013 10:50:58 GMT

What is the specific error you receive, Marcelo? The connection string looks fine, and I'm pretty sure that the System.Data.SqlClient provider will support SQL Server CE.

Connection Wizard or Hardcoding datasource

Mike Thomas — Mon, 21 Jan 2013 10:50:58 GMT

i am using sql server ce 3.5 my oledb provider is Microsoft.SQLSERVER.CE.OLEDB.3.5. how do i modify appmain.vb to use this? i don't think the connection wizard handles this but i could be wrong. can someone step this beginner through whats necessary to hook my app up to the compact edition?

Thanks in advance!

Mike

RE: Connection Wizard or Hardcoding datasource

Marcelo Blank — Mon, 21 Jan 2013 07:45:38 GMT

Hi.

I´m having the same problem ... I need to use a Compact SLQ to a small test app.

I can´t get the connection on the app...

I´m doing this, but gives me an error when try to get the data

private static void SetDataSources()

{

DataLayer.DataSources.Add(new SqlDataSourceItem("ATG",""));

MicroFour.StrataFrame.Data.DataLayer.DataSources["ATG"].ConnectionString =@"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;

Data Source=D:\PROJETOS\SGO\ATGConnector\ATGConn\ATGConn\Ordens.sdf;SSCE:Database Password='123456';";

}

How to set up the SQL Compact with Strataframe ?

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Sun, 14 Sep 2008 10:26:25 GMT

Thanks for the contribution, Keith. You may want to add this to the user contributed samples area...I think that this would be a good fit ;)

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Sun, 14 Sep 2008 01:23:23 GMT

Trent is right naturally, anything but a very simple app or disconnected feature would be better with Express.

That being said, my current project is VERY simple as far as data goes as it mostly does scanning and image manipulation, and will be single workstation only, so SQL Compact I think will be the right choice based on past experience with it.

The attachment is a very simple working sample that uses SF 1.6.6, the ADO .NET Provider for SQL Compact 3.5 (had issues with OLEDB updates I couldnt get past) included with VS2008, and the System.Data.SQLServerCE namespace used to create a SQLCEDataSourceItem. I tested with OptomisticRowVersion since that is what I use. Just slap the SDF file in the ZIP onto the root of your C drive and it should work. Good luck and I hope it helps someone.

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Sat, 13 Sep 2008 22:34:46 GMT

[quote]Hi Trent, if you had to develop a desktop solution for say home users - what database would you use foxpro or access? it looks like i may not understand enough to get the framework to work with sql compact edition. I am a past foxpro person but i wonder about using that anymore as a backend. [/quote]

If I had to choose between those two it would be Access. FoxPro is nothing but a problem waiting for a place to happen...this comes from MANY years of experience.

But in truth, I would not use either of these, but SQL Server Express. I know that this post has been geared around embedded database conversation, but there is a reason that we have not yet created a data source item for this...there is generally a better solution. I do see the benefits of a disconnected laptop for the day, etc. But even then I would tend to lean towards SQL Server Express. First of all, it is free. Secondly, it is faster and a full version of SQL Server. Additionally, you don't have to deal with data type issues. For example, SQL Mobile doesn't have a VarChar data type but only a NVarChar (unless something has changed recently). Not that using an NVarChar is bad, but it just might not line up exactly with the desktop structures (if you a.] have the need or b.] ever plan to have a larger version of an application).

In my opinion, and if I were hired in a capacity for consultation purposes in a situation like this, I would tell my client that they are better off developing on a full SQL Server version versus an embedded edition....I have a lot of embedded experience from years past and there are other issues that you will inevitabely run into (i.e. row locking, etc.).

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Sat, 13 Sep 2008 22:27:48 GMT

Distributing SQL Express is extremely easy. In fact, if you use a tool like InstallAware, it is literally as easy as clicking a CheckBox and tweaking a few parameters. We use InstallAware for our medical software and though, like any install software, there are some frustrations, one thing that they got right were the pre-reqs and installing pre-reqs.

Even if you choose to do this yourself, you can download the SQL Server Express redistributable and via command lines install it very easily. SQL Server is also easy to test for in regards to services if you take the manual approach. We use SQL Server Express in a high percentage of our medical software user base and so we have to test for and install SQL Server Express if needed. This is really one of the easier parts of the install.

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Fri, 12 Sep 2008 17:17:41 GMT

Two apps i have in mind...one is for the home market and I was thinking i didn't want the overhead and possibly slower performance of the SQL Express if I could use compact edition. Also, i am not sure whats involved in distributing sqlexpress to get it install properly as part of the application installation.

do you have some experience with sqlexpress and distributing?

RE: Connection Wizard or Hardcoding datasource

Ivan George Borges — Fri, 12 Sep 2008 17:12:55 GMT

Hey Mike.

Just curious... what are your reasons for not using SQL Server Express?

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Fri, 12 Sep 2008 15:50:37 GMT

Hi Trent, if you had to develop a desktop solution for say home users - what database would you use foxpro or access? it looks like i may not understand enough to get the framework to work with sql compact edition. I am a past foxpro person but i wonder about using that anymore as a backend.

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Fri, 12 Sep 2008 15:03:06 GMT

I am up and running yes.

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Fri, 12 Sep 2008 14:49:01 GMT

Kieth, not sure what you are saying....you app is now working against sql compact edition because you created your customer datasource item?

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Fri, 12 Sep 2008 14:00:00 GMT

Well, this would really only be something that you could answer. It will be a while before we create an SqlCeDataSourceItem ourselves...so if you have this immediate need and do not want to use the OLEDB, then you may want to proceed.

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Fri, 12 Sep 2008 11:32:59 GMT

Trent any opinion on wheher it would be worthwhile to finish the SQLCEDatasoureItem I started versus using OLEDB?

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Fri, 12 Sep 2008 11:29:23 GMT

Oh my. That worked on my work PC, I was trying this stuff at home last night, apparently there is a problem with my install on that box that was the cause of my issues, better late than never.

Thank you for posting this. On a PC that has a good install ( :cool: ) I got OLEDB working.

1) Created OLEDBSOURCE class

2) use #1 in appmain:

MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(New OLEDBSOURCE("", "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\ioivb.sdf"))

3) Change my fills to use OLEDBCommand instead of SqlCommand

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Fri, 12 Sep 2008 10:47:36 GMT

BO Mapper worked fine for me against sql compact edition. Just had to select oledb and then have the proper connection string which for me was the following

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Users\michthom\Documents\Visual Studio 2008\Projects\premframev2\premframev2\premdatav2.sdf

i think i don't understand the framework enough to do what trent is suggesting. I would love if someone had a sql compact code example i could customize for my file. I do really need to use the desktop edition so i am not sure what to do

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Fri, 12 Sep 2008 08:04:27 GMT

I could not get OLEDB to work, if anyone does I would appreciate an email to kchisarik@sigmadatainc.com to let me know what you did but for now I have moved on based on I would rather is an ADO driver than OLEDB anyhow and I spent too much time on OLEDB already.

The provided SQLDataSourceItem will not work due to differences in features support between SQL Serevr and SQL compact. Basic stuff like schema designation, use of IDENTITY_SCOPE are not supported and must be removed from the SQL commands. I went down the road of creating a SQLCEDataSourceIitem that uses the System.Data.SqlServerCE namespace and have had good results so far. I have basic databinding and UPDATES working using the following in appmain.

MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(New SqlCEDataSourceItem("", "Data Source=C:\ioivb.sdf;"))

Again, if someone out there gets either the BOMapper or SF in general to work with less effort using OLEDB, I would appreciate a heads up. If anyone is interested in the SQLCEDataSourceItem, let me know. It will only have the "_NONSP" items done, but it will work.

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Fri, 12 Sep 2008 08:02:37 GMT

Well, Ivan was telling you how to get back to ground zero. The AfterBuild.bat file is actually distributed with the source code, but the PostBuild event is just looking for it on the c:\ folder. So you can copy the AfterBuild.bat to the c:\ and then it will work from that point forward.

Secondly, you should not be changing the SF source code, which it sounds like you are doing. You should create your DbDataSourceItem in one of your assemblied, not the SF base assembly. You NEVER want to change the SF source if you plan to ever receive another update. This is why you want to create your own base assembly which references the SF Base and then either inherit and override logic or create your SF inherited classes there so that when you get a new version of SF it doesn't wipe out your code and require you to go back and keep putting things in.

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Fri, 12 Sep 2008 06:30:07 GMT

Thank you will try.

Do you know if by getting that error that anything was rebuilt? do i need to take out the post build event out to get it to rebuild successfully first?

Thanks again everyone!

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Thu, 11 Sep 2008 21:48:01 GMT

Oh...and Ivan's suggestions look good, Mike :D

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Thu, 11 Sep 2008 21:47:29 GMT

[quote]PS - Cowboys 21 - Eagles 30 Monday night [/quote]

Well...you know, the other day Charles Hankey sent me something prior to the Browns game...and well, we know how that turned out. So I will wait until next Tuesday before I say too much...but when the Boys win :w00t: ... then I will have more to say :D

RE: Connection Wizard or Hardcoding datasource

Ivan George Borges — Thu, 11 Sep 2008 16:41:24 GMT

Hey Mike.

Try the following:

Close down all Visual Studio Instances
Open two Explorer Windows. Navigate one to the "C:\Program Files\MicroFour\StrataFrame Source Code\MicroFour StrataFrame Base\bin" folder and the other to the "C:\Windows\Assembly" folder.
select the "MicroFour StrataFrame Base.dll" from the bin folder
Drag the selected file into the "c:\windows\assembly" folder (GAC)
Also copy the "MicroFour StrataFrame Base.dll" and the "microfour strataframe base.xml" to the "c:\program files\common files\microfour\strataframe" folder
Your assemblies will now be on the latest build.
Go back into Visual Studio

RE: Connection Wizard or Hardcoding datasource

Mike Thomas — Thu, 11 Sep 2008 14:59:52 GMT

copied file and renamed ...not sure what all changes need to be made as it looks very generic to any ole provider.

tried to rebuild microfour.strataframe base and got the following error...

Error 1 The command ""C:\Program Files\MicroFour\StrataFrame Source Code\MicroFour StrataFrame Base\AfterBuild.bat" "C:\Program Files\MicroFour\StrataFrame Source Code\MicroFour StrataFrame Base\bin\MicroFour StrataFrame Base.dll" "C:\Program Files\MicroFour\StrataFrame Source Code\MicroFour StrataFrame Base\bin\microfour strataframe base.xml"" exited with code 9009. microfour strataframe base

was i doing the right thing?

RE: Connection Wizard or Hardcoding datasource

Keith Chisarik — Thu, 11 Sep 2008 12:26:43 GMT

Excellent, your your vacation was relaxing. Welcome back.

PS - Cowboys 21 - Eagles 30 Monday night

RE: Connection Wizard or Hardcoding datasource

Trent L. Taylor — Thu, 11 Sep 2008 12:10:44 GMT

[quote]Is adding official support for SQL Compact on the radar?[/quote]

Yes...but it is a very faint blimp :) There are a number of other more highly demanded things ahead of this...so though this is something that will definitely be part of the framework, it may be a while. Probably the next major release (SF 2.0).