public void CanLoadSchemasAndSeeds()
{
var result = new DbDescription {XmlRoot = DbDescriptions.SchemasAndSeeds};
Assert.That(result.Schemas.Count(), Is.EqualTo(2));
Assert.That(result.Seeds.Count(), Is.EqualTo(2));
}
public static string UpdateConnString(DbDescription newDbDescription)
{
string initialStr = $"data source={newDbDescription.Server};initial catalog={newDbDescription.Name};user id=" + ConfigurationManager.AppSettings["SqlServerLogin"] + "; password="******"SqlServerPassword"] + "; MultipleActiveResultSets=True;App=EntityFramework";
HttpContext.Current.Session["dbConnectionString"] = initialStr;
return(initialStr);
}
public static void CreateDbMirror(DbDescription dbToBeCreated, DbDescription workingMirror)
{
DbManager.RunSqlAgainstDatabase(dbToBeCreated, ConfigurationManager.AppSettings["sqlCreateBackupDb"], dbToBeCreated.ServerDirectory);
List <DependentQuery> listOfQueries = DbManager.BuildInsertsFrom(workingMirror, dbToBeCreated);
DbManager.RunDQueriesAcrossDb(listOfQueries);
}
public List <DbDescription> GetDescriptions(DatabaseInfo databaseInfo)
{
var list = new List <DbDescription>();
using (IRepository repo = DataConnectionHelper.CreateRepository(databaseInfo))
{
var tlist = repo.GetAllTableNames();
if (tlist.Contains(TableName))
{
var ds = repo.ExecuteSql(SelectScript);
if (ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
var dbDesc = new DbDescription();
dbDesc.Object = row[0] == DBNull.Value ? null : row[0].ToString();
dbDesc.Parent = row[1] == DBNull.Value ? null : row[1].ToString();
dbDesc.Description = row[2] == DBNull.Value ? null : row[2].ToString();
list.Add(dbDesc);
}
}
}
}
return(list);
}
public static void RunSqlAgainstDatabase(DbDescription dbDescription, string sqlFileDirectory, string serverDirectory)
{
string script = LoadPreparedSqlQueryForDbCreation(sqlFileDirectory, dbDescription.Name, dbDescription.ServerDirectory);
using (var conn = ServerManager.EstablishBackupServerConnWithCredentials(dbDescription.Server, ConfigurationManager.AppSettings["SqlServerLogin"], ConfigurationManager.AppSettings["SqlServerPassword"]))
{
try
{
IEnumerable <string> splitScript = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
conn.Open();
foreach (string splitted in splitScript)
{
if (!string.IsNullOrEmpty(splitted.Trim()))
{
using (var command = new SqlCommand(splitted, conn))
{
command.ExecuteNonQuery();
}
}
}
conn.Close();
}
catch (Exception e)
{
_log.Error(e.Message);
}
}
}
public void CanLoadSingleSeed(string script)
{
var result = new DbDescription {
XmlRoot = script
};
Assert.That(result.Seeds.Count(), Is.EqualTo(1));
}
public void DescriptionWithEmptyConnectionReturnsNullConnection()
{
var result = new DbDescription {
XmlRoot = DbDescriptions.Empty
};
Assert.That(result.ConnectionInfo, Is.Null);
}
public void EmptyBaseDirectoryUsesAppDomainBaseDirectory()
{
var result = new DbDescription {
XmlRoot = DbDescriptions.SingleFileSchema
};
Assert.That(result.Schemas[0].GetBaseDirectory(), Is.EqualTo(AppDomain.CurrentDomain.BaseDirectory));
}
public void DescriptionMissingSeedReturnsEmptySeedCollection()
{
var result = new DbDescription {
XmlRoot = DbDescriptions.Empty
};
Assert.That(result.Seeds.Count(), Is.EqualTo(0));
}
public void StaticCreateCreatesWithDefaultTypeIfTypeIsNotSpecified()
{
var result = DbDescription.Create(DbDescriptions.ImplicitDbDescriptionType);
Assert.That(result, Is.Not.Null);
Assert.That(result, Is.AssignableTo <IDbDescription>());
Assert.That(result, Is.InstanceOf <DbDescription>());
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo("server=server"));
}
public void CopyOfDbConnectionInfoIsUsed()
{
var connectionInfo = new DbConnectionInfo {ConnectionString = "schema=schema", Provider = "System.Data.SqlClient"};
var result = new DbDescription {ConnectionInfo = connectionInfo};
connectionInfo.ConnectionString = "schema=other_schema";
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo("schema=schema"));
}
public void StaticCreateCreatesDbDescription()
{
var result = DbDescription.Create(DbDescriptions.ExplicitDbDescriptionType);
Assert.That(result, Is.Not.Null);
Assert.That(result, Is.AssignableTo <IDbDescription>());
Assert.That(result, Is.InstanceOf <TestDbDescription>());
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo("server=server"));
}
public void CanLoadSchemasAndSeeds()
{
var result = new DbDescription {
XmlRoot = DbDescriptions.SchemasAndSeeds
};
Assert.That(result.Schemas.Count(), Is.EqualTo(2));
Assert.That(result.Seeds.Count(), Is.EqualTo(2));
}
public void CanLoadRelativeFileScript()
{
var result = new DbDescription
{
XmlRoot = DbDescriptions.RelativeFileSchema,
BaseDirectory = "d:\\DevP\\Utility.Database\\src\\Utility.Database.Test"
};
Assert.That(result.Schemas.First().Load(), Is.EqualTo("schema"));
Assert.That(result.Seeds.First().Load(), Is.EqualTo("seed"));
}
public void CanLoadRelativeFileScript()
{
var result = new DbDescription
{
XmlRoot = DbDescriptions.RelativeFileSchema,
BaseDirectory = "d:\\DevP\\Utility.Database\\src\\Utility.Database.Test"
};
Assert.That(result.Schemas.First().Load(), Is.EqualTo("schema"));
Assert.That(result.Seeds.First().Load(), Is.EqualTo("seed"));
}
public static void SwitchToMirror()
{
foreach (var mirror in _currentListOfDbs)
{
if (mirror.Name != _currentlyConnectedDb.Name && mirror.MirrorSide == _currentlyConnectedDb.MirrorSide)
{
_currentlyConnectedDb = mirror;
break;
}
}
UpdateConnString(_currentlyConnectedDb);
}
public static void SwitchToOtherPart()
{
foreach (var otherPart in _currentListOfDbs)
{
if (otherPart.Name != _currentlyConnectedDb.Name && otherPart.MirrorSide != _currentlyConnectedDb.MirrorSide)
{
_currentlyConnectedDb = otherPart;
break;
}
}
UpdateConnString(_currentlyConnectedDb);
}
public void DescriptionWithValidConnectionLoadsConnection(string description, string connectionString, string provider, Type providerFactoryType)
{
var result = new DbDescription {XmlRoot = description};
Assert.That(result.ConnectionInfo, Is.Not.Null);
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo(connectionString));
Assert.That(result.ConnectionInfo.Provider, Is.EqualTo(provider));
if (providerFactoryType != null)
{
Assert.That(result.ConnectionInfo.ProviderFactory, Is.InstanceOf(providerFactoryType));
}
}
public void CopyOfDbConnectionInfoIsUsed()
{
var connectionInfo = new DbConnectionInfo {
ConnectionString = "schema=schema", Provider = "System.Data.SqlClient"
};
var result = new DbDescription {
ConnectionInfo = connectionInfo
};
connectionInfo.ConnectionString = "schema=other_schema";
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo("schema=schema"));
}
public void DescriptionWithValidConnectionLoadsConnection(string description, string connectionString, string provider, Type providerFactoryType)
{
var result = new DbDescription {
XmlRoot = description
};
Assert.That(result.ConnectionInfo, Is.Not.Null);
Assert.That(result.ConnectionInfo.ConnectionString, Is.EqualTo(connectionString));
Assert.That(result.ConnectionInfo.Provider, Is.EqualTo(provider));
if (providerFactoryType != null)
{
Assert.That(result.ConnectionInfo.ProviderFactory, Is.InstanceOf(providerFactoryType));
}
}
public void SaveDescription(string connectionString, List <DbDescription> cache, string description, string parentName, string objectName)
{
DbDescription desc = null;
if (cache != null)
{
desc = cache.Where(c => c.Object == objectName && c.Parent == parentName).SingleOrDefault();
}
if (desc != null)
{
UpdateDescription(description, parentName, objectName, connectionString);
}
else
{
AddDescription(description, parentName, objectName, connectionString);
}
cache = GetDescriptions(connectionString);
}
public void SaveDescription(DatabaseInfo databaseInfo, List <DbDescription> cache, string description, string parentName, string objectName)
{
DbDescription desc = null;
if (cache != null)
{
desc = cache.Where(c => c.Object == objectName && c.Parent == parentName).SingleOrDefault();
}
if (desc != null)
{
UpdateDescription(description, parentName, objectName, databaseInfo);
}
else
{
AddDescription(description, parentName, objectName, databaseInfo);
}
GetDescriptions(databaseInfo);
}
public static void InitializeDbsData(List <DbDescription> dbs, DbDescription currDb)
{
_currentListOfDbs = dbs;
_currentlyConnectedDb = currDb;
}
/// <summary>
/// 更新表相关注释
/// </summary>
/// <param name="tableName"></param>
public void ReparirDBComment(string tableName)
{
var sql_column = $@"SELECT
table_name,
COLUMN_Name,
COLUMN_COMMENT
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'orderservicedb' ";
var sql_table = $@"SELECT TABLE_NAME,
TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='orderservicedb' AND TABLE_TYPE='base table' ";
if (!tableName.IsNullOrWhiteSpace())
{
sql_column += $" and table_name='{tableName}' ";
sql_table += $" and table_name='{tableName}' ";
}
var columnList = _dapperRepository.Query <Table_Column>(sql_column).ToList();
var tableList = _dapperRepository.Query <Table>(sql_table).ToList();
if (tableList.Any() && columnList.Any())
{
var dllName = "LinkedX.OrderService.Core";
var path = new FileInfo(Assembly.GetEntryAssembly().Location).DirectoryName;
var assembly = Assembly.Load(dllName);
var types = assembly?.GetTypes();
var entityBaseList = types?
.Where(t => t.IsClass &&
!t.IsGenericType &&
!t.IsAbstract &&
(typeof(EntityBase <Guid>).IsAssignableFrom(t) || typeof(EntityBase <int>).IsAssignableFrom(t))
).ToList();
var entityList = types?
.Where(t => t.IsClass &&
!t.IsGenericType &&
!t.IsAbstract &&
(typeof(Entity).IsAssignableFrom(t) || typeof(Entity <Guid>).IsAssignableFrom(t) || typeof(Entity <int>).IsAssignableFrom(t))
).ToList();
var xmlPath = Path.Combine(path, dllName + ".xml");
FileStream fs = new FileStream(xmlPath, FileMode.Open, FileAccess.ReadWrite);
var xmlObj = new QueryCoreXml().Deserialize(fs);
// var member = xmlObj.Members.MemberList.Where(w => w.Name.ToLower().Contains("transactionbill"));
var group = columnList.GroupBy(g => g.Table_Name);
var descriptionList = new List <DbDescription>();
foreach (var item in group)
{
var add = new DbDescription
{
Name = item.Key,
Description = tableList.Where(w => w.Table_Name == item.Key).Select(s => s.Table_Comment).FirstOrDefault(),
Column = new List <DbDescription>()
};
foreach (var column in item)
{
add.Column.Add(new DbDescription
{
Name = column.Column_Name,
Description = column.Column_Comment
});
}
descriptionList.Add(add);
}
var xmlDescriptionList = new List <DbDescription>();
var wantedClassList = new List <string>();
foreach (var member in xmlObj.Members.MemberList)
{
var className = "";
if (member.Name.StartsWith("T"))
{
var index = member.Name.LastIndexOf('.');
className = member.Name.Substring(index + 1).ToLower();
var dbtable = descriptionList.FirstOrDefault(f => f.Name.ToLower().Contains(className));
if (dbtable != null)
{
dbtable.Description = member.Summary;
var colonIndex = member.Name.IndexOf(":");
}
}
}
//entityList.ForEach
}
}
public void EmptyBaseDirectoryUsesAppDomainBaseDirectory()
{
var result = new DbDescription {XmlRoot = DbDescriptions.SingleFileSchema};
Assert.That(result.Schemas[0].GetBaseDirectory(), Is.EqualTo(AppDomain.CurrentDomain.BaseDirectory));
}
public void CanLoadRelativeFileScript()
{
var result = new DbDescription
{
XmlRoot = DbDescriptions.RelativeFileSchema,
BaseDirectory = "c:\\Users\\chogan\\Dropbox\\DevP\\Buddy.Database\\src\\Buddy.Database45.Test"
};
Assert.That(result.Schemas.First().Load(), Is.EqualTo("schema"));
Assert.That(result.Seeds.First().Load(), Is.EqualTo("seed"));
}
public void CanLoadSingleSeed(string script)
{
var result = new DbDescription {XmlRoot = script};
Assert.That(result.Seeds.Count(), Is.EqualTo(1));
}
public void DescriptionMissingSeedReturnsEmptySeedCollection()
{
var result = new DbDescription {XmlRoot = DbDescriptions.Empty};
Assert.That(result.Seeds.Count(), Is.EqualTo(0));
}
public void DescriptionWithEmptyConnectionReturnsNullConnection()
{
var result = new DbDescription {XmlRoot = DbDescriptions.Empty};
Assert.That(result.ConnectionInfo, Is.Null);
}
public void StaticCreateThrowsIfTypeIsInvalid(string xmlRoot)
{
Assert.That(() => DbDescription.Create(xmlRoot), Throws.ArgumentException.With.Property("ParamName").EqualTo("xmlRoot"));
}
public static List <DependentQuery> BuildInsertsFrom(DbDescription sourceDb, DbDescription destinationDb)
{
string[] tables = FetchAllTableNames();
List <DependentQuery> dpQueries = new List <DependentQuery>();
foreach (var table in tables)
{
string sqlGetAllDataFromTable = string.Format("SELECT * FROM {0}.dbo.{1}", sourceDb.Name, table);
using (var conn = ServerManager.EstablishBackupServerConnWithCredentials(sourceDb.Server, ConfigurationManager.AppSettings["SqlServerLogin"], ConfigurationManager.AppSettings["SqlServerPassword"]))
{
SqlCommand cmd = new SqlCommand(sqlGetAllDataFromTable, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
da.Fill(dataTable);
string insertQuery = "SET IDENTITY_INSERT dbName.dbo." + table + " ON \r\n";
insertQuery += "INSERT INTO dbName.dbo." + table + "(";
foreach (var column in dataTable.Columns)
{
insertQuery += column + ",";
}
insertQuery = insertQuery.Remove(insertQuery.Length - 1);
insertQuery += ") Values(";
int iterationNumber = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
List <SqlParameter> byteParams = new List <SqlParameter>();
string inQueryWithVals = insertQuery;
foreach (DataColumn column in dataTable.Columns)
{
var data = dataRow[column.ToString()];
if (data.ToString() == string.Empty)
{
inQueryWithVals += "null,";
}
else
{
if
(column.DataType == typeof(string) || column.DataType == typeof(DateTime))
{
inQueryWithVals += "'" + dataRow[column.ToString()].ToString() + "',";
}
else if (column.DataType == typeof(Byte[]))
{
string sqlParamName = "@byteArrs" + iterationNumber++;
byte[] xy = (byte[])dataRow[column.ToString()];
inQueryWithVals += sqlParamName + ",";
byteParams.Add(new SqlParameter(sqlParamName, SqlDbType.VarBinary)
{
Direction = ParameterDirection.Input,
Size = 16,
Value = xy
});
}
else
{
inQueryWithVals += dataRow[column.ToString()] + ",";
}
}
}
inQueryWithVals = inQueryWithVals.Remove(inQueryWithVals.Length - 1);
inQueryWithVals += ")";
inQueryWithVals += "\r\n SET IDENTITY_INSERT dbName.dbo." + table + " OFF \r\n";
DependentQuery dpQuery = new DependentQuery
{
DatabaseDescription = destinationDb,
Query = inQueryWithVals,
DbSqlParams = byteParams
};
dpQuery = dpQuery.UpdateQueryParams();
dpQueries.Add(dpQuery);
}
conn.Close();
da.Dispose();
}
}
return(dpQueries);
}
