Sitecore, C#, and SQL: Removing Form Data

Sitecore, C#, and SQL: Removing Form Data

Do you have too much data in your SQL database from the Sitecore Forms submit action and need to delete some? Maybe you need to comply with government regulations and delete personal information every few months? If so, you’ve come to the right blog. This blog will help you understand where the Sitecore Form data is stored and how to use C# to delete it.

Understanding Where the Form Data is Stored

If you have the form submit action set to Save Data, it is stored in SQL Server under the <your-sitecore-instance>_ExperienceForms. You will notice there are two tables: Field_Data, and FormEntry. </your-sitecore-instance>

Field_Data is where all the data, such as names, emails, etc., are stored, and the FormEntry stores the relationship between the FieldData and FormID

Field_Data has a foreign key connected to FormEntry, so we only need to delete the data in FormEntry, and the corresponding data in Field_Data will be deleted. No need for any messy JOINS!

Connecting to the SQL Database and Running the Query

Connecting to the SQL DB is actually quite easy. If your Sitecore instance is set up properly, there will be a file named ConnectionStrings.config in your wwwroot/App_Config folder. We don’t have to edit anything, but open it up to see an entry that says experienceforms. We will be connecting to that database.

Here is the C# code to connect to the database and delete from the Form Entry table. Read the comments for more information!

// connect to the experienceforms SQL database
var connectionString = ConfigurationManager.ConnectionStrings["experienceforms"].ConnectionString;

// open up a new sql connection
SqlConnection sqlConnection = new SqlConnection(connectionString);       
try
{

  using (SqlConnection)
  {
      SqlConnection.Open();
            //query to run on the DB. This will delete everything from FormEntry
      string sqlQuery = string.Format("DELETE FROM [FormEntry]");
      var sqlCommand = new SqlCommand(sqlQuery, SqlConnection);
      var affected = sqlCommand.ExecuteNonQuery();

      Log.Info(string.Format("Delete Form Data: {0} lines affected", affected.ToString()), this);
    }
}
catch (SqlException sqlex)
{
    Log.Error(string.Format("Delete Form Data: SQL Error: {0}", sqlex.Message), this);
}
catch (Exception ex)
{
    Log.Error(string.Format("Delete Form Data: Error: {0}", ex.Message), this);
}
finally
{
//close sql connection
    SqlConnection.Close();
}

Right now, the SQL query will delete everything in the FormEntry database, so only run it if you’re sure. I would recommend adding a WHERE clause to filter out the specific data you want to delete.

Conclusion

I hope this blog helped teach you where the Form data actually went and how to remove it.

---