public void ResetIdentitySeed(SqlConnection conn)
{
DataField f = IdentityField;
if (f == null)
{
return;
}
if (conn == null)
{
conn = new SqlConnection(SQLDMGlobal.ConnectionString());
}
using (conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "declare @reseed int set @reseed = (select max(" + f.SafeFieldName + ") from " + FullSafeTableName + ") " +
"dbcc checkident(" + FullSafeTableName + ", reseed, @reseed)";
cmd.ExecuteNonQuery();
}
}
private void SetLinks()
{
_Links = new List <DataLink>();
foreach (DataField field in Dummy.Fields)
{
if (field.ChildTableTypes == null)
{
continue;
}
foreach (Type childType in field.ChildTableTypes)
{
{
DataRecord childDummy = SQLDMGlobal.FindDummy(childType);
if (childDummy == null)
{
continue;
}
foreach (DataField childField in childDummy.Dummy.Fields)
{
if (childField.ParentTableType != null && childField.ParentTableType.FullName == GetType().FullName)
{
DataLink newLink = new DataLink(this, field, childType, childField.SourceColumn);
_Links.Add(newLink);
break;
}
}
}
}
}
}
protected static DataTable GetDataTable(string sql, SqlParameter[] parameters, CommandType commandType, bool loadChildren)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLDMGlobal.ConnectionString()))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = commandType;
cmd.Parameters.AddRange(parameters);
cmd.CommandText = sql;
try
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
cmd.Parameters.Clear();
}
#pragma warning disable CS0168 // Variable is declared but never used
catch (Exception ex)
#pragma warning restore CS0168 // Variable is declared but never used
{
throw;
}
finally
{
cmd.Parameters.Clear();
conn.Close();
}
}
return(dt);
}
protected void LoadChildRecords(Type type)
{
using (SqlConnection conn = new SqlConnection(SQLDMGlobal.ConnectionString()))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
try
{
foreach (DataLink dl in Links.FindAll(c => c.ChildRecordType == type))
{
DataRecord childDummy = SQLDMGlobal.FindDummy(type);
if (childDummy != null)
{
cmd.CommandText = string.Format("select * from {0} where [{1}] = {2}", childDummy.FullSafeTableName, dl.ChildFieldName, dl.ParentField.ParameterName);
SqlParameter param = GetSqlParameter(dl.ParentField.ParameterName);
if (param != null)
{
cmd.Parameters.Add(param);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
DataRecord inst = (DataRecord)System.Activator.CreateInstance(dl.ChildRecordType);
FillRecord(row, inst, true);
ChildRecords.Add(inst);
}
cmd.Parameters.Clear();
}
}
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// Adds a new environment into the system
/// </summary>
/// <param name="environment"></param>
public void Add(SqlEnvironment environment)
{
if (!EnvironmentList.Contains(environment))
{
string n = environment.EnvironmentName;
foreach (SqlEnvironment e in EnvironmentList)
{
if (e.EnvironmentName == n)
{
throw new DataException("Environment " + n + " already exists");
}
}
EnvironmentList.Add(environment);
if (environment.IsCurrent)
{
SetCurrentEnvironment(environment.EnvironmentName);
SQLDMGlobal.Initialise();
}
}
}
internal void LoadRelationships()
{
if (this == Dummy)
{
// Setup relationships and test for field type mismatches
foreach (DataField f in Fields)
{
// Get list of child types
foreach (ForeignKeys fk in SQLDMGlobal.ForeignKeysLinks.FindAll(fk => fk.ParentTable == TableName && fk.ParentSchema == SchemaName && fk.ParentColumn == f.SourceColumn))
{
f.IsParentField = true;
f.ChildTableTypes.Add(SQLDMGlobal.TableType(fk.ChildSchema, fk.ChildTable, GetType()));
}
// Get parent type
foreach (ForeignKeys fk in SQLDMGlobal.ForeignKeysLinks.FindAll(fk => fk.ChildSchema == SchemaName && fk.ChildTable == TableName && fk.ChildColumn == f.SourceColumn))
{
f.ParentTableType = SQLDMGlobal.TableType(fk.ParentSchema, fk.ParentTable, GetType());
}
}
}
}
/// <summary>
/// Saves the data record and all its child records, creating a new record if necessary
/// </summary>
public virtual bool Save(SqlTransaction trans, SqlConnection conn)
{
ReadOnly ro = (ReadOnly)Attribute.GetCustomAttribute(this.GetType(), typeof(ReadOnly));
if (ro != null)
{
throw new DataException("Data is marked as ReadOnly");
}
if (!IsValid())
{
return(false);
}
if (!CanSave())
{
ValidationErrors.Add("Table " + SafeTableName + " must have either an identity field defined or a primary key field with valid data");
return(false);
}
if (conn == null)
{
conn = new SqlConnection(SQLDMGlobal.ConnectionString());
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = conn.CreateCommand();
bool TransactionExists = true;
if (trans == null)
{
TransactionExists = false;
trans = conn.BeginTransaction();
}
cmd.Transaction = trans;
try
{
if (IsDirty)
{
if (IsNew)
{
cmd.CommandText = SQLForCreate();
cmd.Parameters.AddRange(AllDirtyParameters(true).ToArray());
}
else
{
cmd.CommandText = SQLForUpdate(null);
cmd.Parameters.AddRange(AllDirtyParameters(false).ToArray());
}
SqlDataReader rs = cmd.ExecuteReader();
if (rs.Read() && IdentityField != null)
{
IdentityField.Value = rs.GetValue(0);
}
IsNew = false;
rs.Close();
cmd.Parameters.Clear();
foreach (DataField f in Fields)
{
f.OriginalValue = f.Value;
}
}
if (ChildRecords != null)
{
foreach (DataRecord r in ChildRecords)
{
if (!r.Save(trans, conn))
{
ValidationErrors.AddRange(r.ValidationErrors);
throw new DataException("Validation error while saving to " + r.SafeTableName);
}
}
}
if (!TransactionExists)
{
trans.Commit();
conn.Close();
}
if (Saved != null)
{
Saved(this);
}
}
catch (DataException ex)
{
if (!TransactionExists)
{
ValidationErrors.Add(ex.Message);
trans.Rollback();
}
else
{
throw;
}
}
return(ValidationErrors.IsValid);
}
public bool Delete(SqlTransaction trans, SqlConnection conn)
{
ValidationErrors.Clear();
if (IsDeleted)
{
return(true);
}
DataField idField = IdentityField;
if (idField == null)
{
ValidationErrors.Add("Record does not exist");
return(false);
}
if (conn == null)
{
conn = new SqlConnection(SQLDMGlobal.ConnectionString());
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = conn.CreateCommand();
bool TransactionExists = true;
if (trans == null)
{
TransactionExists = false;
trans = conn.BeginTransaction();
}
cmd.Transaction = trans;
try
{
foreach (DataRecord r in ChildRecords)
{
if (!r.Delete(trans, conn))
{
ValidationErrors.AddRange(r.ValidationErrors);
throw new DataException("Validation error while saving to " + r.SafeTableName);
}
}
cmd.CommandText = SQLForDelete(idField);
cmd.Parameters.Add(idField);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (!TransactionExists)
{
trans.Commit();
conn.Close();
}
IsDeleted = true;
Deleted(this);
}
catch (DataException ex)
{
if (!TransactionExists)
{
trans.Rollback();
}
ValidationErrors.Add(ex.Message);
}
return(ValidationErrors.IsValid);
}