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 }