20.05.2020 - 22.05.2020 arası işler

Günün işi storeprocedure üzerinden dönen datayı excel'e kayıt etmek ve onu ilgili kişiye e-mail olarak attırmak.

Eventlog'a yazıyorsanız aklınızda bulunsun Link Link

Storeprocedure üzerinden c# üzerine kodu dinamik olarak aktarmak önce sp oluşturalım

Create table dbo.Customer(
Id INT,
Name VARCHAR(100),
Dob Date)

insert into dbo.Customer values(1,'John','1980-01-02')
go

Create procedure dbo.prc_Customer
AS
BEGIN
Select * From dbo.Customer
END

The below program can be used to run Stored Procedure in C# program and write results to Excel file dynamically.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//added below name spaces
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            //the datetime and Log folder will be used for error log file in case error occured
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {
                //Declare Variables
                //Provide Excel file name that you like to create
                string ExcelFileName = "Customer";
                //Provide the source folder path where you want to create excel file
                string FolderPath = @"C:\Destination\";
                //Provide the Stored Procedure Name
                string StoredProcedureName = "dbo.prc_Customer";
                //Provide Excel Sheet Name 
                string SheetName = "CustomerSheet";
                //Provide the Database in which Stored Procedure exists
                string DatabaseName = "TechBrothersIT";
                ExcelFileName = ExcelFileName + "_" + datetime;

                OleDbConnection Excel_OLE_Con = new OleDbConnection();
                OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                //Construct ConnectionString for Excel
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                //drop Excel file if exists
                File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

                //Create Connection to SQL Server Database from which you like to export tables to Excel
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =" + DatabaseName + "; " + "Integrated Security=true;";


                //Load Data into DataTable from by executing Stored Procedure
                string queryString =
                  "EXEC  " + StoredProcedureName;
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, SQLConnection);
                DataSet ds = new DataSet();
                adapter.Fill(ds);


                //Get Header Columns
                string TableColumns = "";

                // Get the Column List from Data Table so can create Excel Sheet with Header
                foreach (DataTable table in ds.Tables)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        TableColumns += column + "],[";
                    }
                }

                // Replace most right comma from Columnlist
                TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
                TableColumns = TableColumns.Remove(TableColumns.Length - 2);


                //Use OLE DB Connection and Create Excel Sheet
                Excel_OLE_Con.ConnectionString = connstring;
                Excel_OLE_Con.Open();
                Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
                Excel_OLE_Cmd.ExecuteNonQuery();


                //Write Data to Excel Sheet from DataTable dynamically
                foreach (DataTable table in ds.Tables)
                {
                    String sqlCommandInsert = "";
                    String sqlCommandValue = "";
                    foreach (DataColumn dataColumn in table.Columns)
                    {
                        sqlCommandValue += dataColumn + "],[";
                    }

                    sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                    sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                    sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue + ") VALUES(";

                    int columnCount = table.Columns.Count;
                    foreach (DataRow row in table.Rows)
                    {
                        string columnvalues = "";
                        for (int i = 0; i < columnCount; i++)
                        {
                            int index = table.Rows.IndexOf(row);
                            columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                        }
                        columnvalues = columnvalues.TrimEnd(',');
                        var command = sqlCommandInsert + columnvalues + ")";
                        Excel_OLE_Cmd.CommandText = command;
                        Excel_OLE_Cmd.ExecuteNonQuery();
                    }

                }
                Excel_OLE_Con.Close();

            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

Link

- Kodunuzu debug preprocessor ile çalıştırmak için Link

- Excel dosyanızı memorystream olarak kaydetmek için Link kullacağınız kütüphanenin link

- Asp.net projenizde gmail ile mail göndermek için kod kullandığınızda aşağıdaki hatayı alabilirsiniz
The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required. Learn more at
Bu hatayı gidermek için gmail hesabınıza bu işlemi yapabilmesi için yetki vermeniz gerekir.
Yetki Link

- Gmail ile mail gönderimi kod örneği Link

 var fromAddress = new MailAddress("o@gmail.com", "From Name");
                    var toAddress = new MailAddress("o1@xxxx.com", "To Name");
                    const string fromPassword = "1ttegergr";
                    const string subject = "Subject";
                    const string body = "Body";

             

                    var smtp = new SmtpClient
                    {
                        Host = "smtp.gmail.com",
                        Port = 587,
                        EnableSsl = true,
                        DeliveryMethod = SmtpDeliveryMethod.Network,
                        UseDefaultCredentials = false,
                        Credentials = new NetworkCredential(fromAddress.Address, fromPassword)
                    };
                    using (var message = new MailMessage(fromAddress, toAddress)
                    {
                        Subject = subject,
                        Body = body,
                        
                    })
                    {
                        smtp.Send(message);
                    }

Alternatif gönderim kodu

var path = @"C:\test\file.zip";
 using (MailMessage mail = new MailMessage())
                    {
                        mail.From = new MailAddress("o@gmail.com");
                        mail.To.Add("oo@xx.com");
                        mail.Subject = ExcelFileName + " Raporu Ektedir.";
                        mail.Body = "<h1>Hello</h1>";
                        mail.IsBodyHtml = true;
                        mail.Attachments.Add(new Attachment(path));

                        using (SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587))
                        {
                            smtp.Credentials = new NetworkCredential("o@gmail.com", "vvvvvvv");
                            smtp.EnableSsl = true;
                            smtp.Send(mail);
                        }
                    }

- DataTable içindeki kolonların içindeki datanın okunması için Link
- Sql having ifadesi kullanımı Link

Bu Blogda Ara

Kod günlüğü

20.05.2020 - 22.05.2020 arası işler

Yorumlar

Yorum Gönder

Bu blogdaki popüler yayınlar

22.06.2020 - 26.06.2020 arası işler

Asp.net RestSharp ile data post etmek

List Box Item içindeki elemanları aşağı veya yukarı taşımak