| public ExecuteNonQuery ( string queryString ) : int | ||
| queryString | string | |
| return | int | |
// This is a tutorial for GraphView user about how to create SP and execute SP
// Using SP is much more faster than call GraphViewConnection.executeNonQuery()
// We highly recommend you create SP for the queries
// Please notice that following script will drop all the table in database when it finished
public static void run()
{
GraphViewConnection con = new GraphViewConnection(TutorialConfiguration.getConnectionString());
con.Open();
con.ClearData();
try
{
#region Create a schema for node
con.CreateNodeTable(@"CREATE TABLE [Node] (
[ColumnRole:""NodeId""] id INT,
[ColumnRole:""Edge"",Reference:""Node""] Edges VARBINARY(max) )");
System.Console.WriteLine("Create table successed!");
#endregion
#region Create nodes
con.ExecuteNonQuery("INSERT INTO [Node](id) VALUES(1)");
con.ExecuteNonQuery("INSERT INTO [Node](id) VALUES(2)");
con.ExecuteNonQuery("INSERT INTO [Node](id) VALUES(3)");
#endregion
#region Create SP
con.CreateProcedure(@"CREATE PROCEDURE AddEdge
@st INT,
@ed INT
AS
BEGIN
INSERT EDGE INTO Node.Edges
SELECT s,t FROM
Node s , Node t WHERE s.id = @st AND t.id= @ed ;
END");
con.CreateProcedure(@"CREATE PROCEDURE SelectNeighbors
@id INT
AS
BEGIN
SELECT y.* FROM Node x, Node y
MATCH x-[Edges]->y
WHERE x.id = @id
END");
#endregion
#region Using SP to insert edges
using (var command = con.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "AddEdge";
command.Parameters.AddWithValue("@st", 1);
command.Parameters.AddWithValue("@ed", 2);
command.ExecuteNonQuery();
command.Parameters.Clear();
command.Parameters.AddWithValue("@st", 1);
command.Parameters.AddWithValue("@ed", 3);
command.ExecuteNonQuery();
}
#endregion
#region Using SP to query
using (var command = con.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "SelectNeighbors";
command.Parameters.AddWithValue("@id", 1);
var res = command.ExecuteReader();
try
{
while (res.Read())
{
System.Console.WriteLine(res["id"].ToString());
}
}
finally
{
if (res != null)
res.Close();
}
}
#endregion
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
}
finally // close the connection and drop table
{
con.ClearGraphDatabase();
con.Close();
}
}
// This is a tutorial for GraphView user showing how to insert or delete nodes and edges
// Please notice that following script will drop all the table in databse when it finished
public static void run()
{
GraphViewConnection con = new GraphViewConnection( TutorialConfiguration.getConnectionString());
con.Open();
con.ClearData();
try
{
#region Create a schema for node
con.CreateNodeTable(@"CREATE TABLE [People] (
[ColumnRole:""NodeId""] id INT,
[ColumnRole:""Property""] name varchar(20),
[ColumnRole:""Edge"",Reference:""People""] Knows VARBINARY(max) )");
System.Console.WriteLine("Create table successed!");
#endregion
#region Create nodes
con.ExecuteNonQuery("INSERT INTO [People](id,name) VALUES(1,'Alice')");
con.ExecuteNonQuery("INSERT INTO [People](id,name) VALUES(2,'Bob')");
con.ExecuteNonQuery("INSERT INTO [People](id,name) VALUES(3,'Caven')");
con.ExecuteNonQuery("INSERT INTO [People](id,name) VALUES(4,'David')");
// You must insert with ColumnRole 'NodeId' if you have
// i.e. Following insertion is invalid
// con.ExecuteNonQuery("INSERT INTO [People](name) VALUES('Eva')");
#endregion
#region Insert edges
// Alice knows Bob
con.ExecuteNonQuery(@"INSERT EDGE INTO People.Knows
SELECT x,y FROM People x , People y
WHERE x.name = 'Alice' AND y.name = 'Bob' ");
// Bob knows Caven
con.ExecuteNonQuery(@"INSERT EDGE INTO People.Knows
SELECT x,y FROM People x , People y
WHERE x.name = 'Bob' AND y.name = 'caven' ");
// Bob knows David
con.ExecuteNonQuery(@"INSERT EDGE INTO People.Knows
SELECT x,y FROM People x , People y
WHERE x.name = 'Bob' AND y.name = 'David' ");
#endregion
#region Query 1: find out knowers of knowers
System.Console.WriteLine("\nQuery 1:");
var res = con.ExecuteReader(@"SELECT C.* FROM People A, People B, People C
MATCH A-[Knows]->B-[Knows]->C
WHERE A.name = 'Alice' ");
try
{
while (res.Read())
{
System.Console.WriteLine("Name: " + res["name"].ToString());
}
}
finally
{
if (res != null)
res.Close();
}
#endregion
#region Delete edges
con.ExecuteNonQuery(@"DELETE EDGE [x]-[Knows]->[y]
FROM People as x, People as y
WHERE y.name='Bob' or y.name = 'Caven' ");
#endregion
#region Query 2: find out all edges
System.Console.WriteLine("\nQuery 2:");
res = con.ExecuteReader(@"SELECT x.name as name1, y.name as name2 FROM People x, People y
MATCH x-[Knows]->y ");
try
{
while (res.Read())
{
System.Console.WriteLine(res["name1"].ToString() + " knows " + res["name2"].ToString());
}
}
finally
{
if (res != null)
res.Close();
}
#endregion
#region Delete node
con.ExecuteNonQuery("DELETE NODE FROM People WHERE People.name <> 'Bob' and People.name <> 'David' "); // Try to delete all nodes.
// Notice that you can not delete a node with edge linked to it.
// Above query will not delete Bob and David since there's an edge from Bob to David
#endregion
#region Query 3: find out remaining nodes
System.Console.WriteLine("\nQuery 3:");
res = con.ExecuteReader("SELECT * FROM [People] ");
try
{
while (res.Read())
{
System.Console.WriteLine(res["name"].ToString());
}
}
finally
{
if (res != null)
res.Close();
}
#endregion
#region Delete all edges and nodes
con.ExecuteNonQuery(@"DELETE EDGE [x]-[Knows]->[y]
FROM People as x, People as y ");
con.ExecuteNonQuery("DELETE NODE FROM People ");
#endregion
#region Query 4: find out remaining nodes
System.Console.WriteLine("\nQuery 4:");
res = con.ExecuteReader("SELECT * FROM [People] ");
try
{
while (res.Read())
{
System.Console.WriteLine(res["name"].ToString());
}
System.Console.WriteLine("Result should be empty");
}
finally
{
if (res != null)
res.Close();
}
#endregion
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
}
finally // close the connection and drop table
{
// Drop table and clear all the data
con.ClearGraphDatabase();
con.Close();
}
}