///// <summary>
///// 根据EntityMapSql的全名称 "名称空间名字.SQL名字" 获取映射的SQL语句
///// </summary>
///// <param name="fullName">EntityMapSql的全名称，格式： "名称空间名字.SQL名字"</param>
///// <returns>映射的SQL语句</returns>
//public static string GetMapSql(string fullName)
//{
//}
/// <summary>
/// 执行返回单值的查询，通常用于OQL的Count,Max等查询
/// </summary>
/// <param name="oql">查询表达式</param>
/// <param name="db">数据访问对象</param>
/// <returns>单值</returns>
public static object ExecuteScalar(OQL oql, AdoHelper db)
{
if (oql.Parameters != null && oql.Parameters.Count > 0)
{
IDataParameter[] paras = GetParameters(oql.Parameters, db);
return(db.ExecuteScalar(oql.ToString(), CommandType.Text, paras));
}
else
{
return(db.ExecuteScalar(oql.ToString()));
}
}
/// <summary>
/// 执行OQL查询，统计该查询对应的记录数量（一般用于分页前使用，某些方法会自动调用该方法，请注意方法说明）
/// </summary>
/// <param name="oql"></param>
/// <param name="db"></param>
/// <returns></returns>
public static object ExecuteOQLCount(OQL oql, AdoHelper db)
{
string sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, oql.ToString(), "", oql.PageSize, oql.PageNumber, 0);
if (oql.Parameters != null && oql.Parameters.Count > 0)
{
IDataParameter[] paras = GetParameters(oql.Parameters, db);
return(db.ExecuteScalar(sql, CommandType.Text, paras));
}
else
{
return(db.ExecuteScalar(sql));
}
}
/// <summary>
/// Gets a count of rows in the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
public int GetCountByUser(Guid userGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = userGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_userlocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("userguid = :userguid ");
sqlCommand.Append(";");
//return Convert.ToInt32(AdoHelper.ExecuteScalar(
// readConnectionString,
// CommandType.StoredProcedure,
// "mp_userlocation_countbyuser(:userguid)",
// arParams));
object obj = AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(Convert.ToInt32(obj));
}
public int Save <T>(T entity)
{
object val = 0;
try
{
TableInfo tableInfo = EntityHelper.GetTableInfo(entity, DbOperateType.INSERT);
String strSql = EntityHelper.GetInsertSql(tableInfo);
strSql += EntityHelper.GetAutoSql();
IDbDataParameter[] parms = tableInfo.GetParameters();
if (transaction != null)
{
val = AdoHelper.ExecuteScalar(transaction, CommandType.Text, strSql, parms);
}
else
{
val = AdoHelper.ExecuteScalar(AdoHelper.ConnectionString, CommandType.Text, strSql, parms);
}
if (Convert.ToInt32(val) > 0 && (AdoHelper.DbType == DatabaseType.MYSQL || AdoHelper.DbType == DatabaseType.SQLSERVER))
{
PropertyInfo propertyInfo = EntityHelper.GetPrimaryKeyPropertyInfo(entity);
ReflectionHelper.SetPropertyValue(entity, propertyInfo, val);
}
}
catch (Exception e)
{
throw e;
}
return(Convert.ToInt32(val));
}
/// <summary>
/// Inserts a row in the mp_BannedIPAddresses table. Returns new integer id.
/// </summary>
/// <param name="bannedIP"> bannedIP </param>
/// <param name="bannedUTC"> bannedUTC </param>
/// <param name="bannedReason"> bannedReason </param>
/// <returns>int</returns>
public int Add(
string bannedIP,
DateTime bannedUtc,
string bannedReason)
{
#region Bit Conversion
#endregion
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter(":BannedIP", FbDbType.VarChar, 50);
arParams[0].Value = bannedIP;
arParams[1] = new FbParameter(":BannedUTC", FbDbType.TimeStamp);
arParams[1].Value = bannedUtc;
arParams[2] = new FbParameter(":BannedReason", FbDbType.VarChar, 255);
arParams[2].Value = bannedReason;
int newID = Convert.ToInt32(AdoHelper.ExecuteScalar(
writeConnectionString,
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_BANNEDIPADDRESSES_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
public int Count(string strSQL)
{
int count = 0;
IDbConnection connection = null;
bool closeConnection = GetWillConnectionState();
try
{
connection = GetConnection();
count = Convert.ToInt32(AdoHelper.ExecuteScalar(connection, CommandType.Text, strSQL));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
return(count);
}
private int GetCount(
int siteID,
string keyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteSettingsEx ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID AND ");
sqlCommand.Append("KeyName = @KeyName ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteID;
arParams[1] = new FbParameter("@KeyName", FbDbType.VarChar, 128);
arParams[1].Value = keyName;
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// returns true if the record exists
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public bool Exists(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND OldUrl = @OldUrl ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
arParams[1].Value = oldUrl;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
private bool Exists(int siteId, string keyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteSettingsEx ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND ");
sqlCommand.Append("KeyName = @KeyName ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@KeyName", SqlDbType.NVarChar, 128);
arParams[1].Value = keyName;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public int GetCountOfSiteRoles(int siteId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = @SiteID ");
if (!string.IsNullOrEmpty(searchInput))
{
sqlCommand.Append("AND (");
sqlCommand.Append("([DisplayName] LIKE '%' + @SearchInput + '%') ");
sqlCommand.Append("OR ([RoleName] LIKE '%' + @SearchInput + '%') ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@SearchInput", SqlDbType.NVarChar, 50);
arParams[1].Value = searchInput;
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// returns true if the record exists
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public bool Exists(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_redirectlist ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid ");
sqlCommand.Append("AND oldUrl = :oldurl ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("oldurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Value = oldUrl;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
private int GetCount(
int siteID,
string keyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_sitesettingsex ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid AND ");
sqlCommand.Append("keyname = :keyname ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = siteID;
arParams[1] = new NpgsqlParameter("keyname", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[1].Value = keyName;
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams)));
}
public int GetCountOfSiteRoles(int siteId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("COUNT(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = :SiteID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (DisplayName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (RoleName LIKE :SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[2];
arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SQLiteParameter(":SearchInput", DbType.String);
arParams[1].Value = "%" + searchInput + "%";
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// returns true if the record exists
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public bool Exists(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = :SiteID ");
sqlCommand.Append("AND OldUrl = :OldUrl ");
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[2];
arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SQLiteParameter(":OldUrl", DbType.String);
arParams[1].Value = oldUrl;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public int FindCount <T>(string propertyName, object propertyValue) where T : new()
{
int count = 0;
try
{
PropertyInfo[] properties = ReflectionHelper.GetProperties(new T().GetType());
TableInfo tableInfo = EntityHelper.GetTableInfo(new T(), DbOperateType.COUNT, properties);
string strSql = EntityHelper.GetFindCountSql(tableInfo);
strSql += string.Format(" WHERE {0} = @{1}", propertyName, propertyName);
ColumnInfo columnInfo = new ColumnInfo();
columnInfo.Add(propertyName, propertyValue);
IDbDataParameter[] parameters = DbFactory.CreateDbParameters(1);
EntityHelper.SetParameters(columnInfo, parameters);
count = Convert.ToInt32(AdoHelper.ExecuteScalar(AdoHelper.ConnectionString, CommandType.Text, strSql, parameters));
}
catch (Exception ex)
{
throw ex;
}
return(count);
}
public bool Exists(int siteId, string roleName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = @SiteID ");
sqlCommand.Append("AND (RoleName = @RoleName OR DisplayName = @RoleName) ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@RoleName", SqlDbType.NVarChar, 50);
arParams[1].Value = roleName;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
private int GetCount(
int siteID,
string keyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteSettingsEx ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = :SiteID AND ");
sqlCommand.Append("KeyName = :KeyName ");
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[2];
arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteID;
arParams[1] = new SQLiteParameter(":KeyName", DbType.String);
arParams[1].Value = keyName;
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_TaskQueue table.
/// </summary>
public int GetCountUnfinished()
{
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.StoredProcedure,
"mp_taskqueue_countunfinished()",
null)));
}
/// <summary>
/// Gets a count of rows in the mp_TaskQueue table.
/// </summary>
public int GetCount()
{
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.StoredProcedure,
"mp_TaskQueue_GetCount",
null)));
}
/// <summary>
/// Gets a count of rows in the mp_BannedIPAddresses table.
/// </summary>
public int GetCount()
{
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.StoredProcedure,
"mp_bannedipaddresses_count()",
null)));
}
public bool Exists(string folderName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteFolders ");
sqlCommand.Append("WHERE FolderName = :FolderName ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":FolderName", DbType.String);
arParams[0].Value = folderName;
int count = Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Gets a count of rows in the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
public int GetCountByUser(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("UserGuid = ?UserGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[0].Value = userGuid.ToString();
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
arParams)));
}
///// <summary>
///// 根据EntityMapSql的全名称 "名称空间名字.SQL名字" 获取映射的SQL语句
///// </summary>
///// <param name="fullName">EntityMapSql的全名称，格式： "名称空间名字.SQL名字"</param>
///// <returns>映射的SQL语句</returns>
//public static string GetMapSql(string fullName)
//{
//}
/// <summary>
/// 执行返回单值的查询，通常用于OQL的Count,Max等查询
/// </summary>
/// <param name="oql">查询表达式</param>
/// <param name="db">数据访问对象</param>
/// <returns>单值</returns>
public static object ExecuteScalar(OQL oql, AdoHelper db)
{
if (oql.Parameters != null && oql.Parameters.Count > 0)
{
int fieldCount = oql.Parameters.Count;
IDataParameter[] paras = new IDataParameter[fieldCount];
int index = 0;
foreach (string name in oql.Parameters.Keys)
{
paras[index] = db.GetParameter(name, oql.Parameters[name]);
index++;
}
return(db.ExecuteScalar(oql.ToString(), CommandType.Text, paras));
}
else
{
return(db.ExecuteScalar(oql.ToString()));
}
}
/// <summary>
/// Gets a count of rows in the mp_BannedIPAddresses table.
/// </summary>
public int GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_BannedIPAddresses ;");
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
null)));
}
/// <summary>
/// Gets a count of rows in the mp_UserLocation table.
/// </summary>
/// <param name="userGuid"> userGuid </param>
public int GetCountByUser(Guid userGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = userGuid.ToString();
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.StoredProcedure,
"mp_userlocation_countbyuser(:userguid)",
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_SystemLog table.
/// </summary>
public int GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SystemLog ");
sqlCommand.Append(";");
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
null)));
}
/// <summary>
/// Gets a count of rows in the mp_TaskQueue table.
/// </summary>
public int GetCountUnfinished(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
CommandType.StoredProcedure,
"mp_taskqueue_countunfinishedbysite(:siteguid)",
arParams)));
}
public int GetCountOfUsersInRole(int siteId, int roleId, string searchInput)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("COUNT(*) ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = :RoleID ");
if (searchInput.Length > 0)
{
sqlCommand.Append(" AND ");
sqlCommand.Append("(");
sqlCommand.Append(" (u.Name LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LoginName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.Email LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.LastName LIKE :SearchInput) ");
sqlCommand.Append(" OR ");
sqlCommand.Append(" (u.FirstName LIKE :SearchInput) ");
sqlCommand.Append(")");
}
sqlCommand.Append("WHERE u.SiteID = :SiteID ");
sqlCommand.Append(";");
SQLiteParameter[] arParams = new SQLiteParameter[3];
arParams[0] = new SQLiteParameter(":SiteID", DbType.Int32);
arParams[0].Value = siteId;
arParams[1] = new SQLiteParameter(":RoleID", DbType.Int32);
arParams[1].Value = roleId;
arParams[2] = new SQLiteParameter(":SearchInput", DbType.String);
arParams[2].Value = "%" + searchInput + "%";
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
connectionString,
sqlCommand.ToString(),
arParams)));
}
public int NewId(string Atable, string Aidname)
{
string sql;
sql = String.Format("select MAX({0}) from tdbadmin.{1}", Aidname, Atable);
int NewId = (int)_dbhelper.ExecuteScalar(_dbcon, CommandType.Text, sql);
if (NewId < 0)
{
NewId = 0;
}
NewId++;
return(NewId);
}
public int NewID(string Atable, string Aidname)
{
string sql;
sql = String.Format("select MAX({0}) from tdbadmin.{1}", Aidname, Atable);
int newid = (int)dbhelper.ExecuteScalar(dbcon, CommandType.Text, sql);
if (newid < 0)
{
newid = 0;
}
newid++;
return(newid);
}
