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