|
public CreateProcedure ( string sqlStr, |
||
| sqlStr | string | A create procedure script |
| externalTransaction | A SqlTransaction instance under which the create procedure will occur. | |
| return | bool | |
// 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();
}
}