static public void NewCode(Page page, Int64 entityId, out String error)
{
error = "";
try
{
if (entityId == 0)
{
return;
}
String code = GenerateCode(6);
using (IAMDatabase db = new IAMDatabase(IAMDatabase.GetWebConnectionString()))
using (DbParameterCollection par = new DbParameterCollection())
{
par.Add("@code", typeof(String)).Value = code;
par.Add("@entity_id", typeof(Int64)).Value = entityId;
db.ExecuteNonQuery("update entity set recovery_code = @code where deleted = 0 and id = @entity_id and (recovery_code is null or ltrim(rtrim(recovery_code)) = '')", CommandType.Text, par);
db.AddUserLog(LogKey.User_NewRecoveryCode, null, "AutoService", UserLogLevel.Info, 0, 0, 0, 0, 0, entityId, 0, MessageResource.GetMessage("new_recovery_code") + " (" + code + ")", "{ \"ipaddr\":\"" + Tools.Tool.GetIPAddress() + "\"} ");
}
}
catch (Exception ex)
{
error = MessageResource.GetMessage("internal_error");
Tools.Tool.notifyException(ex, page);
return;
}
finally
{
}
}
public override Boolean UserAdmin(DbBase database, Int64 entityId, Int64 enterpriseId)
{
if ((!(database is IAMDatabase)) && (!(database is SqlBase)))
{
throw new Exception("Invalid database type. Expected IAMDatabase or SqlBase");
}
DbParameterCollection par = null;
try
{
par = new DbParameterCollection();
par.Add("@entity_id", typeof(Int64)).Value = entityId;
par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId;
return(database.ExecuteScalar <Boolean>("sp_sys_rbac_admin", CommandType.StoredProcedure, par, null));
}
catch (Exception ex)
{
return(false);
}
finally
{
par = null;
}
}
public override Boolean UserCan(DbBase database, Int64 entityId, Int64 enterpriseId, String module, String permission)
{
if ((!(database is IAMDatabase)) && (!(database is SqlBase)))
{
throw new Exception("Invalid database type. Expected IAMDatabase or SqlBase");
}
DbParameterCollection par = null;
try
{
String[] parts = permission.ToLower().Split(".".ToCharArray(), 2);
par = new DbParameterCollection();
par.Add("@entity_id", typeof(Int64)).Value = entityId;
par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId;
par.Add("@submodule", typeof(String)).Value = parts[0];
par.Add("@permission", typeof(String)).Value = parts[1];
return(database.ExecuteScalar <Boolean>("sp_sys_rbac", CommandType.StoredProcedure, par, null));
}
catch (Exception ex)
{
return(false);
}
finally
{
par = null;
}
}
public override void ResetItemTimeout(HttpContext context, string id)
{
DbProviderFactory factory = ProviderFactory;
DbConnection conn = CreateConnection(factory);
DbCommand cmd = CreateCommand(factory, conn,
"UPDATE Sessions SET Expires = @Expires WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName");
DbParameterCollection parameters = cmd.Parameters;
parameters.Add(CreateParameter <DateTime> (factory, "@Expires", DateTime.Now.AddMinutes(sessionConfig.Timeout.TotalMinutes)));
parameters.Add(CreateParameter <string> (factory, "@SessionId", id, 80));
parameters.Add(CreateParameter <string> (factory, "@ApplicationName", ApplicationName, 255));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ProviderException("Error resetting session item timeout in the database.", ex);
}
finally
{
conn.Close();
}
}
public override void RemoveItem(HttpContext context, string id, object lockId, SessionStateStoreData item)
{
DbProviderFactory factory = ProviderFactory;
DbConnection conn = CreateConnection(factory);
DbCommand cmd = CreateCommand(factory, conn,
"DELETE FROM Sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId");
DbParameterCollection parameters = cmd.Parameters;
parameters.Add(CreateParameter <string> (factory, "@SessionId", id, 80));
parameters.Add(CreateParameter <string> (factory, "@ApplicationName", ApplicationName, 255));
parameters.Add(CreateParameter <int> (factory, "@LockId", (int)lockId));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ProviderException("Error removing item from database.", ex);
}
finally
{
conn.Close();
}
}
public void AddParametersFromCache(string key, DbCommand cmd, DbType[] types, object[] values)
{
DbParameterCollection parms = cmd.Parameters;
parms.Clear();
DbParameter[] cachedParams = cache[key];
if (cachedParams != null && cachedParams.Length > 0)
{
if (types == null)
{
for (int i = 0; i < cachedParams.Length; i++)
{
parms.Add(((ICloneable)cachedParams[i]).Clone());
parms[i].Value = values[i];
db.DbProvider.AdjustParameter(parms[i]);
}
}
else
{
for (int i = 0; i < cachedParams.Length; i++)
{
parms.Add(((ICloneable)cachedParams[i]).Clone());
parms[i].Value = values[i];
parms[i].DbType = types[i];
db.DbProvider.AdjustParameter(parms[i]);
}
}
}
}
public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
{
DbProviderFactory factory = ProviderFactory;
DbConnection conn = CreateConnection(factory);
DbCommand cmd = CreateCommand(factory, conn,
"INSERT INTO Sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values (@SessionId, @ApplicationName, @Created, @Expires, @LockDate, @LockId , @Timeout, @Locked, @SessionItems, @Flags)");
DateTime now = DateTime.Now;
DbParameterCollection parameters = cmd.Parameters;
parameters.Add(CreateParameter <string> (factory, "@SessionId", id, 80));
parameters.Add(CreateParameter <string> (factory, "@ApplicationName", ApplicationName, 255));
parameters.Add(CreateParameter <DateTime> (factory, "@Created", now));
parameters.Add(CreateParameter <DateTime> (factory, "@Expires", now.AddMinutes((double)timeout)));
parameters.Add(CreateParameter <DateTime> (factory, "@LockDate", now));
parameters.Add(CreateParameter <int> (factory, "@LockId", 0));
parameters.Add(CreateParameter <int> (factory, "@Timeout", timeout));
parameters.Add(CreateParameter <bool> (factory, "@Locked", false));
parameters.Add(CreateParameter <string> (factory, "@SessionItems", String.Empty));
parameters.Add(CreateParameter <int> (factory, "@Flags", 1));
try {
conn.Open();
cmd.ExecuteNonQuery();
} catch (Exception ex) {
throw new ProviderException("Error creating uninitialized session item in the database.", ex);
} finally {
conn.Close();
}
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> newrole(IAMDatabase database, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("name"))
{
Error(ErrorType.InvalidRequest, "Parameter name is not defined.", "", null);
return(null);
}
String name = parameters["name"].ToString();
if (String.IsNullOrWhiteSpace(name))
{
Error(ErrorType.InvalidRequest, "Parameter name is not defined.", "", null);
return(null);
}
Int64 parentid = 0;
if (parameters.ContainsKey("parentid"))
{
try
{
parentid = Int64.Parse(parameters["parentid"].ToString());
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter parentid is not a long integer.", "", null);
return(null);
}
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@name", typeof(String)).Value = name;
par.Add("@parent_id", typeof(Int64)).Value = parentid;
par.Add("@system_admin", typeof(Boolean)).Value = false;
par.Add("@enterprise_admin", typeof(Int64)).Value = (parameters.ContainsKey("enterprise_admin") && (parameters["enterprise_admin"] is Boolean) && (Boolean)parameters["enterprise_admin"]);
DataTable dtUsers = database.ExecuteDataTable("sp_new_sys_role", CommandType.StoredProcedure, par, null);
if (dtUsers == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtUsers.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "System role not found.", "", null);
return(null);
}
parameters.Add("roleid", dtUsers.Rows[0]["id"]);
return(get(database, parameters));
}
///<summary>
///Adds the parameters from cache.
///</summary>
///<param name="key">The key.</param>
///<param name="cmd">The CMD.</param>
///<param name="types">The types.</param>
///<param name="values">The values.</param>
public void AddParametersFromCache(string key, DbCommand cmd, DbType[] types, object[] values)
{
DbParameterCollection parms = cmd.Parameters;
parms.Clear();
DbParameter[] cacheParams = cache[key];
if (cacheParams != null && cacheParams.Length > 0)
{
if (types == null)
{
for (int i = 0; i < cacheParams.Length; i++)
{
DbParameter param = (DbParameter)((ICloneable)cacheParams[i]).Clone();
param.Value = values[i];
this.db.DbConfiguration.DbDriver.AdjustParameter(param);
parms.Add(param);
}
}
else
{
for (int i = 0; i <= cacheParams.Length - 1; i++)
{
DbParameter param = (DbParameter)((ICloneable)cacheParams[i]).Clone();
param.DbType = types[i];
param.Value = values[i];
this.db.DbConfiguration.DbDriver.AdjustParameter(param);
parms.Add(param);
}
}
}
}
protected Dictionary <String, Object> GetAuthConfig(IAMDatabase database, System.Web.UI.Page page)
{
Dictionary <String, Object> config = new Dictionary <string, object>();
Int64 enterpriseId = 0;
if ((page.Session["enterprise_data"]) != null && (page.Session["enterprise_data"] is EnterpriseData))
{
enterpriseId = ((EnterpriseData)page.Session["enterprise_data"]).Id;
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId;
par.Add("@plugin", typeof(String)).Value = this.GetPluginId().AbsoluteUri;
DataTable conf = database.ExecuteDataTable("select distinct [key], [value] from dbo.enterprise_auth_par where enterprise_id = @enterprise_id and plugin = @plugin", CommandType.Text, par);
if ((conf != null) && (conf.Rows.Count > 0))
{
foreach (DataRow dr in conf.Rows)
{
FillConfig(this, ref config, dr["key"].ToString(), dr["value"].ToString());
}
}
return(config);
}
public Boolean Send(String mailFom, String smtpServer, String username, String password)
{
try
{
foreach (MailAddress mail in this.mailTo)
{
sendEmail(this.mailSubject, mail.Address, null, this.mailBody, this.isHtml, mailFom, smtpServer, username, password);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@message_id", typeof(Int64)).Value = this.messageId;
par.Add("@status", typeof(String)).Value = "Email enviado com sucesso";
par.Add("@description", typeof(String)).Value = "";
database.ExecuteNonQuery("UPDATE st_messages SET [status] = 'OK' WHERE id = @message_id; INSERT INTO st_messages_status (message_id,date,error,status,description) VALUES(@message_id,getdate(),1,@status,@description);", par);
return(true);
}
catch (Exception ex)
{
DbParameterCollection par = new DbParameterCollection();
par.Add("@message_id", typeof(Int64)).Value = this.messageId;
par.Add("@status", typeof(String)).Value = "Erro no tratamento das variáveis";
par.Add("@description", typeof(String)).Value = ex.Message;
database.ExecuteNonQuery("UPDATE st_messages SET [status] = 'PE' WHERE id = @message_id; INSERT INTO st_messages_status (message_id,date,error,status,description) VALUES(@message_id,getdate(),1,@status,@description);", par);
return(false);
}
}
/// <sammary>
/// Estou adiciondo novos paramestro a coleção, para ser usados
/// </sammary>
/// <param name="parametro">Nome do Parametro da Query ou Procedure</param>
/// <param name="valor">Objeto com o calor do parametro</param>
public void AdicionaParametros(string parametro, object valor)
{
DbParameter dbParameter = AcessaBancoProviderFactory.getFactory().CreateCommand().CreateParameter();
dbParameter.ParameterName = parametro;
dbParameter.Value = valor;
dbParameterCollection.Add(dbParameter);
}
internal override void AddSqlParameters(DbParameterCollection parameters, Func <string, DbType, int, object, DbParameter> makeParam)
{
base.AddSqlParameters(parameters, makeParam);
parameters.Add(makeParam("Value", DbType.Single, 0, (float)Value));
parameters.Add(makeParam("OldValue", DbType.Single, 0, OldValue.HasValue ? (object)(float)OldValue.Value : DBNull.Value));
parameters.Add(makeParam("Operator", DbType.Int32, 0, OperatorId));
}
public void AddLinkCount(Page page)
{
try
{
Int64 id = 0;
if (!String.IsNullOrWhiteSpace((String)page.RouteData.Values["id"]))
{
Int64.TryParse((String)page.RouteData.Values["id"], out id);
}
//Não selecinou nenhum item, ou seja menu principal
if (id == 0)
{
//Opções para gravação dos links na base
Int64 enterpriseId = 0;
Int64 userId = 0;
String submodule = "dashboard";
if ((page.Session["enterprise_data"]) != null && (page.Session["enterprise_data"] is EnterpriseData))
{
enterpriseId = ((EnterpriseData)page.Session["enterprise_data"]).Id;
}
if ((page.Session["login"]) != null && (page.Session["login"] is LoginData))
{
userId = ((LoginData)page.Session["login"]).Id;
}
if (userId > 0 && enterpriseId > 0)
{
String path = page.Request.ServerVariables["PATH_INFO"].ToLower();
String ApplicationVirtualPath = HostingEnvironment.ApplicationVirtualPath;
path = path.Substring(ApplicationVirtualPath.Length); //Corta o virtual path
if (path.IndexOf("_admin") == 0)
{
return;
}
if (!String.IsNullOrWhiteSpace((String)page.RouteData.Values["module"]))
{
submodule = (String)page.RouteData.Values["module"];
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = enterpriseId;
par.Add("@entity_id", typeof(Int64)).Value = userId;
par.Add("@module", typeof(String)).Value = "admin";
par.Add("@submodule", typeof(String)).Value = submodule;
par.Add("@path", typeof(String)).Value = path;
ExecuteNonQuery("sp_insert_link_count", System.Data.CommandType.StoredProcedure, par);
}
}
}
catch { }
}
public void ServiceStatus(String serviceName, String additionsData, SqlTransaction transaction)
{
DbParameterCollection par = new DbParameterCollection();
par.Add("@name", typeof(String)).Value = serviceName;
par.Add("@data", typeof(String)).Value = additionsData;
ExecuteNonQuery("sp_service_status", System.Data.CommandType.StoredProcedure, par, transaction);
}
/// <summary>
///
/// </summary>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
public void InsertUpdateDeleteParameter(string parameterName, DbType dbType, object value)
{
DbParameter sbp = dbFactory.CreateParameter();
sbp.ParameterName = parameterName;
sbp.DbType = dbType;
sbp.Value = value;
dbInsertUpdateDeleteParameter.Add(sbp);
}
/// <summary>
/// Método privado para processamento do método 'logs.list'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Object get(IAMDatabase database, Dictionary <String, Object> parameters)
{
if (!parameters.ContainsKey("logid"))
{
Error(ErrorType.InvalidRequest, "Parameter logid is not defined.", "", null);
return(null);
}
String logid = parameters["logid"].ToString();
if (String.IsNullOrWhiteSpace(logid))
{
Error(ErrorType.InvalidRequest, "Parameter logid is not defined.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@id", typeof(String)).Value = logid;
String sql = "";
sql += "SELECT l.*, res.name resource_name, p.name plugin_name, e.full_name executed_by_name";
sql += " from logs l with(nolock) ";
sql += " left join [identity] i with(nolock) on i.id = l.identity_id";
sql += " left join resource res with(nolock) on res.id = l.resource_id";
sql += " left join plugin p with(nolock) on p.id = l.plugin_id";
sql += " left join entity e with(nolock) on e.id = l.executed_by_entity_id";
sql += " WHERE";
sql += " l.id = @id";
DataTable dtLogs = database.ExecuteDataTable(sql, CommandType.Text, par, null);
if ((dtLogs != null) && (dtLogs.Rows.Count > 0))
{
DataRow dr1 = dtLogs.Rows[0];
Dictionary <string, object> newItem = new Dictionary <string, object>();
newItem.Add("log_id", dr1["id"]);
newItem.Add("date", (Int32)((((DateTime)dr1["date"]) - new DateTime(1970, 1, 1)).TotalSeconds));
newItem.Add("source", dr1["source"]);
newItem.Add("level", dr1["level"]);
newItem.Add("identity_id", dr1["identity_id"]);
newItem.Add("resource_name", dr1["resource_name"]);
newItem.Add("plugin_name", dr1["plugin_name"]);
newItem.Add("text", dr1["text"]);
newItem.Add("additional_data", dr1["additional_data"]);
newItem.Add("executed_by_entity_id", (Int64)dr1["executed_by_entity_id"]);
newItem.Add("executed_by_name", (dr1["executed_by_name"] == DBNull.Value ? "System" : dr1["executed_by_name"].ToString()));
return(newItem);
}
return(null);
}
public void AddPackageTrack(Int64 packageId, String source, String text, SqlTransaction transaction)
{
DbParameterCollection par = new DbParameterCollection();
par.Add("@package_id", typeof(Int64)).Value = packageId;
par.Add("@source", typeof(String)).Value = source;
par.Add("@text", typeof(String), text.Length).Value = text;
ExecuteNonQuery("insert into st_package_track_history ([package_id] ,[source] ,[text]) values (@package_id ,@source ,@text)", System.Data.CommandType.Text, par, transaction);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Boolean restart(IAMDatabase database, Dictionary <String, Object> parameters)
{
if (!parameters.ContainsKey("proxyid"))
{
Error(ErrorType.InvalidRequest, "Parameter proxyid is not defined.", "", null);
return(false);
}
String proxy = parameters["proxyid"].ToString();
if (String.IsNullOrWhiteSpace(proxy))
{
Error(ErrorType.InvalidRequest, "Parameter proxyid is not defined.", "", null);
return(false);
}
Int64 proxyid = 0;
try
{
proxyid = Int64.Parse(proxy);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter proxyid is not a long integer.", "", null);
return(false);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@proxy_id", typeof(Int64)).Value = proxyid;
DataTable dtProxy = database.ExecuteDataTable("select * from proxy p where (p.enterprise_id = @enterprise_id or p.enterprise_id = 0) and p.id = @proxy_id", CommandType.Text, par, null);
if (dtProxy == null)
{
Error(ErrorType.InternalError, "", "", null);
return(false);
}
if (dtProxy.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "Proxy not found.", "", null);
return(false);
}
database.ExecuteNonQuery("update proxy set restart = 1 where id = @proxy_id", CommandType.Text, par);
database.AddUserLog(LogKey.Proxy_ResetRequest, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, 0, 0, "Proxy " + dtProxy.Rows[0]["name"] + " reset requested", "");
return(true);
}
public void insertMessages(String testId, String title, String text)
{
DbParameterCollection par = new DbParameterCollection();
par.Add("@date", typeof(DateTime)).Value = DateTime.Now;
par.Add("@pid", typeof(Int64)).Value = System.Diagnostics.Process.GetCurrentProcess().Id;
par.Add("@testId", typeof(String), testId.Length).Value = testId;
par.Add("@title", typeof(String), title.Length).Value = title;
par.Add("@text", typeof(String), text.Length).Value = text;
ExecuteNonQuery("insert into Messages ([date] ,[pid] ,[testId] ,[title] ,[text]) values (@date ,@pid ,@testId ,@title ,@text)", System.Data.CommandType.Text, par, null);
}
public static void AddPageSort(this DbParameterCollection parameters, PageSort item)
{
if (item != null)
{
parameters.Add(new SqlParameter("PageNumber", item.PageNumber));
parameters.Add(new SqlParameter("PageSize", item.PageSize));
if (item.OrderBy != null && item.OrderBy.Count > 0)
{
var orderBy = item.OrderBy.First();
parameters.Add(new SqlParameter("Column", orderBy.Column));
parameters.Add(new SqlParameter("SortDirection", orderBy.Direction));
}
}
}
public static object ExecuteScalar(string dbName, string strCommand, CommandType commandType, DBParam[] listParam)
{
strCommand = ParseSelector(strCommand, listParam);
object result = null;
using (DbCommand dbCommand = GetCommand(dbName, strCommand))
{
dbCommand.CommandType = commandType;
if (listParam != null)
{
DbParameterCollection parameters = dbCommand.Parameters;
foreach (DBParam dBParam in listParam)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.ParameterName = dBParam.ParamName;
dbParameter.DbType = dBParam.ParamDbType;
dbParameter.Value = dBParam.ParamValue;
if (!parameters.Contains(dbParameter))
{
parameters.Add(dbParameter);
}
}
}
result = dbCommand.ExecuteScalar();
dbCommand.Connection.Close();
dbCommand.Dispose();
}
return(result);
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Ouput parameter that will contain the value retrieved by RETURNING clause.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
IDbColumn autoIdField = GetAutoIdField(insert.Table);
DbParameter autoId = null;
if (autoIdField != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
parameters.Add(autoId);
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
public int NonRecord(String ProcedureName, params SqlParameter[] Parameters)
{
try
{
if (Database.Connection.State == System.Data.ConnectionState.Closed)
{
Database.Connection.Open();
}
DbCommand dbCmd = Database.Connection.CreateCommand();
dbCmd.CommandText = ProcedureName;
dbCmd.CommandType = System.Data.CommandType.StoredProcedure;
DbParameterCollection dbParams = dbCmd.Parameters;
foreach (SqlParameter parameter in Parameters)
{
dbParams.Add(parameter);
}
return(dbCmd.ExecuteNonQuery());
}
catch (SqlException)
{
}
return(-1);
}
public void AddNonSqlParameter()
{
SqlCommand command = new SqlCommand();
DbParameterCollection collection = command.Parameters;
Assert.Throws <ArgumentNullException>(() => collection.Add(null));
Assert.Throws <InvalidCastException>(() => collection.Add(new NotASqlParameter()));
Assert.Throws <ArgumentNullException>(() => collection.Insert(0, null));
Assert.Throws <InvalidCastException>(() => collection.Insert(0, new NotASqlParameter()));
Assert.Throws <InvalidCastException>(() => collection.AddRange(new object[] { new SqlParameter(), new NotASqlParameter() }));
Assert.Equal(0, collection.Count);
collection.Add(new SqlParameter());
Assert.Throws <ArgumentNullException>(() => collection[0] = null);
Assert.Throws <InvalidCastException>(() => collection[0] = new NotASqlParameter());
}
public static DbParameter AddWithNullableValue(
this DbParameterCollection collection,
string parameterName,
DbType type,
object value)
{
DbParameter parameter =
collection is SqlParameterCollection ?
(DbParameter) new SqlParameter() :
null;
if (parameter == null)
{
throw new InvalidOperationException("Operation not supported for the current parameter collection type.");
}
parameter.ParameterName = parameterName;
parameter.DbType = type;
parameter.Value = value ?? DBNull.Value;
collection.Add(parameter);
return(parameter);
}
public override void BulkCopy(DataTable source, String table, Object transaction)
{
if ((connection == null) || (connection.State == ConnectionState.Closed))
{
throw new Exception("Connection is null");
}
List <String> columns = new List <string>();
List <String> values = new List <string>();
foreach (DataColumn dc in source.Columns)
{
columns.Add("[" + dc.ColumnName + "]");
values.Add("@" + dc.ColumnName);
}
String insertCmd = "INSERT INTO [" + table + "] (" + String.Join(",", columns) + ") VALUES (" + String.Join(",", values) + ")";
columns.Clear();
values.Clear();
foreach (DataRow dr in source.Rows)
{
using (DbParameterCollection par = new DbParameterCollection())
{
foreach (DataColumn dc in source.Columns)
{
par.Add(dc.ColumnName, dc.DataType).Value = dr[dc.ColumnName];
}
ExecuteNonQuery(insertCmd, par);
}
}
}
private static void AddOrRemplaceParameters(DbParameterCollection existingParameters, IEnumerable <DbParameter> newParameters)
{
string prefix = DataParameter.GetPrefixParameter(existingParameters);
// Remove existing parameters found in Values properties
for (int i = existingParameters.Count - 1; i >= 0; i--)
{
string parameterName = existingParameters[i].ParameterName;
if (newParameters.Any(p => String.Compare(p.ParameterName, parameterName, true) == 0 ||
String.Compare($"{prefix}{p.ParameterName}", parameterName, true) == 0))
{
existingParameters.RemoveAt(i);
}
}
// Add parameters found in Values properties
foreach (var param in newParameters)
{
if (!param.ParameterName.EndsWith(prefix))
{
param.ParameterName = $"{prefix}{param.ParameterName}";
}
existingParameters.Add(param);
}
}
public static void Add <T>(this DbParameterCollection parameters, string name, T item)
{
if (item != null)
{
parameters.Add(new SqlParameter(name, item));
}
}
public TParameter AddParameter <TValue>(DbParameterCollection parameters, string parameterName, TValue parameterValue,
DbType?parameterType = null, int?size = null, byte?precision = null, byte?scale = null)
{
var parameter = CreateParameter(parameterName, parameterValue, parameterType, size, precision, scale);
parameters.Add(parameter);
return(parameter);
}
public void AddWithValue(string ParameterName, object value)
{
DbParameter paras = ParameterFactory.GetParameter();
paras.ParameterName = ParameterName;
paras.Value = value;
parameters.Add(paras);
}
private DataTable FetchConstraintData(string tableName)
{
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, CONSTRAINT_TYPE ");
select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ");
select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON ");
select.AppendLine(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME ");
select.AppendLine(" AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ");
select.AppendLine("WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ILIKE :tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
if (!string.IsNullOrEmpty(this.Schema))
{
select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
DataTable constraintData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
return constraintData;
}
/// <summary>
/// Executes stored procedure uspPrintError. Uses given connection.
/// </summary>
/// <param name="conn">ConnectionProvider.</param>
/// <returns>Data retrieved by stored procedure.</returns>
public static StoredProcedureResult UspPrintError(IConnectionProvider conn)
{
DbParameterCollection parameters = new DbParameterCollection();
// Return value is fetched as Varchar but it will be converted to integer.
// This is because some DBMSs also support return types other than integer.
// Setting SqlDbType to int would throw an exception in such cases.
parameters.Add(new DbParameter("ReturnValue", DbType.AnsiString, 1000, ParameterDirection.ReturnValue, true, 0, 0, null, DataRowVersion.Proposed, null));
// Execute stored procedure.
DataSet data = DbUtil.ExecuteMultiQuery(conn, "uspPrintError", parameters, CommandType.StoredProcedure, 30);
// Fetch return value.
object retval = (parameters["ReturnValue"].Value == DBNull.Value) ? null : parameters["ReturnValue"].Value;
return new StoredProcedureResult(data, retval);
}
/// <summary>
/// Renders the parameter. RenderedName property is set after this method is executed.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which DBMS dependant parameter name is appended (eg: '@Id' for SQL Server).</param>
/// <param name="parameters">SQL parameter collection to which this parameter will be added.
/// Another unique name is automatically given to the parameter if another parameter with the
/// same name is already in the collection.</param>
public void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Each parameter in the collection must have a unique name which is ensured by appending
// the parameter index to the end of the name. This yields better performance, O(n) complexity,
// than scanning the collection if a parameter with the same name already exists, O(n2).
int newParIdx = parameters.Count;
IParameterRenderer renderer = DbmsComponentFactory.GetComponent<IParameterRenderer>(dbms);
this.uniqueName = renderer.GetUniqueName(this.originalName, newParIdx);
this.renderedName = renderer.RenderParameterName(this);
parameters.Add(this);
output.Append(this.renderedName);
}
/// <summary>Fetches list with basic table and view info.</summary>
public IEnumerable<SqlObjectHeader> FetchTablesAndViews()
{
DbParameterCollection parameters = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT TABLE_NAME as ObjectName, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 0 WHEN 'VIEW' THEN 1 END AS ObjectType ");
select.AppendLine("FROM INFORMATION_SCHEMA.TABLES ");
select.AppendLine("WHERE TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema') ");
if (!string.IsNullOrEmpty(this.Catalog))
{
select.AppendLine("AND TABLE_CATALOG ILIKE :catalog ");
parameters.Add(new DbParameter("catalog", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
if (!string.IsNullOrEmpty(this.Schema))
{
select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
parameters.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
select.AppendLine("ORDER BY TABLE_TYPE ASC, TABLE_NAME ASC ");
DataTable tablesAndViews = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), parameters, CommandType.Text, null, 30);
List<SqlObjectHeader> list = new List<SqlObjectHeader>();
foreach (DataRow row in tablesAndViews.Rows)
list.Add(new SqlObjectHeader((string)row["ObjectName"], (SqlObjectType)row["ObjectType"]));
return list;
}
private DataTable FetchColumnData(string tableName)
{
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT INFORMATION_SCHEMA.COLUMNS.*, 0 AS IsComputed, CHARACTER_MAXIMUM_LENGTH AS ColumnLength, ");
select.AppendLine("position('nextval(' in column_default) AS IsIdentity, ");
select.AppendLine("0 AS IsRowGuidColumn, 0 AS IsPrimaryKey, 0 AS IsForeignKey, 0 AS HasUniqueConstraint, ");
select.AppendLine("case position('nextval(' in column_default) when 1 then trim(trailing '\"''::regclass)' from trim(leading 'nextval(''\"' from column_default)) end as SequenceName ");
select.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS ");
select.AppendLine("WHERE TABLE_NAME ILIKE :tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
if (!string.IsNullOrEmpty(this.Schema))
{
select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
select.AppendLine("ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ");
DataTable fieldsData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
RemoveExcludedFieldsData(tableName, "COLUMN_NAME", fieldsData);
return fieldsData;
}
private DataTable FetchForeignKeysMeta(string tableName)
{
DbParameterCollection selectFKsParams = new DbParameterCollection();
selectFKsParams.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
DataTable fkData = DbUtil.ExecuteQuery(GetConnectionProvider(), SelectForeignKeys, selectFKsParams, CommandType.Text, null, 30);
foreach (DataRow row in fkData.Rows)
{
// Fetched tada contains trailing speces --> must trim.
row[0] = ((string)row[0]).Trim();
row[1] = ((string)row[1]).Trim();
row[2] = ((string)row[2]).Trim();
row[3] = ((string)row[3]).Trim();
row[4] = ((string)row[4]).Trim();
}
return fkData;
}
private DataTable FetchParentRelations(string tableName)
{
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT foreignKeys.CONSTRAINT_NAME, foreignKeys.COLUMN_NAME AS CHILD_COLUMN, parents.TABLE_NAME AS PARENT_TABLE, primaryKeys.COLUMN_NAME AS PARENT_COLUMN ");
select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as foreignKeys ");
select.AppendLine("INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as relations ON ");
select.AppendLine(" foreignKeys.CONSTRAINT_NAME = relations.CONSTRAINT_NAME ");
select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as parents ON ");
select.AppendLine(" relations.UNIQUE_CONSTRAINT_NAME = parents.CONSTRAINT_NAME ");
select.AppendLine("INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS primaryKeys ON ");
select.AppendLine(" primaryKeys.CONSTRAINT_NAME = parents.CONSTRAINT_NAME ");
select.AppendLine("WHERE foreignKeys.TABLE_NAME ILIKE :tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
select.AppendLine(" AND foreignKeys.ORDINAL_POSITION = primaryKeys.ORDINAL_POSITION ");
if (!string.IsNullOrEmpty(this.Schema))
{
select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
DataTable parentData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
RemoveExcludedFieldsData(tableName, "COLUMN_NAME", parentData);
return parentData;
}
private static DbParameterCollection CreateParameters(IValueSet values)
{
if (values == null)
return null;
DbParameterCollection collection = new DbParameterCollection();
foreach (string key in values.Keys)
{
collection.Add(new DbParameterDescriptor(
BuildParameterName(key), values[key]));
}
return collection;
}
