static void Main(string[] args)
{
DatabaseBuilder builder = new DatabaseBuilder();
SqlParameter p = new SqlParameter();
// p.
Column c = new Column();
// c.Properties.
// c.
// c.
ViewPermissions permissions = new ViewPermissions();
permissions.Add("select", "Owner");
permissions.Add("insert", "Owner");
permissions.Add("update", "Owner");
permissions.Add("delete", "Owner");
List <ViewColumn> columns = new List <ViewColumn>();
columns.Add(new ViewColumn("Employees", "Id", true));
columns.Add(new ViewColumn("Employees", "Login", false));
columns.Add(new ViewColumn("Credentials", "Name", false));
columns.Add(new ViewColumn("Credentials", "EMail", false));
columns.Add(new ComputedViewColumn(
new ViewColumn("Employees", "Id", false),
"dbo.aggregateRolesFunction", "Uprawnienia"));
List <string> tables = new List <string>();
tables.Add("Employees");
tables.Add("Credentials");
tables.Add("Roles");
List <ViewJoin> joins = new List <ViewJoin>();
joins.Add(new ViewJoin("Roles",
new ViewColumn("Roles", "EmployeeId", true),
new ViewColumn("Employees", "Id", true)));
joins.Add(new ViewJoin("Credentials",
new ViewColumn("Credentials", "Id", true),
new ViewColumn("Employees", "CredentialsId", true)));
IntegratedView employeesView = new IntegratedView(
"testView", columns, permissions, tables, joins);
builder.generate(employeesView);
Console.WriteLine("Press Enter to Continue");
Console.ReadKey();
}
static void Main(string[] args)
{
DatabaseBuilder builder = new DatabaseBuilder();
SqlParameter p = new SqlParameter();
// p.
Column c = new Column();
// c.Properties.
// c.
// c.
ViewPermissions permissions = new ViewPermissions();
permissions.Add("select", "Owner");
permissions.Add("insert", "Owner");
permissions.Add("update", "Owner");
permissions.Add("delete", "Owner");
List<ViewColumn> columns = new List<ViewColumn>();
columns.Add(new ViewColumn("Employees", "Id", true));
columns.Add(new ViewColumn("Employees", "Login", false));
columns.Add(new ViewColumn("Credentials", "Name", false));
columns.Add(new ViewColumn("Credentials", "EMail", false));
columns.Add(new ComputedViewColumn(
new ViewColumn("Employees", "Id",false),
"dbo.aggregateRolesFunction", "Uprawnienia"));
List<string> tables = new List<string>();
tables.Add("Employees");
tables.Add("Credentials");
tables.Add("Roles");
List<ViewJoin> joins = new List<ViewJoin>();
joins.Add(new ViewJoin("Roles",
new ViewColumn("Roles", "EmployeeId",true),
new ViewColumn("Employees", "Id",true)));
joins.Add(new ViewJoin("Credentials",
new ViewColumn("Credentials", "Id",true),
new ViewColumn("Employees", "CredentialsId",true)));
IntegratedView employeesView = new IntegratedView(
"testView", columns, permissions, tables, joins);
builder.generate(employeesView);
Console.WriteLine("Press Enter to Continue");
Console.ReadKey();
}
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
}
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
}
