I was wondering if anyone has had success with the Netduino plus and connecting to a SQL server for the purposes of logging data to a server. Normally, I would use the System.Data.SqlClient namespace to do this in a .Net application, but it doesnt look like those classes are available on the Netduino Plus.
Is there any way others have found to directly (or indirectly) communicate with a remote SQL server?
Also as a site note, what would be involved with adding some .net classes to be usable by the Netduino? Is this something that would need to be done by Secret Labs (ie in the Firmware), or could i do something in VS to compile them and load them on the device? I know I might be sounding dumb, but I am not familiar with how all the low level stuff works...
Netduino + SQL client (maybe System.Data.SqlClient)??
Started by Christopher Gilmore, Dec 07 2010 08:00 PM
11 replies to this topic
#1
Christopher Gilmore
-
- Members
-
- 12 posts
Member
- LocationBurlington, MA
Posted 07 December 2010 - 08:00 PM
#2
bill.french
-
- Members
-
- 260 posts
Advanced Member
- LocationPrinceton, NJ
Posted 07 December 2010 - 08:23 PM
I can't imagine sqlclient making it into the firmware...
You could easily log to sql using an asp.net app or a forms app, you could look here for maybe a starting point:
http://forums.netdui...ch__1#entry4637
#3
Quiche31
-
- Members
-
- 87 posts
Advanced Member
- LocationFrance
Posted 07 December 2010 - 09:03 PM
Also as a site note, what would be involved with adding some .net classes to be usable by the Netduino?
I agree wit Bill, it will be a real challenge to port a shrinked version of, say MySql connector (as I don't assume that you have access to the sources of System.Data.SqlClient, and its dependencies) to NETMF within the memory constraints.
I can suggest another method, alternative to Bill's: from the Netduino+ which is the closest to the physical sensors, concentrate on the physical data capture, and then invoke a business web-service via SOAP (merely an http post), which would take care of the business and data tiers. As you well know, these second/third tiers can be implemented on any suitable platform/language whether ASP.Net, Java, Ruby or else. Use Netduino Plus for what it does very well.
My cent
JP
#4
Jeff LaFay
-
- Members
-
- 6 posts
New Member
Posted 07 December 2010 - 09:27 PM
I was wondering if anyone has had success with the Netduino plus and connecting to a SQL server for the purposes of logging data to a server. Normally, I would use the System.Data.SqlClient namespace to do this in a .Net application, but it doesnt look like those classes are available on the Netduino Plus.
Is there any way others have found to directly (or indirectly) communicate with a remote SQL server?
Also as a site note, what would be involved with adding some .net classes to be usable by the Netduino? Is this something that would need to be done by Secret Labs (ie in the Firmware), or could i do something in VS to compile them and load them on the device? I know I might be sounding dumb, but I am not familiar with how all the low level stuff works...
I know your question is SQL server specific but would a SQLite configuration suit your purpose better? I'm not even sure if there's a direct solution for a Netduino/SQLite. It could be favorable because it's SQL without the server, in other words, nice and compact.
#5
Eric Burdo
-
- Members
-
- 130 posts
Advanced Member
Posted 07 December 2010 - 09:49 PM
Looks like GHI has a NETMF implementation for SQLite?
http://www.ghielectr...19dd0f5c0ee.htm
~ Eric D. Burdo ~ http://brick-labs.com/
Today LED's, tomorrow, the world!!! Well, OK, maybe servos.
Today LED's, tomorrow, the world!!! Well, OK, maybe servos.
#6
Chris Walker
-
- Moderators
-
- 7767 posts
Secret Labs Staff
- LocationNew York, NY
Posted 07 December 2010 - 10:02 PM
As Eric mentioned, some of the high-end .NET MF boards can support SQLite and the like...
Netduino is a pretty tiny board, whereas some of the high-end .NET MF boards are similar in performance to a Windows CE machine (where you can also run SQL Server Compact natively).
Chris
#7
Mohan
-
- Members
-
- 3 posts
New Member
Posted 08 December 2010 - 02:51 PM
Hi ,
you can use PHP with parameter's on the URL and a simple web URL Request to the web-server will inserts the data in to MySQL or MSSQL
you just need to write one php file thats enough for inserting data into any server with simple web request
for your reference
connecting to mssql with php
http://www.webcheats...ql_database.php
URL passing
serverlog.php file name
<html>
<head>
<title>Server Log</title>
</head>
<body>
<?php
$url1= $_GET['url1'];
$url2= $_GET['url2'];
$url3= $_GET['url3'];
mysql_connect("servername","username","password") or
die ('I cannot connect to database because : '. mysql_error());
mysql_select_db("yourdatabase");
$query="insert into tablename values('.$url1', '".$url2"', '".$url3"');";
mysql_query($query) or die("ERROR ".mysql_error());
echo "Database Updated";
echo "SUCCESS";
?>
</body>
</html>
then you can call the webrequest from netduinoPlus using
http://servername/se...url2=xx,url3=xx
where xx is your dynamic data from ADC or I/O
#8
Chris Walker
-
- Moderators
-
- 7767 posts
Secret Labs Staff
- LocationNew York, NY
Posted 08 December 2010 - 05:23 PM
Clever, Mohan. Thank you, and welcome to the Netduino community!
Chris
#9
Valkyrie-MT
-
- Members
-
- 315 posts
Advanced Member
- LocationIndiana, USA
Posted 09 December 2010 - 01:24 AM
I was wondering if anyone has had success with the Netduino plus and connecting to a SQL server for the purposes of logging data to a server...
Something you should consider is to create a WCF Data Service to expose/wrap your database (SQL Server, Oracle, etc.). Then you can query the database with simple http GETs according to the OData standard. It would allow you to do all the standard Select, Insert, Update, Delete operations. The only thing is, you will have to construct the URLs yourself, which is not so hard. So basically, instead of SQL statements, you'll have to translate to the RESTful OData format. But, if you want to do simple logging, I would probably just create a WCF REST service with a single method to write the log entry with a simple http GET. If you use WCF Data Services, the insert operation would probably need to use the PUT verb or something, which isn't too tough either... I suppose it depends on how much flexibility you want. If you just want to do this one thing to insert logging entries, I would do the WCF REST service. But, if you want to have the flexibility to do all the other database operations in the future, I would go for the WCF Data Service.
-Valkyrie-MT
#10
bill.french
-
- Members
-
- 260 posts
Advanced Member
- LocationPrinceton, NJ
Posted 09 December 2010 - 02:34 AM
$query="insert into tablename values('.$url1', '".$url2"', '".$url3"');";
I can't help but mention the risk of sql injection attacks -- unless i'm misunderstanding how php works, this appears ripe for that sort of exploit. Not that there's much risk when messing around with a netduino, i think establishing good habits regarding injection attacks early on before bad habits take hold are really important.
Here's some info in regards to php:
http://stackoverflow...njection-in-php
... and my favorite sql injection attack:
#11
Chris Walker
-
- Moderators
-
- 7767 posts
Secret Labs Staff
- LocationNew York, NY
Posted 09 December 2010 - 03:52 AM
... and my favorite sql injection attack:
"Little Bobby Tables, we call him"
#12
Christopher Gilmore
-
- Members
-
- 12 posts
Member
- LocationBurlington, MA
Posted 16 December 2010 - 03:35 AM
I took Quiche31's advice and wrote some ASP.NET web services that my computer uses to communicate with the netduino plus. Here is some code...
On the netduino side of things....
//Socket to connect to webservice
Socket socWebService = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
//IP of where to send the request
IPEndPoint Desination = new IPEndPoint(IPAddress.Parse("192.168.1.3"), 8081);
//Bind to new socket
socWebService.Bind(new IPEndPoint(IPAddress.Any, 80));
//Connect to destination
socWebService.Connect(Desination);
if (socWebService != null)
{
string request = "Source=" + Source + "&EventTypeID=" + EventTypeID.ToString();
string header = "POST /DoorBuzzerSvc.asmx/LogDoorEvent HTTP/1.1\r\nHost: localhost\r\nContent-Type: application/x-www-form-urlencoded\r\nContent-Length: " + request.Length.ToString() + "\r\n\r\n";
socWebService.Send(Encoding.UTF8.GetBytes(header), header.Length, SocketFlags.None);
socWebService.Send(Encoding.UTF8.GetBytes(request), request.Length, SocketFlags.None);
//Check to see if we got data back...
if (socWebService.Available > 0)
{
//Process response
byte[] buffer = new byte[socWebService.Available];
int readByteCount = socWebService.Receive(buffer, socWebService.Available, SocketFlags.None);
string content = new string(Encoding.UTF8.GetChars(buffer));
//View Response
Debug.Print(content);
}
}
socWebService.Close();
And on the WebService Side of things..... in an asmx.vb file
<WebMethod()> _
Public Function LogDoorEvent(ByVal Source As String, ByVal EventTypeID As Integer) As Boolean
'Log the ring in a DB
Dim DBConnection As New SqlConnection("data source=.;Integrated Security=SSPI;Initial Catalog=DoorBuzzer;")
DBConnection.Open()
Dim SQLText As String = "INSERT INTO Events (Source, EventTypeID) Values (@Source, @EventTypeID)"
Dim SQLCmd As New SqlCommand(SQLText, DBConnection)
SQLCmd.Parameters.AddWithValue("@Source", Source)
SQLCmd.Parameters.AddWithValue("@EventTypeID", EventTypeID)
SQLCmd.ExecuteNonQuery()
SQLCmd.Dispose()
DBConnection.Close()
DBConnection.Dispose()
Return True
End Function
- Mark Anderson likes this
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
