static void Main(string[] args)
{
string connectionString = "Data Source = (local);Initial Catalog = CleverDb;Integrated Security = True;MultipleActiveResultSets = True";
CleverDbContext db = new CleverDbContext(connectionString);
var obj = new {
name = "Насос",
parentId = 95,
attributes = new {
марка = "Bosh",
мощность = 95.3,
ДатаУстановки = DateTime.Now
}
};
string stringified = Json.Encode(obj);
var clever = CleverObjectService.GetCleverObjectFromJson(stringified);
//insert operation
CleverObject inserted = db.Insert(clever);
Console.WriteLine(inserted.Id);
//get operation
CleverObject found = db.FindById(inserted.Id);
//get subtree operation
string json = db.GetSubTreeForTheNode(166);
Console.WriteLine("done");
Console.ReadKey();
}
public CleverObject Insert(CleverObject co)
{
string queryString = @"
insert into [CleverObjects] (Name, ParentId)
output INSERTED.ID
values (@objectName, @parentId)";
int insertedId;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection)
{
CommandType = CommandType.Text
};
command.Parameters.AddWithValue("@objectName", co.Name);
command.Parameters.AddWithValue("@parentId", (object)co.ParentId ?? DBNull.Value);
try
{
connection.Open();
insertedId = (int)command.ExecuteScalar();
}
finally
{
connection.Close();
}
}
co.Id = insertedId;
InsertAttributes(co);
queryString = @" CreatePathForObject @objId";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection)
{
CommandType = CommandType.Text
};
command.Parameters.AddWithValue("@objId", insertedId);
try
{
connection.Open();
command.ExecuteScalar();
}
finally
{
connection.Close();
}
}
return(co);
}
public CleverObject FindById(int id)
{
CleverObject result = null;
string queryString = "select * from [CleverObjects] where id = @objectId ";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@objectId", id);
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
connection.Open();
dataAdapter.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
{
result = ds.Tables[0].AsEnumerable().Select(dataRow =>
new CleverObject()
{
Id = dataRow.Field <int>("Id"),
Name = dataRow.Field <string>("Name"),
ParentId = dataRow.Field <int?>("ParentId")
}).First <CleverObject>();
}
}
finally
{
connection.Close();
}
}
if (result == null)
{
return(null);
}
return(PopulateAttributes(result));
}
void InsertAttributes(CleverObject co)
{
string queryString = "Declare @DataTable as CleverAttributeType";
foreach (var attribute in co.Attributes)
{
queryString += " insert into @DataTable(Name, StringValue, DateTimeValue, DoubleValue, Type, CleverObjectId) ";
switch (attribute.EnumType)
{
case CleverObjectAttributeTypes.String:
queryString += $@"values('{attribute.Name}', '{attribute.StringValue}', null, null, 'String', {co.Id})";
break;
case CleverObjectAttributeTypes.DateTime:
queryString += $@"values('{attribute.Name}',null, '{attribute.DateTimeValue.Value.ToString("yyyy-MM-dd hh:mm:ss")}', null, 'DateTime', {co.Id})";
break;
case CleverObjectAttributeTypes.Double:
queryString += $@"values('{attribute.Name}',null, null, {attribute.DoubleValue.ToString().Replace(',', '.')}, 'Double', {co.Id})";
break;
default:
break;
}
}
queryString += " EXECUTE InsertCleverObjectAttributes @TableVariable = @DataTable";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
}
}
CleverObject PopulateAttributes(CleverObject co)
{
string queryString = "select * from [CleverObjectAttributes] where CleverObjectId = @objectId";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@objectId", co.Id);
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
connection.Open();
dataAdapter.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
{
co.Attributes = ds.Tables[0].AsEnumerable().Select(dataRow =>
new CleverObjectAttribute(dataRow.Field <string>("Type"))
{
Id = dataRow.Field <int>("Id"),
Name = dataRow.Field <string>("Name"),
StringValue = dataRow.Field <string>("StringValue"),
DoubleValue = dataRow.Field <double?>("DoubleValue"),
DateTimeValue = dataRow.Field <DateTime?>("DateTimeValue"),
CleverObjectId = dataRow.Field <int>("CleverObjectId")
}).ToList();
}
}
finally
{
connection.Close();
}
}
return(co);
}
public CleverObject Insert(dynamic obj)
{
CleverObject result = CleverObjectService.GetCleverObjectFromDynamic(obj);
return(Insert(result));
}
public static CleverObject GetCleverObjectFromDynamic(dynamic decodedObject)
{
CheckDynamicObjectConsistency(decodedObject);
CleverObject result = new CleverObject();
result.Name = decodedObject.name;
result.ParentId = decodedObject.parentId;
foreach (var attribute in decodedObject.attributes)
{
if (attribute.Value.GetType() == typeof(string) || attribute.Value.Type.ToString() == "String")
{
DateTime newDate = new DateTime();
bool parseResult = DateTime.TryParse(attribute.Value.ToString(), out newDate);
if (parseResult)
{
result.Attributes.Add(new CleverObjectAttribute(CleverObjectAttributeTypes.DateTime)
{
Name = attribute.Name,
DateTimeValue = newDate
});
}
else
{
result.Attributes.Add(new CleverObjectAttribute(CleverObjectAttributeTypes.String)
{
Name = attribute.Name,
StringValue = attribute.Value
});
}
}
else
if (attribute.Value.GetType() == typeof(Decimal) || attribute.Value.GetType() == typeof(Int32) || attribute.Value.Type.ToString() == "Float")
{
result.Attributes.Add(new CleverObjectAttribute(CleverObjectAttributeTypes.Double)
{
Name = attribute.Name,
DoubleValue = (double)attribute.Value
});
}
else
if (attribute.Value.GetType() == typeof(DateTime) || attribute.Value.Type.ToString() == "Date")
{
result.Attributes.Add(new CleverObjectAttribute(CleverObjectAttributeTypes.DateTime)
{
Name = attribute.Name,
DateTimeValue = attribute.Value
});
}
else
{
Double parseResult;
Double.TryParse(attribute.Value.ToString(), out parseResult);
result.Attributes.Add(new CleverObjectAttribute(CleverObjectAttributeTypes.Double)
{
Name = attribute.Name,
DoubleValue = parseResult
});
}
}
return(result);
}