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