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();
}
}
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;
}
}
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();
}
//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();
}
}
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
}
