I am trying to create a timer job that will read a list of new customers from a SQL server db, create a site for the customer and then update the table with the site's id. I have no trouble reading and creating the site but I can't seem to update the table. Am I missing something? Here is the code that I am using:
public override void Execute(Guid targetInstanceId)
{
SPWebApplication application = this.Parent as SPWebApplication;
SPSite site = application.Sites[0];
using (AccountPortalDataContext context = new AccountPortalDataContext(CONNECTION_STRING))
{
context.UpdateProject(1, null, true);
context.SubmitChanges();
var newProjects = context.Projects.Where(p => p.Flag == false);
foreach (var project in newProjects)
{
SPWeb web = site.AllWebs.Add(
project.Id.ToString(),
project.ProjectName,
"Project Web",
1033,
site.GetWebTemplates(1033)[1],
false,
false);
project.Flag = true;
project.WebId = web.ID;
context.SubmitChanges();
}
base.Execute(targetInstanceId);
}
}
I have also tried creating a stored procedure and calling that with both linq:
context.UpdateProject(project.Id, project.WebId, project.Flag);
and SQLcommands:
SqlConnection connection = new SqlConnection(CONNECTION_STRING);
connection.Open();
SqlCommand command = new SqlCommand("UpdateProject",connection);
command.Parameters.AddWithValue("@id", 1);
command.Parameters.AddWithValue("@webId", Guid.NewGuid());
command.Parameters.AddWithValue("@flag", true);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
connection.Dispose();
When running the sqlcommand version I get the following error:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
I have also given the user sp_workerprocess db_owner access to the database to try and make sure that is not causing the issue. Thanks for any help!