/// <summary> /// Updates or inserts a row into the table in the data source. /// </summary> /// <param name="row">The row to be inserted.</param> /// <param name="editIndex">The value of the primary key(s) (the column identifier).</param> /// <param name="isInsert">Indicates whether the operation is update or insert.</param> /// <returns>The (new) values for the primary key(s).</returns> internal static bool InsertUpdateRow(ref string editIndex, Row row, bool isInsert) { #region Error and permissions checks if (Grid.GotHttpContext && System.IO.File.Exists(String.Format("{0}\\noupdate.webgrid", HttpContext.Current.Server.MapPath("."))) && row.m_Table.m_Grid.Tag["allowupdate"] == null) { string content = System.IO.File.ReadAllText(String.Format("{0}\\noupdate.webgrid", HttpContext.Current.Server.MapPath("."))); row.m_Table.m_Grid.SystemMessage.Add( String.IsNullOrEmpty(content) ? "The capability to insert, update, and delete has been disabled." : content); return false; } if (row.Columns.Primarykeys.Count == 0) { row.m_Table.m_Grid.SystemMessage.Add( "Your data source is missing primary key(s). This is needed to identify the row you want to update, insert or delete."); return false; } #endregion bool identity = false; if (row.m_Table == row.m_Table.m_Grid.MasterTable) row.m_Table.m_Grid.BeforeValidateEvent(ref row); ValidateColumns(row); BeforeUpdateInsertEventArgs columnArgs = new BeforeUpdateInsertEventArgs {DataSourceId = row.m_Table.DataSourceId}; if (isInsert) { columnArgs.m_Insert = true; columnArgs.m_EditIndex = null; } else { columnArgs.m_Update = true; columnArgs.m_EditIndex = editIndex; } for (int i = 0; i < row.Columns.Count; i++) { // row.Columns[i].Row = row; CellUpdateInsertArgument ea = new CellUpdateInsertArgument(); if (isInsert) { ea.m_Insert = true; ea.m_Update = false; } else { ea.m_Update = true; ea.m_Insert = false; } ea.Name = row.Columns[i].ColumnId; ea.Column = row.Columns[i]; ea.PostBackValue = row[ea.Name].PostBackValue; ea.Value = row[ea.Name].Value; ea.DataSourceValue = row[ea.Name].DataSourceValue; if (row.m_Table.m_Grid.Trace.IsTracing) row.m_Table.m_Grid.Trace.Trace( isInsert ? "{0} on insert has value '{1}' and old value was '{2}'" : "{0} on update has value '{1}' and old value was '{2}'", ea.Name, ea.Value, ea.DataSourceValue); if (row.Columns[i].IsInDataSource == false || row.Columns[i].ColumnType == ColumnType.SystemColumn) ea.IgnoreChanges = true; if (row.Columns[i].Identity) { ea.IgnoreChanges = true; identity = true; if (editIndex == null && row[i].Value != null) editIndex = row[i].Value.ToString(); } if (ea.IgnoreChanges == false) columnArgs.AcceptChanges = true; columnArgs.Row.Add(ea.Name, ea); } row.m_Table.m_Grid.BeforeUpdateInsertEvent(columnArgs); for (int i = 0; i < row.Columns.Count; i++) if (row.Columns[i].ColumnType == ColumnType.Foreignkey == false && row.Columns[i].IsInDataSource && row.Columns[i].ColumnType == ColumnType.SystemColumn == false) row[row.Columns[i].ColumnId].Value = columnArgs.Row[row.Columns[i].ColumnId].Value; if (columnArgs.AcceptChanges == false || row.m_Table.m_Grid.SystemMessage.Count > 0) return false; if (String.IsNullOrEmpty(row.m_Table.DataSourceId) && row.m_Table.DataSource == null) return true; // No datasources. InsertUpdate updateColumn = null; bool updates = false; if ((String.IsNullOrEmpty(row.m_Table.m_Grid.DataSourceId) || row.m_Table.DataSourceType != DataSourceControlType.InternalDataSource) && isInsert) { if ( row.m_Table.DataSource is XmlDataDocument == false) row.m_DataRow = row.m_Table.m_DataSourceColumns.NewRow(); else { // Detect if any node has child nodes. XmlDataDocument xmldoc = row.m_Table.DataSource as XmlDataDocument; if (xmldoc != null && xmldoc.DocumentElement != null) { XmlNodeList nodeList = xmldoc. DocumentElement.SelectNodes( row.m_Table.m_Xmlxpath); if (nodeList != null) foreach (XmlNode xmlNode in nodeList) { DataRow myrow = ((XmlDataDocument) row.m_Table.DataSource).GetRowFromElement( (XmlElement) xmlNode); if (myrow == null) continue; if (!xmlNode.HasChildNodes) continue; row.m_Table.m_Grid.SystemMessage.Add( "This Xml node has child nodes and can only be updated or removed from the loaded XML document."); return false; } } else throw new GridException("XmlDataDocument is null or have a invalid root element (DocumentElement is null)"); updates = true; row.m_DataRow = row.m_Table.m_DataSourceColumns.NewRow(); } } if (row.DataRow == null) { string datasourcetable = row.m_Table.DataSourceId; if (Grid.DATASOURCEID_NULL == datasourcetable) datasourcetable = row.Columns.Primarykeys[0].DataSourceId ?? row.Columns.Primarykeys[0].DefaultDataSourceId; updateColumn = new InsertUpdate(datasourcetable, QueryType.Update, row.m_Table.m_Grid.ActiveConnectionString); if (isInsert) updateColumn.QueryType = QueryType.Insert; else updateColumn.FilterExpression = BuildPKFilter(row.m_Table, editIndex, true); } else { if ( row.m_Table.DataSource is XmlDataDocument && isInsert == false) { if (((XmlDataDocument) row.m_Table.DataSource).DocumentElement == null) throw new GridException("Root of your XmlDocument is null" + row.m_Table.DataSource); List<Column> datacolumns = row.m_Table.Columns.Primarykeys; XmlNodeList nodeList = ((XmlDataDocument)row.m_Table.DataSource).DocumentElement. SelectNodes( row.m_Table.m_Xmlxpath); if (nodeList != null) foreach (XmlNode xmlNode in nodeList) { DataRow myrow = ((XmlDataDocument)row.m_Table.DataSource).GetRowFromElement( (XmlElement) xmlNode); if (myrow == null) continue; if (xmlNode.HasChildNodes) { if (DetectDataSourceRow(row.m_DataRow, myrow, datacolumns)) { row.m_DataRow = myrow; updates = true; break; } } else if (DetectDataSourceRow(row.m_DataRow, myrow, datacolumns)) { updates = true; row.m_DataRow = myrow; break; } } } } try { for (int i = 0; i < row.Columns.Count; i++) { if (row.Columns[i].IsInDataSource == false || row.Columns[i].ColumnType == ColumnType.SystemColumn || !String.IsNullOrEmpty(row.Columns[i].DataSourceId) && row.Columns[i].ColumnType != ColumnType.Foreignkey && row.Columns[i].DataSourceId.Equals(row.m_Table.DataSourceId) == false) continue; CellUpdateInsertArgument ea = columnArgs.Row[row.Columns[i].ColumnId]; if ( ReferenceEquals(Grid.NULLCONSTANT, ea.Value) || ea.Value == null) ea.Value = DBNull.Value; if (row.Columns[i].ColumnType == ColumnType.Foreignkey == false) row.Columns[i].OnUpdateInsert(ea, row[row.Columns[i].ColumnId]); if (row.m_Table.m_Grid.SystemMessage.Count > 0) return false; if (ea.IgnoreChanges || (row.Columns[i].AllowEdit == false && (isInsert == false || (row.Columns[i].Primarykey == false && (row.Columns[i].Required == false && row.Columns[i].IsInDataSource == false)))) || (row.m_Table.m_Grid.DisplayView == DisplayView.Grid && row.Columns[i].AllowEditInGrid == false) || (ea.Value == ea.DataSourceValue && isInsert == false)) { StringBuilder reason = new StringBuilder(); if (ea.IgnoreChanges) reason.Append("ignore"); if (isInsert && row.Columns[i].Primarykey == false) reason.Append(" was not primary key on insert"); if (row.m_Table.m_Grid.Trace.IsTracing) row.m_Table.m_Grid.Trace.Trace( isInsert ? "skipping {0} on insert has value '{1}' and old value was '{2}' (reason: {3})" : "skipping {0} on update has value '{1}' and old value was '{2}' (reason: {3})", ea.Name, ea.Value, ea.DataSourceValue, reason.ToString()); continue; } switch (row.Columns[i].ColumnType) { case ColumnType.File: case ColumnType.Image: if (row.Columns[i].IsBlob == false && row.Columns[i].FileNameColumn != row.Columns[i].ColumnId) continue; if (ea.Parameter != null && row.Columns[i].FileNameColumn != row.Columns[i].ColumnId) { if (row.DataRow == null) updateColumn.Add(row.Columns[i].ColumnId, (BinaryReader) ea.Parameter); else if (row.DataRow.Table.Columns.Contains(row.Columns[i].ColumnId)) row.DataRow[row.Columns[i].ColumnId] = ea.Parameter; } else if (Grid.GotHttpContext &&row.Columns[i].FileNameColumn == row.Columns[i].ColumnId && HttpContext.Current.Session[row[row.Columns[i].ColumnId].CellClientId + "_img"] != null) { object myvalue = HttpContext.Current.Session[row[row.Columns[i].ColumnId].CellClientId + "_img"]; if (row.DataRow == null) updateColumn.Add(row.Columns[i].ColumnId, myvalue); else if (row.DataRow.Table.Columns.Contains(row.Columns[i].ColumnId)) row.DataRow[row.Columns[i].ColumnId] = myvalue; } break; default: if (row.DataRow == null) { if (updateColumn != null) updateColumn.Add(row.Columns[i].ColumnId, ea.Value); } else if (row.DataRow.Table.Columns.Contains(row.Columns[i].ColumnId)) row.DataRow[row.Columns[i].ColumnId] = ea.Value; break; } updates = true; } } catch (Exception ee) { throw new GridDataSourceException("Error generating update/insert sql", ee); } if (row.m_Table.m_Grid.Debug && updateColumn != null) row.m_Table.m_Grid.m_DebugString.AppendFormat("<b>Datainterface.InsertUpdateRow</b> - {0}<br/>", updateColumn.GetQueryString()); string res = null; if (updates) { try { if (row.DataRow == null && row.m_Table.DataSource == null) res = updateColumn.Execute(); else { if ( row.m_Table.DataSource != null && row.m_Table.DataSource is OleDbDataAdapter) { OleDbCommandBuilder updateCommand = new OleDbCommandBuilder((OleDbDataAdapter) row.m_Table.DataSource); if (isInsert) row.DataRow.Table.Rows.Add(row.DataRow); ((OleDbDataAdapter) row.m_Table.DataSource).Update(row.DataRow.Table); updateCommand.Dispose(); } else if (row.m_Table.m_XmlDataDocument != null) { try { if (isInsert) row.DataRow.Table.Rows.Add(row.DataRow); row.m_Table.m_XmlDataSet.WriteXml(row.m_Table.m_XmlDataDocument); } catch (Exception ee) { throw new GridDataSourceException("Error processing xml for updating/inserting", ee); } } else { if (isInsert && row.DataRow != null) row.DataRow.Table.Rows.Add(row.DataRow); switch (row.m_Table.DataSourceType) { case DataSourceControlType.SqlDataSource: case DataSourceControlType.AccessDataSource: UpdateInsertDataSourceControl(row, isInsert); break; case DataSourceControlType.ObjectDataSource: UpdateInsertObjectDataSourceControl(row, isInsert); break; default: row.m_Table.m_Grid.BindDataSourceSession(); break; } } } } catch (Exception ee) { if (updateColumn != null) throw new GridDataSourceException("Error executing insert/update sql", ee); throw new GridDataSourceException("Error updating data source", ee); } if (res != null && isInsert) { editIndex = res; foreach (Column column in row.m_Table.Columns) { if (!column.Identity) continue; row[column.ColumnId].Value = editIndex; break; } } } for (int i = 0; i < row.Columns.Count; i++) if (!String.IsNullOrEmpty(row.Columns[i].DataSourceId) && row.m_Table.m_Grid[row.Columns[i].ColumnId].ColumnType == ColumnType.Foreignkey == false) row.Columns[i].UpdateInsertColumnDataSourceId(row, columnArgs, editIndex); if (identity == false) editIndex = row.PrimaryKeyUpdateValues; return true; }
/// <summary> /// Deletes a row from the database. /// </summary> /// <param name="table">The table in which to delete the row.</param> /// <param name="rowKeys">The row ID.</param> /// <returns></returns> internal static bool DeleteRow(Table table, string rowKeys) { if (Grid.GotHttpContext && System.IO.File.Exists(String.Format("{0}\\noupdate.webgrid", HttpContext.Current.Server.MapPath("."))) && table.m_Grid.Tag["allowupdate"] == null) { string content = System.IO.File.ReadAllText(String.Format("{0}\\noupdate.webgrid", HttpContext.Current.Server.MapPath("."))); table.m_Grid.SystemMessage.Add( String.IsNullOrEmpty(content) ? "Inserting, updating, or deleting a database record functionality has been disabled." : content); return false; } table.GetData(false); if (table.m_Grid.MasterTable.Rows.Count == 0) return true; if ( table.DataSource != null || table.m_Grid.MasterTable.Rows[0].DataRow != null) { string tmpId = table.m_Grid.InternalId; table.m_Grid.InternalId = rowKeys; table.m_Grid.MasterTable.GetData(true); switch (table.DataSourceType) { case DataSourceControlType.SqlDataSource: case DataSourceControlType.AccessDataSource: DeleteDataSourceControl(table.m_Grid.MasterTable.Rows[0]); break; case DataSourceControlType.ObjectDataSource: DeleteObjectDataSourceControl(table.m_Grid.MasterTable.Rows[0]); break; } if (table.m_XmlDataDocument == null) { table.m_Grid.MasterTable.Rows[0].DataRow.Delete(); if ( table.DataSource != null && table.DataSource is OleDbDataAdapter) { try { OleDbCommandBuilder updateCommand = new OleDbCommandBuilder((OleDbDataAdapter) table.DataSource); ((OleDbDataAdapter) table.DataSource).Update( table.m_Grid.MasterTable.Rows[0].DataRow.Table); updateCommand.Dispose(); } catch (Exception ee) { throw new GridException("Error deleting record from data source.", ee); } } table.m_Grid.MasterTable.Rows[0].DataRow.Table.AcceptChanges(); } else if (table.m_XmlDataDocument != null) { try { List<Column> datacolumns = table.Columns.Primarykeys; if (datacolumns == null) { table.m_Grid.SystemMessage.Add("Primary key is required for the XML file to delete rows.", true); return false; } foreach (DataTable dt in table.m_XmlDataSet.Tables) { if (dt.TableName != rowKeys) continue; int count = dt.Rows.Count; for (int i = 0; i < count; i++) table.m_XmlDataSet.Tables[dt.TableName].Rows.RemoveAt(0); break; } table.m_XmlDataSet.AcceptChanges(); table.m_XmlDataSet.WriteXml(table.m_XmlDataDocument); } catch (Exception ee) { throw new GridDataSourceException("Error removing row in XML", ee); } } table.m_Grid.InternalId = tmpId; } else { string datasourcetable = table.DataSourceId; if( datasourcetable == null) return true; if (datasourcetable.Equals(Grid.DATASOURCEID_NULL)) datasourcetable = table.Columns.Primarykeys[0].DataSourceId ?? table.Columns.Primarykeys[0].DefaultDataSourceId; InsertUpdate delete = new InsertUpdate(datasourcetable, QueryType.Delete, table.ConnectionString) {FilterExpression = BuildPKFilter(table, rowKeys, true)}; if (table.m_Grid.Debug) table.m_Grid.m_DebugString.AppendFormat("<b>{0}: SqlConnection/OleDB.DeleteRow({1}) </b>- {2}<br/>", table.m_Grid.ID, table.DataSourceId, delete.GenerateSql()); try { // MUST FIX; delete.Execute(); } catch (Exception ee) { throw new GridDataSourceException(String.Format("Error deleting datasource record (SQL generated:{0}) FILTER: " + BuildPKFilter(table, rowKeys, true), delete.GenerateSql()), ee); } } return true; }