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);
}
