| public static ExecuteScalar ( string sql ) : object | ||
| sql | string | |
| return | object | |
public static int GetCountUnfinishedByType(string taskType)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SerializedTaskType LIKE ?TaskType ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?TaskType", MySqlDbType.VarChar, 266);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = taskType + "%";
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static bool Exists(string folderName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteFolders ");
sqlCommand.Append("WHERE FolderName = ?FolderName ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?FolderName", MySqlDbType.VarChar, 255);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = folderName;
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Gets a count of rows in the sts_MembershipProduct table.
/// </summary>
public static int GetCount(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_MembershipProduct ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetCountOfStateAllUsers(Guid pathId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SitePersonalizationAllUsers ");
sqlCommand.Append("WHERE PathID = ?PathID ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?PathID", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathId.ToString();
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
/// <summary>
/// Gets a count of responses in a survey.
/// </summary>
public static int GetResponseCount(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = ?SurveyGuid ");
sqlCommand.Append("And Complete = 1; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SurveyGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_ContentHistory table.
/// </summary>
public static int GetCount(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContentHistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ContentGuid = ?ContentGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ContentGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetCount(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM sts_FundRaiserUpdates ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = ?ModuleGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets the total number of rows contained in the corresponding MySQL object.
/// </summary>
/// <returns>The number of rows in a given table or view.</returns>
public long GetRowsCount()
{
if (Connection == null)
{
return(0);
}
object objCount = null;
try
{
var sql = $"SELECT COUNT(*) FROM `{Connection.Schema}`.`{Name}`";
objCount = MySqlHelper.ExecuteScalar(Connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
Logger.LogException(ex, true, string.Format(Resources.UnableToRetrieveData, this is DbTable ? "table" : "view", Name));
}
return((long?)objCount ?? 0);
}
public static int GetCountOfSiteRoles(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = ?SiteID ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
static void query_work_station_id()
{
string computer_name = common.GetComputerName();
string user_name = common.GetUserName();
string user_domain_name = common.GetUserDomainName();
string query = @"
select id from tbl_work_station
where w_station = @station
and w_user_name = @user_name;";
var ret = MySqlHelper.ExecuteScalar(MySqlHelper.ConnectionString, query,
new MySql.Data.MySqlClient.MySqlParameter("@station", computer_name),
new MySql.Data.MySqlClient.MySqlParameter("@user_name", user_name));
if (ret != null)
{// 同一台机器， 同一个帐号登录， 则返回id号
Login.Operator["work_station_id"] = ret;
return;
}
// 插入新登录的帐号， 并查询返回id号
string insert = @"
insert into tbl_work_station(`w_station`, `w_user_name`, `w_user_domain_name`, `w_create_time`)
VALUES (@station, @user_name, @user_domain_name, @create_time);";
ret = MySqlHelper.ExecuteNonQuery(MySqlHelper.ConnectionString, insert,
new MySql.Data.MySqlClient.MySqlParameter("@station", computer_name),
new MySql.Data.MySqlClient.MySqlParameter("@user_name", user_name),
new MySql.Data.MySqlClient.MySqlParameter("@user_domain_name", user_domain_name),
new MySql.Data.MySqlClient.MySqlParameter("@create_time", DateTime.Now)
);
ret = MySqlHelper.ExecuteScalar(MySqlHelper.ConnectionString, query,
new MySql.Data.MySqlClient.MySqlParameter("@station", computer_name),
new MySql.Data.MySqlClient.MySqlParameter("@user_name", user_name));
if (ret != null)
{
Login.Operator["work_station_id"] = ret;
}
}
public static int GetCount(bool tracking1ProfileOnly, Guid siteGuid, string profileId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT COALESCE(Country, '') As Country, Count(*) As TheCount ");
sqlCommand.Append("FROM sts_ga_CountryData ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("COALESCE(Country,'') ");
sqlCommand.Append(") s ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int ALTER(string tableName, string key, AlterType alterType, DataType dataType = DataType.INT, string key2 = "", bool isFirst = false)
{
string dataTypeStr = "";
string afterStr = "";
switch (alterType)
{
case AlterType.DROP:
break;
case AlterType.ADD:
dataTypeStr = GetDataTypeCmd(dataType);
if (!string.IsNullOrEmpty(key2))
{
afterStr = isFirst ? "FIRST" : "AFTER " + key2;
}
break;
case AlterType.MODIFY:
dataTypeStr = GetDataTypeCmd(dataType);
break;
case AlterType.CHANGE:
dataTypeStr = GetDataTypeCmd(dataType);
key = key + " " + key2;
break;
case AlterType.RENAME:
break;
default:
break;
}
string cmd = string.Format("ALTER TABLE {0} {1} {2} {3} {4}", tableName, GetAlterTypeCmd(alterType), key, dataTypeStr, afterStr);
object obj = MySqlHelper.ExecuteScalar(MySqlHelper.Conn, CommandType.Text, cmd, null);
return(Convert.ToInt32(obj));
}
/// <summary>
/// 获取首页推荐的分组Id
/// </summary>
/// <returns></returns>
public int HomePageRecommendGetGroupId(int GroupTypeID, int SchemeID)
{
StringBuilder commandText = new StringBuilder();
#region CommandText
commandText.Append(@"SELECT
a.GroupID
FROM
GroupInfo AS a
INNER JOIN GroupSchemes AS b
ON a.GroupID = b.GroupID
WHERE a.GroupTypeID = @GroupTypeID
AND a.Status = 1
AND b.Status = 1
AND b.SchemeID = @SchemeID
LIMIT 1");
#endregion
return(nwbase_sdk.Tools.GetInt(MySqlHelper.ExecuteScalar(this.ConnectionString, commandText.ToString(), new MySqlParameter("@GroupTypeID", GroupTypeID), new MySqlParameter("@SchemeID", SchemeID)), 0));
}
/// <summary>
/// 插入话题，返回话题Id
/// </summary>
/// <param name="topic"></param>
/// <returns></returns>
public static int AddTopic(T_Topic topic)
{
object o = MySqlHelper.ExecuteScalar(Util.GetConnectString(BBSConnString),
"insert into T_Topic(Id, UserId, Invites, Title, TopicContent, TopicText, CreateDate, Good, Hit, ReplyCount, GradeId, SubjectId, Deleted, Blocked, Ip) values (null, @UserId, @Invites, @Title, @TopicContent, @TopicText, @CreateDate, @Good, @Hit, @ReplyCount, @GradeId, @SubjectId, @Deleted, @Blocked, @Ip); select last_insert_id();",
"@UserId".ToInt32InPara(topic.UserId),
"@Invites".ToVarCharInPara(topic.Invites),
"@Title".ToVarCharInPara(topic.Title),
"@TopicContent".ToVarCharInPara(topic.TopicContent),
"@TopicText".ToVarCharInPara(topic.TopicText),
"@CreateDate".ToDateTimeInPara(topic.CreateDate),
"@Good".ToInt32InPara(topic.Good),
"@Hit".ToInt32InPara(topic.Hit),
"@ReplyCount".ToInt32InPara(topic.ReplyCount),
"@GradeId".ToInt32InPara(topic.GradeId),
"@SubjectId".ToInt32InPara(topic.SubjectId),
"@Deleted".ToBitInPara(topic.Deleted),
"@Blocked".ToBitInPara(topic.Blocked),
"@Ip".ToVarCharInPara(topic.Ip)
);
return(o == null ? 0 : int.Parse(o.ToString()));
}
/// <summary>
/// Gets a count of rows in the mp_Letter table.
/// </summary>
public static int GetCountOfDrafts(Guid letterInfoGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Letter ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LetterInfoGuid = ?LetterInfoGuid ");
sqlCommand.Append("AND SendClickedUTC IS NULL ");
sqlCommand.Append("; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?LetterInfoGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// 新建作业
/// </summary>
/// <param name="zy"></param>
/// <returns></returns>
public static int Create(T_Zy zy)
{
object o = MySqlHelper.ExecuteScalar(Util.GetConnectString(ZyConnString),
"insert into T_Zy(UserId, ZyName, CourseId, SubjectId, CreateDate, Ip, IMEI, MobileBrand, SystemType, Browser, OpenDate, DueDate, Type, Status) values (@UserId, @ZyName, @CourseId, @SubjectId, @CreateDate, @Ip, @IMEI, @MobileBrand, @SystemType, @Browser, @OpenDate, @DueDate, @Type, @Status); select last_insert_id();",
"@UserId".ToInt32InPara(zy.UserId),
"@ZyName".ToVarCharInPara(zy.ZyName),
"@CourseId".ToInt32InPara(zy.CourseId),
"@SubjectId".ToInt32InPara(zy.SubjectId),
"@CreateDate".ToDateTimeInPara(zy.CreateDate),
"@Ip".ToVarCharInPara(zy.Ip),
"@IMEI".ToVarCharInPara(zy.IMEI),
"@MobileBrand".ToVarCharInPara(zy.MobileBrand),
"@SystemType".ToVarCharInPara(zy.SystemType),
"@Browser".ToVarCharInPara(zy.Browser),
"@OpenDate".ToDateTimeInPara(zy.OpenDate),
"@DueDate".ToDateTimeInPara(zy.DueDate),
"@Type".ToInt32InPara(zy.Type),
"@Status".ToInt32InPara(zy.Status)
);
return(o == null ? 0 : int.Parse(o.ToString()));
}
protected void Vote_Click(object sender, EventArgs e)
{
try
{
int Id = Convert.ToInt32((sender as LinkButton).CommandArgument);
//string sql = string.Format("update WorksList set VoteNumber=VoteNumber+1 where ID={0} ", Id);
UserInfo user = (UserInfo)Session["User"];
string sql2 = string.Format("select count(1) from VoteRecord where WorksListId = {0} and UserId = {1}", Id, user.Id);
int count = (int)MySqlHelper.ExecuteScalar(sql2);
string sql = "";
if (count > 0)
{
//取消投票
sql = string.Format("update WorksList set VoteNumber=VoteNumber-1 where ID={0} ", Id);
string delsql = string.Format("delete from VoteRecord where WorksListId = {0} and UserId = {1}", Id, user.Id);
MySqlHelper.ExecuteNonQuery(sql);
MySqlHelper.ExecuteNonQuery(delsql);//减投票记录
Response.Write("<script>alert('取消投票成功！');window.location.replace('Pictures.aspx');</script>");
}
else
{
//投票
sql = string.Format("update WorksList set VoteNumber=VoteNumber+1 where ID={0} ", Id);
string updatesql = string.Format("insert VoteRecord values({0},{1},'{2}')", Id, user.Id, DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
MySqlHelper.ExecuteNonQuery(sql);
MySqlHelper.ExecuteNonQuery(updatesql);//加投票记录
Response.Write("<script>alert('投票成功！');window.location.replace('Pictures.aspx');</script>");
}
}
catch (Exception ex)
{
Response.Write("<script>alert('投票失败！');</script>");
}
}
/// <summary>
/// Returns true if the passed in address is banned
/// </summary>
/// <param name="rowID"> rowID </param>
/// <returns>bool</returns>
public static bool IsBanned(string ipAddress)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_BannedIPAddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("BannedIP = ?BannedIP ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?BannedIP", MySqlDbType.VarChar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = ipAddress;
int foundRows = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
return(foundRows > 0);
}
/// <summary>
/// Gets a count of rows in the mp_TaskQueue table.
/// </summary>
/// <param name="siteGuid"> guid </param>
public static int GetCountUnfinished(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND CompleteUTC IS NULL ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// 五、需要的协助和支持
/// </summary>
public static List <AssistInfo> GetAssistInfoList(string whereCondition, string limit, MySqlParameter[] parameters, out int totalCount, out int totalPage)
{
List <AssistInfo> assistInfoList = new List <AssistInfo>();
MySqlConnection con = new MySqlConnection(ConnectionString);
totalCount = 0;
try
{
con.Open();
totalCount = int.Parse(MySqlHelper.ExecuteScalar(con, "select count(*) from Assist_Info " + whereCondition, parameters).ToString());
MySqlDataReader reader = MySqlHelper.ExecuteReader(con, "select * from Assist_Info " + whereCondition + limit, parameters);
while (reader.Read())
{
AssistInfo assistInfo = new AssistInfo();
assistInfo.Id = int.Parse(reader["Id"].ToString());
assistInfo.Content = reader["Content"].ToString();
assistInfo.UserId = reader["UserId"].ToString();
assistInfo.Week = int.Parse(reader["Week"].ToString());
assistInfo.IsForeign = int.Parse(reader["IsForeign"].ToString());
assistInfo.OrderNum = int.Parse(reader["OrderNum"].ToString());
assistInfoList.Add(assistInfo);
}
reader.Close();
}
catch (MySqlException e)
{
Logger.Error("查询AssistInfo出错。", e);
}
finally
{
con.Close();
}
int pageSize = int.Parse(ConfigurationManager.AppSettings["pageSize"]);
totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
return(assistInfoList);
}
/// <summary>
/// Gets the total number of rows contained in the corresponding MySQL object.
/// </summary>
/// <returns>The number of rows in a given table or view.</returns>
public long GetRowsCount()
{
if (Connection == null)
{
return(0);
}
object objCount = null;
try
{
string sql = string.Format("SELECT COUNT(*) FROM `{0}`.`{1}`", Connection.Schema, Name);
objCount = MySqlHelper.ExecuteScalar(Connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, this is DbTable ? "table" : "view", Name), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(objCount != null ? (long)objCount : 0);
}
private int endNum = 6; //在mysql中，第二个数字代表取几个数字
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
string num = context.Request["pageNum"];
if (!string.IsNullOrEmpty(num)) //判断是否有传值
{
if (int.TryParse(num, out pageNum) == true) //判断传值是否是数字
{
pageNum = Convert.ToInt32(num); //页面设为传过来的值
startNum = setStartNum(pageNum);
}
}
// DataTable products = MySqlHelper.ExecuteDataTable(@"select p.Id as Id,p.Name as Name,c.Name as CategoryName from T_Product p
// left join T_ProductCategories c on p.CategoriyId=c.Id");
DataTable products = MySqlHelper.ExecuteDataTable(@"select p.Id as Id,p.Name as Name,
c.Name as CategoryName from T_Product p left join T_ProductCategories c on
p.CategoriyId=c.Id order by Id limit @StartNum,@EndNum",
new MySqlParameter("@StartNum", startNum), new MySqlParameter("@EndNum", endNum));
//不能一次把表中的数据都取出来，否则性能很差
//也不能一次性取出来，用c#过滤
//总条数
long totalCount = (long)MySqlHelper.ExecuteScalar("select count(*) from T_Product");
//总页数
int pageCount = (int)Math.Ceiling(totalCount / endNum * 1.0);
object[] pageData = new object[pageCount];
for (int i = 0; i < pageCount; i++)
{
pageData[i] = new { Href = "ProductList.ashx?pageNum=" + (i + 1),
Title = "第" + (i + 1) + "页" };
}
var data = new { Title = "产品列表", Products = products.Rows, PageData = pageData };
string html = CommonHelper.RenderHtml("Admin/ProductList.html", data);
context.Response.Write(html);
}
/// <summary>
/// 添加系统角色
/// added by kezesong 2014-4-16
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public int AddRightRoles(RoleInfo info)
{
string sqlText = @"insert into RightRoles(TheProj,RoleName,RoleDesc,Status) values(@TheProj,@RoleName,@RoleDesc,@Status);select last_insert_id();";
List <MySqlParameter> paramList = new List <MySqlParameter>();
paramList.Add(new MySqlParameter("@TheProj", info.TheProj));
paramList.Add(new MySqlParameter("@RoleName", info.RoleName));
paramList.Add(new MySqlParameter("@RoleDesc", info.RoleDesc));
paramList.Add(new MySqlParameter("@Status", info.Status));
object ret = MySqlHelper.ExecuteScalar(_connStr, sqlText, paramList.ToArray());
int id = Tools.GetInt(ret, 0);
if (id > 0)
{
nwbase_utils.Cache.CacheHelper.DelCache("RightRolesList", true);
nwbase_utils.Cache.CacheHelper.DelCache("rightRightsDic", true); //同时删除用户权限缓存
return(id);
}
return(0);
}
/// <summary>
/// 查询公司数量
/// </summary>
/// <param name="companySearcher">公司查询对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回查询到的数量</returns>
public long Count(CompanySearcher companySearcher, ICTransaction tran)
{
object count = 0;
long result = 0;
CompanySearcher querySearcher = null;
MysqlQueryParser queryParser = new MysqlQueryParser();
StringBuilder query = new StringBuilder();
query.AppendLine(@"SELECT ");
query.AppendLine(@" COUNT(C.CompanyId) ");
query.AppendLine(@"FROM ");
query.AppendLine(@" Company C ");
if (companySearcher != null)
{
querySearcher = (CompanySearcher)companySearcher.Clone();
querySearcher.TableName = "C";
}
queryParser.SearcherParse(querySearcher);
if (!string.IsNullOrEmpty(queryParser.ConditionString))
{
query.AppendLine(@"WHERE ");
query.AppendLine(@" " + queryParser.ConditionString);
}
if (tran != null)
{
count = MySqlHelper.ExecuteScalar((MySqlConnection)tran.Connection, query.ToString(), queryParser.ParamCollection.ToArray());
}
else
{
count = MySqlHelper.ExecuteScalar(this.CurrentConnectionString, query.ToString(), queryParser.ParamCollection.ToArray());
}
return(long.TryParse(count.ToString(), out result) ? result : 0);
}
public void ChangeAppName()
{
provider = new MySQLMembershipProvider();
NameValueCollection config = new NameValueCollection();
config.Add("connectionStringName", "LocalMySqlServer");
config.Add("applicationName", "/");
config.Add("passwordStrengthRegularExpression", "bar.*");
config.Add("passwordFormat", "Clear");
provider.Initialize(null, config);
MembershipCreateStatus status;
provider.CreateUser("foo", "bar!bar", null, null, null, true, null, out status);
Assert.True(status == MembershipCreateStatus.Success);
MySQLMembershipProvider provider2 = new MySQLMembershipProvider();
NameValueCollection config2 = new NameValueCollection();
config2.Add("connectionStringName", "LocalMySqlServer");
config2.Add("applicationName", "/myapp");
config2.Add("passwordStrengthRegularExpression", "foo.*");
config2.Add("passwordFormat", "Clear");
provider2.Initialize(null, config2);
provider2.CreateUser("foo2", "foo!foo", null, null, null, true, null, out status);
Assert.True(status == MembershipCreateStatus.Success);
provider.ApplicationName = "/myapp";
Assert.False(provider.ValidateUser("foo", "bar!bar"));
Assert.True(provider.ValidateUser("foo2", "foo!foo"));
//Cleanup
//provider.DeleteUser("foo2", true);
//provider.DeleteUser("foo", true);
//Cleanup
MySqlHelper.ExecuteScalar(Connection, "DELETE FROM my_aspnet_users");
MySqlHelper.ExecuteScalar(Connection, "DELETE FROM my_aspnet_membership");
}
private void AddTable(object parameter)
{
if (string.IsNullOrEmpty(TableName + DateField))
{
MessageBoxHelper.MessageBoxShowWarning("表名称和时间字段都为必填项！");
return;
}
ArchiveTable archiveTable = ArchiveTables.FirstOrDefault(x => x.TableName.ToLower() == TableName.ToLower());
if (archiveTable != null)
{
MessageBoxHelper.MessageBoxShowWarning("添加的表名称已存在！");
return;
}
try
{
MySqlHelper.ExecuteScalar(EnvironmentInfo.ConnectionString, $"select {DateField} from {TableName} limit 1");
}
catch (Exception)
{
MessageBoxHelper.MessageBoxShowWarning("添加的表名称或者时间字段在数据库中不存在！");
return;
}
var table = new ArchiveTable()
{
TableName = TableName, DateField = DateField, Where = Where
};
archiveTableManager.AddArchiveTable(table);
ArchiveTables.Add(table);
this.TableName = "";
this.DateField = "";
this.Where = "";
}
/// <summary>
/// Gets the guid of the next page in the survey
/// </summary>
/// <param name="pageGuid">The guid of the current page</param>
/// <returns></returns>
public static Guid GetNextPageGuid(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE PageOrder > (");
sqlCommand.Append("SELECT PageOrder ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid = ?PageGuid) ");
sqlCommand.Append("AND ");
sqlCommand.Append("SurveyGuid = (");
sqlCommand.Append("SELECT SurveyGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid = ?PageGuid) ");
sqlCommand.Append("Order By PageOrder ");
sqlCommand.Append("Limit 1; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?PageGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
object id = MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
public static byte[] GetPersonalizationBlobAllUsers(
int siteId,
String path)
{
Guid pathID = GetOrCreatePathId(siteId, path);
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT PageSettings ");
sqlCommand.Append("FROM mp_SitePersonalizationAllUsers ");
sqlCommand.Append("WHERE PathID = ?PathID LIMIT 1 ; ");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?PathID", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathID.ToString();
byte[] result = null;
try
{
result = (byte[])MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams);
}
catch (System.InvalidCastException ex)
{
if (log.IsErrorEnabled)
{
log.Error("dbPortal.SitePersonalization_GetPersonalizationBlobAllUsers", ex);
}
}
return(result);
}
public void ScopeTimeoutWithMySqlHelper()
{
execSQL("DROP TABLE IF EXISTS Test");
createTable("CREATE TABLE Test (id int)", "INNODB");
string connStr = GetConnectionString(true);
using (new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.FromSeconds(1)))
{
try
{
for (int i = 0; ; i++)
{
MySqlHelper.ExecuteNonQuery(connStr, String.Format("INSERT INTO Test VALUES({0})", i));;
}
}
catch (Exception)
{
}
}
long count = (long)MySqlHelper.ExecuteScalar(connStr, "select count(*) from test");
Assert.AreEqual(0, count);
}
public string GetEmailByCustomerNumber(string num)
{
string output = "";
try
{
output = (String)MySqlHelper.ExecuteScalar(_connectionString, "select email from CustomerLogin where customerNumber = " + num);
/*using (MySqlConnection connection = new MySqlConnection(_connectionString))
* {
* string sql = "select email from CustomerLogin where customerNumber = " + num;
* MySqlCommand cmd = new MySqlCommand(sql, connection);
* output = (string)cmd.ExecuteScalar();
* }*/
}
catch (Exception ex)
{
log.Error("Error getting email by customer number", ex);
output = ex.Message;
}
return(output);
}
