Smo.Column AddColumn(Smo.Table t, string columnName, Smo.DataType dt, bool isNullable)
{
Smo.Column c = new Smo.Column(t, columnName, dt);
t.Columns.Add(c);
c.Nullable = isNullable;
return(c);
}
private void ScriptIndexes(Microsoft.SqlServer.Management.Smo.Table tableToScript)
{
ScriptingOptions options = new ScriptingOptions();
options.ScriptDrops = true;
options.ScriptSchema = true;
options.NoCollation = true;
options.ClusteredIndexes = true;
options.Default = true;
options.NonClusteredIndexes = true;
options.IncludeIfNotExists = true;
options.Indexes = true;
if (tableToScript.Indexes.Count > 0)
{
StringBuilder resultScript = new StringBuilder(string.Empty);
StringCollection coll = null;
foreach (Index ix in tableToScript.Indexes)
{
coll = ix.Script();
foreach (string str in coll)
{
resultScript.Append(str);
resultScript.Append(Environment.NewLine);
}
}
SQLScriptIndexes += resultScript;
SQLScriptIndexes += Environment.NewLine;
}
}
public static void UpdateDataSet(this MSMO.Table table, Data.ApplicationDataSet.DBTablesRow tableRow)
{
try
{
tableRow.DataSpaceUsed = table.DataSpaceUsed;
try
{
tableRow.DateLastModified = table.DateLastModified;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
tableRow.Owner = table.Owner;
tableRow.RowCount = table.RowCount;
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
/// <summary>
/// Creates a database table for each DataTable that's part of a BacksightDataSet,
/// inserts initial rows, and defines constraints.
/// </summary>
/// <param name="logger">Something to display progress messages (not null)</param>
/*
* public void CreateTables(ILog logger)
* {
* try
* {
* if (logger == null)
* throw new ArgumentNullException();
*
* //Transaction.Execute(delegate
* //{
* CreateBacksightTables(logger);
* //});
* }
*
* catch (Exception ex)
* {
* logger.LogMessage(ex.Message);
* throw ex;
* }
* }
*/
/*
* void CreateBacksightTables(ILog logger)
* {
* DropForeignKeyConstraints();
*
* // Create the ced schema
* logger.LogMessage("CREATE SCHEMA ced");
* Smo.Schema s = new Smo.Schema(m_Database, "ced");
* s.Create();
*
* BacksightDataSet ds = new BacksightDataSet();
* foreach (DataTable dt in ds.Tables)
* {
* logger.LogMessage("CREATE TABLE " + GetTableName(dt));
* CreateTable(s, dt);
* }
*
* // Add simple checks (unfortunately, this info isn't held as part of the
* // generated DataSet, so need to explicitly identify each table involved)
*
* AddSimpleChecks(logger, ds.EntityType, ds.EntityType.Checks);
* AddSimpleChecks(logger, ds.EntityTypeSchema, ds.EntityTypeSchema.Checks);
* AddSimpleChecks(logger, ds.Font, ds.Font.Checks);
* AddSimpleChecks(logger, ds.IdAllocation, ds.IdAllocation.Checks);
* AddSimpleChecks(logger, ds.IdFree, ds.IdFree.Checks);
* AddSimpleChecks(logger, ds.IdGroup, ds.IdGroup.Checks);
* AddSimpleChecks(logger, ds.Layer, ds.Layer.Checks);
* AddSimpleChecks(logger, ds.Schema, ds.Schema.Checks);
* AddSimpleChecks(logger, ds.SchemaTemplate, ds.SchemaTemplate.Checks);
* AddSimpleChecks(logger, ds.Template, ds.Template.Checks);
* AddSimpleChecks(logger, ds.Theme, ds.Theme.Checks);
*
* // Insert initial rows & save to database
* logger.LogMessage("Inserting initial rows");
* ds.AddInitialRows();
* ds.Save(ConnectionString);
*
* // Define foreign key constraints
* AddForeignKeyConstraints(logger);
* }
*/
/*
* void AddSimpleChecks(ILog logger, DataTable dt, string[] checks)
* {
* string tableName = GetTableName(dt);
* Smo.Table t = m_Database.Tables[tableName, "ced"];
* if (t==null)
* throw new Exception("Cannot locate table ced."+tableName);
*
* for (int i=0; i<checks.Length; i++)
* {
* string checkName = String.Format("{0}Check{1}", t.Name, i+1);
* logger.LogMessage("ADD CHECK "+checkName);
* AddCheck(t, checkName, checks[i]);
* }
* }
*/
/*
* void AddForeignKeyConstraints(ILog logger)
* {
* BacksightDataSet ds = new BacksightDataSet();
*
* foreach (DataRelation dr in ds.Relations)
* {
* DataTable parent = dr.ParentTable;
* DataTable child = dr.ChildTable;
*
* string parentTableName = GetTableName(parent);
* string childTableName = GetTableName(child);
*
* Smo.Table parentTable = m_Database.Tables[parentTableName, "ced"];
* Smo.Table childTable = m_Database.Tables[childTableName, "ced"];
*
* if (parentTable!=null && childTable!=null)
* {
* if (logger!=null)
* logger.LogMessage("ADD CONSTRAINT "+dr.RelationName);
*
* AddForeignKeyConstraint(dr);
* }
* }
* }
*/
/*
* void DropForeignKeyConstraints()
* {
* BacksightDataSet ds = new BacksightDataSet();
* List<Smo.ForeignKey> fks = new List<Smo.ForeignKey>();
*
* foreach (DataTable dt in ds.Tables)
* {
* string tableName = GetTableName(dt);
* Smo.Table t = m_Database.Tables[tableName, "ced"];
* if (t!=null)
* {
* foreach (Smo.ForeignKey fk in t.ForeignKeys)
* fks.Add(fk);
* }
* }
*
* foreach (Smo.ForeignKey fk in fks)
* fk.Drop();
* }
*/
void CreateTable(Smo.Schema s, DataTable dt)
{
// Drop any previously created version of the table
string tableName = GetTableName(dt);
Smo.Table t = m_Database.Tables[tableName, "ced"];
if (t != null)
{
t.Drop();
}
// Create the table
t = new Smo.Table(m_Database, tableName);
t.Schema = s.Name;
foreach (DataColumn c in dt.Columns)
{
AddColumn(t, c);
}
t.Create();
// Define primary key
CreatePrimaryKey(t, dt);
// Define pk & any unique constraints
CreateIndexes(t, dt);
}
public static void create_table(string a, string name, out string mess, ref int count)
{
database();
count = 0;
if (a == "NEW EXAM")
{
tb = new Table(db, name, "dbo");
}
else
{
tb = new Table(db, a + name, "dbo");
}
col7 = new Column(tb, "id", DataType.VarChar(5));
tb.Columns.Add(col7);
col1 = new Column(tb, "Question", DataType.VarChar(200));
tb.Columns.Add(col1);
col2 = new Column(tb, "CHOICE1", DataType.VarChar(200));
tb.Columns.Add(col2);
col3 = new Column(tb, "CHOICE2", DataType.VarChar(200));
tb.Columns.Add(col3);
col4 = new Column(tb, "CHOICE3", DataType.VarChar(200));
tb.Columns.Add(col4);
col5 = new Column(tb, "CHOICE4", DataType.VarChar(200));
tb.Columns.Add(col5);
col6 = new Column(tb, "Ans", DataType.VarChar(200));
tb.Columns.Add(col6);
tb.Create();
db.Tables.Refresh();
mess = "Table Create Successfully";
}
public ColumnResource(ServerContext context, string dbName, string schemaName, string tableName, string columnName, IUrlHelper urlHelper)
{
this._context = context;
// Get database by name
this._context.SmoServer.Databases.Refresh();
SMO.Database smoDb = this._context.SmoServer.Databases[dbName];
if (smoDb == null)
{
throw new SMO.SmoException(String.Format("Database '{0}' not found.", dbName));
}
// Get table by name
smoDb.Tables.Refresh();
SMO.Table smoTable = smoDb.Tables[tableName, schemaName];
if (smoTable == null)
{
throw new SMO.SmoException(String.Format("Table '{0}' not found in Schema '{1}' in Database '{1}'.",
tableName, schemaName, dbName));
}
// Get column by name
smoTable.Columns.Refresh();
this._smoColumn = smoTable.Columns[columnName];
if (this._smoColumn == null)
{
throw new SMO.SmoException(String.Format("Column {0} not found in Table {1} not found in Database {2}.", columnName, tableName, dbName));
}
this._parent = this._smoColumn.Parent;
this._smoTable = (SMO.Table) this._parent; // TODO: handle other types of parents (only handling Table for now)
this.UpdateLinks(urlHelper);
}
public IActionResult GetColumns(string dbName, string tableName)
{
SMO.Database smoDb = _context.SmoServer.Databases[dbName];
if (smoDb == null)
{
Log.Warning("Database {0} not found. No Columns to display.", dbName);
return(NotFound());
}
SMO.Table smoTable = smoDb.Tables[tableName];
if (smoTable == null)
{
Log.Warning("Table {0} not found in Database {1}. No Columns to display.", tableName, dbName);
return(NotFound());
}
// Project a list of ColumnResource objects
smoTable.Columns.Refresh();
List <ColumnResource> resources = new List <ColumnResource>();
foreach (SMO.Column smoColumn in smoTable.Columns)
{
ColumnResource resource = new ColumnResource(this._context, dbName, tableName, smoColumn.Name, @Url);
resources.Add(resource);
}
return(Ok(resources));
}
/// <summary>
/// Initializes a new instance of the Table class.
/// </summary>
public Table(SMO.Table table)
{
AddinHelper.Common.WriteToDebugWindow(string.Format("SMOH.{0}({1})", "Table", table));
// Save the real table in case we need to get something from it.
_table = table;
CreateDate = table.CreateDate.ToString("yyyy-MM-dd hh:mm:ss");
try
{
DateLastModified = table.DateLastModified.ToString("yyyy-MM-dd hh:mm:ss");
}
catch (Exception)
{
DateLastModified = "Not Available";
}
DataSpaceUsed = table.DataSpaceUsed.ToString();
ID = table.ID.ToString();;
Name = table.Name;
Owner = table.Owner;
RowCount = table.RowCount.ToString();
try
{
ExtendedProperties = table.ExtendedProperties;
}
catch (Exception)
{
}
}
public List <Index> GetIndexes(Microsoft.SqlServer.Management.Smo.Table table)
{
var indexes = new List <Index>();
foreach (Microsoft.SqlServer.Management.Smo.Index index in table.Indexes)
{
var indexType = index.IndexKeyType;
bool isRegularIndex = indexType != IndexKeyType.DriPrimaryKey && indexType != IndexKeyType.DriUniqueKey;
if (isRegularIndex)
{
var columns = index.IndexedColumns.Cast <IndexedColumn>()
.Select(indexedColumn => new Column {
ColumnName = indexedColumn.Name
})
.ToList();
var bimlIndex = new Index
{
Name = index.Name,
Columns = columns,
};
indexes.Add(bimlIndex);
}
}
return(indexes);
}
private void ScriptForeignKeys(Microsoft.SqlServer.Management.Smo.Table tableToScript)
{
if (tableToScript.ForeignKeys.Count > 0)
{
StringBuilder sb = new StringBuilder();
StringBuilder resultScript = new StringBuilder(string.Empty);
StringBuilder resultScriptToAddFK = new StringBuilder(string.Empty);
foreach (ForeignKey fk in tableToScript.ForeignKeys)
{
//Only insert data when table is empty
resultScript.AppendFormat("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND parent_object_id = OBJECT_ID(N'[{2}]'))", fk.ReferencedTableSchema, fk.Name, fk.Parent).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
resultScript.AppendFormat("ALTER TABLE {0} DROP CONSTRAINT [{1}]", fk.Parent, fk.Name).Append(Environment.NewLine);
resultScript.AppendLine("END").Append(Environment.NewLine);
resultScriptToAddFK.AppendFormat("ALTER TABLE {0} WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}].[{4}] ([{5}])", fk.Parent, fk.Name, fk.Columns[0].Name, fk.ReferencedTableSchema, fk.ReferencedTable, fk.Columns[0].ReferencedColumn).Append(Environment.NewLine);
}
SQLScriptForeignKeysDrop += resultScript.ToString();
SQLScriptForeignKeysAdd += resultScriptToAddFK.ToString();
}
}
/// <summary>
/// concat PK columns from a table
/// </summary>
/// <param name="t">smo.table</param>
/// <returns>column list</returns>
private static string GetJoinedColumnsPKOnly(smo.Table t)
{
IEnumerable <string> columnsPK = t.Columns.Cast <smo.Column>()
.Where(c => c.InPrimaryKey)
.Select(c => string.Format(" cast({0} as NVARCHAR)", c.Name)).AsEnumerable();
return(string.Join(" + '|' + ", columnsPK));
}
private void ScriptTriggers(Microsoft.SqlServer.Management.Smo.Table tableToScript)
{
ScriptingOptions options;
Console.WriteLine("Scripting Trigger objects started at : " + DateTime.Now);
StringBuilder resultScript = new StringBuilder(string.Empty);
StringCollection coll = null;
foreach (Microsoft.SqlServer.Management.Smo.Trigger trg in tableToScript.Triggers)
{
if (!trg.IsSystemObject)
{
//Script drop
options = new ScriptingOptions();
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
options.IncludeHeaders = true;
coll = trg.Script(options);
resultScript = new StringBuilder(string.Empty);
foreach (string str in coll)
{
resultScript.Append(str);
resultScript.Append(Environment.NewLine);
}
SQLScriptTriggers = resultScript.ToString();
SQLScriptTriggers += "GO";
SQLScriptTriggers += Environment.NewLine;
SQLScriptTriggers += Environment.NewLine;
//Script Create
options = new ScriptingOptions();
options.IncludeIfNotExists = true;
options.IncludeHeaders = true;
coll = trg.Script(options);
resultScript = new StringBuilder(string.Empty);
foreach (string str in coll)
{
resultScript.Append(str);
resultScript.Append(Environment.NewLine);
}
SQLScriptTriggers += resultScript.ToString();
SQLScriptTriggers += "GO";
SQLScriptTriggers += Environment.NewLine;
Console.WriteLine(trg.Name);
}
}
}
/// <summary>
/// Generates the CREATE PROCEDURE statement to select 1 line from the PK
/// </summary>
/// <param name="t" value="A SMO table"></param>
/// <param name="header" value="Your custom header" remarks="Can be empty if you don't need header"></param>
/// <returns>Return the DDL script</returns>
public static string ScriptProcedureSelect(this smo.Table t, string header = "", bool setNoCount = true)
{
smo.Server s = t.Parent.Parent;
s.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), true);
string create = @"CREATE PROCEDURE [{0}].[{1}_Select]
{2}AS
";
StringBuilder sb = new StringBuilder();
StringBuilder sbParam = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
bool hasPrimary = false;
IEnumerable <string> columns = t.Columns.Cast <smo.Column>()
.Select(c => string.Format("[{0}]", c.Name)).AsEnumerable();
string column = string.Join("\r\n\t, ", columns);
bool firstPrimary = true;
foreach (smo.Column c in t.Columns)
{
if (c.InPrimaryKey)
{
hasPrimary = true;
if (!firstPrimary)
{
sbParam.Append(", ");
sbWhere.Append("\tAND ");
}
firstPrimary = false;
sbParam.AppendLine(string.Format("\t@{0} {1}", c.Name, c.DataType.ScriptToSql()));
sbWhere.AppendLine(string.Format("[{0}] = @{1}", c.Name, c.Name.Replace(" ", "_")));
}
}
if (hasPrimary)
{
if (!string.IsNullOrEmpty(header))
{
sb.AppendLine(header);
}
sb.Append(string.Format(create, t.Schema, t.Name, sbParam.ToString()));
if (setNoCount)
{
sb.AppendLine("SET NOCOUNT ON");
}
sb.AppendLine(string.Format("SELECT {0}", column.ToString()));
sb.AppendLine(string.Format("FROM [{0}].[{1}]", t.Schema, t.Name));
sb.Append(string.Format("WHERE {0}", sbWhere.ToString()));
sb.AppendLine("GO");
}
else
{
sb.AppendLine("-- ####### Scripting error #######");
sb.AppendLine(string.Format("-- {0}.{1} has no primary key !", t.Schema, t.Name));
sb.AppendLine("-- ###############################");
}
return(sb.ToString());
}
private static void Update(MSMO.Table table, SQLInformation.Data.ApplicationDataSet.DBTablesRow dataRow)
{
try
{
try
{
try
{
dataRow.DataSpaceUsed = table.DataSpaceUsed;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
#endif
dataRow.DataSpaceUsed = -1;
}
try
{
dataRow.DateLastModified = table.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 11);
#endif
}
dataRow.FileGroup = table.FileGroup;
dataRow.Owner = table.Owner;
dataRow.RowCount = table.RowCount;
}
catch (Exception ex)
{
ReportException(ex, dataRow, CLASS_BASE_ERRORNUMBER + 12);
//VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 12);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBTables_Update();
//UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
ReportException(ex, dataRow, CLASS_BASE_ERRORNUMBER + 13);
//VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
//UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
public static void Test()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (System.Data.SqlClient.SqlConnection con = (System.Data.SqlClient.SqlConnection)
//SqlFactory.GetConnection()
SqlFactory.LocalConntection
)
{
Microsoft.SqlServer.Management.Common.ServerConnection sc =
new Microsoft.SqlServer.Management.Common.ServerConnection(con);
lock (con)
{
sc.Connect();
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(sc);
Microsoft.SqlServer.Management.Smo.Database database; // = new Microsoft.SqlServer.Management.Smo.Database();
//database = server.Databases["redmine"];
//string schemaName = @"dbo";
//string tableName = @"issues";
database = server.Databases["COR_Basic_Demo_V4"];
string schemaName = @"dbo";
string tableName = @"T_Benutzer";
Microsoft.SqlServer.Management.Smo.Table table = database.Tables[tableName, schemaName];
System.Collections.Specialized.StringCollection result = table.Script();
// table.Script(new ScriptingOptions() { ScriptForAlter = true });
Microsoft.SqlServer.Management.Smo.StoredProcedure proc = database.StoredProcedures["procname", "schema"];
// proc.Script(new ScriptingOptions() { ScriptForAlter = true });
// string alterText = proc.ScriptHeader(true) + proc.TextBody;
foreach (string line in result)
{
sb.AppendLine(line);
} // Next line
sc.Disconnect();
} // End Lock con
} // End Using con
using (System.IO.FileStream fs = System.IO.File.OpenWrite(@"d:\testScriptSAQl.sql"))
{
using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
{
sw.Write(sb.ToString());
sw.Flush();
fs.Flush();
} // End Using sw
} // End Using fs
} // End Sub
private void GetTop100Rows()
{
int rowCount = 100;
string sqlQuery = string.Empty;
try
{
this._top100Rows = new List <Dictionary <string, string> >();
SMO.Database smoDb = _context.SmoServer.Databases[this._dbName];
if (smoDb == null)
{
Log.Warning("Database '{0}' not found. No Rows to display.", this._dbName);
return;
}
SMO.Table smoTable = smoDb.Tables[this._tableName, this._schemaName];
if (smoTable == null)
{
Log.Warning("Table '{0}' not found in Schema '{1}' in Database '{2}'. No Rows to display.",
this._tableName, this._schemaName, this._dbName);
return;
}
// fetch top 100 rows from table
sqlQuery = String.Format("SELECT TOP {0} * FROM [{1}].[{2}] WITH(NOLOCK)",
rowCount, smoTable.Schema, smoTable.Name);
Log.Information("Database: {0}, Schema: {1}, Table: {2}. Running SMO ExecuteWithResults: {3}",
smoDb.Name, smoTable.Schema, smoTable.Name, sqlQuery);
using (DataSet dataset = smoDb.ExecuteWithResults(sqlQuery))
{
if ((dataset != null) && (dataset.Tables.Count > 0) && (dataset.Tables[0].Rows.Count > 0))
{
// Loop through all rows in the table
foreach (DataRow datarow in dataset.Tables[0].Rows)
{
// Loop through all cells in row
int columIndex = 0;
Dictionary <string, string> rowToAdd = new Dictionary <string, string>();
foreach (object dataObj in datarow.ItemArray)
{
rowToAdd[smoTable.Columns[columIndex].Name] = dataObj.ToString();
columIndex++;
}
_top100Rows.Add(rowToAdd);
}
}
}
}
catch (Exception e)
{
Log.Error("Error running query: {0}\n\n{1}", sqlQuery, e.ToString());
}
}
/// <summary>
/// Attempts to locate a simple primary key for a table (a key where the
/// index consists of just one column)
/// </summary>
/// <param name="t">The table of interest</param>
/// <returns>The column that defines the primary key (null if the table does
/// not have a primary key, or it consists of more than one column)</returns>
public static Smo.Column GetSimplePrimaryKeyColumn(Smo.Table t)
{
Smo.Index x = GetPrimaryKey(t);
if (x == null || x.IndexedColumns.Count != 1)
{
return(null);
}
Smo.IndexedColumn xc = x.IndexedColumns[0];
return(t.Columns[xc.Name]);
}
/// <summary>
/// Generates the CREATE PROCEDURE statement to insert 1 line
/// </summary>
/// <param name="t" value="A SMO table"></param>
/// <param name="header" value="Your custom header" remarks="Can be empty if you don't need header"></param>
/// <returns>Return the DDL script</returns>
public static string ScriptProcedureInsert(this smo.Table t, string header = "", bool setNoCount = true)
{
smo.Server s = t.Parent.Parent;
s.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), true);
string create = @"CREATE PROCEDURE [{0}].[{1}_Insert]
{2}AS ";
StringBuilder sb = new StringBuilder();
StringBuilder sbParam = new StringBuilder();
StringBuilder sbColumn = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
bool hasOtherColumn = false;
bool first = true;
foreach (smo.Column c in t.Columns)
{
if (!c.Identity && !c.Computed) // We can't update identity and computed
{
hasOtherColumn = true;
if (!first)
{
sbParam.Append(", ");
sbColumn.Append("\t, ");
sbValues.Append("\t, ");
}
first = false;
sbParam.AppendLine(string.Format("\t@{0} {1}", c.Name.Replace(" ", "_"), c.DataType.ScriptToSql()));
sbColumn.AppendLine(string.Format("[{0}]", c.Name));
sbValues.AppendLine(string.Format("@{0}", c.Name.Replace(" ", "_")));
}
}
if (hasOtherColumn)
{
if (!string.IsNullOrEmpty(header))
{
sb.AppendLine(header);
}
sb.AppendLine(string.Format(create, t.Schema, t.Name, sbParam.ToString()));
if (setNoCount)
{
sb.AppendLine("SET NOCOUNT ON");
}
sb.AppendLine(string.Format("INSERT INTO [{0}].[{1}]", t.Schema, t.Name));
sb.AppendLine(string.Format("({0})", sbColumn.ToString()));
sb.AppendLine(string.Format("values({0})", sbValues.ToString()));
sb.AppendLine("GO");
}
else
{
sb.AppendLine("-- ####### Scripting error #######");
sb.AppendLine(string.Format("-- {0}.{1} has no column to insert !", t.Schema, t.Name));
sb.AppendLine("-- ###############################");
}
return(sb.ToString());
}
private IEnumerable <Run00.SqlCopySchema.Column> GetColumns(Microsoft.SqlServer.Management.Smo.Table table)
{
return(table.Columns.Cast <Microsoft.SqlServer.Management.Smo.Column>().Select(c => new Run00.SqlCopySchema.Column()
{
Name = c.Name,
//Nullable = c.Nullable,
Type = GetClrType(c.DataType.SqlDataType, c.Nullable),
InPrimaryKey = c.InPrimaryKey,
Table = table.Name
}));
}
private void ExecuteScriptDataCommand(Object args)
{
Microsoft.SqlServer.Management.Smo.Table selectedTable = (Microsoft.SqlServer.Management.Smo.Table)args;
WorkspaceData workspace = new WorkspaceData("", "ScriptTableDataView", selectedTable, "Data", true);
Views.Add(workspace);
SetActiveWorkspace(workspace);
ShowContextMenu = false;
}
public MetadataTable BuildMetadata(MetadataDatabase mdb, string TableName, string Schema = "dbo", bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
if (mdb.Tables.TryGetValue(TableName, out mt))
{
return(mt);
}
Microsoft.SqlServer.Management.Smo.Table t = new Microsoft.SqlServer.Management.Smo.Table(SqlDatabase, TableName, Schema);
t.Refresh();
return(BuildMetadata(mdb, t, PrimaryKeyIndexOnly, SelfJoin));
}
private void info_line_Click(object sender, EventArgs e)
{
Server myServer = new Server(@".\MS_SQLSERVER"); //lub (local)
//Autentykacja Windows
myServer.ConnectionContext.LoginSecure = true;
//Autentykacja SQL
myServer.ConnectionContext.Connect();
INFO.Database cBaza = myServer.Databases["PROJEKT"];
INFO.Table cTab = cBaza.Tables["LINE"];
foreach (INFO.Column item in cTab.Columns)
{
MessageBox.Show("Nazwa kolumny: " + item.Name + "\nTyp danych: " + item.DataType.ToString());
}
INFO.Column cKol = cTab.Columns["id"];
MessageBox.Show("Czy kolumna id jest w kluczu głównym tabeli: " + cKol.InPrimaryKey.ToString());
MessageBox.Show("Czy kolumna id jest kluczem obcym: " + cKol.IsForeignKey.ToString());
MessageBox.Show("Czy kolumna id jest kolumną wyliczaną: " + cKol.Computed.ToString());
MessageBox.Show("Czy kolumna id zezwala na NULL-e: " + cKol.Nullable.ToString());
int licznik = 0;
foreach (Microsoft.SqlServer.Management.Smo.View view in cBaza.Views)
{
if (licznik < 5)
{
MessageBox.Show("Nazwa widoku: " + view.Name + "\nData utworzenia: " + view.CreateDate.ToShortDateString());
licznik++;
}
}
//Procedury skałdowe
int licznik2 = 0;
foreach (StoredProcedure sp in cBaza.StoredProcedures)
{
if (licznik2 < 5)
{
MessageBox.Show("Nazwa procedury: " + sp.Name + "\nLiczba parametrów: " + sp.Parameters.Count.ToString());
licznik2++;
}
}
//przegladanie użytkowników DB
foreach (User user in cBaza.Users)
{
MessageBox.Show("User: "******"\nLogin: "******"\nType: " + user.UserType);
}
}
/// <summary>
/// Get the checksum for a table
/// Checksum doesn't manage these types : Xml, Image, Geography, ntext, text
/// </summary>
/// <param name="t">a smo.table</param>
/// <returns>Int64 represents Checksum_agg(Checksum(column list))</returns>
public static Int64 DataChecksum(this smo.Table t)
{
string column = GetJoinedColumns(t);
string query = string.Format("select checksum_agg(CHECKSUM({0})) as nb from [{1}].[{2}]", column, t.Schema, t.Name);
smo.Database d = t.Parent;
DataSet ds = d.ExecuteWithResults(query);
Int64 i;
Int64.TryParse(ds.Tables[0].Rows[0]["nb"].ToString(), out i);
return(i);
}
/// <summary>
/// Locates the primary key for a table
/// </summary>
/// <param name="t">The table of interest</param>
/// <returns>The index that represents the primary key (null if the table
/// doesn't have a primary key)</returns>
static Smo.Index GetPrimaryKey(Smo.Table t)
{
foreach (Smo.Index x in t.Indexes)
{
if (x.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
return(x);
}
}
return(null);
}
/// <summary>
/// Compare 2 schema Tables
/// </summary>
/// <param name="sp1">your first smo Table</param>
/// <param name="sp2">your second smo Table</param>
/// <param name="checkComments">True if you want to compare comments</param>
/// <param name="checkBrackets">True if you want to compare scripts with brackets</param>
/// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param>
/// <returns></returns>
public static KMOCompareInfo CompareSchema(this smo.Table t1, smo.Table t2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true)
{
smo.ScriptingOptions so = new smo.ScriptingOptions();
so.DriAll = true;
string s1 = String.Join(Environment.NewLine, t1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string s2 = String.Join(Environment.NewLine, t2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string message = string.Empty;
if (t1.Columns.Count != t2.Columns.Count)
{
message += "Tables don't have the same column count. ";
}
foreach (smo.Column c1 in t1.Columns)
{
smo.Column c2 = t2.Columns[c1.Name];
if (c2 == null)
{
message += "The column " + c1.Name + " doesn't exist in the second table. ";
}
else if (c2.DataType.Name != c1.DataType.Name || c2.DataType.NumericPrecision != c1.DataType.NumericPrecision || c2.DataType.NumericScale != c1.DataType.NumericScale)
{
message += "The column " + c1.Name + " doesn't have the same type. ";
}
}
foreach (smo.Column c2 in t2.Columns)
{
smo.Column c1 = t1.Columns[c2.Name];
if (c1 == null)
{
message += "The column " + c2.Name + " doesn't exist in the first table. ";
}
}
if (message != string.Empty)
{
return(new KMOCompareInfo {
IsIdentical = false, Message = message, Script1 = s1, Script2 = s2
});
}
if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets))
{
return(new KMOCompareInfo {
IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2
});
}
else
{
return(new KMOCompareInfo {
IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2
});
}
}
void AddCheck(Smo.Table t, string checkName, string check)
{
// String.Format("ALTER TABLE {0} WITH CHECK ADD CONSTRAINT {1} CHECK ({2})",
// t.Name, checkName, check);
Smo.Check ck = new Smo.Check();
ck.Name = checkName;
ck.Parent = t;
ck.Text = check;
ck.IsEnabled = ck.IsChecked = true;
ck.Create();
t.Alter();
}
void CreateIndexes(Smo.Table t, DataTable dt)
{
int indexNum = 0;
foreach (Constraint c in dt.Constraints)
{
if (c is UniqueConstraint)
{
indexNum++;
CreateUniqueIndex(t, (UniqueConstraint)c, indexNum);
}
}
}
Smo.Index CreateUniqueIndex(Smo.Table t, UniqueConstraint uc, int indexNum)
{
if (uc.IsPrimaryKey)
{
return(null);
}
Smo.Index idx = new Smo.Index(t, t.Name + "Index" + indexNum);
idx.IndexKeyType = Smo.IndexKeyType.DriUniqueKey;
SetUniqueIndexColumns(idx, uc.Columns);
idx.Create();
return(idx);
}
Smo.Index CreatePrimaryKey(Smo.Table t, DataTable dt)
{
DataColumn[] pk = dt.PrimaryKey;
if (pk == null || pk.Length == 0)
{
return(null);
}
Smo.Index idx = new Smo.Index(t, t.Name + "Key");
idx.IndexKeyType = Smo.IndexKeyType.DriPrimaryKey;
SetUniqueIndexColumns(idx, pk);
idx.Create();
return(idx);
}
public static void LoadFromSMO(MSMO.Table table, Guid tableID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
foreach (MSMO.Column column in table.Columns)
{
GetInfoFromSMO(column, tableID);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
public MetadataTable BuildMetadata(MetadataDatabase mdb, string TableName, string Schema = "dbo", bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
if (mdb.Tables.TryGetValue(TableName, out mt))
{
return mt;
}
Microsoft.SqlServer.Management.Smo.Table t = new Microsoft.SqlServer.Management.Smo.Table(SqlDatabase, TableName, Schema);
t.Refresh();
return BuildMetadata(mdb, t, PrimaryKeyIndexOnly, SelfJoin);
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List<string> lstErrors = new List<string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if(!line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders();//Clean out folders creating during this run
throw;
}
}
internal Table(Microsoft.SqlServer.Management.Smo.Table Table)
{
_table = Table;
}