private void TgGenerateETL() { StoredProcedure sp; TgStoredProcedureETL = string.Format("ETL_{0}_{1}", DsDbServerName, DsDbName); TgExecStoredProcedureETL = string.Format("[{0}].[{1}]", DwDb.DefaultSchema, TgStoredProcedureETL); TgJobNameExecStoredProcedureETL = string.Format("Execute {0}", TgStoredProcedureETL); sp = new StoredProcedure(DwDb, TgStoredProcedureETL); //Set the TextMode property to false and then set the other object properties. sp.TextMode = false; sp.AnsiNullsStatus = true; sp.QuotedIdentifierStatus = true; //Set the TextBody property to define the stored procedure. string stmt = Environment.NewLine + "DECLARE @NumRowsChanged int;" + Environment.NewLine + "Set @NumRowsChanged = 0;"; if (DsDwMap.DwTableMapTopologicalSortList != null) { foreach (ICollection <DsDwTableMap_M> dsDwTableMapLevelList in DsDwMap.DwTableMapTopologicalSortList) { foreach (DsDwTableMap_M dsDwTableMap in dsDwTableMapLevelList) { stmt = stmt + GenerateScd1(dsDwTableMap) + Environment.NewLine + "Set @NumRowsChanged = @NumRowsChanged + @@ROWCOUNT" + Environment.NewLine; } } } sp.TextBody = stmt + "Select @NumRowsChanged as NumRowsChanged" + Environment.NewLine; //Create the stored procedure on the instance of SQL Server. sp.Create(); }
public void AsHierarchical_SetsNameAndSchema() { var toTest = StoredProcedure.Create("foo", "bar") .WithResults <string, int>() .AsHierarchical <string>(); AssertProcValues(toTest, typeof(HierarchicalStoredProcedure <string>), "foo", "bar"); }
public void AsHierarchical_ThrowsWhenTypeNotSpecifiedByWithResults() { var toTest = StoredProcedure.Create("foo") .WithResults <string, int>(); toTest.Invoking(s => s.AsHierarchical <byte>()) .ShouldThrow <ArgumentException>("because the type specified was not passed in WithResults") .And.Message.Should().Be("The type of TFactory must be one of the types the Stored Procedure has already been declared to return."); }
public void TestCreateSetsNameAndDefaultSchema() { var sp = StoredProcedure.Create("procMe"); sp.Name.Should().Be("procMe", "because it was passed to Create"); sp.Schema.Should().Be("dbo", "because it is the default schema"); sp.FullName.Should().Be("[dbo].[procMe]", "because that is the full name of the stored proc"); sp.Parameters.Should().BeEmpty("because none should be set after creation"); sp.DataTransformers.Should().BeEmpty("because none should be set after creation"); }
public void TestCreateSetsNameAndSchema() { var sp = StoredProcedure.Create("dummy", "usp_test"); sp.Name.Should().Be("usp_test", "because it was passed to Create"); sp.Schema.Should().Be("dummy", "because it was passed to create"); sp.FullName.Should().Be("[dummy].[usp_test]", "because that is the full name of the stored proc"); sp.Parameters.Should().BeEmpty("because none should be set after creation"); sp.DataTransformers.Should().BeEmpty("because none should be set after creation"); }
public void AsHierarchical_SetsDataTransformers() { var xForm = Mock.Of <IDataTransformer>(); var toTest = StoredProcedure.Create("foo") .WithDataTransformer(xForm) .WithResults <string, int>() .AsHierarchical <string>(); toTest.DataTransformers.Should().ContainSingle("because one DataTransformer was set") .Which.Should().Be(xForm, "because it was the one set"); }
//Sample only creates a procedure name with an empty procedure body. You can use TSQL to create the //procedure script. private void CreateProcedure(string procedureName) { if (!this.ServiceBroker.Parent.StoredProcedures.Contains(procedureName)) { StoredProcedure sproc = new StoredProcedure (this.ServiceBroker.Parent, procedureName); sproc.TextMode = false; sproc.TextBody = String.Empty; sproc.Create(); } }
public void AsHierarchical_SetsParameters() { var toTest = StoredProcedure.Create("foo") .WithParameter("name", "baz") .WithResults <string, int>() .AsHierarchical <string>(); var parm = toTest.Parameters.Should().ContainSingle("because one Parameter was set").Which; parm.ParameterName.Should().Be("name", "because that is the parameter that was set"); ((IInputStoredProcedureParameter)parm).Value.Should().Be("baz", "because that is the parameter that was set"); }
public void CreateStoredProcedure(string name, string body, params DbParameter[] parameters) { var sp = new StoredProcedure(SmoDatabase, name); sp.TextMode = false; foreach (var parameter in parameters) { var spParameter = new StoredProcedureParameter(sp, parameter.Name, parameter.DataType); sp.Parameters.Add(spParameter); } sp.TextMode = false; sp.TextBody = body; sp.Create(); }
public void AddDependecyStoredProcedures() { Database db = _smoObjectsAndSettings.Database_Property; StoredProcedure sp = GetDependencyStoredProcedure(Sproc.Mapping.GetDependency.GET_DEPENDENCY, db, MetaSprocDependencyConstants.GET_DEPENDENCY); if (db.StoredProcedures.Contains(Sproc.Mapping.GetDependency.GET_DEPENDENCY)) { db.StoredProcedures[Sproc.Mapping.GetDependency.GET_DEPENDENCY].Drop(); } sp.Create(); sp = GetDependencyStoredProcedure(Sproc.Mapping.AllDependencies.GET_DEPENDENCIES, db, MetaSprocDependencyConstants.GET_DEPENDENCIES); if (db.StoredProcedures.Contains(Sproc.Mapping.AllDependencies.GET_DEPENDENCIES)) { db.StoredProcedures[Sproc.Mapping.AllDependencies.GET_DEPENDENCIES].Drop(); } sp.Create(); }
public static void Go() { var server = new Server("."); Database database; if (server.Databases.Contains("Test_Randal_Sql") == false) { database = new Database(server, "Test_Randal_Sql"); database.Create(); } else { database = server.Databases["Test_Randal_Sql"]; } if (database.StoredProcedures.Contains("mySp") == false) { var sp = new StoredProcedure(database, "mySp"); sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; sp.TextBody = "return -1"; sp.Create(); } if (database.UserDefinedFunctions.Contains("myFunc") == false) { var func = new UserDefinedFunction(database, "myFunc"); func.TextMode = false; func.ExecutionContext = ExecutionContext.Caller; func.FunctionType = UserDefinedFunctionType.Scalar; func.ImplementationType = ImplementationType.TransactSql; func.DataType = DataType.Int; func.TextBody = "begin return(-1); end"; func.Create(); } if (database.Views.Contains("myView") == false) { var view = new View(database, "myView"); view.TextMode = false; view.AnsiNullsStatus = false; view.TextBody = "select 42 [AnswerToEverything]"; view.Create(); } }
public static void Go() { var server = new Server("."); Database database; if (server.Databases.Contains("Test_Randal_Sql") == false) { database = new Database(server, "Test_Randal_Sql"); database.Create(); } else database = server.Databases["Test_Randal_Sql"]; if(database.StoredProcedures.Contains("mySp") == false) { var sp = new StoredProcedure(database, "mySp"); sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; sp.TextBody = "return -1"; sp.Create(); } if (database.UserDefinedFunctions.Contains("myFunc") == false) { var func = new UserDefinedFunction(database, "myFunc"); func.TextMode = false; func.ExecutionContext = ExecutionContext.Caller; func.FunctionType = UserDefinedFunctionType.Scalar; func.ImplementationType = ImplementationType.TransactSql; func.DataType = DataType.Int; func.TextBody = "begin return(-1); end"; func.Create(); } if(database.Views.Contains("myView") == false) { var view = new View(database, "myView"); view.TextMode = false; view.AnsiNullsStatus = false; view.TextBody = "select 42 [AnswerToEverything]"; view.Create(); } }
public static bool SwitchToMo( this StoredProcedure self, Database inMemDatabase, ref string error, ILog logger ) { var retValue = false; if (self.IsSystemObject) { return(true); } if (inMemDatabase.StoredProcedures.Contains(self.Name, self.Schema)) { logger.Log("\t" + "Already exists", self.FName()); return(true); } logger.Log("SP", self.FName()); var newsp = new StoredProcedure(inMemDatabase, self.Name, self.Schema); newsp.CopyPropertiesFrom(self); try { newsp.Create(); logger.Log("OK", self.FName()); retValue = true; } catch (Exception ex) { logger.Log("Error", self.FName()); error = string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException) .Select(ex1 => ex1.Message)); } newsp = null; return(retValue); }
private void CreateSelectProcedure(Schema spSchema, Table tbl) { String procName; StringBuilder sbSQL = new StringBuilder(); StringBuilder sbSelect = new StringBuilder(); StringBuilder sbWhere = new StringBuilder(); StoredProcedure sp; StoredProcedureParameter parm; try { // Create stored procedure name from user entry and table name procName = PrefixTextBox.Text + tbl.Name + @"Select"; if (DropOnlyCheckBox.CheckState == CheckState.Checked) { DropStoredProcedure(procName, spSchema); } else { DropStoredProcedure(procName, spSchema); ScriptTextBox.AppendText(string.Format( System.Globalization.CultureInfo.InvariantCulture, Properties.Resources.CreatingStoredProcedure, spSchema.ToString(), BracketObjectName(procName)) + Environment.NewLine); ScrollToBottom(); // Create the new stored procedure object sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name); sp.TextMode = false; foreach (Column col in tbl.Columns) { // Select columns if (sbSelect.Length > 0) { sbSelect.Append(", " + Environment.NewLine); } // Note: this does not fix object names with embedded brackets sbSelect.Append("\t["); sbSelect.Append(col.Name); sbSelect.Append(@"]"); // Create parameters and where clause from indexed fields if (col.InPrimaryKey == true) { // Parameter columns parm = new StoredProcedureParameter(sp, "@" + col.Name); parm.DataType = col.DataType; parm.DataType.MaximumLength = col.DataType.MaximumLength; sp.Parameters.Add(parm); // Where columns if (sbWhere.Length > 0) { sbWhere.Append(" " + Environment.NewLine + "\tAND "); } // Note: this does not fix object names with embedded brackets sbWhere.Append(@"["); sbWhere.Append(col.Name); sbWhere.Append(@"] = @"); sbWhere.Append(col.Name); } } // Put where clause into string if (sbWhere.Length > 0) { sbWhere.Insert(0, @"WHERE "); } sbrStatus.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture, Properties.Resources.Creating, procName); sbSQL.Append("SELECT "); sbSQL.Append(sbSelect); sbSQL.Append(" " + Environment.NewLine + "FROM "); sbSQL.Append(tbl.ToString()); sbSQL.Append(" " + Environment.NewLine); sbSQL.Append(sbWhere); sp.TextBody = sbSQL.ToString(); sp.Create(); } } catch (SmoException ex) { ExceptionMessageBox emb = new ExceptionMessageBox(ex); emb.Show(this); } finally { // Clean up. sbSQL = null; sbSelect = null; sbWhere = null; sp = null; parm = null; } }
private void AddInformationSchemaProcedures(Database db) { StoredProcedure sp = GetInformationSchemaStoredProcedure(Sproc.Mapping.InformationSchemaTable.INFORMATION_SCHEMA_TABLE, db, InformationSchemaSprocConstants.GET_INFORMATION_SCHEMA_TABLES); if (db.StoredProcedures.Contains(Sproc.Mapping.InformationSchemaTable.INFORMATION_SCHEMA_TABLE)) { db.StoredProcedures[Sproc.Mapping.InformationSchemaTable.INFORMATION_SCHEMA_TABLE].Drop(); } sp.Create(); sp = GetInformationSchemaStoredProcedure(Sproc.Mapping.InformationSchemaTableConstraint.INFORMATION_SCHEMA_TABLE_CONSTRAINTS, db, InformationSchemaSprocConstants.GET_INFORMATION_SCHEMA_TABLE_CONSTRAINTS); if (db.StoredProcedures.Contains(Sproc.Mapping.InformationSchemaTableConstraint.INFORMATION_SCHEMA_TABLE_CONSTRAINTS)) { db.StoredProcedures[Sproc.Mapping.InformationSchemaTableConstraint.INFORMATION_SCHEMA_TABLE_CONSTRAINTS].Drop(); } sp.Create(); sp = GetInformationSchemaStoredProcedure(Sproc.Mapping.InformationSchemaColumn.INFORMATION_SCHEMA_COLUMN, db, InformationSchemaSprocConstants.GET_INFORMATION_SCHEMA_COLUMNS); if (db.StoredProcedures.Contains(Sproc.Mapping.InformationSchemaColumn.INFORMATION_SCHEMA_COLUMN)) { db.StoredProcedures[Sproc.Mapping.InformationSchemaColumn.INFORMATION_SCHEMA_COLUMN].Drop(); } sp.Create(); sp = GetInformationSchemaStoredProcedure(Sproc.Mapping.InformationSchemaConstraintColumnUsage.INFORMATION_SCHEMA_CONSTRAINT_COLUMN_USAGE, db, InformationSchemaSprocConstants.GET_INFORMATION_SCHEMA_CONSTRAINT_COLUMN_USAGE); if (db.StoredProcedures.Contains(Sproc.Mapping.InformationSchemaConstraintColumnUsage.INFORMATION_SCHEMA_CONSTRAINT_COLUMN_USAGE)) { db.StoredProcedures[Sproc.Mapping.InformationSchemaConstraintColumnUsage.INFORMATION_SCHEMA_CONSTRAINT_COLUMN_USAGE].Drop(); } sp.Create(); sp = GetInformationSchemaStoredProcedure(Sproc.Mapping.All.INFORMATION_SCHEMA, db, InformationSchemaSprocConstants.GET_INFORMATION_SCHEMA); if (db.StoredProcedures.Contains(Sproc.Mapping.All.INFORMATION_SCHEMA)) { db.StoredProcedures[Sproc.Mapping.All.INFORMATION_SCHEMA].Drop(); } sp.Create(); sp = GetInformationSchemaIsIdentityStoredProcedure(Sproc.Mapping.InformationSchemaHelper.IS_IDENTITY, db, InformationSchemaSprocConstants.IS_IDENTITY_COLUMN); if (db.StoredProcedures.Contains(Sproc.Mapping.InformationSchemaHelper.IS_IDENTITY)) { db.StoredProcedures[Sproc.Mapping.InformationSchemaHelper.IS_IDENTITY].Drop(); } sp.Create(); }
public void GrantAccessToSchemaObjects() { var helper = new TestHelper(); try { var schema = helper.GetSchema(); //schema.Owner = helper.GetUser().Name; //schema.Alter(); var table = new Table(helper.GetDatabase(), "Table1", schema.Name); table.Columns.Add(new Column(table, "Col1", DataType.Int)); table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax)); table.Create(); helper.AddCleanup(table); var view = new View(helper.GetDatabase(), "View1", schema.Name) { TextMode = false, TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name) }; //view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name); view.Create(); helper.AddCleanup(view); var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name) { TextMode = false, DataType = DataType.DateTime, ExecutionContext = ExecutionContext.Caller, FunctionType = UserDefinedFunctionType.Scalar, ImplementationType = ImplementationType.TransactSql, TextBody = "BEGIN RETURN GETDATE() END" }; scalarTsqlFn.Create(); helper.AddCleanup(scalarTsqlFn); var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name) { TextMode = false, ExecutionContext = ExecutionContext.Caller, FunctionType = UserDefinedFunctionType.Inline, ImplementationType = ImplementationType.TransactSql, TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name) }; inlineTsqlFn.Create(); helper.AddCleanup(inlineTsqlFn); // TODO: Create table valued function // TODO: Create Clr scalar func // TODO: Create Clr inline func (Exists?) // TODO: Create Clr table valued func // TODO: Create Clr Aggregate var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name) { TextMode = false, AnsiNullsStatus = false, QuotedIdentifierStatus = false, TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name) }; proc.Create(); helper.AddCleanup(proc); // TODO: Create Clr Sproc // TODO: Create Constraint // TODO: Create Queue // TODO: Create Statistic // TODO: Create Synonym var user = helper.GetUser(); var permissable = new IObjectPermission[] { table, view, scalarTsqlFn, inlineTsqlFn, proc, }; permissable.Do(tg => tg.GrantAll(user.Name)); permissable.Do(tg => tg.DenyAll(user.Name)); permissable.Do(tg => tg.RevokeAll(user.Name)); // change all owners table.Owner = user.Name; table.Alter(); view.Owner = user.Name; view.Alter(); scalarTsqlFn.Owner = user.Name; scalarTsqlFn.Alter(); inlineTsqlFn.Owner = user.Name; inlineTsqlFn.Alter(); proc.Owner = user.Name; proc.Alter(); } finally { helper.Cleanup(); } }
internal void generate(IntegratedView employeesView) { connectionString = "Data Source=localhost\\SQLEXPRESS;" + "Initial Catalog=szpifDatabase;" + "Integrated Security=SSPI;"; string procName = "get" + employeesView.ViewName; // Create an instance of the server connection = new SqlConnection(connectionString); Server server = new Server(new ServerConnection(connection)); SqlCommand scommand = new SqlCommand(); scommand.CommandText = "IF OBJECT_ID('" + procName + "') IS NOT NULL " + "DROP PROCEDURE " + procName; scommand.Connection = connection; connection.Open(); scommand.ExecuteNonQuery(); connection.Close(); // I want to add the stored procedure to the "MyDatabase" Database Database db = server.Databases["SzpifDatabase"]; // Create a Stored Procedure StoredProcedure getView = //new StoredProcedure(); new StoredProcedure(db, "get" + employeesView.ViewName); getView.TextMode = false; getView.AnsiNullsStatus = false; getView.QuotedIdentifierStatus = false; // GetClubByID requires the ID of the Club as an Input Parameter //StoredProcedureParameter idParam = // new StoredProcedureParameter(getView, "@ID", DataType.Int); //getView.Parameters.Add(idParam); // The SQL Text string command = "SELECT DISTINCT "; string columns = ""; for (int i = 0; i < employeesView.Columns.Count; ++i ) { columns += employeesView.Columns[i].getFullName(); if(i+1 < employeesView.Columns.Count)columns +=", "; } command += columns; command += " FROM "; command += " "+employeesView.TableNames[0]+" " +employeesView.TableNames[0]+"1"+ " "; string joins = ""; string mainName = employeesView.TableNames[0]; foreach (ViewJoin vj in employeesView.Joins) { joins += vj.getText(); } command += joins; getView.TextBody = command; getView.Create(); // teraz tworzę procedurę do update'owania //CREATE PROCEDURE updateEmployeeViewForAdministration // @Id int, // @Login nvarchar(40), // @Name nvarchar(40), // @EMail nvarchar(40) //AS // update Employees set Login = @Login where Id = @Id // update Credentials set Name = @Name, EMail = @EMail where Id = // (select CredentialsId from Employees where Id = @Id) //GO }
public static void Main() { var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; ServerConnection cnn; string databaseName; using (var sqlConnection = new SqlConnection(connectionString)) { cnn = new ServerConnection(sqlConnection); // Read the database name from app.config databaseName = sqlConnection.Database; } cnn.Connect(); Console.WriteLine("Connected"); var server = new Server(cnn); Console.WriteLine("Create the server object"); var database = server.Databases[databaseName]; if (database.StoredProcedures.Contains(StoredProcedureName, SchemaName)) { // Removes the stored procedure from the instance of SQL Server. // To drop a stored procedure, users must have CONTROL permission on the stored procedure or be a member of the db_owner fixed database role. database.StoredProcedures[StoredProcedureName, SchemaName].Drop(); } // To create a stored procedure, users must have CREATE PROCEDURE permission on the parent database // or be a member of the db_owner fixed database role. var newsp = new StoredProcedure(database, StoredProcedureName, SchemaName) { // TextMode the text header is not editable TextMode = false, // TextBody sets the Transact-SQL string that defines the stored procedure TextBody = "SELECT * FROM sys.tables WHERE name = @param", // the stored procedure is encrypted IsEncrypted = true }; // Parameter var parm = new StoredProcedureParameter(newsp, "@param") { DataType = DataType.SysName }; newsp.Parameters.Add(parm); try { newsp.Create(); Console.WriteLine("Successfully created the stored procedure "); //Script the stored procedure var result = newsp.Script(); // Write scripting output to sql file File.WriteAllLines(CTmpStoredprocedureSql, result.Cast <string>()); // Start NOTEPAD and display T-SQL script Process.Start("notepad", CTmpStoredprocedureSql); } catch (Exception ex) { var error = string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException) .Select(ex1 => ex1.Message)); Console.WriteLine("An error occured while creating the stored procedure. The error message is : " + error); } Console.ReadLine(); }
internal void generate(IntegratedView employeesView) { connectionString = "Data Source=localhost\\SQLEXPRESS;" + "Initial Catalog=szpifDatabase;" + "Integrated Security=SSPI;"; string procName = "get" + employeesView.ViewName; // Create an instance of the server connection = new SqlConnection(connectionString); Server server = new Server(new ServerConnection(connection)); SqlCommand scommand = new SqlCommand(); scommand.CommandText = "IF OBJECT_ID('" + procName + "') IS NOT NULL " + "DROP PROCEDURE " + procName; scommand.Connection = connection; connection.Open(); scommand.ExecuteNonQuery(); connection.Close(); // I want to add the stored procedure to the "MyDatabase" Database Database db = server.Databases["SzpifDatabase"]; // Create a Stored Procedure StoredProcedure getView = //new StoredProcedure(); new StoredProcedure(db, "get" + employeesView.ViewName); getView.TextMode = false; getView.AnsiNullsStatus = false; getView.QuotedIdentifierStatus = false; // GetClubByID requires the ID of the Club as an Input Parameter //StoredProcedureParameter idParam = // new StoredProcedureParameter(getView, "@ID", DataType.Int); //getView.Parameters.Add(idParam); // The SQL Text string command = "SELECT DISTINCT "; string columns = ""; for (int i = 0; i < employeesView.Columns.Count; ++i) { columns += employeesView.Columns[i].getFullName(); if (i + 1 < employeesView.Columns.Count) { columns += ", "; } } command += columns; command += " FROM "; command += " " + employeesView.TableNames[0] + " " + employeesView.TableNames[0] + "1" + " "; string joins = ""; string mainName = employeesView.TableNames[0]; foreach (ViewJoin vj in employeesView.Joins) { joins += vj.getText(); } command += joins; getView.TextBody = command; getView.Create(); // teraz tworzę procedurę do update'owania //CREATE PROCEDURE updateEmployeeViewForAdministration // @Id int, // @Login nvarchar(40), // @Name nvarchar(40), // @EMail nvarchar(40) //AS // update Employees set Login = @Login where Id = @Id // update Credentials set Name = @Name, EMail = @EMail where Id = // (select CredentialsId from Employees where Id = @Id) //GO }