/// <summary>
/// Gets all the objects in a given database. Note that the SQL script this method is running is embedded within the project.
/// </summary>
/// <param name="connection">Database connection</param>
/// <returns>List of Database Objects</returns>
public static List <DatabaseObject> GetDatabaseObjects(SqlConnection connection)
{
var result = new List <DatabaseObject>();
// Get the SQL from the embedded file in the project.
var sql = Encoding.ASCII.GetString(EmbeddedResource.Get(@"sar.Databases.MSSQL.GetObjects.sql"));
using (var command = new SqlCommand(sql, connection))
{
command.CommandTimeout = 600; // 10 minutes
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
// convert the database string to an enum
var type = (SqlObjectType)Enum.Parse(typeof(SqlObjectType), reader.GetString(1));
result.Add(new DatabaseObject(name, type));
}
}
}
return(result);
}
/// <summary>
/// Create a SQL script of insert statements which replicates the data of a table object or table type.
/// The purpose is to regenerate data or populate data automated way
/// </summary>
/// <param name="connection">Database Object</param>
/// <returns>Generate insert script</returns>
public string GetInsertScript(SqlConnection connection)
{
string result = "";
switch (this.type)
{
case SqlObjectType.TT:
case SqlObjectType.U:
var sproc = Encoding.ASCII.GetString(EmbeddedResource.Get(@"sar.Databases.MSSQL.GenerateInsert.sql"));
foreach (var sql in DatabaseHelper.SplitByGO(sproc))
{
using (var command = new SqlCommand(sql, connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
command.ExecuteNonQuery();
}
}
// set the script parameters
// @PrintGeneratedCode = 0 => Use PRINT command instead or SELECT
// @GenerateProjectInfo = 0 => Don't add the project comments
var script = "";
script += "EXECUTE ";
script += " dbo.GenerateInsert @ObjectName = N'" + this.name + "'";
script += " ,@PrintGeneratedCode=0";
script += " ,@GenerateProjectInfo=0";
result = @"IF NOT EXISTS (SELECT TOP 1 * FROM [" + this.name + "])" + Environment.NewLine;
using (var command = new SqlCommand(script, connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0) + Environment.NewLine;
}
}
}
// Drop the stored procedure the database
using (var command = new SqlCommand(@"DROP PROCEDURE dbo.GenerateInsert;", connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
command.ExecuteNonQuery();
}
return(result.TrimWhiteSpace());
default:
throw new ApplicationException("object is not a table");
}
}
public string GetCreateScript(SqlConnection connection)
{
string result = "";
switch (this.type)
{
case SqlObjectType.TT:
var createTableTypeScript = Encoding.ASCII.GetString(EmbeddedResource.Get(@"sar.Databases.MSSQL.CreateTableType.sql"));
createTableTypeScript = createTableTypeScript.Replace(@"%%TableTypeName%%", this.name);
using (var command = new SqlCommand(createTableTypeScript, connection))
{
command.CommandTimeout = 600; // 10 minutes
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0) + Environment.NewLine;
}
}
}
return(result.TrimWhiteSpace());
case SqlObjectType.U:
var createTableScript = EmbeddedResource.Get(@"sar.Databases.MSSQL.CreateTable.sql");
var script = Encoding.ASCII.GetString(createTableScript);
script = script.Replace(@"%%TableName%%", this.name);
using (var command = new SqlCommand(script, connection))
{
command.CommandTimeout = 600; // 10 minutes
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0) + Environment.NewLine;
}
}
}
return(result.TrimWhiteSpace());
default:
using (var command = new SqlCommand("sp_helptext " + this.name.QuoteSingle(), connection))
{
command.CommandTimeout = 600; // 10 minutes
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0);
}
}
}
return(result.TrimWhiteSpace());
}
}
/// <summary>
/// Builds a create table script which has the ability to either create (non-existant) table or alter an
/// existing table to added the missing information.
/// </summary>
/// <param name="connection">Database Connection</param>
/// <returns>Generate create script</returns>
public string GetCreateScript(SqlConnection connection)
{
string result = "";
switch (this.type)
{
case SqlObjectType.TT:
// get the embedded sql code
var createTableTypeScript = Encoding.ASCII.GetString(EmbeddedResource.Get(@"sar.Databases.MSSQL.CreateTableType.sql"));
createTableTypeScript = createTableTypeScript.Replace(@"%%TableTypeName%%", this.name);
using (var command = new SqlCommand(createTableTypeScript, connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0) + Environment.NewLine;
}
}
}
return(result.TrimWhiteSpace());
case SqlObjectType.U:
// get the embedded sql code
var createTableScript = EmbeddedResource.Get(@"sar.Databases.MSSQL.CreateTable.sql");
var script = Encoding.ASCII.GetString(createTableScript);
script = script.Replace(@"%%TableName%%", this.name);
using (var command = new SqlCommand(script, connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0) + Environment.NewLine;
}
}
}
return(result.TrimWhiteSpace());
default:
// if the type of the object is not a user defined table or a table type then use
// sq_helptext to return the definition of the object
// msdn: https://msdn.microsoft.com/en-us/library/ms176112.aspx
using (var command = new SqlCommand("sp_helptext " + this.name.QuoteSingle(), connection))
{
command.CommandTimeout = COMMAND_TIMEOUT;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result += reader.GetString(0);
}
}
}
return(result.TrimWhiteSpace());
}
}
