[code=c#]
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Globalization;
using System.Diagnostics;
using System.Net;
using System.Net.Mail;
using System.Runtime.InteropServices;
using System.Net.Mime;
//using System.Threading.Tasks;


namespace GebrNaimSalesWindowsForms
{
    public partial class Form1 : Form
    {
        //SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-E2S6MTB\SQLEXPRESS;Initial Catalog=GebrNaimProject;Integrated Security=True");

        //SqlDataAdapter adapt;
        //public string[] files { get; set; }
        public List<string> files { get; set; }
        public List<string> outputfiles { get; set; }
        public int insertNummer;
        public int row_id;
        public Process sp;
        public string ExceptionMessage { get; set; }

        public Form1()
        {
            InitializeComponent();
            //DisplayData();
        }

        //Display Data in DataGridView  
        //private void DisplayData()
        //{
        //    con.Open();
        //    DataTable dt = new DataTable();
        //    adapt = new SqlDataAdapter("select * from StoredFile", con);
        //    adapt.Fill(dt);
        //    dataGridView1.DataSource = dt;
        //    con.Close();
        //}
        private void btnSales_Click(object sender, EventArgs e)
        {
            outputfiles = null;
            files = null;
            sp = null;
            
            //sp.Refresh();

            if (InformationWindow("Выбирете пожалйста фалы и сохраните в Windows", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == System.Windows.Forms.DialogResult.OK)
            {

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    foreach (var item in openFileDialog1.FileNames)
                    {
                        //files.Add(item);

                        files = new List<string>();
                        files.Add(item);

                    }
                    
                  
                }
                else
                {
                    files = null;
                }

                GetInsertNummer(@"SELECT (SELECT MAX(id) FROM StoredFile) id, MAX(InsertNummer) InsertNummer
FROM StoredFile");

                if (files != null)
                {
                    for (int i = 0; i < files.Count; i++)
                    {
                        InsertInTable(i);

                        row_id++;


                        FileGetSimple(row_id, files[i]);
                      
                    }
                    InformationWindow("Все в порядке с выбранными файлами?", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    CreateMessage("smtp.strato.de");

                    MessageBox.Show("success");


                }

                //DisplayData();


            }


        }

        public DialogResult InformationWindow(string v1, string v2, MessageBoxButtons oKCancel, MessageBoxIcon information)
        {
            string message = v2;
            string caption = v1;
            MessageBoxButtons buttons = oKCancel;
            DialogResult result;

            // Displays the MessageBox.
            return result = MessageBox.Show(message, caption, buttons, information);
        }



        public void CreateMessage(string server)
        {
            using (SmtpClient client = new SmtpClient(server))
            {

                client.Port = 587;
                client.EnableSsl = true;
                
                client.Credentials = new NetworkCredential("s.dadashev@gebr-naim.de", "");

                MailAddress from = new MailAddress("---",--- );
                MailAddress to = new MailAddress("---", ---);
                MailMessage message = new MailMessage(from, to);

                // message.Subject = "Using the SmtpClient class.";
                message.Subject = "Es funktioniert.";
                message.Body = @"Вы получили письмо с вложением";

                foreach (var item in outputfiles)
                {
                    if (!sp.HasExited)
                    {
                        //sp.WaitForExit();
                        sp.Kill();
                        

                        //if (Marshal.IsComObject(sp))
                        //{
                        //    Marshal.ReleaseComObject(sp);
                        //}

                        
                        //Marshal.ReleaseComObject(sp);
                    }
                    else
                        sp = null;
                    //sp.WaitForExit();
                    //sp.Close();

                    //Process[] ps2 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                    //foreach (Process p2 in ps2)
                    //{
                    //    Marshal.ReleaseComObject(p2);
                    //    p2.Kill();
                    //}

                    message.Attachments.Add(new Attachment(item));


                }


                InformationWindow("Success", $"Sending an email message to" + to.Address + " by using the SMTP host " + client.Host + ".", MessageBoxButtons.OK, MessageBoxIcon.Information);

                try
                {
                    client.Send(message);
                }
                catch (Exception ex)
                {
                    // Me.WriteLine("Exception caught in CreateCopyMessage(): {0}",ex.ToString());
                    InformationWindow("Exception", ex.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
               //
            }
        }

  

//using(SmtpClient smtpClient = new SmtpClient())
//{
//    var basicCredential = new NetworkCredential("username", "password"); 
//    using(MailMessage message = new MailMessage())
//    {
//        MailAddress fromAddress = new MailAddress("from@yourdomain.com");

//        smtpClient.Host = "mail.mydomain.com";
//        smtpClient.UseDefaultCredentials = false;
//        smtpClient.Credentials = basicCredential;

//        message.From = fromAddress;
//        message.Subject = "your subject";
//        // Set IsBodyHtml to true means you can send HTML email.
//        message.IsBodyHtml = true;
//        message.Body = "<h1>your message body</h1>";
//        message.To.Add("to@anydomain.com"); 

//        try
//        {
//            smtpClient.Send(message);
//        }
//        catch(Exception ex)
//        {
//            //Error, could not send the message
//            Response.Write(ex.Message);
//        }
//    }
//}



        private void InsertInTable(int i)
        {
            // **** Write BLOB to the Database
            byte[] filebyte = File.ReadAllBytes(files[i]);

            string sql = @"insert into StoredFile(FileName,Data,InsertNummer,Date) values(@FileName,@Data,@InsertNummer,@Date)";

            var cmd = MakeConnection(sql);

            SqlParameter parameter1 = cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(files[i]));
            SqlParameter parameter2 = cmd.Parameters.AddWithValue("@Data", filebyte);
            SqlParameter parameter3 = cmd.Parameters.AddWithValue("@InsertNummer", insertNummer);
            SqlParameter parameter4 = cmd.Parameters.AddWithValue("@Id", row_id);
            SqlParameter parameter5 = cmd.Parameters.AddWithValue("@Date", DateTime.Now);


            cmd.ExecuteNonQuery();
            

          
        }

        public bool FileGetSimple(int Identifier, string filePath)
        {
            var pa = Path.Combine(Path.GetTempPath(), Path.GetFileName(filePath));

            var statement = "SELECT Id, Data, FileName FROM StoredFile WHERE Id = @id;";
            var mc = MakeConnection(statement);
         

            mc.Parameters.AddWithValue("@id", Identifier);
            SqlDataReader reader = mc.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                // the blob field
                int ndx = reader.GetOrdinal("Data");

                var blob = new Byte[(reader.GetBytes(ndx, 0, null, 0, int.MaxValue))];

                reader.GetBytes(ndx, 0, blob, 0, blob.Length);
                reader.Close();

                try
                {
                    //using (var fs = new FileStream(pa, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
                    //{
                    //    fs.Write(blob, 0, blob.Length);
                    //}

                    if (File.Exists(pa))
                    {
                        File.Delete(pa);
                    }

                    using (var fs = new FileStream(pa, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
                    {
                        fs.Write(blob, 0, blob.Length);

                    }


                    

                    //using (FileStream fs = File.Create(pa))
                    //{
                    //    fs.Write(blob, 0, blob.Length);
                    //}

                    //using (var stream = File.Create(pa))
                    //{
                    //File.WriteAllBytes(pa, blob);
                    //}
                }
                catch (IOException e) {
                    InformationWindow("Warning", e.Message + " Schlie&#223;en Sie diese Datei, um fortzusetzen.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    FileGetSimple(Identifier, filePath);
                }

   


                
            }



            OpenFiles(pa);

            return true;

        }

        private void OpenFiles(string tempFilePath)
        {
            

            using (sp = new Process())
            {
                sp = Process.Start(tempFilePath);
            }



            outputfiles = new List<string>();
            outputfiles.Add(tempFilePath);

        }

        public void GetInsertNummer(string ins_num_query)
        {

            var ins_num = MakeConnection(ins_num_query);
            SqlDataReader read_ins_nummer = ins_num.ExecuteReader();

            while (read_ins_nummer.Read())
            {
                int ins = read_ins_nummer.GetOrdinal("InsertNummer");
                int ins_id = read_ins_nummer.GetOrdinal("Id");
                if (!read_ins_nummer.IsDBNull(read_ins_nummer.GetOrdinal("InsertNummer")))
                {
                    insertNummer = read_ins_nummer.GetInt32(ins);
                    row_id = read_ins_nummer.GetInt32(ins_id);
                    insertNummer++;
                }
                else
                {
                    insertNummer = 1;
                    row_id = 0;
                }

            }
        }



        public SqlCommand MakeConnection(string sql)
        {

            SqlConnection connection = new SqlConnection(Properties.Settings.Default.connection_str);
            connection.Open();


            using (SqlCommand cmd = new SqlCommand() { Connection = connection, CommandText = sql })
            {
                try
                {
                    return cmd;

                }
                catch (Exception ex)
                {

                    ExceptionMessage = ex.Message;
                    return cmd;
                }
            }
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //this.Close();
            Application.Exit();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'gebrNaimProjectDataSet.StoredFile' table. You can move, or remove it, as needed.
            //this.storedFileTableAdapter.Fill(this.gebrNaimProjectDataSet.StoredFile);

        }
    }
}

 public bool FileGetSimple(int Identifier, string filePath)
        {
            var pa = Path.Combine(Path.GetTempPath(), Path.GetFileName(filePath));

            var statement = "SELECT Id, Data, FileName FROM StoredFile WHERE Id = @id;";
            var mc = MakeConnection(statement);
         

            mc.Parameters.AddWithValue("@id", Identifier);
            SqlDataReader reader = mc.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                // the blob field
                int ndx = reader.GetOrdinal("Data");

                var blob = new Byte[(reader.GetBytes(ndx, 0, null, 0, int.MaxValue))];

                reader.GetBytes(ndx, 0, blob, 0, blob.Length);
                reader.Close();

                try
                {
                    //using (var fs = new FileStream(pa, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
                    //{
                    //    fs.Write(blob, 0, blob.Length);
                    //}

                    if (File.Exists(pa))
                    {
                        File.Delete(pa);
                    }

                    using (var fs = new FileStream(pa, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
                    {
                        fs.Write(blob, 0, blob.Length);

                    }


                    

                    //using (FileStream fs = File.Create(pa))
                    //{
                    //    fs.Write(blob, 0, blob.Length);
                    //}

                    //using (var stream = File.Create(pa))
                    //{
                    //File.WriteAllBytes(pa, blob);
                    //}
                }
                catch (IOException e) {
                    InformationWindow("Warning", e.Message + " Schließen Sie diese Datei, um fortzusetzen.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    FileGetSimple(Identifier, filePath);
                }

   


                
            }



                   OpenFiles(pa); 

            return true;

        }

        private void OpenFiles(string tempFilePath)
        {
            

            using (sp = new Process())
            {
                  sp = Process.Start(tempFilePath); 
            }



            outputfiles = new List<string>();
            outputfiles.Add(tempFilePath);

        }

                foreach (var item in outputfiles)
                {
                    if (!sp.HasExited)
                    {
                        //sp.WaitForExit();
                           sp.Kill(); 
                        

                        //if (Marshal.IsComObject(sp))
                        //{
                        //    Marshal.ReleaseComObject(sp);
                        //}

                        
                        //Marshal.ReleaseComObject(sp);
                    }
                    else
                              sp = null;
                    //sp.WaitForExit();
                    //sp.Close();

                    //Process[] ps2 = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                    //foreach (Process p2 in ps2)
                    //{
                    //    Marshal.ReleaseComObject(p2);
                    //    p2.Kill();
                    //}

                    message.Attachments.Add(new Attachment(item));

                try
                {

                    if (File.Exists(pa))
                    {
                        File.Delete(pa);
                    }

                    using (var fs = new FileStream(pa, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
                    {
                        fs.Write(blob, 0, blob.Length);

                    }

                }
                catch (IOException e) {
                    InformationWindow("Warning", e.Message + " Schließen Sie diese Datei, um fortzusetzen.", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    Fil