database on a Web server

[vstud]

Hi huys,

I need to write an application (vb.net or c# NOT asp pages) that retrieve data from a database (ms access) which resides on our webserver and display it to the client user.

I'm not sure how to access the database from the client's machine, meaning
the databse is not on the network but rather on a webserver. How do i connect to retrieve info from a file or d.b. in that case.

please point me or help me draft a solution to this type of scenarion, that would be great.

let me know if more info is needed.

TIA

p.s. I can't use asp pages for that matter.

[Plater]

That's kind of a weird situation.
You have a webserver with a ms access database, but you can't write software to run on that webserver (be it a web application or stand alone application)

Are you expecting your users to have to download the entire database every time they want to use it?
How are you planning on having it rectify changes with the server?

[vstud]

No, i don't want the user to download the databse, they don't need to change any data on the database, just to view info from the different tables (different views).

I should be able to write a stand alone application ond place it on the weserver,
but wasn't sure that its something acceptable to do..
how do the user trigger the executable?
should he use something like www.myurl.com/myexe.com ?

or should the application reside on the user machine and with some ADO commands or alike access the databse (if it's possible somehow and I'm missing it let me know).

TIA

[Plater]

And you aren't allowed to add things to the webserver located on that computer?
Hmm.
I guess you could write a custom executable that resides/runs on that system. It would have to listen on a socket for connections from whatever software you create to run on their client systems.
You really got stuck with nasty restrictions, this would be an easy task if you were allowed to make changes to the webserver modules on the computer that is hosting the access database.

[vstud]

sorry, I should be clearer,
Yes,I'm allowed to add and change things on the webserver, and I already
implemented some asp pages. I'm just checking my options as I was thinking that I can accomplish the same thing via vb.net and control the security part of the application better ..correct me if I'm wrong..

Is there a way that i can use, web services and refer to the web service via
stand alone application on the user machine?

[Curtis Rutland]

Is there a way that i can use, web services and refer to the web service via
stand alone application on the user machine?

I've had an issue like this once. What I did is create a .asmx web service on the web server. I added several methods, all taking a string for a parameter (the sql statement).

One method was for selecting (returns a DataTable), one was for selecting scalar like count and sum (returning int) and the other was for all other queries (returning int for rows affected). It's like a passthrough engine, using the same methods you would use if you could do it locally, but since you can't, put it on the web server.

Then you write your console/windows app, and add a web reference to the service you created.

I can post sample code, if you need it.

[vstud]

I've had an issue like this once. What I did is create a .asmx web service on the web server. I added several methods, all taking a string for a parameter (the sql statement).

..... <snip>
I can post sample code, if you need it.

please do post or send me sample code, my experience with web services is thin

TIA

[Curtis Rutland]

please do post or send me sample code, my experience with web services is thin

TIA

Sorry it's taken me so long to reply; the computer I have my code on was bluescreening this morning.

Ok, first create a web service project:

Then, in Service.cs this is the code I used. You will need to modify it to fit your needs:

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.OleDb;
 
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    private const string CONN_STR = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb;User Id=admin;Password=;";
    private OleDbConnection Conn;
    private OleDbCommand Cmd;
    public Service()
    {
        Conn = new OleDbConnection(CONN_STR);
        Cmd = new OleDbCommand();
        Cmd.Connection = Conn;
        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }
 
    [WebMethod]
    public DataTable Select(string selectCommandText)
    {
        OleDbDataAdapter adapt = new OleDbDataAdapter(selectCommandText,Conn);
        DataSet ds = new DataSet();
        adapt.Fill(ds, "table");
        return ds.Tables["table"];
    }
 
    [WebMethod]
    public int ExecuteNonQuery(string commandText)
    {
        //Cmd.CommandText = commandText;
        Cmd = new OleDbCommand(commandText, Conn);
        Cmd.Connection.Open();
        int affectedRows = Cmd.ExecuteNonQuery();
        Cmd.Connection.Close();
        return affectedRows;
    }
 
    [WebMethod]
    public double ExecuteScalar(string selectScalarCommandText)
    {
        Cmd.CommandText = selectScalarCommandText;
        Cmd.Connection.Open();
        double result = Convert.ToDouble(Cmd.ExecuteScalar());
        Cmd.Connection.Close();
        return result;
    }
}
 

Then in another project, you can add a web reference, using the url to the service. For example, if your website was www.somesite.com, and you put it in a directory called "service," the url you would link to is http://www.somesite.com/service/service.asmx . Then, instantiate the webservice object:

string tmp = "insert into test (fname, lname) values ('jim','bob')";
testWebServicce.localhost.Service serve = new testWebServicce.localhost.Service();
serve.ExecuteNonQuery(tmp);
 

You will need to replace testWebServicce.localhost with whatever namespace you are using.

Also make sure that the user: IUSR_<yourmachinename>
(Replace <yourmachinename> with the computer name, in this case, the webserver)
has access to the database directory,

This could be a little hard to follow if you don't have any experience. If you have trouble with any part, just reply and let me know.

[vstud]

<snip>
This could be a little hard to follow if you don't have any experience. If you have trouble with any part, just reply and let me know.

This is great, thank you for your effort. I have better grasp now, still it will take me some time to delve into it.
One quick question: I take it that once the web service is in place, one is able to create the reference to it from vb.net (or c#) and then drop a grid view (for example) on his form and trigger and bind the web service methods ..correct?

Thank you

[Curtis Rutland]

Yes, you can add this reference from either VB or C#. You can't directly bind to one of the methods, but you can do it indirectly.

namespace.youUsed.Service s = new namespace.youUsed.Service();
DataTable table = s.Select("select * from table");
GridView1.DataSource = table;
GridView1.DataBind();
 

This is great, thank you for your effort. I have better grasp now, still it will take me some time to delve into it.
One quick question: I take it that once the web service is in place, one is able to create the reference to it from vb.net (or c#) and then drop a grid view (for example) on his form and trigger and bind the web service methods ..correct?

Thank you