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; }