public string CopyDataBase(string BackUpFile, string DatabaseFilePath, string _databaseName)
{
try
{
string connectionString = connection.getConnectionString();
SqlConnection sqlConnection = new SqlConnection(connectionString);
Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(conn);
string databaseName = GetNextDatabaseName();
string backUpFile = BackUpFile;
string DatabaseFilesPath = DatabaseFilePath;
try
{
string Query = @"USE [master]
GO
RESTORE DATABASE " + databaseName + @" FROM DISK = N'" + backUpFile + @"' WITH MOVE 'Smart1' TO '" + DatabaseFilesPath + @"\" + databaseName + @".mdf',MOVE 'Smart1_Log' TO '" + DatabaseFilesPath + @"\" + databaseName + @".ldf',REPLACE,STATS=10";
server.ConnectionContext.ExecuteNonQuery(Query);
}
catch (Exception ex)
{ string str = ex.Message; }
return(databaseName);
}
catch (Exception ex) { string str = ex.Message; return(_databaseName); }
}
private void comboBoxEdit1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBoxEdit1.SelectedIndex != -1)
{
servername = comboBoxEdit1.SelectedText;
comboBoxEdit2.Properties.Items.Clear();
username = textEdit1.Text;
passwd = textEdit2.Text;
dbname = "master";
connectionstring = string.Format(@"Server={0};Database={1};User Id={2};Password={3};", servername, dbname, username, passwd);
//Server server = new Server(servername);
//"Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;"
SqlConnection sqlConnection = new SqlConnection(connectionstring);
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
Server server = new Server(serverConnection);
//using (var con = new SqlConnection(constring))
//using (var da = new SqlDataAdapter("SELECT Name FROM master.sys.databases", con))
//{
// var ds = new DataSet();
// da.Fill(ds);
foreach (Database db in server.Databases)
{
comboBoxEdit2.Properties.Items.Add(db.Name);
}
//}
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// 数据库操作类
/// </summary>
/// <param name="server"></param>
/// <param name="userId"></param>
/// <param name="password"></param>
/// <param name="database"></param>
public GenerateScripts(string server, string userId, string password, string database)
{
_Connection = new Microsoft.SqlServer.Management.Common.ServerConnection(server, userId, password);
_Server = new Microsoft.SqlServer.Management.Smo.Server(_Connection);
_Scripter = GetScripter(_Server);
_Database = _Server.Databases[database];
}
/// <summary>
/// Return false if login user is not DBO.
/// </summary>
/// <param name="tpf">a TracePropertiesForm object</param>
/// <returns>False if login user is not DBO.</returns>
bool permission(TracePropertiesForm tpf)
{
System.Data.SqlClient.SqlConnection conn;
if (tpf.RawConn != null)
{
conn = new System.Data.SqlClient.SqlConnection(tpf.RawConn);
}
else
{
string str_conn = string.Format("Data Source={0};Application Name={1};Database={2};",
tpf.ServerName, "sqlprofilerapp", "master");
if (tpf.Username == string.Empty)
{
str_conn += string.Format("Integrated Security={0};", true);
}
else
{
str_conn += string.Format("User ID={0};Password={1};", tpf.Username, tpf.Password);
}
conn = new System.Data.SqlClient.SqlConnection(str_conn);
}
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection sconn;
sconn = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server;
server = new Microsoft.SqlServer.Management.Smo.Server(sconn);
bool rc = server.Databases["master"].DboLogin;
conn.Close();
return(rc);
}
private static void Main(string[] args)
{
var sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)\SQLEXPRESS");
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
var server = new Server(serverConnection);
var errorLogPath = server.ErrorLogPath;
// Enumerate Files
}
static void Main( string[] args )
{
// Get Source Folder
string sourceFolder = ConfigurationManager.AppSettings["SourceFolder"];
if ( string.IsNullOrWhiteSpace(sourceFolder)) throw new Exception( "Missing Source Folder Application Setting" );
var dir = new DirectoryInfo( sourceFolder );
if ( dir == null ) throw new Exception( "Invalid Source Folder Directory" );
var files = dir.GetFiles( "*.csv" );
if ( !files.Any() ) throw new Exception( "There are not any *.csv files in the source folder" );
// Get Database
var configConnectionString = ConfigurationManager.ConnectionStrings["DbContext"];
if ( configConnectionString == null ) throw new Exception( "Missing Connection String" );
string connectionString = configConnectionString.ConnectionString;
if ( string.IsNullOrWhiteSpace( connectionString ) ) throw new Exception( "Invalid Connection String" );
var sqlConn = new SqlConnection( connectionString );
if ( sqlConn == null ) throw new Exception( "Invalid Connection String" );
var serverCon = new Microsoft.SqlServer.Management.Common.ServerConnection( sqlConn );
Server server = new Server( serverCon );
var db = server.Databases[sqlConn.Database];
if ( db == null ) throw new Exception( "Invalid Database Name in Connection String" );
string dbTablePrefix = ConfigurationManager.AppSettings["DbTablePrefix"];
if ( string.IsNullOrWhiteSpace( dbTablePrefix ) ) dbTablePrefix = "_csv_";
Console.Write( $"Starting Import [{DateTime.Now.ToShortTimeString()}]" );
// Process Files
foreach ( var file in files )
{
Console.WriteLine();
Console.Write( string.Format( "Processing {0}...", file.Name ) );
// Read all the records
var records = GetRecords( file.FullName );
// Determine the best field types
var fields = GetFields( records );
// Create the table
string tableName = dbTablePrefix + Path.GetFileNameWithoutExtension( file.FullName );
CreateTable( db, tableName, fields );
// Import records
InsertRecords( connectionString, tableName, fields, records );
}
Console.WriteLine();
Console.Write( $"Finished Import [{DateTime.Now.ToShortTimeString()}]" );
Console.ReadLine();
}
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
/// <summary>
/// Contructor de clase. Inicializa el servidor a usar
/// </summary>
/// <param name="serverName">Nombre de servidor e instancia</param>
/// <param name="user">Nombre de usuario</param>
/// <param name="password">Contraseña del usuario</param>
public Guionista(String serverName, String user, String password)
{
SqlConnection sqlConnection = new SqlConnection(@"Data Source=" + serverName + ";Initial Catalog=REALAIS_DESKMANAGER;User ID=" + user + ";Password="******";Max Pool Size=250");
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
server = new Server(serverConnection);
alter = false;
}
public void ExecuteScriptAndUpdateVersionTable(FileInfo fi)
{
bool success = true;
Exception exc = null;
string scriptText = null;
if (string.IsNullOrWhiteSpace(_props.Encoding))
{
scriptText = File.ReadAllText(fi.FullName);
}
else
{
scriptText = File.ReadAllText(fi.FullName, ArgumentHelper.GetEncoding(_props.Encoding));
}
using (SqlConnection sqlConnection = new SqlConnection(_props.ConnectionString))
{
Microsoft.SqlServer.Management.Common.ServerConnection svrConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(svrConnection);
string fileName = fi.FullName.Substring(fi.FullName.IndexOf(_props.VersionScriptsFolder, StringComparison.Ordinal));
bool hasRows = false;
using (SqlDataReader reader = server.ConnectionContext.ExecuteReader($"SELECT * FROM {_props.VersionTable} WHERE FileName = '{fileName}'"))
{
hasRows = reader.HasRows;
}
if (!hasRows)
{
server.ConnectionContext.BeginTransaction();
try
{
server.ConnectionContext.ExecuteNonQuery(scriptText);
}
catch (Exception ex)
{
success = false;
exc = ex;
server.ConnectionContext.RollBackTransaction();
}
if (success)
{
server.ConnectionContext.CommitTransaction();
UpdateVersion(fileName, scriptText);
}
}
}
if (!success)
{
throw exc;
}
}
//METODO QUE DESCOBRE O DIRETORIO PADRAO DE BACKUP DO SQL
private void DescobrirDiretoriosPadrao(out string diretorioDados, out string diretorioLog, out string diretorioBackup)
{
using (var connection = new System.Data.SqlClient.SqlConnection(@"Server=.\SQLEXPRESS;Database=master;Trusted_Connection=True;"))
{
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
diretorioDados = !string.IsNullOrWhiteSpace(server.Settings.DefaultFile) ? server.Settings.DefaultFile : (!string.IsNullOrWhiteSpace(server.DefaultFile) ? server.DefaultFile : server.MasterDBPath);
diretorioLog = !string.IsNullOrWhiteSpace(server.Settings.DefaultLog) ? server.Settings.DefaultLog : (!string.IsNullOrWhiteSpace(server.DefaultLog) ? server.DefaultLog : server.MasterDBLogPath);
diretorioBackup = !string.IsNullOrWhiteSpace(server.Settings.BackupDirectory) ? server.Settings.BackupDirectory : server.BackupDirectory;
}
}
public static string ScriptTable(string connectionString, string schemaName, string tableName)
{
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
//System.Console.WriteLine("Host: {0}", builder.DataSource);
//System.Console.WriteLine("Data: {0}", builder.InitialCatalog);
//System.Console.WriteLine("ISec: {0}", builder.IntegratedSecurity);
//System.Console.WriteLine("User: {0}", builder.UserID);
//System.Console.WriteLine("Pass: {0}", builder.Password);
var conn = new System.Data.SqlClient.SqlConnection(connectionString);
var sc = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
var server = new Microsoft.SqlServer.Management.Smo.Server(sc);
//server.ConnectionContext.Connect();
// Getting version automatically connects.
//System.Console.WriteLine("Server Version: {0}", server.Information.Version);
var sb = new StringBuilder("");
var database = server.Databases[builder.InitialCatalog];
var table = database.Tables[tableName, schemaName];
// Script DROP.
var options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
// If IncludeIfNotExists = true then procedure text will be generated
// through "EXEC dbo.sp_executesql @statement = N'".
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
var strings = table.Script(options);
foreach (var s in strings)
{
sb.AppendLine(s);
}
sb.AppendLine();
// Script CREATE.
options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
//options.AppendToFile = true;
options.ClusteredIndexes = true;
options.NoCollation = true;
//options.DriClustered = false;
//options.NonClusteredIndexes = true;
//options.DriNonClustered = false;
//options.Indexes = true;
//options.DriIndexes = false;
//options.FileName = fileInfo.FullName;
//options.Permissions = true;
strings = table.Script(options);
foreach (var s in strings)
{
sb.AppendLine(s);
}
return(sb.ToString());
}
private static Microsoft.SqlServer.Management.Smo.Database GetDatabase()
{
var databaseName = System.Configuration.ConfigurationManager.AppSettings["ActiveDatabaseConnection"].ToString();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[databaseName].ToString();
var conn = new Microsoft.SqlServer.Management.Common.ServerConnection();
conn.ConnectionString = connectionString;
var server = new Microsoft.SqlServer.Management.Smo.Server(conn);
server.SetDefaultInitFields(typeof (Microsoft.SqlServer.Management.Smo.StoredProcedure), "IsSystemObject");
server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter), "IsOutputParameter");
server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter), "DataType");
server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter),"DefaultValue");
var database = server.Databases[databaseName];
return database;
}
private void button2_Click(object sender, EventArgs e) //delete db
{
var serverName = ".";
Server svr = new Server(serverName);
var dbToDrop = comboBoxDatabases.SelectedItem.ToString();//textBoxDBToDrop.Text;
List <Database> databasesToDelete = new List <Database>();
foreach (Database db in svr.Databases) //delete all databases with entered name
{
if (db.Name == dbToDrop)
{
databasesToDelete.Add(db);
}
}
databasesToDelete.ForEach(x =>
{
if (x.ActiveConnections > 0) //close all connections and drop db
{
string connectionString = svr.ConnectionContext.ToString();
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = ".";
builder["integrated Security"] = true;
builder["Initial Catalog"] = dbToDrop;
SqlConnection sqlConn = new SqlConnection(builder.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);
sqlConn.Open();
String sqlCOmmandText = @"
USE master
ALTER DATABASE " + dbToDrop + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [" + dbToDrop + "]";
SqlCommand sqlCommand = new SqlCommand(sqlCOmmandText, sqlConn);
sqlCommand.ExecuteNonQuery();
sqlConn.Close();
}
else
{
x.Drop();
}
});
comboBoxDatabases.Items.Clear();
populateDatabases();
}
public override void GetDatabases(Common.Entities.MetaDataSchema.Project project)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(project.ExtractorManager.ConnectionString);
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection);
project.Databases.Clear();
foreach (Microsoft.SqlServer.Management.Smo.Database database in server.Databases)
{
Entities.MetaDataSchema.Database db = new Common.Entities.MetaDataSchema.Database();
db.Name = database.Name;
db.ParentProject = project;
project.Databases.Add(db);
project.AddDatabaseNode(project.SchemaDataset.Schema, db.GuidId, "", db.Name, db.Name);
}
}
public override void GetDatabases(Common.Entities.MetaDataSchema.Project project)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(project.ExtractorManager.ConnectionString);
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection);
project.Databases.Clear();
foreach (Microsoft.SqlServer.Management.Smo.Database database in server.Databases)
{
Entities.MetaDataSchema.Database db = new Common.Entities.MetaDataSchema.Database();
db.Name = database.Name;
db.ParentProject = project;
project.Databases.Add(db);
project.AddDatabaseNode(project.SchemaDataset.Schema, db.GuidId, "", db.Name, db.Name);
}
}
//NG 18-1-2009
//Here I Intialize sql connection for master server transaction
public static bool fTestGlobalConenction(string pConnStr)
{
try {
string vDecryptedText = "";
string vlConectionString = "";
vDecryptedText = pConnStr;
vlConectionString = sDecrypt(vDecryptedText);
System.Data.SqlClient.SqlConnection vSqlConnection = new System.Data.SqlClient.SqlConnection(vlConectionString);
Microsoft.SqlServer.Management.Common.ServerConnection vConn = new Microsoft.SqlServer.Management.Common.ServerConnection(vSqlConnection);
vConn.Connect();
vConn.Disconnect();
return(true);
} catch (Exception ex) {
return(false);
}
}
public void Backup(string backupFile, string DatabaseName)
{
Microsoft.SqlServer.Management.Common.ServerConnection connection;
Console.WriteLine(string.Format("Connecting to SQL server {0}", Server));
// Initialize our common objects
connection = new Microsoft.SqlServer.Management.Common.ServerConnection(Server);
smoServer = new Server(connection);
database = smoServer.Databases[DatabaseName];
if (false && database == null)
{
// Invalid database name.
Console.WriteLine(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
foreach (Database db in smoServer.Databases)
{
Console.WriteLine(string.Format("Available database: '{1}' on server '{0}'.", Server, db.Name));
}
throw new Exception(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
}
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = DatabaseName;
backup.Devices.AddDevice(backupFile, DeviceType.File);
backup.Incremental = false;
backup.CompressionOption = BackupCompressionOptions.On;
backup.NoRecovery = true;
// FileNameAndPathSaved.Set(context, backupFile);
// Set status handlers.
backup.PercentCompleteNotification = 10; // Send updates every 10%
backup.PercentComplete += new PercentCompleteEventHandler(BackupProgressEventHandler);
// Purge the file if it's already there.
if (File.Exists(backupFile))
{
File.Delete(backupFile);
}
Console.WriteLine(string.Format("Backing up to file {0}", backupFile));
backup.SqlBackup(smoServer);
}
private void sTestConenction(string pRegKey)
{
try {
//Microsoft.Win32.RegistryKey vRegVer = default(Microsoft.Win32.RegistryKey);
//string vPath = null;
//string vDecryptedText = "";
//vPath = "SOFTWARE\\ProVision";
//vRegVer = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(vPath);
//vDecryptedText = vRegVer.GetValue(pRegKey).ToString();
////vDecryptedText = "rz6hrkSLFbceHYh20294wkDXx+u/olCta5b6TAodMy2rlcBvg4nFgIFb9cN4KzGOi0FbTfjz0sir1ZQDx6TYTB0wTdmbqCcdzN14gEFfWWk3rtptHHFSB1A5EvCJVDgEmdT4fa64KSM2U1C7pUIcZrjzqcdcLaFCTFr/DjUXQS8=";
//vConectionString = sDecrypt(vDecryptedText);
//vConectionString = "Data Source=EIME00/EIMESRV;Initial Catalog=StudentExam;User ID=sa;Password=EimeP@$$w0rd";
if (Debugger.IsAttached == true)
{
vConectionString = "Data Source=ARCI;Initial Catalog=GoodShepherd;Persist Security Info=True;User ID=sa;Password=P@$$w0rdMeedos4";
}
else
{
vConectionString = "Data Source=Lenovo-PC\\SQL2014;Initial Catalog=GoodShepherd;User Id=sa;Password=3112005;";
}
System.Data.SqlClient.SqlConnection vSqlConnection = new System.Data.SqlClient.SqlConnection(vConectionString);
Microsoft.SqlServer.Management.Common.ServerConnection vConn = new Microsoft.SqlServer.Management.Common.ServerConnection(vSqlConnection);
vConn.Connect();
vConn.Disconnect();
vSuccess = true;
// MessageBox.Show("Passed !!!!!!!!!!!!!!");
GoodShepherd.Properties.Settings.Default["GoodShepherdConnectionString"] = BasicClass.vConectionString;
} catch (Exception ex) {
FRM_DBConfiguration vFrm = new FRM_DBConfiguration();
vFrm.vRegKey = pRegKey;
vFrm.ShowDialog();
if (vFrm.vSuccess == true)
{
sTestConenction(pRegKey);
vSuccess = true;
}
else
{
vSuccess = false;
}
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Helper constructor with connection string builder
/// </summary>
/// <param name="serverName">The name of the Sql Server</param>
/// <param name="databaseName">Name of the database</param>
/// <param name="userName">Name of the user (usually sysdba)</param>
/// <param name="password">Password</param>
public DbHandler(string serverName, string databaseName, string userName, string password)
{
_serverName = serverName;
_databaseName = databaseName;
_serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(
serverName,
userName,
password
);
var connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
connStringBuilder.DataSource = serverName;
connStringBuilder.InitialCatalog = databaseName;
connStringBuilder.UserID = userName;
connStringBuilder.Password = password;
OpenDbConnection(connStringBuilder.ToString());
}
public void ExecuteScriptAndUpdateVersionTable(int versionNo, System.IO.FileInfo fi)
{
bool success = true;
Exception exc = null;
string scriptText = null;
if (string.IsNullOrWhiteSpace(_props.Encoding))
{
scriptText = File.ReadAllText(fi.FullName);
}
else
{
scriptText = File.ReadAllText(fi.FullName, ArgumentHelper.GetEncoding(_props.Encoding));
}
using (SqlConnection sqlConnection = new SqlConnection(_props.ConnectionString))
{
Microsoft.SqlServer.Management.Common.ServerConnection svrConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(svrConnection);
server.ConnectionContext.BeginTransaction();
try
{
server.ConnectionContext.ExecuteNonQuery(scriptText);
}
catch (Exception ex)
{
success = false;
exc = ex;
server.ConnectionContext.RollBackTransaction();
}
if (success)
{
server.ConnectionContext.CommitTransaction();
this.UpdateVersion(versionNo, scriptText);
}
}
if (!success)
{
throw exc;
}
}
private void sTestConenction(string pRegKey)
{
try {
Microsoft.Win32.RegistryKey vRegVer = default(Microsoft.Win32.RegistryKey);
//string vPath = null;
//string vDecryptedText = "";
//vPath = "SOFTWARE\\Xlab Software Solution\\ERP Superior V2.0";
//vRegVer = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(vPath);
//vDecryptedText = vRegVer.GetValue(pRegKey);
//vConectionString = sDecrypt(vDecryptedText);
//vConectionString = "Data Source=EIME00/EIMESRV;Initial Catalog=StudentExam;User ID=sa;Password=EimeP@$$w0rd";
vConectionString = "Data Source=.;Initial Catalog=StudentExam;User ID=sa;Password=P@$$w0rdMeedos4";
System.Data.SqlClient.SqlConnection vSqlConnection = new System.Data.SqlClient.SqlConnection(vConectionString);
Microsoft.SqlServer.Management.Common.ServerConnection vConn = new Microsoft.SqlServer.Management.Common.ServerConnection(vSqlConnection);
vConn.Connect();
vConn.Disconnect();
vSuccess = true;
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
void comboBoxEdit2_Click(object sender, EventArgs e)
{
// throw new NotImplementedException();
if (comboBoxEdit2.Properties.Items.Count == 0)
{
username = textEdit1.Text;
passwd = textEdit2.Text;
//if (comboBoxEdit2.SelectedIndex != -1)
//{
connectionstring = string.Format(@"Server={0};Database={1};User Id={2};Password={3};", servername, dbname, username, passwd);
SqlConnection sqlConnection = new SqlConnection(connectionstring);
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
try
{
Server server = new Server(serverConnection);
//using (var con = new SqlConnection(constring))
//using (var da = new SqlDataAdapter("SELECT Name FROM master.sys.databases", con))
//{
// var ds = new DataSet();
// da.Fill(ds);
foreach (Database db in server.Databases)
{
comboBoxEdit2.Properties.Items.Add(db.Name);
}
//}
}
catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException ex)
{
MessageBox.Show(ex.Message);
}
// }
}
else
{
dbname = comboBoxEdit2.SelectedText;
connectionstring = string.Format(@"Server={0};Database={1};User Id={2};Password={3};", servername, dbname, username, passwd);
}
}
static void Main(string[] args)
{
Microsoft.SqlServer.Management.Smo.Server server;
SqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=LOCAL");
//build a "serverConnection" with the information of the "sqlConnection"
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
//The "serverConnection is used in the ctor of the Server.
server = new Server(serverConnection);
db = server.Databases["TestDB"];
Table tbl;
tbl = db.Tables["Sales"];
foreach (ForeignKey fk in tbl.ForeignKeys)
{
Console.WriteLine("Foreign key {0} references table {1} and key {2}", fk.Name, fk.ReferencedTable, fk.ReferencedKey);
}
}
//*****************************表單自訂Fuction****************************************
#region WfRegenView
//todo:效能安全姓及來源DB連線還要調整
private void WfReGenView()
{
try
{
StringBuilder sbDDL = new StringBuilder();
var urns = new List <Urn>();
System.Collections.Specialized.StringCollection strDropCollection;
System.Collections.Specialized.StringCollection strCreateCollection;
StringBuilder sbSql;
DataTable dtAta;
string ata03; //資料庫名稱
if (BoSecurity == null)
{
return;
}
// Connect to the local, default instance of SQL Server.
SqlConnection sc = new SqlConnection(YR.ERP.Shared.GlobalVar.SQLCA_SecConSTR);
Microsoft.SqlServer.Management.Common.ServerConnection sqlConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sc);
Server server = new Server(sqlConn);
// Reference the database.
Database dbSecurity = server.Databases[sqlConn.DatabaseName];
//先刪除
Scripter scrpDrop = new Scripter(server);
scrpDrop.Options.ScriptDrops = true;
scrpDrop.Options.WithDependencies = false;
scrpDrop.Options.Indexes = false; // To include indexes
scrpDrop.Options.DriAllConstraints = true; // to include referential constraints in the script
scrpDrop.Options.Triggers = false;
scrpDrop.Options.FullTextIndexes = false;
scrpDrop.Options.NoCollation = false;
scrpDrop.Options.Bindings = false;
scrpDrop.Options.IncludeIfNotExists = false;
scrpDrop.Options.ScriptBatchTerminator = true;
scrpDrop.Options.ExtendedProperties = true;
scrpDrop.PrefetchObjects = true; // some sources suggest this may speed things up
//再建立
Scripter scrpCreate = new Scripter(server);
scrpCreate.Options.ScriptDrops = false;
scrpCreate.Options.WithDependencies = false;
scrpCreate.Options.Indexes = false; // To include indexes
scrpCreate.Options.DriAllConstraints = true; // to include referential constraints in the script
scrpCreate.Options.Triggers = false;
scrpCreate.Options.FullTextIndexes = false;
scrpCreate.Options.NoCollation = false;
scrpCreate.Options.Bindings = false;
scrpCreate.Options.IncludeIfNotExists = false;
scrpCreate.Options.ScriptBatchTerminator = true;
scrpCreate.Options.ExtendedProperties = true;
scrpCreate.PrefetchObjects = true; // some sources suggest this may speed things up
// Iterate through the views in database and script each one. Display the script.
foreach (Microsoft.SqlServer.Management.Smo.View view in dbSecurity.Views)
{
// check if the view is not a system object
if (view.IsSystemObject == false)
{
urns.Add(view.Urn);
}
}
strCreateCollection = scrpCreate.Script(urns.ToArray());
//foreach (string st in strCreateCollection)
//{
// // It seems each string is a sensible batch, and putting GO after it makes it work in tools like SSMS.
// // Wrapping each string in an 'exec' statement would work better if using SqlCommand to run the script.
// sbDDL.AppendLine(st);
// sbDDL.AppendLine("GO");
//}
if (strCreateCollection != null)
{
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM ata_tb");
dtAta = BoSecurity.OfGetDataTable(sbSql.ToString(), null);
if (dtAta != null && dtAta.Rows.Count > 0)
{
foreach (DataRow drTemp in dtAta.Rows)
{
//這裡要建立刪除的VIEW
ata03 = drTemp["ata03"] == DBNull.Value ? "" : drTemp["ata03"].ToString();
Database dbTemp = server.Databases[ata03];
if (dbTemp == null)
{
continue;
}
//假如主連線與要刪除view的db是相同的,刪不做處理
if (dbSecurity.Name.ToUpper() == dbTemp.Name.ToUpper())
{
continue;
}
urns.Clear();
// Iterate through the views in database and script each one. Display the script.
foreach (Microsoft.SqlServer.Management.Smo.View view in dbTemp.Views)
{
// check if the view is not a system object
if (view.IsSystemObject == false)
{
urns.Add(view.Urn);
}
}
strDropCollection = scrpDrop.Script(urns.ToArray());
if (strDropCollection != null && strDropCollection.Count > 0)
{
dbTemp.ExecuteNonQuery(strDropCollection);
strDropCollection.RemoveAt(0);//執行過後會在第一筆加入 USE DATABASE...因此在這裡移除,避免錯誤
}
dbTemp.ExecuteNonQuery(strCreateCollection);
strCreateCollection.RemoveAt(0);//執行過後會在第一筆加入 USE DATABASE...因此在這裡移除,避免錯誤
}
}
}
}
catch (System.Exception ex)
{
throw ex;
}
}
public static void includeClrExtensionToDB(string servId) {
string fn; string sql;
using (var rdr = getInfo("WebCourseStatistics.sql", out fn)) sql = rdr.ReadToEnd();
if (string.IsNullOrEmpty(sql)) return;
var cb = getConnectionStringInfo(servId);
sql = string.Format(sql, cb.UserID, servId == "lm-virtual-1_run" ? "NewLMComServices" : "NewLMCom", BitConverter.ToString(File.ReadAllBytes(fn)).Replace("-", ""));
//File.WriteAllText(@"d:\temp\pom.sql", sql);
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(cb.DataSource, cb.UserID, cb.Password);
Microsoft.SqlServer.Management.Smo.Server serv = new Microsoft.SqlServer.Management.Smo.Server(connection);
serv.ConnectionContext.ExecuteNonQuery(sql);
serv.ConnectionContext.ExecuteNonQuery("go");
}
public void Restore(string backupFile, string DatabaseName)
{
Microsoft.SqlServer.Management.Common.ServerConnection connection;
Console.WriteLine(string.Format("Connecting to SQL server {0}", Server));
// Initialize our common objects
connection = new Microsoft.SqlServer.Management.Common.ServerConnection(Server);
smoServer = new Server(connection);
database = smoServer.Databases[DatabaseName];
if (false && database == null)
{
// Invalid database name.
Console.WriteLine(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
foreach (Database db in smoServer.Databases)
{
Console.WriteLine(string.Format("Available database: '{1}' on server '{0}'.", Server, db.Name));
}
throw new Exception(string.Format("Invalid database '{1}' on server '{0}'.", Server, DatabaseName));
}
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Database = DatabaseName;
restore.Devices.AddDevice(backupFile, DeviceType.File);
restore.ReplaceDatabase = true;
RelocateFile DataFile = new RelocateFile();
var fileList = restore.ReadFileList(smoServer);
string MDF = fileList.Rows[0][1].ToString();
DataFile.LogicalFileName = restore.ReadFileList(smoServer).Rows[0][0].ToString();
DataFile.PhysicalFileName = smoServer.Databases[DatabaseName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
string LDF = restore.ReadFileList(smoServer).Rows[1][1].ToString();
LogFile.LogicalFileName = restore.ReadFileList(smoServer).Rows[1][0].ToString();
LogFile.PhysicalFileName = smoServer.Databases[DatabaseName].LogFiles[0].FileName;
restore.RelocateFiles.Add(DataFile);
restore.RelocateFiles.Add(LogFile);
// Kill all connections on the database.
if (database != null)
{
Console.WriteLine(string.Format("Deleting database {0}.", DatabaseName));
smoServer.KillDatabase(DatabaseName);
//context.TrackBuildMessage("Killing all processes");
//smoServer.KillAllProcesses(database.Name);
//context.TrackBuildMessage("Setting single-user mode");
//database.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
//database.Alter(TerminationClause.RollbackTransactionsImmediately);
//context.TrackBuildMessage("Detatching database");
//smoServer.DetachDatabase(DatabaseName.Get(context), false);
}
// Set some status update event handlers.
restore.PercentCompleteNotification = 10; // Send updates every 10%
restore.PercentComplete += new PercentCompleteEventHandler(RestoreProgressEventHandler);
Console.WriteLine("Restoring");
restore.SqlRestore(smoServer);
}
public static bool ExecuteSQL(string sql)
{
Microsoft.SqlServer.Management.Smo.Server server = null;
Microsoft.SqlServer.Management.Common.ServerConnection svrConnection = null;
System.Data.SqlClient.SqlConnection sqlConnection = null;
try
{
sqlConnection = new System.Data.SqlClient.SqlConnection(DataSDMX.SQLConnString_DB.ConnectionString);
svrConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
server = new Microsoft.SqlServer.Management.Smo.Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.ForceDisconnected();
return true;
}
catch (Exception ex)
{
if (server != null)
{
server.ConnectionContext.ForceDisconnected();
}
return false;
}
}
private Database GetDatabase(ref Server srv)
{
try
{
Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(new System.Data.SqlClient.SqlConnection(GetConnectionString()));
Server s = new Server(conn);
s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
s.SetDefaultInitFields(typeof(Table), "IsSystemObject");
s.SetDefaultInitFields(typeof(View), "IsSystemObject");
s.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
s.SetDefaultInitFields(typeof(Trigger), "IsSystemObject");
srv = s; //Return the server as well, in the evnet we are using dependency walkaer.
return s.Databases[Database];
}
catch (Exception err)
{
MMDB.Core.MMDBLogFile.Log(err);
return null;
}
}
private static void GenerateScript(SqlConnectionStringBuilder builderTenantLocal, string databaseTenantLiveName, FileInfo fileScript, Encoding encoding)
{
Console.WriteLine();
Console.WriteLine();
Console.Write($"GenerateScript.");
var tableNames = new string[] { "__MigrationHistory", "Culture", "Department", "Folder", "Subscription", "Tenant", "TenantSubscription", "Role", "User", "UserFolder", "UserRole", "RoleUsers", };
using (var connection = new SqlConnection(builderTenantLocal.ConnectionString))
{
var scriptDataInsert = "";
var scriptSchemaDrop = "";
var scriptSchemaCreate = "";
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(connection);
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
var database = server.Databases[builderTenantLocal.InitialCatalog];
var scripter = new Microsoft.SqlServer.Management.Smo.Scripter(server);
var tables = new Microsoft.SqlServer.Management.Smo.UrnCollection();
File.WriteAllText(fileScript.FullName, $"USE [{databaseTenantLiveName}]{Environment.NewLine}{Environment.NewLine}", encoding);
tables.Clear();
scripter.Options.ScriptDrops = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
scripter.Options.WithDependencies = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.NoCommandTerminator = true;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.FileName = fileScript.FullName;
scripter.Options.AppendToFile = true;
scripter.Options.ToFileOnly = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.PrimaryObject = true;
scripter.Options.ExtendedProperties = true;
scripter.Options.Encoding = encoding;
foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
{
tables.Add(table.Urn);
}
scriptSchemaDrop = string.Join(Environment.NewLine, scripter.EnumScript(tables));
tables.Clear();
scripter.Options.ScriptDrops = false;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
scripter.Options.WithDependencies = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.NoCommandTerminator = true;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.FileName = fileScript.FullName;
scripter.Options.AppendToFile = true;
scripter.Options.ToFileOnly = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.PrimaryObject = true;
scripter.Options.ExtendedProperties = true;
scripter.Options.Encoding = encoding;
foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
{
tables.Add(table.Urn);
}
scriptSchemaCreate = string.Join(Environment.NewLine, scripter.EnumScript(tables));
tables.Clear();
scripter.Options.ScriptDrops = false;
scripter.Options.ScriptSchema = false;
scripter.Options.ScriptData = true;
scripter.Options.WithDependencies = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.NoCommandTerminator = true;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.FileName = fileScript.FullName;
scripter.Options.AppendToFile = true;
scripter.Options.ToFileOnly = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.PrimaryObject = true;
scripter.Options.ExtendedProperties = true;
scripter.Options.Encoding = encoding;
foreach (var tableName in tableNames)
{
tables.Add(database.Tables[tableName].Urn);
}
scriptDataInsert = string.Join(Environment.NewLine, scripter.EnumScript(tables));
}
}
private void button1_Click(object sender, EventArgs e)
{
// validate the user input
if (this.textBox1.Text.Length == 0 ||
this.textBox2.Text.Length == 0 ||
this.textBox3.Text.Length == 0)
{
MessageBox.Show("Please ensure that you have completed all fields before clicking Import. Please check your input and try again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Application.UseWaitCursor = true;
SendMessage(this.Handle, 0x20, this.Handle, (IntPtr)1);
Application.DoEvents();
// parse the connection string
string sqlConnection = this.textBox2.Text;
string database = sqlConnection.Split(new string[] { "database=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string server = sqlConnection.Split(new string[] { "server=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string uid = sqlConnection.Split(new string[] { "uid=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string pwd = sqlConnection.Split(new string[] { "pwd=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
// connect to excel workbook
string excelConnection = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", this.textBox1.Text);
// execute procedure
try
{
// open excel workbook
using (OleDbConnection connection = new OleDbConnection(excelConnection))
{
connection.Open();
DataTable tableSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string table = tableSchema.Rows[0]["Table_Name"].ToString();
OleDbCommand command = new OleDbCommand(string.Format("SELECT * FROM [{0}]", table), connection);
using (DbDataReader dr = command.ExecuteReader())
{
// create new table
Microsoft.SqlServer.Management.Common.ServerConnection smoConn = new Microsoft.SqlServer.Management.Common.ServerConnection();
smoConn.ServerInstance = server;
smoConn.LoginSecure = false;
smoConn.Login = uid;
smoConn.Password = pwd;
Server dbserver = new Server(smoConn);
Database db = dbserver.Databases[database];
DataTable schema = dr.GetSchemaTable();
Table newTable = new Table(db, this.textBox3.Text);
for (int i = 0; i < schema.Rows.Count; i++)
{
Column column = new Column(newTable, schema.Rows[i][0].ToString());
column.DataType = DataType.VarChar(5000);
column.Nullable = true;
newTable.Columns.Add(column);
}
newTable.Create();
// copy data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
{
bulkCopy.DestinationTableName = this.textBox3.Text;
bulkCopy.WriteToServer(dr);
}
}
}
MessageBox.Show("Excel Spreadsheet data was imported successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Application.UseWaitCursor = false;
SendMessage(this.Handle, 0x20, this.Handle, (IntPtr)1);
Application.DoEvents();
}
/// <summary>
/// Return false if login user is not DBO.
/// </summary>
/// <param name="tpf">a TracePropertiesForm object</param>
/// <returns>False if login user is not DBO.</returns>
bool permission(TracePropertiesForm tpf)
{
System.Data.SqlClient.SqlConnection conn;
if (tpf.RawConn != null)
conn = new System.Data.SqlClient.SqlConnection(tpf.RawConn);
else
{
string str_conn = string.Format("Data Source={0};Application Name={1};Database={2};",
tpf.ServerName, "sqlprofilerapp", "master");
if (tpf.Username == string.Empty)
str_conn += string.Format("Integrated Security={0};", true);
else
str_conn += string.Format("User ID={0};Password={1};", tpf.Username, tpf.Password);
conn = new System.Data.SqlClient.SqlConnection(str_conn);
}
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection sconn;
sconn = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server;
server = new Microsoft.SqlServer.Management.Smo.Server(sconn);
bool rc = server.Databases["master"].DboLogin;
conn.Close();
return rc;
}
//delete db
private void button2_Click(object sender, EventArgs e)
{
var serverName = ".";
Server svr = new Server(serverName);
var dbToDrop = comboBoxDatabases.SelectedItem.ToString();//textBoxDBToDrop.Text;
List<Database> databasesToDelete = new List<Database>();
foreach (Database db in svr.Databases) //delete all databases with entered name
{
if (db.Name == dbToDrop)
{
databasesToDelete.Add(db);
}
}
databasesToDelete.ForEach(x =>
{
if (x.ActiveConnections > 0) //close all connections and drop db
{
string connectionString = svr.ConnectionContext.ToString();
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = ".";
builder["integrated Security"] = true;
builder["Initial Catalog"] = dbToDrop;
SqlConnection sqlConn = new SqlConnection(builder.ToString());
Microsoft.SqlServer.Management.Common.ServerConnection serverConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn);
Server svrSql = new Server(serverConn);
sqlConn.Open();
String sqlCOmmandText = @"
USE master
ALTER DATABASE " + dbToDrop + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [" + dbToDrop + "]";
SqlCommand sqlCommand = new SqlCommand(sqlCOmmandText, sqlConn);
sqlCommand.ExecuteNonQuery();
sqlConn.Close();
}
else
{
x.Drop();
}
});
comboBoxDatabases.Items.Clear();
populateDatabases();
}
/// <summary>
/// Generates Data Access, Mapping and Transfer Code from the Database
/// </summary>
/// <param name="FolderPath"></param>
/// <param name="ServerName"></param>
/// <param name="DatabaseName"></param>
public static void GenerateFromDatabase(GenerationInfo GenInfo)
{
DirectoryInfo d = new DirectoryInfo(GenInfo.FolderPath);
Microsoft.SqlServer.Management.Common.ServerConnection svrCon = new Microsoft.SqlServer.Management.Common.ServerConnection(GenInfo.ServerName);
Server svr = new Server(svrCon);
String DbScriptsPath = GenInfo.FolderPath + @"\Procedures";
foreach (Microsoft.SqlServer.Management.Smo.Database datab in svr.Databases)
{
if (datab.IsAccessible)
{
if (!(DatabasesToIgnore().Contains(datab.Name)))
{
if (datab.Name.ToLower() == GenInfo.DataBase.ToLower())
{
Console.Write("Generating Code for Database: " + datab.Name + "\n");
String RepositoryPath = GenInfo.FolderPath + @"\" + datab.Name; // The Current Repository
String DataLayerNamespace = String.Format("{0}.{1}.{2}", GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name);
#region [ Clean / Create Directory ]
if (Directory.Exists(RepositoryPath))
{
// Directory Exists clean it out
DirectoryInfo dRepository = new DirectoryInfo(RepositoryPath);
foreach (FileInfo f in dRepository.GetFiles("*.*", SearchOption.AllDirectories))
{
f.Delete();
}
}
else
{
Directory.CreateDirectory(RepositoryPath);
}
#endregion
#region [ Tables ]
String RepositoryPathProcedures = RepositoryPath + @"\Procedures";
if (Directory.Exists(RepositoryPathProcedures))
{
Directory.Delete(RepositoryPathProcedures);
}
Directory.CreateDirectory(RepositoryPathProcedures);
Int32 GenerationStep = 0;
DALGenerator dg = new DALGenerator();
POCOGenerator pg = new POCOGenerator();
foreach (Table t in datab.Tables)
{
GenerationStep = 0;
try
{
if (t.Name == "Loan")
{
"$".IsNormalized();
}
if (!(t.IsSystemObject))
{
// Check if the table has a primary key, if not then dont generate anything. Table muist be normalized
if (ValidateSqlTable(t))
{
#region [ Generation Root ]
String cpFilename = RepositoryPathProcedures + @"\\" + "cp_" + t.Name + ".sql";
String sspFileName = RepositoryPathProcedures + @"\\" + "ssp_" + t.Name + ".sql";
String EntityFileName = RepositoryPath + @"\\" + t.Name + ".cs";
String DALFileName = RepositoryPath + @"\\" + t.Name + "DAL.cs";
String DOMFileName = RepositoryPath + @"\\" + t.Name + "DomainObject.cs";
GenerationStep = 1;
// [0] Stored Procedures
MGenerator.Tools.SqlGeneration.CrudProcedureGenerator gen = new SqlGeneration.CrudProcedureGenerator(datab, t);
gen.Generate(RepositoryPathProcedures, MGenerator.Tools.SqlGeneration.ProcedureGenerationType.Alter);
GenerationStep = 2;
ObjectiveSearchProcedureGenerator ospgen = new ObjectiveSearchProcedureGenerator(datab, t);
ospgen.Generate(RepositoryPathProcedures);
GenerationStep = 3;
// [1] Data Access Classes
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, dg.BuildDalClass(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, t));
GenerationStep = 4;
// [2] Entity / Structure Class
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, pg.BuildPoc(t));
GenerationStep = 5;
// [3] Service Layer
DomainServiceGraph svcgen = new DomainServiceGraph();
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, svcgen.BuildDomainObject(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, t));
GenerationStep = 6;
// [4] API
ApiGraph api = new ApiGraph();
string api_name =
api.WriteApiStation(GenInfo, datab, t,RepositoryPath);
#endregion
Console.Write(t.Name + "....COMPLETE" + "\n");
}
}
}
catch (Exception x)
{
Console.Write(t.Name + "....ERROR" + "\n");
// File.WriteAllText(RepositoryPath + t.Name + ".txt", x.Message + "\n Generation Step:" + GenerationStep.ToString() );
continue;
}
}
#endregion
#region [ Views ]
foreach (View view in datab.Views)
{
try
{
if (view.Name == "vwLOLicense")
{
String fd = "";
}
if (!(view.IsSystemObject))
{
if (ValidateSqlView(view))
{
ObjectiveSearchProcedureGenerator ospgen = new ObjectiveSearchProcedureGenerator(datab, view);
DomainServiceGraph svcgen = new DomainServiceGraph();
ospgen.Generate(RepositoryPathProcedures);
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, dg.BuildDalClass(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, view));
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, pg.BuildPoco(view));
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, svcgen.BuildDomainObject(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, view));
Console.Write(view.Name + "....COMPLETE" + "\n");
}
}
}
catch (Exception x)
{
Console.Write(view.Name + "....ERROR" + "\n");
continue;
}
}
#endregion
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
// validate the user input
if (this.textBox1.Text.Length == 0 ||
this.textBox2.Text.Length == 0 ||
this.textBox3.Text.Length == 0)
{
MessageBox.Show("Please ensure that you have completed all fields before clicking Import. Please check your input and try again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Application.UseWaitCursor = true;
SendMessage(this.Handle, 0x20, this.Handle, (IntPtr)1);
Application.DoEvents();
// parse the connection string
string sqlConnection = this.textBox2.Text;
string database = sqlConnection.Split(new string[] { "database=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string server = sqlConnection.Split(new string[] { "server=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string uid = sqlConnection.Split(new string[] { "uid=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
string pwd = sqlConnection.Split(new string[] { "pwd=" }, StringSplitOptions.None)[1].Split(Convert.ToChar(";"))[0];
// connect to excel workbook
string excelConnection = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", this.textBox1.Text);
// execute procedure
try
{
// open excel workbook
using (OleDbConnection connection = new OleDbConnection(excelConnection))
{
connection.Open();
DataTable tableSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
string table = tableSchema.Rows[0]["Table_Name"].ToString();
OleDbCommand command = new OleDbCommand(string.Format("SELECT * FROM [{0}]", table), connection);
using (DbDataReader dr = command.ExecuteReader())
{
// create new table
Microsoft.SqlServer.Management.Common.ServerConnection smoConn = new Microsoft.SqlServer.Management.Common.ServerConnection();
smoConn.ServerInstance = server;
smoConn.LoginSecure = false;
smoConn.Login = uid;
smoConn.Password = pwd;
Server dbserver = new Server(smoConn);
Database db = dbserver.Databases[database];
DataTable schema = dr.GetSchemaTable();
Table newTable = new Table(db, this.textBox3.Text);
for (int i = 0; i < schema.Rows.Count; i++)
{
Column column = new Column(newTable, schema.Rows[i][0].ToString());
column.DataType = DataType.VarChar(5000);
column.Nullable = true;
newTable.Columns.Add(column);
}
newTable.Create();
// copy data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
{
bulkCopy.DestinationTableName = this.textBox3.Text;
bulkCopy.WriteToServer(dr);
}
}
}
MessageBox.Show("Excel Spreadsheet data was imported successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Application.UseWaitCursor = false;
SendMessage(this.Handle, 0x20, this.Handle, (IntPtr)1);
Application.DoEvents();
}
private void SaveExtendedProperties(SQLInformation.Data.ApplicationDataSet.DatabasesRow database)
{
var instances = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_ID
select new { Name = item.Name_Instance, Port = item.Port };
string instanceName = (string)instances.First().Name;
int port = (int)instances.First().Port;
//MSMO.Server server = new MSMO.Server((string)instances.First());
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection();
connection.ServerInstance = string.Format("{0},{1}", instanceName, port);
connection.NetworkProtocol = Microsoft.SqlServer.Management.Common.NetworkProtocol.TcpIp;
MSMO.Server server = new MSMO.Server(connection);
server.ConnectionContext.LoginSecure = true; // SQL Authentication
//server.ConnectionContext.Login = "******";
//server.ConnectionContext.Password = "******";
server.ConnectionContext.ConnectTimeout = 10; // Seconds
MSMO.ExtendedPropertyCollection extendedProps = server.Databases[database.Name_Database].ExtendedProperties;
MSMO.Database smoDatabase = server.Databases[database.Name_Database];
try
{
//extendedProps["EP_Area"].Value = database.EP_Area;
extendedProps["EP_Area"].Value = te_EP_Area.Text;
extendedProps["EP_Area"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Area", te_EP_Area.Text);
//MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Area", database.EP_Area);
ep.Create();
}
try
{
extendedProps["EP_DBApprover"].Value = te_EP_DBApprover.Text;
extendedProps["EP_DBApprover"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_DBApprover", te_EP_DBApprover.Text);
ep.Create();
}
try
{
extendedProps["EP_DRTier"].Value = te_EP_DRTier.Text;
extendedProps["EP_DRTier"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_DRTier", te_EP_DRTier.Text);
ep.Create();
}
try
{
extendedProps["EP_PrimaryDBContact"].Value = te_EP_PrimaryDBContact.Text;
extendedProps["EP_PrimaryDBContact"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_PrimaryDBContact", te_EP_PrimaryDBContact.Text);
ep.Create();
}
try
{
extendedProps["EP_Team"].Value = te_EP_Team.Text;
extendedProps["EP_Team"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Team", te_EP_Team.Text);
ep.Create();
}
}
private bool ExecuteSP(string connectionString, string clientName)
{
bool error = false;
SqlConnection sqlCon = new SqlConnection(connectionString);
Microsoft.SqlServer.Management.Common.ServerConnection srvCon = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlCon);
try
{
sqlCon.Open();
//objSql
Server srv = new Server(srvCon);
//Reference the database.
Database db = srv.Databases[sqlCon.Database];
//Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
StoredProcedure sp = null;
if (rdobtnlstExecutionMode.SelectedValue == "1")
sp = new StoredProcedure(db, txtSPName.Text);
else if (rdobtnlstExecutionMode.SelectedValue == "2" || rdobtnlstExecutionMode.SelectedValue == "3")
sp = db.StoredProcedures[txtSPName.Text];
if (sp == null)
{
sqlCon.Close();
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Stored Procedure Not Found in '" + clientName + "')", true);
error = true;
}
else
{
//Set the TextMode property to false and then set the other object properties.
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextMode = false;
#region SP Parameters
if (!string.IsNullOrEmpty(txtSPParams.Text))
{
char[] splitter = { ',' };
char[] internalSplitter = { ' ' };
string[] spParameters = txtSPParams.Text.Replace(Environment.NewLine, string.Empty).Replace("\n", string.Empty).Split(splitter, StringSplitOptions.RemoveEmptyEntries);
foreach (string spParam in spParameters)
{
string[] p = spParam.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries);
string paramName = p[0];
string paramDataType = p[1].ToLower();
Microsoft.SqlServer.Management.Smo.DataType thisDt = null;
int scaleLength = 0;
int precision = 0;
string intermediateDT = paramDataType;
if (paramDataType.Contains("varchar"))
{
paramDataType = paramDataType.Replace("(", " ");
paramDataType = paramDataType.Replace(")", " ");
paramDataType = paramDataType.Replace(",", " ");
paramDataType = paramDataType.Replace(" ", " ");
scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
paramDataType = "varchar";
}
else if (paramDataType.Contains("decimal"))
{
paramDataType = paramDataType.Replace("(", " ");
paramDataType = paramDataType.Replace(")", " ");
paramDataType = paramDataType.Replace(",", " ");
paramDataType = paramDataType.Replace(" ", " ");
scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[2]);
paramDataType = "decimal";
}
else if (paramDataType.Contains("numeric"))
{
paramDataType = paramDataType.Replace("(", " ");
paramDataType = paramDataType.Replace(")", " ");
paramDataType = paramDataType.Replace(",", " ");
paramDataType = paramDataType.Replace(" ", " ");
scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[2]);
paramDataType = "numeric";
}
switch (paramDataType)
{
case "int":
thisDt = DataType.Int;
break;
case "bit":
thisDt = DataType.Bit;
break;
case "text":
thisDt = DataType.Text;
break;
case "datetime":
thisDt = DataType.DateTime;
break;
case "bigint":
thisDt = DataType.BigInt;
break;
case "varchar":
thisDt = DataType.VarChar(scaleLength);
paramDataType = intermediateDT;
break;
case "decimal":
thisDt = DataType.Decimal(scaleLength, precision);
paramDataType = intermediateDT;
break;
case "numeric":
thisDt = DataType.Numeric(scaleLength, precision);
paramDataType = intermediateDT;
break;
default:
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('SP Parameter with unknown datatype found.')", true);
error = true;
break;
}
if (error)
{
sqlCon.Close();
return error;
}
sp.Parameters.Add(new StoredProcedureParameter(sp, paramName, thisDt));
}
}
#endregion
//Set the TextBody property to define the stored procedure.
sp.TextBody = txtSPBody.Text.Replace("\n", Environment.NewLine);
if (rdobtnlstExecutionMode.SelectedValue == "1")
{
sp.QuotedIdentifierStatus = true;
//Create the stored procedure on the instance of SQL Server.
sp.Create();
}
else if (rdobtnlstExecutionMode.SelectedValue == "2")
{
sp.QuotedIdentifierStatus = true;
//Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.Alter();
}
else if (rdobtnlstExecutionMode.SelectedValue == "3")
{
//Remove the stored procedure.
sp.Drop();
}
}
}
catch (Exception ex)
{
sqlCon.Close();
divError.InnerHtml = "Following Error Occured : " + Environment.NewLine + ex.Message;
}
return error;
}
public static List <DbTable> FromDB(string dbName, string serverName, string user = null, SecureString pass = null)
{
var tables = new List <DbTable>();
var dicReferenceTable = new Dictionary <string, List <Reference> >();
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(serverName);
if (string.IsNullOrEmpty(user) == false)
{
serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(serverName, user, pass);
}
var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
var db = new Microsoft.SqlServer.Management.Smo.Database(server, dbName);
db.Refresh();
foreach (Microsoft.SqlServer.Management.Smo.Table table in db.Tables)
{
table.Refresh();
var dic = new Dictionary <string, string>();
foreach (Microsoft.SqlServer.Management.Smo.ForeignKey item in table.ForeignKeys)
{
dic.Add(item.Columns[0].Name, item.ReferencedTable);
if (dicReferenceTable.ContainsKey(item.ReferencedTable) == false)
{
dicReferenceTable.Add(item.ReferencedTable, new List <Reference>());
}
dicReferenceTable[item.ReferencedTable].Add(new Reference()
{
PropertyName = item.Columns[0].Name,
ReferenceTableName = table.Name
});
}
var indexes = GetIndexes(table);
var foreignKeys = GetForeignKey(table);
var columns = new List <DbTableColumn>();
var requiredMaxLengths = new List <RequiredMaxLength>();
var defaultValues = new List <DefaultValue>();
var hasColumnTypes = new List <HasColumnType>();
foreach (Microsoft.SqlServer.Management.Smo.Column item in table.Columns)
{
var sqlDataType = item.DataType.Name;
var dotnetType = _typeMapping[sqlDataType];
if (item.Nullable == true && dotnetType != "string" && dotnetType != "byte[]")
{
dotnetType = dotnetType + "?";
}
var entityProperty = new DbTableColumn()
{
DataType = dotnetType,
ColumnName = item.Name,
IsForeignKey = item.IsForeignKey,
IsPrimaryKey = item.InPrimaryKey,
IsIdentity = item.Identity
};
if (item.IsForeignKey == true)
{
entityProperty.ForeignKeyTableName = dic[item.Name];
}
columns.Add(entityProperty);
//hascolumntype
if (sqlDataType == "decimal" ||
sqlDataType == "numeric" ||
sqlDataType == "datetime2" ||
sqlDataType == "datetimeoffset" ||
sqlDataType == "time")
{
hasColumnTypes.Add(new HasColumnType()
{
PropertyName = item.Name,
TypeName = sqlDataType,
NumericPrecision = item.DataType.NumericPrecision,
NumericScale = item.DataType.NumericScale
});
}
//requiredmaxlength
var requiredMaxLength = new RequiredMaxLength()
{
PropertyName = item.Name, MaxLength = -1
};
if (item.Nullable == false && dotnetType == "string")
{
requiredMaxLength.NeedIsRequired = true;
}
if (dotnetType == "string" || dotnetType == "byte[]")
{
requiredMaxLength.MaxLength = item.DataType.MaximumLength;
}
if (requiredMaxLength.NeedIsRequired == true || requiredMaxLength.MaxLength > 0)
{
requiredMaxLengths.Add(requiredMaxLength);
}
//defaultvalue
if (item.DefaultConstraint != null)
{
defaultValues.Add(new DefaultValue()
{
PropertyName = item.Name,
Value = item.DefaultConstraint.Text
});
}
}
var t = new DbTable()
{
TableName = table.Name,
Columns = new ObservableCollection <DbTableColumn>(columns),
ForeignKeys = new ObservableCollection <ForeignKey>(foreignKeys),
Indexes = new ObservableCollection <Index>(indexes),
RequiredMaxLengths = new ObservableCollection <RequiredMaxLength>(requiredMaxLengths),
DefaultValues = new ObservableCollection <DefaultValue>(defaultValues),
HasColumnTypes = new ObservableCollection <HasColumnType>(hasColumnTypes)
};
if (table.Name.StartsWith("Smt"))
{
t.IsSelected = false;
}
else
{
t.IsSelected = true;
}
tables.Add(t);
}
foreach (var table in tables)
{
List <Reference> reference;
if (dicReferenceTable.TryGetValue(table.TableName, out reference) == true)
{
table.ReferencesToThisTable = new ObservableCollection <Reference>(reference);
}
else
{
table.ReferencesToThisTable = new ObservableCollection <Reference>();
}
}
CalculateReferenceLevel(tables);
return(tables);
}
/// <summary>
/// Generates Data Access, Mapping and Transfer Code from the Database
/// </summary>
/// <param name="FolderPath"></param>
/// <param name="ServerName"></param>
/// <param name="DatabaseName"></param>
public static void GenerateFromDatabase(GenerationInfo GenInfo)
{
DirectoryInfo d = new DirectoryInfo(GenInfo.FolderPath);
Microsoft.SqlServer.Management.Common.ServerConnection svrCon = new Microsoft.SqlServer.Management.Common.ServerConnection(GenInfo.ServerName);
Server svr = new Server(svrCon);
String DbScriptsPath = GenInfo.FolderPath + @"\Procedures";
foreach (Microsoft.SqlServer.Management.Smo.Database datab in svr.Databases)
{
if (datab.IsAccessible)
{
if (!(DatabasesToIgnore().Contains(datab.Name)))
{
if (datab.Name.ToLower() == GenInfo.DataBase.ToLower())
{
Console.Write("Generating Code for Database: " + datab.Name + "\n");
String RepositoryPath = GenInfo.FolderPath + @"\" + datab.Name; // The Current Repository
String DataLayerNamespace = String.Format("{0}.{1}.{2}", GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name);
#region [ Clean / Create Directory ]
if (Directory.Exists(RepositoryPath))
{
// Directory Exists clean it out
DirectoryInfo dRepository = new DirectoryInfo(RepositoryPath);
foreach (FileInfo f in dRepository.GetFiles("*.*", SearchOption.AllDirectories))
{
f.Delete();
}
}
else
{
Directory.CreateDirectory(RepositoryPath);
}
#endregion
#region [ Tables ]
String RepositoryPathProcedures = RepositoryPath + @"\Procedures";
if (Directory.Exists(RepositoryPathProcedures))
{
Directory.Delete(RepositoryPathProcedures);
}
Directory.CreateDirectory(RepositoryPathProcedures);
Int32 GenerationStep = 0;
DALGenerator dg = new DALGenerator();
POCOGenerator pg = new POCOGenerator();
foreach (Table t in datab.Tables)
{
GenerationStep = 0;
try
{
if (t.Name == "Loan")
{
"$".IsNormalized();
}
if (!(t.IsSystemObject))
{
// Check if the table has a primary key, if not then dont generate anything. Table muist be normalized
if (ValidateSqlTable(t))
{
#region [ Generation Root ]
String cpFilename = RepositoryPathProcedures + @"\\" + "cp_" + t.Name + ".sql";
String sspFileName = RepositoryPathProcedures + @"\\" + "ssp_" + t.Name + ".sql";
String EntityFileName = RepositoryPath + @"\\" + t.Name + ".cs";
String DALFileName = RepositoryPath + @"\\" + t.Name + "DAL.cs";
String DOMFileName = RepositoryPath + @"\\" + t.Name + "DomainObject.cs";
GenerationStep = 1;
// [0] Stored Procedures
MGenerator.Tools.SqlGeneration.CrudProcedureGenerator gen = new SqlGeneration.CrudProcedureGenerator(datab, t);
gen.Generate(RepositoryPathProcedures, MGenerator.Tools.SqlGeneration.ProcedureGenerationType.Alter);
GenerationStep = 2;
ObjectiveSearchProcedureGenerator ospgen = new ObjectiveSearchProcedureGenerator(datab, t);
ospgen.Generate(RepositoryPathProcedures);
GenerationStep = 3;
// [1] Data Access Classes
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, dg.BuildDalClass(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, t));
GenerationStep = 4;
// [2] Entity / Structure Class
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, pg.BuildPoc(t));
GenerationStep = 5;
// [3] Service Layer
DomainServiceGraph svcgen = new DomainServiceGraph();
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, svcgen.BuildDomainObject(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, t));
GenerationStep = 6;
// [4] API
ApiGraph api = new ApiGraph();
string api_name =
api.WriteApiStation(GenInfo, datab, t, RepositoryPath);
#endregion
Console.Write(t.Name + "....COMPLETE" + "\n");
}
}
}
catch (Exception x)
{
Console.Write(t.Name + "....ERROR" + "\n");
// File.WriteAllText(RepositoryPath + t.Name + ".txt", x.Message + "\n Generation Step:" + GenerationStep.ToString() );
continue;
}
}
#endregion
#region [ Views ]
foreach (View view in datab.Views)
{
try
{
if (view.Name == "vwLOLicense")
{
String fd = "";
}
if (!(view.IsSystemObject))
{
if (ValidateSqlView(view))
{
ObjectiveSearchProcedureGenerator ospgen = new ObjectiveSearchProcedureGenerator(datab, view);
DomainServiceGraph svcgen = new DomainServiceGraph();
ospgen.Generate(RepositoryPathProcedures);
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, dg.BuildDalClass(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, view));
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, pg.BuildPoco(view));
WriteCsharpFile(GenInfo.CompanyName, GenInfo.DataNameSpace, datab.Name, RepositoryPath, svcgen.BuildDomainObject(String.Format("{0}.{1}", GenInfo.CompanyName, GenInfo.DataNameSpace), datab.Name, view));
Console.Write(view.Name + "....COMPLETE" + "\n");
}
}
}
catch (Exception x)
{
Console.Write(view.Name + "....ERROR" + "\n");
continue;
}
}
#endregion
}
}
}
}
}
