Пример #1
2
    protected string generate_forum_name(string group_id)
    {
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;

        SqlConnection connection = new SqlConnection(conn);

        string sqlqry = "SELECT group_name FROM forum_group WHERE group_id=@p1";

        SqlCommand command = new SqlCommand(sqlqry, connection);

        SqlParameter param1 = new SqlParameter();
        param1.SqlDbType = System.Data.SqlDbType.Int;
        param1.ParameterName = "@p1";
        param1.Value = group_id;
        command.Parameters.Add(param1);

        SqlDataReader Reader = null;

        command.Connection.Open();
        Reader = command.ExecuteReader();
        Reader.Read();
        string name = Reader[0].ToString();
        command.Connection.Close();

        return name;
    }
Пример #2
2
    public List<string> consultarMarca(string id)
    {
        List<string> marcas = new List<string>();
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|DBTag.mdf';Integrated Security=True;User Instance=True");
        // Abre a conexão
        conn.Open();
        try
        {
            SqlCommand select = new SqlCommand("SELECT * from TbMarca where id=@id", conn);
            SqlParameter pID = new SqlParameter("id", id);
            select.Parameters.Add(pID);
            // Lê, linha a linha a tabela
            SqlDataReader dr = select.ExecuteReader();
            while (dr.Read())
            {
                marcas.Add(dr["id"].ToString());
                marcas.Add(dr["nome_marca"].ToString());
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString(), ex);
        }

        return marcas;
    }
Пример #3
1
    protected int getTotalCount()
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());

        DataTable dt = new DataTable();

        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM tblContact";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dt);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
        return dt.Rows.Count;
    }
Пример #4
0
    //Función que actualiza un proyecto
    public void actualizaProyecto(int proyectoId, string titulo, string tituloEng, string subtitulo, string subtituloEng, int logo, string descripcion, string descripcionEng, int activo)
    {
        SqlCommand cmd = new SqlCommand();
        SqlParameter sqlPar = null;
        sqlPar = cmd.Parameters.Add("@PROYECTO_ID", SqlDbType.Int);
        sqlPar.Value = proyectoId;
        sqlPar = cmd.Parameters.Add("@TITULO", SqlDbType.VarChar, 200);
        sqlPar.Value = titulo;
        sqlPar = cmd.Parameters.Add("@TITULO_ENG", SqlDbType.VarChar, 200);
        sqlPar.Value = tituloEng;
        sqlPar = cmd.Parameters.Add("@SUBTITULO", SqlDbType.VarChar, 150);
        sqlPar.Value = subtitulo;
        sqlPar = cmd.Parameters.Add("@SUBTITULO_ENG", SqlDbType.VarChar, 150);
        sqlPar.Value = subtituloEng;
        sqlPar = cmd.Parameters.Add("@LOGO", SqlDbType.Bit);
        sqlPar.Value = logo;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION", SqlDbType.VarChar);
        sqlPar.Value = descripcion;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION_ENG", SqlDbType.VarChar);
        sqlPar.Value = descripcionEng;
        sqlPar = cmd.Parameters.Add("@ACTIVO", SqlDbType.Bit);
        sqlPar.Value = activo;

        con.execProcedureVoid(cmd, "spr_ActualizaProyecto");
    }
Пример #5
0
 /// <summary>
 /// Creates and adds parameter to SqlCommand
 /// </summary>
 /// <param name="command">SqlCommand</param>
 /// <param name="parameterName">Paramater Name</param>
 /// <param name="value">Paramater Value</param>
 public void CreateParameter(SqlCommand command, string parameterName, object value)
 {
     SqlParameter param = command.CreateParameter();
     param.ParameterName = parameterName;
     param.Value = value;
     command.Parameters.Add(param);
 }
Пример #6
0
    //Función que añade un proyecto nuevo
    public String anadirProyecto(string titulo, string tituloEng, string subtitulo, string subtituloEng, int logo, string descripcion, string descripcionEng, int activo)
    {
        string valdev = string.Empty;

        SqlCommand cmd = new SqlCommand();
        SqlParameter sqlPar = null;
        sqlPar = cmd.Parameters.Add("@TITULO", SqlDbType.VarChar, 200);
        sqlPar.Value = titulo;
        sqlPar = cmd.Parameters.Add("@TITULO_ENG", SqlDbType.VarChar, 200);
        sqlPar.Value = tituloEng;
        sqlPar = cmd.Parameters.Add("@SUBTITULO", SqlDbType.VarChar, 150);
        sqlPar.Value = subtitulo;
        sqlPar = cmd.Parameters.Add("@SUBTITULO_ENG", SqlDbType.VarChar, 150);
        sqlPar.Value = subtituloEng;
        sqlPar = cmd.Parameters.Add("@LOGO", SqlDbType.Bit);
        sqlPar.Value = logo;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION", SqlDbType.VarChar);
        sqlPar.Value = descripcion;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION_ENG", SqlDbType.VarChar);
        sqlPar.Value = descripcionEng;
        sqlPar = cmd.Parameters.Add("@ACTIVO", SqlDbType.Bit);
        sqlPar.Value = activo;

        valdev = con.execProcedureValor(cmd, "spr_AnadirProyecto");

        return valdev;
    }
    protected void MostrarSeleccao(Object obj, EventArgs e)
    {
        saida.Text = "";

        ddlMunicipios.Items.Clear();
        ddlMunicipios.Items.Insert(0, new ListItem("Seleccione...", "0"));

        //if(dDL.SelectedIndex!=0)
        //{
            //ddlMunicipios.DataSource = municipios[dDL.SelectedIndex-1];
            //ddlMunicipios.DataBind();

            SqlDB Bd = new SqlDB("ConStr_DivAdmin");
            string str = "SELECT NomeMunicipio from Municipios where [CodigoDistrito] = @distrito";

            SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);
            cmd.Parameters.AddWithValue("@distrito",dDL.SelectedItem.Value);
            cmd.Connection = Bd.SqlConDB;

            Bd.SqlConDB.Open();
            SqlDataReader dR = cmd.ExecuteReader();

            //efectuar o data binding
            ddlMunicipios.DataSource = dR;
            ddlMunicipios.DataTextField = "NomeMunicipio";
            ddlMunicipios.DataValueField = "IdMunicipio";
            ddlMunicipios.DataBind();
            dR.Close();
            Bd.SqlConDB.Close();
        //}
    }
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);

        if (!this.IsPostBack)
        {
            //efectuar o data binding
            //dDL.DataSource = distritos;
            //dDL.DataBind();

            SqlDB Bd = new SqlDB("ConStr_DivAdmin");
            string str = "SELECT NomeDistrito from Distritos";

            SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);

            Bd.SqlConDB.Open();
            SqlDataReader dR = cmd.ExecuteReader();

            //efectuar o data binding
            dDL.DataSource = dR;
            dDL.DataTextField = "NomeDistrito";
            dDL.DataBind();
            dR.Close();
            Bd.SqlConDB.Close();
        }

        return;
    }
Пример #9
0
    public building(int id)
    {
        try{
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mainconn"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * FROM building WHERE id = @id", conn);
            cmd.Parameters.AddWithValue("id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            ID = new dataObject(id);
            Name = new dataObject(dr["Name"].ToString());
            Address1 = new dataObject(dr["Address1"].ToString());
            Address2 = new dataObject(dr["Address2"].ToString());
            Address3 = new dataObject(dr["Address3"].ToString());
            City = new dataObject(dr["City"].ToString());
            State = new dataObject(dr["State"].ToString());
            ZipCode = new dataObject(dr["ZipCode"].ToString());
            PhoneNumber = new dataObject(dr["PhoneNumber"].ToString());
            PhoneNumberExtension = new dataObject(dr["PhoneNumberExtension"].ToString());
            FaxNumber = new dataObject(dr["FaxNumber"].ToString());
            MobilePhoneNumber = new dataObject(dr["MobilePhoneNumber"].ToString());
            EmailAddress = new dataObject(dr["EmailAddress"].ToString());
            Contact = new dataObject(dr["Contact"].ToString());
            Active = new dataObject(Convert.ToBoolean(dr["Active"]));
            Comment = new dataObject(dr["Comment"].ToString());
            InputDate = new dataObject(Convert.ToDateTime(dr["InputDate"]));
            InputEmploee = new dataObject(new employee(Convert.ToInt32(dr["InputEmployeeID"])));

        }catch(Exception e){
            throw e;
        }
    }
Пример #10
0
    protected void btnSave_Click(object sender, EventArgs e)
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertITJob";
                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@ITJobHeading", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@ITJobDesc", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@ITJobDate", SqlDbType.DateTime));

                cmd.Parameters["@ITJobHeading"].Value = txtITJob.Text;
                cmd.Parameters["@ITJobDesc"].Value = txtITDesc.Text;
                cmd.Parameters["@ITJobDate"].Value = System.DateTime.Now;

                cmd.Parameters["@Exists"].Value = 0;
                cmd.ExecuteNonQuery();
                int retVal = (int)cmd.Parameters["@Exists"].Value;
            }
        }
        LoadData();
        PanelShow.Visible = true;
        PanelAdd.Visible = false;
        txtITJob.Text = "";
        txtITDesc.Text = "";
    }
Пример #11
0
    private void AddNewRecord(string contactid, string name, string phone, string fileName)
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());
        string sqlStatement = string.Empty;

            sqlStatement = "INSERT INTO tblContact" +
                            " VALUES (@contactid,@name,@phone,@image)";

        try
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@contactid", contactid);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@image", fileName);

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            connection.Close();
        }
    }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con);

        cmd.Parameters.AddWithValue("@username", Textname.Text);
        cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
        cmd.Parameters.AddWithValue("@comment", txtComment.Text);
        cmd.Parameters.AddWithValue("@postedate", DateTime.Now);
        con.Open();
        //SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con);
        cmd.ExecuteNonQuery();
        // con.Close();

        SqlCommand cmd1 = new SqlCommand("select no from [user] where username='******' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con);
        SqlDataReader dr = cmd1.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text = dr["no"].ToString();
        }

        Textname.Text = string.Empty;
        txtSubject.Text = string.Empty;
        txtComment.Text = string.Empty;
        BindRepeaterData();
    }
Пример #13
0
    protected int DoUpdate(int finished)
    {
        int i = 0;
        using (SqlConnection conn = new DB().GetConnection())
        {
            string sql = "Update Shows set Title=@Title,Abs=@Abs,Catalog=@Catalog,CatalogID=@CatalogID,CoverPhotoURL=@CoverPhotoURL,CDT=@CDT,Status=@Status,Orders=@Orders,Finished=@Finished where ID=@ID";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Title", TitleTB.Text);
            cmd.Parameters.AddWithValue("@Abs", AbsTextBox.Text);
            cmd.Parameters.AddWithValue("@Catalog", CatalogsDDL.SelectedItem.Text);
            cmd.Parameters.AddWithValue("@CatalogID", CatalogsDDL.SelectedValue);
            cmd.Parameters.AddWithValue("@CoverPhotoURL", CoverPhoto.ImageUrl);
            cmd.Parameters.AddWithValue("@CDT", CDT_TextBox.Text);
            // RoleID={1,Administrator},{2,Editor},{3,Contributor},{4,Author}
            int RoleID = Convert.ToInt16(Session["RoleID"].ToString());
            if (RoleID > 2)
            {
                cmd.Parameters.AddWithValue("@Status", 0); //状态:新投稿/待审核=0,审核已过=1,审核未过=2

            }
            else
            {
                cmd.Parameters.AddWithValue("@Status", 1); //状态:新投稿/待审核=0,审核已过=1,审核未过=2
            }
            cmd.Parameters.AddWithValue("@Orders", Orders.Text);
            cmd.Parameters.AddWithValue("@Finished", finished);
            cmd.Parameters.AddWithValue("@ID",IDLabel.Text);

            conn.Open();
            i = cmd.ExecuteNonQuery();
        }
        return i;
    }
Пример #14
0
    public String modificarSucursales(DataSucursal ds)
    {
        string msg = "";

        SqlCommand cmd = new SqlCommand("USP_ACTUALIZAR_SUCURSAL", cn.getCn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = ds.Id;
        cmd.Parameters.Add("@Sucur", SqlDbType.VarChar).Value = ds.Sucursal;
        cmd.Parameters.Add("@descSucur", SqlDbType.VarChar).Value = ds.Descripcion;
        cmd.Parameters.Add("@telefono", SqlDbType.VarChar).Value = ds.Telefono;
        cmd.Parameters.Add("@idDist", SqlDbType.Char).Value = ds.Distrito;
        cmd.Parameters.Add("@direccion", SqlDbType.VarChar).Value = ds.Direccion;

        cn.getCn.Open();
        try
        {
            msg = cmd.ExecuteNonQuery().ToString() + " Sucursal actualizado";
        }
        catch (Exception ex)
        {
            msg = ex.Message;

        }
        finally
        {
            cn.getCn.Close();
        }
        return msg;
    }
Пример #15
0
        protected void Fill_User_Header()
        {
            DataView view = null;
            SqlConnection con;
            SqlCommand cmd = new SqlCommand();
            DataSet ds     = new DataSet();
            DataTable dt   = new DataTable();
            System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp");
            System.Configuration.ConnectionStringSettings connString;
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"];
            con            = new SqlConnection(connString.ToString());
            cmd.Connection = con;
            con.Open();
            string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor
                         FROM Criterios
                         WHERE Criterio_ID = " + @Criterio_ID;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds);
            dt = ds.Tables[0];
            view = new DataView(dt);
            foreach (DataRowView row in view)
            {
                Lbl_Fecha_Desde.Text    = row["Fecha_Desde"].ToString("dd-MM-yyyy");
                Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString();
                Lbl_Region.Text         = row["Region"].ToString();
                Lbl_Supervisor.Text     = row["Supervisor"].ToString();
             }

            con.Close();
        }
Пример #16
0
    public static List<string> SearchEmployees(string prefixText, int count)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["rateMyMPConnectionString"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.CommandText = "SELECT dbo.mpDetails.constituencyId,dbo.userMaster.profilePic, dbo.constituency.constituency, dbo.state.state, dbo.userMaster.firstName, dbo.userMaster.middleName, dbo.userMaster.lastName,dbo.userMaster.profilePic FROM dbo.constituency INNER JOIN dbo.state ON dbo.constituency.stateId = dbo.state.stateId INNER JOIN dbo.mpDetails ON dbo.state.stateId = dbo.mpDetails.permanentStateId AND dbo.constituency.constituencyId = dbo.mpDetails.constituencyId INNER JOIN dbo.userMaster ON dbo.mpDetails.guid = dbo.userMaster.guid where dbo.userMaster.firstName like '%' + @search+'%' or dbo.userMaster.middleName like '%'+ @search+'%' or dbo.userMaster.lastName like '%' + @search+'%' or  dbo.state.state  like '%'+ @search+'%' or dbo.constituency.constituency like '%'+ @search+'%'";
                cmd.Parameters.AddWithValue("@search", prefixText);
                cmd.Connection = conn;
                conn.Open();
                List<string> employees = new List<string>();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        employees.Add(AjaxControlToolkit.AutoCompleteExtender
                           .CreateAutoCompleteItem(string.Format("{0}{1}{2}{3}{4}{5} ",
                           sdr["firstName"] + " ", sdr["middleName"] + " ", sdr["lastName"] + " ,", sdr["constituency"] + " ,", sdr["state"] + " ,", sdr["profilePic"].ToString()),
                           sdr["constituencyId"].ToString()));
                    }
                }
                conn.Close();
                return employees;
            }
        }
    }
Пример #17
0
    void GetMedicalHistory(int ID)
    {
        using (SqlConnection con = new SqlConnection(Helper.GetCon()))
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "SELECT Operation, Details, StartDate, EndDate " +
                "FROM MedicalHistory WHERE DispatchID=@DispatchID";
            cmd.Parameters.AddWithValue("@DispatchID", ID);
            using (SqlDataReader data = cmd.ExecuteReader())
            {
                if (data.HasRows)
                {
                    while (data.Read())
                    {
                        DateTime sDate = Convert.ToDateTime(data["StartDate"].ToString());
                        DateTime eDate = Convert.ToDateTime(data["EndDate"].ToString());

                        txtOperation.Text = data["Operation"].ToString();
                        txtStartDate.Text = sDate.ToString("MM/dd/yyyy");
                        txtEndDate.Text = eDate.ToString("MM/dd/yyyy");
                        txtDetails.Text = data["Details"].ToString();

                    }
                    con.Close();
                }
                else
                {
                    con.Close();
                    Response.Redirect("View.aspx");
                }
            }
        }
    }
Пример #18
0
    public String eliminaSucursales(DataSucursal ds)
    {
        string msg = "";

        SqlCommand cmd = new SqlCommand("USP_ELIMINAR_SUCURSAL", cn.getCn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = ds.Id;

        cn.getCn.Open();
        try
        {
            msg = cmd.ExecuteNonQuery().ToString() + " Sucursal eliminado";
        }
        catch (Exception ex)
        {
            msg = ex.Message;

        }
        finally
        {
            cn.getCn.Close();
        }
        return msg;
    }
Пример #19
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        Label1.Text = Server.HtmlEncode(this.elm1.Value);
        //Label1.Text = this.elm1.Value;

        //String QueryString = "INSERT INTO Article (Id, auteur, date, categorie) VALUES ('7', 'FCW', '', 'Informatique')";

        String QueryString = "INSERT INTO Article_Blog (ID, Auteur, Content, Tag) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "',  '" + this.elm1.Value + "','" + TextBox3.Text + "')";
        SqlConnection Cn = new SqlConnection("server=WAZZUP-PC\\SQLEXPRESS; initial catalog=TestDB; integrated security=true");

        try
        {

            Cn.Open();
            if (Cn != null)
            {
                Label1.Text = "CONNECTER";
                Console.WriteLine("CONNECTER");
            }

            SqlCommand Cmd = new SqlCommand();
            Cmd.CommandText = QueryString;
            //Cmd.CommandType = CommandType.Text;
            Cmd.Connection = Cn;

            Cmd.ExecuteNonQuery();

            Cn.Close();
        }
        catch (Exception Ex)
        {
            Label1.Text = Ex.ToString();
            Cn.Close();
        };
    }
Пример #20
0
    public bool tblPackageTime_Delete(int packageTimeId)
    {
        int i = 0;
        try
        {
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            SqlCommand cmd = new SqlCommand("DELETE FROM tblpackageTime WHERE packageTimeId = @packageTimeId",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = packageTimeId;
            i = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        catch (Exception ex)
        {

        }
        finally
        {
            ConnectionData._MyConnection.Close();
        }
        return i > 0;
    }
    protected void BtnReg_Click(object sender, EventArgs e)
    {
        if (CB1Accept.Checked)
        {
            string str, chk;
            str = "select Ans from Securityimg  where Id = " + Convert.ToInt16(ViewState["code"]);
            cmd = new SqlCommand(str, conn);
            conn.Open();
            chk = cmd.ExecuteScalar().ToString();
            conn.Close();
            if (TBAns.Text == chk)
            {
                str = "insert into Registration (Firstname,Lastname,Countryid,Stateid,Cityid,Username,Password,Emailid) values( '" + TBFName.Text + "','" + TBLName.Text + "'," + DDLCountry.SelectedItem.Value + "," + DDLState.SelectedItem.Value + "," + DDLCity.SelectedItem.Value + ",'" + TBUName.Text + "','" + TBpass.Text + "','" + TBEmail.Text + "')";
                cmd = new SqlCommand(str, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                Response.Redirect("UserHome.aspx");
            }
            else
                status.Text = "Invalid Code";
        }
        else
            status.Text = "Please accept the terms and conditions";

        string path = Server.MapPath("./");
        Response.Write("path");
    }
 protected void btn_giris_Click(object sender, EventArgs e)
 {
     try
     {
         Yardimci.baglanti.Open();
         SqlCommand komut = new SqlCommand();
         komut.CommandText = "SELECT YONETICI_ADI, YONETICI_SIFRE FROM YONETICILER WHERE YONETICI_ADI = @YONETICI_ADI AND YONETICI_SIFRE = @YONETICI_SIFRE";
         komut.Parameters.AddWithValue("@YONETICI_ADI", txt_yon_ad.Text);
         komut.Parameters.AddWithValue("@YONETICI_SIFRE", txt_sifre.Text);
         komut.Connection = Yardimci.baglanti;
         SqlDataReader oku = komut.ExecuteReader();
         if (oku.Read())
         {
             lbl_sonuc.Text = "Giris basarılı";
             Session.Add("yoneticigirisi", true);
             Session.Add("yonetici", txt_yon_ad.Text);
             Response.Redirect("Yonetim.aspx");
         }
         else
         {
             lbl_sonuc.Text = "Kullanıcı adı veya şifre hatalı";
         }
         Yardimci.baglanti.Close();
     }
     catch (Exception hata)
     {
         lbl_sonuc.Text = hata.Message;
         Yardimci.baglanti.Close();
     }
 }
 protected void Page_Load(object sender, EventArgs e)
 {
     if (Page.IsPostBack)
     {
         Page.Validate();
         if (Page.IsValid)
         {
             SqlConnection dbConnection = new SqlConnection("Data Source=cscsql2.carrollu.edu;Initial Catalog=csc319mcrowell;Persist Security Info=True;User ID=csc319mcrowell;Password=393160");
             try
             {
                 dbConnection.Open();
                 string SQLString = "SELECT * FROM students WHERE studentID=" + studentID.Text;
                 SqlCommand checkIDTable = new SqlCommand(SQLString, dbConnection);
                 SqlDataReader idRecords = checkIDTable.ExecuteReader();
                 if (idRecords.Read())
                 {
                     Response.Redirect("ReturningStudent.aspx?studentID=" + studentID.Text);
                     idRecords.Close();
                 }
                 else
                 {
                     validateMessage.Text = "<p>**Invalid student ID**</P>";
                     idRecords.Close();
                 }
             }
             catch (SqlException exception)
             {
                 Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>");
             }
         }
     }
 }
Пример #24
0
    public BaiViet GetABaiViet(int id)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = @"SELECT tblBaiViet.*, tenCM , hoTen
                            FROM tblChuyenMuc INNER JOIN tblBaiViet
                            ON  tblBaiViet.id_CM = tblChuyenMuc.id
                            INNER JOIN tblNguoiDung
                            ON tblNguoiDung.id = tblBaiViet.id_ND
                            WHERE tblBaiViet.id = @id";
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@id", id);
        SqlDataReader rd = cmd.ExecuteReader();
        BaiViet bv = new BaiViet();
        if (rd.Read())
        {
            bv.id_cm = Convert.ToInt32(rd["id_CM"]);
            bv.tenND = Convert.ToString(rd["hoTen"]);
            bv.id_nd = Convert.ToInt32(rd["id_ND"]);
            //bv.tenCM = Convert.ToString(rd["tenCM"]);
            bv.tieuDe = Convert.ToString(rd["tieuDe"]);
            bv.noiDung = Convert.ToString(rd["noiDung"]);
            bv.moTa = Convert.ToString(rd["moTa"]);;
            bv.tuKhoa  = Convert.ToString(rd["tuKhoa"]);
            bv.hinhAnh = Convert.ToString(rd["hinhAnh"]);
            bv.ngayTao = Convert.ToDateTime(rd["ngayTao"]);
            bv.trangThai = Convert.ToInt32(rd["trangThai"]);
            bv.luotXem = Convert.ToInt32(rd["luotXem"]);

        }
        con.Close();
        return bv;
    }
Пример #25
0
 public DBAccounts()
 {
     string constr = ConfigurationManager.ConnectionStrings["Accounts"].ConnectionString;
     Con = new SqlConnection(constr);
     Com = new SqlCommand();
     Com.Connection = Con;
 }
Пример #26
0
    public static List<ReporteNotificacionGeocerca> ObtenerNotificacionesGeocerca(List<string> unidades, List<string> geocercas, DateTime fechaInicial, DateTime fechaFinal, int accion, string cliente)
    {
        List<ReporteNotificacionGeocerca> reporteFinal = new List<ReporteNotificacionGeocerca>();
        List<Vehiculo> vehiculos = AutentificacionBD.VehiculosCliente(cliente);
        string query = "SELECT     Unidad, accion, fechahora, geocerca, nombre, id_reportegeocerca " +
                       "FROM         vReporteGeocercaUTC " +
                       "WHERE     (Unidad IN (";
        foreach (string unidad in unidades)
            query += "'" + unidad + "', ";
        //quitar la última coma y espacio
        query = query.Remove(query.Length - 2);
        //agregar los dos paréntesis finales.
        query += ")) AND (fechahora between @fechainicial AND @fechafinal) ";
        if (accion == ReporteNotificacionGeocerca.AccionFuera)
        {
            query += "AND accion = 'Fuera' ";
        }
        else if(accion == ReporteNotificacionGeocerca.AccionDentro)
        {
            query += "AND accion = 'Dentro' ";
        }
        else if (accion == ReporteNotificacionGeocerca.AccionDentroFuera)
        {
            query += " AND (accion = 'Fuera' OR accion = 'Dentro') ";
        }
        query +="ORDER BY fechahora DESC";

        using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionStringBD"].ConnectionString))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand(query,sqlConnection))
            {
                sqlCommand.Parameters.AddWithValue("@fechainicial", fechaInicial);
                sqlCommand.Parameters.AddWithValue("@fechafinal", fechaFinal);
                Hashtable nombresGeocercas = GeocercasBD.ConsultarGeocercasNombreBD(cliente);
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read()){
                        string geocercaID= (string)reader["geocerca"];
                        if (geocercas.Where(x=>x.Equals(geocercaID)).ToList().Count == 1)
                        {
                            ReporteNotificacionGeocerca reporte = new ReporteNotificacionGeocerca();
                            string sVehiculo = (string)reader["Unidad"];
                            string sAccionBD = (string)reader["accion"];
                            reporte.VehiculoID = sVehiculo;
                            reporte.GeocercaID = geocercaID;
                            reporte.Fecha = (DateTime)reader["fechahora"];
                            reporte.Vehiculo = vehiculos.Find(x=> x.Unidad.Equals(sVehiculo)).Descripcion;
                            //reporte.Accion = sAccionBD == "Dentro"?Resources.Reportes.aspx.Dentro:Resources.Reportes.aspx.Fuera;
                            reporte.iAccion = sAccionBD == "Dentro" ? ReporteNotificacionGeocerca.AccionDentro : ReporteNotificacionGeocerca.AccionFuera;
                            reporte.Geocerca = nombresGeocercas[geocercaID].ToString();
                            reporteFinal.Add(reporte);
                        }
                    }
                }
            }
        }

        return reporteFinal.OrderBy(x => x.Fecha).ToList();
    }
Пример #27
0
 //  To Change status of 'Group' record of specific GroupId from database by stored procedure
 public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table
         SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         DCmd.Parameters.AddWithValue("@GroupId", GroupId);
         DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
         DCmd.Parameters.AddWithValue("@IsActive", IsActive);
         DCmd.Parameters.AddWithValue("@RetMsg", returnmsg);
         try
         {
             return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }
Пример #28
0
 //  To insert 'Group' record in database by stored procedure
 public int InsertGroup(string GroupName, string GroupDescription,bool IsActive, int  LoginUser,string Ret)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspInsertGroup' stored procedure is used to insert record in Group table
         SqlCommand DCmd = new SqlCommand("uspInsertGroup", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         try
         {
             DCmd.Parameters.AddWithValue("@GroupName", GroupName);
             DCmd.Parameters.AddWithValue("@GroupDescription", GroupDescription);
             DCmd.Parameters.AddWithValue("@LoggedInUser", LoginUser);
             DCmd.Parameters.AddWithValue("@IsActive", IsActive);
             DCmd.Parameters.AddWithValue("@RetMsg", Ret);
              return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }
Пример #29
0
    void GetCorporateMembership()
    {
        if (CheckCorporateMembership())
        {
            using (SqlConnection con = new SqlConnection(Helper.GetCon()))
            using (SqlCommand cmd = new SqlCommand())
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "SELECT CorporateID FROM Users " +
                              "WHERE UserID=@UserID";
                cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString());
                int corporateID = (int)cmd.ExecuteScalar();

                cmd.CommandText = "SELECT Name, Length FROM CorporateAccounts " +
                                  "INNER JOIN CorporatePayments ON CorporateAccounts.CorporateID=" +
                                  "CorporatePayments.CorporateID WHERE CorporateAccounts.CorporateID=" +
                                  "@CorporateID";
                cmd.Parameters.AddWithValue("@CorporateID", corporateID);
                SqlDataReader da = cmd.ExecuteReader();
                while (da.Read())
                {
                    txtEmployerName.Text = da["Name"].ToString();
                    txtCorporateLength.Text = da["Length"].ToString();
                    txtCorporateType.Text = "Corporate/Bulk";
                }
            }
        }
        else
        {
            panelCorporate.Visible = false;
        }
    }
Пример #30
0
    public static string PublishActivity(int ClubId, string ActivityContent)
    {
        // 将新增活动存入数据库,并从数据库返回信息及数据
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CZConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        // 存储
        string PublishDate = DateTime.Now.ToString();
        string queryString1 = "Insert Into Activity Values (" + ClubId + ",N'" + ActivityContent + "','" + PublishDate + "')";
        SqlCommand cmd = new SqlCommand(queryString1, conn);
        cmd.ExecuteNonQuery();
        // 查询最后插入的数据,就是新的数据
        string queryString2 = "Select Top 1 * From Activity Where ClubId=" + ClubId + " Order By PublishDate Desc";
        cmd = new SqlCommand(queryString2, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        int Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"].ToString());
        string Content = ds.Tables[0].Rows[0]["Content"].ToString();
        string Date = ds.Tables[0].Rows[0]["PublishDate"].ToString();

        conn.Close();

        // 通过判断前后时间知是否查入成功
        if (PublishDate == Date)
        {
            return "{status:1,id:" + Id + ",content:'" + Content + "',date:'" + Date + "'}";
        }
        else
        {
            return "{status:-1}";
        }
    }
Пример #31
0
    public bool InRunner(string SocietyId, string RunnerVer, string DistrictId)
    {
        bool result = false;
        try
        {
            OpenConnection();
            trans = connection.BeginTransaction();
            commandt = new SqlCommand();
            commandt = connection.CreateCommand();
            commandt.Transaction = trans;
            commandt.CommandType = CommandType.Text;
            commandt.CommandText = "Select count(*) from RunnerRegistration where  RunnerID='" + SocietyId + "'  ";
            string res = Convert.ToString(commandt.ExecuteScalar());
            commandt.Dispose();

            string status = "";
            if (Convert.ToInt16(res) > 0)
            {
                status = "Reg";
            }
            else
            {
                status = "UnReg";
            }

            string date = getRDate_MDY(System.DateTime.Now.ToShortDateString());
            string LogID = SocietyId.ToString() + date;
            string LogIP = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"].ToString();
            string LogDate = date;
            cmd = connection.CreateCommand();
            cmd.Transaction = trans;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "in_RunnerLog";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@LogID", LogID);
            cmd.Parameters.AddWithValue("@LogIP", LogIP);
            cmd.Parameters.AddWithValue("@LogDate", LogDate);
            cmd.Parameters.AddWithValue("@Pc_Id", SocietyId);
            cmd.Parameters.AddWithValue("@District_Code", DistrictId);
            cmd.Parameters.AddWithValue("@Society_Id", SocietyId);
            cmd.Parameters.AddWithValue("@RunnerID", SocietyId);
            cmd.Parameters.AddWithValue("@Status", status);
            cmd.Parameters.AddWithValue("@RunnerVer", RunnerVer);
            cmd.Parameters.AddWithValue("@DayCount", "0");
            int req = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (req > 0)
            {
                trans.Commit();
                result = true;
            }
        }
        catch (Exception)
        {
            trans.Rollback();
            CloseConnection();
        }
        finally
        {
            CloseConnection();
        }
        return result;
    }
    protected void btnSignIn_Click(object sender, EventArgs e)
    {
        string counter  = string.Empty;
        string TCNumber = txtUserTC.Text;
        string password = txtPassword.Text;

        sIpAddress = HttpContext.Current.Request.UserHostAddress;
        LogMessage("### Information ### => ipAddress = " + sIpAddress, TCNumber, logFileName);

        SqlConnection connection = new SqlConnection(GetXmlConfigParameter("database", "ConnectionString"));
        SqlCommand    command    = new SqlCommand();
        SqlDataReader reader     = null;

        string Sql = "EXEC [ExamProctorProject].[dbo].[spLoginControl]" + sqlVal(TCNumber, "'") + sqlVal(password, "'");

        LogMessage("### SP Login Control ### => Sql :   " + Sql, TCNumber, logFileName);

        #region TCNumber & Password Control
        try
        {
            connection.Open();
            command             = new SqlCommand("[ExamProctorProject].[dbo].[spLoginControl]", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@TCNo", TCNumber);
            command.Parameters.AddWithValue("@password", password);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                counter = reader["numofContact"].ToString();
            }
        }
        catch (Exception ex)
        {
            LogMessage("!!! Error !!! => [ExamProctorProject].[dbo].[spLoginControl] , Error Message = " + ex.Message.ToString(), TCNumber, logFileName);
            counter = string.Empty;
        }

        finally
        {
            command.Dispose();
            reader = null;
            connection.Close();
        }

        LogMessage("### Information ### => counter = " + counter.ToString(), TCNumber, logFileName);
        #endregion

        #region Get User Information
        if (counter == "1")
        {
            int error = 0;
            sTCNumber = TCNumber;
            try
            {
                connection = new SqlConnection(GetXmlConfigParameter("database", "ConnectionString"));
                connection.Open();
                command             = new SqlCommand("[ExamProctorProject].[dbo].[spAllInformation]", connection);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@sLogin", TCNumber);
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    sUserTypeID = Convert.ToInt32(reader["UserTypeID"]);

                    if (sUserTypeID != 7)
                    {
                        sDepartmentID = Convert.ToInt32(reader["UserDepartmentID"]);
                        sFacultyID    = Convert.ToInt32(reader["UserFacultyID"]);
                        if (sUserTypeID == 1)
                        {
                            sStudentNo = Convert.ToInt32(reader["StudentNumber"]);
                            sClassDeg  = Convert.ToInt32(reader["UserDegree"]);
                        }
                    }
                    sTitle = reader["UserTitle"].ToString();
                }

                LogMessage("### Information ### => sUserTypeID = " + sUserTypeID.ToString(), TCNumber, logFileName);
                LogMessage("### Information ### => sDepartmentID = " + sDepartmentID.ToString(), TCNumber, logFileName);
                LogMessage("### Information ### => sFacultyID = " + sFacultyID.ToString(), TCNumber, logFileName);
                LogMessage("### Information ### => sClassDegree = " + sClassDeg.ToString(), TCNumber, logFileName);
                LogMessage("### Information ### => sStudentNo = " + sStudentNo.ToString(), TCNumber, logFileName);
                LogMessage("### Information ### => sTitle = " + sTitle.ToString(), TCNumber, logFileName);
            }
            catch (Exception ex)
            {
                error   = 1;
                counter = string.Empty;
                LogMessage("!!! Error !!! => [ExamProctorProject].[dbo].[spAllInformation] , Error Message = " + ex.Message.ToString(), TCNumber, logFileName);
            }
            finally
            {
                command.Dispose();
                reader = null;
                connection.Close();
            }
            if (error == 0)
            {
                sLoginControl = 1;
                LogMessage("### Information ### => Response.Redirect(LoginAccess.aspx) ", TCNumber, logFileName);

                // ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openModal();", true);
                //getPhoneNumber();
                Response.Redirect("HomePage.aspx");
            }
            else
            {
                //ShowMessage("There is no user with this information, please check your information");
            }
        }
        else
        {
            //ShowMessage("There is no user with this information, please check your information");
        }
        #endregion
    }
Пример #33
0
        public List<Bandera> Buscar_Bandera()
        {



            //string SQL = "Data Source=.;Initial Catalog=GTDB; Integrated Security=False;User Id=SA;Password=CAPUFE";
            string SQL = "Data Source=.;Initial Catalog=GTDBPruebas; Integrated Security=False;User Id=Sa;Password=CAPUFE";

            SqlConnection ConexionSQL = new SqlConnection(SQL);
            List<Bandera> Lista = new List<Bandera>();
            Bandera NewObject = new Bandera();

            using (SqlCommand SqlCommand = new SqlCommand("SELECT convert(datetime,Fecha)Fecha, Evento FROM Historico WHERE Fecha = (SELECT MAX(Fecha) FROM Historico) group by Fecha, Evento", ConexionSQL))
            {
                try
                {
                    ConexionSQL.Open();
                    SqlCommand.ExecuteNonQuery();
                    SqlDataAdapter sqlData = new SqlDataAdapter(SqlCommand);
                    DataTable table = new DataTable();
                    sqlData.Fill(table);

                    if (table.Rows.Count > 0)
                    {

                        foreach (DataRow item in table.Rows)
                        {
                            Lista.Add(new Bandera
                            {
                                Bandera_Nueva = Convert.ToDateTime(item["Fecha"].ToString()),
                                Evento = item["Evento"].ToString()
                            });
                        }
                    }
                    else
                    {
                        return null;
                    }

                }
                catch (Exception Ex)
                {
                    using (StreamWriter file = new StreamWriter(path + archivo, true))
                    {
                        file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + "" + "Bandera"); //se agrega información al documento
                        file.Dispose();
                        file.Close();
                    }
                    StopService();

                }
                finally
                {
                    ConexionSQL.Close();
                }


                return Lista;

            }


        }
        private void btnConsultarMotorista_Click(object sender, EventArgs e)
        {
            // Conexao objMotorista = new Conexao();
            incluirMotorista _model = new incluirMotorista();
            List<incluirMotorista> _lstMotorista = new List<incluirMotorista>();
            string strSql = "SELECT * FROM[dbo].[Motoristas] with(nolock) where Matricula = @Matricula";
            SqlConnection sqlCon = new SqlConnection(strCon);
            SqlCommand comando = new SqlCommand(strSql, sqlCon);

            comando.Parameters.Add("@Matricula", SqlDbType.VarChar).Value = txbConsultarMotorista.Text;

            try
            {
                if (txbConsultarMotorista.Text == string.Empty)
                {
                    throw new Exception("Você precisa digitar uma matricula!");
                }


                sqlCon.Open();

                SqlDataReader dr = comando.ExecuteReader();


                if (dr.HasRows == false)
                {
                    throw new Exception("Matricula não encontrada!");
                }
                else
                {
                    while (dr.Read())
                    {
                        _lstMotorista.Add(new incluirMotorista(

                            _model.Matricula = Convert.ToString(dr["Matricula"]),
                            _model.Nome = Convert.ToString(dr["Nome"]),
                            _model.Cargo = Convert.ToString(dr["Cargo"]),
                            _model.DataNascimento = dr["Data_nascimento"] != DBNull.Value ? Convert.ToDateTime(dr["Data_nascimento"]) : DateTime.MinValue,
                            _model.Rg = Convert.ToString(dr["RG"]),
                            _model.Cpf = Convert.ToString(dr["CPF"]),
                            _model.Cnh = Convert.ToString(dr["CNH"]),
                            _model.Categoria = Convert.ToString(dr["Categoria"]),
                            _model.Cep = Convert.ToString(dr["CEP"]),
                            _model.Endereco = Convert.ToString(dr["Endereco"]),
                            _model.Uf = Convert.ToString(dr["UF"]),
                            _model.Cidade = Convert.ToString(dr["Cidade"]),
                            _model.Bairro = Convert.ToString(dr["Bairro"]),
                            _model.DataAdmissao = dr["Data_admissao"] != DBNull.Value ? Convert.ToDateTime(dr["Data_admissao"]) : DateTime.MinValue,
                            _model.DataExame = dr["Data_exame"] != DBNull.Value ? Convert.ToDateTime(dr["Data_exame"]) : DateTime.MinValue,
                            _model.AntCriminais = Convert.ToString(dr["Antecedentes_Criminais"])
                        ));
                    }
                }

                if (_lstMotorista.Count > 0)
                {
                    dgvConsultarMotorista.DataSource = _lstMotorista;
                    txbConsultarMotorista.Text = string.Empty; 
                }
                else
                {
                    MessageBox.Show("Nenhum registro encontrado!");
                }
            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }

            finally
            {
                sqlCon.Close();

            }

        }
    public static List <Common> getPhoneNumber()
    {
        string sTCNumber = HttpContext.Current.Session["sTCNumber"].ToString();

        string        firstphoneNum    = string.Empty;
        string        secondphoneNum   = string.Empty;
        string        connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection connection       = new SqlConnection(connectionstring);
        SqlCommand    command          = new SqlCommand();
        SqlDataReader reader           = null;

        string Sql = "EXEC [ExamProctorProject].[dbo].[spGetPhoneNum] " + sqlValStatic(sTCNumber, "'");

        LogMessageStatic("### SP Phone Number Control ### => Sql  :   " + Sql, sTCNumber, logFileName);

        try
        {
            connection.Open();
            command             = new SqlCommand("[ExamProctorProject].[dbo].[spGetPhoneNum]", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@sLogin", sTCNumber);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                firstphoneNum  = reader["UserFirstPhone"].ToString();
                secondphoneNum = reader["UserSecondPhone"].ToString();
            }
        }

        catch (Exception ex)
        {
            LogMessageStatic("!!! Error !!! => [ExamProctorProject].[dbo].[spGetPhoneNum] , Error Message = " + ex.Message.ToString(), sTCNumber, logFileName);
        }

        finally
        {
            command.Dispose();
            reader = null;
            connection.Close();
        }
        LogMessageStatic("### Information ### => firstPhoneNumber = " + firstphoneNum.ToString(), sTCNumber, logFileName);
        LogMessageStatic("### Information ### => secondPhoneNumber = " + secondphoneNum.ToString(), sTCNumber, logFileName);

        List <Common> phoneNumbers = new List <Common>();
        Common        phoneNumber  = new Common();

        phoneNumber.Text  = "Choose...";
        phoneNumber.Value = "0";
        phoneNumbers.Add(phoneNumber);


        if (!string.IsNullOrEmpty(firstphoneNum.Replace(" ", "")))
        {
            phoneNumber       = new Common();
            phoneNumber.Text  = firstphoneNum;
            phoneNumber.Value = "1";
            phoneNumbers.Add(phoneNumber);
        }
        if (!string.IsNullOrEmpty(secondphoneNum.Replace(" ", "")))
        {
            phoneNumber       = new Common();
            phoneNumber.Text  = secondphoneNum;
            phoneNumber.Value = "2";
            phoneNumbers.Add(phoneNumber);
        }
        return(phoneNumbers);
    }
        public void Execute(DataSet dataSet, CancellationToken cancellationToken)
        {
            try
            {
                if (dataSet.Tables.Count > 0)
                {
                    using (var sqlConnection = new SqlConnection(InsiteDbConnectionString))
                    {
                        sqlConnection.Open();

                        //Load DataTable in to SQL Server Temp Table
                        const string createTempTableSql = @"Create table #ProductFilter 
                                                            (Name varchar(32),ERPDescription varchar(max),ProductCode varchar(50),PriceCode varchar(50),UnitOfMeasure varchar(50),Sku varchar(100),
                                                            ActivateOn varchar(100),TaxCode1 varchar(50),ShippingWeight decimal(18,3), QtyPerPackage decimal(18,3),
                                                            ERPNumber varchar(50),UPCCode varchar(50),TaxCategory varchar(50),BasicListPrice decimal(18,3), 
                                                            IsDiscontinued varchar(100), ManufacturerItem varchar(100),SuspendCode varchar(10),
                                                            LastMaintenanceDate varchar(100),Price1 decimal(18,3), Price2 decimal(18,3), 
                                                            Price3 decimal(18,3), Price4 decimal(18,3), Price5 decimal(18,3),AltCnv varchar(100),
                                                            Replacement varchar(32), ItemContrCode varchar(32), --RestrictionCode varchar(10), 
                                                            PrdTaxId varchar(100), QtyBrkCls varchar(50), VolumeQtyGroupName varchar(max) , WareHouseID nvarchar(max))";

                        using (var command = new SqlCommand(createTempTableSql, sqlConnection))
                        {
                            command.CommandTimeout = CommandTimeOut;
                            command.ExecuteNonQuery();
                        }
                        WriteToServer(sqlConnection, "tempdb..#ProductFilter", dataSet.Tables[0]);

                        // Merge the data from the Temp Table
                        const string productMerge = @"  
                                                       Update #ProductFilter set
                                                        Name =RTRIM(LTRIM(Name)), ERPDescription =RTRIM(LTRIM(ERPDescription)), ProductCode =RTRIM(LTRIM(ProductCode)),
                                                        PriceCode =RTRIM(LTRIM(PriceCode)), UnitOfMeasure =RTRIM(LTRIM(UnitOfMeasure)), Sku =RTRIM(LTRIM(Sku)),
                                                        ActivateOn =RTRIM(LTRIM(ActivateOn)), TaxCode1 =RTRIM(LTRIM(TaxCode1)), ShippingWeight =RTRIM(LTRIM(ShippingWeight)), 
                                                        QtyPerPackage =RTRIM(LTRIM(QtyPerPackage)), ERPNumber =RTRIM(LTRIM(ERPNumber)), UPCCode =RTRIM(LTRIM(UPCCode)),
                                                        TaxCategory =RTRIM(LTRIM(TaxCategory)), BasicListPrice =RTRIM(LTRIM(BasicListPrice)), IsDiscontinued =RTRIM(LTRIM(IsDiscontinued)), 
                                                        ManufacturerItem =RTRIM(LTRIM(ManufacturerItem)), SuspendCode =RTRIM(LTRIM(SuspendCode)), LastMaintenanceDate =RTRIM(LTRIM(LastMaintenanceDate)),
                                                        Price1 =RTRIM(LTRIM(Price1)),  Price2 =RTRIM(LTRIM(Price2)),  Price3 =RTRIM(LTRIM(Price3)),  Price4 =RTRIM(LTRIM(Price4)),  Price5 =RTRIM(LTRIM(Price5)),AltCnv = RTRIM(LTRIM(AltCnv)),
                                                        Replacement =RTRIM(LTRIM(Replacement)), ItemContrCode=RTRIM(LTRIM(ItemContrCode)),--RestrictionCode = LTRIM(RTRIM(RestrictionCode)),
                                                        PrdTaxId = LTRIM(RTRIM(PrdTaxId)), QtyBrkCls = LTRIM(RTRIM(QtyBrkCls)), VolumeQtyGroupName = LTRIM(RTRIM(VolumeQtyGroupName)), WareHouseID =RTRIM(LTRIM(WareHouseID))


                                                        Declare @currentdate  as datetime 
                                                        SET @currentdate = GETDATE();
                                                        Declare @ProductProperty Table (ProductId uniqueidentifier, IsDiscontinued varchar(max),PriceClass nvarchar(50),
                                                        Price1 nvarchar(50),Price2 nvarchar(50),Price3 nvarchar(50),Price4 nvarchar(50),Price5 nvarchar(50),AltCnv nvarchar(50),
                                                        ItemContrCode nvarchar(50),ERPPriceMatrix nvarchar(max));

                                                        -- BUSA-501 : To populate QTYPERPKG from ZXPRODUCT view into Insite Product.MultipleSaleQty column Starts  
                                                        select distinct pr.Id as ProductId,pf.Name,pf.ERPDescription,pf.ProductCode,pf.UnitOfMeasure,pf.Sku,pf.ActivateOn,pf.TaxCode1,pf.ShippingWeight,pf.ERPNumber,pf.UPCCode,pf.PrdTaxId,pf.BasicListPrice,pf.IsDiscontinued,pf.SuspendCode,pf.ManufacturerItem,pf.QtyPerPackage,pf.PriceCode,pf.Price1,pf.Price2,pf.Price3,pf.Price4,pf.Price5,pf.AltCnv,pf.ItemContrCode,--pf.RestrictionCode,
                                                        stuff((SELECT '|'+ concat('CompanyNo=' +cast(mxcono as varchar(25))+',PriceDiscountCode='+
                                                        cast(mxprds as varchar(25))+',Discount='+cast(mxdscp as varchar(25))+',MarkupCode='+
                                                        cast(mxdsmr as varchar(25))+',CustomerPriceList=',mxcprl)
                                                        FROM OEMTX WHERE mxprcl = pf.PriceCode FOR XML PATH('')),1,1,'') as ERPPriceMatrix,CASE WHEN ISNULL(pf.QtyPerPackage,0) > 0 THEN 'Multiple Only' ELSE '' END AS 'RoundingRule',pf.QtyBrkCls,pf.VolumeQtyGroupName
                                                        into #UpdateProductList from #ProductFilter pf join Product pr on pr.Name= pf.Name
                                                        left outer join OEMTX otx on otx.mxprcl= pf.pricecode

																		  
                                                        MERGE INTO Product AS TARGET USING
                                                        (select distinct pf.Name,pf.ERPDescription,pf.ProductCode,pf.PriceCode,pf.UnitOfMeasure,pf.Sku,pf.ActivateOn,pf.TaxCode1,pf.ShippingWeight,pf.ERPNumber,pf.UPCCode,pf.PrdTaxId,pf.BasicListPrice,pf.IsDiscontinued,pf.SuspendCode,pf.ManufacturerItem,pf.QtyPerPackage,pf.Price1,pf.Price2,pf.Price3,pf.Price4,
                                                        pf.Price5,pf.AltCnv,pf.ItemContrCode,--pf.RestrictionCode, 
                                                        CASE WHEN ISNULL(pf.QtyPerPackage,0) > 0 THEN 'Multiple Only' ELSE '' END AS 'RoundingRule',pf.QtyBrkCls,pf.VolumeQtyGroupName,
                                                        stuff((SELECT '|'+ concat('CompanyNo=' +cast(mxcono as varchar(25))+',PriceDiscountCode='+
                                                        cast(mxprds as varchar(25))+',Discount='+cast(mxdscp as varchar(25))+',MarkupCode='+cast(mxdsmr as varchar(25))+
                                                        ',CustomerPriceList=',mxcprl)FROM OEMTX WHERE mxprcl = pf.PriceCode FOR XML PATH('')),1,1,'') as ERPPriceMatrix
                                                        from #ProductFilter pf left outer join OEMTX otx on otx.mxprcl= pf.pricecode WHERE Name NOT LIKE '%.%') 
                                                        AS SOURCE
                                                        ON TARGET.Name =SOURCE.Name
                                                        WHEN NOT MATCHED THEN 
                                                        INSERT(Name,ShortDescription,ERPDescription,ProductCode,PriceCode,UnitOfMeasure,Sku,ActivateOn,DeactivateOn,TaxCode1,ShippingWeight,
	                                                            ERPNumber, UPCCode,TaxCategory,BasicListPrice,IsDiscontinued,ManufacturerItem,QtyPerShippingPackage,UrlSegment,ContentManagerId,--RestrictionGroupId,
                                                                MultipleSaleQty,RoundingRule,ModelNumber,PriceBasis)
                                                        VALUES(SOURCE.Name,ISNULL(SOURCE.ERPDescription,''),ISNULL(SOURCE.ERPDescription,''),SOURCE.ProductCode,SOURCE.PriceCode,
	                                                            ISNULL(SOURCE.UnitOfMeasure,''),ISNULL(SOURCE.Sku,''),CONVERT(datetime,convert(char(8),SOURCE.ActivateOn),102),
	                                                            @currentdate,ISNULL(SOURCE.TaxCode1,''),SOURCE.ShippingWeight,ISNULL(SOURCE.ERPNumber,''),ISNULL(SOURCE.UPCCode,''),
	                                                            ISNULL(SOURCE.PrdTaxId,''),SOURCE.BasicListPrice,(CASE WHEN (SOURCE.IsDiscontinued = '530' OR SOURCE.SuspendCode='S') THEN 1 ELSE 0 END) ,
	                                                            ISNULL(SOURCE.ManufacturerItem,''), ISNULL(SOURCE.QtyPerPackage,1),SOURCE.Name, newid(),
		                                                        --(select Id from RestrictionGroup where Name=SOURCE.RestrictionCode),
                                                                ISNULL(SOURCE.QtyPerPackage,0),RoundingRule,ISNULL(SOURCE.VolumeQtyGroupName,''),ISNULL(SOURCE.QtyBrkCls,''))
                                                        OUTPUT Inserted.Id,Inserted.IsDiscontinued,SOURCE.PriceCode,SOURCE.Price1,SOURCE.Price2,SOURCE.Price3,SOURCE.Price4,SOURCE.Price5,SOURCE.AltCnv,
                                                        SOURCE.ItemContrCode,SOURCE.ERPPriceMatrix
		                                                        into @ProductProperty(ProductId,IsDiscontinued,PriceClass,Price1,Price2,Price3,Price4,Price5,AltCnv,ItemContrCode,ERPPriceMatrix);

                                                        MERGE INTO Product AS TARGET USING
                                                            (select  * from #UpdateProductList)
                                                        AS SOURCE
                                                        ON TARGET.Id =SOURCE.ProductId 
                                                        WHEN MATCHED THEN 
                                                            UPDATE SET	ERPDescription=ISNULL(SOURCE.ERPDescription,''),ProductCode=ISNULL(SOURCE.ProductCode,''),PriceCode=ISNULL(SOURCE.PriceCode,''),
			                                                        UnitOfMeasure=ISNULL(SOURCE.UnitOfMeasure,''),Sku=ISNULL(SOURCE.Sku,''),
			                                                        ActivateOn=CONVERT(datetime,convert(char(8),SOURCE.ActivateOn),102),			
			                                                        TaxCode1=ISNULL (SOURCE.TaxCode1,''),ShippingWeight=SOURCE.ShippingWeight,ERPNumber=ISNULL(SOURCE.ERPNumber,''),
			                                                        UPCCode=ISNULL(SOURCE.UPCCode,''),TaxCategory=ISNULL(SOURCE.PrdTaxId,''),
			                                                        BasicListPrice=SOURCE.BasicListPrice,IsDiscontinued= (CASE WHEN (SOURCE.IsDiscontinued = '530' OR SOURCE.SuspendCode='S') THEN 1 ELSE 0 END),
			                                                        ManufacturerItem=ISNULL(SOURCE.ManufacturerItem,''),QtyPerShippingPackage=ISNULL(SOURCE.QtyPerPackage,1),
			                                                        --RestrictionGroupId=(select Id from RestrictionGroup where Name = RestrictionCode),
                                                                    MultipleSaleQty=ISNULL(SOURCE.QtyPerPackage,0),RoundingRule=SOURCE.RoundingRule,ModelNumber=ISNULL(VolumeQtyGroupName,''),PriceBasis=ISNULL(QtyBrkCls,'');

                                                                    -- BUSA-501 : To populate QTYPERPKG from ZXPRODUCT view into Insite Product.MultipleSaleQty column Ends

                                                            MERGE INTO Product AS TARGET USING
                                                            (select pr.Id as RProductId,pf.Name as Name from #ProductFilter pf 
	                                                        join Product pr on (pf.IsDiscontinued = '530' or pf.SuspendCode='S') and pf.Replacement = pr.Name)
	                                                        AS SOURCE
                                                        ON TARGET.Name = SOURCE.Name and TARGET.IsDiscontinued = 1
                                                        WHEN MATCHED THEN 
                                                            UPDATE SET	ReplacementProductId =  SOURCE.RProductId;


                                                        MERGE INTO ContentManager AS TARGET
	                                                        USING Product AS SOURCE	ON TARGET.ID = SOURCE.CONTENTMANAGERID
                                                        WHEN NOT MATCHED THEN
	                                                        INSERT (Id,Name) 
	                                                        VALUES (SOURCE.CONTENTMANAGERID,'Product');                                                      
                                                        

                                                            Declare @warehouseId as varchar(max) 
                                                            Declare @WareHouseName as nvarchar(50)

                                                            SET @WareHouseName = (SELECT Distinct WareHouseID FROM [#ProductFilter])
                                                            SET @warehouseId = (SELECT [Id] FROM [Warehouse] WHERE Name = @WareHouseName)

                                                        --SET @warehouseId = (SELECT [Id] FROM [Warehouse] WHERE Name = 'B1')
                                                        

                                                            MERGE INTO ProductWarehouse AS TARGET USING
                                                            (select  ProductId,IsDiscontinued  from @ProductProperty) 
	                                                        AS SOURCE
                                                            ON TARGET.ProductId =SOURCE.ProductId 
                                                            WHEN NOT MATCHED THEN 
                                                            INSERT(ProductId,WarehouseId,ErpQtyAvailable)
                                                            VALUES(SOURCE.ProductId,ISNULL(@warehouseId,''),(CASE WHEN SOURCE.IsDiscontinued = 0 THEN 100 ELSE 0 END))
                                                            WHEN MATCHED THEN
                                                            UPDATE SET  ProductId=SOURCE.ProductId,WarehouseId=ISNULL(@warehouseId,''),
                                                            ErpQtyAvailable=(CASE WHEN SOURCE.IsDiscontinued = 0 THEN 100 ELSE 0 END);

                                                            MERGE INTO CustomProperty AS TARGET USING
	                                                        (
		                                                        SELECT ProductId, Name, Value FROM (SELECT ProductId,CAST(PriceClass as varchar(max)) PriceClass,
		                                                        CAST(Price1 as varchar(max)) Price1,CAST(Price2 as varchar(max)) Price2, CAST(Price3 as varchar(max)) Price3,
		                                                        CAST(Price4 as varchar(max)) Price4,CAST(Price5 as varchar(max)) Price5,CAST(AltCnv as varchar(max)) AltCnv,
		                                                        CAST(ItemContrCode as varchar(max)) ProductContractCode,
		                                                        CAST(ERPPriceMatrix as varchar(max)) ERPPriceMatrix  FROM @ProductProperty) pp 
		                                                        UNPIVOT (Value FOR Name IN (PriceClass,Price1,Price2,Price3,Price4,Price5,AltCnv,
		                                                        ProductContractCode,ERPPriceMatrix)) as ppvt
		                                                        UNION
		                                                        SELECT ProductId, Name, Value FROM (SELECT ProductId,CAST('True' as varchar(max)) IsModified,
                                                                CAST(PriceCode as varchar(max)) PriceClass,CAST(Price1 as varchar(max)) Price1,CAST(Price2 as varchar(max)) Price2, 
                                                                CAST(Price3 as varchar(max)) Price3,CAST(Price4 as varchar(max)) Price4,
                                                                CAST(Price5 as varchar(max)) Price5,CAST(AltCnv as Varchar(max)) AltCnv ,CAST(ItemContrCode as varchar(max)) ProductContractCode, 
		                                                        CAST(ERPPriceMatrix as varchar(max)) ERPPriceMatrix FROM #UpdateProductList) pp 
                                                                UNPIVOT (Value FOR Name IN (IsModified,PriceClass,Price1,Price2,Price3,Price4,Price5,AltCnv,
		                                                        ProductContractCode,ERPPriceMatrix)) as ppvt) AS SOURCE
                                                            ON TARGET.ParentId = SOURCE.ProductId AND TARGET.Name= SOURCE.Name
                                                            WHEN NOT MATCHED THEN 
	                                                        INSERT(ParentId,Name,Value,ParentTable)
	                                                        VALUES(SOURCE.ProductId,SOURCE.Name,SOURCE.Value,'Product')
                                                        WHEN MATCHED THEN
	                                                        UPDATE SET  Value=SOURCE.Value,ParentTable='Product';

                                                    -- BUSA-257 : Question: Viewing Product Status Code in Insite Starts
                                                    
                                                            MERGE INTO CustomProperty AS TARGET USING
                                                            (SELECT ProductId, Name, Value FROM (SELECT distinct pf.IsDiscontinued AS ProductStatusCode, p.Id as ProductId FROM  #ProductFilter pf JOIN Product p ON                                 pf.Name = p.Name) pp UNPIVOT (Value FOR Name IN (ProductStatusCode)) as ppvt) AS SOURCE 
                                                            ON TARGET.ParentId = SOURCE.ProductId AND TARGET.Name= SOURCE.Name
                                                            WHEN NOT MATCHED THEN INSERT(ParentId,Name,Value,ParentTable)
                                                            VALUES(SOURCE.ProductId,SOURCE.Name,SOURCE.Value,'Product')
                                                            WHEN MATCHED THEN
                                                            UPDATE SET  Value=SOURCE.Value,ParentTable='Product';

                                                    -- BUSA-257 : Question: Viewing Product Status Code in Insite Ends

                                                        DROP TABLE #UpdateProductList;
                                                        DROP TABLE #ProductFilter;

                                                        -- BEGIN AttributeTypeRefresh --
                                                        Declare @AttributeTypeTemp Table (Name varchar(50),IsFilter bit,IsComparable bit,IsActive bit, Label varchar(50));
                                                        INSERT INTO @AttributeTypeTemp (Name,IsFilter,IsComparable,IsActive,Label)
                                                        VALUES ('Discontinued Products',1,1,1,'Discontinued Products');
                                                        MERGE INTO AttributeType AS TARGET USING 
                                                        (select Name,IsFilter,IsComparable,IsActive,Label from @AttributeTypeTemp) AS SOURCE
                                                        on TARGET.Name = SOURCE.Name
                                                        WHEN NOT MATCHED THEN 
                                                            INSERT(Name,IsFilter,IsComparable,IsActive,Label)
                                                            VALUES(SOURCE.Name,SOURCE.IsFilter,SOURCE.IsComparable,SOURCE.IsActive,SOURCE.Label)
                                                        WHEN MATCHED THEN
                                                            UPDATE SET Name=SOURCE.Name,IsFilter=SOURCE.IsFilter,IsComparable=SOURCE.IsComparable,IsActive=SOURCE.IsActive,Label=SOURCE.Label;
                                                        -- END AttributeTypeRefresh --


                                                        -- BEGIN AttributeValueRefresh --
                                                        Declare @AttributeValueTemp Table (AttributeTypeId varchar(50),Value varchar(50),IsActive bit, SortOrder int);
                                                        INSERT INTO @AttributeValueTemp (AttributeTypeId,Value,IsActive,SortOrder)
                                                        VALUES ((select id from AttributeType where Name ='Discontinued Products'),'Show Discontinued Products',1,0);
                                                        MERGE INTO AttributeValue AS TARGET USING 
                                                        (select AttributeTypeId,Value,IsActive,SortOrder from @AttributeValueTemp) AS SOURCE
                                                        on TARGET.AttributeTypeId = SOURCE.AttributeTypeId and TARGET.Value = SOURCE.Value
                                                        WHEN NOT MATCHED THEN 
                                                            INSERT(AttributeTypeId,Value,IsActive,SortOrder)
                                                            VALUES(SOURCE.AttributeTypeId,SOURCE.Value,SOURCE.IsActive,SOURCE.SortOrder)
                                                        WHEN MATCHED THEN
                                                            UPDATE SET AttributeTypeId=SOURCE.AttributeTypeId,Value=SOURCE.Value,IsActive=SOURCE.IsActive,SortOrder=SOURCE.SortOrder;
                                                        -- END AttributeValueRefresh --

                                                        -- BEGIN CategoryAttributeTypeRefresh --
                                                        MERGE INTO CategoryAttributeType AS TARGET USING 
                                                        (select Id from Category) AS SOURCE
	                                                        on TARGET.CategoryId = SOURCE.Id and AttributeTypeId = (select id from AttributeType where Name='Discontinued Products')
                                                        WHEN NOT MATCHED THEN 
                                                            INSERT(CategoryId,AttributeTypeId,SortOrder,IsActive,DetailDisplaySequence)
                                                            VALUES(SOURCE.ID,(select id from AttributeType where Name='Discontinued Products'),0,1,0)
                                                        WHEN MATCHED THEN
                                                            UPDATE SET CategoryId=SOURCE.ID,AttributeTypeId=(select id from AttributeType where Name='Discontinued Products'),IsActive=1,DetailDisplaySequence=0; 
                                                        -- END CategoryAttributeTypeRefresh --


                                                        -- BEGIN ProductAttributeValueRefresh --
                                                        MERGE INTO ProductAttributeValue AS TARGET USING 
	                                                        (select Id,IsDiscontinued from Product) AS SOURCE
                                                        on TARGET.ProductId = SOURCE.Id and (TARGET.AttributeValueId=(select Id from AttributeValue where Value='Show Discontinued Products') or
									                                                            TARGET.AttributeValueId=(select Id from AttributeValue where Value='Show Available Products'))
                                                        WHEN NOT MATCHED THEN 
	                                                        INSERT(ProductId,AttributeValueId)
	                                                        VALUES(SOURCE.Id, (case  when SOURCE.IsDiscontinued = 0
							                                                            then (select Id from AttributeValue where Value='Show Available Products')
							                                                            when SOURCE.IsDiscontinued = 1
							                                                            then (select Id from AttributeValue where Value='Show Discontinued Products')
							                                                            else (select Id from AttributeValue where Value='Show Available Products') end))

                                                        -- Changes For BUSA - 255 Starts
                                                        WHEN MATCHED THEN 
                                                        UPDATE SET AttributeValueId =  case  when SOURCE.IsDiscontinued = 0
                                                        then (select Id from AttributeValue where Value='Show Available Products')
                                                        when SOURCE.IsDiscontinued = 1
                                                        then (select Id from AttributeValue where Value='Show Discontinued Products')
                                                        else (select Id from AttributeValue where Value='Show Available Products') end
                                                        -- Changes For BUSA - 255 Ends
                                                        
                                                        WHEN NOT MATCHED BY SOURCE and (TARGET.AttributeValueId=(select Id from AttributeValue where Value='Show Discontinued Products') or
								                                                        TARGET.AttributeValueId=(select Id from AttributeValue where Value='Show Available Products')) then
                                                        Delete;
                                                        -- END ProductAttributeValueRefresh --


                                                        --BEGIN RMAthreshold value assignment --
                                                        
                                                        MERGE INTO CustomProperty AS TARGET USING
                                                            (select  Id,ProductCode from Product)
                                                                AS SOURCE
                                                            ON TARGET.ParentId = SOURCE.Id   AND TARGET.Name='RMAthreshold'
                                                            WHEN NOT MATCHED THEN 
                                                            INSERT(ParentId,Name,Value,ParentTable)
                                                            VALUES(SOURCE.Id,'RMAthreshold',(CASE WHEN EXISTS (SELECT TOP 1 Id FROM Product p WHERE (SELECT Value
                                                            FROM SystemSetting
                                                            WHERE Name = 'Custom_PowerSubClass') LIKE '%'+SOURCE.ProductCode+'%') THEN 30 ELSE 90 END),
                                                            'Product')
                                                            WHEN MATCHED THEN
                                                            UPDATE SET  Value=(CASE WHEN EXISTS (SELECT TOP 1 Id FROM Product p WHERE (SELECT Value
                                                            FROM SystemSetting
                                                            WHERE Name = 'Custom_PowerSubClass') LIKE '%'+SOURCE.ProductCode+'%') THEN 30 ELSE 90 END),
                                                            ParentTable='Product';
                                                       --END  RMAthreshold value assignment--


                        ";

                        using (var command = new SqlCommand(productMerge, sqlConnection))
                        {
                            command.CommandTimeout = CommandTimeOut;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                else
                {
                    LogHelper.For((object)this).Info(string.Format("Brasseler:DataSet is Empty"));
                }
            }
            catch (Exception ex)
            {
                LogHelper.For((object)this).Info(ex.ToString());
                throw;
            }
        }
Пример #37
0
        private static void InsertRecords( string connectionString, string tableName, List<CsvFieldInfo> fields, List<dynamic> records )
        {
            var insertPrefix = new StringBuilder();
            foreach ( var field in fields )
            {
                if ( insertPrefix.Length == 0 )
                {
                    insertPrefix.AppendFormat( "INSERT INTO [{0}] ( [{1}]", tableName, field.Name );
                }
                else
                {
                    insertPrefix.AppendFormat( ", [{0}]", field.Name );
                }
            }

            insertPrefix.AppendFormat( " ){0}    VALUES ", Environment.NewLine );

            using ( SqlConnection connection = new SqlConnection( connectionString ) )
            {
                connection.Open();

                int iRec = 0;
                string msg = string.Empty;
                int msgLen = 0;

                var insertRows = new StringBuilder();
                
                foreach ( var record in records )
                {
                    if ( insertRows.Length > 0 )
                    {
                        insertRows.Append( "," + Environment.NewLine );
                    }

                    var insertFields = new StringBuilder();
                    foreach ( var p in record )
                    {
                        var fieldInfo = fields.FirstOrDefault( f => f.Name == p.Key );
                        if ( fieldInfo != null )
                        {
                            string value = p.Value.ToString().Trim();

                            if ( insertFields.Length > 0 )
                            {
                                insertFields.Append( ", " );
                            }

                            if ( fieldInfo.IsBool )
                            {
                                if ( value == null || value == "" )
                                {
                                    value = "NULL";
                                }
                                else
                                {
                                    if ( bool.TryParse( value, out bool selected ) )
                                    {
                                        value = selected ? "1" : "0";
                                    }
                                    else
                                    {
                                        value = "NULL";
                                    }
                                }
                            }
                            else if ( fieldInfo.IsDateTime )
                            {
                                if ( value == null || value == "" )
                                {
                                    value = "NULL";
                                }
                                else
                                {
                                    if ( DateTime.TryParse( value, out DateTime selected ) )
                                    {
                                        value = "'" + selected.ToString() + "'";
                                    }
                                    else
                                    {
                                        value = "NULL";
                                    }
                                }
                            }
                            else if ( fieldInfo.IsInteger )
                            {
                                if ( value == null || value == "" )
                                {
                                    value = "NULL";
                                }
                                else
                                {
                                    if ( !int.TryParse( value, out int selected ) )
                                    {
                                        value = "NULL";
                                    }
                                }
                            }
                            else if ( fieldInfo.IsDecimal )
                            {
                                if ( value == null || value == "" )
                                {
                                    value = "NULL";
                                }
                                else
                                {
                                    if ( decimal.TryParse( value, out decimal selected ) )
                                    {
                                        value = selected.ToString();
                                    }
                                    else
                                    {
                                        value = "NULL";
                                    }
                                }
                            }
                            else
                            {
                                if ( value == null )
                                {
                                    value = "";
                                }
                                value = "'" + value.Replace( "'", "''" ) + "'";
                            }

                            insertFields.Append( value );
                        }
                    }

                    insertRows.AppendFormat( "( {0} )", insertFields.ToString() );

                    iRec++;

                    if ( iRec % 100 == 0 )
                    {
                        if ( msgLen > 0 )
                        {
                            var bs = new String( '\b', msgLen );
                            Console.Write( bs );
                        }

                        msg = $"{iRec:N0} of {records.Count:N0}";
                        msgLen = msg.Length;
                        Console.Write( msg );

                        using ( SqlCommand querySaveStaff = new SqlCommand( insertPrefix.ToString() + insertRows.ToString(), connection ) )
                        {
                            querySaveStaff.ExecuteNonQuery();
                        }
                        insertRows = new StringBuilder();
                    }
                }

                if ( msgLen > 0 )
                {
                    var bs = new String( '\b', msgLen );
                    Console.Write( bs );
                }

                msg = $"{iRec:N0} of {records.Count:N0}";
                msgLen = msg.Length;
                Console.Write( msg );

                if ( insertRows.Length > 0 )
                {
                    using ( SqlCommand querySaveStaff = new SqlCommand( insertPrefix.ToString() + insertRows.ToString(), connection ) )
                    {
                        querySaveStaff.ExecuteNonQuery();
                    }
                }

                connection.Close();
            }
        }
		public List<DB_LOG> GetItems(string CURRENT_USER, Int32 CURRENT_REGISTRY_ID)
		{
			List<DB_LOG> objReturn = null;

			SqlConnection sConn = null;
			SqlCommand sCmd = null;
			SqlDataAdapter sAdapter = null;
			DataSet objTemp = null;

			try
			{
				sConn = new SqlConnection(SqlConnectionString);

				sConn.Open();

				sCmd = new SqlCommand("CRS.usp_DB_LOG_getitems", sConn);
				sCmd.CommandTimeout = SqlCommandTimeout;
				sCmd.CommandType = CommandType.StoredProcedure;
				sCmd.Parameters.AddWithValue("@CURRENT_USER", CURRENT_USER);
				sCmd.Parameters.AddWithValue("@CURRENT_REGISTRY_ID", CURRENT_REGISTRY_ID);

				objTemp = new DataSet();
				sAdapter = new SqlDataAdapter(sCmd);

				LogDetails logDetails = new LogDetails(String.Format("{0}.{1}", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name), CURRENT_USER, CURRENT_REGISTRY_ID);
				sAdapter.Fill(objTemp);
				LogManager.LogTiming(logDetails);
				CheckDataSet(objTemp);

				if (objTemp != null  && objTemp.Tables.Count > 0 && objTemp.Tables[0].Rows.Count > 0)
				{
					var myData = objTemp.Tables[0].AsEnumerable().Select(r => ParseReader(r));
					if (myData != null)
					{
						objReturn = myData.ToList<DB_LOG>();
					}
				}

				sConn.Close();
			}
			catch (Exception ex)
			{
				LogManager.LogError(ex.Message, String.Format("{0}.{1}", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name), CURRENT_USER, CURRENT_REGISTRY_ID);
				throw ex;
			}
			finally
			{
				if (sAdapter != null)
				{
					sAdapter.Dispose();
					sAdapter = null;
				}
				if (sCmd != null)
				{
					sCmd.Dispose();
					sCmd = null;
				}
				if (sConn != null)
				{
					if (sConn.State != ConnectionState.Closed) { sConn.Close(); }
					sConn.Dispose();
					sConn = null;
				}
			}

			return objReturn;
		}
		public Int32 Save(string CURRENT_USER, Int32 CURRENT_REGISTRY_ID, DB_LOG objSave)
		{
			Int32 objReturn = 0;

			SqlConnection sConn = null;
			SqlCommand sCmd = null;
			SqlParameter p = null;

			try
			{
				sConn = new SqlConnection(SqlConnectionString);

				sConn.Open();

				sCmd = new SqlCommand("CRS.usp_DB_LOG_save", sConn);
				sCmd.CommandTimeout = SqlCommandTimeout;
				sCmd.CommandType = CommandType.StoredProcedure;
				sCmd.Parameters.AddWithValue("@CURRENT_USER", CURRENT_USER);
				sCmd.Parameters.AddWithValue("@CURRENT_REGISTRY_ID", CURRENT_REGISTRY_ID);

				p = new SqlParameter("@COMMENTS", SqlDbType.VarChar, -1);
				p.Precision = 0;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.COMMENTS);
				p = new SqlParameter("@CREATED", SqlDbType.DateTime, 8);
				p.Precision = 23;
				p.Scale = 3;
				AddParameter(ref sCmd, ref p, objSave.CREATED);
				p = new SqlParameter("@CREATEDBY", SqlDbType.VarChar, 30);
				p.Precision = 0;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.CREATEDBY);
				p = new SqlParameter("@CRS_DB_LOG_ID", SqlDbType.Int, 4);
				p.Direction = ParameterDirection.InputOutput;
				p.Precision = 10;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.CRS_DB_LOG_ID);
				p = new SqlParameter("@IS_ERROR", SqlDbType.Bit, 1);
				p.Precision = 1;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.IS_ERROR);
				p = new SqlParameter("@MESSAGE", SqlDbType.VarChar, -1);
				p.Precision = 0;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.MESSAGE);
				p = new SqlParameter("@PROCESS_NAME", SqlDbType.VarChar, 200);
				p.Precision = 0;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.PROCESS_NAME);
				p = new SqlParameter("@STD_REGISTRY_ID", SqlDbType.Int, 4);
				p.Precision = 10;
				p.Scale = 0;
				AddParameter(ref sCmd, ref p, objSave.STD_REGISTRY_ID);

				//LogDetails logDetails = new LogDetails(String.Format("{0}.{1}", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name), CURRENT_USER, CURRENT_REGISTRY_ID);
				int cnt = sCmd.ExecuteNonQuery();
				//LogManager.LogTiming(logDetails);

				objReturn = (Int32)sCmd.Parameters["@CRS_DB_LOG_ID"].Value;

				sConn.Close();
			}
			catch (Exception ex)
			{
                //TODO: Log to event log and file system only
				//LogManager.LogError(ex.Message, String.Format("{0}.{1}", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name), CURRENT_USER, CURRENT_REGISTRY_ID);
				throw ex;
			}
			finally
			{
				if (sCmd != null)
				{
					sCmd.Dispose();
					sCmd = null;
				}
				if (sConn != null)
				{
					if (sConn.State != ConnectionState.Closed) { sConn.Close(); }
					sConn.Dispose();
					sConn = null;
				}
			}

			return objReturn;
		}
Пример #40
0
        static void Main(string[] args)
        {
            using SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();

            StringBuilder result = new StringBuilder();

            string countryName = Console.ReadLine();

            string getCountryIdQueryText = @"SELECT Id
                                             FROM Countries
                                             WHERE [Name] = @countryName";

            using SqlCommand getCountryIdCmd = new SqlCommand(getCountryIdQueryText, sqlConnection);
            getCountryIdCmd.Parameters.AddWithValue("@countryName", countryName);

            string countryId = getCountryIdCmd.ExecuteScalar()?.ToString();

            if (countryId == null)
            {
                Console.WriteLine("No town names were affected.");
                return;
            }

            string updateTownNamesToUpperCaseQueryText = @"UPDATE Towns
                                                           SET [Name] = UPPER([Name])
                                                           WHERE CountryCode = @countryId";

            using SqlCommand updateTownNameToUpperCaseCmd = new SqlCommand(updateTownNamesToUpperCaseQueryText, sqlConnection);
            updateTownNameToUpperCaseCmd.Parameters.AddWithValue("@countryId", countryId);

            int rowAffected = updateTownNameToUpperCaseCmd.ExecuteNonQuery();

            if (rowAffected < 1)
            {
                Console.WriteLine("No town names were affected.");
                return;
            }

            result.AppendLine($"{rowAffected} town names were affected.");

            string getTownsFromCountryQueryText = @"SELECT *
                                                    FROM Towns
                                                    WHERE CountryCode = @countryId";

            using SqlCommand getTownsFromCountryCmd = new SqlCommand(getTownsFromCountryQueryText, sqlConnection);
            getTownsFromCountryCmd.Parameters.AddWithValue("@countryId", countryId);

            using SqlDataReader reader = getTownsFromCountryCmd.ExecuteReader();

            if (!reader.HasRows)
            {
                Console.WriteLine("No town names were affected.");
                return;
            }
            result.Append("[");
            for (int i = 0; i < rowAffected; i++)
            {
                reader.Read();
                if (i + 1 == rowAffected)
                {
                    result.Append(reader["Name"]);
                }
                else
                {
                    result.Append($"{reader["Name"]}, ");
                }
            }
            result.Append("]");
            Console.WriteLine(result.ToString().TrimEnd());
        }
Пример #41
0
    public bool InOperator(DataSet dsOperator)
    {
        bool result = false;
        try
        {
            if (dsOperator != null)
            {
                if (dsOperator.Tables[0].Rows.Count > 0)
                {
                    OpenConnection();
                    trans = connection.BeginTransaction();
                    foreach (DataRow dr in dsOperator.Tables[0].Rows)
                    {
                        try
                        {
                            string Op_id = dr["Op_id"].ToString();
                            string PCID = dr["PCID"].ToString();
                            string SocietyID = dr["SocietyID"].ToString();
                            string DistrictId = dr["DistrictId"].ToString();

                            commandt = connection.CreateCommand();
                            commandt.Transaction = trans;
                            commandt.CommandType = CommandType.Text;
                            commandt.CommandText = "select count(*) from OperatorRegistration where Op_id='" + Op_id + "'  and PCID= '" + PCID + "' and SocietyID='" + SocietyID + "'";
                            Int64 res = Convert.ToInt64(commandt.ExecuteScalar());
                            commandt.Dispose();
                            if (res <= 0)
                            {
                                string Proc_AgID = dr["Proc_AgID"].ToString();
                                string Name = dr["Name"].ToString();
                                string MobileNo = dr["MobileNo"].ToString();
                                string Email = dr["Email"].ToString();
                                string Address = dr["Address"].ToString();
                                string Password1 = dr["Password1"].ToString();
                                string MasterPassword = dr["MasterPassword"].ToString();

                                cmd = connection.CreateCommand();
                                cmd.Transaction = trans;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = "in_operator";
                                cmd.Parameters.Clear();

                                cmd.Parameters.AddWithValue("@Op_id", Op_id);
                                cmd.Parameters.AddWithValue("@DistrictId", DistrictId);
                                cmd.Parameters.AddWithValue("@Proc_AgID", Proc_AgID);
                                cmd.Parameters.AddWithValue("@PCID", PCID);
                                cmd.Parameters.AddWithValue("@SocietyID", SocietyID);
                                cmd.Parameters.AddWithValue("@Name", Name);
                                cmd.Parameters.AddWithValue("@MobileNo", MobileNo);
                                cmd.Parameters.AddWithValue("@Email", Email);
                                cmd.Parameters.AddWithValue("@Address", Address);
                                cmd.Parameters.AddWithValue("@Password1", Password1);
                                cmd.Parameters.AddWithValue("@MasterPassword", MasterPassword);
                                int req = cmd.ExecuteNonQuery();
                                cmd.Dispose();
                            }
                            else
                            {
                                string Proc_AgID = dr["Proc_AgID"].ToString();
                                string Name = dr["Name"].ToString();
                                string MobileNo = dr["MobileNo"].ToString();
                                string Email = dr["Email"].ToString();
                                string Address = dr["Address"].ToString();
                                string Password1 = dr["Password1"].ToString();
                                string MasterPassword = dr["MasterPassword"].ToString();

                                commandt = connection.CreateCommand();
                                commandt.Transaction = trans;
                                commandt.CommandType = CommandType.Text;
                                commandt.CommandText = "select count(*) from OperatorRegistration where Op_id='" + Op_id + "'  and PCID= '" + PCID + "' and SocietyID='" + SocietyID + "' and DistrictId = '" + DistrictId + "' and Name =N'" + Name + "' and MobileNo ='" + MobileNo + "'and Email ='" + Email + "'and Address =N'" + Address + "'";
                                Int64 res_update = Convert.ToInt64(commandt.ExecuteScalar());
                                commandt.Dispose();
                                if (res_update <= 0)
                                {
                                    cmd = connection.CreateCommand();
                                    cmd.Transaction = trans;
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.CommandText = "In_Operator_Update_Log";
                                    cmd.Parameters.Clear();

                                    cmd.Parameters.AddWithValue("@Op_id", Op_id);
                                    cmd.Parameters.AddWithValue("@DistrictId", DistrictId);

                                    cmd.Parameters.AddWithValue("@PCID", PCID);
                                    cmd.Parameters.AddWithValue("@SocietyID", SocietyID);
                                    int req_log = cmd.ExecuteNonQuery();
                                    cmd.Dispose();
                                    if (req_log > 0)
                                    {
                                        cmd = connection.CreateCommand();
                                        cmd.Transaction = trans;
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.CommandText = "in_Operator_Update_Info";
                                        cmd.Parameters.Clear();

                                        cmd.Parameters.AddWithValue("@Op_id", Op_id);
                                        cmd.Parameters.AddWithValue("@DistrictId", DistrictId);

                                        cmd.Parameters.AddWithValue("@PCID", PCID);
                                        cmd.Parameters.AddWithValue("@SocietyID", SocietyID);
                                        cmd.Parameters.AddWithValue("@Name", Name);
                                        cmd.Parameters.AddWithValue("@MobileNo", MobileNo);
                                        cmd.Parameters.AddWithValue("@Email", Email);
                                        cmd.Parameters.AddWithValue("@Address", Address);

                                        int req = cmd.ExecuteNonQuery();
                                        cmd.Dispose();
                                    }
                                }
                            }
                        }
                        catch
                        {
                            ////////////////
                        }
                    }
                    trans.Commit();
                    result = true;
                }
            }
        }
        catch (Exception)
        {
            trans.Rollback();
            CloseConnection();
        }
        finally
        {
            CloseConnection();
        }
        return result;
    }
Пример #42
0
        static void Main()
        {
            string idVillain = Console.ReadLine();

            using var connection = new SqlConnection(@"Server=.\SQLEXPRESS;
                                                       Database=MinionsDB;
                                                       Integrated Security=true;");
            connection.Open();

            var transaction = connection.BeginTransaction();

            var command = new SqlCommand("SELECT Name " +
                                         "  FROM Villains " +
                                         " WHERE Id = @villainId", connection);

            command.Parameters.AddWithValue("@villainId", idVillain);

            command.Transaction = transaction;

            string nameVillain = command.ExecuteScalar()?.ToString();

            string result = string.Empty;

            if (nameVillain == null)
            {
                result = "No such villain was found.";
            }
            else
            {
                try
                {
                    command = new SqlCommand("DELETE FROM MinionsVillains " +
                                             "WHERE VillainId = @villainId", connection);
                    command.Parameters.AddWithValue("@villainId", idVillain);

                    command.Transaction = transaction;

                    int num = command.ExecuteNonQuery();

                    command = new SqlCommand("DELETE FROM Villains " +
                                             " WHERE Id = @villainId", connection);
                    command.Parameters.AddWithValue("@villainId", idVillain);

                    command.Transaction = transaction;

                    command.ExecuteNonQuery();

                    transaction.Commit();

                    result = $"{nameVillain} was deleted." + Environment.NewLine +
                             $"{num} minions were released.";
                }
                catch (Exception ex)
                {
                    try
                    {
                        result = ex.Message;

                        transaction.Rollback();
                    }
                    catch (Exception e)
                    {
                        result = e.Message;
                    }
                }
            }

            Console.WriteLine(result);
        }
Пример #43
0
    public bool InInitial(DataSet dsInitial)
    {
        bool result = false;
        try
        {
            OpenConnection();
            trans = connection.BeginTransaction();
            commandt = connection.CreateCommand();
            commandt.Transaction = trans;
            commandt.CommandType = CommandType.Text;
            commandt.CommandText = "select count(*) from Initial where Society_Id='" + dsInitial.Tables[0].Rows[0]["Society_Id"].ToString() + "'";
            Int64 res = Convert.ToInt64(commandt.ExecuteScalar());
            commandt.Dispose();
            if (res <= 0)
            {
                string District_ID = dsInitial.Tables[0].Rows[0]["District_ID"].ToString();
                string District_Name = dsInitial.Tables[0].Rows[0]["District_Name"].ToString();
                string Society_Id = dsInitial.Tables[0].Rows[0]["Society_Id"].ToString();
                string SocietyName = dsInitial.Tables[0].Rows[0]["SocietyName"].ToString();

                string PC_Id = dsInitial.Tables[0].Rows[0]["PC_Id"].ToString();
                string PC_NAME = dsInitial.Tables[0].Rows[0]["PC_NAME"].ToString();
                string AgencyId = dsInitial.Tables[0].Rows[0]["AgencyId"].ToString();
                string AgencyName = dsInitial.Tables[0].Rows[0]["AgencyName"].ToString();

                string MarketingSeasonId = dsInitial.Tables[0].Rows[0]["MarketingSeasonId"].ToString();
                string MarketingSeason = dsInitial.Tables[0].Rows[0]["MarketingSeason"].ToString();
                string CropYear = dsInitial.Tables[0].Rows[0]["CropYear"].ToString();
                string OpeningStockOfGunny = dsInitial.Tables[0].Rows[0]["OpeningStockOfGunny"].ToString();

                string Password1 = dsInitial.Tables[0].Rows[0]["Password1"].ToString();
                string BankName = dsInitial.Tables[0].Rows[0]["BankName"].ToString();
                string AccNO = dsInitial.Tables[0].Rows[0]["AccNO"].ToString();
                string BranchName = dsInitial.Tables[0].Rows[0]["BranchName"].ToString();

                string ManagerName = dsInitial.Tables[0].Rows[0]["ManagerName"].ToString();
                string VersionNo = dsInitial.Tables[0].Rows[0]["VersionNo"].ToString();
                string NoOfToulKanta = dsInitial.Tables[0].Rows[0]["NoOfToulKanta"].ToString();
                string DailySc_Capacity = dsInitial.Tables[0].Rows[0]["DailySc_Capacity"].ToString();

                string Societycreditlimit = dsInitial.Tables[0].Rows[0]["Societycreditlimit"].ToString();
                string OneFarmerLimit = dsInitial.Tables[0].Rows[0]["OneFarmerLimit"].ToString();
                string MgrMobileNo = dsInitial.Tables[0].Rows[0]["MgrMobileNo"].ToString();
                string SocBandaranCapacity = dsInitial.Tables[0].Rows[0]["SocBandaranCapacity"].ToString();

                string DateTimeOfInstall = getRDate_MDY(dsInitial.Tables[0].Rows[0]["DateTimeOfInstall"].ToString());
                string Remarks = dsInitial.Tables[0].Rows[0]["Remarks"].ToString();

                cmd = connection.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "in_Initial";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@District_ID", District_ID);
                cmd.Parameters.AddWithValue("@District_Name", District_Name);
                cmd.Parameters.AddWithValue("@Society_Id", Society_Id);
                cmd.Parameters.AddWithValue("@SocietyName", SocietyName);
                cmd.Parameters.AddWithValue("@PC_Id", Society_Id);
                cmd.Parameters.AddWithValue("@PC_NAME", PC_NAME);
                cmd.Parameters.AddWithValue("@AgencyId", AgencyId);
                cmd.Parameters.AddWithValue("@AgencyName", AgencyName);
                cmd.Parameters.AddWithValue("@MarketingSeasonId", MarketingSeasonId);
                cmd.Parameters.AddWithValue("@MarketingSeason", MarketingSeason);
                cmd.Parameters.AddWithValue("@CropYear", CropYear);
                if (OpeningStockOfGunny == "")
                {
                    OpeningStockOfGunny = "0";
                }
                cmd.Parameters.AddWithValue("@OpeningStockOfGunny", OpeningStockOfGunny);
                cmd.Parameters.AddWithValue("@Password1", Password1);
                cmd.Parameters.AddWithValue("@BankName", BankName);
                cmd.Parameters.AddWithValue("@AccNO", AccNO);
                cmd.Parameters.AddWithValue("@BranchName", BranchName);
                cmd.Parameters.AddWithValue("@ManagerName", ManagerName);
                cmd.Parameters.AddWithValue("@VersionNo", VersionNo);
                cmd.Parameters.AddWithValue("@NoOfToulKanta", NoOfToulKanta);
                cmd.Parameters.AddWithValue("@DailySc_Capacity", DailySc_Capacity);

                if (Societycreditlimit == "")
                {
                    Societycreditlimit = "0.0";
                }
                cmd.Parameters.AddWithValue("@Societycreditlimit", Societycreditlimit);
                cmd.Parameters.AddWithValue("@OneFarmerLimit", OneFarmerLimit);
                cmd.Parameters.AddWithValue("@SocBandaranCapacity", SocBandaranCapacity);
                cmd.Parameters.AddWithValue("@MgrMobileNo", MgrMobileNo);
                cmd.Parameters.AddWithValue("@DateTimeOfInstall", DateTimeOfInstall);
                int req = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            else
            {
                string District_ID = dsInitial.Tables[0].Rows[0]["District_ID"].ToString();
                string Society_Id = dsInitial.Tables[0].Rows[0]["Society_Id"].ToString();
                string PC_Id = dsInitial.Tables[0].Rows[0]["PC_Id"].ToString();
                string NoOfToulKanta = dsInitial.Tables[0].Rows[0]["NoOfToulKanta"].ToString();
                string DailySc_Capacity = dsInitial.Tables[0].Rows[0]["DailySc_Capacity"].ToString();
                string OneFarmerLimit = dsInitial.Tables[0].Rows[0]["OneFarmerLimit"].ToString();
                string MgrMobileNo = dsInitial.Tables[0].Rows[0]["MgrMobileNo"].ToString();
                string SocBandaranCapacity = dsInitial.Tables[0].Rows[0]["SocBandaranCapacity"].ToString();
                string BankName = dsInitial.Tables[0].Rows[0]["BankName"].ToString();
                string AccNO = dsInitial.Tables[0].Rows[0]["AccNO"].ToString();
                string BranchName = dsInitial.Tables[0].Rows[0]["BranchName"].ToString();
                string ManagerName = dsInitial.Tables[0].Rows[0]["ManagerName"].ToString();
                string UpdationDate = getRDate_MDY(dsInitial.Tables[0].Rows[0]["UpdationDate"].ToString());
                string VersionNo = dsInitial.Tables[0].Rows[0]["VersionNo"].ToString();
                string IP = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"].ToString();
                commandt = connection.CreateCommand();
                commandt.Transaction = trans;
                commandt.CommandType = CommandType.Text;
                commandt.CommandText = "select count(*) from Initial where Society_Id='" + Society_Id + "' AND District_ID = '" + District_ID + "' AND  PC_Id = '" + PC_Id + "' AND NoOfToulKanta = '" + NoOfToulKanta + "' AND DailySc_Capacity = '" + DailySc_Capacity + "' AND OneFarmerLimit = '" + OneFarmerLimit + "' AND MgrMobileNo = '" + MgrMobileNo + "' AND SocBandaranCapacity = '" + SocBandaranCapacity + "' AND BankName = N'" + BankName + "' AND AccNO = '" + AccNO + "' AND BranchName = N'" + BranchName + "' AND ManagerName = N'" + ManagerName + "' AND VersionNo = '" + VersionNo + "'";
                Int64 res2 = Convert.ToInt64(commandt.ExecuteScalar());
                commandt.Dispose();
                if (res2 <= 0)
                {
                    cmd = new SqlCommand();
                    cmd = connection.CreateCommand();
                    cmd.Transaction = trans;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "In_Initial_Update_Log";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@Society_Id", Society_Id);
                    cmd.Parameters.AddWithValue("@District_ID", District_ID);
                    cmd.Parameters.AddWithValue("@PC_Id", PC_Id);
                    cmd.Parameters.AddWithValue("@IP", IP);
                    int req_log = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    if (req_log > 0)
                    {
                        cmd = new SqlCommand();
                        cmd = connection.CreateCommand();
                        cmd.Transaction = trans;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Update_Initial_Info";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@District_ID", District_ID);
                        cmd.Parameters.AddWithValue("@Society_Id", Society_Id);
                        cmd.Parameters.AddWithValue("@PC_Id", Society_Id);
                        cmd.Parameters.AddWithValue("@BankName", BankName);
                        cmd.Parameters.AddWithValue("@AccNO", AccNO);
                        cmd.Parameters.AddWithValue("@BranchName", BranchName);
                        cmd.Parameters.AddWithValue("@ManagerName", ManagerName);
                        cmd.Parameters.AddWithValue("@VersionNo", VersionNo);
                        cmd.Parameters.AddWithValue("@NoOfToulKanta", NoOfToulKanta);
                        cmd.Parameters.AddWithValue("@DailySc_Capacity", DailySc_Capacity);
                        cmd.Parameters.AddWithValue("@OneFarmerLimit", OneFarmerLimit);
                        cmd.Parameters.AddWithValue("@SocBandaranCapacity", SocBandaranCapacity);
                        cmd.Parameters.AddWithValue("@MgrMobileNo", MgrMobileNo);
                        cmd.Parameters.AddWithValue("@UpdationDate", UpdationDate);
                        int req = cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                }
            }

            trans.Commit();
            result = true;
        }
        catch (Exception)
        {
            trans.Rollback();
            CloseConnection();
        }
        finally
        {
            CloseConnection();
        }
        return result;
    }
Пример #44
0
 public bool InFinalizeData(DataSet dsFinalizeData)
 {
     bool result = false;
     try
     {
         OpenConnection();
         trans = connection.BeginTransaction();
         foreach (DataRow dr in dsFinalizeData.Tables[0].Rows)
         {
             try
             {
                 string District_ID = dr["District_ID"].ToString();
                 string SocietyID = dr["SocietyID"].ToString();
                 commandt = connection.CreateCommand();
                 commandt.Transaction = trans;
                 commandt.CommandType = CommandType.Text;
                 commandt.CommandText = "select count(*) from FinalizeDataBase where District_ID='" + District_ID + "'  and SocietyID= '" + SocietyID + "' ";
                 Int64 res = Convert.ToInt64(commandt.ExecuteScalar());
                 commandt.Dispose();
                 string FinalizeDate = "";
                 if (res <= 0)
                 {
                     string FinalizeStatus = dr["FinalizeStatus"].ToString();
                     string CreatedDate = getRDate_MDY(dr["CreatedDate"].ToString());
                     cmd = connection.CreateCommand();
                     cmd.Transaction = trans;
                     cmd.CommandType = CommandType.StoredProcedure;
                     cmd.CommandText = "in_FinalizeDatabase";
                     cmd.Parameters.Clear();
                     cmd.Parameters.AddWithValue("@District_ID", District_ID);
                     cmd.Parameters.AddWithValue("@SocietyID", SocietyID);
                     cmd.Parameters.AddWithValue("@FinalizeStatus", FinalizeStatus);
                     cmd.Parameters.AddWithValue("@CreatedDate", CreatedDate);
                     int rev = cmd.ExecuteNonQuery();
                 }
                 else
                 {
                     if (dr["FinalizeDate"].ToString() != "")
                     {
                         if (dr["FinalizeDate"].ToString() != "")
                         {
                             FinalizeDate = getRDate_MDY(dr["FinalizeDate"].ToString());
                         }
                         else
                         {
                             FinalizeDate = "01/01/1900";
                         }
                         string FinalizeStatus = dr["FinalizeStatus"].ToString();
                         cmd = connection.CreateCommand();
                         cmd.Transaction = trans;
                         cmd.CommandType = CommandType.Text;
                         cmd.CommandText = "update FinalizeDataBase set FinalizeStatus='Y',FinalizeDate='" + FinalizeDate + "',IsActive='N' where District_ID='" + District_ID + "' and SocietyID='" + SocietyID + "' ";
                         cmd.ExecuteNonQuery();
                     }
                 }
             }
             catch
             {
                 //////////////
             }
         }
         trans.Commit();
         result = true;
     }
     catch (Exception)
     {
         trans.Rollback();
         CloseConnection();
     }
     finally
     {
         CloseConnection();
     }
     return result;
 }
Пример #45
0
        public SpinData SP_SlotsKingPoker_Spin(InputSpin valueSpin)
        {
            try
            {
                var db = new DBHelper(ConnectionString.GameConnectionString);

                var oCommand = new SqlCommand(valueSpin.BetType == 1 ? "SP_Spins_CreateTransaction" : "SP_Spins_CreateTransaction_Coin") { CommandType = CommandType.StoredProcedure };
                oCommand.Parameters.AddWithValue("@_AccountID", valueSpin.AccountId);
                oCommand.Parameters.AddWithValue("@_UserName", valueSpin.UserName);
                oCommand.Parameters.AddWithValue("@_RoomID", valueSpin.RoomId);
                oCommand.Parameters.AddWithValue("@_LinesData", valueSpin.LinesData);
                oCommand.Parameters.AddWithValue("@_ClientIP", valueSpin.ClientIp);

                var outSpinId = new SqlParameter("@_SpinID", SqlDbType.BigInt) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outSpinId);

                var outSlotsData = new SqlParameter("@_SlotsData", SqlDbType.NVarChar, 50) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outSlotsData);

                var outPrizesData = new SqlParameter("@_PrizesData", SqlDbType.NVarChar, 500) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outPrizesData);

                var outTotalBetValue = new SqlParameter("@_TotalBetValue", SqlDbType.Int) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outTotalBetValue);

                var outTotalPrizeValue = new SqlParameter("@_TotalPrizeValue", SqlDbType.BigInt) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outTotalPrizeValue);


                var outIsJackpot = new SqlParameter("@_IsJackpot", SqlDbType.Bit) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outIsJackpot);

                var outJackpot = new SqlParameter("@_Jackpot", SqlDbType.BigInt) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outJackpot);

                var outBalance = new SqlParameter("@_Balance", SqlDbType.BigInt) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outBalance);

                var outResponseStatus = new SqlParameter("@_ResponseStatus", SqlDbType.Int) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outResponseStatus);

                var outLuckyData = new SqlParameter("@_LuckyData", SqlDbType.NVarChar) { Direction = ParameterDirection.Output, Size = 500 };
                oCommand.Parameters.Add(outLuckyData);

                var outTotalJP = new SqlParameter("@_TotalJackpot", SqlDbType.Int) { Direction = ParameterDirection.Output };
                oCommand.Parameters.Add(outTotalJP);


                db.ExecuteNonQuery(oCommand);

                var spinData = new SpinData();
                spinData.SpinId = (long)outSpinId.Value;
                spinData.SlotData = outSlotsData.Value.ToString();
                spinData.PrizesData = outPrizesData.Value.ToString();
                spinData.TotalBetValue = (int)outTotalBetValue.Value;
                spinData.TotalPrizeValue = (long)outTotalPrizeValue.Value;
                spinData.Jackpot = (long)outJackpot.Value;
                spinData.Balance = (long)outBalance.Value;
                spinData.IsJackpot = (bool) outIsJackpot.Value;
                spinData.ResponseStatus = (int)outResponseStatus.Value;
                spinData.LuckyData = (string)outLuckyData.Value;
                spinData.TotalJackPot = (int)outTotalJP.Value;
                return spinData;
            }
            catch (Exception ex)
            {
                NLogManager.PublishException(ex);
                return new SpinData()
                {
                    ResponseStatus = -10000
                };
            }
        }
Пример #46
0
    public bool InInstallationInfo(DataSet dsInstallationInfo)
    {
        bool result = false;
        try
        {
            if (dsInstallationInfo != null)
            {
                if (dsInstallationInfo.Tables[0].Rows.Count > 0)
                {
                    OpenConnection();
                    trans = connection.BeginTransaction();
                    foreach (DataRow dr in dsInstallationInfo.Tables[0].Rows)
                    {
                        try
                        {
                            string SocietyID = dr["SocietyID"].ToString();
                            string District_ID = dr["District_ID"].ToString();

                            commandt = connection.CreateCommand();
                            commandt.Transaction = trans;
                            commandt.CommandType = CommandType.Text;
                            commandt.Parameters.Clear();
                            commandt.CommandText = "select count(*) from InstallationInfo where SocietyID='" + SocietyID + "' and District_ID='" + District_ID + "'";
                            Int64 res = Convert.ToInt64(commandt.ExecuteScalar());
                            commandt.Dispose();
                            if (res <= 0)
                            {
                                string UserID = dr["UserID"].ToString();
                                string InsDate = getRDate_MDY(dr["InsDate"].ToString());
                                cmd = connection.CreateCommand();
                                cmd.Transaction = trans;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = "in_InstallationInfo";
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@District_ID", District_ID);
                                cmd.Parameters.AddWithValue("@SocietyID", SocietyID);
                                cmd.Parameters.AddWithValue("@UserID", UserID);
                                cmd.Parameters.AddWithValue("@InsDate", InsDate);
                                int req = cmd.ExecuteNonQuery();
                                cmd.Dispose();
                                if (req > 0)
                                {
                                    result = true;
                                }
                            }
                        }
                        catch (Exception)
                        {
                            ///////////////////
                        }
                    }
                    trans.Commit();
                }
            }
        }
        catch (Exception)
        {
            trans.Rollback();
            CloseConnection();
        }
        finally
        {
            CloseConnection();
        }
        return result;
    }
Пример #47
0
        private void button1_Click(object sender, EventArgs e)
        {
            String[] Students = new string[4];

            string line1;
            System.IO.StreamReader file1 = new System.IO.StreamReader("Answers 1.txt");
            line1 = file1.ReadLine();

            string line2;
            System.IO.StreamReader file2 = new System.IO.StreamReader("Answers 2.txt");
            line2 = file2.ReadLine();

            string line3;
            System.IO.StreamReader file3 = new System.IO.StreamReader("Answers 3.txt");
            line3 = file3.ReadLine();

            string line4;
            System.IO.StreamReader file4 = new System.IO.StreamReader("Answers 4.txt");
            line4 = file4.ReadLine();

            Students[0] = line1;
            Students[1] = line2;
            Students[2] = line3;
            Students[3] = line4;

            int k = 0;
            int x = 4;

            while (k < x)
            {
                if (line1 == lstPossibleAnswer1.Text)
                {
                    k++;
                }
                if (line2 == lstPossibleAnswer2.Text)
                {
                    k++;
                }
                if (line3 == lstPossibleAnswer3.Text)
                {
                    k++;
                }
                if (line4 == lstPossibleAnswer4.Text)
                {
                    k++;
                }

                string connectionString = null;
                connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C: \Users\Tido\Desktop\17749831 NAMENG THATO PROG 2A TASK-2\StudentQuizzes
                \StudentQuizzes\Student Information.mdf;Integrated Security=True;Connect Timeout=30";
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                SqlCommand cnnd = new SqlCommand(@"INSERT INTO StudentMarks (Id, Marks) VALUES ('" + "*" + "','" + k + "')'", conn);
            }

            string conString = null;
            conString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C: \Users\Tido\Desktop\17749831 NAMENG THATO PROG 2A TASK-2\StudentQuizzes
                \StudentQuizzes\Student Information.mdf;Integrated Security=True;Connect Timeout=30";
            SqlConnection cnd = new SqlConnection(conString);
            cnd.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM LectureAns WHERE *", cnd);
            cmd.ExecuteNonQuery();
            cnd.Close();

            SqlConnection con = new SqlConnection(conString);
            con.Open();
            SqlCommand cs = new SqlCommand(@"INSERT INTO StudAns (Question, StudAns) VALUES ('" + "*" + "','" + Students + "')'", con);
            con.Close();

            this.Hide();
            frmQuizResult rslt = new frmQuizResult();
            rslt.Show();
        }
        // GET: Cohorts/Delete/5
        public ActionResult Delete(int id)
        {
            using (SqlConnection conn = Connection)
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT s.Id AS StudentId, s.FirstName AS StudentFirstName, 
                                        s.LastName AS StudentLastName, s.SlackHandle AS StudentSlackHandle,
                                        i.Id AS InstructorId, i.FirstName AS InstructorFirstName, 
                                        i.LastName AS InstructorLastName, i.SlackHandle AS InstructorSlackHandle,
                                        c.Id AS CohortId, c.Name AS CohortName
                                        FROM Students s
                                        FULL OUTER JOIN Instructors i ON s.CohortId = i.CohortId
                                        LEFT JOIN Cohorts c ON c.Id = s.CohortId OR c.Id = i.CohortId
                                        WHERE s.CohortId = @id OR i.CohortId = @id
                                        ";
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    SqlDataReader reader = cmd.ExecuteReader();

                    Cohort cohort = null;

                    if (reader.Read())
                    {
                        cohort = new Cohort()
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("CohortId")),
                            Name = reader.GetString(reader.GetOrdinal("CohortName"))
                        };
                        if (!reader.IsDBNull(reader.GetOrdinal("StudentId")))
                        {
                            Student student = new Student()
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("StudentId")),
                                FirstName = reader.GetString(reader.GetOrdinal("StudentFirstName")),
                                LastName = reader.GetString(reader.GetOrdinal("StudentLastName")),
                                SlackHandle = reader.GetString(reader.GetOrdinal("StudentSlackHandle")),
                                CohortId = reader.GetInt32(reader.GetOrdinal("CohortId"))
                            };
                            cohort.StudentList.Add(student);
                        }
                        if (!reader.IsDBNull(reader.GetOrdinal("InstructorId")))
                        {
                            Instructor instructor = new Instructor()
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("InstructorId")),
                                FirstName = reader.GetString(reader.GetOrdinal("InstructorFirstName")),
                                LastName = reader.GetString(reader.GetOrdinal("InstructorLastName")),
                                SlackHandle = reader.GetString(reader.GetOrdinal("InstructorSlackHandle")),
                                CohortId = reader.GetInt32(reader.GetOrdinal("CohortId"))
                            };
                            cohort.InstructorList.Add(instructor);
                        }
                        reader.Close();
                    }
                    else
                    {
                        cohort = GetCohortById(id);
                    }
                    return View(cohort);
                }
            }
        }
    public static List <Common> getEmailAddr()
    {
        string        sTCNumber        = HttpContext.Current.Session["sTCNumber"].ToString();
        string        firstemailAddr   = string.Empty;
        string        secondemailAddr  = string.Empty;
        string        connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection connection       = new SqlConnection(connectionstring);
        SqlCommand    command          = new SqlCommand();
        SqlDataReader reader           = null;
        string        Sql = "EXEC [ExamProctorProject].[dbo].[spGetEmail]" + sqlValStatic(sTCNumber, "'");

        LogMessageStatic("### SP GET EMAIL CONTROL ### => TC = " + Sql, sTCNumber, logFileName);

        #region spGetEmail
        try
        {
            connection.Open();
            command             = new SqlCommand("ExamProctorProject.dbo.spGetEmail", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@sLogin", sTCNumber);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                firstemailAddr  = reader["UserFirstEmail"].ToString();
                secondemailAddr = reader["UserSecondEmail"].ToString();
            }
        }

        catch (Exception ex)
        {
            //LogMessage("!!! Error !!! => [ExamProctorProject].[dbo].[spGetEmail] , Error Message = " + ex.Message.ToString(), sTCNumber, logFileName);
        }

        finally
        {
            command.Dispose();
            reader = null;
            connection.Close();
        }


        List <Common> emailAddr     = new List <Common>();
        Common        emailAddrress = new Common();
        emailAddrress.Text  = "Choose...";
        emailAddrress.Value = "0";
        emailAddr.Add(emailAddrress);


        if (!string.IsNullOrEmpty(firstemailAddr.Replace(" ", "")))
        {
            emailAddrress       = new Common();
            emailAddrress.Text  = firstemailAddr;
            emailAddrress.Value = "1";
            emailAddr.Add(emailAddrress);
        }
        if (!string.IsNullOrEmpty(secondemailAddr.Replace(" ", "")))
        {
            emailAddrress       = new Common();
            emailAddrress.Text  = secondemailAddr;
            emailAddrress.Value = "2";
            emailAddr.Add(emailAddrress);
        }
        return(emailAddr);


        #endregion
    }
        /// <summary>
        ///
        /// </summary>
        /// <param name="variableId"></param>
        /// <returns>[0]:数据库名  [1]:表格名  [2]:列名</returns>
        private List <TagInfo> GetTableNameAndFieldNameByVariableId(string variableId)
        {
            SqlServerDataFactory dataFactory = new SqlServerDataFactory(ConnectionStringFactory.NXJCConnectionString);
            VariableParams       vp          = new VariableParams(variableId);

            string[]      tagArray      = vp.VariableName.Split(',');
            string        dataBase      = "";
            string        commandFormat = @"
                    SELECT [DBName],[TableName], [FieldName],[DCSName]
                      FROM [{0}].[dbo].[View_DCSContrast]";
            StringBuilder sqlBuilder    = new StringBuilder(commandFormat);

            sqlBuilder.Append(" where (");
            List <SqlParameter> parameterList = new List <SqlParameter>();

            foreach (string item in tagArray)
            {
                if (item.Trim() != "")
                {
                    sqlBuilder.Append("TagName=@" + item.Trim() + " or ");
                    parameterList.Add(new SqlParameter(item.Trim(), item.Trim()));
                }
            }
            sqlBuilder.Remove(sqlBuilder.Length - 4, 4);
            sqlBuilder.Append(")");


            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                string     mySql   = @"select  a.OrganizationID,a.LevelType, a.LevelCode,a.Name,b.MeterDatabase,b.DCSProcessDatabase
                                        from system_Organization a,system_Database b
                                        where a.DatabaseID=b.DatabaseID
                                        and a.OrganizationID=@myOrganizationID";
                command.CommandText = mySql;
                command.Parameters.Add(new SqlParameter("myOrganizationID", vp.OrganizationId));
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read() == true)
                    {
                        dataBase = reader["MeterDatabase"].ToString().Trim();
                    }
                }
                //command.CommandText = string.Format(commandFormat, dataBase);

                ////command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId));
                //command.Parameters.Add(new SqlParameter("variableName", vp.VariableName));

                //using (SqlDataReader reader = command.ExecuteReader())
                //{
                //    //if (reader.Read() == false)
                //    //    continue;
                //    // [0]:数据库名  [1]:表格名  [2]:列名
                //    if (reader.Read() == true)
                //        return new string[] { reader["DBName"].ToString().Trim(), "History_" + reader["TableName"].ToString().Trim(), reader["FieldName"].ToString().Trim() };
                //}
            }
            Dictionary <string, TagInfo> dictionary = new Dictionary <string, TagInfo>();
            List <TagInfo> infoList = new List <TagInfo>();
            List <string>  t_list   = new List <string>();//存储字段名
            DataTable      table    = dataFactory.Query(string.Format(sqlBuilder.ToString(), dataBase), parameterList.ToArray());

            if (table.Rows.Count == 0)
            {
                throw new ArgumentException("所提供的VariableID:" + variableId + "在DCS数据库中无对应项,请检查对照表配置是否正确。");
            }
            else
            {
                foreach (DataRow dr in table.Rows)
                {
                    if (t_list.Contains(dr["FieldName"].ToString().Trim()))//如果已包含该字段则跳过
                    {
                        continue;
                    }
                    TagInfo infoObj = new TagInfo();
                    infoObj.DBName    = dr["DBName"].ToString().Trim();
                    infoObj.TableName = "History_" + dr["TableName"].ToString().Trim();
                    infoObj.FieldName = dr["FieldName"].ToString().Trim();
                    infoObj.DCSName   = dr["DCSName"].ToString().Trim();
                    if (!infoList.Contains(infoObj))
                    {
                        infoList.Add(infoObj);
                    }
                }
                return(infoList);
            }
        }
Пример #51
0
        public static Status AddWccMemberdetail(string appid, string subcode, string customercode, string customertype, string name, string telephone, string email, string region, string province, string city, string systemaccount, string systempassword, string isAD)
        {
            //bool result = false;
            Status wcc = new Status();
            //WccMemberCommon wccMembercommon = null;
            string sqlCommand = "Insert into wechat_new..wcc_member_common(pk,appid, subcode, customercode, customertype, name, telephone, email, region, province, city, systemaccount, systempassword, isAD) values(@pk,@appid,@subcode,@customercode,@customertype,@name,@telephone,@email,@region,@province,@city,@systemaccount,@systempassword,@isAD)";
            using (SqlConnection connection = new SqlConnection(connstring))
            {
                using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                {
                    Guid pk = Guid.NewGuid();
                    command.Parameters.AddWithValue("@pk", pk);
                    command.Parameters.AddWithValue("@appid", appid);
                    command.Parameters.AddWithValue("@subcode",subcode);
                    command.Parameters.AddWithValue("@customercode", customercode);
                    command.Parameters.AddWithValue("@customertype", customertype);
                    command.Parameters.AddWithValue("@name", name);
                    command.Parameters.AddWithValue("@telephone", telephone);
                    command.Parameters.AddWithValue("@email", email);
                    command.Parameters.AddWithValue("@region", region);
                    command.Parameters.AddWithValue("@province", province);
                    command.Parameters.AddWithValue("@city", city);
                    command.Parameters.AddWithValue("@systemaccount", systemaccount);
                    command.Parameters.AddWithValue("@systempassword", systempassword);
                    command.Parameters.AddWithValue("@isAD", isAD);
                    connection.Open();
                    //SqlDataReader reader = command.ExecuteReader();
                    int count = command.ExecuteNonQuery();
                    if(count >0)
                    {
                        wcc.status = StatusEnum.success.ToString();
                        wcc.msg = msgEnum.会员中间表插入成功.ToString();
                        Logger.Info("会员中间表插入成功..........................");
                    }
                    else
                    {
                        wcc.status = StatusEnum.fail.ToString();
                        wcc.msg = msgEnum.会员中间表插入失败.ToString();
                        Logger.Info("会员中间表插入失败..........................");
                    }
                    //wcc.status = StatusEnum.success.ToString();
                    //wcc.msg = msgEnum.会员中间表插入成功.ToString();
                    //Logger.Info("会员中间表插入成功..........................");
                    //result = true;
                    /**if (reader.Read())
                    {
                        wcc.status =StatusEnum.success.ToString();
                        wcc.msg = "插入会员中间表成功";

                    }
                    else
                    {
                        wcc.status = StatusEnum.fail.ToString();
                        wcc.msg = "插入会员中间表失败";
                    }**/
                    //reader.Close();
                }
            }

            /**string sqlCommand1 = "Insert into wechat_new..scrm_qrcode_common (appid,subcode) values(@appid,@subcode)";
            using (SqlConnection connection = new SqlConnection(connstring))
            {
                using (SqlCommand command1 = new SqlCommand(sqlCommand1, connection))
                {
                  
                    command1.Parameters.AddWithValue("@appid", appid);
                    command1.Parameters.AddWithValue("@subcode", subcode);
                    connection.Open();
                    SqlDataReader reader = command1.ExecuteReader();
                    //TemplateCommon templateCommon = new TemplateCommon(); 
                    wcc.status = StatusEnum.success.ToString();
                    wcc.msg = msgEnum.会员中间表和二维码中间表插入成功.ToString();
                    Logger.Info("二维码中间表插入成功.......................................");
                    /**while (reader.Read())
                    {
                        templateCommon.status = StatusEnum.success.ToString();
                        templateCommon.msg = "插入模板消息中间表成功";



                    }
                    reader.Close();
                }
            }
            if (wcc.status != StatusEnum.success.ToString())
            {
                wcc.msg = msgEnum.会员中间表和二维码中间表插入失败.ToString();
                wcc.status = StatusEnum.fail.ToString();
            }**/
            return wcc;
        }
Пример #52
0
        private void button1_Click_1(object sender, EventArgs e)
        {
            try
            {
                sql1.Open();
            }
            catch { MessageBox.Show("Не удается установить соединение! Проверьте Ваше подключение! :)", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Information); return; }//Проверка наличия соединения

            {
                SqlCommand thisCommand = sql1.sql1.CreateCommand();
                thisCommand.CommandText = @"select [ФИО] from Users where Login='******'";
                SqlDataReader thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label4.Text += thisReader["ФИО"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Добавление проекта] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label5.Text = thisReader["Добавление проекта"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Добавление отдела] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label6.Text = thisReader["Добавление отдела"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Добавление сотрудника] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label7.Text = thisReader["Добавление сотрудника"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Создание пользователя] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label8.Text = thisReader["Создание пользователя"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Отображение отчёта] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label9.Text = thisReader["Отображение отчёта"].ToString();
                }
                thisReader.Close();
                thisCommand.CommandText = @"select [Добавление клиента] from Users where Login='******'";
                thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    label10.Text = thisReader["Добавление клиента"].ToString();
                }
                thisReader.Close();
                sql1.Close();
            }// Формирование sql-запроса в label4
            SqlDataAdapter sql2 = new SqlDataAdapter("Select Count(*) From Users where Login='******' and Password='******'", sql1.sql1);
            DataTable dt = new DataTable();
            sql2.Fill(dt);
            if (dt.Rows[0][0].ToString() == "1")
            {
                Form loading = new Form2(label4.Text, textBox1.Text, label5.Text, label6.Text, label7.Text, label8.Text, label9.Text, label10.Text);
                loading.Show();
            }
            else
            {
                MessageBox.Show("Вы ошиблись! Возможно Логин или пароль неверны", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            textBox2.Clear();
            label4.Text = "";
        }
Пример #53
0
        protected void Insert(object sender, EventArgs e)
        {
            int    e_kcal;
            int    e_kJ;
            int    kolicina;
            string NazivJedinice;
            string nazivNamirnice;
            string TipNamirnice;

            try {
                e_kcal         = Convert.ToInt32(txtEnergija_kcal.Text);
                e_kJ           = Convert.ToInt32(txtEnergija_kJ.Text);
                kolicina       = Convert.ToInt32(txtKolicina.Text);
                NazivJedinice  = txtJedinica.Text;
                nazivNamirnice = txtNaziv.Text;
                TipNamirnice   = txtTip.Text;
            } catch (FormatException) {
                return;
            }
            finally {
                txtEnergija_kcal.Text = "";
                txtEnergija_kJ.Text   = "";
                txtKolicina.Text      = "";
                txtJedinica.Text      = "";
                txtNaziv.Text         = "";
                txtTip.Text           = "";
            }

            using (SqlConnection con = new SqlConnection(Helper.CONNECTION_STRING))
            {
                DataTable t          = new DataTable();
                int       IDJedinica = -1;
                using (SqlDataAdapter a = new SqlDataAdapter("exec Dohvati_Jedinicu '" + NazivJedinice + "'", con))
                {
                    a.Fill(t);
                    IDJedinica = Convert.ToInt32(t.Rows[0]["IDJedinica"]);
                }
                t = new DataTable();
                int IDTipNamirnice = -1;
                using (SqlDataAdapter a = new SqlDataAdapter("exec Dohvati_TipNamirnice " + TipNamirnice, con))
                {
                    a.Fill(t);
                    IDTipNamirnice = Convert.ToInt32(t.Rows[0]["IDTipNamirnice"]);
                }
                using (SqlCommand cmd = new SqlCommand("Dodaj_Namirnicu"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Naziv", nazivNamirnice);
                    cmd.Parameters.AddWithValue("@TipNamirniceID", IDTipNamirnice);
                    cmd.Parameters.AddWithValue("@Energija_kcal", e_kcal);
                    cmd.Parameters.AddWithValue("@Energija_kJ", e_kJ);
                    cmd.Parameters.AddWithValue("@JedinicaID", IDJedinica);
                    cmd.Parameters.AddWithValue("@Kolicina", kolicina);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteScalar();
                    con.Close();
                }
            }
            BindGrid();
        }
Пример #54
0
        public static MemoryStream ItemsToCSV(string type)
        {
            string connectionstring;
            string query = "";

            connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            if (type == "items")
            {
                query = @"SELECT       Brand.Name as Brand, Serie.Name AS Serie, Model.Name AS Model,  BadBee.FR, DateFrom.Date as DateFr, DateTo.Date as DateT, Wva.Description , Wva.WvaNo, BadBee.BadBeeNo, Height.Height,  Width.Width,   Thickness.Thickness, Systems.Abbreviation as System
                    FROM Item INNER JOIN
                      BadBee ON Item.BadBeeId = BadBee.BadBeeId INNER JOIN
                         Dimension ON BadBee.DimensionId = Dimension.DimensionId INNER JOIN
                         Height ON Dimension.HeightId = Height.HeightId INNER JOIN
                         Model ON Item.ModelId = Model.ModelId INNER JOIN
                         Serie ON Model.SerieId = Serie.SerieId INNER JOIN
                         Brand ON Serie.BrandId = Brand.BrandId INNER JOIN
                         Systems ON BadBee.SystemId = Systems.SystemId INNER JOIN
                         Thickness ON Dimension.ThicknessId = Thickness.ThicknessId INNER JOIN
                         Width ON Dimension.WidthId = Width.WidthId INNER JOIN
                         Wva ON BadBee.WvaId = Wva.WvaId INNER JOIN
                         Year ON Model.YearId = Year.YearId INNER JOIN
                         Date as DateFrom ON DateFrom.DateId = Year.DateFromId inner join

                         Date as DateTo on DateTo.DateId = Year.DateToId


                         order by Brand, Serie, Model, FR";
            }
            else if (type == "itemsWithIds")
            {
                query = @"SELECT      BadBee.BadBeeNo, BadBee.FR, Brand.Name as brand, Date.Date, Height.Height, Model.Name AS model, Serie.Name AS serie, Systems.Abbreviation AS brakeSystem, Thickness.Thickness, Width.Width, Wva.WvaNo, 
                         Wva.Description, Year.YearId, Date.DateId, Wva.WvaId, Width.WidthId, Thickness.ThicknessId, Serie.SerieId, Model.ModelId, Systems.SystemId AS SystemId, Dimension.DimensionId, Height.HeightId AS HeightId, 
                         BadBee.BadBeeId, Item.Id, Brand.BrandId
FROM            Item INNER JOIN
                         BadBee ON Item.BadBeeId = BadBee.BadBeeId INNER JOIN
                         Dimension ON BadBee.DimensionId = Dimension.DimensionId INNER JOIN
                         Height ON Dimension.HeightId = Height.HeightId INNER JOIN
                         Model ON Item.ModelId = Model.ModelId INNER JOIN
                         Serie ON Model.SerieId = Serie.SerieId INNER JOIN
                         Brand ON Serie.BrandId = Brand.BrandId INNER JOIN
                         Systems ON BadBee.SystemId = Systems.SystemId INNER JOIN
                         Thickness ON Dimension.ThicknessId = Thickness.ThicknessId INNER JOIN
                         Width ON Dimension.WidthId = Width.WidthId INNER JOIN
                         Wva ON BadBee.WvaId = Wva.WvaId INNER JOIN
                         Year ON Model.YearId = Year.YearId INNER JOIN
                         Date ON Year.DateToId = Date.DateId AND Year.DateFromId = Date.DateId
                        
                            order by Brand, Serie, Model, FR";
            }
            SqlConnection connection = new SqlConnection(connectionstring);

            connection.Open();

            SqlCommand command = new SqlCommand(query, connection);

            DataTable data = new DataTable();

            using (SqlDataAdapter a = new SqlDataAdapter(command))
            {
                a.Fill(data);
                using (XLWorkbook wb = new XLWorkbook())
                {
                    if (type == "items")
                    {
                        wb.Worksheets.Add(data, "Excel Export Products");
                    }

                    else if (type == "itemsWithIds")
                    {
                        wb.Worksheets.Add(data, "Excel Export Products to Import");
                    }

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.Buffer      = true;
                    HttpContext.Current.Response.Charset     = "";
                    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    if (type == "items")
                    {
                        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Excel Export Products.xlsx");
                    }

                    else if (type == "itemsWithIds")
                    {
                        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Excel Export Products to Import.xlsx");
                    }

                    using (MemoryStream excelMS = new MemoryStream())
                    {
                        wb.SaveAs(excelMS);
                        excelMS.WriteTo(HttpContext.Current.Response.OutputStream);
                        HttpContext.Current.Response.Flush();
                        HttpContext.Current.Response.End();

                        return(excelMS);
                    }
                }
            }
        }
Пример #55
0
        public List<TagCuenta> MetodoInicial()
        {
            CrucesIniciales = 0;
            CrucesRegistrados = 0;
            Buscar_Texto();
            var Bandera = Buscar_Bandera();
            string Query = string.Empty;

            if (Bandera == null)

                Query = "SELECT CONTENU_ISO, VOIE, ID_GARE, TAB_ID_CLASSE, TO_CHAR(DATE_TRANSACTION, 'dd/mm/yyyy hh24:mi:ss')DATE_TRANSACTION, PRIX_TOTAL, EVENT_NUMBER, TAG_TRX_NB, INDICE_SUITE FROM  TRANSACTION Where  ID_PAIEMENT = '15' AND TO_CHAR(DATE_TRANSACTION, 'YYYY/MM/DD HH24:MI:SS' ) >= '2019/04/21 00:00:00'  AND TO_CHAR(DATE_TRANSACTION, 'YYYY/MM/DD HH24:MI:SS' ) < '2019/04/26 00:00:00' AND SUBSTR(TO_CHAR(CONTENU_ISO),0,3) = '501' and TAB_ID_CLASSE >=1 order by DATE_TRANSACTION ASC";
            //Query = "SELECT CONTENU_ISO, VOIE, ID_GARE, TAB_ID_CLASSE, TO_CHAR(DATE_TRANSACTION, 'dd/mm/yyyy hh24:mi:ss')DATE_TRANSACTION, PRIX_TOTAL, EVENT_NUMBER FROM  TRANSACTION Where  ID_PAIEMENT = '15' AND TO_CHAR(DATE_TRANSACTION, 'YYYY/MM/DD HH24:MI:SS' ) > '2019/04/03 09:00:00'  AND ACD_CLASS >= 1  order by DATE_TRANSACTION ASC";

            else

                Query = @"SELECT CONTENU_ISO, VOIE, ID_GARE, TAB_ID_CLASSE, TO_CHAR(DATE_TRANSACTION, 'dd/mm/yyyy hh24:mi:ss')DATE_TRANSACTION, PRIX_TOTAL, EVENT_NUMBER, TAG_TRX_NB, INDICE_SUITE
                        FROM  TRANSACTION
                        Where  ID_PAIEMENT = '15'
                        AND TO_CHAR(DATE_TRANSACTION, 'YYYY/MM/DD HH24:MI:SS' ) > '" + Convert.ToString(Bandera[0].Bandera_Nueva.AddMinutes(-18).ToString("yyyy/MM/dd HH:mm:ss")) + "' AND SUBSTR(TO_CHAR(CONTENU_ISO),0,3) = '501' AND TAB_ID_CLASSE >= 1 order by DATE_TRANSACTION ASC";

            var Cruces = Buscar_Cruces(Query);


            //string SQL = "Data Source=.;Initial Catalog=GTDB; Integrated Security=False;User Id=SA;Password=CAPUFE";
            string SQL = "Data Source=.;Initial Catalog=GTDBPruebas; Integrated Security=False;User Id=Sa;Password=CAPUFE";


            SqlConnection ConexionSQL = new SqlConnection(SQL);
            List<Historico> CrucesListos = new List<Historico>();

            try
            {

                using (SqlCommand cmd = new SqlCommand("", ConexionSQL))
                {
                    ConexionSQL.Open();

                    foreach (var item in Cruces)
                    {
                        Query = @"SELECT COUNT(*) FROM dbo.Historico 
	                        WHERE Id IN (SELECT Id FROM dbo.Historico  WHERE CONVERT(DATE, Fecha, 102) = '" + item.Fecha.ToString("yyyy-MM-dd") + "') " +
                                "AND (Fecha = '" + item.Fecha.ToString("dd/MM/yyyy HH:mm:ss") + "' AND Evento = '" + item.Evento + "' AND Tag = '" + item.NumTag + "'  AND Carril = '" + item.Carril + "' AND Clase = '" + Buscar_Clase(item.Clase) + "')";

                        cmd.CommandText = Query;
                        var Valida = Convert.ToInt32(cmd.ExecuteScalar());

                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                        {
                            file.WriteLine(DateTime.Now.ToString() + " " + item.NumTag + " " + item.Evento + " " + Valida + "/n" + Query ); //se agrega información al documento
                            file.Dispose();
                            file.Close();
                        }

                        if (Valida == 0)
                        {
                            CrucesListos.Add(new Historico
                            {
                                NumTag = item.NumTag,
                                Delegacion = item.Delegacion,
                                Plaza = item.Plaza,
                                Tramo = item.Tramo,
                                Carril = item.Carril,
                                Clase = item.Clase,
                                Fecha = Convert.ToDateTime(item.Fecha),
                                Evento = item.Evento,
                                Saldo = item.Saldo,
                                Operadora = item.Operadora,
                                TAG_TRX_NB = item.TAG_TRX_NB
                            });
                        }

                    }
                }
            }
            catch (Exception Ex)
            {
                using (StreamWriter file = new StreamWriter(path + archivo, true))
                {
                    Consecutivotxt++;
                    file.WriteLine("Prblema Limpiando la lista : " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + Query); //se agrega información al documento
                    file.Dispose();
                    file.Close();
                }
                StopService();
                
            }
            finally
            {
                ConexionSQL.Close();
            }

            if (CrucesListos != null && CrucesListos.Count > 0)
            {
                if (Bandera == null)
                {
                    BanderaTxt = DateTime.Now;
                    CrucesIniciales = CrucesListos.Count();
                }
                else
                {
                    BanderaTxt = Bandera[0].Bandera_Nueva;
                    CrucesIniciales = CrucesListos.Count();
                }


                Actualizar(CrucesListos);
            }

            return null;
        }
Пример #56
0
        //Display Data in DataGridView
        private void displayBudgets()
        {
            dataGridView1.DataSource = null;
            cmd = new SqlCommand("BudgetCodeSelect", con);
            cmd.CommandType = CommandType.StoredProcedure;


            DataTable dt = new DataTable();
            getHeaders(dt);

            dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("Tahoma", 10F, FontStyle.Bold);
            dataGridView1.Font = new Font("Tahoma", 9F, FontStyle.Bold);

            // Build the data rows
            try
            {

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DataRow dr = dt.NewRow();
                        dr[0] = reader.GetValue(0).ToString();
                        dr[1] = reader.GetValue(1).ToString();
                        dr[2] = reader.GetValue(2).ToString();
                        dr[3] = reader.GetValue(3).ToString();
                        dr[4] = reader.GetValue(4).ToString();
                        dr[5] = reader.GetValue(5).ToString();

                        decimal value = Convert.ToDecimal(dr[5]);
                        dr[5] = value.ToString("C", new CultureInfo("en-US"));
                        dt.Rows.Add(dr);
                        dataGridView1.DataSource = dt;

                        //****************************************************************************
                        //set autosize mode
                        dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dataGridView1.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                        dataGridView1.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
                        dataGridView1.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                    
                        //datagrid has calculated it's widths so we can store them
                        for (int i = 0; i <= dataGridView1.Columns.Count - 1; i++)
                        {
                            //store autosized widths
                            int colw = dataGridView1.Columns[i].Width;
                            //remove autosizing
                            dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                            //set width to calculated by autosize
                            dataGridView1.Columns[i].Width = colw;
                        }
                        //****************************************************************************
                        
                        dataGridView1.BackgroundColor = System.Drawing.SystemColors.Control;
                        dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.RowCount - 1;

                    }
                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                       
                    }
                    txbCategory.Text = "";
                    txbSubCategory.Text = "";
                    txbBudgetCode.Text = "";
                    txbDesc.Text = "";
                    txbMonthBudget.Text = "";

                }
               

            }
            catch (Exception ex)
            {

                MessageBox.Show("Error, \n \n\n" + ex.Message);
                con.Close();
            }
    }
Пример #57
0
        public void Actualizar(List<Historico> Historicos)
        {


            //string SQL = "Data Source=.;Initial Catalog=GTDB; Integrated Security=False;User Id=SA;Password=CAPUFE";
            string SQL = "Data Source=.;Initial Catalog=GTDBPruebas; Integrated Security=False;User Id=Sa;Password=CAPUFE";

            SqlConnection ConexionSQL = new SqlConnection(SQL);
            string Query = string.Empty;
            string SaldoAnterior = string.Empty;
            string SaldoActualizado = string.Empty;
            string NumeroCuenta = string.Empty;
            string TipoCuenta = string.Empty;
            foreach (var item in Historicos)
            {

                var TagCuenta2 = Busca_TagCuenta(item.NumTag, item.Saldo);

                if (TagCuenta2.Count == 0)
                {
                    SinRegistro = true;
                    using (StreamWriter file = new StreamWriter(path + archivo, true))
                    {
                        file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + "Sin Coincidencia de cruce con SQL"); //se agrega información al documento
                        file.Dispose();
                        file.Close();
                    }
                    StopService();
                }
                else
                {
                    SinRegistro = false;
                }


                foreach (var item2 in TagCuenta2)
                {
                    switch (Convert.ToString(item2.TypeCuenta))
                    {
                        case "Colectiva":

                            SaldoAnterior = Convert.ToString(item2.SaldoCuenta);
                            var NuevoSaldoColectivos = item2.SaldoCuenta - item2.DescuentoCruce;
                            SaldoActualizado = Convert.ToString(NuevoSaldoColectivos);
                            //CambiosNewColumn
                            NumeroCuenta = item2.NumCuenta;
                            TipoCuenta = item2.TypeCuenta;

                            if (NuevoSaldoColectivos < 15.25)
                            {


                                using (SqlCommand cmd = new SqlCommand(Query, ConexionSQL))
                                {
                                    try
                                    {
                                        ConexionSQL.Open();
                                        cmd.CommandText = "Update CuentasTelepeajes Set SaldoCuenta = '" + Convert.ToString(Math.Round((NuevoSaldoColectivos * 100),2)) + "' Where NumCuenta = '" + item2.NumCuenta + "'";
                                        cmd.ExecuteNonQuery();

                                        cmd.CommandText = "Update Tags Set SaldoTag = '" + Convert.ToString(Math.Round((NuevoSaldoColectivos * 100),2)) + "' Where CuentaId = '" + item2.CuentaId + "'";
                                        cmd.ExecuteNonQuery();


                                        if (ValidarExcentos(item.NumTag))
                                        {

                                            cmd.CommandText = "Update CuentasTelepeajes Set StatusCuenta = '0' where NumCuenta = '" + item2.NumCuenta + "'";
                                            cmd.ExecuteNonQuery();


                                            cmd.CommandText = "Update Tags Set StatusTag = '0' where CuentaId = '" + item2.CuentaId + "'";
                                            cmd.ExecuteNonQuery();
                                        }

                                    }
                                    catch (Exception Ex)
                                    {
                                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                                        {
                                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Actualizacion de colectivo <"); //se agrega información al documento
                                            file.Dispose();
                                            file.Close();
                                        }
                                        StopService();

                                    }
                                    finally
                                    {
                                        ConexionSQL.Close();

                                    }
                                }
                            }
                            else
                            {

                                using (SqlCommand cmd = new SqlCommand(Query, ConexionSQL))
                                {
                                    try
                                    {
                                        ConexionSQL.Open();

                                        cmd.CommandText = "Update CuentasTelepeajes Set SaldoCuenta = '" + Convert.ToString(Math.Round((NuevoSaldoColectivos * 100), 2)) + "' Where NumCuenta = '" + item2.NumCuenta + "'";
                                        cmd.ExecuteNonQuery();

                                        cmd.CommandText = "Update Tags Set SaldoTag = '" + Convert.ToString(Math.Round((NuevoSaldoColectivos * 100), 2)) + "' Where CuentaId = '" + item2.CuentaId + "'";
                                        cmd.ExecuteNonQuery();



                                    }
                                    catch (Exception Ex)
                                    {
                                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                                        {
                                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Actualizacion de Colectivos"); //se agrega información al documento
                                            file.Dispose();
                                            file.Close();
                                        }
                                        StopService();
                                    }
                                    finally
                                    {
                                        ConexionSQL.Close();

                                    }
                                }
                            }

                            break;


                        case "Individual":

                            SaldoAnterior = Convert.ToString(item2.SaldoTag);
                            var NuevoSaldoIndividuales = item2.SaldoTag - item2.DescuentoCruce;
                            SaldoActualizado = Convert.ToString(NuevoSaldoIndividuales);
                            //CambiosNewColumn
                            NumeroCuenta = item2.NumCuenta;
                            TipoCuenta = item2.TypeCuenta;



                            if (NuevoSaldoIndividuales < 15.25)
                            {


                                using (SqlCommand cmd = new SqlCommand(Query, ConexionSQL))
                                {
                                    try
                                    {
                                        ConexionSQL.Open();

                                        cmd.CommandText = "Update Tags Set SaldoTag = '" + Convert.ToString(Math.Round((NuevoSaldoIndividuales * 100), 2)) + "' Where CuentaId = '" + item2.CuentaId + "'";
                                        cmd.ExecuteNonQuery();

                                        if (ValidarExcentos(item.NumTag))
                                        {
                                            cmd.CommandText = "Update Tags Set StatusTag = '0' where NumTag = '" + item2.NumTag + "'";
                                            cmd.ExecuteNonQuery();
                                        }

                                    }
                                    catch (Exception Ex)
                                    {
                                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                                        {
                                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Actualiazcion Individual <"); //se agrega información al documento
                                            file.Dispose();
                                            file.Close();
                                        }
                                        StopService();
                                    }
                                    finally
                                    {
                                        ConexionSQL.Close();

                                    }
                                }
                            }
                            else
                            {

                                using (SqlCommand cmd = new SqlCommand(Query, ConexionSQL))
                                {
                                    try
                                    {
                                        ConexionSQL.Open();

                                        cmd.CommandText = "Update Tags Set SaldoTag = '" + Convert.ToString(Math.Round((NuevoSaldoIndividuales * 100), 2)) + "' Where CuentaId = '" + item2.CuentaId + "'";
                                        cmd.ExecuteNonQuery();


                                    }
                                    catch (Exception Ex)
                                    {
                                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                                        {
                                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Actualizacion Individual"); //se agrega información al documento
                                            file.Dispose();
                                            file.Close();
                                        }
                                        StopService();

                                    }
                                    finally
                                    {
                                        ConexionSQL.Close();

                                    }
                                }
                            }

                            break;

                        default:
                            break;

                    }


                }
                if (SinRegistro == false)
                    ActualizarHistorico(Historicos, item.NumTag, item.Evento, SaldoAnterior, SaldoActualizado, NumeroCuenta, TipoCuenta);
            }

            using (StreamWriter file = new StreamWriter(path + archivo, true))
            {
                Consecutivotxt++;
                file.WriteLine("Se inicio el proceso ServicioWinTags: " + Consecutivotxt.ToString() + " a las " + BanderaTxt.ToString("dd/MM/yyy  hh:mm:ss.fff") + " Al iniciar Registro " + CrucesIniciales + " Cruces " + "Termino a las: " + DateTime.Now.ToString("dd/MM/yyy  hh:mm:ss.fff") + " Ingreso " + CrucesRegistrados + " Cruces "); //se agrega información al documento
                file.Dispose();
                file.Close();
            }




        }
Пример #58
0
        public void ActualizarHistorico(List<Historico> Lista, string Tag, string Evento, string SaldoAnterior, string SaldoActualizado, string NumeroCuenta, string TipoCuenta)
        {
            try
            {

                var List = Lista.Where(x => x.NumTag == Tag).Where(x => x.Evento == Evento).ToList();

                DataTable table =   CreaDt();                
                foreach (var item in List)
                {
                    DataRow row = table.NewRow();

                    row["Tag"] = item.NumTag.ToString();
                    row["Carril"] = item.Carril.ToString();
                    row["Delegacion"] = item.Delegacion.ToString();
                    row["Plaza"] = item.Plaza.ToString();
                    row["Cuerpo"] = item.Tramo.ToString();
                    DateTime date = DateTime.ParseExact(item.Fecha.ToString("dd/MM/yyyy HH:mm:ss"), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
                    row["Fecha"] = date;
                    row["Clase"] = Buscar_Clase(item.Clase);
                    row["Evento"] = item.Evento.ToString();
                    row["Saldo"] = item.Saldo.ToString();
                    if (item.Operadora.ToString().Substring(0, 4) == "IMDM")
                        row["Operador"] = "Otros";
                    else
                        row["Operador"] = "SIVA";
                    row["SaldoAnterior"] = SaldoAnterior.Replace(".",",");
                    row["SaldoActualizado"] = SaldoActualizado.Replace(".",",");
                    row["NumeroCuenta"] = NumeroCuenta;
                    row["TipoCuenta"] = TipoCuenta;
                    //row["TAG_TRX_NB"] = item.TAG_TRX_NB;
                    table.Rows.Add(row);

                }

                //string SQL = "Data Source=.;Initial Catalog=GTDB; Integrated Security=False;User Id=SA;Password=CAPUFE";
                string SQL = "Data Source=.;Initial Catalog=GTDBPruebas; Integrated Security=False;User Id=Sa;Password=CAPUFE";
                SqlConnection ConexionSQL = new SqlConnection(SQL);

                using (SqlCommand SqlCommand = new SqlCommand("", ConexionSQL))
                {

                    try
                    {
                        ConexionSQL.Open();

                        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(ConexionSQL))
                        {
                            sqlBulk.BulkCopyTimeout = 1000;
                            sqlBulk.DestinationTableName = "Historico";
                            sqlBulk.WriteToServer(table);
                            sqlBulk.Close();
                        }
                        
                        CrucesRegistrados++;

                    }
                    catch (Exception Ex)
                    {

                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                        {
                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Insertar en Historico"); //se agrega información al documento
                            file.Dispose();
                            file.Close();

                        }
                        StopService();

                    }
                    finally
                    {
                        ConexionSQL.Close();
                    }
                }
            }
            catch (Exception Ex)
            {
                using (StreamWriter file = new StreamWriter(path + archivo, true))
                {
                    Consecutivotxt++;
                    file.WriteLine("Error en el proceso ServicioWinTags: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + Ex.Message + Ex.StackTrace); //se agrega información al documento
                    file.Dispose();
                    file.Close();
                }
                StopService();

            }
        }
Пример #59
0
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                t1_podacha = Convert.ToDouble(textBox4.Text);
                t2_obratka = Convert.ToDouble(textBox5.Text);
                t_house    = Int32.Parse(textBox3.Text);
                s_sec      = Convert.ToDouble(textBox7.Text);
                count_sec  = Convert.ToDouble(textBox6.Text);
                l_pipe     = Convert.ToDouble(textBox9.Text);
                t_air      = Int32.Parse(textBox1.Text);
                t_zima     = Double.Parse(textBox2.Text);
            }
            catch { }
            t_napor      = t1_podacha / 2 + t2_obratka / 2 - t_house;
            label12.Text = t_napor.ToString();

            f_rad      = s_sec * count_sec;
            q_rad      = Math.Round(f_rad * 435);
            q_tr       = Math.Round(q_heat_loss * t_napor * l_pipe);
            q_max      = q_rad + q_tr;
            q_maxGcal  = q_max / 1000000;
            q_avg      = Math.Round(q_max / ((t_house - t_air) / (t_house - t_zima)));
            q_avg_Gcal = q_avg / 1000000;
            q_day      = q_avg * 24;
            q_day_Gcal = q_day / 1000000;
            users      = Int32.Parse(textBox8.Text);
            hrs        = Int32.Parse(textBox9.Text);
            gvs_count  = Int32.Parse(textBox11.Text);
            usage_hrs  = Int32.Parse(textBox10.Text);
            gvs_q_day  = Decimal.Parse(textBox12.Text);
            gvs_q_hr   = Decimal.Parse(textBox13.Text);
            Q_q_day    = Decimal.Parse(textBox14.Text);
            Q_q_hr     = Decimal.Parse(textBox15.Text);
            ekm1       = Double.Parse(textBox7.Text);
            radiator1  = comboBox4.Text;

            decimal alpha = 0;
            decimal p, phr, Nphr, gvs_max_hr, gvs_avg_hr, gvs_avg_day, Q_gvs_max_hr_kvt, Q_gvs_max_hr_cal, Q_gvs_max_hr_kcal, Q_gvs_avg_hr_kvt, Q_gvs_avg_hr_cal, Q_gvs_avg_hr_kcal, Q_gvs_avg_day_kvt, Q_gvs_avg_day_cal, Q_gvs_avg_day_kcal;

            p = Math.Round((gvs_q_hr * users) / (Q_q_day * gvs_count * 3600), 3, MidpointRounding.AwayFromZero);


            phr  = Math.Round((3600 * p * Q_q_day) / Q_q_hr, 3, MidpointRounding.AwayFromZero);
            Nphr = Math.Round(Int32.Parse(textBox11.Text) * phr, 2, MidpointRounding.AwayFromZero);


            con.Open();
            SqlCommand cmd = con.CreateCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = (String.Format("SELECT AlphaVal FROM AlphaTable WHERE N = {0}", Nphr.ToString().Replace(',', '.')));
            cmd.ExecuteNonQuery();
            SqlDataReader dr;

            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if ((decimal)Nphr <= (decimal)0.015)
                {
                    alpha = (decimal)0.2;
                }
                alpha = Decimal.Parse(dr["AlphaVal"].ToString());
            }
            con.Close();

            textBox16.Text = alpha.ToString();

            gvs_max_hr         = Math.Round((decimal)0.005 * Q_q_hr * alpha, 3, MidpointRounding.AwayFromZero);
            gvs_avg_hr         = Math.Round((gvs_q_day * users) / (1000 * usage_hrs), 3, MidpointRounding.AwayFromZero);
            gvs_avg_day        = Math.Round((gvs_q_day * users) / 1000, 3, MidpointRounding.AwayFromZero);
            Q_gvs_max_hr_kvt   = Math.Round((decimal)1.16 * gvs_max_hr * ((decimal)t2_obratka - 5), 1, MidpointRounding.AwayFromZero);
            Q_gvs_max_hr_cal   = Math.Round(Q_gvs_max_hr_kvt * (decimal)860.4206);
            Q_gvs_max_hr_kcal  = Q_gvs_max_hr_cal / (decimal)1000000.0;
            Q_gvs_avg_hr_kvt   = Math.Round((decimal)1.16 * gvs_avg_hr * ((decimal)t2_obratka - 5), 2, MidpointRounding.AwayFromZero);
            Q_gvs_avg_hr_cal   = Math.Round(Q_gvs_avg_hr_kvt * (decimal)860.4206);
            Q_gvs_avg_hr_kcal  = Q_gvs_avg_hr_cal / (decimal)1000000.0;
            Q_gvs_avg_day_kvt  = Math.Round(Q_gvs_avg_hr_kvt * usage_hrs, 2, MidpointRounding.AwayFromZero);
            Q_gvs_avg_day_cal  = Math.Round(Q_gvs_avg_day_kvt * (decimal)860.4206);
            Q_gvs_avg_day_kcal = Q_gvs_avg_day_cal / (decimal)1000000.0;


            p1          = $"1. Расходные данные для расчета тепла на отопление\r\n1.1 Расчетная величина наружного воздуха для расчета отопления:\r\n{t_air}\r\n\r\n1.2 Средняя температура наружного воздуха за отопительный период:\r\n{t_zima}\r\n\r\n1.3 Расчетная внутренняя температура в помещениях:\r\n{t_house}\r\n\r\n1.4 Температурный напор отопительных приборов и трубопроводов:\r\n\r\nTн = t1 + t2 / 2 - tв\r\n\r\nгде:\r\nt1 - температура в подающем трубопроводе: t1 = +{t1_podacha}C\r\nt2 - температура воды в обратном трубопроводе t2 = +{t2_obratka}C\r\n\r\nTн = {t1_podacha} + {t2_obratka} / 2 - {t_house} = {t_napor}\r\n\r\n1.5Количество и характеристики отопительных приборов и трубопроводов\r\nТип радиатора: {radiator1}, количество секций: {count_sec}, 1 секция = {ekm1}экм, D = {d_pipe}, {l_pipe}";
            p2          = $"2. Расчет расхода тепла на отопление.\r\n\r\n2.1. Поверхность установленных приборов отопления:\r\nFрад = {ekm1} x {count_sec} = {s_sec}экм\r\n\r\n2.2 Теплоотдача установленных приборов отопления при теплопередаче\r\n1экм = 435 ккал/ч, при tв = +18С;\r\nQрад = 435 x {s_sec} = {q_rad}ккал/ч\r\n\r\n2.3. Теплоотдача открыто проложенных трубопроводов:\r\n Qтр = q x L x Tп\r\nгде q теплоотдача 1м трубопровода при d = {d_pipe}, q = {q_heat_loss}ккал/ч, L - длина трубопровода\r\nQтр = {q_heat_loss} x {l_pipe} x {t_napor} = {q_tr}ккал/ч\r\n\r\n2.4. Общая теплоотдача - максимальный часовой расход тепла на отопление:\r\nQmaxот = Qпрот + Qтр = {q_rad} + {q_tr} = {q_max}ккал/ч({q_maxGcal}Гкал/ч)\r\n\r\n2.5. Среднечасовой расход тепла за отопительный период:\r\nQсрот = Qmaxот / {t_house} - ({t_zima}) x {t_house} - ({t_air}) = {q_max} / {t_house} - ({t_zima}) x {t_house} - ({t_air}) = {q_avg}ккал/ч({q_avg_Gcal}Гкал/ч)\r\n\r\n2.6. Среднесуточный расход тепла на отопление:\r\nQсутот = Qсрот x 24 = {q_avg} x 24 = {q_day}ккал/сут({q_day_Gcal}Гкал/сут)";
            p3          = $"3. Исходные данные для расчета тепла на горячее водоснабжение\r\n\r\nКоличество пользователей - {users} (U)\r\nРежим работы в часах - {usage_hrs}\r\nКоличество санитарных приборов, потребляющих горячую воду - {usage_hrs} шт. (N)\r\nРасход горячей воды потребителем:\r\nqhu = {gvs_q_day}\tqhhr,u = {gvs_q_hr}\r\nРасчет горячей воды санприбором:\r\nqho = {Q_q_day}\tqho,hr = {Q_q_hr}\r\n\r\n4. Расчет расхода горячей воды\r\n\r\np = qhhr,u x U / qho x N x 3600 = {gvs_q_hr} x {users} / {Q_q_day} x {users} x 3600 = {p}\r\nphr = 3600 x p x qho / qho,hr = 3600 x {p} x {Q_q_day} / {Q_q_hr} = {phr}\r\nNphr = {gvs_count} x {phr} = {Nphr}, a = {alpha}\r\n\r\nМаксимальный часовой расход горячей воды:\r\nqhhr = 0.005 x qho,hr x a = 0.005 x {Q_q_hr} x {alpha} = {gvs_max_hr}м3/ч\r\n\r\nСреднечасовой расход горячей воды:\r\nqht = qhu x U / 1000 x T = {gvs_q_day} x {users} / 1000 x {usage_hrs} = {gvs_avg_hr}м3/ч\r\n\r\nСреднесуточный расход горячей воды:\r\nqu = qhu x U / 1000 = {gvs_q_day} x {users} / 1000 = {gvs_avg_day}м3/сут";
            p4          = $"5. Расчет тепла на горячее водоснабжение\r\n\r\nЧасовое максимальное потребление:\r\nQhhr = 1.16 x qhhr ({t2_obratka} - 5) = 1.16 x {gvs_max_hr} x ({t2_obratka} - 5) = {Q_gvs_max_hr_kvt}кВт\r\nQhhr = {Q_gvs_max_hr_kvt}кВт = {Q_gvs_max_hr_cal}ккал/ч({Q_gvs_max_hr_kcal}Гкал/ч)\r\n\r\nСреднечасовое потребление:\r\nQht = 1.16 x qht x ({t2_obratka} - 5) = 1.16 x {gvs_avg_hr} x ({t2_obratka} 5) = {Q_gvs_avg_hr_kvt}кВт\r\nQht = {Q_gvs_avg_hr_kvt}кВт = {Q_gvs_avg_hr_cal}ккал/ч({Q_gvs_avg_hr_kcal}Гкал/ч\r\n\r\nСуточное потребление:\r\nQhu = Qht x T = {Q_gvs_avg_day_kvt} x {usage_hrs} = {Q_gvs_avg_day_kvt}кВт\r\nQhu = {Q_gvs_avg_day_kvt} = {Q_gvs_avg_day_cal}ккал/ч({Q_gvs_avg_day_kcal}Гкал/ч)";
            resBox.Text = $"{p1}\r\n\r\n{p2}\r\n\r\n{p3}\r\n\r\n{p4}";
        }
Пример #60
0
        public List<TagCuenta> Busca_TagCuenta(string Cruce, double saldo)
        {

            List<TagCuenta> ListaTagCuenta = new List<TagCuenta>();

            try
            {

                string Query = string.Empty;

                //string SQL = "Data Source=.;Initial Catalog=GTDB; Integrated Security=False;User Id=SA;Password=CAPUFE";
                string SQL = "Data Source=.;Initial Catalog=GTDBPruebas; Integrated Security=False;User Id=Sa;Password=CAPUFE";

                SqlConnection ConexionSQL = new SqlConnection(SQL);

                Query = "Select CuentaId, NumTag, NumCuenta, StatusTag, StatusCuenta, TypeCuenta, SaldoCuenta, SaldoTag " +
                        "From Tags t Inner Join CuentasTelepeajes c on t.CuentaId = c.Id Where t.NumTag = '" + Cruce + "'";

                using (SqlCommand cmd = new SqlCommand(Query, ConexionSQL))
                {
                    try
                    {
                        ConexionSQL.Open();
                        cmd.ExecuteNonQuery();
                        DataTable dt = new DataTable();
                        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
                        myAdapter.Fill(dt);


                        if (dt.Rows.Count != 0)
                        {
                            var pruebas = dt.Rows[0]["TypeCuenta"].ToString();
                            var CasandoBug = dt.Rows[0]["NumTag"].ToString();

                            //if (CasandoBug == "50100000877")
                            //{
                            //    string lisds = "AQUI";
                            //}

                            //else if (CasandoBug == "50100000663")
                            //{
                            //    string istas = "OAQUI";
                            //}

                            if (dt.Rows[0]["TypeCuenta"].ToString() == "Individual")
                            {

                                ListaTagCuenta.Add(new TagCuenta
                                {
                                    CuentaId = Convert.ToInt64(dt.Rows[0]["CuentaId"].ToString()),
                                    NumTag = Convert.ToString(dt.Rows[0]["NumTag"]),
                                    NumCuenta = Convert.ToString(dt.Rows[0]["NumCuenta"]),
                                    StatusTag = Convert.ToBoolean(dt.Rows[0]["StatusTag"]),
                                    StatusCuenta = Convert.ToBoolean(dt.Rows[0]["StatusCuenta"]),
                                    TypeCuenta = Convert.ToString(dt.Rows[0]["TypeCuenta"]),
                                    SaldoCuenta = 0,
                                    SaldoTag = double.Parse((Convert.ToDouble(Convert.ToString(dt.Rows[0]["SaldoTag"])) / 100.00).ToString("F2")),
                                    DescuentoCruce = saldo
                                    //DescuentoCruce = Convert.ToDouble(saldo)
                                    //DescuentoCruce = Convert.ToDouble("15.25")

                                });
                            }
                            else
                            {
                                ListaTagCuenta.Add(new TagCuenta
                                {
                                    CuentaId = Convert.ToInt64(dt.Rows[0]["CuentaId"].ToString()),
                                    NumTag = Convert.ToString(dt.Rows[0]["NumTag"]),
                                    NumCuenta = Convert.ToString(dt.Rows[0]["NumCuenta"]),
                                    StatusTag = Convert.ToBoolean(dt.Rows[0]["StatusTag"]),
                                    StatusCuenta = Convert.ToBoolean(dt.Rows[0]["StatusCuenta"]),
                                    TypeCuenta = Convert.ToString(dt.Rows[0]["TypeCuenta"]),
                                    SaldoCuenta = double.Parse((Convert.ToDouble(Convert.ToString(dt.Rows[0]["SaldoCuenta"])) / 100.00).ToString("F2")),
                                    SaldoTag = double.Parse((Convert.ToDouble(Convert.ToString(dt.Rows[0]["SaldoTag"])) / 100.00).ToString("F2")),
                                    DescuentoCruce = saldo
                                    //DescuentoCruce = Convert.ToDouble(saldo)
                                    //DescuentoCruce = Convert.ToDouble("15.25")

                                });
                            }

                        }


                    }
                    catch (Exception Ex)
                    {
                        using (StreamWriter file = new StreamWriter(path + archivo, true))
                        {
                            file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Busqueda de Cruce en SQL"); //se agrega información al documento
                            file.Dispose();
                            file.Close();
                        }
                        StopService();

                    }
                    finally
                    {
                        ConexionSQL.Close();
                    }
                }

                return ListaTagCuenta;
            }
            catch (Exception Ex)
            {
                using (StreamWriter file = new StreamWriter(path + archivo, true))
                {
                    file.WriteLine("Error en el proceso ServicioWinProsis: " + Consecutivotxt.ToString() + " a las " + DateTime.Now.ToString() + " " + Ex.Message + " " + Ex.StackTrace + " " + "Busqueda de Cruce en SQL"); //se agrega información al documento
                    file.Dispose();
                    file.Close();
                }
                StopService();

            }

            return ListaTagCuenta;
        }