public DataSet GetSQLResult(string editor, string dbName, string sqlText, string[] ParameterNameArray, string[] ParameterValueArray)
{
ExecSQLLogger.Info("editor is: " + editor + ", Database is: " + dbName + ", ExecSQL is: " + "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; \n" + sqlText);
System.Data.SqlClient.SqlParameter[] paramsArray = null;
if (ParameterNameArray != null && ParameterValueArray != null && ParameterNameArray.Length == ParameterValueArray.Length)
{
paramsArray = new System.Data.SqlClient.SqlParameter[ParameterNameArray.Length];
for (int i = 0; i < ParameterNameArray.Length; i++)
{
paramsArray[i] = new System.Data.SqlClient.SqlParameter(ParameterNameArray[i].StartsWith("@") ? ParameterNameArray[i] : "@" + ParameterNameArray[i], ParameterValueArray[i]);
}
}
sqlText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; \n" + sqlText + " select @@ROWCOUNT as AffectRowCount";
string ConnectString = string.Format(IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString, dbName);
if (string.IsNullOrEmpty(dbName))
{
ConnectString = IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString_FA;
}
IProductRepository MyRepository = RepositoryFactory.GetInstance().GetRepository<IProductRepository, IMES.FisObject.FA.Product.IProduct>();
try
{
return IMES.Infrastructure.Repository._Schema.SqlHelper.ExecSPorSql(ConnectString, CommandType.Text, sqlText, paramsArray);
}
catch (Exception e)
{
ExecSQLLogger.Error(e.Message, e);
throw e;
}
}
private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario)
{
// Aca se lee la informacion de la base de datos
// y se preparan los layers
string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand
("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn);
System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100);
prm.Value = IDParametro;
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int);
if (IDPortal.HasValue)
{
prm.Value = IDPortal.Value;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int);
if (IDSistema.HasValue)
{
prm.Value = IDSistema.Value;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar);
if (IDUsuario != null)
{
prm.Value = IDUsuario;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
// IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora
cmd.CommandType = System.Data.CommandType.Text;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds);
conn.Close();
return ds.Tables[0].Rows[0];
//return resultado;
}
public DataTable GetSPResult(string editor, string dbName, string spName, string[] ParameterNameArray, string[] ParameterValueArray)
{
if (ParameterNameArray == null || ParameterValueArray == null)
{
return null;
}
string ParameterValuesStr = "";
System.Data.SqlClient.SqlParameter[] paramsArray = new System.Data.SqlClient.SqlParameter[ParameterNameArray.Length];
for (int i = 0; i < ParameterNameArray.Length; i++)
{
paramsArray[i] = new System.Data.SqlClient.SqlParameter(ParameterNameArray[i].StartsWith("@") ? ParameterNameArray[i] : "@" + ParameterNameArray[i], ParameterValueArray[i]);
ParameterValuesStr = ParameterValuesStr + paramsArray[i].ParameterName + "=" + ParameterValueArray[i] + ",";
}
ExecSQLLogger.Info("editor is: " + editor + ", Database is: " + dbName + ", SPName is: " + spName + ", Parameters is :" + ParameterValuesStr.TrimEnd(new char[] { ',' }));
IProductRepository MyRepository = RepositoryFactory.GetInstance().GetRepository<IProductRepository, IMES.FisObject.FA.Product.IProduct>();
string ConnectString = string.Format(IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString, dbName);
try
{
return IMES.Infrastructure.Repository._Schema.SqlHelper.ExecuteDataFillConsiderOutParams(ConnectString, CommandType.StoredProcedure, spName, paramsArray);
}
catch (Exception e)
{
ExecSQLLogger.Error(e.Message, e);
throw e;
}
}
public void AddParameter(string name, object value, System.Data.DbType paramtype)
{
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(name, paramtype);
param.Value = value;
comm.Parameters.Add(param);
}
private void btnPreg_Click(object sender, EventArgs e)
{
if (this.txtPreg.Text == string.Empty)
{
MessageBox.Show("Ingrese un texto para la pregunta");
return;
}
// Creo la pregunta
//Abro la conexion
AccesoDatos.getInstancia().abrirConexion();
System.Data.SqlClient.SqlCommand comVenta = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_creaPregunta");
System.Data.SqlClient.SqlParameter p1V = new System.Data.SqlClient.SqlParameter("@id_usu", this.id_comprador);
comVenta.Parameters.Add(p1V);
System.Data.SqlClient.SqlParameter p2V = new System.Data.SqlClient.SqlParameter("@id_pub", this.id_pub);
comVenta.Parameters.Add(p2V);
System.Data.SqlClient.SqlParameter p3V = new System.Data.SqlClient.SqlParameter("@txt", this.txtPreg.Text.Trim());
comVenta.Parameters.Add(p3V);
System.Data.SqlClient.SqlParameter p4V = new System.Data.SqlClient.SqlParameter("@fecha", this.fecha_hoy);
comVenta.Parameters.Add(p4V);
System.Data.SqlClient.SqlDataReader rolReader = AccesoDatos.getInstancia().ejecutaSP(comVenta);
//Cierro la conexion
AccesoDatos.getInstancia().cerrarConexion();
MessageBox.Show("Su pregunta se ha registrado con exito");
this.Close();
}
private static async Task DbCommandAsync(Phone p)
{
using (MobileContext db = new MobileContext())
{
System.Data.SqlClient.SqlParameter name = new System.Data.SqlClient.SqlParameter("name", p.Name);
System.Data.SqlClient.SqlParameter price = new System.Data.SqlClient.SqlParameter("price", p.Price);
await db.Database.ExecuteSqlCommandAsync("INSERT INTO Phones (Name, Price) VALUES (@name, @price)", name, price);
}
}
public static System.Data.Objects.ObjectResult<BAOCAOLICHSUTONKHO> GetLichSuTonKho(KaraokeEntities kara, int KhoID, DateTime dtFrom, DateTime dtTo)
{
var Parameter_KhoID = new System.Data.SqlClient.SqlParameter("@KhoID", System.Data.SqlDbType.Int);
Parameter_KhoID.Value = KhoID;
var Parameter_DateFrom = new System.Data.SqlClient.SqlParameter("@DateFrom", System.Data.SqlDbType.DateTime);
Parameter_DateFrom.Value = dtFrom;
var Parameter_DateTo = new System.Data.SqlClient.SqlParameter("@DateTo", System.Data.SqlDbType.DateTime);
Parameter_DateTo.Value = dtTo;
return kara.ExecuteStoreQuery<BAOCAOLICHSUTONKHO>("SP_BAOCAOLICHSUTONKHO @KhoID, @DateFrom, @DateTo", Parameter_KhoID, Parameter_DateFrom, Parameter_DateTo);
}
public bool Save(string CodAntena, string TagEquipo)
{
// Aca se graba la informacion en la base de datos.
//1. Para el caso RFID, donde del Agente está escuchando o leyendo los Data Logger cada 15 segundos se debe ejecutar la siguiente SP con los parámetros que se detallan:
//-----------------------------------------------------------------------------------------------------------------------
//-- Declare type parameter
//DECLARE @RC int --Valor de retorno 0=OK
//DECLARE @CODANTENA varchar(4) --Código de antena
//DECLARE @TAGEQUIPO varchar(16) --Nro de Tag del equipo/camión
//-- Set parameter values
//SET @CODANTENA = '0001'
//SET @TAGEQUIPO = 'A000000000000112'
//EXEC @RC = [TARJA].[dbo].[STP_MOVIMIENTOS_PUNTOS_CONTROL] @CODANTENA, @TAGEQUIPO
//-----------------------------------------------------------------------------------------------------------------------
// Para los códigos de las antenas determinamos la siguiente codificación:
// 1 = Trapiche 1
//2 = Trapiche 2
//3 = Trapiche 3
//4 = Bascula 9
//5 = Bascula 10
//6 = Portería Gas
//7 = Portería Gas Interno)
try
{
string strConnection = GetConnectionString();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConnection);
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "STP_MOVIMIENTOS_PUNTOS_CONTROL";
System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@CODANTENA", System.Data.SqlDbType.VarChar, 4);
prm.Value = CodAntena;
cmd.Parameters.Add(prm);
prm = new System.Data.SqlClient.SqlParameter("@TAGEQUIPO", System.Data.SqlDbType.VarChar, 16);
prm.Value = TagEquipo;
cmd.Parameters.Add(prm);
int rc =(int) cmd.ExecuteScalar();
if (rc == 0)
return true;
else
return false;
}
catch (Exception)
{
return false;
}
}
public static System.Data.SqlClient.SqlParameter[] NullParameters()
{
System.Data.SqlClient.SqlParameter[] retVal = new System.Data.SqlClient.SqlParameter[4];
retVal[0] = new System.Data.SqlClient.SqlParameter("maxlong", null);
retVal[1] = new System.Data.SqlClient.SqlParameter("minLong", null);
retVal[2] = new System.Data.SqlClient.SqlParameter("maxlat", null);
retVal[3] = new System.Data.SqlClient.SqlParameter("minlat", null);
return retVal;
}
public static System.Data.SqlClient.SqlParameter getDecimalInputParam(string ip_name,
object ip_value)
{
Debug.Assert(ip_name.IndexOf(C_PREFIX_OF_PARAM) >= 0, C_WRONG_PARAM_NAME);
System.Data.SqlClient.SqlParameter v_sqlPara = new System.Data.SqlClient.SqlParameter();
v_sqlPara.SqlDbType = SqlDbType.Decimal;
v_sqlPara.Direction = ParameterDirection.Input;
v_sqlPara.Value = ip_value;
v_sqlPara.ParameterName = ip_name;
return v_sqlPara;
}
public override System.Data.IDataReader AgeClassGetAll()
{
System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[0];
if ((_SqlTransaction == null))
{
return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(ConnectionString, "usp_AgeClassGetAll", arrSqlParameter)));
}
else
{
return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(_SqlTransaction, "usp_AgeClassGetAll", arrSqlParameter)));
}
}
public static NHANVIEN CheckLogin(KaraokeEntities kara,string user, string pass)
{
if (!String.IsNullOrEmpty(user) && !String.IsNullOrEmpty(pass))
{
var Parameter_TenDangNhap = new System.Data.SqlClient.SqlParameter("@TenDangNhap", System.Data.SqlDbType.VarChar, 50);
Parameter_TenDangNhap.Value = user;
var Parameter_MatKhau = new System.Data.SqlClient.SqlParameter("@MatKhau", System.Data.SqlDbType.VarChar, 255);
Parameter_MatKhau.Value = pass;
NHANVIEN nv = kara.ExecuteStoreQuery<NHANVIEN>("SP_Login_NhanVien @TenDangNhap, @MatKhau", Parameter_TenDangNhap, Parameter_MatKhau).FirstOrDefault();
return nv;
}
return null;
}
public static IQueryable<BAOCAONGAYTONG> GetBaoCaoNgayTong(KaraokeEntities kara,DateTime dtFrom, DateTime dtTo)
{
//return from x in kara.BAOCAONGAYTONGs
// where dtFrom.CompareTo(x.NgayBan.Value) <= 0 && dtTo.CompareTo(x.NgayBan.Value) >= 0
// select x;
var paraFrom = new System.Data.SqlClient.SqlParameter("@NgayBatDau", System.Data.SqlDbType.DateTime);
paraFrom.Value = dtFrom;
var paraTo = new System.Data.SqlClient.SqlParameter("@NgayKetThuc", System.Data.SqlDbType.DateTime);
paraTo.Value = dtTo;
var sql = kara.ExecuteStoreQuery<BAOCAONGAYTONG>("SP_REPORT_BAOCAONGAYTONG @NgayBatDau,@NgayKetThuc", paraFrom, paraTo);
return sql.AsQueryable<BAOCAONGAYTONG>();
}
public override void AgeClassDelete(int AgeClassID)
{
System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[] {
new System.Data.SqlClient.SqlParameter("@AgeClassID", Helper.CheckForNullValue(AgeClassID))};
if ((_SqlTransaction == null))
{
SqlHelper.ExecuteNonQuery(ConnectionString, "usp_AgeClassDelete", arrSqlParameter);
}
else
{
SqlHelper.ExecuteNonQuery(_SqlTransaction, "usp_AgeClassDelete", arrSqlParameter);
}
}
public override System.Data.IDataReader AgeClassGet(int AgeClassID)
{
System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[] {
new System.Data.SqlClient.SqlParameter("@AgeClassID", Helper.CheckForNullValue(AgeClassID))};
if ((_SqlTransaction == null))
{
return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(ConnectionString, "usp_AgeClassGet", arrSqlParameter)));
}
else
{
return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(_SqlTransaction, "usp_AgeClassGet", arrSqlParameter)));
}
}
public IQueryable<BAOCAOKHU> GetBaoCaoKhu(DateTime dtFrom, DateTime dtTo)
{
//return from x in mKaraokeEntities.BAOCAOKHUs
// where dtFrom.CompareTo(x.NgayBan.Value) <= 0 && dtTo.CompareTo(x.NgayBan.Value) >= 0
// orderby x.TenKhu
// select x;
var paraFrom = new System.Data.SqlClient.SqlParameter("@NgayBatDau", System.Data.SqlDbType.DateTime);
paraFrom.Value = dtFrom;
var paraTo = new System.Data.SqlClient.SqlParameter("@NgayKetThuc", System.Data.SqlDbType.DateTime);
paraTo.Value = dtTo;
var sql = mKaraokeEntities.ExecuteStoreQuery<BAOCAOKHU>("SP_REPORT_BAOCAOKHU @NgayBatDau,@NgayKetThuc", paraFrom, paraTo);
return sql.AsQueryable<BAOCAOKHU>();
}
public void LoadData()
{
System.Data.SqlClient.SqlCommand command = SqlHelper.CreateCommand(DAC.Connection, "GetContactInfoByContactID");
command.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter pContactID = new System.Data.SqlClient.SqlParameter("@ContactID", System.Data.SqlDbType.Int);
pContactID.Direction = System.Data.ParameterDirection.Input;
pContactID.Value = ContactID;
command.Parameters.Add(pContactID);
if (DAC.Connection.State == ConnectionState.Closed)
{
DAC.Connection.Open();
}
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
this.Phone1 = reader["Phone1"].ToString();
this.Phone2 = reader["Phone2"].ToString();
this.OtherPhone = reader["PhoneOther"].ToString();
this.Fax1 = reader["PhoneFax1"].ToString();
this.Fax2 = reader["PhoneFax2"].ToString();
this.Url = reader["Url"].ToString();
this.FirstName = reader["AccountRepFirstName"].ToString();
this.FirstNamePhonetic = reader["AccountRepFirstNamePhonetic"].ToString();
this.FirstNameRomaji = reader["AccountRepFirstNameRomaji"].ToString();
this.LastName = reader["AccountRepLastName"].ToString();
this.LastNamePhonetic = reader["AccountRepLastNamePhonetic"].ToString();
this.LastNameRomaji = reader["AccountRepLastNameRomaji"].ToString();
this.Street1 = reader["Street1"].ToString();
this.Street2 = reader["Street2"].ToString();
this.Street3 = reader["Street3"].ToString();
this.City = reader["City"].ToString();
this.State = reader["State"].ToString();
this.PostalCode = reader["PostalCode"].ToString();
this.Country = reader["Country"].ToString();
this.Block = reader["BlockCode"].ToString();
this.ClosestStation1 = reader["ClosestStation1"].ToString();
this.ClosestStation2 = reader["ClosestStation2"].ToString();
this.ClosestLine1 = reader["ClosestLine1"].ToString();
this.ClosestLine2 = reader["ClosestLine2"].ToString();
this.MinutesToStation1 = reader["MinutesToStation1"].ToString();
this.MinutesToStation2 = reader["MinutesToStation2"].ToString();
}
if (DAC.Connection.State == ConnectionState.Open)
{
DAC.Connection.Close();
}
}
private void AMEmpresa_Load(object sender, EventArgs e)
{
//Chequeo si es una modificacion
if (this.id_usuario != -1)
{
//Traigo la informacion del usuario
System.Data.SqlClient.SqlCommand comUsu = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_getUsuarioEmpresaXId");
//Defino los parametros
System.Data.SqlClient.SqlParameter pUsu = new System.Data.SqlClient.SqlParameter("@id", this.id_usuario);
comUsu.Parameters.Add(pUsu);
// Abro la conexion
AccesoDatos.getInstancia().abrirConexion();
System.Data.SqlClient.SqlDataReader usuario = AccesoDatos.getInstancia().ejecutaSP(comUsu);
if (!usuario.HasRows)
{
MessageBox.Show("Usuario invalido");
return;
}
usuario.Read();
this.lblUsu.Text += usuario.GetString(0);
this.lblEstado.Text += usuario.GetString(1);
this.empRazSocial.Text = usuario.GetString(2);
this.empMail.Text = usuario.GetString(3).Trim();
this.empTelefono.Text = usuario.GetString(4).Trim();
this.empDireccion.Text = usuario.GetString(5).Trim();
this.empNroCalle.Text = usuario.GetDecimal(6).ToString().Trim();
this.empNroPiso.Text = usuario.GetDecimal(7).ToString().Trim();
this.empDpto.Text = usuario.GetString(8).Trim();
this.empLocalidad.Text = usuario.GetString(9).Trim();
this.empCodPostal.Text = usuario.GetString(10).Trim();
this.ciudad.Text = usuario.GetString(11).Trim();
this.empCuit.Text = usuario.GetString(12).Trim();
this.empFechaCre.Value = usuario.GetDateTime(13);
this.empContacto.Text = usuario.GetString(14);
// Cierro la conexion
usuario.Close();
AccesoDatos.getInstancia().cerrarConexion();
}
else
{
this.lblUsu.Text += "AUTOGENERADO";
this.lblEstado.Text += "Habilitado";
}
}
protected Boolean bInScheduleWindow(int SID)
{
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["TL_Connection_String"].ToString());
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SELECT BRLSessionAvailabiltyWindowOpen, BRLSessionAvailabiltyWindowClose FROM dbo.tlBRLSessionDates WHERE BRLSessionDateID = @ID", connection);
System.Data.SqlClient.SqlParameter safeID = new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.VarChar);
System.Data.SqlClient.SqlDataReader reader = null;
safeID.Value = SID;
command.Parameters.Add(safeID);
//Open The Connection
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
try
{
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
DateTime Started = DateTime.Parse(reader["BRLSessionAvailabiltyWindowOpen"].ToString());
DateTime Ends = DateTime.Parse(reader["BRLSessionAvailabiltyWindowClose"].ToString());
connection.Close();
if (DateTime.Now > Started && DateTime.Now < Ends)
return true;
else
return false;
}
else
{
connection.Close();
return false;
}
}
catch (Exception ex)
{
connection.Close();
ErrorLabel.Text = "Database error, try again.</br>" + ex.ToString();
ErrorPanel.Visible = true;
ScanPanel.Visible = true;
SchedulePanel.Visible = false;
DateSelectionPanel.Visible = false;
return false;
}
}
private void btnComprar_Click(object sender, EventArgs e)
{
int cant_compra=0;
//Valido los campos numericos
try
{
cant_compra = int.Parse(this.txtCant.Text);
}
catch
{
MessageBox.Show("Debe ingresar una cantidad a comprar numerica");
return;
}
if (cant_compra > int.Parse(this.lblStock.Text))
{
MessageBox.Show("Debe ingresar una cantidad menor o igual al stock disponible");
return;
}
// Creo la venta
//Abro la conexion
AccesoDatos.getInstancia().abrirConexion();
System.Data.SqlClient.SqlCommand comVenta = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_creaVenta");
System.Data.SqlClient.SqlParameter p1V = new System.Data.SqlClient.SqlParameter("@id_publ", this.id_pub);
comVenta.Parameters.Add(p1V);
System.Data.SqlClient.SqlParameter p2V = new System.Data.SqlClient.SqlParameter("@id_comprador", this.id_comprador);
comVenta.Parameters.Add(p2V);
System.Data.SqlClient.SqlParameter p3V = new System.Data.SqlClient.SqlParameter("@fecha", this.fecha_hoy);
comVenta.Parameters.Add(p3V);
System.Data.SqlClient.SqlParameter p4V = new System.Data.SqlClient.SqlParameter("@cant", cant_compra);
comVenta.Parameters.Add(p4V);
System.Data.SqlClient.SqlDataReader rolReader = AccesoDatos.getInstancia().ejecutaSP(comVenta);
//Cierro la conexion
AccesoDatos.getInstancia().cerrarConexion();
Comprar_Ofertar.VisVendedor fVend = new VisVendedor();
fVend.idVendedor = this.id_vendedor;
fVend.ShowDialog();
this.Close();
}
private void AMAdmin_Load(object sender, EventArgs e)
{
//Chequeo si es una modificacion
if (this.id_usuario != -1)
{
//Traigo la informacion del usuario
System.Data.SqlClient.SqlCommand comUsu = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_getUsuarioAdminXId");
//Defino los parametros
System.Data.SqlClient.SqlParameter pUsu = new System.Data.SqlClient.SqlParameter("@id", this.id_usuario);
comUsu.Parameters.Add(pUsu);
// Abro la conexion
AccesoDatos.getInstancia().abrirConexion();
System.Data.SqlClient.SqlDataReader usuario = AccesoDatos.getInstancia().ejecutaSP(comUsu);
if (!usuario.HasRows)
{
MessageBox.Show("Usuario invalido");
return;
}
usuario.Read();
this.lblUsu.Text += usuario.GetString(0);
this.lblEstado.Text += usuario.GetString(1);
this.txtLegajo.Text = usuario.GetString(2);
this.txtSuc.Text = usuario.GetString(3).Trim();
this.txtMail.Text = usuario.GetString(4).Trim();
this.txtCalle.Text = usuario.GetString(5).Trim();
this.txtNroCalle.Text = usuario.GetDecimal(6).ToString().Trim();
this.txtNroPiso.Text = usuario.GetDecimal(7).ToString().Trim();
this.txtDepto.Text = usuario.GetString(8).Trim();
this.txtLocalidad.Text = usuario.GetString(9).Trim();
this.txtCP.Text = usuario.GetString(10).Trim();
this.txtCiudad.Text = usuario.GetString(11).Trim();
// Cierro la conexion
usuario.Close();
AccesoDatos.getInstancia().cerrarConexion();
}
else
{
this.lblUsu.Text += "AUTOGENERADO";
this.lblEstado.Text += "Habilitado";
}
}
public void Delete(int codigo)
{
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Codigo", codigo)
};
ExecuteCommand("usp_estado_delete", parameters);
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "EstadoDAL.Delete");
}
}
public static System.Data.SqlClient.SqlParameter getImageInputParam(string ip_name,
object ip_value)
{
try
{
Debug.Assert(ip_name.IndexOf(C_PREFIX_OF_PARAM) >= 0, C_WRONG_PARAM_NAME);
System.Data.SqlClient.SqlParameter v_sqlPara = new System.Data.SqlClient.SqlParameter();
v_sqlPara.SqlDbType = SqlDbType.Image;
v_sqlPara.Direction = ParameterDirection.Input;
v_sqlPara.Value = ip_value;
v_sqlPara.ParameterName = ip_name;
return v_sqlPara;
}
catch (Exception v_ex)
{
throw (v_ex);
}
}
public void Insert(Model.CidadeModel data)
{
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Capital", data.Capital ? "S" : "N" ),
new System.Data.SqlClient.SqlParameter("@Estado", data.EstadoCodigo),
new System.Data.SqlClient.SqlParameter("@Nome", data.Nome)
};
ExecuteCommand("usp_cidade_insert", parameters);
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "CidadeDAL.Insert");
}
}
public void Insert(Model.UsuarioModel data)
{
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Login", data.Login),
new System.Data.SqlClient.SqlParameter("@Nome", data.Nome),
new System.Data.SqlClient.SqlParameter("@Senha", data.Senha)
};
ExecuteCommand("usp_usuario_insert", parameters);
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "UsuarioDAL.Insert");
}
}
public System.Data.SqlClient.SqlParameter[] Parameters()
{
System.Data.SqlClient.SqlParameter[] retVal = new System.Data.SqlClient.SqlParameter[4];
if (MaxLong != 0.0m)
{
retVal[0] = new System.Data.SqlClient.SqlParameter("maxlong", MaxLong);
retVal[1] = new System.Data.SqlClient.SqlParameter("minLong", MinLong);
retVal[2] = new System.Data.SqlClient.SqlParameter("maxlat", MaxLat);
retVal[3] = new System.Data.SqlClient.SqlParameter("minlat", MinLat);
}
else
{
retVal = GeoBox.NullParameters();
}
return retVal;
}
public void CreateServiceSession(out Int64 lNewSessionId, out Int64 lPrevSessionId)
{
var newSessionId = new System.Data.SqlClient.SqlParameter("newSessionId", System.Data.SqlDbType.BigInt)
{
Direction = System.Data.ParameterDirection.Output,
IsNullable = false,
};
var prevSessionId = new System.Data.SqlClient.SqlParameter("prevSessionId", System.Data.SqlDbType.BigInt)
{
Direction = System.Data.ParameterDirection.Output,
IsNullable = false,
};
Database.ExecuteSqlCommand(@"EXEC [dbo].[uspHFDServiceStartSession] @newSessionId OUTPUT, @prevSessionId OUTPUT", newSessionId, prevSessionId);
lNewSessionId = (Int64)newSessionId.Value;
lPrevSessionId = (Int64)prevSessionId.Value;
}
public void Insert(Model.EstadoModel data)
{
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Pais", data.Pais),
new System.Data.SqlClient.SqlParameter("@Nome", data.Nome),
new System.Data.SqlClient.SqlParameter("@Sigla", data.Sigla),
new System.Data.SqlClient.SqlParameter("@Regiao", data.Regiao)
};
ExecuteCommand("usp_estado_insert", parameters);
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "EstadoDAL.Insert");
}
}
private void VisVendedor_Load(object sender, EventArgs e)
{
//Traigo la informacion del la publicacion
System.Data.SqlClient.SqlCommand comUsu = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_getInfoVendedorXId");
//Defino los parametros
System.Data.SqlClient.SqlParameter pUsu = new System.Data.SqlClient.SqlParameter("@id_vend", this.id_vendedor);
comUsu.Parameters.Add(pUsu);
// Abro la conexion
AccesoDatos.getInstancia().abrirConexion();
System.Data.SqlClient.SqlDataReader pub = AccesoDatos.getInstancia().ejecutaSP(comUsu);
if (!pub.HasRows)
{
MessageBox.Show("Vendedor invalido");
return;
}
pub.Read();
ArrayList lista = new ArrayList();
lista.Add("Usuario: " + pub.GetString(0));
lista.Add("Calle: " + pub.GetString(1));
lista.Add("Nro calle: " + pub.GetDecimal(2).ToString());
lista.Add("Piso: " + pub.GetDecimal(3).ToString());
lista.Add("Depto.: " + pub.GetString(4));
lista.Add("Codigo Postal: " + pub.GetString(5));
lista.Add("Correo electronico: " + pub.GetString(6));
lista.Add("Localidad: " + pub.GetString(7));
lista.Add("Ciudad: " + pub.GetString(8));
this.listInfo.DataSource = lista;
// Cierro la conexion
pub.Close();
AccesoDatos.getInstancia().cerrarConexion();
}
public List<Model.EstadoModel> Select(int codigo, string nome)
{
List<Model.EstadoModel> result = new List<Model.EstadoModel>();
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Codigo", codigo),
new System.Data.SqlClient.SqlParameter("@Nome", nome)
};
ExecuteReader("usp_estado_select", parameters, s =>
{
result.Add(new Model.EstadoModel(s));
});
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "EstadoDAL.Select");
}
return result;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string fn = txtFName.Text;
string ln = txtLName.Text;
string rpt = txtReportsTo.Text;
string email = txtEmail.Text;
string title = txtTitle.Text;
string dept = txtDept.Text;
string loc = txtLocation.Text;
DateTime hd = calHireDate.SelectedDate;
DateTime bd = calBirthDate.SelectedDate;
string imgPath = Server.MapPath("/content/images/sycom/employees/icons/");
if (uplPicture.UploadedFiles.Count() > 0)
{
foreach (UploadedFile f in uplPicture.UploadedFiles)
{
f.SaveAs(imgPath + fn + ln + ".jpg");
}
}
else
{
Response.Write("No File Selected");
}
string sql = "Insert into eng_users ( fname, lname, reports_to, email, title, department, location, birthday, hiredate) VALUES (@fn, @ln, @rpt, @email, @title, @dept, @loc, @bd, @hd)";
System.Data.SqlClient.SqlParameter[] p = new System.Data.SqlClient.SqlParameter[9];
p[0] = new System.Data.SqlClient.SqlParameter("@fn", fn);
p[1] = new System.Data.SqlClient.SqlParameter("@ln", ln);
p[2] = new System.Data.SqlClient.SqlParameter("@rpt", rpt);
p[3] = new System.Data.SqlClient.SqlParameter("@email", email);
p[4] = new System.Data.SqlClient.SqlParameter("@title", title);
p[5] = new System.Data.SqlClient.SqlParameter("@dept", dept);
p[6] = new System.Data.SqlClient.SqlParameter("@loc", loc);
p[7] = new System.Data.SqlClient.SqlParameter("@hd", hd);
p[8] = new System.Data.SqlClient.SqlParameter("@bd", bd);
SyComEngaged.App_Code.Eng_Data.eng_Update(sql, p);
}
/// <summary>
/// Finds a Cosumer by his product_id
/// </summary>
/// <param product="product_id"></param>
/// <returns></returns>
/// <exception cref="InstanceNotFoundException"></exception>
public Product FindByProductId(Decimal product_id)
{
Product product = null;
#region Option 2: Using eSQL over dbSet
string sqlQuery = "Select * FROM Product where product_id=@product_id";
DbParameter product_idParameter =
new System.Data.SqlClient.SqlParameter("product_id", product_id);
product = Context.Database.SqlQuery <Product>(sqlQuery, product_idParameter).FirstOrDefault <Product>();
#endregion Option 2: Using eSQL over dbSet
if (product == null)
{
throw new InstanceNotFoundException(product_id,
typeof(Product).FullName);
}
return(product);
}
/// <summary>
/// 获取所需要打印的图片
/// </summary>
/// <param name="strPhotoID">照片ID</param>
/// <param name="strState">修改后状态</param>
/// <returns></returns>
public bool UpdatePrintState(string strPhotoID, string strState)
{
string sql = @" UPDATE [HP_Photo] SET [State ]= @State WHERE ID = @ID; ";
sql = sql + @" UPDATE a SET a.[State] = @State FROM HP_PrintCode a JOIN HP_Photo b ON (a.ClientID = b.ClientID AND a.PrintCode = b.PrintCode)
WHERE b.ID = @ID ";
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", strPhotoID),
new System.Data.SqlClient.SqlParameter("@State", strState)
};
int rowsAffected = DbHelperSQL.ExecuteSql(sql, paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新二手产品状态
/// </summary>
/// <param name="strPID">产品编号</param>
/// <returns></returns>
public bool UpdateAtlasByPID(string strPID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE MS_ProductAtlas ");
strSql.Append(" SET ImgState = 1 ");
strSql.Append(" WHERE PID = @PID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@PID", strPID)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public void Download()
{
WebClient wc = new WebClient();
string adress = "http://www.cbr.ru/scripts/XML_daily.asp?date_req=" +
DateTime.Now.Date.ToString();
wc.DownloadFile(adress, "temp.xml");
XDocument xdoc = XDocument.Load("temp.xml");
DateTime today = DateTime.Parse(
xdoc.Element("ValCurs").Attribute("Date").Value);
foreach (XElement v in xdoc.Element("ValCurs").Elements("Valute"))
{
using (RateContext db = new RateContext())
{
System.Data.SqlClient.SqlParameter pDate = new System.Data.SqlClient.SqlParameter("@date",
today);
var list = db.Rates.SqlQuery("SELECT * FROM Rates WHERE Rates.date=@date", pDate);
if (list.Count() > 0)
{
return;
}
Rate rate = new Rate
{
date = today,
NumCode = int.Parse(v.Element("NumCode").Value),
CharCode = v.Element("CharCode").Value,
Nominal = int.Parse(v.Element("Nominal").Value),
Name = v.Element("Name").Value,
Value = decimal.Parse(v.Element("Value").Value)
};
db.Rates.Add(rate);
db.SaveChanges();
}
}
}
/// <summary>
/// 资源取数据的存储过程 用于导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tabelName"></param>
/// <param name="where"></param>
/// <param name="orderString"></param>
/// <param name="orderType"></param>
/// <returns></returns>
public object QueryResourceAll <T>(string tabelName, string columns, string orderString, int orderType = 1) where T : Kingsun.DB.Action, new()
{
try
{
List <System.Data.Common.DbParameter> list = new List <System.Data.Common.DbParameter>();
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[4];
param[0] = new System.Data.SqlClient.SqlParameter("@Columns", System.Data.SqlDbType.NVarChar, 500);
param[1] = new System.Data.SqlClient.SqlParameter("@ModData", System.Data.SqlDbType.Text);
param[2] = new System.Data.SqlClient.SqlParameter("@OrderColumns", System.Data.SqlDbType.NVarChar, 350);
param[3] = new System.Data.SqlClient.SqlParameter("@IsOrderByASC", System.Data.SqlDbType.Int);
param[0].Value = string.IsNullOrEmpty(columns) ? "*" : columns;
param[1].Value = tabelName;
param[2].Value = orderString;
param[3].Value = orderType;
list.AddRange(param);
System.Data.DataSet ds = ExecuteProcedure("proc_ResourceAllView", list);
if (ds != null && ds.Tables.Count == 1)
{
object returnData = new
{
List = FillData <T>(ds.Tables[0])
};
return(returnData);
}
else
{
return(new
{
List = new List <T>()
});
}
}
catch
{
throw;
}
}
public ActionResult SinglePost(int?Id, Home details)
{
if (Session["username"] == null)
{
return(RedirectToAction("../Login/Login"));
}
else
{
DataSet ds = new DataSet();
ds = details.GetByProductID_SingleProjectDetails();
if (ds.Tables[0].Rows.Count > 0)
{
details.projectname = ds.Tables[0].Rows[0]["ProjectName"].ToString();
details.cost = Convert.ToInt32(ds.Tables[0].Rows[0]["Cost"].ToString());
details.features = ds.Tables[0].Rows[0]["Features"].ToString();
details.description = ds.Tables[0].Rows[0]["Description"].ToString();
details.technologyname = ds.Tables[0].Rows[0]["TechnologyName"].ToString();
details.screenshot = ds.Tables[0].Rows[0]["Screenshot"].ToString();
details.authorfirstname = ds.Tables[0].Rows[0]["FirstName"].ToString();
details.authorlastname = ds.Tables[0].Rows[0]["LastName"].ToString();
details.profilepicture = ds.Tables[0].Rows[0]["ProfilePicture"].ToString();
}
string sqlConnectionString = ConfigurationManager.AppSettings["sqlConnectionString"].ToString();
System.Data.SqlClient.SqlParameter[] arrParameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Id", System.Data.DbType.Int32)
};
arrParameters[0].Value = Id;
string comments = SqlHelper.ExecuteScalar(sqlConnectionString, System.Data.CommandType.Text, "select count(Comment) as Comment from sb_ProjectComments where ProjectId=@Id", arrParameters).ToString();
details.comments = Convert.ToInt32(comments);
details.rating = SqlHelper.ExecuteScalar(sqlConnectionString, System.Data.CommandType.Text, "select count(Rating) as Rating from sb_ProjectComments where ProjectId=@Id", arrParameters).ToString();
}
return(View(details));
}
public static bool UnRegistration(int idTypeDirectory, int idCatalog)
{
bool _return = false;
using (var command = new TreeNSIDbContext(CONNECTION_NAME).Database.Connection.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.usp_TreeNSI_DirectoryUnRegistration";
System.Data.SqlClient.SqlParameter idType = new System.Data.SqlClient.SqlParameter("@directoryType", idTypeDirectory);
System.Data.SqlClient.SqlParameter id = new System.Data.SqlClient.SqlParameter("@catalogId", idCatalog);
System.Data.SqlClient.SqlParameter idUser = new System.Data.SqlClient.SqlParameter("@user", SecuritySystem.CurrentUserId);
System.Data.SqlClient.SqlParameter _errorMassage = new System.Data.SqlClient.SqlParameter("@errorMassage", System.Data.SqlDbType.VarChar);
_errorMassage.Size = 1000;
_errorMassage.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(idType);
command.Parameters.Add(id);
command.Parameters.Add(idUser);
command.Parameters.Add(_errorMassage);
command.Connection.Open();
var _result = command.ExecuteScalar();
if (String.IsNullOrWhiteSpace(_errorMassage.Value.ToString()) /*&& _result.HasValue && _result.Value == 0*/)
{
_return = true;
}
string _error = _errorMassage.Value.ToString();
if (!String.IsNullOrWhiteSpace(_error))
{
throw new Exception(_error.Trim());
}
//if (!_result.HasValue || _result.Value != 0)
// throw new Exception("Снятие с регитсрации не возможно!");
}
return(_return);
}
public static System.Data.SqlClient.SqlParameter CreateParameter(string parameterName, object value)
{
System.Data.SqlClient.SqlParameter ret = new System.Data.SqlClient.SqlParameter();
// ret = new System.Data.SqlClient.SqlParameter("Test", SqlDbType.DateTime);
ret.ParameterName = parameterName;
if (value != null)
{
/*
* Gestisco le DateTime di C# per SQLServer
* In SQL non possono essere valorizzate come DateTime.MinDate quindi le considero un null
*
*/
if (value is DateTime)
{
// ret.SqlDbType = SqlDbType.DateTime;
DateTime valueDate = (DateTime)value;
if (valueDate == DateTime.MinValue)
{
ret.Value = DBNull.Value;
}
else
{
ret.Value = value;
}
}
else
{
ret.Value = value;
}
}
else
{
ret.Value = DBNull.Value;
}
return(ret);
}
//获取操作员列表
DataTable ICommon.GetOperList(string sysn_time, string branch_no)
{
try
{
DB.IDB db = new DB.DBByAutoClose(Appsetting.conn);
string sql = "select oper_id,oper_type,oper_name,oper_pw,oper_status,update_time,is_branch,is_admin,branch_no ";
sql += "from sa_t_operator_i where 1=1 ";//update_time>=@sysn_time ";
var pars = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@sysn_time", sysn_time),
new System.Data.SqlClient.SqlParameter("@branch_no", branch_no)
};
var dt = db.ExecuteToTable(sql, pars);
return(dt);
}
catch (Exception ex)
{
LogHelper.writeLog("Common.GetOperList()", ex.ToString(), sysn_time);
throw new Exception(ex.Message);
}
}
//获取商品列表
DataTable ICommon.GetItemList(string sysn_time)
{
try
{
DB.IDB db = new DB.DBByAutoClose(Appsetting.conn);
string sql = "select item_no,item_subno,item_subname,item_clsno,item_name,item_brand,item_brandname,unit_no,item_size";
sql += ",product_area,barcode,price,base_price,base_price2,base_price3,0 base_price4,0 base_price5,sale_price,display_flag,item_flag ";
sql += "from bi_t_item_info where update_time>=@sysn_time ";
var pars = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@sysn_time", sysn_time)
};
var dt = db.ExecuteToTable(sql, pars);
return(dt);
}
catch (Exception ex)
{
LogHelper.writeLog("Common.GetItemList()", ex.ToString(), sysn_time);
throw new Exception(ex.Message);
}
}
/// <summary>
/// 修改活动状态
/// </summary>
/// <param name="strID">活动ID</param>
/// <param name="strState">修改的状态</param>
/// <returns></returns>
public bool UpdateActivityActStatus(string strID, string strState)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE ACT_SiteActivity ");
strSql.Append(" SET ActStatus = @State ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", strID),
new System.Data.SqlClient.SqlParameter("@State", strState)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取老师班级导学列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="UserID"></param>
/// <param name="ClassID"></param>
/// <returns></returns>
public IList <T> QueryGuidingList <T>(string UserID, string ClassID) where T : Kingsun.DB.Action, new()
{
try
{
List <System.Data.Common.DbParameter> list = new List <System.Data.Common.DbParameter>();
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@UserID", UserID);
param[1] = new System.Data.SqlClient.SqlParameter("@ClassID", ClassID);
list.AddRange(param);
System.Data.DataSet ds = ExecuteProcedure("Pro_UserClassGuid", list);
if (ds == null)
{
return(null);
}
IList <T> GuidList = FillData <T>(ds.Tables[0]);
return(GuidList);
}
catch
{
throw;
}
}
/// <summary>
/// 添加打印码
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddHPClient(HP_Client model)
{
string sql = @"INSERT INTO [Hp_client] ([ID],[SiteCode],[ClientCode],[IsDel])
VALUES (@ID,@SiteCode,@ClientCode,@IsDel)";
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", model.ID),
new System.Data.SqlClient.SqlParameter("@SiteCode", model.SiteCode),
new System.Data.SqlClient.SqlParameter("@ClientCode", model.ClientCode),
new System.Data.SqlClient.SqlParameter("@IsDel", model.IsDel)
};
int rowsAffected = DbHelperSQL.ExecuteSql(sql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <Model.UsuarioModel> Select(int codigo, string login)
{
List <Model.UsuarioModel> result = new List <Model.UsuarioModel>();
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Id", codigo),
new System.Data.SqlClient.SqlParameter("@Login", login)
};
ExecuteReader("usp_usuario_select", parameters, s =>
{
result.Add(new Model.UsuarioModel(s));
});
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "UsuarioDAL.Select");
}
return(result);
}
// User must use a stored procedure so that the SQL server can handle password encryption.
public override bool Add()
{
bool result;
StoredProcedure proc = new StoredProcedure(Constants.CONNECTION_STRING, Constants.STORED_PROC_ADD_USER);
proc.AddInput("@userName", UserName.Trim(), System.Data.SqlDbType.VarChar);
proc.AddInput("@email", Email.Trim(), System.Data.SqlDbType.VarChar);
proc.AddInput("@password", Password.Trim(), System.Data.SqlDbType.VarChar);
proc.AddInput("@displayName", DisplayName.Trim(), System.Data.SqlDbType.VarChar);
proc.AddInput("@description", Description.Trim(), System.Data.SqlDbType.VarChar);
System.Data.SqlClient.SqlParameter idParam = proc.AddOutput("@userId", System.Data.SqlDbType.Int);
result = proc.Execute();
if (result)
{
UserId = Convert.ToInt32(idParam.Value);
}
return(result);
}
private void GetResult(Chat chat)
{
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter("@ChatId", chat.id);
var result = _context.UserResults.FromSql("GetStatistic @ChatId", param).ToList();
StringBuilder text = new StringBuilder();
int userCount = result.Count;
text.Append($"Результаты выдачи бонусов:{Environment.NewLine}");
for (int i = 0; i < userCount; i++)
{
text.Append($"{i + 1}) Cотрудник {result[i].UserName} получил бонусы {result[i].Count} раз");
if (i != (userCount - 1))
{
text.Append(Environment.NewLine);
}
}
foreach (var p in result)
{
Console.WriteLine("{0} - {1}{2}", p.UserName.Trim(), p.Count, Environment.NewLine);
}
_service.SendMessageAsync(chat.id, text.ToString());
}
// public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request)
// {
// using (var connection =
// new SqlConnection("Data Source=db-mssql;Initial Catalog=s18310;Integrated Security=True"))
// using (var command = new SqlCommand())
// {
// command.Connection = connection;
// connection.Open();
// command.CommandText = "exec PromoteStudents @Studies,@Semester";
// command.Parameters.AddWithValue("Studies", request.Studies);
// command.Parameters.AddWithValue("Semester", request.Semester);
// var reader = command.ExecuteReader();
// if (reader.Read())
// {
// EnrollStudentResponse response = new EnrollStudentResponse
// {
// IdEnrollment = (int) reader["IdEnrollment"],
// Semester = (int) reader["Semester"],
// IdStudy = (int) reader["IdStudy"],
// StartDate = DateTime.Parse(reader["StartDate"].ToString())
// };
// return response;
// }
// }
//
// return null;
// }
public EnrollStudentResponse PromoteStudents(PromoteStudentRequest request)
{
var database = new s18310Context();
var enrollment = database.Enrollment.Include(enroll => enroll.IdStudyNavigation).FirstOrDefault(enroll => enroll.Semester == request.Semester && enroll.IdStudyNavigation.Name.Equals(request.Studies));
if (enrollment == null)
{
throw new Exception("No such enrollment to promote");
}
var sem = new SqlParameter("@semester", request.Semester);
var stud = new SqlParameter("@Studies", request.Studies);
database.Database.ExecuteSqlRaw("exec PromoteStudents @Studies,@Semester", stud, sem);
EnrollStudentResponse response = new EnrollStudentResponse
{
IdEnrollment = enrollment.IdEnrollment,
Semester = enrollment.Semester + 1,
IdStudy = enrollment.IdStudy
};
return(response);
}
/// <summary>
/// 更新库存
/// </summary>
/// <param name="strStock"></param>
/// <param name="strID"></param>
/// <returns></returns>
public bool UpdateStock(int strStock, string strID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE MS_ProductPara ");
strSql.Append(" SET Stock = @Stock ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", strID),
new System.Data.SqlClient.SqlParameter("@Stock", strStock)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新订单付款状态
/// </summary>
/// <param name="strOrderID">订单号</param>
/// <param name="strState">付款状态(0:未付款 1:已付款)</param>
/// <returns></returns>
public static bool UpdateOrderPayState(string strOrderID, string strState)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE MS_ProductOrder ");
strSql.Append(" SET PayTime='" + DateTime.Now + "',PayState=@State ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@State", strState),
new System.Data.SqlClient.SqlParameter("@ID", strOrderID)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新订单审核状态(后台用)
/// </summary>
/// <param name="strID"></param>
/// <param name="state"></param>
/// <returns></returns>
public bool UpdatePhotoSubmitRv(string strID, int state)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE MS_PhotoSubmit ");
strSql.Append(" SET Reivew = @Reivew ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", strID),
new System.Data.SqlClient.SqlParameter("@Reivew", state)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
// Thanks to Kevin Trickey for providing the C# implementation of this method!
protected static DataSet SprocGetToken(string token)
{
// create a connection...
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseApplication.Application.ConnectionString);
connection.Open();
// create a command...
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("GetToken", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
// parameters...
System.Data.SqlClient.SqlParameter tokenParam = command.Parameters.Add("@token", System.Data.SqlDbType.VarChar, 256);
tokenParam.Value = token;
// extract the dataset...
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
adapter.Dispose();
// cleanup...
command.Dispose();
connection.Close();
// return dataset...
return(dataset);
}
/// <summary>
/// 发送消息 群发
/// </summary>
/// <param name="messgaeType"></param>
/// <param name="title"></param>
/// <param name="content"></param>
/// <param name="toUserType"></param>
public void InstatMessage(int messgaeType, string title, string content, int toUserType)
{
/*
* @toUserType nvarchar(50),--发送对象
* @Title nvarchar(100),--消息标题
* @content nvarchar(500),--消息内容
* @msgType int --消息类型
*/
string procName = "sp_sendMessage";
System.Data.SqlClient.SqlParameter[] paramerter = new System.Data.SqlClient.SqlParameter[] {
new System.Data.SqlClient.SqlParameter("@toUserType", System.Data.SqlDbType.Int),
new System.Data.SqlClient.SqlParameter("@Title", System.Data.SqlDbType.NVarChar),
new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NVarChar),
new System.Data.SqlClient.SqlParameter("@msgType", System.Data.SqlDbType.Int)
};
paramerter[0].Value = toUserType;
paramerter[1].Value = title;
paramerter[2].Value = content;
paramerter[3].Value = messgaeType;
this.ExProcNoReader(procName, paramerter);
}
/// <summary>
/// 修改优惠券状态
/// </summary>
/// <param name="strCouponID">优惠券ID</param>
/// <param name="strState">优惠券状态</param>
/// <returns></returns>
public bool UpdateCouponState(string strCouponID, string strState)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE ACT_Coupon SET CouponStatus = @CouponStatus ");
strSql.Append(" FROM ACT_Coupon ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@CouponStatus", strState),
new System.Data.SqlClient.SqlParameter("@ID", strCouponID)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <Model.EstadoModel> Select(int codigo, string nome)
{
List <Model.EstadoModel> result = new List <Model.EstadoModel>();
try
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Codigo", codigo),
new System.Data.SqlClient.SqlParameter("@Nome", nome)
};
ExecuteReader("usp_estado_select", parameters, s =>
{
result.Add(new Model.EstadoModel(s));
});
}
catch (Exception ex)
{
Utils.HelperLog.WriteText(ex, "EstadoDAL.Select");
}
return(result);
}
/// <summary>
/// 更新论坛访问
/// </summary>
/// <param name="visit">访问量</param>
/// <param name="strFID">论坛编号</param>
/// <returns></returns>
public bool UpdateForumVist(int visit, string strFID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" UPDATE MS_ForumSet ");
strSql.Append(" SET Visit = @Visit ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", strFID),
new System.Data.SqlClient.SqlParameter("@Visit", visit)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 修改账户口令
/// <param name="strAccountID">账户ID</param>
/// <param name="strPD">账户口令</param>
/// </summary>
public bool UpdateAccountPD(string strAccountID, string strPD)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE SYS_Account SET ");
strSql.Append(" [Password] = @PD ");
strSql.Append(" WHERE ID = @AccountID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@AccountID", strAccountID),
new System.Data.SqlClient.SqlParameter("@PD", strPD)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新用户信息
/// </summary>
/// <param name="card"></param>
public static int UpdateUserVcard(UserVcard card)
{
string sql = "update Users set userName=@userName,GroupID=@GroupID,orderID=@orderID,sex=@sex,Password=@Password,FaceIndex=@FaceIndex,"
+ "CreateRooms=@CreateRooms,isAdmin=@isAdmin,isSendSMS=@isSendSMS,isEditUserData=@isEditUserData,isSendNotice=@isSendNotice,isBroadcast=@isBroadcast,Vcard=@Vcard"
+ " where userID=@userID";
System.Data.SqlClient.SqlParameter[] sqlpar = new System.Data.SqlClient.SqlParameter[]
{ new System.Data.SqlClient.SqlParameter("@userID", card.UserID),
new System.Data.SqlClient.SqlParameter("@userName", card.UserName),
new System.Data.SqlClient.SqlParameter("@GroupID", card.GroupID),
new System.Data.SqlClient.SqlParameter("@orderID", card.OrderID),
new System.Data.SqlClient.SqlParameter("@sex", card.Sex),
new System.Data.SqlClient.SqlParameter("@Password", card.Password),
new System.Data.SqlClient.SqlParameter("@FaceIndex", card.FaceIndex),
new System.Data.SqlClient.SqlParameter("@CreateRooms", card.CreateRooms),
new System.Data.SqlClient.SqlParameter("@isAdmin", card.isAdmin),
new System.Data.SqlClient.SqlParameter("@isSendSMS", card.isSendSMS),
new System.Data.SqlClient.SqlParameter("@isEditUserData", card.isEditUserData),
new System.Data.SqlClient.SqlParameter("@isSendNotice", card.isSendNotice),
new System.Data.SqlClient.SqlParameter("@isBroadcast", card.isBroadcast),
new System.Data.SqlClient.SqlParameter("@Vcard", Factory.CreateXMLMsg(card)), };
return(IMLibrary.SqlData.DataAccess.ExecSql(sql, sqlpar));
}
/// <summary>
/// 根据ID修改竞猜信息状态
/// </summary>
/// <param name="jcID"></param>
/// <returns></returns>
public bool UpdateJCScoreState(string jcID)
{
StringBuilder strSql = new StringBuilder();
int state = 0;
try
{
state = Convert.ToInt32(GetJCScoreValueById("State", jcID));
}
catch (Exception)
{
state = 0;
}
if (state == 1)
{
return(true);
}
else
{
strSql.Append(" UPDATE JC_Score ");
strSql.Append(" SET State = 1 ");
strSql.Append(" WHERE ID = @ID ");
System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ID", jcID)
};
int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), paras);
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public System.Collections.Generic.List <Master_SpUpdateDistrictsReturnModel> Master_SpUpdateDistricts(string action, long?rowid, bool?status, out int procResult)
{
var actionParam = new System.Data.SqlClient.SqlParameter {
ParameterName = "@Action", SqlDbType = System.Data.SqlDbType.VarChar, Direction = System.Data.ParameterDirection.Input, Value = action, Size = 100
};
if (actionParam.Value == null)
{
actionParam.Value = System.DBNull.Value;
}
var rowidParam = new System.Data.SqlClient.SqlParameter {
ParameterName = "@ROWID", SqlDbType = System.Data.SqlDbType.BigInt, Direction = System.Data.ParameterDirection.Input, Value = rowid.GetValueOrDefault(), Precision = 19, Scale = 0
};
if (!rowid.HasValue)
{
rowidParam.Value = System.DBNull.Value;
}
var statusParam = new System.Data.SqlClient.SqlParameter {
ParameterName = "@STATUS", SqlDbType = System.Data.SqlDbType.Bit, Direction = System.Data.ParameterDirection.Input, Value = status.GetValueOrDefault()
};
if (!status.HasValue)
{
statusParam.Value = System.DBNull.Value;
}
var procResultParam = new System.Data.SqlClient.SqlParameter {
ParameterName = "@procResult", SqlDbType = System.Data.SqlDbType.Int, Direction = System.Data.ParameterDirection.Output
};
var procResultData = Database.SqlQuery <Master_SpUpdateDistrictsReturnModel>("EXEC @procResult = [Master].[Sp_UPDATE_Districts] @Action, @ROWID, @STATUS", actionParam, rowidParam, statusParam, procResultParam).ToList();
procResult = (int)procResultParam.Value;
return(procResultData);
}
