private void Form2_Load(object sender, EventArgs e)
{
//RewriteTextBox.SetControlRoundRectRgn(textBox1, 30);
//RewriteTextBox.SetControlRoundRectRgn(textBox2, 70);
//RewriteTextBox.SetControlRoundRectRgn(btnLogin, 10);
RewriteTextBox.SetFormRoundRectRgn(this, 10);
string tmpSQL = "";
tmpSQL = "select * from UsersManage";
DataSet ds = new DataSet();
ds = MyDBHelper.GetMyDBData(tmpSQL, "UsersManage");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
comboBoxUser.Items.Add(ds.Tables[0].Rows[i]["LoginUsers"].ToString());
}
if (comboBoxUser.Items.Count > 0)
{
comboBoxUser.SelectedIndex = 0;
}
if (comboBoxUser.CanFocus)
{
comboBoxUser.Focus();
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(UserConfiguration userConfiguration)
{
string sql =
"UPDATE tblUserConfiguration " +
"SET " +
" UserInfoID = @UserInfoID"
+ ", UserConfigurationKey = @UserConfigurationKey"
+ ", UserConfigurationValue = @UserConfigurationValue"
+ ", Description = @Description"
+ ", UserConfigurationNo = @UserConfigurationNo"
+ " WHERE UserConfigurationID = @UserConfigurationID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserConfigurationID", userConfiguration.UserConfigurationID)
, new MySqlParameter("@UserInfoID", ToDBValue(userConfiguration.UserInfoID))
, new MySqlParameter("@UserConfigurationKey", ToDBValue(userConfiguration.UserConfigurationKey))
, new MySqlParameter("@UserConfigurationValue", ToDBValue(userConfiguration.UserConfigurationValue))
, new MySqlParameter("@Description", ToDBValue(userConfiguration.Description))
, new MySqlParameter("@UserConfigurationNo", ToDBValue(userConfiguration.UserConfigurationNo))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 批量插入Model
/// </summary>
public bool BatchInsert(List <UserWebFolder> lstUserWebFolder)
{
StringBuilder sbSQL = new StringBuilder();
int index = 0;
List <MySqlParameter> lstPara = new List <MySqlParameter>();
foreach (var userWebFolder in lstUserWebFolder)
{
sbSQL.AppendLine(string.Format(@"INSERT INTO tblUserWebFolder(UserWebFolderID, WebFolderName, UserInfoID, CreateTime, Visible, ParentWebfolderID, IntroContent,IElementJSON,IElementHashcode) VALUES(
@UserWebFolderID{0}, @WebFolderName{1}, @UserInfoID{2}, @CreateTime{3}, @Visible{4}, @ParentWebfolderID{5}, @IntroContent{6},@IElementJSON{7},@IElementHashcode{8});",
index, index, index, index, index, index, index, index, index));
MySqlParameter[] paramater = new MySqlParameter[]
{
new MySqlParameter(string.Format("@UserWebFolderID{0}", index), ToDBValue(userWebFolder.UserWebFolderID)),
new MySqlParameter(string.Format("@WebFolderName{0}", index), ToDBValue(userWebFolder.WebFolderName)),
new MySqlParameter(string.Format("@UserInfoID{0}", index), ToDBValue(userWebFolder.UserInfoID)),
new MySqlParameter(string.Format("@CreateTime{0}", index), ToDBValue(userWebFolder.CreateTime)),
new MySqlParameter(string.Format("@Visible{0}", index), ToDBValue(userWebFolder.Visible)),
new MySqlParameter(string.Format("@ParentWebfolderID{0}", index), ToDBValue(userWebFolder.ParentWebfolderID)),
new MySqlParameter(string.Format("@IntroContent{0}", index), ToDBValue(userWebFolder.IntroContent)),
new MySqlParameter(string.Format("@IElementJSON{0}", index), ToDBValue(userWebFolder.IElementJSON)),
new MySqlParameter(string.Format("@IElementHashcode{0}", index), ToDBValue(userWebFolder.IElementHashcode)),
};
lstPara.AddRange(paramater);
index = index + 1;
}
int AddId = (int)MyDBHelper.ExecuteNonQuery(sbSQL.ToString(), lstPara.ToArray());
return(AddId > 0);
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(UserWebFolder userWebFolder)
{
string sql =
"UPDATE tblUserWebFolder " +
"SET " +
" WebFolderName = @WebFolderName"
+ ", UserInfoID = @UserInfoID"
+ ", CreateTime = @CreateTime"
+ ", Grade = @Grade"
+ ", ParentWebfolderID = @ParentWebfolderID"
+ ", IntroContent = @IntroContent"
+ ", IElementJSON = @IElementJSON"
+ ", IElementHashcode = @IElementHashcode"
+ " WHERE UserWebFolderID = @UserWebFolderID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserWebFolderID", userWebFolder.UserWebFolderID)
, new MySqlParameter("@WebFolderName", ToDBValue(userWebFolder.WebFolderName))
, new MySqlParameter("@UserInfoID", ToDBValue(userWebFolder.UserInfoID))
, new MySqlParameter("@CreateTime", ToDBValue(userWebFolder.CreateTime))
, new MySqlParameter("@Grade", ToDBValue(userWebFolder.Grade))
, new MySqlParameter("@ParentWebfolderID", ToDBValue(userWebFolder.ParentWebfolderID))
, new MySqlParameter("@IntroContent", ToDBValue(userWebFolder.IntroContent))
, new MySqlParameter("@IElementJSON", ToDBValue(userWebFolder.IElementJSON))
, new MySqlParameter("@IElementHashcode", ToDBValue(userWebFolder.IElementHashcode))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
void UserLogin()
{
try
{
string tmpSQL = "";
//tmpSQL = "select * from UsersManage where LoginUsers='" + txtUser.Text.Trim().ToString() + "' and LoginPassWord='******'";
tmpSQL = "select * from UsersManage where LoginUsers='" + comboBoxUser.Text.Trim().ToString() + "' and LoginPassWord='******'";
DataSet ds = new DataSet();
ds = MyDBHelper.GetMyDBData(tmpSQL, "UsersManage");
if (ds.Tables[0].Rows.Count > 0)
{
floginUser.LoginName = ds.Tables[0].Rows[0]["LoginUsers"].ToString();
floginUser.LoginState = (UserState)(StringUtil.strtoint(ds.Tables[0].Rows[0]["LoginUsers"].ToString()) - 1);
//MessageBox.Show("登录成功！");
floginUser.Loginpermission = int.Parse(ds.Tables[0].Rows[0]["Permission"].ToString());
switch (floginUser.Loginpermission)
{
case 3:
{
this.Visible = false;
//frmHilTest frmEolTest = new frmHilTest();
//frmEolTest.WindowState = FormWindowState.Maximized;
//frmEolTest.Show();
//if (this.FrmMain != null)
// this.FrmMain.Close();
this.DialogResult = DialogResult.OK;
break;
}
case 2: goto case 1;
case 1:
{
this.Visible = false;
//if (this.FrmMain == null)
//{
// this.FrmMain = new FrmMain();
// this.FrmMain.Show();
// this.Visible = false;
//}
//else
//{
// this.FrmMain.Visible = true;
//}
this.DialogResult = DialogResult.OK;
break;
}
}
}
else
{
MessageBox.Show("密码错误，登录失败！");
this.DialogResult = DialogResult.Cancel;
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(BookmarkInfo bookmarkInfo)
{
string sql = "INSERT INTO tblBookmarkInfo (UserWebFolderID, UserInfoID, Href, HTML, Host, CreateTime, IElementJSON, BookmarkName, Grate, HashCode, IsShowWithiframe) VALUES (@UserWebFolderID, @UserInfoID, @Href, @HTML, @Host, @CreateTime, @IElementJSON, @BookmarkName, @Grate, @HashCode, @IsShowWithiframe)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserWebFolderID", ToDBValue(bookmarkInfo.UserWebFolderID)),
new MySqlParameter("@UserInfoID", ToDBValue(bookmarkInfo.UserInfoID)),
new MySqlParameter("@Href", ToDBValue(bookmarkInfo.Href)),
new MySqlParameter("@HTML", ToDBValue(bookmarkInfo.HTML)),
new MySqlParameter("@Host", ToDBValue(bookmarkInfo.Host)),
new MySqlParameter("@CreateTime", ToDBValue(bookmarkInfo.CreateTime)),
new MySqlParameter("@IElementJSON", ToDBValue(bookmarkInfo.IElementJSON)),
new MySqlParameter("@BookmarkName", ToDBValue(bookmarkInfo.BookmarkName)),
new MySqlParameter("@Grate", ToDBValue(bookmarkInfo.Grate)),
new MySqlParameter("@HashCode", ToDBValue(bookmarkInfo.HashCode)),
new MySqlParameter("@IsShowWithiframe", ToDBValue(bookmarkInfo.IsShowWithiframe)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(SystemLog systemLog)
{
string sql =
"UPDATE tblSystemLog " +
"SET " +
" LogTitle = @LogTitle"
+ ", LogContent = @LogContent"
+ ", LogType = @LogType"
+ ", CreateTime = @CreateTime"
+ ", DynamicInfo = @DynamicInfo"
+ " WHERE SystemLogID = @SystemLogID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@SystemLogID", systemLog.SystemLogID)
, new MySqlParameter("@LogTitle", ToDBValue(systemLog.LogTitle))
, new MySqlParameter("@LogContent", ToDBValue(systemLog.LogContent))
, new MySqlParameter("@LogType", ToDBValue(systemLog.LogType))
, new MySqlParameter("@CreateTime", ToDBValue(systemLog.CreateTime))
, new MySqlParameter("@DynamicInfo", ToDBValue(systemLog.DynamicInfo))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(BookmarkComment bookmarkComment)
{
string sql = "INSERT INTO tblBookmarkComment (CommentTitle, CommentContent, BookmarkInfoID, CriticsUserID, CreateTime, BookmarkUserID) VALUES (@CommentTitle, @CommentContent, @BookmarkInfoID, @CriticsUserID, @CreateTime, @BookmarkUserID)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@CommentTitle", ToDBValue(bookmarkComment.CommentTitle)),
new MySqlParameter("@CommentContent", ToDBValue(bookmarkComment.CommentContent)),
new MySqlParameter("@BookmarkInfoID", ToDBValue(bookmarkComment.BookmarkInfoID)),
new MySqlParameter("@CriticsUserID", ToDBValue(bookmarkComment.CriticsUserID)),
new MySqlParameter("@CreateTime", ToDBValue(bookmarkComment.CreateTime)),
new MySqlParameter("@BookmarkUserID", ToDBValue(bookmarkComment.BookmarkUserID)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(BookmarkComment bookmarkComment)
{
string sql =
"UPDATE tblBookmarkComment " +
"SET " +
" CommentTitle = @CommentTitle"
+ ", CommentContent = @CommentContent"
+ ", BookmarkInfoID = @BookmarkInfoID"
+ ", CriticsUserID = @CriticsUserID"
+ ", CreateTime = @CreateTime"
+ ", BookmarkUserID = @BookmarkUserID"
+ " WHERE BookmarkCommentID = @BookmarkCommentID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@BookmarkCommentID", bookmarkComment.BookmarkCommentID)
, new MySqlParameter("@CommentTitle", ToDBValue(bookmarkComment.CommentTitle))
, new MySqlParameter("@CommentContent", ToDBValue(bookmarkComment.CommentContent))
, new MySqlParameter("@BookmarkInfoID", ToDBValue(bookmarkComment.BookmarkInfoID))
, new MySqlParameter("@CriticsUserID", ToDBValue(bookmarkComment.CriticsUserID))
, new MySqlParameter("@CreateTime", ToDBValue(bookmarkComment.CreateTime))
, new MySqlParameter("@BookmarkUserID", ToDBValue(bookmarkComment.BookmarkUserID))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(RetrievePasswordLog retrievePasswordLog)
{
string sql =
"UPDATE tblRetrievePasswordLog " +
"SET " +
" UserInfoID = @UserInfoID"
+ ", LogStatus = @LogStatus"
+ ", Token = @Token"
+ ", CreateTime = @CreateTime"
+ ", LastTime = @LastTime"
+ " WHERE RetrievePasswordLogID = @RetrievePasswordLogID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@RetrievePasswordLogID", retrievePasswordLog.RetrievePasswordLogID)
, new MySqlParameter("@UserInfoID", ToDBValue(retrievePasswordLog.UserInfoID))
, new MySqlParameter("@LogStatus", ToDBValue(retrievePasswordLog.LogStatus))
, new MySqlParameter("@Token", ToDBValue(retrievePasswordLog.Token))
, new MySqlParameter("@CreateTime", ToDBValue(retrievePasswordLog.CreateTime))
, new MySqlParameter("@LastTime", ToDBValue(retrievePasswordLog.LastTime))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(UserDynamicInfo userDynamicInfo)
{
string sql =
"UPDATE tblUserDynamicInfo " +
"SET " +
" UserDynamicContent = @UserDynamicContent"
+ ", CreateTime = @CreateTime"
+ ", UserInfoID = @UserInfoID"
+ ", UserDynamicType = @UserDynamicType"
+ ", UserDynamicURL = @UserDynamicURL"
+ " WHERE UserDynamicInfoID = @UserDynamicInfoID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserDynamicInfoID", userDynamicInfo.UserDynamicInfoID)
, new MySqlParameter("@UserDynamicContent", ToDBValue(userDynamicInfo.UserDynamicContent))
, new MySqlParameter("@CreateTime", ToDBValue(userDynamicInfo.CreateTime))
, new MySqlParameter("@UserInfoID", ToDBValue(userDynamicInfo.UserInfoID))
, new MySqlParameter("@UserDynamicType", ToDBValue(userDynamicInfo.UserDynamicType))
, new MySqlParameter("@UserDynamicURL", ToDBValue(userDynamicInfo.UserDynamicURL))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
public bool BatchInsert(List <BookmarkInfo> lstBookmarkInfo)
{
StringBuilder sbSQL = new StringBuilder();
int index = 0;
List <MySqlParameter> lstPara = new List <MySqlParameter>();
foreach (var bookmarkInfo in lstBookmarkInfo)
{
sbSQL.AppendLine("INSERT INTO tblBookmarkInfo (UserWebFolderID, UserInfoID, Href, HTML, Host, CreateTime, IElementJSON, BookmarkName, Grate, HashCode, IsShowWithiframe) VALUES (@UserWebFolderID" + index +
", @UserInfoID" + index + ", @Href" + index + ", @HTML" + index + ", @Host" +
index + ", @CreateTime" + index + ", @IElementJSON" + index + ", @BookmarkName" + index + ", @Grate" + index + ", @HashCode" + index + ", @IsShowWithiframe" + index + ");");
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserWebFolderID" + index, ToDBValue(bookmarkInfo.UserWebFolderID)),
new MySqlParameter("@UserInfoID" + index, ToDBValue(bookmarkInfo.UserInfoID)),
new MySqlParameter("@Href" + index, ToDBValue(bookmarkInfo.Href)),
new MySqlParameter("@HTML" + index, ToDBValue(bookmarkInfo.HTML)),
new MySqlParameter("@Host" + index, ToDBValue(bookmarkInfo.Host)),
new MySqlParameter("@CreateTime" + index, ToDBValue(bookmarkInfo.CreateTime)),
new MySqlParameter("@IElementJSON" + index, ToDBValue(bookmarkInfo.IElementJSON)),
new MySqlParameter("@BookmarkName" + index, ToDBValue(bookmarkInfo.BookmarkName)),
new MySqlParameter("@HashCode" + index, ToDBValue(bookmarkInfo.HashCode)),
new MySqlParameter("@Grate" + index, ToDBValue(bookmarkInfo.Grate)),
new MySqlParameter("@IsShowWithiframe" + index, ToDBValue(bookmarkInfo.IsShowWithiframe)),
};
lstPara.AddRange(para);
index = index + 1;
}
return((int)MyDBHelper.ExecuteScalar(sbSQL.ToString(), lstPara.ToArray()) > 0);
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(MessageInfo messageInfo)
{
string sql = "INSERT INTO tblMessageInfo (MessageTitle, MessageContent, UserInfoID, IsRead, MessageInfoType, CreateTime, MessageURL) VALUES (@MessageTitle, @MessageContent, @UserInfoID, @IsRead, @MessageInfoType, @CreateTime, @MessageURL)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@MessageTitle", ToDBValue(messageInfo.MessageTitle)),
new MySqlParameter("@MessageContent", ToDBValue(messageInfo.MessageContent)),
new MySqlParameter("@UserInfoID", ToDBValue(messageInfo.UserInfoID)),
new MySqlParameter("@IsRead", ToDBValue(messageInfo.IsRead)),
new MySqlParameter("@MessageInfoType", ToDBValue(messageInfo.MessageInfoType)),
new MySqlParameter("@CreateTime", ToDBValue(messageInfo.CreateTime)),
new MySqlParameter("@MessageURL", ToDBValue(messageInfo.MessageURL)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取城市房源URL HashSet（默认取1000条）
/// </summary>
/// <returns></returns>
public HashSet <String> GetAllHouseOnlineURL(string source, string cityName, int limit = 1000)
{
HashSet <String> hsURL = new HashSet <string>();
string sqlText = @"select distinct HouseOnlineURL from housecrawler.HouseInfos where Source =@Source
and LocationCityName=@LocationCityName order by PubTime limit " + limit;
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter()
{
ParameterName = "@Source", Value = source, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@LocationCityName", Value = cityName, DbType = DbType.String
},
};
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(sqlText, para))
{
while (reader.Read())
{
string houseOnlineURL = (string)ToModelValue(reader, "HouseOnlineURL");
if (!hsURL.Contains(houseOnlineURL))
{
hsURL.Add(houseOnlineURL);
}
}
reader.Dispose();
}
return(hsURL);
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(UserInfo userInfo)
{
string sql = "INSERT INTO tblUserInfo (UserLoginName, UserPassword, UserName, UserEmail, UserPhone, UserQQ, CreateTime, UserImagURL, UserInfoComment, ActivateAccountToken, AccountStatus) VALUES (@UserLoginName, @UserPassword, @UserName, @UserEmail, @UserPhone, @UserQQ, @CreateTime, @UserImagURL, @UserInfoComment, @ActivateAccountToken, @AccountStatus)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserLoginName", ToDBValue(userInfo.UserLoginName)),
new MySqlParameter("@UserPassword", ToDBValue(userInfo.UserPassword)),
new MySqlParameter("@UserName", ToDBValue(userInfo.UserName)),
new MySqlParameter("@UserEmail", ToDBValue(userInfo.UserEmail)),
new MySqlParameter("@UserPhone", ToDBValue(userInfo.UserPhone)),
new MySqlParameter("@UserQQ", ToDBValue(userInfo.UserQQ)),
new MySqlParameter("@CreateTime", ToDBValue(userInfo.CreateTime)),
new MySqlParameter("@UserImagURL", ToDBValue(userInfo.UserImagURL)),
new MySqlParameter("@UserInfoComment", ToDBValue(userInfo.UserInfoComment)),
new MySqlParameter("@ActivateAccountToken", ToDBValue(userInfo.ActivateAccountToken)),
new MySqlParameter("@AccountStatus", ToDBValue(userInfo.AccountStatus)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(WebpageComment webpageComment)
{
string sql =
"UPDATE tblWebpageComment " +
"SET " +
" CommentUserID = @CommentUserID"
+ ", URLInfoID = @URLInfoID"
+ ", WebpageUserInfoID = @WebpageUserInfoID"
+ ", CommentTitle = @CommentTitle"
+ ", CommentContent = @CommentContent"
+ ", CommentType = @CommentType"
+ ", CreateTime = @CreateTime"
+ " WHERE WebpageCommentID = @WebpageCommentID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@WebpageCommentID", webpageComment.WebpageCommentID)
, new MySqlParameter("@CommentUserID", ToDBValue(webpageComment.CommentUserID))
, new MySqlParameter("@URLInfoID", ToDBValue(webpageComment.URLInfoID))
, new MySqlParameter("@WebpageUserInfoID", ToDBValue(webpageComment.WebpageUserInfoID))
, new MySqlParameter("@CommentTitle", ToDBValue(webpageComment.CommentTitle))
, new MySqlParameter("@CommentContent", ToDBValue(webpageComment.CommentContent))
, new MySqlParameter("@CommentType", ToDBValue(webpageComment.CommentType))
, new MySqlParameter("@CreateTime", ToDBValue(webpageComment.CreateTime))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
///<summary>
/// 获得分页记录集IEnumerable<>
///</summary>
public IEnumerable <DBHouseInfo> SearchHouseInfo(string cityName, string source = "",
int houseCount = 100, int withinAnyDays = 7, string keyword = "")
{
List <DBHouseInfo> houses = new List <DBHouseInfo>();
if (string.IsNullOrEmpty(source))
{
var douban = GetSQLText(cityName, ConstConfigurationName.Douban, houseCount, withinAnyDays, keyword);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(douban.Item1, douban.Item2.ToArray()))
{
houses.AddRange(ToModels(reader));
}
var huzu = GetSQLText(cityName, ConstConfigurationName.HuZhuZuFang, houseCount, withinAnyDays, keyword);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(douban.Item1, douban.Item2.ToArray()))
{
houses.AddRange(ToModels(reader));
}
var pinpai = GetSQLText(cityName, ConstConfigurationName.PinPaiGongYu, houseCount, withinAnyDays, keyword);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(douban.Item1, douban.Item2.ToArray()))
{
houses.AddRange(ToModels(reader));
}
}
else
{
var result = GetSQLText(cityName, source, houseCount, withinAnyDays, keyword);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(result.Item1, result.Item2.ToArray()))
{
houses.AddRange(ToModels(reader));
}
}
return(houses);
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(WebpageComment webpageComment)
{
string sql = "INSERT INTO tblWebpageComment (CommentUserID, URLInfoID, WebpageUserInfoID, CommentTitle, CommentContent, CommentType, CreateTime) VALUES (@CommentUserID, @URLInfoID, @WebpageUserInfoID, @CommentTitle, @CommentContent, @CommentType, @CreateTime)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@CommentUserID", ToDBValue(webpageComment.CommentUserID)),
new MySqlParameter("@URLInfoID", ToDBValue(webpageComment.URLInfoID)),
new MySqlParameter("@WebpageUserInfoID", ToDBValue(webpageComment.WebpageUserInfoID)),
new MySqlParameter("@CommentTitle", ToDBValue(webpageComment.CommentTitle)),
new MySqlParameter("@CommentContent", ToDBValue(webpageComment.CommentContent)),
new MySqlParameter("@CommentType", ToDBValue(webpageComment.CommentType)),
new MySqlParameter("@CreateTime", ToDBValue(webpageComment.CreateTime)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Save()
{
string sql = @"INSERT INTO student (StudentName, StudentNumber, QQNumber, GroupType, Intro, ITEleteInfo, Photo, CreateTime,BirthDay)
VALUES (@StudentName, @StudentNumber, @QQNumber, @GroupType, @Intro, @ITEleteInfo, @Photo, @CreateTime,@BirthDay)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@StudentName", ToDBValue(StudentName)),
new MySqlParameter("@StudentNumber", ToDBValue(StudentNumber)),
new MySqlParameter("@QQNumber", ToDBValue(QQNumber)),
new MySqlParameter("@GroupType", ToDBValue(GroupType)),
new MySqlParameter("@Intro", ToDBValue(Intro)),
new MySqlParameter("@CreateTime", ToDBValue(CreateTime)),
new MySqlParameter("@ITEleteInfo", ToDBValue(ITEleteInfo)),
new MySqlParameter("@Photo", ToDBValue(Photo)),
new MySqlParameter("@BirthDay", ToDBValue(BirthDay)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(GroupInfo groupInfo)
{
string sql =
"UPDATE tblGroupInfo " +
"SET " +
" GroupName = @GroupName"
+ ", GroupIntro = @GroupIntro"
+ ", CreateUesrID = @CreateUesrID"
+ ", CreateTime = @CreateTime"
+ ", ObjectHashcode = @ObjectHashcode"
+ " WHERE GroupInfoID = @GroupInfoID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@GroupInfoID", groupInfo.GroupInfoID)
, new MySqlParameter("@GroupName", ToDBValue(groupInfo.GroupName))
, new MySqlParameter("@GroupIntro", ToDBValue(groupInfo.GroupIntro))
, new MySqlParameter("@CreateUesrID", ToDBValue(groupInfo.CreateUesrID))
, new MySqlParameter("@CreateTime", ToDBValue(groupInfo.CreateTime))
, new MySqlParameter("@ObjectHashcode", ToDBValue(groupInfo.ObjectHashcode))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(ConfigurationInfo configurationInfo)
{
string sql =
"UPDATE tblConfigurationInfo " +
"SET " +
" ConfigurationKey = @ConfigurationKey"
+ ", ConfigurationValue = @ConfigurationValue"
+ ", Description = @Description"
+ ", ConfigurationNo = @ConfigurationNo"
+ ", HashCode = @HashCode"
+ " WHERE ConfigurationInfoID = @ConfigurationInfoID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@ConfigurationInfoID", configurationInfo.ConfigurationInfoID)
, new MySqlParameter("@ConfigurationKey", ToDBValue(configurationInfo.ConfigurationKey))
, new MySqlParameter("@ConfigurationValue", ToDBValue(configurationInfo.ConfigurationValue))
, new MySqlParameter("@Description", ToDBValue(configurationInfo.Description))
, new MySqlParameter("@ConfigurationNo", ToDBValue(configurationInfo.ConfigurationNo))
, new MySqlParameter("@HashCode", ToDBValue(configurationInfo.HashCode))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 根据传入Model，并返回Model
/// </summary>
public bool Add(UserWebFolder userWebFolder)
{
string sql = "INSERT INTO tblUserWebFolder (WebFolderName, UserInfoID, CreateTime, Grade, ParentWebfolderID, IntroContent, IElementJSON, IElementHashcode) VALUES (@WebFolderName, @UserInfoID, @CreateTime, @Grade, @ParentWebfolderID, @IntroContent, @IElementJSON, @IElementHashcode)";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@WebFolderName", ToDBValue(userWebFolder.WebFolderName)),
new MySqlParameter("@UserInfoID", ToDBValue(userWebFolder.UserInfoID)),
new MySqlParameter("@CreateTime", ToDBValue(userWebFolder.CreateTime)),
new MySqlParameter("@Grade", ToDBValue(userWebFolder.Grade)),
new MySqlParameter("@ParentWebfolderID", ToDBValue(userWebFolder.ParentWebfolderID)),
new MySqlParameter("@IntroContent", ToDBValue(userWebFolder.IntroContent)),
new MySqlParameter("@IElementJSON", ToDBValue(userWebFolder.IElementJSON)),
new MySqlParameter("@IElementHashcode", ToDBValue(userWebFolder.IElementHashcode)),
};
int AddId = (int)MyDBHelper.ExecuteScalar(sql, para);
if (AddId == 1)
{
return(true);
}
else
{
return(false);
}
}
///<summary>
/// 获得分页记录集IEnumerable<>
///</summary>
public IEnumerable <DBHouseInfo> SearchHouseInfo(string cityName, string source = "", int houseCount = 100, int withAnyDays = 3)
{
string sqlText = "SELECT * from HouseInfos where 1=1 ";
List <MySqlParameter> lstMySqlParameter = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(cityName))
{
sqlText = sqlText + " and LocationCityName = @LocationCityName ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@LocationCityName", Value = cityName, DbType = DbType.String
});
}
if (!string.IsNullOrEmpty(source))
{
sqlText = sqlText + " and Source = @Source ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@Source", Value = source, DbType = DbType.String
});
}
sqlText = sqlText + " and PubTime>= @PubTime ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@PubTime", Value = DateTime.Now.Date.AddDays(-withAnyDays), DbType = DbType.Date
});
sqlText = sqlText + string.Format(" limit {0} ", houseCount);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(sqlText, lstMySqlParameter.ToArray()))
{
return(ToModels(reader));
}
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int Update(MessageInfo messageInfo)
{
string sql =
"UPDATE tblMessageInfo " +
"SET " +
" MessageTitle = @MessageTitle"
+ ", MessageContent = @MessageContent"
+ ", UserInfoID = @UserInfoID"
+ ", IsRead = @IsRead"
+ ", MessageInfoType = @MessageInfoType"
+ ", CreateTime = @CreateTime"
+ ", MessageURL = @MessageURL"
+ " WHERE MessageInfoID = @MessageInfoID";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@MessageInfoID", messageInfo.MessageInfoID)
, new MySqlParameter("@MessageTitle", ToDBValue(messageInfo.MessageTitle))
, new MySqlParameter("@MessageContent", ToDBValue(messageInfo.MessageContent))
, new MySqlParameter("@UserInfoID", ToDBValue(messageInfo.UserInfoID))
, new MySqlParameter("@IsRead", ToDBValue(messageInfo.IsRead))
, new MySqlParameter("@MessageInfoType", ToDBValue(messageInfo.MessageInfoType))
, new MySqlParameter("@CreateTime", ToDBValue(messageInfo.CreateTime))
, new MySqlParameter("@MessageURL", ToDBValue(messageInfo.MessageURL))
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
///<summary>
/// 获得分页记录集IEnumerable<>
///</summary>
public IEnumerable <DBHouseInfo> SearchHouseInfo(string cityName, string source = "",
int houseCount = 100, int withinAnyDays = 3, bool showDoubanInvalidData = true, string keyword = "")
{
string sqlText = "SELECT * from HouseInfos where 1=1 ";
List <MySqlParameter> lstMySqlParameter = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(cityName))
{
sqlText = sqlText + " and LocationCityName = @LocationCityName ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@LocationCityName", Value = cityName, DbType = DbType.String
});
}
if (!string.IsNullOrEmpty(source))
{
sqlText = sqlText + " and Source = @Source ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@Source", Value = source, DbType = DbType.String
});
}
//是否展示豆瓣无效数据
if (!showDoubanInvalidData && source == ConstConfigurationName.Douban)
{
sqlText = sqlText + " and Status = @Status and HousePrice > 0 ";
//仅仅显示有效数据
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@Status", Value = 1, DbType = DbType.Int32
});
}
if (!string.IsNullOrEmpty(keyword))
{
keyword = "%" + keyword + "%";
sqlText = sqlText + " and (HouseText like @keyword or HouseLocation like @keyword ) ";
//仅仅显示有效数据
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@keyword", Value = keyword, DbType = DbType.String
});
}
sqlText = sqlText + " and PubTime>= @PubTime ";
lstMySqlParameter.Add(new MySqlParameter()
{
ParameterName = "@PubTime", Value = DateTime.Now.Date.AddDays(-withinAnyDays), DbType = DbType.Date
});
sqlText = sqlText + string.Format(" limit {0} ", houseCount);
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(sqlText, lstMySqlParameter.ToArray()))
{
return(ToModels(reader));
}
}
/// <summary>
/// 获取一批的用户信息
/// </summary>
/// <param name="lstUID"></param>
/// <returns></returns>
public IEnumerable <UserInfo> GetByUserIDList(List <long> lstUID)
{
string sql = string.Format("SELECT * FROM tblUserInfo WHERE UserInfoID IN ({0})", string.Join(",", lstUID));
using (MySqlDataReader reader = MyDBHelper.ExecuteDataReader(sql))
{
return(ToModels(reader));
}
}
///<summary>
/// 获得总记录集IEnumerable<>
///</summary>
public IEnumerable <UserInfo> GetAll()
{
string sql = "SELECT * FROM tblUserInfo";
using (MySqlDataReader reader = MyDBHelper.ExecuteDataReader(sql))
{
return(ToModels(reader));
}
}
///<summary>
///根据字段名获取数据记录IEnumerable<>
///</summary>
public IEnumerable <UserInfo> GetBycolumnName(string columnName, string columnContent)
{
string sql = "SELECT * FROM tblUserInfo where " + columnName + "=" + columnContent;
using (MySqlDataReader reader = MyDBHelper.ExecuteDataReader(sql))
{
return(ToModels(reader));
}
}
///<summary>
/// 获取需要分析的数据（一次2000条）
///</summary>
public IEnumerable <DBHouseInfo> LoadUnAnalyzeList()
{
string sqlText = "SELECT * FROM housecrawler.HouseInfos where Source='douban' and Status =0 limit 2000 ;";
using (DbDataReader reader = MyDBHelper.ExecuteDataReader(sqlText))
{
return(ToModels(reader));
}
}
/// <summary>
//获取用户的关系记录
/// </summary>
public IEnumerable <UserRelationship> GetByBeFollowUserID(long beFollwedUID)
{
string sql = "SELECT * FROM tblUserRelationship WHERE BeFollwedUID = @BeFollwedUID order by CreateTime desc";
using (MySqlDataReader reader = MyDBHelper.ExecuteDataReader(sql, new MySqlParameter("@BeFollwedUID", beFollwedUID)))
{
return(ToModels(reader));
}
}
