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); }