public DataTable SearchDocument(DSupplier supplier)
        {
            DataTable     DtResult = new DataTable("supplier");
            SqlConnection SqlCon   = new SqlConnection();

            try
            {
                SqlCon.ConnectionString = Conection.cn;
                SqlCommand SqlCmd = new SqlCommand();
                SqlCmd.Connection  = SqlCon;
                SqlCmd.CommandText = "spsearch_supplier_document";
                SqlCmd.CommandType = CommandType.StoredProcedure;


                SqlParameter ParSearch = new SqlParameter();
                ParSearch.ParameterName = "@searchtext";
                ParSearch.SqlDbType     = SqlDbType.VarChar;
                ParSearch.Size          = 50;
                ParSearch.Value         = supplier.SearchText;
                SqlCmd.Parameters.Add(ParSearch);


                SqlDataAdapter SqlData = new SqlDataAdapter(SqlCmd);
                SqlData.Fill(DtResult);
            }
            catch (Exception ex)
            {
                DtResult = null;
            }
            return(DtResult);
        }
        public string Delete(DSupplier supplier)
        {
            string        resp   = "";
            SqlConnection SqlCon = new SqlConnection();

            try
            {
                SqlCon.ConnectionString = Conection.cn;
                SqlCon.Open();

                SqlCommand SqlCmd = new SqlCommand();

                SqlCmd.Connection  = SqlCon;
                SqlCmd.CommandText = "spdelete_supplier";
                SqlCmd.CommandType = CommandType.StoredProcedure;

                //Initialize variables
                SqlParameter ParId = new SqlParameter();
                ParId.ParameterName = "@id"; //same name as in the procedure
                ParId.SqlDbType     = SqlDbType.Int;
                ParId.Value         = supplier.Id;

                SqlCmd.Parameters.Add(ParId);



                //Execute the command
                resp = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "Not deleted";
            }
            catch (Exception ex)
            {
                resp = "Something went wrong: " + ex.Message;
            }
            finally
            {
                if (SqlCon.State == ConnectionState.Open)
                {
                    SqlCon.Close();
                }
            }

            return(resp);
        }
        public string Edit(DSupplier supplier)
        {
            string        resp   = "";
            SqlConnection SqlCon = new SqlConnection();

            try
            {
                SqlCon.ConnectionString = Conection.cn;
                SqlCon.Open();

                SqlCommand SqlCmd = new SqlCommand();

                SqlCmd.Connection  = SqlCon;
                SqlCmd.CommandText = "spedit_supplier";
                SqlCmd.CommandType = CommandType.StoredProcedure;

                //Initialize variables
                SqlParameter ParId = new SqlParameter();
                ParId.ParameterName = "@id";
                ParId.SqlDbType     = SqlDbType.Int;
                ParId.Value         = supplier.Id;
                //ParId.Direction = ParameterDirection.Output;

                SqlCmd.Parameters.Add(ParId);

                SqlParameter ParCompany = new SqlParameter();
                ParCompany.ParameterName = "@company";
                ParCompany.SqlDbType     = SqlDbType.VarChar;
                ParCompany.Size          = 50;
                ParCompany.Value         = supplier.Company;

                SqlCmd.Parameters.Add(ParCompany);

                SqlParameter ParBusiness_department = new SqlParameter();
                ParBusiness_department.ParameterName = "@business_department";
                ParBusiness_department.SqlDbType     = SqlDbType.VarChar;
                ParBusiness_department.Size          = 50;
                ParBusiness_department.Value         = supplier.Business_Department;

                SqlCmd.Parameters.Add(ParBusiness_department);

                SqlParameter ParDocumentType = new SqlParameter();
                ParDocumentType.ParameterName = "@document_type";
                ParDocumentType.SqlDbType     = SqlDbType.VarChar;
                ParDocumentType.Size          = 50;
                ParDocumentType.Value         = supplier.Document_Type;

                SqlCmd.Parameters.Add(ParDocumentType);

                SqlParameter ParDocument_number = new SqlParameter();
                ParDocument_number.ParameterName = "@document_number";
                ParDocument_number.SqlDbType     = SqlDbType.VarChar;
                ParDocumentType.Size             = 50;
                ParDocument_number.Value         = supplier.Document_Number;

                SqlCmd.Parameters.Add(ParDocument_number);

                SqlParameter ParAddress = new SqlParameter();
                ParAddress.ParameterName = "@address";
                ParAddress.SqlDbType     = SqlDbType.VarChar;
                ParAddress.Size          = 150;
                ParAddress.Value         = supplier.Address;

                SqlCmd.Parameters.Add(ParAddress);

                SqlParameter ParPhone = new SqlParameter();
                ParPhone.ParameterName = "@phone";
                ParPhone.SqlDbType     = SqlDbType.VarChar;
                ParPhone.Size          = 50;
                ParPhone.Value         = supplier.Phone;

                SqlCmd.Parameters.Add(ParPhone);


                SqlParameter ParEmail = new SqlParameter();
                ParEmail.ParameterName = "@email";
                ParEmail.SqlDbType     = SqlDbType.VarChar;
                ParEmail.Size          = 50;
                ParEmail.Value         = supplier.Email;

                SqlCmd.Parameters.Add(ParEmail);

                SqlParameter ParUrl = new SqlParameter();
                ParUrl.ParameterName = "@url";
                ParUrl.SqlDbType     = SqlDbType.VarChar;
                ParUrl.Size          = 50;
                ParUrl.Value         = supplier.Url;

                SqlCmd.Parameters.Add(ParUrl);

                //Execute the command
                resp = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "Not Edited";
            }
            catch (Exception ex)
            {
                resp = "Something went wrong: " + ex.Message;
            }
            finally
            {
                if (SqlCon.State == ConnectionState.Open)
                {
                    SqlCon.Close();
                }
            }
            return(resp);
        }