/// <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());
}
}
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());
}
}
private static EmbeddedResource Find(string resource)
{
if (embeddedResources.ContainsKey(resource))
{
return embeddedResources[resource];
}
else
{
var embeddedResource = new EmbeddedResource(resource);
embeddedResources.Add(resource, embeddedResource);
return embeddedResource;
}
}