public static TimeZoneInfo[] GetZones(DBHelper dbh, int langId)
{
List<TimeZoneInfo> list = new List<TimeZoneInfo>();
using (IDataReader reader = dbh.RunSPDataReader("TimeZonesGet", DBHelper.MP("@LanguageId", SqlDbType.Int, langId)))
{
while (reader.Read())
{
TimeZoneInfo item = new TimeZoneInfo();
item.TimeZoneId = (int)reader["TimeZoneId"];
item.Bias = (int)reader["Bias"];
item.StandardBias = (int)reader["StandardBias"];
item.DaylightBias = (int)reader["DaylightBias"];
item.DaylightMonth = (int)reader["DaylightMonth"];
item.DaylightDayOfWeek = (int)reader["DaylightDayOfWeek"];
item.DaylightWeek = (int)reader["DaylightWeek"];
item.DaylightHour = (int)reader["DaylightHour"];
item.StandardMonth = (int)reader["StandardMonth"];
item.StandardDayOfWeek = (int)reader["StandardDayOfWeek"];
item.StandardWeek = (int)reader["StandardWeek"];
item.StandardHour = (int)reader["StandardHour"];
list.Add(item);
}
}
return list.ToArray();
}
public DatabaseConfigurator(string applicationPhysicalPath)
{
_applicationPhysicalPath = applicationPhysicalPath;
_toolsDir = Path.Combine(applicationPhysicalPath, @"..\..\tools\");
_dbHelper = new DBHelper(ConfigurationManager.AppSettings["ConnectionString"]);
_dbHelper.CommandTimeout = 600;
}
public static void LoadList(DBHelper source, int metaClassId, ArrayList fields)
{
using (IDataReader reader = source.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT F.Name, F.Length, T.SqlName, F.AllowNulls, T.Variable, T.DefaultValue FROM MetaClassMetaFieldRelation R JOIN MetaField F ON F.MetaFieldId = R.MetaFieldId JOIN MetaDataType T ON T.DataTypeId = F.DataTypeId WHERE R.MetaClassId = {0} AND F.SystemMetaClassId = 0 ORDER BY F.MetaFieldId", metaClassId)))
{
while (reader.Read())
{
MyMetaField item = Load(reader);
fields.Add(item);
}
}
}
public static void SaveRolePrincipals(DBHelper target, string tableName, int objectId, int roleId, IEnumerable<int> principals)
{
foreach (int principalId in principals)
{
target.RunText(string.Concat("INSERT INTO [", tableName, "] ([ObjectId],[PrincipalId],[RoleId]) VALUES (@p1,@p2,@p3)")
, DBHelper.MP("@p1", SqlDbType.Int, objectId)
, DBHelper.MP("@p2", SqlDbType.Int, principalId)
, DBHelper.MP("@p3", SqlDbType.Int, roleId)
);
}
}
private DBTransaction _transaction; // commit, dispose
#endregion Fields
#region Constructors
public MetadataContext(DBHelper dbHelper)
{
_previousContext = DataContext.Current;
_transaction = dbHelper.BeginTransaction();
_currentContext = new DataContext(string.Empty);
_currentContext.SqlContext.CommandTimeout = dbHelper.CommandTimeout;
_currentContext.SqlContext.Transaction = _transaction.SqlTran;
DataContext.Current = _currentContext;
}
internal void Load(DBHelper dbHelper)
{
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [LanguageId],[Locale],[FriendlyName] FROM [LANGUAGES] WHERE [IsDefault]=1"))
{
if (reader.Read())
{
LanguageId = (int)reader[0];
Locale = reader[1].ToString();
FriendlyName = reader[2].ToString();
}
}
}
internal static IList<CompanyInfo> LoadList(DBHelper source)
{
List<CompanyInfo> ret = new List<CompanyInfo>();
using (IDataReader reader = source.RunTextDataReader("SELECT [company_id], [domain], [company_name], [db_name] FROM [COMPANIES]"))
{
while (reader.Read())
{
CompanyInfo item = Load(reader);
ret.Add(item);
}
}
return ret;
}
internal static string GetPortalParameterValue(DBHelper dbHelper, string name)
{
string result = null;
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [Value] FROM [PortalConfig] WHERE [Key]=@Key"
, DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name)))
{
if (reader.Read())
result = reader[0].ToString();
}
return result;
}
public void CreateTables(DBHelper target)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
StringBuilder sb3 = new StringBuilder();
target.RunText(string.Format(CultureInfo.InvariantCulture, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[{0}]", TableName));
target.RunText(string.Format(CultureInfo.InvariantCulture, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[{0}]", TableNameHistory));
sb.Append(string.Format(CultureInfo.InvariantCulture, "CREATE TABLE [dbo].[{0}] ([ObjectId] [int] NOT NULL, [CreatorId] [int] NULL, [Created] [datetime] NULL, [ModifierId] [int] NULL, [Modified] [datetime] NULL", TableName));
sb2.Append(string.Format(CultureInfo.InvariantCulture, "CREATE TABLE [dbo].[{0}] ([Id] [int] IDENTITY (1, 1) NOT NULL, [ObjectId] [int] NOT NULL, [ModifierId] [int] NULL, [Modified] [datetime] NULL", TableNameHistory));
sb3.Append(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] ADD ", TableName));
string column;
int defaultCount = 0;
foreach (MyMetaField field in Fields)
{
column = string.Format(CultureInfo.InvariantCulture, ", [{0}] [{1}] {2}", field.Name, field.SqlName, field.Variable ? ("(" + field.Length + ") ") : "");
sb.Append(column);
sb2.Append(column);
if (!field.AllowNulls)
sb.Append("NOT ");
sb.Append("NULL");
sb2.Append("NULL");
if (!field.AllowNulls)
{
if (defaultCount > 0)
sb3.Append(", ");
sb3.Append(string.Format(CultureInfo.InvariantCulture, "CONSTRAINT [DF__{0}__{1}] DEFAULT ({2}) FOR [{1}]", TableName, field.Name, (field.DefaultValue.Length > 0 ? field.DefaultValue : "''")));
defaultCount++;
}
}
sb.Append(") ON [PRIMARY]");
sb2.Append(") ON [PRIMARY]");
target.RunText(sb.ToString());
target.RunText(sb2.ToString());
target.RunText(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([ObjectId]) ON [PRIMARY]", TableName));
target.RunText(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]", TableNameHistory));
if (defaultCount > 0)
target.RunText(sb3.ToString());
target.RunSP("mdpsp_sys_CreateMetaClassHistoryTrigger", DBHelper.MP("@MetaClassId", SqlDbType.Int, MetaClassId));
//if(!Name.StartsWith("ListsEx_"))
target.RunSP("mdpsp_sys_CreateMetaClassProcedure", DBHelper.MP("@MetaClassId", SqlDbType.Int, MetaClassId));
}
public static ArrayList LoadList(DBHelper source)
{
ArrayList ret = new ArrayList();
using (IDataReader reader = source.RunTextDataReader("SELECT [MetaClassId], [Name], [TableName] FROM [MetaClass] WHERE [IsSystem] = 0 AND [IsAbstract] = 0"))
{
while (reader.Read())
{
MyMetaClass item = Load(reader);
if (item.Name != "AssetsEx")
ret.Add(item);
}
}
return ret;
}
internal static void SetPortalParameterValue(DBHelper dbHelper, string name, string value)
{
if (value == null)
{
dbHelper.RunText("DELETE FROM [PortalConfig] WHERE [Key]=@Key"
, DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name)
);
}
else
{
dbHelper.RunText("IF EXISTS (SELECT 1 FROM [PortalConfig] WHERE [Key]=@Key) UPDATE [PortalConfig] SET [Value]=@Value WHERE [Key]=@Key ELSE INSERT INTO [PortalConfig] ([Key],[Value]) VALUES (@Key,@Value)"
, DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name)
, DBHelper.MP("@Value", SqlDbType.NText, value)
);
}
}
public static void SaveList(DBHelper target, IDictionary<int, ListFolder> folders)
{
List<ListFolder> list = new List<ListFolder>(folders.Values);
list.Sort(Compare);
const string targetTableName = "cls_ListFolder";
IbnConverter.SetInsertIdentity(target, targetTableName, true);
foreach (ListFolder folder in list)
{
folder.Save(target);
}
IbnConverter.SetInsertIdentity(target, targetTableName, false);
}
public static IDictionary<int, ListFolder> LoadList(DBHelper source)
{
Dictionary<int, ListFolder> ret = new Dictionary<int, ListFolder>();
using (IDataReader reader = source.RunTextDataReader("SELECT * FROM [LIST_FOLDERS]"))
{
while (reader.Read())
{
ListFolder item = Load(reader);
if (!ret.ContainsKey(item._listFolderId))
ret.Add(item._listFolderId, item);
}
}
// Calculate OutlineLevel, OutlineNumber, HasChildren.
foreach (ListFolder folder in ret.Values)
folder.CalculateOutline(ret);
return ret;
}
internal static IConfigurationParameter[] ListPortalParameters(DBHelper dbHelper)
{
List<IConfigurationParameter> list = new List<IConfigurationParameter>();
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [Key],[Value] FROM [PortalConfig] ORDER BY [Key]"))
{
while (reader.Read())
{
string name = reader[0].ToString();
string value = reader[1].ToString();
if (!name.StartsWith("system.", StringComparison.OrdinalIgnoreCase))
{
ConfigurationParameter item = new ConfigurationParameter(name, value);
list.Add(item);
}
}
}
return list.ToArray();
}
public static void Convert(DBHelper dbHelper)
{
List<MetaViewInfo> views = new List<MetaViewInfo>();
using (MetadataContext context = new MetadataContext(dbHelper))
{
// Load MetaView and MetaViewPreference objects for each List_* class.
//Console.WriteLine(DateTime.Now);
Regex listDetector = new Regex(@"List_\d+");
MetaClassManager metaClassManager = DataContext.Current.MetaModel;
foreach (MetaClass metaClass in metaClassManager.MetaClasses)
{
if (listDetector.IsMatch(metaClass.Name))
{
foreach (MetaView metaView in metaClassManager.GetMetaViews(metaClass))
{
MetaViewInfo view = new MetaViewInfo();
view.Load(metaView);
views.Add(view);
}
}
}
// Save ListViewProfile.
//Console.WriteLine(DateTime.Now);
foreach (MetaViewInfo view in views)
{
view.SaveListViewProfiles();
}
context.Commit();
//Console.WriteLine(DateTime.Now);
}
}
public static User[] GetUsers(DBHelper dbh, int groupId, int alertGroupID)
{
List<User> list = new List<User>();
using (IDataReader reader = dbh.RunTextDataReader("SELECT PrincipalId, Login, Password, FirstName, LastName, Email, Activity, salt, [hash] FROM [USERS] WHERE Login IS NOT NULL AND Password IS NOT NULL AND IsExternal = 0"))
{
while (reader.Read())
{
User item = new User();
item.principalId = (int)reader["PrincipalId"];
item.login = reader["Login"].ToString();
item.password = reader["Password"].ToString();
item.firstName = reader["FirstName"].ToString();
item.lastName = reader["LastName"].ToString();
item.email = reader["Email"].ToString();
if (reader["salt"] != DBNull.Value)
item.salt = reader["salt"].ToString();
if (reader["hash"] != DBNull.Value)
item.hash = reader["hash"].ToString();
item.isActive = (byte)reader["Activity"] == 3;
if (string.Compare(item.login, "alert", StringComparison.OrdinalIgnoreCase) != 0)
item.imGroupId = groupId;
else
item.imGroupId = alertGroupID;
list.Add(item);
}
}
return list.ToArray();
}
private static void SaveRolePrincipals(DBHelper target, int objectId, int roleId, IEnumerable<int> principals)
{
foreach (int principalId in principals)
{
target.RunText("INSERT INTO [cls_TimeTrackingBlock_Role_Principal] ([ObjectId],[PrincipalId],[RoleId]) VALUES (@p1,@p2,@p3)"
, DBHelper.MP("@p1", SqlDbType.Int, objectId)
, DBHelper.MP("@p2", SqlDbType.Int, principalId)
, DBHelper.MP("@p3", SqlDbType.Int, roleId)
);
}
}
private static NameValueCollection LoadList(DBHelper source, string commandText)
{
NameValueCollection ret = new NameValueCollection();
using (IDataReader reader = source.RunTextDataReader(commandText))
{
while (reader.Read())
{
ret.Add(reader["ItemId"].ToString(), reader["ItemName"].ToString());
}
}
return ret;
}
private void ClearTables(XmlNodeList tables, DBHelper target)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.Text;
for (int i = tables.Count - 1; i >= 0; i--)
{
string tableName = tables[i].Attributes["name"].Value;
XmlAttribute del = tables[i].Attributes["delete"];
if (del == null || del.Value == "1")
{
SendProgress("Clearing table: {0}", tableName);
StringBuilder sb = new StringBuilder();
sb.Append("DELETE [");
sb.Append(tableName);
sb.Append("]");
XmlAttribute where = tables[i].Attributes["where"];
if (where != null)
{
sb.Append(" WHERE ");
sb.Append(where.Value);
}
cmd.CommandText = sb.ToString();
target.RunCmd(cmd);
SendProgress("Done");
}
}
}
}
private void CopyPortalTables(int currentPass, int maxPass, XmlNodeList tablesCopy, DBHelper source, DBHelper target, IList<StringCollection> nameLists, IList<Hashtable> valueHashTables)
{
SendProgress("* Copying tables. Pass {0} of {1} *", currentPass, maxPass);
string tableName, selectCommand;
//NameValueCollection columnsMapping = new NameValueCollection();
foreach (XmlNode table in tablesCopy)
{
XmlAttribute aCopy = table.Attributes["copy"];
XmlAttribute aPass = table.Attributes["pass"];
bool copy = (aCopy == null || aCopy.Value == "1");
int tPass = (aPass == null ? 1 : int.Parse(aPass.Value, CultureInfo.InvariantCulture));
if (copy && tPass == currentPass)
{
tableName = table.Attributes["name"].Value;
bool copyBinary = true;
{
XmlAttribute attr = table.Attributes["copyBinary"];
if (attr != null)
copyBinary = bool.Parse(attr.Value);
}
selectCommand = string.Empty;
XmlAttribute where = table.Attributes["where"];
if (where != null)
selectCommand = string.Concat("WHERE ", where.Value);
switch (tableName)
{
case "CALENDARS":
// Fill ProjectId column with NULLs
StringCollection namesProjectId = new StringCollection();
namesProjectId.Add("projectid");
List<StringCollection> names = new List<StringCollection>();
names.Add(namesProjectId);
Hashtable valuesProjectId = new Hashtable();
List<Hashtable> values = new List<Hashtable>();
values.Add(valuesProjectId);
CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, names, values, true, null);
break;
case "CONTAINERSHIP":
CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, null, null, false, null);
break;
case "USERS":
CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, true, null);
break;
// TODO: Update IMGroupId for partner groups
default:
CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, null, null, true, null);
break;
}
}
}
SendProgress("* Done *");
}
public static IDictionary<int, Project> LoadList(DBHelper source)
{
Dictionary<int, Project> ret = new Dictionary<int, Project>();
using (IDataReader reader = source.RunTextDataReader("SELECT [ProjectId],[Title],[StatusId],[ManagerId],[ExecutiveManagerId] FROM [PROJECTS]"))
{
while (reader.Read())
{
Project item = Load(reader);
if (!ret.ContainsKey(item._projectId))
ret.Add(item._projectId, item);
}
}
foreach (Project project in ret.Values)
{
using (IDataReader reader = source.RunTextDataReader("SELECT [PrincipalId],[IsTeamMember],[IsSponsor],[IsStakeHolder] FROM [PROJECT_MEMBERS] WHERE [ProjectId]=@p1", DBHelper.MP("@p1", SqlDbType.Int, project._projectId)))
{
while (reader.Read())
{
int principalId = (int)reader["PrincipalId"];
if ((bool)reader["IsTeamMember"])
project._teamMembers.Add(principalId);
if ((bool)reader["IsSponsor"])
project._sponsors.Add(principalId);
if ((bool)reader["IsStakeHolder"])
project._stakeholders.Add(principalId);
}
}
}
return ret;
}
public void SaveProjectRoles(DBHelper target, string tableName, int objectId)
{
Helper.SaveRolePrincipals(target, tableName, objectId, 1, _managerId); // ProjectManager
if (_executiveManagerId != null)
Helper.SaveRolePrincipals(target, tableName, objectId, 2, _executiveManagerId.Value); // ExecutiveManager
Helper.SaveRolePrincipals(target, tableName, objectId, 3, _sponsors); // ProjectSponsor
Helper.SaveRolePrincipals(target, tableName, objectId, 4, _stakeholders); // ProjectStakeholder
Helper.SaveRolePrincipals(target, tableName, objectId, 5, _teamMembers); // ProjectTeamMember
}
public void Save(DBHelper target)
{
target.RunText(
"INSERT INTO [cls_Project] ([ProjectId],[Title],[StatusId]) VALUES (@p1,@p2,@p3)"
, DBHelper.MP("@p1", SqlDbType.Int, _projectId)
, DBHelper.MP("@p2", SqlDbType.NVarChar, 100, _title)
, DBHelper.MP("@p3", SqlDbType.Int, _statusId)
);
SaveProjectRoles(target, "cls_Project_Role_Principal", _projectId);
_blockTypeInstanceId = target.RunTextInteger(
"INSERT INTO [cls_TimeTrackingBlockTypeInstance] ([Title],[BlockTypeId],[ProjectId]) VALUES (@p1,@p2,@p3) SELECT @retval = SCOPE_IDENTITY()"
, DBHelper.MP("@p1", SqlDbType.NVarChar, 100, _title)
, DBHelper.MP("@p2", SqlDbType.Int, ProjectTTBlockTypeId)
, DBHelper.MP("@p3", SqlDbType.Int, _projectId)
);
SaveProjectRoles(target, "cls_TimeTrackingBlockTypeInstance_Role_Principal", _blockTypeInstanceId);
}
public void CopyTable(string tableName, string selPrefix, string selColumns, string selCondition, bool copyBinary, DBHelper source, DBHelper target, IList<StringCollection> nameLists, IList<Hashtable> valueHashTables, bool insertIdentity, Hashtable identities)
{
CopyTable(tableName, tableName, null, selPrefix, selColumns, selCondition, copyBinary, source, target, nameLists, valueHashTables, insertIdentity, identities);
}
public static void Init(DBHelper dbHelper)
{
_dbHelper = dbHelper;
}
private static void SaveRolePrincipals(DBHelper target, int objectId, int roleId, int principalId)
{
SaveRolePrincipals(target, objectId, roleId, new int[] { principalId });
}
private static void UpdateRecord(string tableName, string sourceCondition, string targetCondition, ArrayList skip, DBHelper source, DBHelper target)
{
string colName;
DataTable dt;
bool isIdentity;
ArrayList columns = new ArrayList();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
using (IDataReader reader = source.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}] {1}", tableName, sourceCondition)))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
colName = (string)row["ColumnName"];
if (!skip.Contains(colName) && CheckColumn(target, cmd, tableName, colName, out isIdentity))
columns.Add(colName);
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb.Append("SELECT ");
sb2.Append("UPDATE [");
sb2.Append(tableName);
sb2.Append("] SET ");
for (int i = 0; i < columns.Count; i++)
{
colName = (string)columns[i];
if (i > 0)
{
sb.Append(", ");
sb2.Append(", ");
}
sb.Append("[");
sb.Append(colName);
sb.Append("]");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("] = @p");
sb2.Append(i);
}
sb.Append(" FROM [");
sb.Append(tableName);
sb.Append("] ");
sb.Append(sourceCondition);
sb2.Append(" ");
sb2.Append(targetCondition);
string selectCommand = sb.ToString();
string updateCommand = sb2.ToString();
// Read data from source and update target
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
cmd.CommandText = updateCommand;
cmd.Parameters.Clear();
if (reader.Read())
{
// Create parameters
for (int i = 0; i < columns.Count; i++)
{
cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)columns[i], reader[i], null, null));
if ((Type)dt.Rows[i]["DataType"] == typeof(byte[]))
cmd.Parameters[i].SqlDbType = SqlDbType.Binary;
}
target.RunCmd(cmd);
}
}
}
private void CopyTable(string sourceTableName, string targetTableName, NameValueCollection columnsMapping, string selPrefix, string selColumns, string selCondition, bool copyBinary, DBHelper source, DBHelper target, IList<StringCollection> nameLists, IList<Hashtable> valueHashTables, bool insertIdentity, Hashtable identities)
{
SendProgress("Copying data from table {0} to table {1}", sourceTableName, targetTableName);
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
DataTable dt;
Type dataType;
bool isIdentity, hasIdentity = false;
string selectCommand;
string insertCommand;
ArrayList columnTypes = new ArrayList();
ArrayList sourceColumns = new ArrayList();
ArrayList targetColumns = new ArrayList();
ArrayList sourceBinColumns = new ArrayList();
ArrayList targetBinColumns = new ArrayList();
Hashtable binIdentities = new Hashtable();
string sourceIdentityColumn = null, targetIdentityColumn = null;
int identityIndex = -1;
string sourceColumnName, targetColumnName;
// Get column names to be inserted
selectCommand = string.Format(CultureInfo.InvariantCulture, "SELECT {0}{1} FROM [{2}] {3}", selPrefix, selColumns, sourceTableName, selCondition);
using (IDataReader reader = source.RunTextDataReaderSchemaOnly(selectCommand))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
sourceColumnName = (string)row["ColumnName"];
targetColumnName = GetTargetColumn(sourceColumnName, columnsMapping);
dataType = (Type)row["DataType"];
// Check if column exists and is identity
if (CheckColumn(target, cmd, targetTableName, targetColumnName, out isIdentity))
{
if (isIdentity)
{
hasIdentity = true;
sourceIdentityColumn = sourceColumnName;
targetIdentityColumn = targetColumnName;
}
if (dataType == typeof(byte[]))
{
sourceBinColumns.Add(sourceColumnName);
targetBinColumns.Add(targetColumnName);
}
else
{
columnTypes.Add(dataType);
sourceColumns.Add(sourceColumnName);
targetColumns.Add(targetColumnName);
}
}
}
}
// Build select command
sb.Append("SELECT ");
int n = 0;
foreach (string colName in sourceColumns)
{
if (n > 0)
sb.Append(", ");
sb.Append(selPrefix);
sb.Append("[");
sb.Append(colName);
sb.Append("]");
n++;
}
sb.Append(" FROM [");
sb.Append(sourceTableName);
sb.Append("] ");
sb.Append(selCondition);
// Build insert command
sb2.Append("INSERT INTO [");
sb2.Append(targetTableName);
sb2.Append("] (");
int j = n = 0;
foreach (string colName in targetColumns)
{
if (colName == targetIdentityColumn)
identityIndex = j;
if (insertIdentity || identityIndex != j)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("]");
n++;
}
j++;
}
sb2.Append(") VALUES (");
n = 0;
for (int i = 0; i < targetColumns.Count; i++)
{
if (insertIdentity || identityIndex != i)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("@p");
sb2.Append(i);
n++;
}
}
sb2.Append(")");
if (hasIdentity && !insertIdentity)
sb2.Append(" SELECT @@IDENTITY");
selectCommand = sb.ToString();
insertCommand = sb2.ToString();
// Read data from source and insert into target
using (DBTransaction tran = target.BeginTransaction())
{
object identity;
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, true);
try
{
cmd.CommandText = insertCommand;
while (reader.Read())
{
if (cmd.Parameters.Count == 0)
{
// Create parameters
for (int i = 0; i < targetColumns.Count; i++)
{
SqlParameter p = cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables));
if (columnTypes[i] == typeof(Decimal))
p.DbType = DbType.Decimal;
}
}
else
{
// Set values
for (int i = 0; i < targetColumns.Count; i++)
{
cmd.Parameters[i].Value = GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables);
}
}
identity = target.RunCmdScalar(cmd);
if (hasIdentity)
{
object oldIdentity = reader[identityIndex];
if (insertIdentity)
binIdentities[oldIdentity] = oldIdentity;
else
binIdentities[oldIdentity] = identity;
if (!insertIdentity && identities != null)
identities[oldIdentity] = identity;
}
}
}
finally
{
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, false);
}
}
tran.Commit();
}
// Copy binary and image fields
if (copyBinary)
{
if (sourceIdentityColumn != null && sourceBinColumns.Count > 0)
{
SqlParameter paramTextPtr = DBHelper.MP("@ptr", SqlDbType.VarBinary, 16, null);
SqlParameter paramData = new SqlParameter("@data", SqlDbType.VarBinary);
paramData.Value = _buffer;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@id";
foreach (object id in binIdentities.Keys)
{
paramId.Value = binIdentities[id];
for (int i = 0; i < sourceBinColumns.Count; i++)
{
CopyBinaryData(source, sourceTableName, (string)sourceBinColumns[i], sourceIdentityColumn, id,
target, targetTableName, (string)targetBinColumns[i], targetIdentityColumn,
cmd, paramTextPtr, paramData, paramId);
}
}
}
}
SendProgress("Done");
}
public void Save(DBHelper target, Project project)
{
_blockTypeInstanceId = project != null ? project.BlockTypeInstanceId : NonProjectTTBlockTypeInstanceId;
_blockId = target.RunTextInteger("INSERT INTO [cls_TimeTrackingBlock] ([Modified],[ModifierId],[Card],[Title],[BlockTypeInstanceId],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[OwnerId],[mc_StateId],[mc_StateMachineId],[StartDate],[ProjectId],[AreFinancesRegistered],[IsRejected]) VALUES (@p1,@p2,@p3,@p5,@p6,@p7,@p7,@p7,@p7,@p7,@p7,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14) SELECT @retval = SCOPE_IDENTITY()"
, DBHelper.MP("@p1", SqlDbType.DateTime, _lastSavedDate)
, DBHelper.MP("@p2", SqlDbType.Int, _lastEditorId)
, DBHelper.MP("@p3", SqlDbType.NVarChar, 50, "TimeTrackingBlockDescr")
, DBHelper.MP("@p5", SqlDbType.NVarChar, 100, _title)
, DBHelper.MP("@p6", SqlDbType.Int, _blockTypeInstanceId)
, DBHelper.MP("@p7", SqlDbType.Float, 0F)
, DBHelper.MP("@p8", SqlDbType.Int, _userId)
, DBHelper.MP("@p9", SqlDbType.Int, ConvertState(_statusId, _projectId > 0, ref _isRejected))
, DBHelper.MP("@p10", SqlDbType.Int, TTBlockStateMachineId)
, DBHelper.MP("@p11", SqlDbType.DateTime, _startDate)
, DBHelper.MP("@p12", SqlDbType.Int, _projectId > 0 ? (object)_projectId : DBNull.Value)
, DBHelper.MP("@p13", SqlDbType.Bit, _areFinancesRegistered)
, DBHelper.MP("@p14", SqlDbType.Bit, _isRejected)
);
if (!string.IsNullOrEmpty(_managerComments))
{
target.RunText("INSERT INTO [cls_TimeTrackingBlockDescr] ([TimeTrackingBlockId],[Description]) VALUES (@p1,@p2)"
, DBHelper.MP("@p1", SqlDbType.Int, _blockId)
, DBHelper.MP("@p2", SqlDbType.NText, _managerComments)
);
}
// Save role principals
Helper.SaveRolePrincipals(target, "cls_TimeTrackingBlock_Role_Principal", _blockId, 6, _userId); // Owner
if (project != null)
project.SaveProjectRoles(target, "cls_TimeTrackingBlock_Role_Principal", _blockId);
}
public static IDictionary<int, TTBlock> LoadList(DBHelper source)
{
Dictionary<int, TTBlock> ret = new Dictionary<int,TTBlock>();
using (IDataReader reader = source.RunTextDataReader("SELECT T.*, P.Title, S.ProjectSpreadSheetId FROM [WeekTimeSheet] T LEFT JOIN [PROJECTS] P ON P.ProjectId = T.ProjectId LEFT JOIN [ProjectSpreadSheet] S ON S.ProjectId = T.ProjectId"))
{
while (reader.Read())
{
TTBlock item = Load(reader);
if (!ret.ContainsKey(item._weekTimeSheetId))
ret.Add(item._weekTimeSheetId, item);
}
}
return ret;
}
