How can i read and write to Virtuoso WebDAV in ASP.NET C# code?
The Microsoft .NET Framework Data Provider for ODBC can be used to connect to Virtuoso via its ODBC Driver and the WebDAV API calls used for reading, writing, searching etc the Virtuoso DAV repository.
Hi @kidehen @hwilliams, I’m unable to connect virtuoso via ODBC in my ASP.NET Core 5.0 project
is any wrong with my connectionString or code snippet?
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Linq;
using System.Web;
using OpenLink.Data.Virtuoso;
namespace VirtuosoService.Services
{
public class DbConnection : IDisposable
{
private VirtuosoConnection connection;
//private OdbcConnection connection;
//private String connectionString = "Driver={OpenLink Virtuoso};Server=127.0.0.1:1111;Database=DB;Uid=dba;Pwd=dba;";
public DbConnection()
{
try
{
connection = new VirtuosoConnection("HOST=127.0.0.1:1111;UID=dba;PWD=dba;Charset=UTF-8");
//connection = new OdbcConnection(connectionString);
connection.Open();
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
}
public void OpenConnection()
{
if (connection.State == ConnectionState.Closed)
connection.Open();
}
public void CloseConnection()
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
public IDbCommand GetCommand()
{
IDbCommand cmd = connection.CreateCommand();
return cmd;
}
public void Dispose()
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
}
public IEnumerable<String> TestVT()
{
using (VirtuosoConnection connection = new ())
{
try
{
OdbcCommand oCommand = connection.GetCommand("sparql insert into graph <ado.net> { <a> <P01> \"131\"^^<http://www.w3.org/2001/XMLSchema#short> }");
connection.OpenConnection();
OdbcDataReader oReader = oCommand.ExecuteReader();
while (oReader.Read())
{
Console.WriteLine(oReader[0] + " " + oReader[1]);
}
}
catch(Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
}
return null;
}
What OS are you running ASP.NET Core 5.0 on ie Windows, macOS or Linux ?
How do you build and run the sample code provided above in an ASP.NET Core 5.0 project, as I don’t see how they interact, although I am not a .Net programmer ? Saying you are unable to connect without details steps on what is being done to get to that point is of little use.
Installing ASP.NET Core 5.0 on Windows 64bit and putting together some sample code for the System.Data.Odbc assembly I found and amending connectstring for Virtuoso’s ODBC Driver I could make a connection to Virtuoso:
C:\dotnet\MyApp>type Program.cs
using System;
using System.Data.Odbc;
namespace MyApp
{
class Program
{
static void Main(string[] args)
{
string connetionString = null;
OdbcConnection cnn ;
connetionString = "Driver={OpenLink Virtuoso};HOST=127.0.0.1:1111;Database=DB;Uid=dba;Pwd=dba;";
cnn = new OdbcConnection(connetionString);
try
{
cnn.Open();
Console.WriteLine ("Connection Open ! ");
cnn.Close();
}
catch (Exception ex)
{
Console.WriteLine("Can not open connection ! ");
}
}
}
}
C:\dotnet\MyApp>dotnet run
Connection Open !
C:\dotnet\MyApp>
Although note that I had to install the Microsoft.Windows.Compatibility Pack. => NuGet Gallery | Microsoft.Windows.Compatibility 6.0.0 as the System.Data.Odbc assembly is not part of ASP.NET Core 5.0, as indicated on StackOverFlow.
-
I’m the ASP.NET Core app on Windows 64bit
-
I’m unable to connect because ASP.NET Core app is returning “Exception thrown: ‘System.Data.Odbc.OdbcException’ in System.Data.Odbc.dll”.
-
Even after executing your code, it still returns “Exception thrown: ‘System.Data.Odbc.OdbcException’ in System.Data.Odbc.dll”
I assume you have install the Microsoft.Windows.Compatibility Pack. => NuGet Gallery | Microsoft.Windows.Compatibility 6.0.0 package containing the System.Data.Odbc assembly ?
What is the actual output of running the running my sample code on your machine, as assume it returns more than just Exception thrown: ‘System.Data.Odbc.OdbcException’ in System.Data.Odbc.dll ?
I installed the `Microsoft.Windows.Compatibility Package and but got this exception message “Exception message: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”.
Here is the screenshot of my Data source Administrator
You still have not provided the output of running the test program I provided as requested previously ?
Anyway, your screenshots show the Virtuoso (Open Source) ODBC Driver is being used for which you have a DSN (Data Source Name) called OpenLink Virtuoso, which is a bad choice for a DSN name, as the Virtuoso commercial edition ODBC Driver is called OpenLink Virtuoso.
And in the same code you provided previously the connect string being passed is:
Driver={OpenLink Virtuoso};Server=127.0.0.1:1111;Database=DB;Uid=dba;Pwd=dba;
ie in you case you are passing the ODBC DSN rather then ODBC Driver name to the Driver attribute of the connect string, which in your case should be Virtuoso (Open Source) ie
Driver={Virtuoso (Open Source)};Host=127.0.0.1:1111;Database=DB;Uid=dba;Pwd=dba;
Thus amend your connectstring accordingly …
It worked…thanks for your support
Hello @hwilliams.
I have been following this post carefully and after debugging, I was able to get a “Connection Open” response from your earlier example.
However, using the code below, I introduced a WebDAV command which is meant to create a folder called “testbosa” based on what was mentioned in Virtuoso Documentation here: DAV add & update functions (openlinksw.com). This usage is found in the first line at Example 24.76.
string finalStatus = "Idle";
string connectionString = "Driver={Virtuoso (Open Source)};Host=127.0.0.1:1111;Database=DB;Uid=dba;Pwd=dba;";
using (OdbcConnection cnn =
new OdbcConnection(connectionString))
{
string odbcQuery = "select DB.DBA.DAV_COL_CREATE ('/DAV/testbosa/','110100000R', 'dav','dav','dav','dav');";
try
{
cnn.Open();
finalStatus = "Connection Open !";
using (OdbcCommand command = new OdbcCommand(odbcQuery, cnn)) //pass SQL query created above and connection
{
command.ExecuteNonQuery(); //execute the Query
finalStatus = "Query Executed.";
}
cnn.Close();
}
catch (Exception ex)
{
finalStatus = "Cannot open connection: " + ex;
}
Console.WriteLine(finalStatus);
}
After implementing this code, my variable, finalStatus reads the following error:
Cannot open connection: System.Data.Odbc.OdbcException (0x80131937): ERROR [37000] [OpenLink][Virtuoso ODBC Driver][Virtuoso Server]SQ074: Line 1: syntax error
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at Program.<>c.<<<Main>$>b__0_0>d.MoveNext() in C:\Users\*****\Program.cs:line 34
(The path to my Program.cs file was intentionally hidden from the public)
Please can you let me know what I am doing wrong here to get this syntax error and how I can fix this? Thank you.
Regards.
It was a strange issue. I removed the “;” at the end of the query string and it eventually worked.
Here is the correction:
string odbcQuery = "select DB.DBA.DAV_COL_CREATE ('/DAV/testbosa/','110100000R', 'dav','dav','dav','dav')";
Thanks a lot for the help!
That is correct as the “;” is a termination character for the isql command line tool but not required for general SQL query execution via ODBC/JDBC/ADO.Net and other such SQL APIs …
@hwilliams Which query can be used to retrieve a single or all file from WebDav directory?
See response in Nesting ISQL commands in VSP files - #7 by kidehen indicating the Virtuoso ODS-Briefcase should be used for WebDAV interactions over HTTP.
Via SQL a query like select res_full_path as PATH from ws..sys_dav_res where res_full_path like '/DAV/home/dav/%' will return a list of files in a WebDAV folder.
Hi @hwilliams Which query can be used to upload png or jpg images to WebDav directory?
The DAV_RES_UPLOAD function can be used to add content to a WebDAV directory as detailed in the DAV content add/update/delete functions document.
@hwilliams when i execute this query "SQL> select DB.DBA.DAV_RES_UPLOAD (’/DAV/user/A/A.txt’,‘this is a test’,‘text/plain’,‘110100000R’,‘dav’,‘dav’,‘dav’,‘dav’);
" to upload text file it works
but when i try to upload a non-text file like pdf,png,jpg it does’t work…how do i pass pdf,png,jpg file to the query?
Direct Files ( text or binary) need to be placed is an allowed location relative the the Virtuoso database directory and converted from a file_to_string in the called to the DAV_RES_UPLOAD function and with content type of application/ octet-stream for binary files. For example:
SQL> select DB.DBA.DAV_RES_UPLOAD ('/DAV/user/A/mt555.ttl', file_to_string('./mt555.ttl'),'application/
octet-stream','110100000R','dav','dav','dav','dav');
DAV_RES_UPLOAD
LONG VARCHAR
_______________________________________________________________________________
1212
1 Rows. -- 8 msec.
SQL>
SQL> select res_full_path, length(res_content), md5(cast(res_content as varbinary)) from ws..sys_dav_res where res_full_path like '/DAV/user/A/%.ttll
';
RES_FULL_PATH length md5
VARCHAR INTEGER NOT NULL VARCHAR
_______________________________________________________________________________
/DAV/user/A/mt555.ttl 173 0ef554c505470584b9c53678721f8b7a
1 Rows. -- 0 msec.
SQL>