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 } } } } }