public override void ReaderExecuting(
DbCommand command, DbCommandInterceptionContext <DbDataReader> interceptionContext)
{
int i = 0;
int recordsAffected = 0;
statements = GetBatchStatements(command.CommandText);
if (statements != null)
{
foreach (String statement in statements)
{
if (++i >= statements.Count) // break if last
{ // probably a SELECT command
command.CommandText = statement
.Replace("__ingres_@@ROWCOUNT",
recordsAffected.ToString());;
break;
}
using (IngresCommand command2 = (command as IngresCommand).Clone())
{ // probably an INSERT command
command2.CommandText = statement;
// INSERT/UPDATE/DELETE using the command.Parameters
recordsAffected = command2.ExecuteNonQuery();
// clear parms on the SELECT since they were just consumed
command.Parameters.Clear();
}
} // end foreach (String statement in statements)
}
base.ReaderExecuting(command, interceptionContext); // SELECT
}
private void Delete_Click(object sender, EventArgs e)
{
IngresConnection con = new IngresConnection(Login.myConnectionString);
con.Open();
IngresCommand cmd = new IngresCommand();
cmd.Connection = con;
cmd.CommandText = "rol_delete";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new IngresParameter("t_id_rol", IngresType.Decimal));
cmd.Parameters["t_id_rol"].Value = int.Parse(this.metroTextBox1.Text);
IngresTransaction trans = con.BeginTransaction();
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
MetroMessageBox.Show(this, "Elemento eliminado correctamente.", "Nota", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
catch (IngresException c)
{
MetroMessageBox.Show(this, c.ErrorCode + c.Message, "Error");
}
con.Close();
this.read();
}
/*
** Name: CreateCommand
**
** History:
** 27-Aug-02 (thoda04)
** Created.
*/
/// <summary>
/// Creates an IngresCommand object associated with the IngresConnection.
/// </summary>
/// <returns>An IngresCommand object.</returns>
public new IngresCommand CreateCommand()
{
// Return a new instance of a command object.
IngresCommand cmd = new IngresCommand(
String.Empty, this, this.Transaction);
return cmd;
}
private void metroButton1_Click(object sender, EventArgs e)
{
IngresConnection con = new IngresConnection(Login.myConnectionString);
con.Open();
IngresCommand cmd = new IngresCommand();
cmd.Connection = con;
cmd.CommandText = "rol_create";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new IngresParameter("t_id_rol", IngresType.Decimal));
cmd.Parameters.Add(new IngresParameter("t_nombre", IngresType.VarChar));
cmd.Parameters["t_id_rol"].Value = 0;
cmd.Parameters["t_nombre"].Value = this.nombre.Text;
try
{
cmd.ExecuteNonQuery();
MetroMessageBox.Show(this, "Elemento creado correctamente.", "Nota", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
catch (IngresException c)
{
MetroMessageBox.Show(this, c.ErrorCode + c.Message, "Error");
}
con.Close();
this.read();
}
/// <summary>
/// Connect to two databases while automatically enlisting in the ambient Transaction,
/// and execute two non-query SQL commands under the TransactionScope.
/// Commit the Transaction by marking the TransactionScope complete,
/// if issueScopeComplete argument is set true for testing purposes.
/// </summary>
/// <param name="connString1">ConnectionString for database 1.</param>
/// <param name="connString2">ConnectionString for database 2.</param>
/// <param name="commandText1">Non-query CommandText to execute against database 1.</param>
/// <param name="commandText2">Non-query CommandText to execute against database 2.</param>
/// <param name="issueScopeComplete">If true, issue TransactionScope.Complete()</param>
static void ExecuteNonQueryWithinTransactionScope(
string connString1, string connString2,
string commandText1, string commandText2,
bool issueScopeComplete)
{
Console.WriteLine("\n\tUpdateUsingTransactionScope...\n");
using (TransactionScope scope = new TransactionScope())
{
using (IngresConnection conn1 = new IngresConnection(connString1))
{
using (IngresConnection conn2 = new IngresConnection(connString2))
{
Console.WriteLine("\tIngresConnection1.Open()...");
// Open the connection to the database and
// enlist in the ambient Transaction using MSDTC
conn1.Open();
Console.WriteLine("\tIngresConnection1.Open() complete\n");
Console.WriteLine("\tIngresConnection2.Open()...");
// Open the connection to the database and
// enlist in the ambient Transaction using MSDTC
conn2.Open();
Console.WriteLine("\tIngresConnection2.Open() complete\n");
try
{
IngresCommand cmd1 = conn1.CreateCommand();
cmd1.CommandText = commandText1;
cmd1.ExecuteNonQuery();
IngresCommand cmd2 = conn2.CreateCommand();
cmd2.CommandText = commandText2;
cmd2.ExecuteNonQuery();
// mark the Transaction complete
// mark the Transaction complete
if (issueScopeComplete) // test debug flag for testing scope.Complete
{
Console.WriteLine("\tTransactionScope completing...");
scope.Complete();
Console.WriteLine("\tTransactionScope complete\n");
}
// note: TransactionScope will not be committed until
// TransactionScope Dispose() is called.
}
catch (Exception ex)
{
string s = ex.ToString();
Console.WriteLine("\n\tApplication throws Exception!! " + s + "\n");
throw;
}
} // end using (IngresConnection2) // closes and disposes conn2
} // end using (IngresConnection1) // closes and disposes conn1
Console.WriteLine("\tTransactionScope.Dispose()...");
} // end using (TransactionScope) // calls System.Transactions.Dispose() --> System.Transactions.CommittableTransaction.Commit
Console.WriteLine("\tTransactionScope.Dispose() complete\n");
} // end ExecuteNonQueryWithinTransactionScope
/// <summary>
/// Takes, as input, a user name and returns the names of the roles to which the user
/// belongs. If the user is not assigned to any roles, <c>GetRolesForUser</c> returns an empty
/// string array (a string array with no elements). If the user name does not exist,
/// <c>GetRolesForUser</c> throws a <c>ProviderException</c>.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetRolesForUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username we want to get the roles for.</param>
/// <returns>Any array of all of the roles for the given user.</returns>
internal string[] GetRolesForUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username");
// Validate that the user exists and throw exception if he doesn't
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserNotFound));
}
// Build up the required SQL
string sql = @"
SELECT
aspnet_Roles.RoleName
FROM
aspnet_Roles,
aspnet_UsersInRoles
WHERE
aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
AND aspnet_Roles.ApplicationId = ?
AND aspnet_UsersInRoles.UserId = ?
ORDER BY
aspnet_Roles.RoleName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("UserId", DbType.String).Value = this.GetUserIdByName(username);
// Instantiate a new reader and read in the query
IngresDataReader reader = cmd.ExecuteReader();
// Get the roles out of the reader
List <string> roleNamesList = new List <string>();
while (reader.Read())
{
roleNamesList.Add(DBUtil.ColValAsString(reader, "RoleName"));
}
reader.Close();
// Return an appropriate string array
if (roleNamesList.Count > 0)
{
return(roleNamesList.ToArray());
}
// we had no roles and so just return an empty string array.
return(new string[0]);
}
/// <summary>
/// Gets the Id for the current application.
/// </summary>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
/// <returns>The Id for the current application.</returns>
private string GetApplicationId(IngresConnection conn, IngresTransaction tran)
{
string id = null;
string sql = @"
SELECT
ApplicationId
FROM
aspnet_Applications
WHERE LoweredApplicationName = ?
";
// Create the new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
conn.Open();
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
reader.Read();
// Retrieve the Id
id = DBUtil.ColValAsString(reader, "ApplicationId");
reader.Close();
}
else
{
// Close the reader.
reader.Close();
// We don't have an application so create one.
this.CreateApplication(this.config.ApplicationName, out id);
}
}
// Mark the application Id as current so that we don't have to fetch it from the database
// again unless it changes.
this.config.IsApplicationIdCurrent = true;
// Close the connection
conn.Close();
return(id);
}
} // end ExecuteNonQueryWithinExplicitTransactionScope
/// <summary>
/// Open a database and execute a non-query SQL string.
/// </summary>
/// <param name="connString"></param>
/// <param name="commandText"></param>
static void ExecuteNonQuery(
string connString, string commandText)
{
using (IngresConnection conn = new IngresConnection(connString))
{
conn.Open();
IngresCommand cmd = conn.CreateCommand();
cmd.CommandText = commandText;
cmd.ExecuteNonQuery();
} // end using (IngresConnection conn)
} // end ExecuteNonQuery
private void Update_Click(object sender, EventArgs e)
{
IngresConnection con = new IngresConnection(Login.myConnectionString);
con.Open();
IngresCommand cmd = new IngresCommand();
cmd.Connection = con;
cmd.CommandText = "empleado_update";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new IngresParameter("t_id_empleado", IngresType.Decimal));
cmd.Parameters.Add(new IngresParameter("t_primer_nombre", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_segundo_nombre", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_primer_apellido", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_segundo_apellido", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_calle", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_avenida", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_num_casa", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_ciudad", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_departamento", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_referencia", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_correo_primario", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_correo_secundario", IngresType.VarChar));
cmd.Parameters.Add(new IngresParameter("t_fecha_nacimiento", IngresType.Date));
cmd.Parameters.Add(new IngresParameter("t_fecha_inicio", IngresType.Date));
cmd.Parameters["t_id_empleado"].Value = int.Parse(this.id.Text);
cmd.Parameters["t_primer_nombre"].Value = this.prinom.Text;
cmd.Parameters["t_segundo_nombre"].Value = this.senom.Text;
cmd.Parameters["t_primer_apellido"].Value = this.priape.Text;
cmd.Parameters["t_segundo_apellido"].Value = this.seape.Text;
cmd.Parameters["t_calle"].Value = this.ca.Text;
cmd.Parameters["t_avenida"].Value = this.avenida.Text;
cmd.Parameters["t_num_casa"].Value = this.numcasa.Text;
cmd.Parameters["t_ciudad"].Value = this.ciudad.Text;
cmd.Parameters["t_departamento"].Value = this.departamento.Text;
cmd.Parameters["t_referencia"].Value = this.referencia.Text;
cmd.Parameters["t_correo_primario"].Value = this.pricorreo.Text;
cmd.Parameters["t_correo_secundario"].Value = this.secorreo.Text;
cmd.Parameters["t_fecha_nacimiento"].Value = this.metroDateTime1.Value.ToString("yyyy-MM-dd");
cmd.Parameters["t_fecha_inicio"].Value = this.metroDateTime2.Value.ToString("yyyy-MM-dd");
try
{
cmd.ExecuteNonQuery();
MetroMessageBox.Show(this, "Elemento actualizado correctamente.", "Nota", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
catch (IngresException c)
{
MetroMessageBox.Show(this, c.ErrorCode + c.Message, "Error");
}
con.Close();
this.read();
}
/// <summary>
/// Returns the names of all existing roles. If no roles exist, <c>GetAllRoles</c> returns an
/// empty string array (a string array with no elements).
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetAllRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <returns>A string array with all of the roles for the application.</returns>
internal string[] GetAllRoles()
{
// Ensure that we have an application Id
if (this.ApplicationId == null)
{
throw new Exception(Messages.ApplicationIdNotFound);
}
// build the SQL command
string sql = @"
SELECT
Rolename
FROM
aspnet_Roles
WHERE
ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the requires parameteters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Instantiate a reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get all roles from the reader
List <string> roleNamesList = new List <string>();
while (reader.Read())
{
roleNamesList.Add(DBUtil.ColValAsString(reader, "Rolename"));
}
reader.Close();
// If we have some role names then return them as a string array
if (roleNamesList.Count > 0)
{
return(roleNamesList.ToArray());
}
}
// Otherwise just return a new empty string array
return(new string[0]);
}
/// <summary>
/// Creates a role in the database.
/// </summary>
/// <param name="roleName">The rolename to create.</param>
/// <param name="roleid">The role id.</param>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
private void CreateRole(string roleName, out string roleid, IngresConnection conn, IngresTransaction tran)
{
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Ensure that the proposed roleName does not already exist
if (this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleAlreadyExists, roleName));
}
string sql = @"
INSERT INTO aspnet_Roles
(ApplicationId,
RoleId,
RoleName,
LoweredRoleName,
Description)
VALUES
(?,
?,
?,
?,
NULL)
";
// Create the command with the current connection and enrol in the transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Generate a new role Id - this will be the sent out
roleid = Guid.NewGuid().ToString().ToLower();
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
cmd.Parameters.Add("RoleName", DbType.String).Value = roleName;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Execute the query
int rows = cmd.ExecuteNonQuery();
// Validate that the query affected the correct numbber of rows.
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
private void read()
{
this.dt = new System.Data.DataTable();
IngresConnection con = new IngresConnection(Login.myConnectionString);
string query = "select * from read_rol";
IngresCommand comm = new IngresCommand(query, con);
con.Open();
IngresDataAdapter adapt = new IngresDataAdapter(comm);
adapt.Fill(this.dt);
con.Close();
this.metroGrid1.DataSource = this.dt;
adapt.Dispose();
}
/// <summary>
/// Helper method to create a user.
/// </summary>
/// <param name="username">The username that we wish to create.</param>
/// <returns>The Id for the newly created user.</returns>
private string CreateUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string id = Guid.NewGuid().ToString().ToLower();
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
UserId,
UserName,
LoweredUserName,
MobileAlias,
IsAnonymous,
LastActivityDate)
VALUES
(?,
?,
?,
?,
NULL,
'0',
?)
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("UserId", DbType.String).Value = id;
cmd.Parameters.Add("UserName", DbType.String).Value = username;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LastActivity", DbType.Date).Value = DateTime.Now;
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
// the user has been successfully created and so we return the id.
return(id);
}
/// <summary>
/// Helper method to determine whether a user exists for a given
/// </summary>
/// <param name="username">The username that we want to check the existence of.</param>
/// <returns>A boolean indicating whether the user exists.</returns>
private bool UserExists(string username)
{
// Validate the input
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
// assume that the user doesn't exist
bool exists = false;
// build up the parameterised SQL string
string sql = @"
SELECT
UserName
FROM
aspnet_Users
WHERE
LoweredUserName = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Instantiate a new reader and execute the query
IngresDataReader datareader = cmd.ExecuteReader();
// If we have returned anything then the user exists
if (datareader.HasRows)
{
exists = true;
}
// close the datareader and return
datareader.Close();
return(exists);
}
/// <summary>
/// Takes, as input, a role name and determines whether the role exists.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>RoleExists</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">role name to check the existence of</param>
/// <returns>Whether the given role exists.</returns>
internal bool RoleExists(string roleName)
{
// Validate the Parameter
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Assume that the role does not exist
bool exists = false;
string sql = @"
SELECT
RoleName
FROM
aspnet_Roles
WHERE
LoweredRoleName = ?
AND ApplicationId = ?
";
// Instantiate a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
// Execute the query.
IngresDataReader reader = cmd.ExecuteReader();
// Determine whether the role exists.
if (reader.HasRows)
{
exists = true;
}
reader.Close();
return(exists);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
data.Properties.TryGetValue("SQLStatement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);
var cols = new CustomListColumnCollection();
var con = GetConnection(data);
var command = new IngresCommand(SQLStatement, con);
var reader = command.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow sqlcol in schemaTable.Rows)
{
var columnName = (string)sqlcol["ColumnName"];
var dataType = (Type)sqlcol["DataType"];
var listColumnType = CustomListColumnTypes.String;
// We convert the types to one of the three Peakboard types (string, number or boolean)
if (dataType == typeof(string))
{
listColumnType = CustomListColumnTypes.String;
}
else if (dataType == typeof(bool))
{
listColumnType = CustomListColumnTypes.Boolean;
}
else
{
listColumnType = DataTypeHelper.IsNumericType(dataType) ? CustomListColumnTypes.Number : CustomListColumnTypes.String;
}
cols.Add(new CustomListColumn(columnName, listColumnType));
}
con.Close();
return(cols);
}
/// <summary>
/// Finds the Id for a given rolename.
/// </summary>
/// <param name="rolename">The name of the role to find the Id for.</param>
/// <returns>The Id for the given role.</returns>
private string GetRoleIdByName(string rolename)
{
string id = null;
string sql = @"
SELECT
RoleId
FROM
aspnet_Roles
WHERE
LoweredRoleName = ?
AND ApplicationId = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = rolename.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
IngresDataReader datareader = cmd.ExecuteReader();
if (datareader != null)
{
if (datareader.HasRows)
{
datareader.Read();
id = datareader.GetString(0);
}
datareader.Close();
}
return(id);
}
} // ApplyParameterInfo
/// <summary>
/// Populate the IngresCommand.Parameters collection with data type
/// metadata about the parameters of the specified database procedure.
/// </summary>
/// <param name="command"></param>
public static void DeriveParameters(IngresCommand command)
{
if (command == null)
{
throw new ArgumentNullException(
"DeriveParameters parameter 'command' must not be null.");
}
IngresConnection conn = command.Connection;
if (conn == null || conn.State != ConnectionState.Open)
{
throw new InvalidOperationException(
"The IngresCommand.Connection must be specified and open.");
}
if (command.CommandText == null ||
command.CommandText.Length == 0)
{
throw new InvalidOperationException(
"The IngresCommand.CommandText must be specify a procedure name.");
}
if (command.CommandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(
"Only CommandType.StoredProcedure is supported.");
}
ArrayList tokens = Ingres.ProviderInternals.MetaData.ScanSqlStatement(
command.CommandText);
String[] restriction = new String[3];
if (tokens.Count == 1) // procname
{
restriction[2] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
}
else
if (tokens.Count == 3 && // schemaname.procname
(String)tokens[1] == ".")
{
restriction[1] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
}
else
if (tokens.Count == 5 && // catalogname.schemaname.procname
(String)tokens[1] == "." &&
(String)tokens[3] == ".")
{
restriction[0] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[1] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[4], conn, "\"", "\"");
}
else
{
throw new InvalidOperationException(
"Invalid procedure name.");
}
DataTable datatable = conn.GetSchema("ProcedureParameters", restriction);
command.Parameters.Clear();
foreach (DataRow row in datatable.Rows)
{
string name = (String)row["COLUMN_NAME"];
IngresType ingresType = (IngresType)row["INGRESTYPE"];
IngresParameter parm = new IngresParameter(name, ingresType);
command.Parameters.Add(parm);
} // end foreach (DataRow row in datatable)
}
/// <summary>
/// Takes, as input, a list of user names and a list of role names and adds the specified
/// users to the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>AddUsersToRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">A list of user names.</param>
/// <param name="roleNames">A list of roles.</param>
internal void AddUsersToRoles(string[] usernames, string[] roleNames)
{
// Note: Most of this validation is also done by the .NET framework - however I will
// explicitly do it here also.
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Ensure that all of the roleNames are valid.
foreach (string rolename in roleNames)
{
if (!this.RoleExists(rolename))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
}
// Ensure that all of the usernames are valid.
foreach (string username in usernames)
{
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
}
// Ensure that all of the users actually are in the roles.
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyInRole, username, rolename));
}
}
}
// Instantiate a string to hold the required SQL
string sql = @"
INSERT INTO aspnet_UsersInRoles
(UserId,
RoleId)
VALUES
(?,
?)
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
IngresParameter userParam = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParam = cmd.Parameters.Add("RoleId", DbType.String);
// Note: this if a bit "row at a time" style rather than "chunk at a time"
// processing - however it makes for easier to read and understand code and
// allows for a static parameterized SQL string. If performance is poor then
// this could be changed.
// for each user
foreach (string username in usernames)
{
// obtain the user id
string userId = this.GetUserIdByName(username);
// for each rolename
foreach (string rolename in roleNames)
{
// obtain role id
string roleId = this.GetRoleIdByName(rolename);
userParam.Value = userId;
roleParam.Value = roleId;
// try to add user to role
// The number of rows affected
int rows = cmd.ExecuteNonQuery();
// One row should be affected
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
}
}
protected override IComponent[] CreateComponentsCore(IDesignerHost host)
{
const string prefix = VSNETConst.shortTitle; // "Ingres"
IContainer designerHostContainer = host.Container;
ArrayList newComponents = new ArrayList();
ArrayList newCommandComponents = new ArrayList();
string name;
IngresConnection connection;
IngresCommand providerCommand;
if (designerHostContainer == null) // safety check
return null;
IngresDataAdapter dataAdapter = new IngresDataAdapter();
// Once a reference to an assembly has been added to this service,
// this service can load types from names that
// do not specify an assembly.
ITypeResolutionService resService =
(ITypeResolutionService)host.GetService(typeof(ITypeResolutionService));
System.Reflection.Assembly assembly = dataAdapter.GetType().Module.Assembly;
if (resService != null)
{
resService.ReferenceAssembly(assembly.GetName());
// set the assembly name to load types from
}
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, "DataAdapter");
try
{
designerHostContainer.Add(dataAdapter, name);
newComponents.Add(dataAdapter);
}
catch (ArgumentException ex)
{
string exMsg = ex.ToString() +
"\nRemove IngresDataAdapter component '" + name + "'" +
" that is defined outside of the designer. ";
MessageBox.Show(exMsg, "Add " + name + " failed");
return null;
}
string [] commandName = new String []
{
"SelectCommand",
"InsertCommand",
"UpdateCommand",
"DeleteCommand",
};
providerCommand = new IngresCommand(); // SELECT
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.SelectCommand = providerCommand;
providerCommand = new IngresCommand(); // INSERT
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.InsertCommand = providerCommand;
providerCommand = new IngresCommand(); // UPDATE
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.UpdateCommand = providerCommand;
providerCommand = new IngresCommand(); // DELETE
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.DeleteCommand = providerCommand;
// invoke the wizard to create the connection and query
// pass VS.NET IDesignerHost host so we can add Connection
DataAdapterWizard wizard = new DataAdapterWizard(dataAdapter, host);
// The DataAdapterWizard called the ConnectionEditor and
// gave the Wizard and Editor a chance to create the Connection
// object and SELECT statement, or to cancel out.
// If not cancelled, add the Connection object to
// the VS.NET component tray if not already done.
if (dataAdapter.SelectCommand != null &&
dataAdapter.SelectCommand.Connection != null)
{
connection = dataAdapter.SelectCommand.Connection;
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, "Connection");
//add the connection to the VS.NET component tray
designerHostContainer.Add(connection, name);
}
// add the four Command objects (each with a unique name)
// to the designer's components tray
for (int i=0; i<4; i++)
{
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, commandName[i]);
designerHostContainer.Add(
(IComponent) newCommandComponents[i], name);
} // end for loop thru command components
string s = "Creating component list for "+ this.GetType() +"?\n";
return (IComponent[])(newComponents.ToArray(typeof(IComponent)));
//return base.CreateComponentsCore(host);
}
public QueryDesignerForm(IngresCommand providerCmd)
: this(providerCmd.Connection, providerCmd.CommandText)
{
}
public DataAdapterWizardGenerateForm(IngresCommand command)
{
this.Command = command;
this.CommandText =
command.CommandText != null?command.CommandText:String.Empty;
this.Name = "DataAdapterWizardGenerateForm";
Panel panel = new Panel();
panel.Parent = this;
panel.Location = new Point(0, 0);
panel.Size = new Size(MainSize.Width, 68);
panel.BackColor = SystemColors.Window;
panel.BorderStyle = BorderStyle.FixedSingle;
panel.Anchor =
AnchorStyles.Top |
AnchorStyles.Left |
AnchorStyles.Right;
// load the picture bitmap
PictureBox picture = new PictureBox();
System.Reflection.Assembly assembly = GetType().Module.Assembly;
Image bitmap =
Image.FromStream(assembly.GetManifestResourceStream(facemapName));
picture.Size = new Size(48, 60);
picture.Image = bitmap;
picture.SizeMode = PictureBoxSizeMode.StretchImage;
picture.TabStop = false;
picture.Location = new Point(panel.Width - picture.Size.Width - 15, 3);
picture.Anchor =
AnchorStyles.Top |
AnchorStyles.Right;
picture.BorderStyle = BorderStyle.FixedSingle;
picture.Parent = panel;
Label titleLabel = new Label();
titleLabel.Text = "Generate the SQL statements";
titleLabel.Parent = panel;
titleLabel.Anchor =
AnchorStyles.Top |
AnchorStyles.Left |
AnchorStyles.Right;
titleLabel.Font = new Font(
this.Font.FontFamily, this.Font.Height, FontStyle.Bold);
titleLabel.BackColor = SystemColors.Window;
titleLabel.ForeColor = SystemColors.WindowText;
titleLabel.Location = new Point(FormFontHeight, FormFontHeight);
titleLabel.Name = "titleLabel";
titleLabel.Size = new Size(MainSize.Width, 3 * this.Font.Height / 2);
Label msgLabel = new Label();
msgLabel.Text =
"The Select statement will be used to retrieve data from " +
"the database.";
msgLabel.Parent = panel;
msgLabel.Anchor =
AnchorStyles.Top |
AnchorStyles.Left |
AnchorStyles.Right;
//msgLabel.Font = new Font(this.Font.FontFamily, 14F, FontStyle.Bold);
msgLabel.BackColor = SystemColors.Window;
msgLabel.ForeColor = SystemColors.WindowText;
msgLabel.Location = new Point(
2 * FormFontHeight, titleLabel.Location.Y + titleLabel.Size.Height);
msgLabel.Name = "msgLabel";
msgLabel.Size = new Size(
MainSize.Width - 6 * FormFontHeight, 3 * FormFontHeight);
Label msgLabel1 = new Label();
msgLabel1.Text =
"Enter your SQL command or click on Query Builder to design your query.";
msgLabel1.Parent = this;
msgLabel1.Anchor =
AnchorStyles.Top |
AnchorStyles.Left |
AnchorStyles.Right;
//msgLabel1.Font = new Font(this.Font.FontFamily, 14F, FontStyle.Bold);
//msgLabel1.BackColor = SystemColors.Control;
//msgLabel1.ForeColor = SystemColors.ControlText;
msgLabel1.Location = Below(panel, FormFontHeight, FormFontHeight);
msgLabel1.Name = "msgLabelSelect";
msgLabel1.Size = new Size(
MainSize.Width - msgLabel1.Location.X, 2 * FormFontHeight);
Label msgLabel2 = new Label();
msgLabel2.Text =
"SELECT Command Text:";
msgLabel2.Parent = this;
msgLabel2.Anchor =
AnchorStyles.Top |
AnchorStyles.Left |
AnchorStyles.Right;
//msgLabel2.Font = new Font(this.Font.FontFamily, 14F, FontStyle.Bold);
//msgLabel2.BackColor = SystemColors.Control;
//msgLabel2.ForeColor = SystemColors.ControlText;
msgLabel2.Location = Below(msgLabel1, FormFontHeight);
msgLabel2.Name = "msgLabelSelectCommand";
msgLabel2.Size = new Size(
MainSize.Width - msgLabel2.Location.X, FormFontHeight);
txtbox = new TextBox();
txtbox.Parent = this;
txtbox.Anchor =
AnchorStyles.Top |
AnchorStyles.Bottom |
AnchorStyles.Left |
AnchorStyles.Right;
txtbox.Multiline = true;
txtbox.ScrollBars = ScrollBars.Both;
txtbox.AcceptsReturn = true;
txtbox.AcceptsTab = true;
txtbox.Location = Below(msgLabel2);
txtbox.Size = new Size(
MainSize.Width - 2 * txtbox.Location.X,
MainSize.Height - txtbox.Location.Y - 2 * ButtonSize.Height);
txtbox.TextChanged +=
new System.EventHandler(this.txtbox_TextChanged);
txtbox.Leave +=
new System.EventHandler(this.txtbox_OnLeave);
// QueryBuilder button
Button btnBuilderButton = new Button();
btnBuilderButton.Parent = this;
btnBuilderButton.Text = "&Query Builder...";
btnBuilderButton.Size = new Size(4 * ButtonSize.Width / 3, ButtonSize.Height);
btnBuilderButton.Location =
new Point(ClientSize.Width - btnBuilderButton.Size.Width - 10,
Below(txtbox, FormFontHeight).Y);
btnBuilderButton.Anchor =
AnchorStyles.Bottom |
AnchorStyles.Right;
btnBuilderButton.Click +=
new System.EventHandler(this.btnQueryBuilder_Click);
// if Back or Finish buttons clicked then set form's CommandText
btnBackButton.Click +=
new System.EventHandler(this.btnFinishOrBackButton_Click);
btnFinishButton.Click +=
new System.EventHandler(this.btnFinishOrBackButton_Click);
this.Load += new System.EventHandler(
this.DataAdapterWizardGenerateForm_Load);
} // DataAdapterWizardGenerateForm constructor
/// <summary>
/// Takes, as input, a user name and a role name and determines whether the specified user
/// is associated with the specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>IsUserInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username that we wish to check.</param>
/// <param name="roleName">The role which we wish to check.</param>
/// <returns>Whether the given user is in the given role.</returns>
internal bool IsUserInRole(string username, string roleName)
{
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username");
if (username.Length < 1)
{
return(false);
}
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserNotFound));
}
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// ensure that we have an appication id
if (this.ApplicationId == null)
{
throw new ArgumentNullException("ApplicationId");
}
// Instantiate a bool for whether the user is in the role - assume not.
bool userIsInRole = false;
// Build up the SQL string
string sql = @"
SELECT
COUNT(*)
FROM
aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// Create a new command and enrol in the current transaction.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Get the user and role id's for the specified user and role names.
string userid = this.GetUserIdByName(username);
string roleid = this.GetRoleIdByName(roleName);
cmd.Parameters.Add("UserId", DbType.String).Value = userid;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
// Execute the query and determine is the user is in the role.
int rows = Convert.ToInt32(cmd.ExecuteScalar());
if (rows > 0)
{
userIsInRole = true;
}
return(userIsInRole);
}
/// <summary>
/// Takes, as input, a list of user names and a list of role names and removes the
/// specified users from the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>RemoveUsersFromRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">The array of usernames.</param>
/// <param name="roleNames">The array of roles.</param>
internal void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
// Validate the input parameters
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Instantiate lists to hold the calculated user and role Ids.
List <string> userIdsList = new List <string>();
List <string> roleIdsList = new List <string>();
// Ensure that all of the roleNames are valid
foreach (string username in usernames)
{
string userId = this.GetUserIdByName(username);
if (userId == null)
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
userIdsList.Add(userId);
}
// Ensure that all of the roleNames are valid
foreach (string rolename in roleNames)
{
string roleId = this.GetRoleIdByName(rolename);
if (roleId == null)
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
roleIdsList.Add(roleId);
}
// Ensure that the users are actually the the roles to begin with!
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (!this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyNotInRole, username, rolename));
}
}
}
// Build up the SQL string
string sql = @"
DELETE FROM aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// Create a new command and enrol in the current transaction.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
IngresParameter userParm = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParm = cmd.Parameters.Add("RoleId", DbType.String);
// For each user
foreach (string userId in userIdsList)
{
// For each role
foreach (string roleId in roleIdsList)
{
userParm.Value = userId;
roleParm.Value = roleId;
// Remove the user from the role
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Takes, as input, a role name and a Boolean value that indicates whether to throw an
/// exception if there are users currently associated with the role, and then deletes the
/// specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The user name that we wish to delete.</param>
/// <param name="throwOnPopulatedRole">Whether we should throw an exception if the role
/// we wish to delete has any users in the role or not.</param>
/// <returns>Whether the role was successfully deleted.</returns>
internal bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
// Validate the rolename
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// The roleName should exist
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// If we have to throw errors for a poupulated roll then we must check if there are
// any users in the role.
if (throwOnPopulatedRole && (this.GetUsersInRole(roleName).Length > 0))
{
throw new ProviderException(string.Format(Messages.RoleNotEmpty));
}
string sqlDeleteUsersInRoles = @"
DELETE FROM aspnet_UsersInRoles
WHERE
RoleId = ?
";
// Create a new command and enrol in the current transactions
IngresCommand cmdDeleteUsersInRoles = new IngresCommand(sqlDeleteUsersInRoles, this.conn);
cmdDeleteUsersInRoles.Transaction = this.tran;
cmdDeleteUsersInRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required parameter
cmdDeleteUsersInRoles.Parameters.Add("RoleId", DbType.String).Value = this.GetRoleIdByName(roleName);
// Delete the users
cmdDeleteUsersInRoles.ExecuteNonQuery();
string sqlDeleteRoles = @"
DELETE FROM aspnet_Roles
WHERE
LoweredRolename = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmdDeleteRoles = new IngresCommand(sqlDeleteRoles, this.conn);
cmdDeleteRoles.Transaction = this.tran;
cmdDeleteRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required paramaters
cmdDeleteRoles.Parameters.Add("LoweredRolename", DbType.String).Value = roleName.ToLower();
cmdDeleteRoles.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Finally delete the role
int rows = cmdDeleteRoles.ExecuteNonQuery();
// If more than one row was effected then throw an error
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
return(true);
}
/// <summary>
/// Takes, as input, a search pattern and a role name and returns a list of users belonging
/// to the specified role whose user names match the pattern. Wildcard syntax is
/// data-source-dependent and may vary from provider to provider. User names are returned
/// in alphabetical order.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>FindUsersInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The rolename to find users for.</param>
/// <param name="usernameToMatch">The username wildcard to match.</param>
/// <returns>Returns a list of users belonging to the specified role whose user names match the pattern.</returns>
internal string[] FindUsersInRole(string roleName, string usernameToMatch)
{
// Validate the input parameters
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
ValidationUtil.CheckParameterIsOK(ref usernameToMatch, true, true, false, 256, "usernameToMatch");
// Check if the role exists and throw an exception if it doesn't
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// Get the role id
string roleId = this.GetRoleIdByName(roleName);
// Adjust the username so that it is in the correct format for an Ingres "LIKE"
usernameToMatch = String.Format("%{0}%", usernameToMatch);
string sql = @"
SELECT
aspnet_Users.UserName
FROM
aspnet_Users,
aspnet_UsersInRoles
WHERE
aspnet_Users.Username LIKE ?
AND aspnet_Users.UserId = aspnet_UsersInRoles.UserId
AND aspnet_UsersInRoles.RoleId = ?
AND aspnet_Users.ApplicationId = ?
ORDER BY
aspnet_Users.UserName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the requires parameters
cmd.Parameters.Add("Username", DbType.String).Value = usernameToMatch;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleId;
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
// Instantiate a new reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get the results out of the reader
List <string> userNamesList = new List <string>();
while (reader.Read())
{
userNamesList.Add(DBUtil.ColValAsString(reader, "UserName"));
}
reader.Close();
// return an appropriate string array
if (userNamesList.Count > 0)
{
return(userNamesList.ToArray());
}
}
return(new string[0]);
}
CreateComponentsCore(IDesignerHost host)
{
const string prefix = VSNETConst.shortTitle; // "Ingres"
IContainer designerHostContainer = host.Container;
ArrayList newComponents = new ArrayList();
ArrayList newCommandComponents = new ArrayList();
string name;
IngresConnection connection;
IngresCommand providerCommand;
if (designerHostContainer == null) // safety check
{
return(null);
}
IngresDataAdapter dataAdapter = new IngresDataAdapter();
// Once a reference to an assembly has been added to this service,
// this service can load types from names that
// do not specify an assembly.
ITypeResolutionService resService =
(ITypeResolutionService)host.GetService(typeof(ITypeResolutionService));
System.Reflection.Assembly assembly = dataAdapter.GetType().Module.Assembly;
if (resService != null)
{
resService.ReferenceAssembly(assembly.GetName());
// set the assembly name to load types from
}
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, "DataAdapter");
try
{
designerHostContainer.Add(dataAdapter, name);
newComponents.Add(dataAdapter);
}
catch (ArgumentException ex)
{
string exMsg = ex.ToString() +
"\nRemove IngresDataAdapter component '" + name + "'" +
" that is defined outside of the designer. ";
MessageBox.Show(exMsg, "Add " + name + " failed");
return(null);
}
string [] commandName = new String []
{
"SelectCommand",
"InsertCommand",
"UpdateCommand",
"DeleteCommand",
};
providerCommand = new IngresCommand(); // SELECT
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.SelectCommand = providerCommand;
providerCommand = new IngresCommand(); // INSERT
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.InsertCommand = providerCommand;
providerCommand = new IngresCommand(); // UPDATE
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.UpdateCommand = providerCommand;
providerCommand = new IngresCommand(); // DELETE
newCommandComponents.Add(providerCommand);
providerCommand.DesignTimeVisible = false;
dataAdapter.DeleteCommand = providerCommand;
// invoke the wizard to create the connection and query
// pass VS.NET IDesignerHost host so we can add Connection
DataAdapterWizard wizard = new DataAdapterWizard(dataAdapter, host);
// The DataAdapterWizard called the ConnectionEditor and
// gave the Wizard and Editor a chance to create the Connection
// object and SELECT statement, or to cancel out.
// If not cancelled, add the Connection object to
// the VS.NET component tray if not already done.
if (dataAdapter.SelectCommand != null &&
dataAdapter.SelectCommand.Connection != null)
{
connection = dataAdapter.SelectCommand.Connection;
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, "Connection");
//add the connection to the VS.NET component tray
designerHostContainer.Add(connection, name);
}
// add the four Command objects (each with a unique name)
// to the designer's components tray
for (int i = 0; i < 4; i++)
{
name = DesignerNameManager.CreateUniqueName(
designerHostContainer, prefix, commandName[i]);
designerHostContainer.Add(
(IComponent)newCommandComponents[i], name);
} // end for loop thru command components
string s = "Creating component list for " + this.GetType() + "?\n";
return((IComponent[])(newComponents.ToArray(typeof(IComponent))));
//return base.CreateComponentsCore(host);
} // CreateComponentsCore
internal DbCommand CreateCommand(
DbProviderManifest manifest,
DbCommandTree commandTree,
bool wantParameters)
{
CheckNotNull(manifest, "manifest");
CheckNotNull(commandTree, "commandTree");
IngresCommand command = new IngresCommand();
try
{
List <DbParameter> parameters;
CommandType commandType;
command.CommandText = SqlGenerator.GenerateSql((IngresProviderManifest)manifest, commandTree, out parameters, out commandType);
command.CommandType = commandType;
if (wantParameters == false)
{
return(command);
}
// Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = ((DbFunctionCommandTree)commandTree).EdmFunction;
}
// Now make sure we populate the command's parameters
// from the Canonical Query Trees's (CQT) parameters:
foreach (KeyValuePair <string, TypeUsage> queryParameter in commandTree.Parameters)
{
IngresParameter parameter;
// Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
// type trump user-defined facets and type in the EntityCommand).
FunctionParameter functionParameter;
if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter))
{
parameter = CreateSqlParameter((IngresProviderManifest)manifest, functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateSqlParameter((IngresProviderManifest)manifest, queryParameter.Key, queryParameter.Value, ParameterMode.In, DBNull.Value);
}
command.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
// does not support user parameters, where there is no risk of name collision)
if (null != parameters && 0 < parameters.Count)
{
if (!(commandTree is DbInsertCommandTree) &&
!(commandTree is DbUpdateCommandTree) &&
!(commandTree is DbDeleteCommandTree))
{
throw new InvalidOperationException("SqlGenParametersNotPermitted");
}
foreach (DbParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return(command);
}
catch
{
command.Dispose();
throw;
}
}
static void TestDbProc(string connstring, Type type)
{
int recsaffected;
string strSeq;
string strName;
IngresConnection conn = new IngresConnection(connstring);
IngresCommand cmd;
Console.WriteLine("\nTestDbProc (using " + type.ToString()
+ ") to database: " + conn.Database);
conn.Open();
cmd = new IngresCommand(
"declare global temporary table session.tab (seq integer," +
"tabname varchar(32), tabowner varchar(32), numpages integer)" +
" on commit preserve rows with norecovery", conn);
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"declare gtt\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"insert into session.tab (tabname,tabowner,numpages)" +
" select table_name,table_owner,number_pages from iitables", conn);
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"insert into gtt\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"{ call gttproc(gtt1 = session.tab)}", conn);
// "{ call gttproc(session.tab)}", conn);
cmd.CommandType = CommandType.Text;
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"execute proc\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"select seq,tabname from session.tab", conn);
IngresDataReader reader = cmd.ExecuteReader();
Console.Write(reader.GetName(0) + "\t");
Console.Write(reader.GetName(1));
Console.WriteLine();
while (reader.Read())
{
strSeq = reader.IsDBNull(0)?
"<none>":reader.GetInt32(0).ToString();
strName = reader.IsDBNull(1)?
"<none>":reader.GetString(1);
Console.WriteLine(strSeq + "\t" + strName);
} // end while loop through result set
reader.Close();
conn.Close();
}
/// <summary>
/// Takes, as input, a role name and returns the names of all users assigned to that role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetUsersInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The rolename that we wish to get all the users for.</param>
/// <returns>An array of all of the users for the given role.</returns>
internal string[] GetUsersInRole(string roleName)
{
// If we don't have an application id then we aren't going to have any roles.
if (this.ApplicationId == null)
{
return(new string[0]);
}
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Check if the role exists and throw an exception if it doesn't
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
string sql = @"
SELECT
aspnet_Users.UserName
FROM
aspnet_Users,
aspnet_UsersInRoles,
aspnet_Roles
WHERE
aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId
AND aspnet_UsersInRoles.UserId = aspnet_Users.UserId
AND aspnet_Roles.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Users.ApplicationId = ?
AND aspnet_Roles.LoweredRoleName = ?
ORDER BY
aspnet_Users.UserName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Instantiate a new reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get the users from the reader
List <string> userList = new List <string>();
while (reader.Read())
{
userList.Add(DBUtil.ColValAsString(reader, "UserName"));
}
// Close the reader
reader.Close();
// retun an appropriate string array dependent on whether we managed to return any users or not
if (userList.Count > 0)
{
return(userList.ToArray());
}
}
// no users so return an empty array
return(new string[0]);
}
public EFIngresCommand(string commandText, EFIngresConnection connection)
{
_wrappedCommand = new IngresCommand();
CommandText = commandText;
DbConnection = connection;
}
} // end constructor DataAdapter
/// <summary>
/// Create a new DataAdapter object with specified SELECT Command object.
/// </summary>
/// <param name="selectCommand">
/// Command object with SELECT SQL CommandText.</param>
public IngresDataAdapter(IngresCommand selectCommand)
{
this.SelectCommand = selectCommand;
} // end constructor DataAdapter
