[dba-VB] Browser based database

This time without the RTF

Damn, keyboard. That was supposed to say "I use a click once app for our
sales force"

Here's a sub that calls a webservice and populates a table for a SSCE
database: (but you could do what ever you want with your returned dataset)
private void retrieveData(int intMode) {
        // This process will call a webservice passing a parameter which in
turn executes a SQL stored procedure, returning a dataset
        try {
            // call webservice and populate dataset
            localhost.Service1 MyObj = new localhost.Service1();
            DataSet ds;
            ds = MyObj.RetrieveSQLdata(intMode);
            // At this point, the dataset has already been filled by the web
service
            SqlServerCe.SqlCeConnection CEconn;
            SqlServerCe.SqlCeCommand CEcmd;
            CEconn = new SqlServerCe.SqlCeConnection(("Data Source ="
                            + (PathPC + FileName)));
            CEconn.Open();
            CEcmd = new SqlServerCe.SqlCeCommand();
            CEcmd.Connection = CEconn;
            // Delete the exisiting data from the iPAQ tables before
populating it
            string TableName = "";
            switch (intMode) {
                case 1:
                    TableName = "PListHdr";
                    break;
                case 12:
                    TableName = "PListItems";
                    break;
            }
            CEcmd.CommandText = ("DELETE " + TableName);
            CEcmd.ExecuteNonQuery();
            this.lblStatus.Text = ("Retrieving "
                        + (TableName + " data from SQL"));
            System.Windows.Forms.Application.DoEvents();
            DataRow row;
            string mystr = "";
            int RowCount = 0;
            foreach (row in ds.Tables[0].Rows) {
                mystr = "";
                if ((intMode == 1)) {
                    mystr = (mystr + "INSERT INTO PListHdr (PListID,
PListDesc) ");
                    mystr = (mystr + "values(");
                    mystr = (mystr + ("\'"
                                + (row["PListID"].ToString + "\', ")));
                    mystr = (mystr + ("\'"
                                + (row["PListDesc"].ToString + "\')")));
                }
                else if ((intMode == 12)) {
                    mystr = (mystr + "INSERT INTO PListItems (PListID,
ItemNo, Price) ");
                    mystr = (mystr + "values(");
                    mystr = (mystr + ("\'"
                                + (row["PListID"].ToString + "\', ")));
                    mystr = (mystr + ("\'"
                                + (row["ItemNo"].ToString + "\', ")));
                    mystr = (mystr
                                + (row["Price"].ToString + ")"));
                }
                CEcmd.CommandText = mystr;
                CEcmd.ExecuteNonQuery();
            }
            this.lblStatus.Text = (TableName + " data retrieved from SQL");
            System.Windows.Forms.Application.DoEvents();
            CEcmd.Dispose();
            CEconn.Close();
            CEconn.Dispose();
        }
        catch (Exception ex) {
            throw new Exception("updateCustMast() Failed");
        }
    }



This is how the sub is called from within my local/users' app:
    public void UpdatePListHdr() {
        if ((DoesTableExist("PListHdr") == true)) {
            if (this.chkRunInDebugMode.Checked) {
                MessageBox.Show("UpdatePListHdr()");
            }
            this.lblStatus.Text = "Retrieving PListHdr data";
            System.Windows.Forms.Application.DoEvents();
            this.retrieveData(1);
        }
    }

This is the web service call (you can replace the string mystr=(); line with
your favorite dynamic SQL :
    [WebMethod()]
    public DataSet RetrieveSQLdata(int intMode) {
        SqlConnection conn;
        SqlDataAdapter da;
        DataSet ds;
        string mystr = ("EXEC stpRetrieveData \'"
                    + (LoginUser + ("\', " + intMode)));
        conn = new SqlConnection(strConn);
        da = new SqlDataAdapter(mystr, conn);
        da.SelectCommand.Parameters.AddWithValue("@Mode", intMode);
        ds = new DataSet();
        da.Fill(ds);
        return ds;
        conn.Close();
    }

so you can see, it is pretty simple if you are already making similar calls
in C#