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