public void ExportToTable(string tableName, List <Row> data) { if (OPT.GetBool("db.save.backup")) { scriptTable(tableName, true); } using (SqlCommand sqlCmd = new SqlCommand("", sqlCon)) { sqlCmd.Connection.Open(); if (OPT.GetBool("db.save.drop")) { scriptTable(tableName, false); sqlCmd.CommandText = string.Format("DROP TABLE {0}", tableName); sqlCmd.ExecuteNonQuery(); string script = new StreamReader(string.Format(@"{0}\{1}_{2}_so.sql", scriptDir, tableName, DateTime.Now.ToString("hhMMddyyy"))).ReadToEnd(); db.ExecuteNonQuery(script); } else { sqlCmd.CommandText = string.Format("TRUNCATE TABLE {0}", tableName); sqlCmd.ExecuteNonQuery(); } sqlCmd.Connection.Close(); } SqlCommand insertCmd = tManager.RDBCore.InsertStatement; insertCmd.Connection = sqlCon; insertCmd.CommandText = insertCmd.CommandText.Replace("<tableName>", tableName); int rows = data.Count; tManager.RDBTab.ProgressMax = rows; for (int rowIdx = 0; rowIdx < rows; rowIdx++) { Row row = data[rowIdx]; using (SqlCommand sqlCmd = insertCmd) { foreach (SqlParameter sqlParam in sqlCmd.Parameters) { sqlParam.Value = row[sqlParam.ParameterName]; } sqlCmd.Connection.Open(); sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } if (((rowIdx * 100) / rows) != ((rowIdx - 1) * 100 / rows)) { tManager.RDBTab.ProgressVal = rowIdx; } } tManager.RDBTab.ProgressVal = 0; tManager.RDBTab.ProgressMax = 100; }
public void CreateDatabaseFromScriptFile( string dbName, string scriptFilePath, StringCollection assembliesToDeploy) { if (!File.Exists(scriptFilePath)) { throw new UpdateDatabaseException( string.Format( "Cannot create database, database script file {0} does not exist.", scriptFilePath)); } DropDatabase(dbName); using (var cn = new SqlConnection(mConnectionString)) { cn.Open(); var sc = new ServerConnection(cn); var srv = new Server(sc); var db = new Microsoft.SqlServer.Management.Smo.Database(srv, dbName); // // Set trustworthy options, if we have assemblies // db.DatabaseOptions.Trustworthy = true; db.Create(); foreach (string assemblyPath in assembliesToDeploy) { var assembly = new SqlAssembly(db, Path.GetFileNameWithoutExtension(assemblyPath)) { AssemblySecurityLevel = AssemblySecurityLevel.Unrestricted }; assembly.Create(Environment.ExpandEnvironmentVariables(assemblyPath)); } using (var sr = new StreamReader(scriptFilePath)) { var createDatabaseScript = sr.ReadToEnd(); db.ExecuteNonQuery(createDatabaseScript); } } }
public void ExecuteBatch( string batchText) { using (var cn = new SqlConnection(mConnectionString)) { cn.Open(); var sc = new ServerConnection(cn); var srv = new Server(sc); var db = new Microsoft.SqlServer.Management.Smo.Database( srv, this.mDatabaseName); db.ExecuteNonQuery(batchText); } }
public static void ExportToTable(List <Row> data) { string tableName = string.Empty; using (InputGUI input = new InputGUI("Please enter the table name", StructureManager.TableName(GUI.Instance.RDBControls.StructureListValue))) { if (input.ShowDialog() != DialogResult.OK) { return; } if (string.IsNullOrEmpty(input.Value)) { return; } tableName = input.Value; } try { if (OPT.GetBool("db.save.backup")) { scriptTable(tableName, true); } using (SqlCommand sqlCmd = new SqlCommand("", sqlCon)) { sqlCmd.Connection.Open(); if (OPT.GetBool("db.save.drop")) { scriptTable(tableName, false); sqlCmd.CommandText = string.Format("DROP TABLE {0}", tableName); sqlCmd.ExecuteNonQuery(); string script = new StreamReader(string.Format(@"{0}\{1}_{2}_so.sql", scriptDir, tableName, DateTime.Now.ToString("hhMMddyyy"))).ReadToEnd(); db.ExecuteNonQuery(script); } else { sqlCmd.CommandText = string.Format("TRUNCATE TABLE {0}", tableName); sqlCmd.ExecuteNonQuery(); } sqlCmd.Connection.Close(); } SqlCommand insertCmd = GUI.Instance.rCore.InsertStatement; insertCmd.Connection = sqlCon; insertCmd.CommandText = insertCmd.CommandText.Replace("<tableName>", tableName); int rows = data.Count; GUI.Instance.UpdateProgressMaximum(rows); for (int rowIdx = 0; rowIdx < rows; rowIdx++) { Row row = data[rowIdx]; using (SqlCommand sqlCmd = insertCmd) { foreach (SqlParameter sqlParam in sqlCmd.Parameters) { sqlParam.Value = row[sqlParam.ParameterName]; } sqlCmd.Connection.Open(); sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } if (((rowIdx * 100) / rows) != ((rowIdx - 1) * 100 / rows)) { GUI.Instance.UpdateProgressValue(rowIdx); } } } catch (Exception ex) { MessageBox.Show(string.Format("SQL Error:\n\n{0}", ex.Message), "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { GUI.Instance.UpdateProgressValue(0); GUI.Instance.UpdateProgressMaximum(100); } }