public static List <Question> getQuestionBySurveyId(int survey_id)
{
try
{
List <Question> list = new List <Question>();
MySqlConnection conn = new MySqlConnection(Config.sqlUrl);
conn.Open();
String sql = "select * from question where survey_id=@id";
MySqlCommand comm = new MySqlCommand(sql, conn);
comm.Parameters.Add("id", survey_id);
MySqlDataReader sdr = comm.ExecuteReader();
while (sdr.Read())
{
Question question = new Question();
question.id = sdr.GetInt32("id");
question.survey_id = sdr.GetInt32("survey_id");
question.title = sdr.GetString("title");
question.type = sdr.GetInt32("type");
question.num = sdr.GetInt32("num");
question.options = getOptionByQuestionId(question.id);
int clickCount = 0;
foreach (Option option in question.options)
{
clickCount += option.click;
}
if (clickCount == 0)
{
clickCount = 1;
}
question.click_count = clickCount;
list.Add(question);
}
sdr.Close();
comm.Clone();
conn.Close();
return(list);
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <GenVoteUser> Search(Int32 Vid, Int32 UserId, String CreateTime, String VIp, Int32 Id, int pageIndex, int pageSize)
{
PageEntity <GenVoteUser> returnValue = new PageEntity <GenVoteUser>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadGenVoteUser = cmdLoadGenVoteUser.Clone() as MySqlCommand;
MySqlCommand _cmdGetGenVoteUserCount = cmdGetGenVoteUserCount.Clone() as MySqlCommand;
_cmdLoadGenVoteUser.Connection = oc;
_cmdGetGenVoteUserCount.Connection = oc;
try
{
_cmdLoadGenVoteUser.Parameters["@PageIndex"].Value = pageIndex;
_cmdLoadGenVoteUser.Parameters["@PageSize"].Value = pageSize;
_cmdLoadGenVoteUser.Parameters["@Vid"].Value = Vid;
_cmdLoadGenVoteUser.Parameters["@UserId"].Value = UserId;
_cmdLoadGenVoteUser.Parameters["@CreateTime"].Value = CreateTime;
_cmdLoadGenVoteUser.Parameters["@VIp"].Value = VIp;
_cmdLoadGenVoteUser.Parameters["@Id"].Value = Id;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadGenVoteUser.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new GenVoteUser().BuildSampleEntity(reader));
}
returnValue.RecordsCount = Convert.ToInt32(_cmdGetGenVoteUserCount.ExecuteScalar());
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadGenVoteUser.Dispose();
_cmdLoadGenVoteUser = null;
_cmdGetGenVoteUserCount.Dispose();
_cmdGetGenVoteUserCount = null;
GC.Collect();
}
return(returnValue);
}
/// <summary>
/// 添加数据
/// <param name="es">数据实体对象数组</param>
/// <returns></returns>
/// </summary>
public int Insert(ProProduct e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdInsertProProduct = cmdInsertProProduct.Clone() as MySqlCommand;
int returnValue = 0;
_cmdInsertProProduct.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdInsertProProduct.Parameters["@Pid"].Value = e.Pid;
_cmdInsertProProduct.Parameters["@Title"].Value = e.Title;
_cmdInsertProProduct.Parameters["@BbInfo"].Value = e.BbInfo;
_cmdInsertProProduct.Parameters["@State"].Value = e.State == 0 ? 1 : e.State;
_cmdInsertProProduct.Parameters["@Level"].Value = e.Level;
_cmdInsertProProduct.Parameters["@CreateTime"].Value = e.CreateTime;
_cmdInsertProProduct.Parameters["@CreateId"].Value = e.CreateId;
_cmdInsertProProduct.Parameters["@ViewNum"].Value = e.ViewNum;
_cmdInsertProProduct.Parameters["@IsWash"].Value = e.IsWash ? 1 : 2;
_cmdInsertProProduct.Parameters["@MinAge"].Value = e.MinAge;
_cmdInsertProProduct.Parameters["@MaxAge"].Value = e.MaxAge;
_cmdInsertProProduct.Parameters["@Price"].Value = e.Price;
_cmdInsertProProduct.Parameters["@ItemType"].Value = (int)e.ItemType;
_cmdInsertProProduct.Parameters["@ItemSort"].Value = (int)e.ItemSort;
_cmdInsertProProduct.Parameters["@Sex"].Value = (int)e.Sex;
_cmdInsertProProduct.ExecuteNonQuery();
returnValue = Convert.ToInt32(_cmdInsertProProduct.LastInsertedId);
return(returnValue);
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdInsertProProduct.Dispose();
_cmdInsertProProduct = null;
}
}
public static void deleteSurveyById(int id)
{
try
{
MySqlConnection conn = new MySqlConnection(Config.sqlUrl);
conn.Open();
String sql = "delete from survey where id = @id";
MySqlCommand comm = new MySqlCommand(sql, conn);
comm.Parameters.Add("id", id);
comm.ExecuteNonQuery();
comm.Clone();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public static void useArrayByCode(string code)
{
try
{
MySqlConnection conn = new MySqlConnection(Config.sqlUrl);
conn.Open();
String sql = "update array set status = 0 where code = @code";
MySqlCommand comm = new MySqlCommand(sql, conn);
comm.Parameters.Add("code", code);
comm.ExecuteNonQuery();
comm.Clone();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private static MySqlCommand GetMySqlCommand(MySqlConnection connection, String sql, MySqlParameter[] parameters = null, CommandType commandType = CommandType.Text)
{
if (baseCommand == null)
{
baseCommand = new MySqlCommand();
}
MySqlCommand commond = (MySqlCommand)baseCommand.Clone();
commond.Connection = connection;
commond.CommandText = sql;
commond.CommandType = commandType;
if (parameters != null)
{
commond.Parameters.AddRange(parameters);
}
return(commond);
}
private void Valider_Click(object sender, EventArgs e)
{
string categorie_a_ajoutée = New_categories_text.Text;
if (categorie_a_ajoutée == null || categorie_a_ajoutée == "")
{
MessageBox.Show("Vous n'avez pas saisi de nouvelle categorie", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
conn.Open();
string categorie_ajoutée = categorie_a_ajoutée.ToString();
MySqlCommand verif_new_categorie_query = new MySqlCommand("SELECT name FROM category WHERE name = '" + categorie_ajoutée + "'", conn);
MySqlDataReader verif_new_categorie = verif_new_categorie_query.ExecuteReader();
if (!verif_new_categorie.HasRows)
{
//ObjectDumperExtensions.DumpToString(verif_new_categorie, "categorie existe deja");
verif_new_categorie.Close();
MySqlCommand ajouter_new_categorie_query = new MySqlCommand("INSERT INTO category (name) VALUES ('" + categorie_ajoutée + "')", conn);
MySqlDataReader ajouter_new_categorie = ajouter_new_categorie_query.ExecuteReader();
MessageBox.Show("Nouvelle catégorie ajoutée au nom de : " + categorie_ajoutée, "Succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
ajouter_new_categorie_query.Clone();
conn.Close();
}
else
{
MessageBox.Show("La categorie existe déjà", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
verif_new_categorie.Close();
conn.Close();
}
}
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <GenBless> Search(Int32 Bid, String Content, DateTime CreateTime, Int32 CreateId, int pageIndex, int pageSize)
{
PageEntity <GenBless> returnValue = new PageEntity <GenBless>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadGenBless = cmdLoadGenBless.Clone() as MySqlCommand;
MySqlCommand _cmdGetGenBlessCount = cmdGetGenBlessCount.Clone() as MySqlCommand;
_cmdLoadGenBless.Connection = oc;
_cmdGetGenBlessCount.Connection = oc;
try
{
_cmdLoadGenBless.Parameters["@PageIndex"].Value = pageIndex;
_cmdLoadGenBless.Parameters["@PageSize"].Value = pageSize;
_cmdLoadGenBless.Parameters["@Bid"].Value = Bid;
_cmdLoadGenBless.Parameters["@Content"].Value = Content;
_cmdLoadGenBless.Parameters["@CreateTime"].Value = CreateTime;
_cmdLoadGenBless.Parameters["@CreateId"].Value = CreateId;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadGenBless.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new GenBless().BuildSampleEntity(reader));
}
returnValue.RecordsCount = Convert.ToInt32(_cmdGetGenBlessCount.ExecuteScalar());
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadGenBless.Dispose();
_cmdLoadGenBless = null;
_cmdGetGenBlessCount.Dispose();
_cmdGetGenBlessCount = null;
GC.Collect();
}
return(returnValue);
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <SysRole> Search(Int32 RoleId, String RoleName, Int32 EntId, Int32 FatherId, int pageIndex, int pageSize)
{
PageEntity <SysRole> returnValue = new PageEntity <SysRole>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadSysRole = cmdLoadSysRole.Clone() as MySqlCommand;
MySqlCommand _cmdGetSysRoleCount = cmdGetSysRoleCount.Clone() as MySqlCommand;
_cmdLoadSysRole.Connection = oc;
_cmdGetSysRoleCount.Connection = oc;
try
{
_cmdLoadSysRole.Parameters["@PageIndex"].Value = pageIndex;
_cmdLoadSysRole.Parameters["@PageSize"].Value = pageSize;
_cmdLoadSysRole.Parameters["@RoleId"].Value = RoleId;
_cmdLoadSysRole.Parameters["@RoleName"].Value = RoleName;
_cmdLoadSysRole.Parameters["@EntId"].Value = EntId;
_cmdLoadSysRole.Parameters["@FatherId"].Value = FatherId;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadSysRole.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new SysRole().BuildSampleEntity(reader));
}
returnValue.RecordsCount = Convert.ToInt32(_cmdGetSysRoleCount.ExecuteScalar());
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadSysRole.Dispose();
_cmdLoadSysRole = null;
_cmdGetSysRoleCount.Dispose();
_cmdGetSysRoleCount = null;
GC.Collect();
}
return(returnValue);
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <AdUser> Search(String UserAccount, DateTime BabyBirthday, int State, int pageIndex, int pageSize)
{
PageEntity <AdUser> returnValue = new PageEntity <AdUser>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadAdUser = cmdLoadAdUser.Clone() as MySqlCommand;
MySqlCommand _cmdGetAdUserCount = cmdGetAdUserCount.Clone() as MySqlCommand;
_cmdLoadAdUser.Connection = oc;
_cmdGetAdUserCount.Connection = oc;
try
{
_cmdLoadAdUser.Parameters["@PageIndex"].Value = pageIndex * pageSize;;
_cmdLoadAdUser.Parameters["@PageSize"].Value = (pageIndex + 1) * pageSize;;
_cmdLoadAdUser.Parameters["@UserAccount"].Value = UserAccount;
_cmdLoadAdUser.Parameters["@BabyBirthday"].Value = BabyBirthday;
_cmdLoadAdUser.Parameters["@State"].Value = State;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadAdUser.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new AdUser().BuildSampleEntity(reader));
}
returnValue.RecordsCount = Convert.ToInt32(_cmdGetAdUserCount.ExecuteScalar());
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadAdUser.Dispose();
_cmdLoadAdUser = null;
_cmdGetAdUserCount.Dispose();
_cmdGetAdUserCount = null;
GC.Collect();
}
return(returnValue);
}
public static void updateClickByOptionId(int id, int click)
{
try
{
MySqlConnection conn = new MySqlConnection(Config.sqlUrl);
conn.Open();
String sql = "update opt set click = @click where id = @id";
MySqlCommand comm = new MySqlCommand(sql, conn);
comm.Parameters.Add("id", id);
comm.Parameters.Add("click", click);
comm.ExecuteNonQuery();
comm.Clone();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <SysRoleFunction> Search(Int32 SysRoleFunctionId, Int32 RoleId, Int32 FunctionId, int pageIndex, int pageSize)
{
PageEntity <SysRoleFunction> returnValue = new PageEntity <SysRoleFunction>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadSysRoleFunction = cmdLoadSysRoleFunction.Clone() as MySqlCommand;
MySqlCommand _cmdGetSysRoleFunctionCount = cmdGetSysRoleFunctionCount.Clone() as MySqlCommand;
_cmdLoadSysRoleFunction.Connection = oc;
_cmdGetSysRoleFunctionCount.Connection = oc;
try
{
_cmdLoadSysRoleFunction.Parameters["@PageIndex"].Value = pageIndex;
_cmdLoadSysRoleFunction.Parameters["@PageSize"].Value = pageSize;
_cmdLoadSysRoleFunction.Parameters["@SysRoleFunctionId"].Value = SysRoleFunctionId;
_cmdLoadSysRoleFunction.Parameters["@RoleId"].Value = RoleId;
_cmdLoadSysRoleFunction.Parameters["@FunctionId"].Value = FunctionId;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadSysRoleFunction.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new SysRoleFunction().BuildSampleEntity(reader));
}
returnValue.RecordsCount = (int)_cmdGetSysRoleFunctionCount.ExecuteScalar();
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadSysRoleFunction.Dispose();
_cmdLoadSysRoleFunction = null;
_cmdGetSysRoleFunctionCount.Dispose();
_cmdGetSysRoleFunctionCount = null;
GC.Collect();
}
return(returnValue);
}
/// <summary>
/// 根据条件分页获取指定数据
/// <param name="pageIndex">当前页</param>
/// <para>索引从0开始</para>
/// <param name="pageSize">每页记录条数</param>
/// <para>记录数必须大于0</para>
/// </summary>
public PageEntity <MarketingVisit> Search(Int32 ChanceId, int pageIndex, int pageSize)
{
PageEntity <MarketingVisit> returnValue = new PageEntity <MarketingVisit>();
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdLoadMarketingVisit = cmdLoadMarketingVisit.Clone() as MySqlCommand;
MySqlCommand _cmdGetMarketingVisitCount = cmdGetMarketingVisitCount.Clone() as MySqlCommand;
_cmdLoadMarketingVisit.Connection = oc;
_cmdGetMarketingVisitCount.Connection = oc;
try
{
_cmdLoadMarketingVisit.Parameters["@PageIndex"].Value = pageIndex;
_cmdLoadMarketingVisit.Parameters["@PageSize"].Value = pageSize;
_cmdLoadMarketingVisit.Parameters["@ChanceId"].Value = ChanceId;
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
MySqlDataReader reader = _cmdLoadMarketingVisit.ExecuteReader();
while (reader.Read())
{
returnValue.Items.Add(new MarketingVisit().BuildSampleEntity(reader));
}
reader.Close();
_cmdGetMarketingVisitCount.Parameters["@ChanceId"].Value = ChanceId;
returnValue.RecordsCount = Convert.ToInt32(_cmdGetMarketingVisitCount.ExecuteScalar());
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdLoadMarketingVisit.Dispose();
_cmdLoadMarketingVisit = null;
_cmdGetMarketingVisitCount.Dispose();
_cmdGetMarketingVisitCount = null;
GC.Collect();
}
return(returnValue);
}
/// <summary>
/// 修改指定的数据
/// <param name="e">修改后的数据实体对象</param>
/// <para>数据对应的主键必须在实例中设置</para>
/// </summary>
public void Update(SysUser e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdUpdateSysUser = cmdUpdateSysUser.Clone() as MySqlCommand;
_cmdUpdateSysUser.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdUpdateSysUser.Parameters["@UserId"].Value = e.UserId;
_cmdUpdateSysUser.Parameters["@UserName"].Value = e.UserName;
_cmdUpdateSysUser.Parameters["@UserAccount"].Value = e.UserAccount;
_cmdUpdateSysUser.Parameters["@Pwd"].Value = e.Pwd;
_cmdUpdateSysUser.Parameters["@Mobile"].Value = e.Mobile;
_cmdUpdateSysUser.Parameters["@Email"].Value = e.Email;
_cmdUpdateSysUser.Parameters["@CreateTime"].Value = e.CreateTime;
_cmdUpdateSysUser.Parameters["@EndTime"].Value = e.EndTime;
_cmdUpdateSysUser.Parameters["@EntAdminId"].Value = e.EntAdminId;
_cmdUpdateSysUser.Parameters["@IsEnable"].Value = e.IsEnable;
_cmdUpdateSysUser.Parameters["@Type"].Value = e.Type;
_cmdUpdateSysUser.Parameters["@EntId"].Value = e.EntId;
_cmdUpdateSysUser.Parameters["@Sex"].Value = e.Sex;
_cmdUpdateSysUser.Parameters["@Birthday"].Value = e.Birthday;
_cmdUpdateSysUser.Parameters["@PicId"].Value = e.PicId;
_cmdUpdateSysUser.Parameters["@EntCode"].Value = e.EntCode;
_cmdUpdateSysUser.ExecuteNonQuery();
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdUpdateSysUser.Dispose();
_cmdUpdateSysUser = null;
GC.Collect();
}
}
public static Order AddOrder(int userid, int goodid, string cloudcode, string addtime, string orderid)
{
Order order = null;
MySqlConnection con = new MySqlConnection(Config_MySql.sqlUrl);
con.Open();
string sql = "insert into cloudorder(userid,goodid,cloudcode,addtime,orderid)values(@userid,@goodid,@cloudcode,@addtime,@orderid)";
MySqlCommand comm = new MySqlCommand(sql, con);
comm.Parameters.Add(new MySqlParameter("@userid", userid));
comm.Parameters.Add(new MySqlParameter("@goodid", goodid));
comm.Parameters.Add(new MySqlParameter("@cloudcode", cloudcode));
comm.Parameters.Add(new MySqlParameter("@addtime", addtime));
comm.Parameters.Add(new MySqlParameter("@orderid", orderid));
comm.ExecuteNonQuery();
comm.Clone();
con.Close();
return(order);
}
/// <summary>
/// 添加数据
/// <param name="es">数据实体对象数组</param>
/// <returns></returns>
/// </summary>
public int Insert(SysUser e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdInsertSysUser = cmdInsertSysUser.Clone() as MySqlCommand;
int returnValue = 0;
_cmdInsertSysUser.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdInsertSysUser.Parameters["@UserName"].Value = e.UserName;
_cmdInsertSysUser.Parameters["@UserAccount"].Value = e.UserAccount;
_cmdInsertSysUser.Parameters["@Pwd"].Value = e.Pwd;
_cmdInsertSysUser.Parameters["@Mobile"].Value = e.Mobile;
_cmdInsertSysUser.Parameters["@Email"].Value = e.Email;
_cmdInsertSysUser.Parameters["@CreateTime"].Value = e.CreateTime;
_cmdInsertSysUser.Parameters["@EndTime"].Value = e.EndTime;
_cmdInsertSysUser.Parameters["@EntAdminId"].Value = e.EntAdminId;
_cmdInsertSysUser.Parameters["@IsEnable"].Value = e.IsEnable;
_cmdInsertSysUser.Parameters["@Type"].Value = e.Type;
_cmdInsertSysUser.Parameters["@EntId"].Value = e.EntId;
_cmdInsertSysUser.Parameters["@Birthday"].Value = e.Birthday;
_cmdInsertSysUser.Parameters["@Sex"].Value = e.Sex;
_cmdInsertSysUser.Parameters["@PicId"].Value = e.PicId;
_cmdInsertSysUser.Parameters["@EntCode"].Value = e.EntCode;
_cmdInsertSysUser.ExecuteNonQuery();
returnValue = Convert.ToInt32(_cmdInsertSysUser.LastInsertedId);
return(returnValue);
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdInsertSysUser.Dispose();
_cmdInsertSysUser = null;
}
}
public string GetBookInfoDetail()
{
DataTable dt = new DataTable("MyTable");
string sql = "SELECT " +
" a.* " +
" FROM" +
" `SYS_BOOKINFO` a" +
" WHERE 1 = 1";
//" AND FromDate > @now";
try
{
using (MySqlConnection conn = new MySqlConnection())
{
conn.ConnectionString = DBCnstr;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(sql, conn);
//cmd.Parameters.Add(new MySqlParameter("@now", DateTime.Now));
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
cmd.Clone();
cmd.Dispose();
}
}
catch (Exception ex)
{
//發信
myClass.myClass.SendMail("發生錯誤", "Exception:" + ex.Message, new List <string>());
}
return(JsonConvert.SerializeObject(dt));
}
public bool DeleteBookInfo(string ID)
{
bool _result = false;
//DataTable dt = new DataTable("MyTable");
string sql = "Delete FROM `SYS_BOOKINFO` WHERE ID = @Id";
try
{
using (MySqlConnection conn = new MySqlConnection())
{
conn.ConnectionString = DBCnstr;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@Id", ID));
int effectRow = cmd.ExecuteNonQuery();
cmd.Clone();
cmd.Dispose();
if (effectRow > 0)
{
_result = true;
}
}
}
catch (Exception ex)
{
//發信
myClass.myClass.SendMail("發生錯誤", "Exception:" + ex.Message, new List <string>());
}
return(_result);
}
public static int getUserIdByEmail(string email)
{
MySqlConnection con = new MySqlConnection(Config_MySql.sqlUrl);
con.Open(); //进行数据库连接
string sql_email = "select * from user where email=@email";
MySqlCommand comm_email = new MySqlCommand(sql_email, con);
comm_email.Parameters.Add("email", email);
MySqlDataReader sdr_email = comm_email.ExecuteReader();
int id = -1;
if (sdr_email.Read())
{
id = sdr_email.GetInt32("id");
}
sdr_email.Close();
comm_email.Clone();
con.Close();
return(id);
}
public static int getUserIdByPhone(string phonenumber)
{
MySqlConnection con = new MySqlConnection(Config_MySql.sqlUrl);
con.Open(); //进行数据库连接
string sql_phone = "select * from user where phonenumber=@phonenumber";
MySqlCommand comm_phone = new MySqlCommand(sql_phone, con);
comm_phone.Parameters.Add("phonenumber", phonenumber);
MySqlDataReader sdr_phone = comm_phone.ExecuteReader();
int id = -1;
if (sdr_phone.Read())
{
id = sdr_phone.GetInt32("id");
}
sdr_phone.Close();
comm_phone.Clone();
con.Close();
return(id);
}
public static int addQuestion(int surveyId, String title, String type, int num)
{
try
{
MySqlConnection conn = new MySqlConnection(Config.sqlUrl);
conn.Open();
String sql = "insert into question (survey_id,title,type,num) values (@survey_id,@title,@type,@num)";
MySqlCommand comm = new MySqlCommand(sql, conn);
comm.Parameters.Add("survey_id", surveyId);
comm.Parameters.Add("title", title);
if (type.Equals("f"))
{
comm.Parameters.Add("type", 1);
}
else
{
comm.Parameters.Add("type", 0);
}
comm.Parameters.Add("num", num);
comm.ExecuteNonQuery();
sql = "select * from question order by id desc";
comm = new MySqlCommand(sql, conn);
MySqlDataReader sdr = comm.ExecuteReader();
if (sdr.Read())
{
return(sdr.GetInt32("id"));
}
sdr.Close();
comm.Clone();
conn.Close();
}
catch
{
}
return(0);
}
/// <summary>
/// 修改指定的数据
/// <param name="e">修改后的数据实体对象</param>
/// <para>数据对应的主键必须在实例中设置</para>
/// </summary>
public void Update(MarketingChance e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdUpdateMarketingChance = cmdUpdateMarketingChance.Clone() as MySqlCommand;
_cmdUpdateMarketingChance.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdUpdateMarketingChance.Parameters["@IdmarketingChance"].Value = e.IdmarketingChance;
_cmdUpdateMarketingChance.Parameters["@ChanceType"].Value = e.ChanceType;
_cmdUpdateMarketingChance.Parameters["@CustomerType"].Value = e.CustomerType;
_cmdUpdateMarketingChance.Parameters["@ContactName"].Value = e.ContactName;
_cmdUpdateMarketingChance.Parameters["@Remark"].Value = e.Remark;
_cmdUpdateMarketingChance.Parameters["@AddTime"].Value = e.AddTime;
_cmdUpdateMarketingChance.Parameters["@Qq"].Value = e.Qq;
_cmdUpdateMarketingChance.Parameters["@Email"].Value = e.Email;
_cmdUpdateMarketingChance.Parameters["@Tel"].Value = e.Tel;
_cmdUpdateMarketingChance.Parameters["@Phone"].Value = e.Phone;
_cmdUpdateMarketingChance.Parameters["@Rate"].Value = e.Rate;
_cmdUpdateMarketingChance.Parameters["@EntId"].Value = e.EntId;
_cmdUpdateMarketingChance.Parameters["@UserId"].Value = e.UserId;
_cmdUpdateMarketingChance.ExecuteNonQuery();
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdUpdateMarketingChance.Dispose();
_cmdUpdateMarketingChance = null;
GC.Collect();
}
}
/// <summary>
/// 修改指定的数据
/// <param name="e">修改后的数据实体对象</param>
/// <para>数据对应的主键必须在实例中设置</para>
/// </summary>
public void Update(CustomerPrivate e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdUpdateCustomerPrivate = cmdUpdateCustomerPrivate.Clone() as MySqlCommand;
_cmdUpdateCustomerPrivate.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdUpdateCustomerPrivate.Parameters["@CustomerPrivateId"].Value = e.CustomerPrivateId;
_cmdUpdateCustomerPrivate.Parameters["@Name"].Value = e.Name;
_cmdUpdateCustomerPrivate.Parameters["@Sex"].Value = e.Sex;
_cmdUpdateCustomerPrivate.Parameters["@Birthday"].Value = e.Birthday;
_cmdUpdateCustomerPrivate.Parameters["@Industy"].Value = e.Industy;
_cmdUpdateCustomerPrivate.Parameters["@Mobile"].Value = e.Mobile;
_cmdUpdateCustomerPrivate.Parameters["@Email"].Value = e.Email;
_cmdUpdateCustomerPrivate.Parameters["@Qq"].Value = e.Qq;
_cmdUpdateCustomerPrivate.Parameters["@Phone"].Value = e.Phone;
_cmdUpdateCustomerPrivate.Parameters["@Address"].Value = e.Address;
_cmdUpdateCustomerPrivate.Parameters["@Detail"].Value = e.Detail;
_cmdUpdateCustomerPrivate.Parameters["@EntId"].Value = e.EntId;
_cmdUpdateCustomerPrivate.Parameters["@OwnerId"].Value = e.OwnerId;
_cmdUpdateCustomerPrivate.ExecuteNonQuery();
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdUpdateCustomerPrivate.Dispose();
_cmdUpdateCustomerPrivate = null;
GC.Collect();
}
}
/// <summary>
/// 添加数据
/// <param name="es">数据实体对象数组</param>
/// <returns></returns>
/// </summary>
public int Insert(CustomerPrivate e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdInsertCustomerPrivate = cmdInsertCustomerPrivate.Clone() as MySqlCommand;
int returnValue = 0;
_cmdInsertCustomerPrivate.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdInsertCustomerPrivate.Parameters["@Name"].Value = e.Name;
_cmdInsertCustomerPrivate.Parameters["@Sex"].Value = e.Sex;
_cmdInsertCustomerPrivate.Parameters["@Birthday"].Value = e.Birthday;
_cmdInsertCustomerPrivate.Parameters["@Industy"].Value = e.Industy;
_cmdInsertCustomerPrivate.Parameters["@Mobile"].Value = e.Mobile;
_cmdInsertCustomerPrivate.Parameters["@Email"].Value = e.Email;
_cmdInsertCustomerPrivate.Parameters["@Qq"].Value = e.Qq;
_cmdInsertCustomerPrivate.Parameters["@Phone"].Value = e.Phone;
_cmdInsertCustomerPrivate.Parameters["@Address"].Value = e.Address;
_cmdInsertCustomerPrivate.Parameters["@Detail"].Value = e.Detail;
_cmdInsertCustomerPrivate.Parameters["@EntId"].Value = e.EntId;
_cmdInsertCustomerPrivate.Parameters["@OwnerId"].Value = e.OwnerId;
_cmdInsertCustomerPrivate.ExecuteNonQuery();
returnValue = Convert.ToInt32(_cmdInsertCustomerPrivate.LastInsertedId);
return(returnValue);
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdInsertCustomerPrivate.Dispose();
_cmdInsertCustomerPrivate = null;
}
}
public void CloneCommand()
{
using (var connection = new MySqlConnection(AppConfig.ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var param = new MySqlParameter("@param", MySqlDbType.Decimal)
{
Value = 12.3m
};
using (var cmd = new MySqlCommand("SELECT @param;", connection, transaction)
{
CommandType = CommandType.StoredProcedure,
Parameters = { param },
})
{
using (var cmd2 = (MySqlCommand)cmd.Clone())
{
Assert.Equal(cmd.Connection, cmd2.Connection);
Assert.Equal(cmd.Transaction, cmd2.Transaction);
Assert.Equal(cmd.CommandText, cmd2.CommandText);
Assert.Equal(cmd.CommandType, cmd2.CommandType);
Assert.Single(cmd2.Parameters);
var param2 = cmd2.Parameters[0];
Assert.Equal(param.ParameterName, param2.ParameterName);
Assert.Equal(param.MySqlDbType, param2.MySqlDbType);
Assert.Equal(param.Value, param2.Value);
cmd.CommandText = "New text";
Assert.NotEqual(cmd.CommandText, cmd2.CommandText);
param.Value = 0m;
Assert.NotEqual(0m, cmd2.Parameters[0].Value);
}
}
}
}
}
public bool Delete(string ID)
{
bool _result = false;
DataTable dt = new DataTable("MyTable");
string sql = "Delete FROM `SYS_PLACEINFO` WHERE ID = @Id";
try
{
using (MySqlConnection conn = new MySqlConnection())
{
conn.ConnectionString = DBCnstr;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@Id", ID));
int effectRow = cmd.ExecuteNonQuery();
cmd.Clone();
cmd.Dispose();
if (effectRow > 0)
{
_result = true;
}
}
}
catch (Exception ex)
{
//發信
}
return(_result);
}
public bool editar(Album objAlbum)
{
MySqlCommand mySql = null;
try
{
conexion();
String sql = "UPDATE Album SET titulo=@titulo, tipo=@tipo, noCanciones=@noCanciones, artista=@artista, srcPortada=@srcPortada, srcSpotify=@srcSpotify WHERE idAlbum=@idAlbum;";
mySql = new MySqlCommand(sql, conex);
mySql.Parameters.AddWithValue("@titulo", objAlbum.Titulo);
mySql.Parameters.AddWithValue("@tipo", objAlbum.Tipo);
mySql.Parameters.AddWithValue("@noCanciones", objAlbum.NoCanciones);
mySql.Parameters.AddWithValue("@artista", objAlbum.Artista);
mySql.Parameters.AddWithValue("@srcPortada", objAlbum.Portada);
mySql.Parameters.AddWithValue("@srcSpotify", objAlbum.SrcSpotify);
mySql.Parameters.AddWithValue("@idAlbum", objAlbum.idAlbum);
int id = Convert.ToInt32(mySql.ExecuteNonQuery());
if (id == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
return(false);
}
finally
{
if (mySql != null)
{
mySql.Dispose();
mySql.Clone();
}
}
}
/// <summary>
/// 添加数据
/// <param name="es">数据实体对象数组</param>
/// <returns></returns>
/// </summary>
public int Insert(CustomerEnt e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdInsertCustomerEnt = cmdInsertCustomerEnt.Clone() as MySqlCommand;
int returnValue = 0;
_cmdInsertCustomerEnt.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdInsertCustomerEnt.Parameters["@EntName"].Value = e.EntName;
_cmdInsertCustomerEnt.Parameters["@Industy"].Value = e.Industy;
_cmdInsertCustomerEnt.Parameters["@ContactUsername"].Value = e.ContactUsername;
_cmdInsertCustomerEnt.Parameters["@ContactMobile"].Value = e.ContactMobile;
_cmdInsertCustomerEnt.Parameters["@ContactPhone"].Value = e.ContactPhone;
_cmdInsertCustomerEnt.Parameters["@ContactEmail"].Value = e.ContactEmail;
_cmdInsertCustomerEnt.Parameters["@ContactQq"].Value = e.ContactQq;
_cmdInsertCustomerEnt.Parameters["@Address"].Value = e.Address;
_cmdInsertCustomerEnt.Parameters["@Detail"].Value = e.Detail;
_cmdInsertCustomerEnt.Parameters["@EntId"].Value = e.EntId;
_cmdInsertCustomerEnt.Parameters["@OwnerId"].Value = e.OwnerId;
_cmdInsertCustomerEnt.ExecuteNonQuery();
returnValue = Convert.ToInt32(_cmdInsertCustomerEnt.LastInsertedId);
return(returnValue);
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdInsertCustomerEnt.Dispose();
_cmdInsertCustomerEnt = null;
}
}
/// <summary>
/// 修改指定的数据
/// <param name="e">修改后的数据实体对象</param>
/// <para>数据对应的主键必须在实例中设置</para>
/// </summary>
public void Update(CustomerEnt e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdUpdateCustomerEnt = cmdUpdateCustomerEnt.Clone() as MySqlCommand;
_cmdUpdateCustomerEnt.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdUpdateCustomerEnt.Parameters["@CustomerEntId"].Value = e.CustomerEntId;
_cmdUpdateCustomerEnt.Parameters["@EntName"].Value = e.EntName;
_cmdUpdateCustomerEnt.Parameters["@Industy"].Value = e.Industy;
_cmdUpdateCustomerEnt.Parameters["@ContactUsername"].Value = e.ContactUsername;
_cmdUpdateCustomerEnt.Parameters["@ContactMobile"].Value = e.ContactMobile;
_cmdUpdateCustomerEnt.Parameters["@ContactPhone"].Value = e.ContactPhone;
_cmdUpdateCustomerEnt.Parameters["@ContactEmail"].Value = e.ContactEmail;
_cmdUpdateCustomerEnt.Parameters["@ContactQq"].Value = e.ContactQq;
_cmdUpdateCustomerEnt.Parameters["@Address"].Value = e.Address;
_cmdUpdateCustomerEnt.Parameters["@Detail"].Value = e.Detail;
_cmdUpdateCustomerEnt.Parameters["@EntId"].Value = e.EntId;
_cmdUpdateCustomerEnt.Parameters["@OwnerId"].Value = e.OwnerId;
_cmdUpdateCustomerEnt.ExecuteNonQuery();
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdUpdateCustomerEnt.Dispose();
_cmdUpdateCustomerEnt = null;
GC.Collect();
}
}
/// <summary>
/// 添加数据
/// <param name="es">数据实体对象数组</param>
/// <returns></returns>
/// </summary>
public int Insert(MarketingChance e)
{
MySqlConnection oc = ConnectManager.Create();
MySqlCommand _cmdInsertMarketingChance = cmdInsertMarketingChance.Clone() as MySqlCommand;
int returnValue = 0;
_cmdInsertMarketingChance.Connection = oc;
try
{
if (oc.State == ConnectionState.Closed)
{
oc.Open();
}
_cmdInsertMarketingChance.Parameters["@ChanceType"].Value = e.ChanceType;
_cmdInsertMarketingChance.Parameters["@CustomerType"].Value = e.CustomerType;
_cmdInsertMarketingChance.Parameters["@ContactName"].Value = e.ContactName;
_cmdInsertMarketingChance.Parameters["@Remark"].Value = e.Remark;
_cmdInsertMarketingChance.Parameters["@AddTime"].Value = e.AddTime;
_cmdInsertMarketingChance.Parameters["@Qq"].Value = e.Qq;
_cmdInsertMarketingChance.Parameters["@Email"].Value = e.Email;
_cmdInsertMarketingChance.Parameters["@Tel"].Value = e.Tel;
_cmdInsertMarketingChance.Parameters["@Phone"].Value = e.Phone;
_cmdInsertMarketingChance.Parameters["@Rate"].Value = e.Rate;
_cmdInsertMarketingChance.Parameters["@EntId"].Value = e.EntId;
_cmdInsertMarketingChance.Parameters["@UserId"].Value = e.UserId;
_cmdInsertMarketingChance.ExecuteNonQuery();
returnValue = Convert.ToInt32(_cmdInsertMarketingChance.LastInsertedId);
return(returnValue);
}
finally
{
oc.Close();
oc.Dispose();
oc = null;
_cmdInsertMarketingChance.Dispose();
_cmdInsertMarketingChance = null;
}
}
public void CloneCommand()
{
MySqlCommand cmd = new MySqlCommand();
MySqlCommand newCommand = cmd.Clone();
#if !RT
IDbCommand newCommand2 = (IDbCommand)(cmd as ICloneable).Clone();
#endif
}