Reposted from the C# Express forum, by suggestion from JohnGrove...

This has been driving me crazy. I'm testing the water with C#, with a fairly simple calculator style program. There is no input, nor output. Just a static set of data programmed and loaded, which the user can run calculations against with values entered at runtime. (From controls on the form.)

Some of the data I need to work with is currently located in a Access mdb file. It's static data, so I don't need to edit the data or touch the db other than to get the initial data out of it. (The mdb is under 500k) I have added the mdb file to the project as a resource, and created the DataSet and Bindings and linked them to the form as needed. Everything works and works correctly. BUT, no matter what I do the program wants to touch the external mdb file at runtime when all I want to 'publish' is the exe.

How do I get Visual C# to include the data tables/views into the resources and STOP it from asking for the external MDB at runtime?

I've tried changing the "Build Action" setting on the mdb file and nothing seems to work. Resource, Embedded Resource, Compile, etc, and it still wants the mdb at runtime. AKA Click the exe and get a cannot find the mdb file error when someone tries to run the program from another computer. You can still use the rest of the program if you do the continue anyway action, but the part that relies on the db obviously has no data and is useless.

(And yes, the file was added as a resource in the Solution Explorer and isn't just a connection in the DataSource Explorer. So that shouldn't be the problem...I hope.)

You can't. It's a database. The way you access data in a database is to open the database and read it. As you even state in your post, "I actually did extract the data from the mdb" the last time you did this.

If you really don't want to include the database (and why not, since it gives you databinding?), I would export the data into XML, and then set up a datasource to point at the XML and read it with Linq.

This question is outside the scope of this forum, but here's an idea of what I'm talking about:

Add New Item
choose XML To Schema
Find the XML file of your data
Name it appropriately. It will add it to your project.

You can then do this to load it:

Dim myXML as XElement.Load(filename)

And then use Linq queries to read it. (This example comes from one where I'm loading the whole "table" and parsing it for something.)

I don't understand what you have against distributing the Access database, or converting it to SLQCE and deploying that with your application. (SQLCE is a very lightweight SQLServer version that you are free to deploy as a one-database-per-user solution).


Good luck.
RobinDotNet

---

Click here to visit my ClickOnce blog!

The data I want to use is coming from a database, but it's just a format/source. I don't need any of the other database functions (Edit, Update, Delete, etc.), just the format and handling of the data so I can bind the data to a form. (A readonly data source or prefilled static DataSet.) To that effect I would like to include the data of the database in the program as a resource (like Icons or strings are handled) so that the end user isn't breaking things by looking, moving, editing or even knowing about the data as a seperate file from the main program. It's simply more convenient and less exhausting to manage the data in a db than in text files.

I have figured out how to get the IDE to do some of this, I can include the mdb file into the exe...except I can't get C# to use it when it comes time to get the data out. It still looks for a external file and there doesn't appear to be any way to get C# to do anything but that.

This is contrary to what C# *appears* to be telling me. As in, if I go to Resources in Solution Explorer and Add Existing Item, then change the file type it lists *.mdb under "Data Files". When I do that it pops up the Data Source configuration wizard, and from that point on what I want to use as a "data file" ends up being treated as a "data connection" to an external source. You can manually add the mdb file to project resources (outside of the Solution Explorer), but none of the data configuration wizards or controls can "see" the data from there.

My other option seems to be to extract the data from the mdb as text, and then include those files into the resources and manually parse them to manually build a DataSet at runtime. I'm not familiar enough with how C# handles xml to know if that format would make them any easier to handle than plain ol' text however. Either way though, I'm still left having to write a lot of custom code to get the data back into a format that can be linked to the form easily.

A bit of background here in case this helps explain: This is actually a rewrite/expansion of a project I originally wrote a few years back in an old version of Borland's C++ compiler that I had laying around. At that time, I actually did extract the data from the mdb, included the data as a 'flat text' resource, and then read the data at runtime to build up the "lists" that were linked to each control on the form. The key point here being that I had multiple lists and I switched between them using pointers...something which C# doesn't let you do. So far though, I can 'emulate' the list switching in C# by simply unbinding and rebinding datasets to the form controls/displays. (Using a query would be redundant and inefficient for what I am doing.)

My main problem then, is either I can find some way to include the data from the mdb into the exe as a resource and by extension automate the dataset population...OR I am going to have to manually create and populate each and every dataset and binding from raw text at runtime unless I distribute the mdb file itself with the app. It can be done...it's just a needless expense of time and effort to avoid exposing more of the program internals (data) than should really be necessary.

You can't. It's a database. The way you access data in a database is to open the database and read it. As you even state in your post, "I actually did extract the data from the mdb" the last time you did this.

If you really don't want to include the database (and why not, since it gives you databinding?), I would export the data into XML, and then set up a datasource to point at the XML and read it with Linq.

This question is outside the scope of this forum, but here's an idea of what I'm talking about:

Add New Item
choose XML To Schema
Find the XML file of your data
Name it appropriately. It will add it to your project.

You can then do this to load it:

Dim myXML as XElement.Load(filename)

And then use Linq queries to read it. (This example comes from one where I'm loading the whole "table" and parsing it for something.)

I don't understand what you have against distributing the Access database, or converting it to SLQCE and deploying that with your application. (SQLCE is a very lightweight SQLServer version that you are free to deploy as a one-database-per-user solution).


Good luck.
RobinDotNet

---

Click here to visit my ClickOnce blog!

Hi Nobodaddyï¼?br/>
As Robin said, it is impossible to embed database into resource. The problem you want to solve is make sure the mdb file will be deployed along with your application. Once the application folder contain that file, user won't receive that error. Am I guess right?

1. If you use ClickOnce deployment, you can add that mdb file to your project first, then on the Publish page, click the "Application Files..." button, to make sure that file has been included.

2. If you use MSI, you can add that mdb file into "Applicaiton Folder" manually by Add -> File...

If I misunderstood you, please tell me.

Sincerely,
Kira Qian

---

Please mark the replies as answers if they help and unmark if they don't.

I don't want to include the database, since all I wanted was the data in a "record" format. It didn't need to be a database other than for C# to 'understand' the format and do the databindings and fill the datatable. The documentation I was able to run across made it sound like it was possible to do this using the mdb as a source and there didn't seem to be a way to do the same thing with other data formats without manually parsing the files and writing code to do that.

I think the confusion came about as all the docs make the assumption that you know what they are talking about without explaining what they are talking about. So when I read that you could set whether your program read the db only at build time, it wasn't readily apparent that they were talking about the program copying the mdb file to the working and publishing folders (Copy never, Copy if New, etc.) and not simply re-reading the db to refresh the data tables taken from the mdb. The docs simply assume that you know that a dataset only exists at runtime in memory, and can't be pre-read and stored in a file format (The 'DataSet' in the resources).

There is also the naming confusion. There is a database program (Access/SQL), a database 'file' format (mdb) and then the data itself. (The columns and records.) My assumption was that the mdb was treated like what it's physical presense was, a binary data file. You don't need the full blown database program to 'read' the database *file*. Therefore it made perfect sense that C# would be able to 'read' the mdb file without having to load the database interface/program & external file every time. Excel and other programs for example have no problem importing 'data' from mdb files for example, and once imported the source/database file isn't needed again.

As for why I didn't want to include the mdb, that is a multipart answer.

First: I was still under the misunderstanding that it wasn't necessary. (That the dataset was actually importing the data from the mdb.)

Second: The program was created with the intention of not having multiple files be a part of the end product. Much less have them in an 'outside' format that anyone with Access (the program) could open and break the program by modifying. (The 'data' in the database isn't anything not already known by everyone who might use this program, it's just the file integrity I'm worried about.)

Third: If I had planned to include the data as an external file, I had to make sure the program could use the file on the end user's computer! Either I need to write code into the program to parse those files, or I had to make absolutely certain that the end user had the drivers, programs or filters necessary to read the data on their computer or the program would crash. This is an issue since the program is mainly just a 'free' downloadable application for anyone to use. ("As Is", thus the simpler the program the better, since I wasn't planning on doing 'support' for it!) And thus there is no expectations on what the end user would have on their PC other than .Net/Windows(XP-Vista). My earlier version ran into this problem with the Borland runtimes, and I wanted to sidestep the possibility that the end user wouldn't have the Jet Drivers or Office Runtimes necessary to open a standalone mdb. Or if the exe would handle that automatically...that kind of thing is never covered in documentation very well and doesn't show up on the 'developement' PC, so you only see it (like I did this time) when it's released to the end users. (Or end user tester in this case.)

Fourth: I could have gone so far as to "hard code" all the data that's in the mdb file, there are some other things in the end program done that way in fact. Except the emount of records in this section was a bit large, and is far more likely to be altered on an irregular basis. So storing the data in a external format and imported as needed was a practical decision.

After looking over everything, I think in the end my solution is going to be done in two steps. First will be to rebuild the mdb into a 'releasable' format to go with the current exe for this 'release'. And second to rework the program to load that data from xml/text resources in the future versions, since I am planning on rewriting any future version anyway. (Clean up the code, rewrite it for better code practices, etc.)

Okay, that's helpful. One thing you might look into is the SQL Compact Edition. It is very lightweight SQLServer,and is basically treated like a file, but I think it's more lightweight than an Access database. You should be able to use the same ADO.Net code to access it as you do for the Access database, and just change the connection string. It's free to distribute it as a one-user-one-database solution, and it would be perfect for what you're trying to do, and remove the dependency on the Jet drivers. And while a user may have Access installed, it's unlikely that anyone other than a developer is going to go download SQLServer in any way, shape or form.

You can deploy it as a prerequisite, OR by deploying the specific dll's required to run it along with your application.

Post back if you're interested, and I'll dig up the information and post back.

I think if you definitely don't want anyone hacking the data, you should store it in XML and include it as embedded resources.

RobinDotNet

---

Click here to visit my ClickOnce blog!

The SQL compact is a bit of overkill yet for what I'm doing. I can see where it makes more sense than relying on other pre-installs, but it's still more than is really necessary for this project.

I'm currently looking into the XML solution, but have just started working it out. (The time I have for this project is fairly sporadic right now.) I have an example of how the data in the XML should look, but no examples of how to interact with it once I add it to the project yet.

If you know of a good starting point for finding XML to DataSet examples online, I'm all ears. (And eyes!)

You haven't said VB or C#, so here's some help in C#. I'm no expert, mind you, but maybe this will help you. This assumes, of course, that your XML is formed correctly. So you might want to take your dataset, convert it to XML this way, and write it to a file in order to create it and know it will convert back.

You could store your XML strings in the Resources file or in the Application Settings, since you said they weren't going to be changed by the user, just be input data.

public static string ConvertDataSetToXMLString(DataSet myDataSet)
{
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
myDataSet.WriteXml (xw);
string XmlString = sw.ToString();
return XmlString;
}

public static DataSet ConvertXMLStringToDataSet(string XMLString)
{
try
{
DataSet OutputDataSet = new DataSet();
StringReader sd = new StringReader(XMLString);
XmlTextReader reader = new XmlTextReader(sd);
OutputDataSet.ReadXml(reader);
return OutputDataSet;
}
catch(Exception ex)
{
return null;
}
}

RobinDotNet

---

Click here to visit my ClickOnce blog!

I started to test using xml and the example you gave, but before I got too far in working out the bugs, I found the 'easy' way to do the first half of this. I started by making a 'junk' project just to test with, but if I want to ever polish it, I can remake it into a command line app for batching.

First, since the point of this was just to get the data from the mdb file into XML format, it wasn't an issue in just creating the db connection to the dataset as otherwise would be normal. After that it was almost a snap to just use one method to do all the work...here is the code I ended up with.

private void button1_Click(object sender, EventArgs e)
{


string teststring = lineage2DataSet1.GetXml();

System.IO.StreamWriter crywriter = new System.IO.StreamWriter("CryData.xml");
lineage2DataSet1.WriteXml(crywriter, XmlWriteMode.WriteSchema);
crywriter.Close();

textBox1.Text = teststring;


}

The string and text box is just there to 'see' the xml data without opening the output file manually. If you look you can see that I write the schema to the file, but it doesn't show up in the textbox as the WriteXml and GetXml behave slightly differently.

Of course not everything worked out smooth to start with. At first I just got the schema but no data, or the tempuri URL but nothing else. It caused me some grief before I figured out that this was because none of the data was being fetched from the db at all! Or to be a bit more precise, nothing was being pre-fetched. The connection was setup, but nothing was being 'filled'. That is, I was writing the data out to the file, but all the data was 'empty' since it had never been moved from the mdb to the dataset.

To fix this I created a bunch of comboboxes and linked their datasource to the tables I wanted the data from. (Not precisely necessary to make the comboboxes, but it helped visually make sure I didn't miss any table data, and let the compiler handle the code automatically.) This would cause the dataset to 'fill' on form load and I would get all the data I needed when it wrote out to the xml file.

Now I just need to make the other half of the equation work by getting the data from the xml into the dataset in the actual program. Here since I don't want the external file I can't use the ReadXML directly, and will have to work out how to use the ResourceReader to do this trick. Gutting the old db connection is going to be half the problem, due to the errors you get if you don't completely 'clean up' the binding code, but if you do it looks like I'd have to rewrite the same thing afterwards anyway. That and making sure nothing points to the old mdb file or connection settings either, even if it's not acutally used it will still throw up some unnecessary errors.

Cool, that's great. I'm glad you've figured out how to get it to work for you.

RobinDotNet

---

Click here to visit my ClickOnce blog!