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