public static IList <clsTipoDestinoCarga> ListarTiposDestinoCarga(string descripcion) { IList <clsTipoDestinoCarga> lista = new List <clsTipoDestinoCarga>(); clsTipoDestinoCarga destino; SqlDataReader objReader = null; SqlParameter[] objParams; try { objParams = SqlHelperParameterCache.GetSpParameterSet(BaseDatos.GetConexion(), "SP_C_TIPO_DESTINO_CARGA"); objParams[0].Value = descripcion; objReader = SqlHelper.ExecuteReader(BaseDatos.GetConexion(), "SP_C_TIPO_DESTINO_CARGA", objParams); while (objReader.Read()) { destino = new clsTipoDestinoCarga(); destino.Id = Convert.ToInt64(objReader["Id"]); destino.Nombre = objReader["Descripcion"].ToString(); destino.Usuario.Id = Convert.ToInt64(objReader["IdUsuario"]); destino.Usuario.Nombre = objReader["Nombres"].ToString(); destino.Usuario.ApellidoPaterno = objReader["ApellidoPaterno"].ToString(); destino.Usuario.ApellidoMaterno = objReader["ApellidoMaterno"].ToString(); destino.FechaCreacion = Convert.ToDateTime(objReader["FechaCreacion"]); lista.Add(destino); } } catch (Exception ex) { Base.Log.Log.EscribirLog(ex.Message); return(null); } finally { if (objReader != null) { objReader.Close(); } } return(lista); }
/// <summary> /// 获取一个给SQL语句传参用的参数集合 /// </summary> /// <returns>SqlParameter[] 集合</returns> public override SqlParameter[] GetParms() { try { var parms = SqlHelperParameterCache.GetCachedParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmDepartment_Insert); if (parms == null) { parms = new[] { new SqlParameter(Parm_CrmDepartment_CompanyId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_ParentId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Code, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_FullName, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_ShortName, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Nature, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Manager, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Phone, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Fax, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Email, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Remark, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Enabled, SqlDbType.Int, 10), new SqlParameter(Parm_CrmDepartment_SortCode, SqlDbType.Int, 10), new SqlParameter(Parm_CrmDepartment_RowStatus, SqlDbType.Int, 10), new SqlParameter(Parm_CrmDepartment_CreatorId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_CreateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_CreateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDepartment_UpdateId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_UpdateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_UpdateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDepartment_Address, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDepartment_Id, SqlDbType.NVarChar, 50) }; SqlHelperParameterCache.CacheParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmDepartment_Insert, parms); } return(parms); } catch (Exception e) { throw new Exception("获取查询参数时出错!", e); } }
//********************************************************************* // // Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in // the connection string using the provided parameter values. This method will query the database to discover the parameters for the // stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. // // This method provides no access to output parameters or the stored procedure's return value parameter. // // e.g.: // int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); // // param name="connectionString" a valid connection string for a SqlConnection // param name="spName" the name of the stored procedure // param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure // returns an object containing the value in the 1x1 resultset generated by the command // //********************************************************************* public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return(ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters)); } //otherwise we can just call the SP without params else { return(ExecuteScalar(connectionString, CommandType.StoredProcedure, spName)); } }
public DataModule.UserInfo GetUserInfoByUserID(int userID) { string sql = "SELECT * FROM Tb_UserInfo WHERE UserID=@UserID"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.BigInt) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = userID; using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms)) { return(FillUserInfo(new RowHelper(dr))); } }
public bool IsExistsNickName(string nickName) { bool result = true; string sql = "SELECT COUNT(UserID) FROM Tb_UserInfo WHERE NickName=@NickName"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@NickName", SqlDbType.NVarChar, 20) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = nickName; int rows = Convert.ToInt32(SqlHelper.ExecuteScalar(connString, CommandType.Text, sql, parms)); result = rows > 0; return(result); }
public PagedResult <ActualsAssociate> GetActualsAssociateList(int typeId, string actualsName, string stockName, int?page, int rows) { PagedResult <ActualsAssociate> pagest = new PagedResult <ActualsAssociate>(); SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(SqlConnectFactory.CMS, up_ActualsAssociatePageList); commandParameters[0].Value = rows; //pageSize 每页显示记录数 commandParameters[1].Value = page; //pageindex 当前页码 commandParameters[2].Value = actualsName; commandParameters[3].Value = stockName; commandParameters[4].Value = typeId; using (DataSet ds = SqlHelper.ExecuteDataset(SqlConnectFactory.CMS, CommandType.StoredProcedure, up_ActualsAssociatePageList, commandParameters)) { if (ds != null && ds.Tables.Count > 0) { pagest.Result = EntityHelper.GetEntityListByDT <ActualsAssociate>(ds.Tables[0], null); } pagest.Total = int.Parse(commandParameters[5].Value.ToString()); } return(pagest); }
/// <summary> /// 检查用户名是否存在 /// </summary> /// <param name="userName"></param> /// <returns></returns> public bool IsExistsUserName(string userName) { bool result = true; string sql = "select count(UserID) from Tb_User where UserName=@UserName"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@UserName", SqlDbType.NVarChar, 15) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = userName; int rows = Convert.ToInt32(SqlHelper.ExecuteScalar(connString, CommandType.Text, sql, parms)); result = rows > 0; return(result); }
public void InsertUserInfo(DataModule.UserInfo userInfo) { string sql = "INSERT INTO Tb_UserInfo (UserID, Email, NickName, Sex, BirthDay, Age) values (@UserID, @Email, @NickName, @Sex, @BirthDay, @Age)"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.BigInt), new SqlParameter("@Email", SqlDbType.NVarChar, 50), new SqlParameter("@NickName", SqlDbType.NVarChar, 20), new SqlParameter("@Sex", SqlDbType.SmallInt), new SqlParameter("@BirthDay", SqlDbType.Date), new SqlParameter("@Age", SqlDbType.Int) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sql, parms); }
/// <summary> /// 判断开仓的商品已开仓过没平仓 /// </summary> /// <param name="ActualCode"></param> /// <param name="AnalystId"></param> /// <returns></returns> //public bool IsSell(string ActualCode, int AnalystId) //{ // string sql; // List<AnalystLive> list; // sql = "select LiveId from AnalystLive where ActualCode=@ActualCode and AnalystId=@AnalystId and IsSell=0"; // SqlParameter[] parsms = new SqlParameter[] { new SqlParameter("@ActualCode", ActualCode), new SqlParameter("@AnalystId", AnalystId) }; // using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlConnectFactory.CMS, CommandType.Text, sql, parsms)) // { // list = EntityHelper.GetEntityListByDataReader<AnalystLive>(dr, null); // if (list != null) // { // if (list.Count > 0) // { // return true; // } // else // { // return false; // } // } // return false; // } //} /// <summary> /// 信息列表查询 /// </summary> /// <param name="page"></param> /// <param name="rows"></param> /// <param name="txtKeyword"></param> /// <param name="txtStartDate"></param> /// <param name="txtEndDate"></param> /// <param name="ChannelId"></param> /// <param name="status">值为1000时,就是全选</param> /// <param name="totalFlag">是否查询总数的标志,1:查询总数,0:不查询总数</param> /// <param name="sort">0-空文章,1-新文章,2-图片文章。值为1000时,就全选</param> /// <returns></returns> public PagedResult <AnalystLive> GetAnalystLiveList(int?page, int rows, int AnalystId, int totalFlag, int AnalystType) { PagedResult <AnalystLive> pagest = new PagedResult <AnalystLive>(); SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(SqlConnectFactory.CMS, up_AnalystLive_GetPageList); commandParameters[0].Value = rows; //pageSize 每页显示记录数 commandParameters[1].Value = page; //pageindex 当前页码 commandParameters[2].Value = AnalystId; //分析师ID commandParameters[3].Value = totalFlag; commandParameters[4].Value = AnalystType; using (DataSet ds = SqlHelper.ExecuteDataset(SqlConnectFactory.CMS, CommandType.StoredProcedure, up_AnalystLive_GetPageList, commandParameters)) { if (ds != null && ds.Tables.Count > 0) { pagest.Result = EntityHelper.GetEntityListByDT <AnalystLive>(ds.Tables[0], null); } pagest.Total = int.Parse(commandParameters[5].Value.ToString()); } return(pagest); }
public DataModule.UserInfo GetUserInfoByNickName(string nickName) { string sql = "SELECT * FROM Tb_UserInfo WHERE NickName=@NickName"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@NickName", SqlDbType.NVarChar, 20) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = nickName; using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms)) { return(FillUserInfo(new RowHelper(dr))); } }
public static void OverPlantPost(int cropYear, int factoryNumber, string userName) { const string METHOD_NAME = "OverPlantPost"; try { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) { string procName = "bawpOverPlantFactoryPost"; if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.SqlClient.SqlParameter[] spParams = SqlHelperParameterCache.GetSpParameterSet(conn, procName, false); spParams[0].Value = cropYear; spParams[1].Value = factoryNumber; spParams[2].Value = userName; try { SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procName, spParams); } catch (SqlException sqlEx) { if (sqlEx.Number == Convert.ToInt32(WSCIEMP.Common.CException.KnownError.DataWarning)) { WSCIEMP.Common.CWarning wscWarn = new WSCIEMP.Common.CWarning(sqlEx.Message, sqlEx); throw (wscWarn); } else { throw (sqlEx); } } } } catch (System.Exception ex) { string errMsg = MOD_NAME + METHOD_NAME; WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex); throw (wscEx); } }
/// <summary> /// 获取一个给SQL语句传参用的参数集合 /// </summary> /// <returns>SqlParameter[] 集合</returns> public override SqlParameter[] GetParms() { try { var parms = SqlHelperParameterCache.GetCachedParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmMessageWork_Insert); if (parms == null) { parms = new[] { new SqlParameter(Parm_CrmMessageWork_Titles, SqlDbType.NVarChar, 400), new SqlParameter(Parm_CrmMessageWork_ProcessInstanceID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_SendActivityID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_ActivityInstanceID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_StartDate, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmMessageWork_SenderID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_ExecuteDate, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmMessageWork_ActionerID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_FormID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_FormData, SqlDbType.Xml, -1), new SqlParameter(Parm_CrmMessageWork_FormAddress, SqlDbType.NVarChar, 200), new SqlParameter(Parm_CrmMessageWork_ContentTypeID, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_IsReply, SqlDbType.Int, 10), new SqlParameter(Parm_CrmMessageWork_State, SqlDbType.Int, 10), new SqlParameter(Parm_CrmMessageWork_Note, SqlDbType.NVarChar, 100), new SqlParameter(Parm_CrmMessageWork_RowStatus, SqlDbType.Int, 10), new SqlParameter(Parm_CrmMessageWork_CreatorId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_CreateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_CreateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmMessageWork_UpdateId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_UpdateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmMessageWork_UpdateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmMessageWork_Id, SqlDbType.NVarChar, 50) }; SqlHelperParameterCache.CacheParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmMessageWork_Insert, parms); } return(parms); } catch (Exception e) { throw new Exception("获取查询参数时出错!", e); } }
public IList <UserFirend> GetFirendListByUserID(int userID) { IList <UserFirend> result = new List <UserFirend>(); string sql = @"SELECT TOP (100) PERCENT dbo.Tb_UserFriend.*, dbo.Tb_UserInfo.NickName AS UNickName, dbo.Tb_UserGroup.GroupName, dbo.Tb_UserGroup.SortNum FROM dbo.Tb_UserFriend INNER JOIN dbo.Tb_UserInfo ON dbo.Tb_UserFriend.FriendID = dbo.Tb_UserInfo.UserID INNER JOIN dbo.Tb_UserGroup ON dbo.Tb_UserFriend.GroupID = dbo.Tb_UserGroup.ID where dbo.Tb_UserFriend.UserID=@UserID ORDER BY dbo.Tb_UserGroup.SortNum DESC"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.BigInt) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = userID; using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms)) { RowHelper row = new RowHelper(dr); while (row.Read()) { UserFirend ufInfo = new UserFirend(); ufInfo.ID = row.GetInt32("ID"); ufInfo.FriendID = row.GetInt32("FriendID"); ufInfo.GroupID = row.GetInt32("GroupID"); ufInfo.GroupName = row.GetString("GroupName"); ufInfo.NickName = string.IsNullOrEmpty(row.GetString("NickName")) ? row.GetString("UNickName") : row.GetString("NickName"); ufInfo.Subscription = row.GetString("Subscription"); ufInfo.UserID = row.GetInt32("UserID"); result.Add(ufInfo); } dr.Close(); } return(result); }
public static void GetSendRptOption(int memberID, ref string sendRptOption) { try { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) { string procName = "bawpUserGetSendRptOption"; if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.SqlClient.SqlParameter[] spParams = SqlHelperParameterCache.GetSpParameterSet(conn, procName, false); spParams[0].Value = memberID; SetTimeout(); SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procName, spParams); sendRptOption = spParams[1].Value.ToString(); } } catch (SqlException sqlEx) { if (sqlEx.Number == Convert.ToInt32(WSCIEMP.Common.CException.KnownError.DataWarning)) { WSCIEMP.Common.CWarning wscWarn = new WSCIEMP.Common.CWarning(sqlEx.Message, sqlEx); throw (wscWarn); } else { string errMsg = MOD_NAME + "GetSendRptOption"; WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, sqlEx); throw (wscEx); } } catch (System.Exception e) { string errMsg = MOD_NAME + "GetSendRptOption"; WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, e); throw (wscEx); } }
public void UpdateFriendNickName(int userID, int friendID, string nickName) { string sql = "Update Tb_UserFriend set NickName=@NickName where UserID=@UserID and FriendID=@FriendID"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@NickName", SqlDbType.NVarChar, 20), new SqlParameter("@UserID", SqlDbType.BigInt), new SqlParameter("@FriendID", SqlDbType.BigInt) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = nickName; parms[1].Value = userID; parms[2].Value = friendID; SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sql, parms); }
public DataSet ListarCajas(pruebaEntidad caja) { string sqlConnString = _sConexion; string spName = "spr_listarCajas"; DataSet ds = new DataSet(); try { SqlParameter[] storedParms = new SqlParameter[3]; storedParms = SqlHelperParameterCache.GetSpParameterSet(sqlConnString, spName); storedParms[0].Value = caja.Estatus; storedParms[1].Value = caja.CodigoUsuario; storedParms[2].Value = caja.CodigoEmpresa; ds = SqlHelper.ExecuteDataset(sqlConnString, System.Data.CommandType.StoredProcedure, spName, storedParms); } catch (Exception e) { throw new Exception(e.Message); } return(ds); }
public void UpdateUserLoginInfo(User user) { string sql = "Update Tb_User set LastLoginIP=@LastLoginIP,LastLoginTime=getdate(),Server=@Server where UserID=@UserID"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@LastLoginIP", SqlDbType.NVarChar, 20), new SqlParameter("@Server", SqlDbType.NVarChar, 50), new SqlParameter("@UserID", SqlDbType.BigInt) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = user.LastLoginIP; parms[1].Value = user.Server; parms[2].Value = user.UserID; SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sql, parms); }
public void UpdateFriendSubscription(int userID, int friendID, string subscription) { string sql = "Update Tb_UserFriend set Subscription=@Subscription where UserID=@UserID and FriendID=@FriendID"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@Subscription", SqlDbType.NVarChar, 10), new SqlParameter("@UserID", SqlDbType.BigInt), new SqlParameter("@FriendID", SqlDbType.BigInt) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = subscription; parms[1].Value = userID; parms[2].Value = friendID; SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sql, parms); }
public void UpdateGroupSortNum(int userID, int id, int sortNum) { string sql = "sp_update_group_sort"; SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql); if (parms == null) { parms = new SqlParameter[] { new SqlParameter("@userid", SqlDbType.Int), new SqlParameter("@id", SqlDbType.Int), new SqlParameter("@sort", SqlDbType.Int) }; SqlHelperParameterCache.CacheParameterSet(sql, parms); } parms[0].Value = userID; parms[1].Value = id; parms[2].Value = sortNum; SqlHelper.ExecuteNonQuery(connString, CommandType.StoredProcedure, sql, parms); }
public override int PostAdd(Post post) { string spName = "Post_Add"; SqlParameter[] parameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(parameters, new object[] { post.PostID, post.Title, post.Body, post.Publish }); //Get the parameters from memory int result = SqlHelper.ExecuteNonQuery(connectionString, System.Data.CommandType.StoredProcedure, spName, parameters); if (result > 0) { foreach (var item in parameters) { if (string.Compare(item.ParameterName, "@PostID", true) == 0) { return((int)item.Value); } } } return(result); }
public static ResultadoTransaccion ListarEstadosCotizacionDirecta() { ResultadoTransaccion res = new ResultadoTransaccion(); IList <Estado> list = new List <Estado>(); //Abrir Conexion var conn = BaseDatos.NuevaConexion(); try { SqlCommand command = new SqlCommand("SP_L_COTIZACION_DIRECTA_ESTADOS", conn); objParams = SqlHelperParameterCache.GetSpParameterSet(conn, "SP_L_COTIZACION_DIRECTA_ESTADOS"); command.Transaction = conn.BeginTransaction(); command.CommandType = CommandType.StoredProcedure; var reader = command.ExecuteReader(); while (reader.Read()) { list.Add(GetFromDataReader(reader)); } res.Accion = Entidades.Enums.Enums.AccionTransaccion.Consultar; res.ObjetoTransaccion = list; res.Descripcion = "Listado Cotizaciones Directas Estados"; } catch (Exception ex) { Log.EscribirLog(ex.Message); res.Descripcion = ex.Message; res.ArchivoError = NombreClase; res.MetodoError = MethodBase.GetCurrentMethod().Name; } finally { conn.Close(); } return(res); }
public static ResultadoTransaccion ListarTodosLasCotizacionesPorUsuario(Int64 usuario) { ResultadoTransaccion res = new ResultadoTransaccion(); try { //Abrir Conexion conn = BaseDatos.Conexion(); //Consultar Asuntos x Tipo Actividad objParams = SqlHelperParameterCache.GetSpParameterSet(conn, "SP_L_Cotizacion_Cotizaciones"); SqlCommand command = new SqlCommand("SP_L_Cotizacion_Cotizaciones", conn); command.Transaction = transaction; objParams[0].Value = usuario; //objParams[1].Value = ClsTipoCotizacion.Tipo.SolicitudDeTarifa;//IdTipoCotizacin command.Parameters.AddRange(objParams); command.CommandType = CommandType.StoredProcedure; dreader = command.ExecuteReader(); res.Accion = Entidades.Enums.Enums.AccionTransaccion.Consultar; res.ObjetoTransaccion = GetFromDataReader(dreader); } catch (Exception ex) { Log.EscribirLog(ex.Message); res.Descripcion = ex.Message; res.ArchivoError = "ClsCotizacionADO"; res.MetodoError = "SP_L_Cotizacion_Cotizaciones"; } finally { conn.Close(); } return(res); }
/// <summary> /// Fills a single instance with data based on its primary key values. /// </summary> public virtual void Fill(PropertyRightsType prt, System.Int16 id) { // create the connection to use SqlConnection cnn = new SqlConnection(PropertyRightsType.GetConnectionString()); try { // discover the sql parameters SqlParameterHash sqlparams = SqlHelperParameterCache.GetSpParameterSet(PropertyRightsType.GetConnectionString(), "gsp_SelectPropertyRightsType"); using (cnn) { // open the connection cnn.Open(); // set the parameters sqlparams["@id"].Value = id; // create an instance of the reader to fill. SqlDataReader datareader = SqlHelper.ExecuteReader(cnn, "gsp_SelectPropertyRightsType", sqlparams); if (datareader.Read()) prt.SetMembers(ref datareader); cnn.Close(); // close the connection } // nullify the connection var cnn = null; } catch (SqlException sqlex) { throw sqlex; } }
/// <summary> /// 获取一个给SQL语句传参用的参数集合 /// </summary> /// <returns>SqlParameter[] 集合</returns> public override SqlParameter[] GetParms() { try { var parms = SqlHelperParameterCache.GetCachedParameterSet(SqlHelper.SqlConnStringWrite, Sql_ComMenu_Insert); if (parms == null) { parms = new[] { new SqlParameter(Parm_ComMenu_MenuName, SqlDbType.NVarChar, 100), new SqlParameter(Parm_ComMenu_ParentMenuId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_ComMenu_Controller, SqlDbType.NVarChar, 100), new SqlParameter(Parm_ComMenu_Action, SqlDbType.NVarChar, 100), new SqlParameter(Parm_ComMenu_MenuUrl, SqlDbType.NVarChar, 200), new SqlParameter(Parm_ComMenu_MenuLevel, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_IsMenu, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_MenuDesc, SqlDbType.NVarChar, 200), new SqlParameter(Parm_ComMenu_IconPic, SqlDbType.NVarChar, 100), new SqlParameter(Parm_ComMenu_OpenType, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_SortCode, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_Source, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_RowStatus, SqlDbType.Int, 10), new SqlParameter(Parm_ComMenu_CreatorId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_ComMenu_CreateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_ComMenu_CreateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_ComMenu_UpdateId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_ComMenu_UpdateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_ComMenu_UpdateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_ComMenu_Id, SqlDbType.NVarChar, 50) }; SqlHelperParameterCache.CacheParameterSet(SqlHelper.SqlConnStringWrite, Sql_ComMenu_Insert, parms); } return(parms); } catch (Exception e) { throw new Exception("获取查询参数时出错!", e); } }
public static void EquityDeductionDelete(int equityDeductionID, string rowVersion) { const string METHOD_NAME = "EquityDeductionDelete"; try { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) { string procName = "bawpEquityDeductionDelete"; if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.SqlClient.SqlParameter[] spParams = SqlHelperParameterCache.GetSpParameterSet(conn, procName, false); spParams[0].Value = equityDeductionID; spParams[1].Value = rowVersion; SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procName, spParams); } } catch (SqlException sqlEx) { if (sqlEx.Number == Convert.ToInt32(WSCIEMP.Common.CException.KnownError.DataWarning)) { WSCIEMP.Common.CWarning wscWarn = new WSCIEMP.Common.CWarning(sqlEx.Message, sqlEx); throw (wscWarn); } else { string errMsg = MOD_NAME + METHOD_NAME; WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, sqlEx); throw (wscEx); } } catch (System.Exception e) { string errMsg = MOD_NAME + METHOD_NAME; WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, e); throw (wscEx); } }
private SqlParameter[] BindParameters(object inputObject, string procedureName) { //Get the type of the inputObject (used later in the function) Type inputType = inputObject.GetType(); //Get the parameters for the procedure using the Data Access Block SqlParameter[] parameters = SqlHelperParameterCache.GetSpParameterSet(Conn, procedureName); //Loop through the parameters and find all parameters of the procedure // that match properties of our inputObject. foreach (SqlParameter parameter in parameters) { //Get the property. The SqlParameter.ParameterName will include the "@" // sign so we must remove it. Notice that the parameters of the stored // procedure must exactly match the property values. It is possible to // map properties of one name to parameters of another name, and that // will likely be covered in a future article. PropertyInfo property = inputType.GetProperty(parameter.ParameterName.Replace("@", "")); if (property == null) { property = inputType.GetProperty(parameter.ParameterName.Replace("@", "").ToUpper()); } if (property == null) { property = inputType.GetProperty(parameter.ParameterName.Replace("@", "").ToLower()); } //If the property doesn't exist, we will have a null property reference, // so we have to check for that here before setting the parameter's value // from the property value. if (property != null) { parameter.Value = property.GetValue(inputObject, null); } } return(parameters); }
/// <summary> /// 获取一个给SQL语句传参用的参数集合 /// </summary> /// <returns>SqlParameter[] 集合</returns> public override SqlParameter[] GetParms() { try { var parms = SqlHelperParameterCache.GetCachedParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmDailyOneProblem_Insert); if (parms == null) { parms = new[] { new SqlParameter(Parm_CrmDailyOneProblem_QuestionsTypeId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_QuestionsTypeName, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_Question, SqlDbType.NVarChar, -1), new SqlParameter(Parm_CrmDailyOneProblem_OptionA, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionB, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionC, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionD, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionE, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionF, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_OptionG, SqlDbType.NVarChar, 500), new SqlParameter(Parm_CrmDailyOneProblem_Answer, SqlDbType.NVarChar, 20), new SqlParameter(Parm_CrmDailyOneProblem_Time, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDailyOneProblem_AppointedDay, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDailyOneProblem_RowStatus, SqlDbType.Int, 10), new SqlParameter(Parm_CrmDailyOneProblem_CreatorId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_CreateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_CreateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDailyOneProblem_UpdateId, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_UpdateBy, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_UpdateOn, SqlDbType.DateTime, 23), new SqlParameter(Parm_CrmDailyOneProblem_Id, SqlDbType.NVarChar, 50), new SqlParameter(Parm_CrmDailyOneProblem_QuestionBankType, SqlDbType.NVarChar, 50) }; SqlHelperParameterCache.CacheParameterSet(SqlHelper.SqlConnStringWrite, Sql_CrmDailyOneProblem_Insert, parms); } return(parms); } catch (Exception e) { throw new Exception("获取查询参数时出错!", e); } }
public static SqlDataReader SecurityRoleGetUsers(SqlConnection conn, int roleID) { const string METHOD_NAME = MOD_NAME + "SecurityRoleGetUsers: "; SqlDataReader dr = null; try { string procName = "bawpUserSecurityGetByRole"; if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.SqlClient.SqlParameter[] spParams = SqlHelperParameterCache.GetSpParameterSet(conn, procName, false); spParams[0].Value = roleID; //SetTimeout(); try { dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, procName, spParams); } catch (SqlException sqlEx) { if (sqlEx.Number == Convert.ToInt32(WSCIEMP.Common.CException.KnownError.DataWarning)) { WSCIEMP.Common.CWarning wscWarn = new WSCIEMP.Common.CWarning(sqlEx.Message, sqlEx); throw (wscWarn); } else { throw (sqlEx); } } } catch (System.Exception e) { WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(MOD_NAME + METHOD_NAME, e); throw (wscEx); } return(dr); }
/// <summary> /// 编辑文章 /// </summary> /// <param name="News"></param> /// <returns></returns> public int EditNews(News News) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(SqlConnectFactory.CMS, up_News_Update); commandParameters[0].Value = News.NewsId; commandParameters[1].Value = News.ChannelId; commandParameters[2].Value = News.Title; commandParameters[3].Value = News.Author; commandParameters[4].Value = News.KeyWord; commandParameters[5].Value = News.NewsAbstract; commandParameters[6].Value = News.OrderNum; commandParameters[7].Value = News.Sort; commandParameters[8].Value = News.Status; commandParameters[9].Value = News.CreatedTime; commandParameters[10].Value = News.UpdatedUser; commandParameters[11].Value = News.UpdatedTime; commandParameters[12].Value = News.SecondTitle; commandParameters[13].Value = News.SecondUrl; commandParameters[14].Value = News.TitleColor; commandParameters[15].Value = News.IsBold; commandParameters[16].Value = News.ImgUrl; commandParameters[17].Value = News.Content; commandParameters[18].Value = News.Prefix; commandParameters[19].Value = News.IsRetinue; commandParameters[20].Value = News.ParentNewsId; commandParameters[21].Value = News.FileUrl; commandParameters[22].Value = News.Url; commandParameters[23].Value = News.StockCode; commandParameters[24].Value = News.Important; commandParameters[25].Value = News.Reliability; commandParameters[26].Value = News.Effect; commandParameters[27].Value = News.DeadLine; commandParameters[28].Value = News.LastValue; commandParameters[29].Value = News.ForeCast; commandParameters[30].Value = News.HUrl; commandParameters[31].Value = News.CUrl; commandParameters[32].Value = News.TabImgUrl; SqlHelper.ExecuteNonQuery(SqlConnectFactory.CMS, CommandType.StoredProcedure, up_News_Update, commandParameters); return(News.NewsId); }
private SqlParameter[] GetParameters(U_ADM_EVNT_Type tobjU_ADM_EVNT_Type) { SqlParameter[] objParamArray = SqlHelperParameterCache.GetCachedParameterSet(General.SQL_CONN_STRING, SQL_INSERT_U_ADM_EVNT_Type); if (objParamArray == null) { //Represents a parameter to a System.Data.SqlClient.SqlCommand, //and optionally, its mapping to System.Data.DataSet columns. objParamArray = new SqlParameter[] { new SqlParameter(PARAM_Event_Type_Id, tobjU_ADM_EVNT_Type.Event_Type_Id), new SqlParameter(PARAM_Event_Type_Name, tobjU_ADM_EVNT_Type.Event_Type_Name), new SqlParameter(PARAM_Event_Desc, tobjU_ADM_EVNT_Type.Event_Desc), new SqlParameter(PARAM_Media_Id_Img, tobjU_ADM_EVNT_Type.Media_Id_Img), new SqlParameter(PARAM_Created_Date, tobjU_ADM_EVNT_Type.Created_Date), new SqlParameter(PARAM_Updated_Date, tobjU_ADM_EVNT_Type.Updated_Date), new SqlParameter(PARAM_Created_by, tobjU_ADM_EVNT_Type.Created_by), new SqlParameter(PARAM_Updated_by, tobjU_ADM_EVNT_Type.Updated_by), }; SqlHelperParameterCache.CacheParameterSet(General.SQL_CONN_STRING, SQL_INSERT_U_ADM_EVNT_Type, objParamArray); } return(objParamArray); }