public void AseAdapter_WithAseCommandBuilder_CanInsertUpdateAndDelete()
{
using (var connnection = new AseConnection(ConnectionStrings.Default))
{
connnection.Open();
using (var adapter = new AseDataAdapter("SELECT ColumnId, ColumnDescription, ColumnNullable, COALESCE(ColumnNullable, 'Foo') AS ColumnCalculated FROM AseDataAdapterTests_Table1", connnection))
{
using (new AseCommandBuilder(adapter))
{
var original = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(original, SchemaType.Mapped);
adapter.Fill(original);
Assert.AreEqual(5, original.Rows.Count); // SELECT
var updateRow = original.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
updateRow["ColumnDescription"] = "an updated value"; // UPDATE
var deleteRow = original.Rows.Find(3);
Assert.IsNotNull(deleteRow, "Did not find a row in AseDataAdapterTests_Table1 for delete with ColumnId=3");
deleteRow.Delete(); // DELETE
original.Rows.Add(-1, "an inserted value"); // INSERT
// Commit the changes to the database.
adapter.Update(original);
original.AcceptChanges();
var fresh = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(fresh, SchemaType.Mapped);
adapter.Fill(fresh);
Assert.AreEqual(5, fresh.Rows.Count); // SELECT
updateRow = fresh.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
Assert.AreEqual(updateRow["ColumnDescription"], "an updated value");
deleteRow = fresh.Rows.Find(3);
Assert.IsNull(deleteRow);
var insertRow = fresh.Rows.Find(6); // Next identity value.
Assert.IsNotNull(insertRow);
}
}
}
}
/// <summary>
/// 更新DataTable的方式插入数据
/// </summary>
/// <param name="_dt">DataTable</param>
/// <returns>插入记录条数</returns>
public override int DataTableInsert(DataTable _dt)
{
bool flag = false;
int _nResult = 0;
if (_dt == null)
{
return(_nResult);
}
string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
AseCommand _Command = (AseCommand)CreateCommand(_sCmdText, CommandType.Text);
AseDataAdapter _adapter = new AseDataAdapter(_Command);
AseDataAdapter _adapter1 = new AseDataAdapter(_Command);
AseCommandBuilder _builder = new AseCommandBuilder(_adapter1);
_adapter.InsertCommand = _builder.GetInsertCommand();
if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
{
flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
foreach (DataColumn _dc in _dt.Columns)
{
if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
{
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",@" + _dc.ColumnName);
_adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, AseDbType.Decimal, _dc.MaxLength, _dc.ColumnName);
if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
{
break;
}
}
}
}
if (flag)
{
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));
}
this.BeginTransaction();
try
{
_Command.CommandText = "delete from " + _dt.TableName;
_Command.ExecuteNonQuery();
_adapter.InsertCommand.Transaction = _Command.Transaction;
_nResult = _adapter.Update(_dt);
this.CommitTransaction();
}
catch (Exception ex)
{
this.RollbackTransaction();
throw ex;
}
finally
{
if (flag)
{
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
}
}
return(_nResult);
}
/// <summary>
/// 更新DataTable的方式插入数据
/// </summary>
/// <param name="_dt">DataTable</param>
/// <returns>插入记录条数</returns>
public override int DataTableInsert(DataTable _dt)
{
bool flag = false;
int _nResult = 0;
if (_dt == null)
return _nResult;
string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
AseCommand _Command = (AseCommand)CreateCommand(_sCmdText, CommandType.Text);
AseDataAdapter _adapter = new AseDataAdapter(_Command);
AseDataAdapter _adapter1 = new AseDataAdapter(_Command);
AseCommandBuilder _builder = new AseCommandBuilder(_adapter1);
_adapter.InsertCommand = _builder.GetInsertCommand();
if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
{
flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
foreach (DataColumn _dc in _dt.Columns)
{
if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
{
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",@" + _dc.ColumnName);
_adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, AseDbType.Decimal, _dc.MaxLength, _dc.ColumnName);
if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
break;
}
}
}
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));
this.BeginTransaction();
try
{
_Command.CommandText = "delete from " + _dt.TableName;
_Command.ExecuteNonQuery();
_adapter.InsertCommand.Transaction = _Command.Transaction;
_nResult = _adapter.Update(_dt);
this.CommitTransaction();
}
catch (Exception ex)
{
this.RollbackTransaction();
throw ex;
}
finally
{
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
}
return _nResult;
}