database on a Web server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • vstud
    New Member
    • May 2008
    • 17

    database on a Web server

    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
    Recognized Expert Expert
    • Apr 2007
    • 7872

    #2
    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?

    Comment

    • vstud
      New Member
      • May 2008
      • 17

      #3
      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

      Comment

      • Plater
        Recognized Expert Expert
        • Apr 2007
        • 7872

        #4
        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.

        Comment

        • vstud
          New Member
          • May 2008
          • 17

          #5
          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?

          Comment

          • Curtis Rutland
            Recognized Expert Specialist
            • Apr 2008
            • 3264

            #6
            Originally posted by vstud
            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.

            Comment

            • vstud
              New Member
              • May 2008
              • 17

              #7
              Originally posted by insertAlias
              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

              Comment

              • Curtis Rutland
                Recognized Expert Specialist
                • Apr 2008
                • 3264

                #8
                Originally posted by vstud
                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:
                Code:
                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:
                Code:
                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_<yourmachi nename>
                (Replace <yourmachinenam e> 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.

                Comment

                • vstud
                  New Member
                  • May 2008
                  • 17

                  #9
                  Originally posted by insertAlias
                  <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

                  Comment

                  • Curtis Rutland
                    Recognized Expert Specialist
                    • Apr 2008
                    • 3264

                    #10
                    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.

                    Code:
                    namespace.youUsed.Service s = new namespace.youUsed.Service();
                    DataTable table = s.Select("select * from table");
                    GridView1.DataSource = table;
                    GridView1.DataBind();
                    Originally posted by vstud
                    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

                    Comment

                    Working...