public bool Delete(string empresaid, tb_co_tabla01_mediopago BE)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_DELETE", cnx))
         {
             {
                 cmd.CommandTimeout = 0;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
             }
             try
             {
                 cnx.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                 {
                     return true;
                 }
                 else
                 {
                     return false;
                 }
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }
         }
     }
 }

        public static Usuario GetUsuarioSinPass(String puser)
        {
            Usuario usuario = null;
            var sqlQuery = "SELECT  (c.nombre +' '+ c.apellidos) As nombre_completo, u.id_usuario, u.nombre_usuario, u.id_empleado,  u.id_rol, u.pregunta_seguridad, u.respuesta_seguridad FROM Usuarios u  JOIN Empleados e On u.id_empleado = e.id_empleado JOIN Candidatos c On e.id_candidato = c.id_candidato WHERE u.nombre_usuario  = @puser and u.esta_oculto=0 ";
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.AddWithValue("@puser", puser);

            var ds = DBAccess.ExecuteSQLWithDS(cmd, sqlQuery);
            if (ds.Tables[0].Rows.Count > 0)
            {
                var dr = ds.Tables[0].Rows[0];
                usuario = new Usuario
                {
                    Id = Convert.ToInt32(dr["id_usuario"]),
                    NombreUsuario = dr["nombre_usuario"].ToString(),
                    IdEmpleado = Convert.ToInt32(dr["id_empleado"]),
                    IdRol = Convert.ToInt32(dr["id_rol"].ToString()),
                    PreguntaSeguridad = dr["pregunta_seguridad"].ToString(),
                    RespuestaSeguridad = dr["respuesta_seguridad"].ToString(),
                    NombreCompleto = dr["nombre_completo"].ToString()
                };
            }

            return usuario;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            System.IO.StreamReader reader = new System.IO.StreamReader(HttpContext.Current.Request.InputStream);
            string requestFromPost = reader.ReadToEnd();

            //loop through
               // string formValue;

            string speed;
            string initialLocation;
            string finalLocation;
            string IMEI;

            if (!string.IsNullOrEmpty(Request.Form["txtSpeed"]))
            {
                //formValue = Request.Form["txtSpeed"];
                //formValue = Request.Form["txtImei"];
                speed = Request.Form["Speed"];
                initialLocation = Request.Form["initialLocation"];
                finalLocation = Request.Form["finalLocation"];
                IMEI = Request.Form["IMEI"];

                string s = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
                SqlConnection cn = new SqlConnection(s);
                cn.Open();
                SqlCommand cmd = new SqlCommand("insert into DataHistory(Speed, initialLocation, finalLocation, IMEI)values('" + speed + "','" + initialLocation + "','" + finalLocation + "','" + IMEI + "')", cn);
                cmd.ExecuteNonQuery();

            }
        }

        public RootObjectOut GetMessageByUser(UserIn jm)
        {
            RootObjectOut output = new RootObjectOut();
            String jsonString = "";
            try
            {
                String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                SqlConnection Connection = new SqlConnection(strConnection);
                String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
                SqlCommand Command = new SqlCommand(strSQL, Connection);
                Connection.Open();
                SqlDataReader Dr;
                Dr = Command.ExecuteReader();
                if (Dr.HasRows)
                {
                    if (Dr.Read())
                    {
                        jsonString = Dr.GetValue(0).ToString();
                    }
                }
                Dr.Close();
                Connection.Close();
            }
            catch (Exception ex)
            {
                output.errorMessage = ex.Message;
            }
            finally
            {
            }
            JavaScriptSerializer ser = new JavaScriptSerializer();
            output = ser.Deserialize<RootObjectOut>(jsonString);

            return output;
        }

        static void Main(string[] args)
        {
            Console.Write("Enter some text to search for it in the Products: ");
            var searchedProduct = Console.ReadLine();

            SqlConnection dbCon = new SqlConnection("Server=localhost; " +
                "Database=NORTHWND; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand(
                    "SELECT ProductName FROM Products " +
                    "WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);

                cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);

                SqlDataReader reader = cmd.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];
                        Console.WriteLine(productName);
                    }
                }
            }
        }

 private void repositoryItemButtonEditDelete_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
 {
     if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
         return;
     DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
     if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
     {
         LoadData();
         return;
     }
     SqlConnection con = new SqlConnection(MyCL.SqlConStr);
     SqlCommand cmd = new SqlCommand("", con);
     try
     {
         cmd.CommandText = @"Delete From CDknow Where knowID = " + Row["knowID"];
         con.Open();
         cmd.ExecuteNonQuery();
         MyCL.ShowMsg("تم الحذف", false, this);
     }
     catch (SqlException ex)
     {
         MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
     }
     con.Close();
     LoadData();
 }

 private void repositoryItemButtonEditSave_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
 {
     if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
         return;
     DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
     SqlConnection con = new SqlConnection(MyCL.SqlConStr);
     SqlCommand cmd = new SqlCommand("", con);
     try
     {
         if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
         {
             string NewID = MyCL.GetNewID("CDknow", "knowID");
             cmd.CommandText = string.Format(@"Insert Into CDknow (knowID, know) VALUES ({0}, '{1}')",
             NewID, Row["know"]);
         }
         else
         {
             cmd.CommandText = string.Format(@"Update CDknow Set know = '{0}' Where knowID = {1}",
             Row["know"], Row["knowID"]);
         }
         con.Open();
         cmd.ExecuteNonQuery();
         MyCL.ShowMsg("تم الحفظ", false, this);
     }
     catch (SqlException ex)
     {
         MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
     }
     con.Close();
     LoadData();
 }

        public static void Main()
        {
            var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
            connection.Open();
            var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);

            var categoriesAndProducts = new Dictionary<string, List<string>>();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var category = (string)reader["CategoryName"];
                var product = (string)reader["ProductName"];
                if(categoriesAndProducts.ContainsKey(category))
                {
                    categoriesAndProducts[category].Add(product);
                }
                else
                {
                    categoriesAndProducts.Add(category, new List<string>());
                }
            }

            connection.Close();

            foreach (var kvp in categoriesAndProducts)
            {
                Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));

                foreach (var product in kvp.Value)
                {
                    Console.WriteLine("-- " + product);
                }
            }
        }

 public bool FindUserEmailID(int userEmailID)
 {
     string sql = string.Format("SELECT * FROM UserEmails WHERE UserEmailID = '{0}' ", userEmailID);
     using(Connection cn = new Connection())
     {
         using (SqlCommand cmd = new SqlCommand())
         {
             try
             {
                 cmd.Connection = cn.SqlConnection;
                 cmd.CommandText = sql;
                 int recordsAffected = cmd.ExecuteNonQuery();
                 SqlDataReader reader = cmd.ExecuteReader();
                 if (reader.HasRows)
                 {
                     while (reader.Read())
                     {
                         //UserID = (int)reader["UserID"];
                         //loginName = reader["LoginName"].ToString();
                     }
                     return true;
                 }
                 else
                     return false;
             }
             catch (Exception ex)
             {
                 System.Diagnostics.Debug.WriteLine(ex.Message);
                 return false;
             }
         }
     }
     //return true;
 }

 public static DataTable Ilceler(int id)
 {
     try
     {
         using (baglan = new SqlConnection(ConnectionString))
         {
             baglan.Open();
             using (komutver = new SqlCommand())
             {
                 komutver.Connection = baglan;
                 komutver.CommandText = "ilce_Listele";
                 komutver.Parameters.Add("@ilceId", SqlDbType.Int);
                 komutver.Parameters["@ilceId"].Value = Convert.ToInt32(id);
                 komutver.CommandType = CommandType.StoredProcedure;
                 adp = new SqlDataAdapter(komutver);
                 dt = new DataTable();
                 adp.Fill(dt);
             }
         }
     }
     catch (Exception)
     {
         throw;
     }
     return dt;
 }

 public DataSet GetAll(string empresaid, tb_co_tabla01_mediopago BE)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_SEARCH", cnx))
         {
             DataSet ds = new DataSet();
             {
                 cmd.CommandTimeout = 0;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
                 cmd.Parameters.Add("@descripcion", SqlDbType.VarChar, 100).Value = BE.descripcion;
                 cmd.Parameters.Add("@sigla", SqlDbType.Char, 3).Value = BE.sigla;
                 cmd.Parameters.Add("@usuar", SqlDbType.Char, 15).Value = BE.usuar;
             }
             try
             {
                 cnx.Open();
                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                 {
                     da.Fill(ds);
                 }
                 return ds;
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }
         }
     }
 }

        public void GetDBBackup(string backupDBName, string backupPath)
        {
            try
            {
                DataBaseUtility db = new DataBaseUtility();

                SqlCommand cm = new SqlCommand();
                string s = DateTime.Now.ToString("MMM/dd/yyyy");

                SqlParameter[] sqlParams = new SqlParameter[]
            {   new SqlParameter("@DBNAME", backupDBName),
                new SqlParameter("@PATH", backupPath),
                new SqlParameter("@BACKUPTYPE", 1),
                new SqlParameter("@MSG", "Genrate Backup"),
                new SqlParameter("@BACKUPFILENAME", s.Replace("/", "_") )};

                db.ExecuteSP("DATABASE_BACKUP", sqlParams);

                log.Info("Backup created success fully with name "+ s.Replace("/", "_"));
            }
            catch (Exception ex)
            {

                log.Info("Exception in  GetDBBackup  ",ex);
            }
        }

        protected override void DataPortal_Insert()
        {
            bool cancel = false;
            OnInserting(ref cancel);
            if (cancel) return;

            const string commandText = "INSERT INTO [dbo].[Category] ([CategoryId], [Name], [Descn]) VALUES (@p_CategoryId, @p_Name, @p_Descn)";
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using(var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddWithValue("@p_CategoryId", this.CategoryId);
                command.Parameters.AddWithValue("@p_Name", ADOHelper.NullCheck(this.Name));
                command.Parameters.AddWithValue("@p_Descn", ADOHelper.NullCheck(this.Description));

                    //result: The number of rows changed, inserted, or deleted. -1 for select statements; 0 if no rows were affected, or the statement failed. 
                    int result = command.ExecuteNonQuery();
                    if (result == 0)
                        throw new DBConcurrencyException("The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.");

                    LoadProperty(_originalCategoryIdProperty, this.CategoryId);
                }

                FieldManager.UpdateChildren(this, connection);
            }

            OnInserted();

        }

        private void DataPortal_Fetch(CategoryCriteria criteria)
        {
            bool cancel = false;
            OnFetching(criteria, ref cancel);
            if (cancel) return;

            string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
                    using(var reader = new SafeDataReader(command.ExecuteReader()))
                    {
                        if (reader.Read())
                            Map(reader);
                        else
                            throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
                    }
                }
            }

            OnFetched();
        }

        public static DataTable GetAllCidades(int estado_id)
        {
            DataTable retorno = new DataTable();
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");

            try
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(SQL.ToString(), connection);
                    command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
                    command.ExecuteNonQuery();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(retorno);
                }
            }
            catch (Exception erro)
            {
                throw erro;
            }

            return retorno;
        }

        public static int PH_BHTrans_Calc(string strConnDB, string strYearWeek)
        {
            try
            {
                int rtn = 0;
                using (SqlConnection sqlConn = new SqlConnection(strConnDB))
                {
                    if (sqlConn.State == ConnectionState.Closed ) sqlConn.Open();
                    SqlCommand sqlComm = new SqlCommand("usp_BH_Transaction_Calc", sqlConn);
                    sqlComm.CommandText = "usp_BH_Transaction_Calc";
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandTimeout = 0;
                    sqlComm.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@Tesco_Week", strYearWeek) });
                    rtn = sqlComm.ExecuteNonQuery();
                }
                    //int rtn =
                    //SqlTransaction sqlTran = new SqlConnection(strConnDB).BeginTransaction();

                    //SqlHelper.ExecuteNonQuery(strConnDB, CommandType.StoredProcedure, "usp_BH_Transaction_Calc"
                    //            , new SqlParameter[] {new SqlParameter("@Tesco_Week",strYearWeek)
                    //                                 });

                    return rtn;
            }
            catch (Exception ex)
            {
                throw new Exception("PH_BHTrans_Calc >> " + ex.Message);
            }
        }

        public void GetScheduledAppointments()
        {
            var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");



            string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
                             FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
                                  JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
                                  JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
                                  JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
                            WHERE e.eventOwner = 'mgeary'
                            ORDER BY e.eventID";
            using (var command = new SqlCommand(sql, con))
            {
                con.Open();
                using (var reader = command.ExecuteReader())
                {
                    var list = new List<ScheduledAppointment>();
                    while (reader.Read())
                        list.Add(new ScheduledAppointment
                        {
                            eventID = reader.GetInt32(0),
                            eventName = reader.GetString(1),
                            eventLocation = reader.GetString(2),
                            eventDate = reader.GetDateTime(3),
                            eventDuration = (float)reader.GetDouble(4),
                            eventUserName = reader.GetString(5)
                        });
                    allAppointments = list.ToArray();
                }
            }
        }

        public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
        {
            string str = "";
            string sql = "InsertKH";
            //   string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
            SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TenKH", TenKH);
            cmd.Parameters.AddWithValue("@gt", Gioitinh);
            cmd.Parameters.AddWithValue("@DC", Diachi);
            cmd.Parameters.AddWithValue("@SDT", SDT);
            cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
            cmd.Parameters.AddWithValue("@ghichu", Ghichu);
            //cmd.ExecuteNonQuery();

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            str = dt.Rows[0].ItemArray[0].ToString();

            cmd.Dispose();
            con.Close();

            return str;
        }

        public DataSet GetOne(string empresaid, tb_tipimpto BE)
        {
            using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
            {
                using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_SELECT", cnx))
                {
                    DataSet ds = new DataSet();

                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
                    }

                    try
                    {
                        cnx.Open();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(ds);
                        }
                        return ds;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        private static ICollection<string> FindProductBy(string partOfName)
        {
            var names = new List<string>();
            string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";

            var dbCon = new SqlConnection(connectionString);

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);

                command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");

                SqlDataReader reader = command.ExecuteReader();

                Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
                using (reader)
                {
                    string categoryName = string.Empty;
                    string currentCategoryName = string.Empty;
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];

                        names.Add(productName);
                    }
                }
            }

            return names;
        }