public MySmo.DataType GetDataType(Smo.Column c)
{
var dt = c.DataType;
if (dt.SqlDataType == Smo.SqlDataType.UserDefinedDataType)
{
var db = c.GetDatabase();
var ut = db.UserDefinedDataTypes[c.DataType.Name, c.DataType.Schema];
var st = Utils.GetSqlDataType(ut.SystemType, ut.MaxLength);
dt = new Smo.DataType(st)
{
MaximumLength = ut.MaxLength,
NumericPrecision = ut.NumericPrecision,
NumericScale = ut.NumericScale
};
}
return(new MySmo.DataType
{
Name = dt.Name,
Schema = dt.Schema,
MaximumLength = dt.MaximumLength,
NumericPrecision = dt.NumericPrecision,
NumericScale = dt.NumericScale,
SqlDataType = (MySmo.SqlDataType)(int) dt.SqlDataType
});
}
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);
}
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);
}
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);
}
}
void OnSelect()
{
// Get the ID of the selected field.
Smo.Column col = (fieldsListBox.SelectedItem as Smo.Column);
if (col == null)
{
return;
}
// Append the column name to the format
formatTextBox.Text += String.Format("[{0}]", col.Name);
}
/// <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
});
}
}
public static void UpdateDataSet(this MSMO.Column column, Data.ApplicationDataSet.TBColumnsRow dataRow)
{
try
{
//dataRow.X = column.X;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
/// <summary>
/// Initializes a new instance of the Column class.
/// </summary>
public Column(SMO.Column column)
{
AddinHelper.Common.WriteToDebugWindow(string.Format("SMOH.{0}({1})", "Column", column));
Name = column.Name;
DataType = column.DataType.ToString();
Default = column.Default;
MaximumLength = column.DataType.MaximumLength.ToString();
NumericPrecision = column.DataType.NumericPrecision.ToString();
NumericScale = column.DataType.NumericScale.ToString();
ID = column.ID.ToString();;
Identity = column.Identity.ToString();
InPrimaryKey = column.InPrimaryKey.ToString();
IsForeignKey = column.IsForeignKey.ToString();
Nullable = column.Nullable.ToString();
}
private static void Update(MSMO.Column column, SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow)
{
try
{
column.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
public override string GetColumnDbDataType(string tableName, string columnName)
{
Smo.Column column = _database.Tables[tableName].Columns[columnName];
string dbDataType = column.DataType.SqlDataType.ToString();
if (column.DataType.SqlDataType == Smo.SqlDataType.VarCharMax)
{
dbDataType = "VarChar";
}
else if (column.DataType.SqlDataType == Smo.SqlDataType.NVarCharMax)
{
dbDataType = "NVarChar";
}
else if (column.DataType.SqlDataType == Smo.SqlDataType.VarBinaryMax)
{
dbDataType = "VarBinary";
}
return(dbDataType);
}
public MySmo.DefaultConstraint GetDefaultConstraint(Smo.Column smo_c, MySmo.Column parentColumn, MySmo.Database parentDatabase = null)
{
var dc = smo_c.DefaultConstraint;
if (dc == null)
{
return(null);
}
var result = new MySmo.DefaultConstraint
{
CreateTime = dc.CreateDate,
IsSystemNamed = dc.IsSystemNamed,
Name = dc.Name,
Text = dc.Text,
ParentColumn = parentColumn,
ParentDatabase = parentDatabase
};
result.ExtendedProperties = GetExtendProperties(result, dc.ExtendedProperties);
return(result);
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a SQL view.</summary>
///
/// <param name="sqlColumn">The input sql column.</param>
/// <param name="viewText">View text to get table info.</param>
///--------------------------------------------------------------------------------
public void LoadViewProperty(Microsoft.SqlServer.Management.Smo.Column sqlColumn, string viewText)
{
try
{
// load the basic view property information
Urn = sqlColumn.Urn;
State = sqlColumn.State.ToString();
ReferencedColumn = sqlColumn.Name;
// hack to get table name, as not currently available via smo
if (viewText.IndexOf("." + ReferencedColumn) > 0)
{
string s = viewText.Substring(0, viewText.IndexOf("." + ReferencedColumn));
int spaceIndex = s.LastIndexOf(' ');
int periodIndex = s.LastIndexOf('.');
int quoteIndex = s.LastIndexOf('"');
if (quoteIndex > spaceIndex)
{
s = s.Substring(0, quoteIndex);
ReferencedTable = s.Substring(s.LastIndexOf('"') + 1);
}
else if (spaceIndex > periodIndex)
{
ReferencedTable = s.Substring(spaceIndex + 1);
}
else
{
ReferencedTable = s.Substring(periodIndex + 1);
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception)
{
throw;
}
}
private void columnsPage_ShowFromNext(object sender, EventArgs e)
{
columnsGrid.Rows.Clear();
idColumnComboBox.Items.Clear();
TableFactory tf = new TableFactory();
string tableName = m_Edit.TableName;
Smo.Table t = tf.FindTableByName(tableName);
if (t == null)
{
return;
}
// Get any domains already associated with the table
IColumnDomain[] curDomains = m_Edit.ColumnDomains;
columnsGrid.RowCount = t.Columns.Count;
for (int i = 0; i < columnsGrid.RowCount; i++)
{
Smo.Column c = t.Columns[i];
idColumnComboBox.Items.Add(c.Name);
DataGridViewRow row = columnsGrid.Rows[i];
row.Cells["dgcColumnName"].Value = c.Name;
Smo.DataType dt = c.DataType;
string dataType = dt.SqlDataType.ToString().ToLower();
if (dt.SqlDataType == Smo.SqlDataType.Char ||
dt.SqlDataType == Smo.SqlDataType.NChar ||
dt.SqlDataType == Smo.SqlDataType.VarChar ||
dt.SqlDataType == Smo.SqlDataType.NVarChar)
{
dataType += String.Format("({0})", dt.MaximumLength);
}
if (!c.Nullable)
{
dataType += " not null";
}
row.Cells["dgcDataType"].Value = dataType;
// Display any domain previously associated with the column
IColumnDomain cd = Array.Find <IColumnDomain>(curDomains,
delegate(IColumnDomain tcd) { return(tcd.ColumnName == c.Name); });
if (cd != null)
{
row.Cells["dgcDomain"].Value = cd.Domain;
}
row.Tag = c;
}
// Nothing initially selected
columnsGrid.CurrentCell = null;
// If we have a simple primary key, assume it's the feature ID column
if (String.IsNullOrEmpty(m_Edit.IdColumnName))
{
Smo.Column pk = TableFactory.GetSimplePrimaryKeyColumn(t);
if (pk != null)
{
idColumnComboBox.SelectedItem = pk.Name;
}
}
else
{
idColumnComboBox.SelectedItem = m_Edit.IdColumnName;
}
}
private static SQLInformation.Data.ApplicationDataSet.TBColumnsRow Add(Guid tableID, MSMO.Column column)
{
SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.TBColumns.NewTBColumnsRow();
dataRow.ID = Guid.NewGuid();
dataRow.Name_Column = column.Name;
dataRow.DBTable_ID = tableID;
dataRow.DataType = column.DataType.ToString();
dataRow.Identity = column.Identity;
dataRow.IsForeignKey = column.IsForeignKey;
dataRow.Nullable = column.Nullable;
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.TBColumns.AddTBColumnsRow(dataRow);
Common.ApplicationDataSet.TBColumnsTA.Update(Common.ApplicationDataSet.TBColumns);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
private static SQLInformation.Data.ApplicationDataSet.TBColumnsRow GetInfoFromSMO(MSMO.Column column, Guid tableID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace5("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.TBColumnsRow dataRow = null;
try
{
var dbs = from tb in Common.ApplicationDataSet.TBColumns
where tb.DBTable_ID == tableID
select tb;
var dbs2 = from db2 in dbs
where db2.Name_Column == column.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(column, dataRow);
}
else
{
dataRow = Add(tableID, column);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace5("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
internal Column(Microsoft.SqlServer.Management.Smo.Column Column)
{
_column = Column;
}
public GenResult Generate(params Oe.NodeBase[] targetElements)
{
#region Init
GenResult gr;
var t = (Smo.Table)targetElements[0].Tag;
var db = t.Parent;
var pks = Utils.GetPrimaryKeyColumns(t);
Smo.Column vc = pks[0], nc = null;
var sacs = Utils.GetSearchableColumns(t);
if (sacs.Count == 0)
{
nc = vc;
}
else
{
nc = sacs[0];
}
var sb = new StringBuilder();
#endregion
#region Gen
DataSet ds = null;
try
{
ds = db.ExecuteWithResults("SELECT [" + Utils.GetEscapeSqlObjectName(vc.Name) + "], [" + Utils.GetEscapeSqlObjectName(nc.Name) + "] FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + "].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ORDER BY [" + Utils.GetEscapeSqlObjectName(nc.Name) + "]");
}
catch { }
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "当前表中没有数据！生成失败！";
return(gr);
}
var tbn = Utils.GetEscapeSqlObjectName(t.Name);
sb.Append(@"
/// <summary>
/// " + Utils.GetDescription(t) + @"
/// </summary>
public enum " + tbn + @"
{");
foreach (DataRow c in ds.Tables[0].Rows)
{
sb.Append(@"
" + Utils.GetEscapeName(c[nc.Name].ToString()) + @" = " + c[vc.Name].ToString() + @",");
}
sb.Append(@"
}
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment.first = this.Properties[GenProperties.Tips].ToString();
gr.CodeSegment.second = sb.ToString();
return(gr);
//gr = new GenResult(GenResultTypes.CodeSegments);
//gr.CodeSegments.Add("1", sb.ToString());
//gr.CodeSegments.Add("2", sb.ToString());
//return gr;
//gr = new GenResult(GenResultTypes.Files);
//gr.Files.Add("1.txt", sb);
//gr.Files.Add("2.txt", sb);
//return gr;
#endregion
}
/// <summary>
/// To Create Table and return the message as String
/// </summary>
/// <returns> return the message as String </returns>
#region Create Table
public string createTable(string DatabaseName, string TableName, DataTable dtColumns)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
if (database != null)
{
bool tableExists = database.Tables.Contains(TableName);
if (tableExists)
{
SqlServerDisconnect();
return("Table Already Exist.kindly Enter Different Table Name");
}
else
{
Table tbl = new Table(database, TableName);
foreach (DataRow dr in dtColumns.Rows)
{
string columnName = dr["ColumName"].ToString();
string DataType = dr["DataType"].ToString();
string dataSize = dr["Size"].ToString();
Microsoft.SqlServer.Management.Smo.Column columntoAdd = null;
switch (DataType)
{
case "Varchar":
if (dataSize == "max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarChar(Convert.ToInt32(dataSize)));
}
break;
case "Int":
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.Int);
break;
case "nVarchar":
if (dataSize == "max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarChar(Convert.ToInt32(dataSize)));
}
break;
}
if (columntoAdd != null)
{
tbl.Columns.Add(columntoAdd);
}
}
tbl.Create();
SqlServerDisconnect();
return("Table Created Successfully !");
}
}
}
else
{
return("Enter valid SQL Connection Details");
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return("Sorry Error While Creating Table");
}
/// <summary>
/// Gets a code member property based on the provided column.
/// </summary>
private static CodeMemberProperty GetCodeMemberProperty(ILanguageProvider languageProvider, Smo.Column column)
{
var typeName = languageProvider.GetTypeString(column.DataType);
var fieldName = languageProvider.GetFormattedInternalFieldName(column.Name);
var retval = new CodeMemberProperty
{
Name = column.Name,
Attributes = MemberAttributes.Public | MemberAttributes.Final,
Type = new CodeTypeReference(typeName),
HasGet = true,
HasSet = true
};
retval.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), fieldName)));
retval.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), fieldName), new CodePropertySetValueReferenceExpression()));
return(retval);
}
/// <summary>
/// Get the max column length for the specified table and column
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public override string GetColumnMaxLength(string tableName, string columnName)
{
Smo.Column column = _database.Tables[tableName].Columns[columnName];
return(column.DataType.MaximumLength == -1 ? "MAX" : column.DataType.MaximumLength.ToString());
}
/// <summary>
/// Gets a code member field based on the provided column.
/// </summary>
private static CodeMemberField GetCodeMemberField(ILanguageProvider languageProvider, Smo.Column column)
{
var fieldName = languageProvider.GetFormattedInternalFieldName(column.Name);
var typeName = languageProvider.GetTypeString(column.DataType);
return(new CodeMemberField
{
Name = fieldName,
Attributes = MemberAttributes.Private,
Type = new CodeTypeReference(typeName)
});
}
