Another Shot at this one

Got a question or comment about PowerChurch that doesn't belong anywhere else? Why not post it here!?

Another Shot at this one

Postby alexhortdog95 » Thu Jun 07, 2018 6:51 pm

Good evening all,

So - I love the fact that you can download your Powerchurch DB into an Access DB. Love that.

But I'm wondering if there was a schema list of what each table is? Be nice to know so I can really start to use some things here :mrgreen:
alexhortdog95
 
Posts: 7
Joined: Wed Jan 03, 2018 7:39 pm

Re: Another Shot at this one

Postby NeilZ » Thu Jun 07, 2018 9:28 pm

alexhortdog95 wrote:Good evening all,

So - I love the fact that you can download your Powerchurch DB into an Access DB. Love that.

But I'm wondering if there was a schema list of what each table is? Be nice to know so I can really start to use some things here :mrgreen:


AFAIK .. they've never released a schema.

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8905
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Another Shot at this one

Postby Zorak » Fri Jun 08, 2018 8:10 am

We have published Knowledge Base articles that list what general information is stored in each table:

https://www.powerchurch.com/support/kb. ... data+files
Zorak
Tech Support
Tech Support
 
Posts: 2490
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Another Shot at this one

Postby alexhortdog95 » Tue Jul 02, 2019 9:31 am

Been a while since I replied. Thanks for the list of data files, helped out tremendously.

I developed a quick and dirty WinApp program to update my emails as needed, based on the input being a comma delimited file where the emails were kept:

Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace UpdateEmails
{
    class Program
    {
        static void Main(string[] args)
        {
            EmailUpdater();
        }


        public static bool EmailUpdater()
        {

            try
            {
                var conString = System.Configuration.ConfigurationManager.ConnectionStrings["PowerChurch"].ConnectionString;

                string rec;


                using (OleDbConnection connection = new OleDbConnection(conString.ToString()))
                {
                    connection.Open();
                    string sql = null;
                    OleDbDataAdapter adapter = new OleDbDataAdapter();

                    //start of loop using the streamreader here
                    using (StreamReader reader = new StreamReader("B:\\somefile.csv"))
                    {
                        reader.ReadLine(); //skip the header row of the file.

                        while ((rec = reader.ReadLine()) != null)
                        {

                            //parse the input file.
                            string[] valArr = new string[] { };
                            string[] splitChar = new string[] { "," };
                            valArr = rec.Split(splitChar, StringSplitOptions.None);

                            sql = "UPDATE ME SET E_MAIL = '" + valArr[0] + "' WHERE ENV_NO = " + Convert.ToInt32(valArr[1]);

                            //try to update the line here
                            adapter.UpdateCommand = connection.CreateCommand();
                            adapter.UpdateCommand.CommandText = sql;
                            int updateRows = adapter.UpdateCommand.ExecuteNonQuery();
                        }

                    }

                    //End of loop
                    sql = "SELECT * FROM ME WHERE E_MAIL <> ''";

                    adapter.SelectCommand = connection.CreateCommand();
                    adapter.SelectCommand.CommandText = sql;


                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "ME");

                    DataTable dt = new DataTable();
                    dt = ds.Tables["ME"];

                    using(StreamWriter writer = new StreamWriter("B:\\EmailOutput.csv"))
                    {
                        StringBuilder sb = new StringBuilder(128);
                        sb.Append("Last Name,First Name,Email,Envelope Number");
                        writer.WriteLine(sb.ToString());

                        foreach (DataRow row in dt.Rows)
                        {
                            sb = new StringBuilder(128);
                            sb.Append(row["lastname"].ToString().Trim()).Append(",");
                            sb.Append(row["firstname"].ToString().Trim()).Append(",");
                            sb.Append(row["e_mail"].ToString().Trim()).Append(",");
                            sb.Append(row["env_no"].ToString().Trim()).Append(",");
                            writer.WriteLine(sb.ToString());
                        }

                    }

 
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

    }
}


alexhortdog95
 
Posts: 7
Joined: Wed Jan 03, 2018 7:39 pm

Re: Another Shot at this one

Postby Zorak » Tue Jul 02, 2019 11:17 am

Is this code updating an Access db, or the PowerChurch data files directly?

If it's just the Access db, then this looks safe enough.

Creating the CSV output at the end could potentially have problems if the last name or first name fields have commas in them. Sometimes users enter "Smith, Jr" in the last name field rather than using the provided suffix field. That would break the CSV file if you don't escape commas.

If you are updating PowerChurch data files directly, you will want to make sure that no one is working in PowerChurch at the time you are running the process, as it could corrupt the table.

Another improvement that could be made, just to have fewer table updates, would be to look at the current e-mail address in the db before updating. It looks like this is updating every record every time regardless.
Zorak
Tech Support
Tech Support
 
Posts: 2490
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Another Shot at this one

Postby alexhortdog95 » Tue Jul 02, 2019 4:35 pm

Zorak wrote:Is this code updating an Access db, or the PowerChurch data files directly?

If it's just the Access db, then this looks safe enough.

Creating the CSV output at the end could potentially have problems if the last name or first name fields have commas in them. Sometimes users enter "Smith, Jr" in the last name field rather than using the provided suffix field. That would break the CSV file if you don't escape commas.

If you are updating PowerChurch data files directly, you will want to make sure that no one is working in PowerChurch at the time you are running the process, as it could corrupt the table.

Another improvement that could be made, just to have fewer table updates, would be to look at the current e-mail address in the db before updating. It looks like this is updating every record every time regardless.


The situation we had was that we had users without an email address in the ME table. We gathered this information from our congregants and updated using this application. The application only reads the records in from the CSV file. If the email isn't in the CSV, it won't do a lookup from PowerChurch. It also uses the System.Data.OleDb dll to link directly to PowerChurch. It would only run after business hours when no users are logged into PowerChurch.

The Writer at the end grabs all of the records in the table and outputs them into another csv file.
alexhortdog95
 
Posts: 7
Joined: Wed Jan 03, 2018 7:39 pm

Re: Another Shot at this one

Postby Zorak » Tue Jul 02, 2019 4:38 pm

Makes sense. Good job!
Zorak
Tech Support
Tech Support
 
Posts: 2490
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software