C# NpgsqlDataAdapter.Update方法代码示例

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost; Database=dbprueba; Id=javi; password=sistemas";
        NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
        NpgsqlCommand selectCommand = dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from articulo";
        NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);

        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);

        Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");

        foreach (DataTable dataTable in dataSet.Tables){
            show (dataTable);

        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"] = DateTime.Now.ToString();

        Console.WriteLine("Tabla con los cambios:");
            show (dataSet.Tables[0]);

        //		dbDataAdapter.RowUpdated +- delegate(object dbDadapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
        //			Console.WriteLine("RowUpdating command.CommandText= {0}", EventArgs.Command.CommantText);
        //
        //			foreach (IDataParameter dataParameter in EventArgs.Command.Parameters)
        //					Console.WriteLine ("{0}={1}", dataParameter.ParameterName, dataParameter.Value);
            };
            dbDataAdapter.Update(dataSet.Tables[0]);
    }

    protected void OnExecuteActionActivated(Object sender, System.EventArgs e)
    {
        String connectionString ="Server=localhost;Database=dbprueba;User Id=dbprueba;Password=1234";
        NpgsqlCommand selectCommand = dbConnection.CreateCommand ();
        selectCommand.CommandText = "select * from categoria";
        NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        new NpgsqlCommandBuilder(dbDataAdapter);

            dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataset = new Dataset();
        dbDataAdapter.Fill (DataSet);
        //Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);

        foreach(DataTable dataTable in dataset.Tables)
            show (dataTable);

        DataRow dataRow = dataset.Tables[0].Rows[0];
        dataRow["Nombre"] = DateTime.Now.ToString();

        Console.WriteLine("Cambios");
        show (dataset.Tables[0]);

        dbDataAdapter.Update(dataset);
    }

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ;

        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        IDbCommand selectCommand = dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from articulo where id=4";
        IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);

        Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
        foreach (DataTable dataTable in dataSet.Tables)
            show (dataTable);

        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"] = DateTime.Now.ToString ();
        Console.WriteLine("Tabla con los cambios");
        show (dataSet.Tables[0]);

        dbDataAdapter.Update (dataSet);
    }

    protected void OnPropertiesActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost; Database=pruebaBD; Id=ximo; password=admin";

        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        IDbCommand selectCommand = dbConnection.CreateCommand();

        selectCommand.CommandText = "select * BaseDatos1.from articulo where id=1";

        IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);

        Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");

            foreach (DataTable dataTable in dataSet.Tables){
            Show(dataTable);

            DataRow dataRow = dataSet.Tables[0].Rows[0];
            dataRow["Nombre"] = DateTime.Now.ToString();

            Console.WriteLine("Tabla con los cambios: ");
            Show (dataSet.Tables[0]);

            dbDataAdapter.Update (dataSet);
        }
    }

        private void loadData()
        {
            sqlQuerys.setCounter();
            analysisQuery = sqlQuerys.queryForAnalysis(analysisLetter);
            if (analysisLetter == 'H')
            {
                try
                {
                    pg.createTempTable(pg.conn, analysisLetter, dateFrom, dateTo, "asdasdad");

                    NpgsqlCommand command = new NpgsqlCommand(analysisQuery, pg.conn);

                    NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
                    adapter.SelectCommand = command;
                    DataTable dbTable = new DataTable();
                    adapter.Fill(dbTable);
                    BindingSource bSource = new BindingSource();

                    bSource.DataSource = dbTable;
                    dataGridView1.DataSource = bSource;
                    adapter.Update(dbTable);
                }
                catch
                {

                }
            }
            else if (analysisLetter == 'D')
            {
                string[] temp;
                string sqlForDayAnalysis = sqlQuerys.createSqlForDayAnalysis(dateFrom, dateTo);
                analysisQuery = sqlQuerys.queryForAnalysis(analysisLetter);
                analysisQuery += sqlForDayAnalysis;
                temp = analysisQuery.Split('#');

                try
                {
                    pg.createTempTable(pg.conn, analysisLetter, dateFrom, dateTo, temp[1]);

                    NpgsqlCommand command = new NpgsqlCommand(temp[0], pg.conn);

                    NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
                    adapter.SelectCommand = command;
                    DataTable dbTable = new DataTable();
                    adapter.Fill(dbTable);
                    BindingSource bSource = new BindingSource();

                    bSource.DataSource = dbTable;
                    dataGridView1.DataSource = bSource;
                    adapter.Update(dbTable);
                }
                catch
                {

                }
            }
        }

 public static void Save()
 {
     MyConnection.ConnectionString = connectionStr;
        MyConnection.Open();
        string sql = String.Format(zapytanie);
        NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, MyConnection);
        NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da);
        da.Update(newdt);
        MyConnection.Close();
 }

        public void InsertWithDataSet()
        {
            var ds = new DataSet();
            var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn);

            da.InsertCommand = new NpgsqlCommand("INSERT INTO data (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", Conn);

            da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
            da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
            da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));

            da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
            da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
            da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;

            da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
            da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
            da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";

            da.Fill(ds);

            var dt = ds.Tables[0];
            var dr = dt.NewRow();
            dr["field_int2"] = 4;
            dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
            dr["field_numeric"] = 7.3M;
            dt.Rows.Add(dr);

            var ds2 = ds.GetChanges();
            da.Update(ds2);

            ds.Merge(ds2);
            ds.AcceptChanges();

            var dr2 = new NpgsqlCommand("SELECT field_int2, field_numeric, field_timestamp FROM data", Conn).ExecuteReader();
            dr2.Read();

            Assert.AreEqual(4, dr2[0]);
            Assert.AreEqual(7.3000000M, dr2[1]);
            dr2.Close();
        }

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost;Database=dbprueba;User Id=dbprueba;password=Juliana";
        //clase para conectar la bd
        NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
        NpgsqlCommand selectCommand = dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from categoria ";
        //Interfaz
        NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);

        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        //rellenar el dataset
        dbDataAdapter.Fill(dataSet);

        Console.WriteLine("Tables.Count={0}",dataSet.Tables.Count);
        //le pasamos al métos show los datos de la tabla.
        foreach(DataTable dataTable in dataSet.Tables)
            show (dataTable);
        //modificar un registro
        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"]= DateTime.Now.ToString();

        Console.WriteLine("Tabla con los cambios: ");
        show(dataSet.Tables[0]);

        dbDataAdapter.RowUpdating += delegate (object dbDataAdapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
            Console.WriteLine("RowUpdating command.CommandText={0}",eventArgs.Command.CommandText);
        };

        foreach(IDataParameter dataParameter in eventArgs.Command.Parameters)
            Console.WriteLine("{0}={1}",dataParameter.ParameterName,dataParameter.Value);

        //actualiza la tabla.Falla el update
        dbDataAdapter.Update(dataSet.Tables[0]);

        //TreeView.Fill (treeView,dataSet);
    }

        public void DataAdapterUpdateReturnValue()
        {
            var ds = new DataSet();
            var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn);

            da.InsertCommand = new NpgsqlCommand(@"INSERT INTO data (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", Conn);

            da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
            da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
            da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));

            da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
            da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
            da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;

            da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
            da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
            da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";

            da.Fill(ds);

            var dt = ds.Tables[0];
            var dr = dt.NewRow();
            dr["field_int2"] = 4;
            dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
            dr["field_numeric"] = 7.3M;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["field_int2"] = 4;
            dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
            dr["field_numeric"] = 7.3M;
            dt.Rows.Add(dr);

            var ds2 = ds.GetChanges();
            var daupdate = da.Update(ds2);

            Assert.AreEqual(2, daupdate);
        }

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString=("Server=localhost; Database=dbprueba; UserId=dbprueba; Password=sistemas");
        NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
        NpgsqlCommand selectCommand =dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from categoria where id=1";
        NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        new NpgsqlCommandBuilder(dbDataAdapter);
        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);
        Console.WriteLine ("Tables.Count={0}",dataSet.Tables.Count);

        foreach (DataTable dataTable in dataSet.Tables)
        {
            show (dataTable);

        }

        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"] = DateTime.Now.ToString();

        Console.WriteLine("tablas con los cambios");
        show(dataSet.Tables[0]);

        /*dbDataAdapter.RowUpdating+= delegate(object dbDataAdapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
            Console.WriteLine("RowUpdating command.commandText={0}",
                              eventArgs.Command.CommandText);

            foreach(IDataParameter dataParameter in eventArgs.Command.Parameters)
                Console.WriteLine("{0}={1}", dataParameter.ParameterName,dataParameter.Value);

        };*/

          dbDataAdapter.Update(dataSet.Tables[0]);
    }

        private void wrtiteData()
        {
            DataTable dwt = new DataTable();

            NpgsqlConnection conn = DBExchange.Inst.connectDb;

            dwt.Columns.Add("ticket_id", System.Type.GetType("System.Int64"));
            dwt.Columns.Add("template_id", System.Type.GetType("System.Int32"));

            int patid = this.searchPatientBox1.pIdN;
            int docin = DBExchange.Inst.dbUsrId;

            int serviceid = rl[this.comboBox1.SelectedIndex].ResearchId;

            string _command = "Insert into ticket_radio (pat_id, doc_in, type_id, date_app, status) values "
               + "('" + patid + "','" + docin + "','" + serviceid + "', '" + this.dateTimePicker1.Value.ToShortDateString() + "', 'false') RETURNING ticket_id ;";
            NpgsqlCommand writeTicket = new NpgsqlCommand(_command, conn);

            DataTable ds = new DataTable();

            NpgsqlDataAdapter da = new NpgsqlDataAdapter("Select ticket_id, template_id from lab_results_radio where ticket_id = (Select max(ticket_id) from lab_results_radio);", conn);

            da.InsertCommand = new NpgsqlCommand("insert into lab_results_radio (ticket_id, template_id ) " +
                                    " values (:a, :b)", conn);

            da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Bigint));
            da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Integer));

            da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
            da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;

            da.InsertCommand.Parameters[0].SourceColumn = "ticket_id";
            da.InsertCommand.Parameters[1].SourceColumn = "template_id";

            try
            {
                Int64 gotInt6 = (Int64)writeTicket.ExecuteScalar();

                foreach (int i in checkedListBox1.CheckedIndices)
                {
                    DataRow dwtNewRo = dwt.NewRow();
                    dwtNewRo["ticket_id"] = gotInt6;
                    dwtNewRo["template_id"] = zl[i].ZoneId;

                    dwt.Rows.Add(dwtNewRo);
                }

                da.Fill(ds);

                ds = dwt;
                DataTable ds2 = ds.GetChanges();

                da.Update(ds2);

                ds.Merge(ds2);
                ds.AcceptChanges();

                foreach (int i in checkedListBox1.CheckedIndices)
                {
                    checkedListBox1.SetItemChecked(i, false);
                }
                dwt.Clear();

            }
            catch (Exception exception)
            {
                Warnings.WarnLog log = new Warnings.WarnLog();
                log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString());

            }
        }

		public void InsertWithDataSet()
		{
			
			_conn.Open();
			
			DataSet ds = new DataSet();

			NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", _conn);
	
			da.InsertCommand = new NpgsqlCommand("insert into tableb(field_int2, field_timestamp, field_numeric) values (:a, :b, :c)", _conn);
			
			da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
	
			da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
			
			da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
	
			da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
			da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
			da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
	
			da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
			da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
			da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
	
	
			da.Fill(ds);
	
			
			DataTable dt = ds.Tables[0];
			
			DataRow dr = dt.NewRow();
			dr["field_int2"] = 4;
			dr["field_timestamp"] = new DateTime(2003, 03, 03, 14, 0, 0);
			dr["field_numeric"] = 7.3M;
			
			dt.Rows.Add(dr);
	
			
			DataSet ds2 = ds.GetChanges();
			
			da.Update(ds2);
			
			ds.Merge(ds2);
			ds.AcceptChanges();
			
			
			NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tableb where field_serial > 4", _conn).ExecuteReader();
			dr2.Read();
			
			
			Assert.AreEqual(4, dr2[1]);
			Assert.AreEqual(7.3000000M, dr2[3]);
			
			new NpgsqlCommand("delete from tableb where field_serial > 4", _conn).ExecuteNonQuery();
			
			
						
		}

        /// <summary>
        /// Indexes a MyLo datastore using PostgreSQL function 'SetUpIndexCursorsPhotosAndActivities'
        /// </summary>
        public int ExecuteIndexerOnDataStore()
        {
            try
            {
                DataSet indexDS = new DataSet();
                NpgsqlTransaction t = _conn.BeginTransaction();
                NpgsqlCommand command = new NpgsqlCommand("SetUpIndexCursorsPhotosAndActivities", _conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new NpgsqlParameter());
                command.Parameters[0].DbType = DbType.Int64;
                command.Parameters[0].Value = _userId;

                NpgsqlCommand updateCommand = new NpgsqlCommand("UpdatePhotoActivityId", _conn);
                updateCommand.CommandType = CommandType.StoredProcedure;
                updateCommand.Parameters.Add(new NpgsqlParameter());
                updateCommand.Parameters.Add(new NpgsqlParameter());
                updateCommand.Parameters.Add(new NpgsqlParameter());
                updateCommand.Parameters[0].DbType = DbType.Int64;
                updateCommand.Parameters[1].DbType = DbType.Guid;
                updateCommand.Parameters[2].DbType = DbType.Int64;
                updateCommand.Parameters[0].Value = _userId;
                updateCommand.Parameters[1].SourceColumn = "uniqueid";
                updateCommand.Parameters[2].SourceColumn = "activityid";

                NpgsqlDataAdapter postgresqlAdapter = new NpgsqlDataAdapter(command);
                postgresqlAdapter.UpdateCommand = updateCommand;

                postgresqlAdapter.Fill(indexDS);

                DataTable photos = indexDS.Tables[0];
                DataTable activities = indexDS.Tables[1];
                _countIndexed = 0;

                foreach (DataRow photo in photos.Rows)
                {
                    foreach (DataRow activity in activities.Rows)
                    {
                        if ((Double)photo["gpsLat"] != 0.0)
                        {
                            // We have GPS coordinates for the photo
                            if (IsSameTime(photo, activity, 4) && IsSameLocation(photo, activity, 1.0))
                            {
                                photo["activityid"] = activity["activityid"];
                                Debug.WriteLine("Indexed Photoid Location and Time: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
                                Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
                                Debug.WriteLine("Photo loc: {0}, {1} Activity loc: {2}, {3} ", photo["gpslat"], photo["gpslong"], activity["latitude"], activity["longitude"]);
                                Debug.WriteLine("");
                                _countIndexed++;
                                break;
                            }
                        }
                        else
                        {
                            // We do NOT have GPS coordinates for the photo
                            if (IsSameTime(photo, activity, 4))
                            {
                                photo["activityid"] = activity["activityid"];
                                _countIndexed++;
                                Debug.WriteLine("Indexed Photoid Time only: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
                                Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
                                Debug.WriteLine("");
                                break;
                            }
                        }
                    }
                }

                t.Commit();

                // now write changes back to the database
                DataSet changeDS = indexDS.GetChanges(DataRowState.Modified);
                if (changeDS != null)
                {
                    postgresqlAdapter.Update(changeDS);
                    indexDS.Merge(changeDS);
                    indexDS.AcceptChanges();
                }
                _conn.Close();
                return _countIndexed;
            }
            catch (System.Data.DBConcurrencyException daex)
            {
                _conn.Close();
                Debug.WriteLine("Exception Data {0}", daex.Data);
                throw new MyLoDataStoreException(daex.Message, daex);
            }
            catch (NpgsqlException npex)
            {
                _conn.Close();
                throw new MyLoDataStoreException(npex.Message, npex);
            }
            catch (Exception ex)
            {
                _conn.Close();
                throw new MyLoDataStoreException(ex.Message);
            }
        }

        /// <summary>
        /// Warning! This method does not support transactions!
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="tableName"></param>
        /// <param name="insertQuery"></param>
        /// <param name="updateQuery"></param>
        public override void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery)
        {
            #region Input Validation

            if (dataTable == null)
            {
                throw new ArgumentNullException("DataTable");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("TableName");
            }
            if (insertQuery == null)
            {
                throw new ArgumentNullException("InsertQuery");
            }
            if (updateQuery == null)
            {
                throw new ArgumentNullException("UpdateQuery");
            }
            #endregion Input Validation

            IDbConnection connection = GetConnection(connectionString);
            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();

            string edittedUpdateQuery = updateQuery.SqlStatement;
            //edittedUpdateQuery = updateQuery.SqlStatement.Replace("@OldValue", "`@OldValue`");
            //edittedUpdateQuery = edittedUpdateQuery.Replace("@NewValue", "`@NewValue`");

            adapter.InsertCommand = (NpgsqlCommand)GetCommand(insertQuery.SqlStatement, connection, insertQuery.Parameters);
            adapter.UpdateCommand = (NpgsqlCommand)GetCommand(edittedUpdateQuery, connection, updateQuery.Parameters);

            try
            {
                //Logger.Log(insertQuery);
                //Logger.Log(updateQuery);
                adapter.Update(dataTable);
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error updating data.", ex);
            }
        }

        /// <summary>
        /// Indexes a MyLo datastore using PostgreSQL function 'SetupIndexCursorsIntervalAndTimePoints'
        /// </summary>
        public int ExecuteIndexerOnDataStore()
        {
            try
            {
                DataSet indexDS = new DataSet();

                NpgsqlTransaction t = _conn.BeginTransaction();
                NpgsqlCommand command = new NpgsqlCommand("SetupIndexCursorsOrdered", _conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new NpgsqlParameter());
                command.Parameters[0].DbType = DbType.Int64;
                command.Parameters[0].Value = _userId;

                NpgsqlCommand updateCommandforPhotos = new NpgsqlCommand("UpdatePhotoActivityId", _conn);
                updateCommandforPhotos.CommandType = CommandType.StoredProcedure;
                updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
                updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
                updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
                updateCommandforPhotos.Parameters[0].DbType = DbType.Int64;
                updateCommandforPhotos.Parameters[1].DbType = DbType.Guid;
                updateCommandforPhotos.Parameters[2].DbType = DbType.Int64;
                updateCommandforPhotos.Parameters[0].Value = _userId;
                updateCommandforPhotos.Parameters[1].SourceColumn = "uniqueid";
                updateCommandforPhotos.Parameters[2].SourceColumn = "activityid";

                NpgsqlDataAdapter postgresqlAdapterForPhotos = new NpgsqlDataAdapter(command);
                postgresqlAdapterForPhotos.UpdateCommand = updateCommandforPhotos;

                NpgsqlCommand commandForActivities = new NpgsqlCommand("SetupIndexCursorsIntervalAndTimePoints", _conn);
                commandForActivities.CommandType = CommandType.StoredProcedure;
                commandForActivities.Parameters.Add(new NpgsqlParameter());
                commandForActivities.Parameters.Add(new NpgsqlParameter());
                commandForActivities.Parameters.Add(new NpgsqlParameter());
                commandForActivities.Parameters[0].DbType = DbType.Int64;
                commandForActivities.Parameters[1].DbType = DbType.DateTime;
                commandForActivities.Parameters[2].DbType = DbType.Int32;
                commandForActivities.Parameters[0].Value = _userId;
                NpgsqlDataAdapter postgresqlAdapterForActivities = new NpgsqlDataAdapter(commandForActivities);

                postgresqlAdapterForPhotos.Fill(indexDS);

                DataTable photos = indexDS.Tables[0];
                _countIndexed = 0;

                foreach (DataRow photo in photos.Rows)
                {
                    DataSet activitiesDS = new DataSet();
                    // TODO - remove the Fill when datetaken time is still within the last retrieved activity interval
                    commandForActivities.Parameters[1].Value = photo["datetaken"];
                    // TODO - make this hours radius a variable set in UI
                    commandForActivities.Parameters[2].Value = 4;
                    postgresqlAdapterForActivities.Fill(activitiesDS);
                    DataTable activities = activitiesDS.Tables[0];

                    // TODO - the function returns the data sorted by duration, but this order is not preserved by ADO.Net!! Need to investigate
                    // and avoid the use of the ADO.Net sorted view.
                    DataView activitiesView = activities.DefaultView;
                    activitiesView.Sort = "duration ASC";
                    DataTable activitiesSorted = activitiesView.ToTable();

                    //foreach (DataRow activity in activities.Rows)
                    foreach (DataRow activity in activitiesSorted.Rows)
                    {
                        if ((Double)photo["gpsLat"] != 0.0)
                        {
                            if (IsSameLocation(photo, activity, 2.0))
                            {
                                photo["activityid"] = activity["activityid"];
                                Debug.WriteLine("Indexed Photoid Location and Time: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
                                Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
                                Debug.WriteLine("Photo loc: {0}, {1} Activity loc: {2}, {3} ", photo["gpslat"], photo["gpslong"], activity["latitude"], activity["longitude"]);
                                Debug.WriteLine("");
                                _countIndexed++;
                                break;
                            }
                        }
                        else
                        {
                            photo["activityid"] = activity["activityid"];
                            _countIndexed++;
                            Debug.WriteLine("Indexed Photoid Time only: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
                            Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
                            Debug.WriteLine("");
                            break;
                        }
                    }
                }

                t.Commit();

                // now write changes back to the database
                DataSet changeDS = indexDS.GetChanges(DataRowState.Modified);
                if (changeDS != null)
                {
                    postgresqlAdapterForPhotos.Update(changeDS);
                    indexDS.Merge(changeDS);
                    indexDS.AcceptChanges();
//.........这里部分代码省略.........