public void GetInsertCommandTest ()
{
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
OdbcCommand cmd = null;
try {
string selectQuery = "select id, lname from employee where id = 3";
OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
OdbcCommandBuilder cb;
cb = new OdbcCommandBuilder (da);
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
cmd.CommandText, "#A1");
Assert.AreSame (conn, cmd.Connection, "#A2");
AssertInsertParameters (cmd, "#A3:");
cb = new OdbcCommandBuilder (da);
cb.QuotePrefix = "\"";
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO \"employee (\"id, \"lname) VALUES (?, ?)",
cmd.CommandText, "#B1");
Assert.AreSame (conn, cmd.Connection, "#B2");
AssertInsertParameters (cmd, "#B3:");
cb = new OdbcCommandBuilder (da);
cb.QuoteSuffix = "´";
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO employee´ (id´, lname´) VALUES (?, ?)",
cmd.CommandText, "#C1");
Assert.AreSame (conn, cmd.Connection, "#C2");
AssertInsertParameters (cmd, "#C3:");
cb = new OdbcCommandBuilder (da);
cb.QuotePrefix = "\"";
cb.QuoteSuffix = "´";
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO \"employee´ (\"id´, \"lname´) VALUES (?, ?)",
cmd.CommandText, "#D1");
Assert.AreSame (conn, cmd.Connection, "#D2");
AssertInsertParameters (cmd, "#D3:");
} finally {
if (cmd != null)
cmd.Dispose ();
ConnectionManager.Singleton.CloseConnection ();
}
}
public void QuoteSuffix_InsertCommand_Generated ()
{
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
OdbcCommand cmd = null;
try {
string selectQuery = "select id, lname from employee where id = 3";
OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
cmd = cb.GetInsertCommand ();
Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#1");
try {
cb.QuoteSuffix = "";
Assert.Fail ("#2");
} catch (InvalidOperationException ex) {
// The QuotePrefix and QuoteSuffix properties
// cannot be changed once an Insert, Update, or
// Delete command has been generated
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
Assert.IsNull (ex.InnerException, "#4");
Assert.IsNotNull (ex.Message, "#5");
}
Assert.AreEqual (string.Empty, cb.QuoteSuffix, "#6");
cb.RefreshSchema ();
cb.QuoteSuffix = "";
} finally {
if (cmd != null)
cmd.Dispose ();
ConnectionManager.Singleton.CloseConnection ();
}
}
public void GetInsertCommandTest_option_false ()
{
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
try {
string selectQuery = "select id, lname from employee where id = 3";
OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
OdbcCommand cmd = cb.GetInsertCommand (false);
Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
cmd.CommandText, "#1");
Assert.AreSame (conn, cmd.Connection, "#2");
AssertInsertParameters (cmd, "#3:");
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
}
public void GetInsertCommandTestWithExpression ()
{
if (ConnectionManager.Singleton.Engine.Type == EngineType.MySQL)
Assert.Ignore ("Schema info from MySQL is incomplete");
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
OdbcCommand cmd = null;
try {
string selectQuery = "select id, lname, id+1 as next_id from employee where id = 3";
OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
cmd = cb.GetInsertCommand ();
Assert.AreEqual ("INSERT INTO employee (id, lname) VALUES (?, ?)",
cmd.CommandText, "#1");
Assert.AreSame (conn, cmd.Connection, "#2");
AssertInsertParameters (cmd, "#3:");
} finally {
if (cmd != null)
cmd.Dispose ();
ConnectionManager.Singleton.CloseConnection ();
}
}
// Sauvegarde tous les changements effectué dans le dataset
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
return;
switch (connType)
{
case ConnectionType.DATABASE_MSSQL:
{
try
{
var conn = new SqlConnection(connString);
var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
var builder = new SqlCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
PrintDatasetErrors(changes);
dataSet.AcceptChanges();
}
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table " + tableName, ex);
}
break;
}
case ConnectionType.DATABASE_ODBC:
{
try
{
var conn = new OdbcConnection(connString);
var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
var builder = new OdbcCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table ", ex);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
return;
}
case ConnectionType.DATABASE_OLEDB:
{
try
{
var conn = new OleDbConnection(connString);
var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
var builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table", ex);
}
break;
}
}
}
public void SaveDataSetThroughAdapter(System.Data.DataSet dsSetRef,
Boolean blnRequiredTransaction, String ExcludeTableName, String strConName)
{
Boolean blnBeginTrans = false;
OleDbDataAdapter objOleDBAdpater;
OdbcDataAdapter objOdbcDBAdpater;
SqlDataAdapter objSqlDBAdpater;
OdbcCommandBuilder objOdbcDBCommandBuilder;
OleDbCommandBuilder objOleDBCommandBuilder;
SqlCommandBuilder objSqlDBCommandBuilder;
IDbCommand IMainCommand;
DataTable dtInsert;
DataTable dtUpdate;
DataTable dtDelete;
Boolean TableExist;
String strQuery;
try
{
if (dsSetRef == null)
{
throw new Exception("DataSet not Initialized");
}
String[] TableName;
char[] delimeter;
String seperator;
seperator = ",";
delimeter = seperator.ToCharArray();
TableName = ExcludeTableName.Split(delimeter);
if (blnRequiredTransaction.IsFalse())
{
if (strConName.Length > 0)
{
OpenConnection(strConName);
}
else
{
OpenConnection(String.Empty);
}
}
if (disconnection.IsNotNull())
{
if (blnRequiredTransaction.IsFalse())
{
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (transaction == null)
{
throw new Exception("Transaction is not initialized");
}
else
{
blnBeginTrans = true;
}
}
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = new SqlCommand();
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = new OleDbCommand();
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = new OdbcCommand();
}
else
{
IMainCommand = null;
}
IMainCommand.Connection = disconnection;
IMainCommand.Transaction = transaction;
}
else
{
throw new Exception("Connection is not initialized");
}
IMainCommand.CommandTimeout = CommandTimeOutValue;
foreach (DataTable dtRef in dsSetRef.Tables)
{
TableExist = false;
foreach (String tablename in TableName)
{
if (dtRef.TableName.ToUpper() == tablename.ToUpper())
{
TableExist = true;
break;
}
}
if (TableExist) continue;
if ((Boolean)dtRef.ExtendedProperties[JoinedQuery])
{
strQuery = dtRef.ExtendedProperties[UpdateQuery].ToString();
}
else
{
strQuery = dtRef.ExtendedProperties[Query].ToString();
}
if ((strQuery.Trim()).Length == 0)
{
throw new Exception("Query is blank");
}
IMainCommand.CommandText = strQuery;
dtInsert = dtRef.GetChanges(DataRowState.Added);
dtUpdate = dtRef.GetChanges(DataRowState.Modified);
dtDelete = dtRef.GetChanges(DataRowState.Deleted);
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
objSqlDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand);
objSqlDBCommandBuilder = new SqlCommandBuilder(objSqlDBAdpater);
if (dtDelete.IsNotNull())
{
objSqlDBCommandBuilder.GetDeleteCommand();
objSqlDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
if (dtInsert.IsNotNull())
{
objSqlDBCommandBuilder.GetInsertCommand();
objSqlDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objSqlDBCommandBuilder.GetUpdateCommand();
objSqlDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
objOleDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand);
objOleDBCommandBuilder = new OleDbCommandBuilder(objOleDBAdpater);
if (dtInsert.IsNotNull())
{
objOleDBCommandBuilder.GetInsertCommand();
objOleDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOleDBCommandBuilder.GetUpdateCommand();
objOleDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOleDBCommandBuilder.GetDeleteCommand();
objOleDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
objOdbcDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand);
objOdbcDBCommandBuilder = new OdbcCommandBuilder(objOdbcDBAdpater);
if (dtInsert.IsNotNull())
{
objOdbcDBCommandBuilder.GetInsertCommand();
objOdbcDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOdbcDBCommandBuilder.GetUpdateCommand();
objOdbcDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOdbcDBCommandBuilder.GetDeleteCommand();
objOdbcDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else
{
objSqlDBAdpater = null;
objOleDBAdpater = null;
objOdbcDBAdpater = null;
objSqlDBCommandBuilder = null;
objOleDBCommandBuilder = null;
objOdbcDBCommandBuilder = null;
}
}
if (blnRequiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
disconnection.Close();
disconnection.Dispose();
disconnection = null;
}
}
catch (System.Data.OleDb.OleDbException exOleDb)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exOleDb);
}
catch (System.Data.DBConcurrencyException exDBCE)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exDBCE);
}
catch (System.Exception ex)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
finally
{
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = null;
objSqlDBAdpater = null;
objSqlDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = null;
objOleDBAdpater = null;
objOleDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = null;
objOdbcDBAdpater = null;
objOdbcDBCommandBuilder = null;
}
}
}
/// <summary>
/// 插入数据通过Datatable
/// </summary>
/// <param name="_dt"></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);
OdbcCommand _Command = (OdbcCommand)CreateCommand(_sCmdText, CommandType.Text);
OdbcDataAdapter _adapter = new OdbcDataAdapter(_Command);
OdbcDataAdapter _adapter1 = new OdbcDataAdapter(_Command);
OdbcCommandBuilder _builder = new OdbcCommandBuilder(_adapter1);
DataTable dt = new DataTable(_dt.TableName);
_adapter.Fill(dt);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
_dt.WriteXml(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
dt.ReadXml(ms);
ms.Dispose();
_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, ",?");
_adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OdbcType.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));
dt.Dispose();
}
return _nResult;
}
