Beispiel #1
0
		[Category ("NotWorking")] // Requires newer sqlite than is on wrench
		public void UpdateResetRowErrorCorrectly ()
		{
			const string connectionString = "URI = file::memory:; Version = 3";
			using (var dbConnection = new SqliteConnection (connectionString)) {
				dbConnection.Open ();

				using (var cmd = dbConnection.CreateCommand ()) {
					cmd.CommandText = "CREATE TABLE data (id PRIMARY KEY, name TEXT)";
					cmd.ExecuteNonQuery ();
				}


				var ts = dbConnection.BeginTransaction ();
				var da = new SqliteDataAdapter ("SELECT * FROM data", dbConnection);
				var builder = new SqliteCommandBuilder (da);
				da.UpdateCommand = builder.GetUpdateCommand ();
				da.UpdateCommand.Transaction = ts;

				var ds1 = new DataSet ();
				da.Fill (ds1, "data");

				var table = ds1.Tables [0];
				var row = table.NewRow ();
				row ["id"] = 10;
				row ["name"] = "Bart";
				table.Rows.Add (row);

				var ds2 = ds1.GetChanges ();
				da.Update (ds2, "data");
				Assert.IsFalse (ds2.HasErrors);
			}
		}
Beispiel #2
0
		public void XimarinBugzillaBug853Test()
                {
                        const string connectionString = "URI = file:./SqliteTest.db; Version = 3";//will be in System.Data directory
                        SqliteConnection dbConnection = new SqliteConnection(connectionString);
                        dbConnection.Open();
			SqliteCommand ClearTableEntry=new SqliteCommand("DELETE FROM Primus;",dbConnection);
			ClearTableEntry.ExecuteNonQuery();

                        SqliteDataAdapter sqliteDataAdapter = new SqliteDataAdapter("SELECT * FROM primus", dbConnection);
                        SqliteCommandBuilder builder = new SqliteCommandBuilder(sqliteDataAdapter);
			sqliteDataAdapter.InsertCommand = builder.GetInsertCommand();
                        sqliteDataAdapter.DeleteCommand = builder.GetDeleteCommand();
			
                        DataSet dataSet = new DataSet();

                        sqliteDataAdapter.Fill(dataSet, "Primus");//reset

                        DataRow rowToBeAdded = dataSet.Tables["Primus"].NewRow();
                        rowToBeAdded["id"] = 123;
                        rowToBeAdded["name"] = "Name";//not null primary key
                        rowToBeAdded["value"] = 777;

                        dataSet.Tables["Primus"].Rows.Add(rowToBeAdded);
sqliteDataAdapter.Update (dataSet, "Primus");

			//This would fail with NULL constraint violation in bug
			//report.  Because before the patch, it would create
			//a new record with all fields being null-- if the
			//exception rises, test fails
                        sqliteDataAdapter.Update (dataSet, "Primus");

                        dbConnection.Close();
                        dbConnection = null;
		}
Beispiel #3
0
        private static DataTable SaveDynamicEntity(tgDataRequest request)
        {
            bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted;

            DataTable dataTable = CreateDataTable(request);

            using (SqliteDataAdapter da = new SqliteDataAdapter())
            {
                da.AcceptChangesDuringUpdate = false;

                DataRow row = dataTable.NewRow();
                dataTable.Rows.Add(row);

                SqliteCommand cmd = null;

                switch (request.EntitySavePacket.RowState)
                {
                    case tgDataRowState.Added:
                        cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket);
                        SetModifiedValues(request, request.EntitySavePacket, row);
                        break;

                    case tgDataRowState.Modified:
                        cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket);
                        SetOriginalValues(request, request.EntitySavePacket, row, false);
                        SetModifiedValues(request, request.EntitySavePacket, row);
                        row.AcceptChanges();
                        row.SetModified();
                        break;

                    case tgDataRowState.Deleted:
                        cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request);
                        SetOriginalValues(request, request.EntitySavePacket, row, true);
                        row.AcceptChanges();
                        row.Delete();
                        break;
                }

                if (!needToDelete && request.Properties != null)
                {
                    request.Properties["tgDataRequest"] = request;
                    request.Properties["esEntityData"] = request.EntitySavePacket;
                    dataTable.ExtendedProperties["props"] = request.Properties;
                }

                DataRow[] singleRow = new DataRow[1];
                singleRow[0] = row;

                try
                {
                    if (!request.IgnoreComputedColumns)
                    {
                        da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated);
                    }
                    tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);

                    #region Profiling

                    if (sTraceHandler != null)
                    {
                        using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace))
                        {
                            try
                            {
                                da.Update(singleRow);
                            }
                            catch (Exception ex)
                            {
                                esTrace.Exception = ex.Message;
                                throw;
                            }
                        }
                    }
                    else

                    #endregion Profiling

                    {
                        da.Update(singleRow);
                    }
                }
                finally
                {
                    tgTransactionScope.DeEnlist(cmd);
                }

                if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
                {
                    foreach (SqliteParameter param in cmd.Parameters)
                    {
                        switch (param.Direction)
                        {
                            case ParameterDirection.Output:
                            case ParameterDirection.InputOutput:

                                request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
                                break;
                        }
                    }
                }

                cmd.Dispose();
            }

            return dataTable;
        }
Beispiel #4
0
        private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request)
        {
            SqliteCommand cmd = null;

            DataTable dataTable = CreateDataTable(request);

            using (tgTransactionScope scope = new tgTransactionScope())
            {
                using (SqliteDataAdapter da = new SqliteDataAdapter())
                {
                    da.AcceptChangesDuringUpdate = false;
                    da.ContinueUpdateOnError = request.ContinueUpdateOnError;

                    try
                    {
                        cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request);
                        tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);

                        DataRow[] singleRow = new DataRow[1];

                        // Delete each record
                        foreach (tgEntitySavePacket packet in request.CollectionSavePacket)
                        {
                            DataRow row = dataTable.NewRow();
                            dataTable.Rows.Add(row);

                            SetOriginalValues(request, packet, row, true);
                            row.AcceptChanges();
                            row.Delete();

                            singleRow[0] = row;

                            #region Profiling

                            if (sTraceHandler != null)
                            {
                                using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
                                {
                                    try
                                    {
                                        da.Update(singleRow);
                                    }
                                    catch (Exception ex)
                                    {
                                        esTrace.Exception = ex.Message;
                                        throw;
                                    }
                                }
                            }
                            else

                            #endregion Profiling

                            {
                                da.Update(singleRow);
                            }

                            if (row.HasErrors)
                            {
                                request.FireOnError(packet, row.RowError);
                            }

                            dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
                        }
                    }
                    finally
                    {
                        tgTransactionScope.DeEnlist(cmd);
                        cmd.Dispose();
                    }
                }
                scope.Complete();
            }

            return request.Table;
        }
Beispiel #5
0
    void InitData()
    {
        string cs = "URI=file:test.db";
        _dataTable=new DataTable();

        //		createData();

        string stm = "SELECT * FROM Cars";

        con = new SqliteConnection(cs);

            con.Open();

            ds = new DataSet();
            SqliteCommandBuilder cmdBuilder;
            da = new SqliteDataAdapter(stm, con);

                da.Fill(ds, "Cars");
                cmdBuilder = new SqliteCommandBuilder(da);

        //				SqliteCommand insCmd = cmdBuilder.GetInsertCommand();
                // INSERT INTO [Cars] ([Name], [Note]) VALUES (@param1, @param2)

                dgv.DataSource = ds.Tables["Cars"];

        //BindingSource bs=ds.Tables["Cars"];
        //dgv.DataSource=bs;

                addRow("audi","another note");
                addRowSQL("audi sql","another note");

                da.Update(ds.Tables[0]);
    }
Beispiel #6
0
        private void UpdateDatabase()
        {
            try
            {
                if (File.Exists("data/db/players.db"))
                {
                    File.Move("data/db/players.db", "data/db/players_old.db");
                    using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\players_old.db"))
                    {
                        old.Open();

                        DataTable table = new DataTable("players");
                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", old);
                            adapter.Fill(table);
                        }

                        foreach (DataRow row in table.Rows)
                        {
                            row.SetAdded();
                        }

                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", connection);
                            adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
                            adapter.Update(table);
                        }
                    }
                }
                if (File.Exists("data/db/hosts.db"))
                {
                    File.Move("data/db/hosts.db", "data/db/hosts_old.db");
                    using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\hosts_old.db"))
                    {
                        old.Open();

                        DataTable table = new DataTable("hosts");
                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", old);
                            adapter.Fill(table);
                        }

                        foreach (DataRow row in table.Rows)
                        {
                            row.SetAdded();
                        }

                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", connection);
                            adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
                            adapter.Update(table);
                        }
                    }
                }
                if (File.Exists("data/db/comments.db"))
                {
                    File.Move("data/db/comments.db", "data/db/comments_old.db");
                    using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\comments_old.db"))
                    {
                        old.Open();

                        DataTable table = new DataTable("comments");
                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", old);
                            adapter.Fill(table);
                        }

                        foreach (DataRow row in table.Rows)
                        {
                            row.SetAdded();
                        }

                        using (SqliteDataAdapter adapter = new SqliteDataAdapter())
                        {
                            adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", connection);
                            adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
                            adapter.Update(table);
                        }
                    }
                }
            }
            catch(Exception e)
            {
                using (StreamWriter writer = new StreamWriter("migrate_error.txt"))
                {
                    writer.WriteLine(e.Message);
                    writer.WriteLine(e.StackTrace);
                }
            }
        }
Beispiel #7
0
		/// <summary>  
		/// 更新数据集中数据到数据库  
		/// </summary>  
		/// <param name="insertCommand">insert语句</param>  
		/// <param name="deleteCommand">delete语句</param>  
		/// <param name="updateCommand">update语句</param>  
		/// <param name="dataSet">要更新的DataSet</param>  
		/// <param name="tableName">数据集中要更新的table名</param>  
		public static void UpdateDataset (SqliteCommand insertCommand, SqliteCommand deleteCommand, SqliteCommand updateCommand, DataSet dataSet, string tableName)
		{  
			if (insertCommand == null)
				throw new ArgumentNullException ("insertCommand");  
			if (deleteCommand == null)
				throw new ArgumentNullException ("deleteCommand");  
			if (updateCommand == null)
				throw new ArgumentNullException ("updateCommand");  
			if (tableName == null || tableName.Length == 0)
				throw new ArgumentNullException ("tableName");  

			// Create a SQLiteDataAdapter, and dispose of it after we are done  
			using (SqliteDataAdapter dataAdapter = new SqliteDataAdapter ()) {  
				// Set the data adapter commands  

				dataAdapter.UpdateCommand = updateCommand;  
				dataAdapter.InsertCommand = insertCommand;  
				dataAdapter.DeleteCommand = deleteCommand;  
				// Update the dataset changes in the data source  
				dataAdapter.Update (dataSet, tableName);  

				// Commit all the changes made to the DataSet  
				dataSet.AcceptChanges ();  
			}  
		}