public List <DEBIT_HIS> DataToDebit(DataTable dt) { List <DEBIT_HIS> list = new List <DEBIT_HIS>(); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { List <USER> lu = new T_USER_SQL(connstring).Get_User(dt.Rows[i]["U_SYSID"].ToString()); DEBIT_HIS d = new DEBIT_HIS { U_SYSID = dt.Rows[i]["U_SYSID"].ToString(), D_AMOUNT = dt.Rows[i]["D_AMOUNT"].ToString(), D_REASON = dt.Rows[i]["D_REASON"].ToString(), D_ISDEL = dt.Rows[i]["D_ISDEL"].ToString(), D_DATE = dt.Rows[i]["D_DATE"].ToString(), D_SYSID = dt.Rows[i]["D_SYSID"].ToString(), USER = lu.Count > 0 ? lu[0] : new USER(), NUM = dt.Rows[i]["NUM"].ToString() }; list.Add(d); } } return(list); }
public int Insert(DEBIT_HIS d) { using (SqlConnection conn = new SqlConnection(connstring)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SELECT MAX(D_SYSID) FROM T_DEBIT_HIS WHERE SUBSTRING(D_SYSID,1,8)='{0}';", DateTime.Now.ToString("yyyyMMdd")); string d_sysid = string.Empty; d_sysid = cmd.ExecuteScalar().ToString(); if (string.IsNullOrEmpty(d_sysid)) { d_sysid = DateTime.Now.ToString("yyyyMMdd") + "5000001"; } else { d_sysid = (Convert.ToInt64(d_sysid) + 1).ToString(); } cmd.CommandText = string.Format(@"INSERT INTO T_DEBIT_HIS (U_SYSID,D_REASON,D_AMOUNT,D_DATE,D_SYSID) VALUES ('{0}',N'{1}','{2}','{3}','{4}')", d.U_SYSID, d.D_REASON, d.D_AMOUNT, d.D_DATE, d_sysid); return(cmd.ExecuteNonQuery()); } } }
public int Debit_Amount(DEBIT_HIS d) { using (SqlConnection conn = new SqlConnection(connstring)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(@"UPDATE T_USER SET U_BALANCE=U_BALANCE-'{0}' WHERE U_SYSID='{1}' AND U_ISDEL='1' ", d.D_AMOUNT, d.U_SYSID); return(cmd.ExecuteNonQuery()); } } }
/// <summary> /// 更新 /// </summary> /// <param name="d"></param> /// <returns></returns> public int Update(DEBIT_HIS d) { using (SqlConnection conn = new SqlConnection(connstring)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(@"UPDATE T_DEBIT_HIS SET D_REASON=N'{0}',D_AMOUNT='{1}',D_DATE='{2}' WHERE D_SYSID='{3}' AND D_ISDEL='1' ", d.D_REASON, d.D_AMOUNT, d.D_DATE, d.D_SYSID); return(cmd.ExecuteNonQuery()); } } }
private void button1_Click(object sender, EventArgs e) { try { if (MessageBox.Show("查询全部信息消费5元", "查询提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK) { DEBIT_HIS d = new DEBIT_HIS(); d.U_SYSID = logonUser.U_SYSID; d.D_REASON = "平台内借款信息查询"; d.D_AMOUNT = "5"; d.D_DATE = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); string balance = user_sql.QueryByWhere_XP(string.Format(" AND U_SYSID='{0}'", logonUser.U_SYSID))[0].U_BALANCE; if (Convert.ToInt32(balance) < 5) { MessageBox.Show("余额不足", "查询提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { if (debit_his_sql.Insert(d) == 1) { user_sql.Debit_Amount(d); logonUser.U_BALANCE = (Convert.ToInt32(balance) - 5).ToString(); bindData(); } else { MessageBox.Show("失败,请重试", "查询信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } } catch (Exception e1) { MessageBox.Show(e1.Message, "报错", MessageBoxButtons.OK, MessageBoxIcon.Error); } }