[dba-VB] Goin' for the (browser based) gold

David McAfee davidmcafee at gmail.com
Wed Mar 17 12:54:38 CDT 2010


This is how I pull data from my SQLCE database and send it to my webservice:
(You would be reading from a CSV file instead)

    private void moveInvHdrFromSdfToSQL() {
        localhost.Service1 MyObj = new localhost.Service1();
        SqlServerCe.SqlCeConnection CEconn;
        CEconn = new SqlServerCe.SqlCeConnection(("Data Source ="
                        + (PathPC + FileName)));
        CEconn.Open();
        string strSQL = "SELECT
ICNo,StoreNo,InvNo,InvDate,CustPoNo,ResaleFlag,CrMemoFlag,SelForPrtFlag
FROM tblInvHdr";
        SqlServerCe.SqlCeDataAdapter CEda = new
SqlServerCe.SqlCeDataAdapter(strSQL, CEconn);
        DataSet ds = new DataSet();
        CEda.Fill(ds, "InvHdr");
        string RtnMsg = MyObj.InsertInvHdr(ds);
        if ((RtnMsg != "InvHdr data inserted")) {
            throw new Exception(RtnMsg);
        }
        CEda.Dispose();
        CEconn.Close();
        CEconn.Dispose();
    }


and this is the webservice being called:

[WebMethod()]
    public string InsertInvHdr(string strInput, DataSet dsIn) {
        int errNum;
        string errMsg;
        SqlConnection myConnection;
        SqlCommand myCommand;
        DataRow row;
        try {
            myConnection = new
SqlConnection("server=MyServerName;uid=MyLogin;pwd=MyPW;database=MyDataBaseName");
            myConnection.Open();
            myCommand = new SqlCommand();
            myCommand.Connection = myConnection;
            foreach (row in dsIn.Tables[0].Rows) {
                myCommand.CommandText = ("EXEC stpInsertIntoInvHdr \'"
                            + (row["ICNo"].ToString() + ("\', \'"
                            + (row["StoreNo"].ToString() + ("\', \'"
                            + (row["InvNo"].ToString() + ("\', \'"
                            + (row["InvDate"].ToString() + ("\', \'"
                            + (row["CustPoNo"].ToString().Replace(''',
"||") + ("\', "
                            + (row["ResaleFlag"].ToString() + (", "
                            + (row["CrMemoFlag"].ToString() + (", " +
row["SelForPrtFlag"].ToString())))))))))))))));
                myCommand.ExecuteNonQuery();
            }
            return "InvHdr data inserted";
            myConnection.Close();
        }
        catch (Exception ex) {
            errNum = Err.Number;
            errMsg = ex.Message;
            return ("SQL/WS Error: WS.InsertInvHdr - "
                        + (errNum + (":" + errMsg)));
        }
    }



And this is the Stored procedure:
(I've simplified it a bit, just to save time)

CREATE  PROCEDURE stpInsertIntoInvHdr(
	@ICnum AS VARCHAR(10),
	@StoreNo AS VARCHAR(16),
	@InvNo AS VARCHAR (8),
	@InvDate AS VARCHAR(22),
	@CustPoNo AS VARCHAR (20),
	@ResaleFlag AS INT,
	@CrMemoFlag AS INT,
	@SelForPrtFlag AS INT) AS

INSERT INTO InvHdr (ICNo, StoreNo, InvNo, InvDate, CustPoNo,
ResaleFlag, CrMemoFlag, SelForPrtFlag)
 VALUES (@ICnum, @StoreNo, @InvNo, @InvDate, @CustPoNo,
@ResaleFlag, at CrMemoFlag, at SelForPrtFlag)



This is using a SQL 2000 database, if you are using SQL Server 2008,
you don't have to insert line by line and can actually do a bulk
insert, which is even easier.

David


On Wed, Mar 17, 2010 at 10:20 AM, David McAfee <davidmcafee at gmail.com> wrote:
> John I vote for either a Webpage or a C# FE using a Web Service to
> transfer data.
>
> Web services are really easy.
>
> You can even distribute your FE via Click Once, so distribution is
> almost as easy as a webpage.
>
> I can help you out with those if you need.
>
> You can pass datasets to your webservice and have them Returned to your app.
>
> David McAfee
>
> On Wed, Mar 17, 2010 at 6:52 AM, jwcolby <jwcolby at colbyconsulting.com> wrote:
>> As you guys know, I have started doing a lot of stuff in C#.  One specific client places orders with
>> me to provide him "counts of records where..." kind of thing.  To make a long story short, it is a
>> moderately complex process which I am automating using C#.  However what I would REALLY like to do
>> is to make it a process that they can do themselves.
>>
>> My question to those who know more than I (translated "most everybody") how would i go about doing
>> something like this.
>>
>> 1) The client is in NY.
>> 2) The data is in my server in NC
>> 3) The server is Windows 2003
>> 4) SQL Server 2008
>> 5) I have and am getting pretty comfortable with C#
>> 6) I have moderately high internet speed, 12M down 1 meg up, but the new "burst mode" which
>> essentially doubles that for the first 30 seconds.
>>
>> So...  What are my options?
>>
>> 1) Make it a browser based widget that hits a web server here in my office.  I don't know how to
>> build a web app.
>> 2) Make it a service based C# program that they have on their desktop but hits a data service on my
>> office.  I don't know how to do web services yet.
>> 3) Something else that I am not thinking about yet.
>>
>> At the moment the client sends me a spreadsheet of zips.  I do a machination, place the zips in a
>> directory, import into a database in SQL Server.  Maybe perform a minor edit to an existing view to
>> get the counts.  Open an email and paste the results back in, and send the email.
>>
>> I have this process down from what used to take an hour or two back a year or so ago to about 15 or
>> 20 minutes today, but I still have to be in the loop.  My idea is to build a program that allows
>> them to do this themselves, log that it has happened and bill them $25 (or something) every time
>> they do a count.
>>
>> Let them do it themselves making it faster for them, gets me out of the loop, drops my income a
>> little but I get paid for my computer instead of my time.  Gets me out of the loop is BIG!
>>
>> --
>> John W. Colby
>> www.ColbyConsulting.com
>> _______________________________________________
>> dba-VB mailing list
>> dba-VB at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-vb
>> http://www.databaseadvisors.com
>>
>>
>




More information about the dba-VB mailing list