public int CreateCategory(Category categoryToCreate) { int categoryId = -1; using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { if (categoryToCreate.Id == -1) { String sqlQuery = "SELECT MAX(ID) FROM CATEGORIES"; OleDbDataReader queryData = dataContext.Select(sqlQuery); if (queryData.HasRows && queryData.Read()) { int idxColumn = queryData.GetOrdinal("ID"); categoryToCreate.Id = queryData.GetInt32(idxColumn) + 1; queryData.Close(); sqlQuery = "INSERT INTO CATEGORIES(ID,LABEL) VALUES(" + categoryToCreate.Id + ",'" + categoryToCreate.Name + "')"; dataContext.Execute(sqlQuery); } } else { //UPDATE String sqlQuery = "UPDATE CATEGORIES SET " + Environment.NewLine; sqlQuery += "LABEL='" + categoryToCreate.Name + "'" + Environment.NewLine; sqlQuery += "WHERE ID=" + categoryToCreate.Id; dataContext.Execute(sqlQuery); } if (categoryToCreate.TemplateFields != null && categoryToCreate.TemplateFields.Length > 0) { Category currentDbCategory = this.GetCategory(categoryToCreate.Id); foreach (TemplateFieldData tfData in categoryToCreate.TemplateFields) { if (tfData.Id == -1) { if (!this.TemplateFieldExists(tfData.FieldName, tfData.FieldType)) { this.AddTemplateField(tfData); } } else { //Update template field this.UpdateTemplateField(tfData.Id, tfData.FieldName, tfData.FieldType); } } } } return(categoryId); }
private int _AddTemplateFieldToCategory(int IdTemplateField, int IdCategory) { int result = -1; using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { String sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD_CATEGORY"; OleDbDataReader resultData = dataContext.Select(sqlQuery); if (resultData.HasRows && resultData.Read()) { int idxColumn = resultData.GetOrdinal("NEW_ID"); object value = resultData.GetValue(idxColumn); if (typeof(DBNull) == value.GetType()) { result = 1; } else { result = (int)value;//resultData.GetInt32(idxColumn); } } else { result = 1; } sqlQuery = "INSERT INTO TEMPLATE_FIELD_CATEGORY(ID,ID_CATEGORY,ID_TEMPLATE_FIELD) VALUES(" + result + "," + IdCategory + "," + IdTemplateField + ")"; dataContext.Execute(sqlQuery); resultData.Close(); } return(result); }
private int _AddTemplateField(TemplateFieldData FieldData) { int result = -1; using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { String sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD"; OleDbDataReader resultData = dataContext.Select(sqlQuery); if (resultData.HasRows && resultData.Read()) { int idxColumn = resultData.GetOrdinal("NEW_ID"); object value = resultData.GetValue(idxColumn); if (typeof(DBNull) == value.GetType()) { result = 1; } else { result = (int)value;//resultData.GetInt32(idxColumn); } } else { result = 1; } resultData.Close(); sqlQuery = "INSERT INTO TEMPLATE_FIELD(ID,LABEL,ID_FIELD_TYPE) VALUES(" + result + ",'" + FieldData.FieldName + "'," + (FieldData.FieldType == TemplateFieldType.Text ? "0" : "1") + ")"; dataContext.Execute(sqlQuery); } return(result); }
public bool SaveVideo(string IdVideo, SupportData Data) { using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { if (Data.IsExist) { //Purge TEMPLATE_FIELD String sqlQuery = "DELETE FROM TEMPLATE_FIELD_VIDEO_VALUES WHERE ID_VIDEO ='" + Data.UIdSupport + "'"; dataContext.Execute(sqlQuery); //UPDATE sqlQuery = "UPDATE Videos SET " + Environment.NewLine; sqlQuery += "ID='" + Data.UIdSupport + "'," + Environment.NewLine; sqlQuery += "EXTENSION='" + Data.Extension + "'," + Environment.NewLine; sqlQuery += "TC_START=" + Data.TcStart.ToString() + "," + Environment.NewLine; sqlQuery += "EOM=" + Data.Eom.ToString() + "," + Environment.NewLine; sqlQuery += "DURATION=" + Data.Duration.ToString() + "," + Environment.NewLine; sqlQuery += "WIDTH=" + Data.Width.ToString() + "," + Environment.NewLine; sqlQuery += "HEIGHT=" + Data.Height.ToString() + "," + Environment.NewLine; sqlQuery += "FRAMERATE=" + (int)Data.FrameRate + "," + Environment.NewLine; //sqlQuery += "AUDIO_COUNT=" + Data.AudioCount.ToString() + Environment.NewLine; sqlQuery += "FILE_SIZE=" + Data.FileSize.ToString() + "," + Environment.NewLine; sqlQuery += "FILE_NAME='" + Data.FileName + "'," + Environment.NewLine; //sqlQuery += "STATE=" + Data.State + Environment.NewLine; //sqlQuery += "SUPPORT_ID=" + Environment.NewLine; sqlQuery += "TITLE='" + Data.Title + "'," + Environment.NewLine; sqlQuery += "ID_CATEGORY=" + Data.IdCategory.ToString() + Environment.NewLine; sqlQuery += " WHERE ID='" + IdVideo + "'"; dataContext.Execute(sqlQuery); } else { //INSERT CreateVideo(Data); } //INSERT NEW TEMPLATE FIELD VALUE foreach (TemplateFieldValueData TFData in Data.TemplateFields) { AddTemplateFieldValueForVideo(Data.UIdSupport, Data.IdCategory, TFData); } } return(true); }
public void UpdateTemplateField(int IdTemplateField, string newName, TemplateFieldType newType) { using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { //UPDATE String sqlQuery = "UPDATE TEMPLATE_FIELD SET " + Environment.NewLine; sqlQuery += "LABEL='" + newName + "'" + Environment.NewLine; sqlQuery += "ID_FIELD_TYPE=" + newType + Environment.NewLine; sqlQuery += "WHERE ID='" + IdTemplateField + "'"; dataContext.Execute(sqlQuery); } }
private void DeleteTemplateField(long TemplateFieldId) { using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { String sqlQuery = "SELECT ID FROM TEMPLATE_FIELD_CATEGORY WHERE ID_TEMPLATE_FIELD = " + TemplateFieldId; OleDbDataReader resultData = dataContext.Select(sqlQuery); if (resultData.HasRows) { while (resultData.Read()) { int idxColumn = resultData.GetOrdinal("ID"); long IdTFC = resultData.GetInt32(idxColumn); sqlQuery = "DELETE FROM TEMPLATE_FIELD_VIDEO_VALUES WHERE ID_TEMPLATE_FIELD_CATEGORY = " + IdTFC; dataContext.Execute(sqlQuery); } sqlQuery = "DELETE FROM TEMPLATE_FIELD_CATEGORY WHERE ID_TEMPLATE_FIELD = " + TemplateFieldId; dataContext.Execute(sqlQuery); sqlQuery = "DELETE FROM TEMPLATE_FIELD WHERE ID = " + TemplateFieldId; dataContext.Execute(sqlQuery); } } }
public SupportData CreateVideo(SupportData Data) { SupportData result = Data; using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { String sqlQuery = "INSERT INTO Videos(ID,EXTENSION,TC_START,EOM,DURATION,WIDTH,HEIGHT,FRAMERATE,AUDIO_COUNT,FILE_SIZE,FILE_NAME,STATE,SUPPORT_ID,ID_CATEGORY,TITLE)" + Environment.NewLine; sqlQuery += "VALUES('" + Data.UIdSupport + "','" + Data.Extension + "'," + Data.TcStart + "," + Data.Eom + "," + Data.Duration + "," + Data.Width + "," + Data.Height + "," + (int)Data.FrameRate + "," + 0 + ",'" + Data.FileSize + "','" + Data.FileName + "'," + 0 + ",'0'," + Data.IdCategory + ",'" + Data.Title + "')"; dataContext.Execute(sqlQuery); result = GetVideoInfo(Data.UIdSupport); } return(result); }
public bool DeleteVideo(string IdVideo, string Volume) { bool result = false; using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { SupportData data = GetVideoInfo(IdVideo); dataContext.Execute("DELETE FROM Videos WHERE ID ='" + IdVideo + "'"); System.IO.File.Delete(System.IO.Path.Combine(Volume, data.FileName + data.Extension)); result = true; } return(result); }
private void AddTemplateFieldValueForVideo(string IdVideo, int IdCategory, TemplateFieldValueData ValueData) { //Test if Field Exist for this category int idFieldCategory = ValueData.FieldDefinition.IdFieldCategory; if (idFieldCategory == -1) { int idTemplateField = ValueData.FieldDefinition.Id; if (idTemplateField == -1) { idTemplateField = _AddTemplateField(ValueData.FieldDefinition); } if (idFieldCategory == -1) { idFieldCategory = _AddTemplateFieldToCategory(idTemplateField, IdCategory); } } using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { String sqlQuery = ""; if (ValueData.IdValue == -1) { sqlQuery = "SELECT MAX(ID) + 1 AS NEW_ID FROM TEMPLATE_FIELD_VIDEO_VALUES"; OleDbDataReader resultData = dataContext.Select(sqlQuery); int newId = 1; if (resultData.HasRows && resultData.Read() == true) { int idxColumn = resultData.GetOrdinal("NEW_ID"); object value = resultData.GetValue(idxColumn); if (typeof(DBNull) == value.GetType()) { newId = 1; } else { newId = (int)value;//resultData.GetInt32(idxColumn); } } sqlQuery = "INSERT INTO TEMPLATE_FIELD_VIDEO_VALUES(ID,FIELD_VALUE,ID_VIDEO,ID_TEMPLATE_FIELD_CATEGORY) VALUES(" + newId + ",'" + ValueData.Value + "','" + IdVideo + "'," + idFieldCategory + ")"; } else { sqlQuery = "UPDATE TEMPLATE_FIELD_VIDEO_VALUES SET " + Environment.NewLine; sqlQuery += "FIELD_VALUE='" + ValueData.Value + "'" + Environment.NewLine; sqlQuery += "WHERE ID_VIDEO ='" + IdVideo + "'" + Environment.NewLine; } dataContext.Execute(sqlQuery); } }
public void CheckSystem() { try { using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer")) { // CHECK IF DATABASE IS OK String sqlQuery = "SELECT COUNT(Column_Name) as AS_CATEGORY FROM sys.columns WHERE name='Video' AND Column_Name = 'ID_CATEGORY'"; if (!dataContext.CheckSchema("ID_CATEGORY", "Videos")) { //CATEGORIES sqlQuery = "CREATE TABLE CATEGORIES(ID Integer CONSTRAINT PK_CATEGORY PRIMARY KEY,LABEL Text )"; dataContext.Execute(sqlQuery); sqlQuery = "INSERT INTO CATEGORIES(ID,LABEL) VALUES(0,'Default')"; dataContext.InsertData(sqlQuery); sqlQuery = "ALTER TABLE Videos ADD COLUMN ID_CATEGORY Integer default 0"; dataContext.Execute(sqlQuery); sqlQuery = "ALTER TABLE Videos ADD CONSTRAINT FK_CATEGORY FOREIGN KEY(ID_CATEGORY) REFERENCES CATEGORIES(ID)"; dataContext.Execute(sqlQuery); //TEMPLATE FIELDS sqlQuery = "CREATE TABLE TEMPLATE_FIELD(ID Integer CONSTRAINT PK_TEMPLATE_FIELD PRIMARY KEY, LABEL Text, ID_FIELD_TYPE Integer)"; dataContext.Execute(sqlQuery); sqlQuery = "CREATE TABLE TEMPLATE_FIELD_TYPE(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_TYPE PRIMARY KEY, LABEL Text)"; dataContext.Execute(sqlQuery); sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(0,'Texte')"; dataContext.InsertData(sqlQuery); sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(1,'Picture')"; dataContext.InsertData(sqlQuery); sqlQuery = "INSERT INTO TEMPLATE_FIELD_TYPE(ID,LABEL) VALUES(2,'Video')"; dataContext.InsertData(sqlQuery); sqlQuery = "ALTER TABLE TEMPLATE_FIELD ADD CONSTRAINT FK_TEMPLATE_FIELD_TYPE FOREIGN KEY(ID_FIELD_TYPE) REFERENCES TEMPLATE_FIELD_TYPE(ID)"; dataContext.Execute(sqlQuery); sqlQuery = "CREATE TABLE TEMPLATE_FIELD_CATEGORY(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_CATEGORY PRIMARY KEY,ID_CATEGORY Integer,ID_TEMPLATE_FIELD Integer)"; dataContext.Execute(sqlQuery); sqlQuery = "ALTER TABLE TEMPLATE_FIELD_CATEGORY ADD CONSTRAINT FK_TEMPLATE_FIELD_CATEGORY_CATEGORY FOREIGN KEY(ID_CATEGORY) REFERENCES CATEGORIES(ID)"; dataContext.Execute(sqlQuery); sqlQuery = "ALTER TABLE TEMPLATE_FIELD_CATEGORY ADD CONSTRAINT FK_TEMPLATE_FIELD_TEMPLATE_FIELD FOREIGN KEY(ID_TEMPLATE_FIELD) REFERENCES TEMPLATE_FIELD(ID)"; dataContext.Execute(sqlQuery); //TEMPLATE FIELD VALUES sqlQuery = "CREATE TABLE TEMPLATE_FIELD_VIDEO_VALUES(ID Integer CONSTRAINT PK_TEMPLATE_FIELD_VIDEO_VALUES PRIMARY KEY, FIELD_VALUE Text, ID_VIDEO Text, ID_TEMPLATE_FIELD_CATEGORY Integer)"; dataContext.Execute(sqlQuery); sqlQuery = "ALTER TABLE TEMPLATE_FIELD_VIDEO_VALUES ADD CONSTRAINT FK_TEMPLATE_FIELD_VIDEO_VALUES_VIDEO FOREIGN KEY(ID_VIDEO) REFERENCES Videos(ID)"; dataContext.Execute(sqlQuery); sqlQuery = "ALTER TABLE TEMPLATE_FIELD_VIDEO_VALUES ADD CONSTRAINT FK_TFVV_TFC FOREIGN KEY(ID_TEMPLATE_FIELD_CATEGORY) REFERENCES TEMPLATE_FIELD_CATEGORY(ID)"; dataContext.Execute(sqlQuery); } if (!dataContext.CheckSchema("TITLE", "Videos")) { sqlQuery = "ALTER TABLE Videos ADD COLUMN TITLE Text"; dataContext.Execute(sqlQuery); } } } catch (Exception ex) { } }