VB.Net app send information via HTTP to SQL server

[Mr. Arnold]

Hi

A want to write a new local VB.Net application. One of the features I want
it to have is to be able to send a few small pieces of information to my
website's backend SQL Server database hosted at GoDaddy.com. E.g.

Client Code: 456418
Amount: $455.98
Stock Number: 945482

I expect I'll just want to run an INSERT query statement to append this
data
to the financial transactions table.

How could I do this? I have programmed quite a bit in MSAccess, used
FrontPage to write ASP.NET but never with VB.Net Studio. I can download VS
Express VB.Net from MS.

What you need is a good ADO.NET book.

http://www.google.com/search?hl=en&q=ado.net+and+SQL+sever+book&btnG=Google+Search

 

[Steven Cheng]

Hi Jonathan,

From your description, you're going to write a client winform program that
will send some data to a remote SQL Serve database hosted on a host
provider's server, correct?

Based on my understanding, since database server(such as SQL Server is
normally protected from directly internet access, especially for those high
restricted host providers), you may consider the following means to make
your application here:


** Instead of a winform app, why don't you add an additional page(like an
adminstrative page) in your existing ASP.NET web application and add
ADO.NET code in this page to query or update the backend database. Thus,
you can just access this administrative page directly rather than create
another winform application.


** Instead of web page, you can create a webservice in your ASP.NET
application and put the ADO.NET code which update database in the
webservice. And your local winform application can just call that
webservice to update database:

#ASP.NET 2.0 Web Services QuickStart Tutorial
http://quickstart.developerfusion.co.uk/quickstart/webservices/

#Build and Consume an ASP.NET Web Service
http://softwarecommunity.intel.com/articles/eng/2184.htm

How do you think? If you have any other particular concerns, please feel
free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

 

[Michel Posseth [MCP]]

Hello Jonathan

The most obvious aproach would be in my opinion to create a webservice that
accepts the parameters from your winform app
so do some googling on webservices and see if this suits your needs in my
opinion it is the easyest way to acomplish what you want
although there are other ways ofcourse,, with the httpwebrequest object for
instance you could post the data to a webpage but this requires a lot more
coding on the client ( winform ) side while the webservices would generate
all the code automaticly ( you wouldn`t be aware of the low level
comunication )


regards

Michel

 

[Jonathan]

Hi

A want to write a new local VB.Net application. One of the features I want
it to have is to be able to send a few small pieces of information to my
website's backend SQL Server database hosted at GoDaddy.com. E.g.

Client Code: 456418
Amount: $455.98
Stock Number: 945482

I expect I'll just want to run an INSERT query statement to append this data
to the financial transactions table.

How could I do this? I have programmed quite a bit in MSAccess, used
FrontPage to write ASP.NET but never with VB.Net Studio. I can download VS
Express VB.Net from MS.

 

[rowe_newsgroups]

Thanks. That's good because I don't want the user to see what's going on --
they don't need and I don't want them to see the communication. My plan is
to have it come from a Microsoft Office Add-in (Vb.Net, Office 2007, VSTO)
which I am hoping to write soon.

I trust the communication will in all situations get through any corporate
firewalls at any of the client sites?

I have scoured around the internet to see any Vb.Net examples of code of how
this could be done but haven't found a simple, comprehensive example as yet.

If anyone knows the code needed to say run an INSERT query to push the data
into thewww.mywebsite.comSQL Server that would be appreciated.

Client Code: 456418
Amount: $455.98
Stock Number: 945482

tblTransactions

Also, in general terms, what really is meant by the phrase 'Webservice'?

You won't (or at least shouldn't) be passing the information to the
Sql Server directly. Instead, you'll need to add a webservice to your
website that will be called. The service could be www.mywebsite.com/webservice.asmx
or whatever you want to name it. If it's in a subdirectory and you are
using the web.config to store your connection string (which you should
be) you'll only need to update the connection string in one place for
both the web site and the web service.

To add a web service to your site, just right click on the project in
the solution explorer and do add --> new item --> web service (or
something like that). Call the service something meaningful, as once
you start to use it you won't be able to change the name easily. A
sample web method for the service would be something like:

/////////////////
'// Typed in message

<WebMethod()> _
Public Sub saveClientTransaction(clientCode As String, amount As
Double, stockNumber As String)
Using conn as new
SqlConnection(ConfigurationManager.ConnectionStrings("ConnString").ConnectionString)
Using com as SqlCommand = conn.CreateCommand()
conn.Open()

com.CommandType = CommandType.StoredProcedure
com.CommandText = "saveClientTransaction" '// Or whatever
the name of your stored proc is

com.Parameters.Add("@ClientCode", SqlDbType.VarChar).Value
= clientCode
com.Parameters.Add("@amount", SqlDbType.Decimal).Value =
amount
com.Parameters.Add("@stockNumber",
SqlDbType.VarChar).Value = stockNumber

com.ExecuteNonQuery()
End Using
End Using
End Sub
////////////////////

Please also note that it might be a good idea to look into SSL if you
are doing sensitive transactions, the request sent to your service
could easily be intercepted and used maliciously.

Thanks,

Seth Rowe [MVP]

 

[Jonathan]

Thanks. That's good because I don't want the user to see what's going on --
they don't need and I don't want them to see the communication. My plan is
to have it come from a Microsoft Office Add-in (Vb.Net, Office 2007, VSTO)
which I am hoping to write soon.

I trust the communication will in all situations get through any corporate
firewalls at any of the client sites?

I have scoured around the internet to see any Vb.Net examples of code of how
this could be done but haven't found a simple, comprehensive example as yet.

If anyone knows the code needed to say run an INSERT query to push the data
into the www.mywebsite.com SQL Server that would be appreciated.

Client Code: 456418
Amount: $455.98
Stock Number: 945482

tblTransactions

Also, in general terms, what really is meant by the phrase 'Webservice'?

 

[Guest]

1) I am assuming that the code you gave there sits in the
www.mywebsite.com/webservice.asmx file?
Yes.

2) If so, what would the VB.Net code in the Add-in say to call the Sub
in www.mywebsite.com/webservice.asmx to insert the data?

You call it explicitly. i.e. MyProxy.MyInsertFunction(MyParameters)

3) What's to stop someone else from accessing the
www.mywebsite.com/webservice.asmx file?

You need to implement security. WSE/WCF all have frameworks for
authentication and authorization.

4) Can the VB.Net code in the Add-in get a result back to know whether
the INSERT was successful?

Yes.

 

[Jonathan]

You won't (or at least shouldn't) be passing the information to the
Sql Server directly. Instead, you'll need to add a webservice to your
website that will be called. The service could be www.mywebsite.com/webservice.asmx
or whatever you want to name it. If it's in a subdirectory and you are
using the web.config to store your connection string (which you should
be) you'll only need to update the connection string in one place for
both the web site and the web service.

To add a web service to your site, just right click on the project in
the solution explorer and do add --> new item --> web service (or
something like that). Call the service something meaningful, as once
you start to use it you won't be able to change the name easily. A
sample web method for the service would be something like:

/////////////////
'// Typed in message

<WebMethod()> _
Public Sub saveClientTransaction(clientCode As String, amount As
Double, stockNumber As String)
Using conn as new
SqlConnection(ConfigurationManager.ConnectionStrings("ConnString").Connectio
nString)
Using com as SqlCommand = conn.CreateCommand()
conn.Open()

com.CommandType = CommandType.StoredProcedure
com.CommandText = "saveClientTransaction" '// Or whatever
the name of your stored proc is

com.Parameters.Add("@ClientCode", SqlDbType.VarChar).Value
= clientCode
com.Parameters.Add("@amount", SqlDbType.Decimal).Value =
amount
com.Parameters.Add("@stockNumber",
SqlDbType.VarChar).Value = stockNumber

com.ExecuteNonQuery()
End Using
End Using
End Sub
////////////////////

Please also note that it might be a good idea to look into SSL if you
are doing sensitive transactions, the request sent to your service
could easily be intercepted and used maliciously.

Thanks,

Seth Rowe [MVP]

Thanks very much Seth.

Please excuse my ignorance but:

1) I am assuming that the code you gave there sits in the
www.mywebsite.com/webservice.asmx file?

2) If so, what would the VB.Net code in the Add-in say to call the Sub in
www.mywebsite.com/webservice.asmx to insert the data?

3) What's to stop someone else from accessing the
www.mywebsite.com/webservice.asmx file?

4) Can the VB.Net code in the Add-in get a result back to know whether the
INSERT was successful?

TIA

 

[rowe_newsgroups]

Also, in general terms, what really is meant by the phrase 'Webservice'?

I meant to tell you, a WebService is a UI-less object that can be
interacted with through code. The webservice sends and receives
information through SOAP formatted Xml (see wikipedia for a rundown on
SOAP and Xml). Just imaging have a code library that floats around
hyperspace waiting for it to be used, that, in it's simplest form, is
a web service. One great feature is that a web service can be called
by almost any language, be it .NET or PHP or just plain html.

Be warned, that if you don't authenticate and authorize people to use
your service, anyone who discovers it will be able to call it whenever
and however they want. So besides using just SSL to protect the
transmissions, you might want to figure out a way to lock down your
service to prevent people from abusing it. I would highly recommend
you do some heavy reading (or outside contracting) on ASP.NETs ways of
authorizing users.

Thanks,

Seth Rowe [MVP]

 

[Jonathan]

Thanks again.

I meant to tell you, a WebService is a UI-less object that can be
interacted with through code. The webservice sends and receives
information through SOAP formatted Xml (see wikipedia for a rundown on
SOAP and Xml). Just imaging have a code library that floats around
hyperspace waiting for it to be used, that, in it's simplest form, is
a web service. One great feature is that a web service can be called
by almost any language, be it .NET or PHP or just plain html.

Be warned, that if you don't authenticate and authorize people to use
your service, anyone who discovers it will be able to call it whenever
and however they want. So besides using just SSL to protect the
transmissions, you might want to figure out a way to lock down your
service to prevent people from abusing it. I would highly recommend
you do some heavy reading (or outside contracting) on ASP.NETs ways of
authorizing users.

Thanks,

Seth Rowe [MVP]