/// <summary> /// 向数据库中插入一条新记录。 /// </summary> /// <param name="_CardSecretModel">CardSecret实体</param> /// <returns>新插入记录的编号</returns> public long Insert(CardSecretEntity _CardSecretModel) { string sqlStr = "insert into CardSecret([CardId],[ActId],[CardSecret],[IsUsed],[UsedBy],[SaleStatus],[Memo],[CreateTime],[UpdatTime],[IsActive]) values(@CardId,@ActId,@CardSecret,@IsUsed,@UsedBy,@SaleStatus,@Memo,@CreateTime,@UpdatTime,@IsActive) select @@identity"; long res; SqlParameter[] _param = { new SqlParameter("@CardId", SqlDbType.VarChar), new SqlParameter("@ActId", SqlDbType.BigInt), new SqlParameter("@CardSecret", SqlDbType.VarChar), new SqlParameter("@IsUsed", SqlDbType.Bit), new SqlParameter("@UsedBy", SqlDbType.VarChar), new SqlParameter("@SaleStatus", SqlDbType.SmallInt), new SqlParameter("@Memo", SqlDbType.VarChar), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@UpdatTime", SqlDbType.DateTime), new SqlParameter("@IsActive", SqlDbType.Bit) }; _param[0].Value = _CardSecretModel.CardId; _param[1].Value = _CardSecretModel.ActId; _param[2].Value = _CardSecretModel.CardSecret; _param[3].Value = _CardSecretModel.IsUsed; _param[4].Value = _CardSecretModel.UsedBy; _param[5].Value = _CardSecretModel.SaleStatus; _param[6].Value = _CardSecretModel.Memo; _param[7].Value = _CardSecretModel.CreateTime; _param[8].Value = _CardSecretModel.UpdatTime; _param[9].Value = _CardSecretModel.IsActive; res = Convert.ToInt64(SqlHelper.ExecuteScalar(SqlHelper.Connection, CommandType.Text, sqlStr, _param)); return(res); }
/// <summary> /// 向数据表CardSecret更新一条记录。 /// </summary> /// <param name="_CardSecretModel">_CardSecretModel</param> /// <returns>影响的行数</returns> public int Update(CardSecretEntity _CardSecretModel) { string sqlStr = "update CardSecret set [CardId]=@CardId,[ActId]=@ActId,[CardSecret]=@CardSecret,[IsUsed]=@IsUsed,[UsedBy]=@UsedBy,[SaleStatus]=@SaleStatus,[Memo]=@Memo,[CreateTime]=@CreateTime,[UpdatTime]=@UpdatTime,[IsActive]=@IsActive where Id=@Id"; SqlParameter[] _param = { new SqlParameter("@Id", SqlDbType.BigInt), new SqlParameter("@CardId", SqlDbType.VarChar), new SqlParameter("@ActId", SqlDbType.BigInt), new SqlParameter("@CardSecret", SqlDbType.VarChar), new SqlParameter("@IsUsed", SqlDbType.Bit), new SqlParameter("@UsedBy", SqlDbType.VarChar), new SqlParameter("@SaleStatus", SqlDbType.SmallInt), new SqlParameter("@Memo", SqlDbType.VarChar), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@UpdatTime", SqlDbType.DateTime), new SqlParameter("@IsActive", SqlDbType.Bit) }; _param[0].Value = _CardSecretModel.Id; _param[1].Value = _CardSecretModel.CardId; _param[2].Value = _CardSecretModel.ActId; _param[3].Value = _CardSecretModel.CardSecret; _param[4].Value = _CardSecretModel.IsUsed; _param[5].Value = _CardSecretModel.UsedBy; _param[6].Value = _CardSecretModel.SaleStatus; _param[7].Value = _CardSecretModel.Memo; _param[8].Value = _CardSecretModel.CreateTime; _param[9].Value = _CardSecretModel.UpdatTime; _param[10].Value = _CardSecretModel.IsActive; return(SqlHelper.ExecuteNonQuery(SqlHelper.Connection, CommandType.Text, sqlStr, _param)); }
/// <summary> /// 根据ID,返回一个CardSecret对象 /// </summary> /// <param name="id">id</param> /// <returns>CardSecret对象</returns> public CardSecretEntity Get_CardSecretEntity(long id) { CardSecretEntity _obj = null; SqlParameter[] _param = { new SqlParameter("@Id", SqlDbType.BigInt) }; _param[0].Value = id; string sqlStr = "select * from CardSecret with(nolock) where Id=@Id and isactive=1 "; using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.Connection, CommandType.Text, sqlStr, _param)) { while (dr.Read()) { _obj = Populate_CardSecretEntity_FromDr(dr); } if (!dr.IsClosed) { dr.Close(); } } return(_obj); }
/// <summary> /// 得到 cardsecret 数据实体 /// </summary> /// <param name="dr">dr</param> /// <returns>cardsecret 数据实体</returns> public CardSecretEntity Populate_CardSecretEntity_FromDr(IDataReader dr) { CardSecretEntity Obj = new CardSecretEntity(); Obj.Id = ((dr["Id"]) == DBNull.Value) ? 0 : (long)dr["Id"]; Obj.CardId = dr["CardId"].ToString(); Obj.ActId = ((dr["ActId"]) == DBNull.Value) ? 0 : (long)dr["ActId"]; Obj.CardSecret = dr["CardSecret"].ToString(); Obj.IsUsed = ((dr["IsUsed"]) == DBNull.Value) ? false : Convert.ToBoolean(dr["IsUsed"]); Obj.UsedBy = dr["UsedBy"].ToString(); Obj.SaleStatus = ((dr["SaleStatus"]) == DBNull.Value) ? (short)0 : (short)dr["SaleStatus"]; Obj.Memo = dr["Memo"].ToString(); Obj.CreateTime = ((dr["CreateTime"]) == DBNull.Value) ? Convert.ToDateTime("1900-1-1") : Convert.ToDateTime(dr["CreateTime"]); Obj.UpdatTime = ((dr["UpdatTime"]) == DBNull.Value) ? Convert.ToDateTime("1900-1-1") : Convert.ToDateTime(dr["UpdatTime"]); Obj.IsActive = ((dr["IsActive"]) == DBNull.Value) ? false : Convert.ToBoolean(dr["IsActive"]); return(Obj); }
/// <summary> /// 通用单表列表翻页+where字符串拼接查询。(op后台使用,不适合高并发因为拼接sql没有参数化) /// </summary> /// <param name="pageCount">记录数</param> /// <param name="pageIndex">页号</param> /// <param name="pageSize">页码</param> /// <param name="where">查询条件</param> /// <param name="orderField">排序字段</param> /// <param name="isDesc">排序规则(true正序false倒序)</param> /// <returns></returns> public IList <CardSecretEntity> Search(out int pageCount, int pageIndex, int pageSize, string where, string orderField, bool isDesc) { IList <CardSecretEntity> list = new List <CardSecretEntity>(); SqlParameter[] _param = { new SqlParameter("@pageIndex", SqlDbType.Int), new SqlParameter("@pageSize", SqlDbType.Int), new SqlParameter("@strWhere", SqlDbType.VarChar), new SqlParameter("@fldName", SqlDbType.VarChar), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@tblName", SqlDbType.VarChar), new SqlParameter("@strGetFields", SqlDbType.VarChar), new SqlParameter("@pageCount", SqlDbType.Int) }; _param[0].Value = pageIndex; _param[1].Value = pageSize; _param[2].Value = where; _param[3].Value = orderField; _param[4].Value = isDesc; _param[5].Value = "CardSecret"; _param[6].Value = "*"; _param[7].Direction = ParameterDirection.Output; using (IDataReader dr = SqlHelper.ExecuteReader(SqlHelper.Connection, CommandType.StoredProcedure, "sp_OF_Page", _param)) { while (dr.Read()) { CardSecretEntity info = Populate_CardSecretEntity_FromDr(dr); list.Add(info); } if (!dr.IsClosed) { dr.Close(); } pageCount = Convert.ToInt32(_param[7].Value.ToString()); } return(list); }
/// <summary> /// 得到 cardsecret 数据实体 /// </summary> /// <param name="row">row</param> /// <returns>cardsecret 数据实体</returns> public CardSecretEntity Populate_CardSecretEntity_FromDr(DataRow row) { CardSecretEntity Obj = new CardSecretEntity(); if (row != null) { Obj.Id = ((row["Id"]) == DBNull.Value) ? 0 : (long)row["Id"]; Obj.CardId = row["CardId"].ToString(); Obj.ActId = ((row["ActId"]) == DBNull.Value) ? 0 : (long)row["ActId"]; Obj.CardSecret = row["CardSecret"].ToString(); Obj.IsUsed = ((row["IsUsed"]) == DBNull.Value) ? false : Convert.ToBoolean(row["IsUsed"]); Obj.UsedBy = row["UsedBy"].ToString(); Obj.SaleStatus = ((row["SaleStatus"]) == DBNull.Value) ? (short)0 : (short)row["SaleStatus"]; Obj.Memo = row["Memo"].ToString(); Obj.CreateTime = ((row["CreateTime"]) == DBNull.Value) ? Convert.ToDateTime("1900-1-1") : Convert.ToDateTime(row["CreateTime"]); Obj.UpdatTime = ((row["UpdatTime"]) == DBNull.Value) ? Convert.ToDateTime("1900-1-1") : Convert.ToDateTime(row["UpdatTime"]); Obj.IsActive = ((row["IsActive"]) == DBNull.Value) ? false : Convert.ToBoolean(row["IsActive"]); } else { return(null); } return(Obj); }
public long Update(CardSecretEntity cardSecretEntity) { return(cardSecretdal.Update(cardSecretEntity)); }
public long Insert(CardSecretEntity cardSecretEntity) { return(cardSecretdal.Insert(cardSecretEntity)); }
public ActionResult Upload(HttpPostedFileBase filebase, FormCollection form) { HttpPostedFileBase file = Request.Files["excel"];//获取上传的文件 string FileName; string savePath; if (file == null || file.ContentLength <= 0 || form.Count == 0) { ViewBag.error = "文件或节目类型不能为空"; return(Redirect("/Card/Index")); } else { string filename = Path.GetFileName(file.FileName); int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte string fileEx = Path.GetExtension(filename); //获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(filename); //获取无扩展名的文件名 int Maxsize = 10000 * 1024; //定义上传文件的最大空间大小为10M string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串 FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx; if (!FileType.Contains(fileEx)) { ViewBag.error = "文件类型错误,只能导入xls和xlsx格式的文件"; return(View()); } if (filesize >= Maxsize) { ViewBag.error = "上传文件超过10M,不能上传"; return(View()); } string path = AppDomain.CurrentDomain.BaseDirectory + "Uploads\\Excel\\"; savePath = Path.Combine(path, FileName); file.SaveAs(savePath); } DataSet myDataSet = new DataSet(); try { //连接串 string strConn = string.Format(SqlHelper.OledbConnection, savePath); OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等 DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //包含excel中表名的字符串数组 string[] strTableNames = new string[dtSheetName.Rows.Count]; for (int k = 0; k < dtSheetName.Rows.Count; k++) { strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); } OleDbDataAdapter myCommand = null; DataTable dt = new DataTable(); //从指定的表明查询数据,可先把所有表明列出来供用户选择 string strExcel = "select*from[" + strTableNames[0] + "]"; myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(myDataSet, "ExcelInfo"); } catch (Exception ex) { ViewBag.error = ex.Message; return(Redirect("/Card/Index")); } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); for (int i = 0; i < table.Rows.Count; i++) { CardSecretEntity data = new CardSecretEntity(); data.CardId = table.Rows[i][0].ToString(); data.CardSecret = table.Rows[i][1].ToString(); data.IsUsed = false; data.UsedBy = string.Empty; data.SaleStatus = 1; data.CreateTime = DateTime.Now; data.UpdatTime = DateTime.Now; data.IsActive = true; data.ActId = long.Parse(form["CardId"]); CardSecretBll.GetInstance().Insert(data); } ViewBag.error = "上传成功"; System.Threading.Thread.Sleep(500); return(Redirect("/Card/Index")); }