using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Text;
using System.IO.Compression;
namespace WindowsFormsApp7
{
static class Program
{
///
<summary>
/// The main entry point for the application.
/// </summary>
///
private static string directoryPath = @"c:\temp";
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
SqlConnection sqlCon = new SqlConnection("Data Source=IP;Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD;MultipleActiveResultSets=True;");
sqlCon.Open();
string sql = "SELECT sc.name + '.' + ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN(1,0) GROUP BY sc.name,ta.name having SUM(pa.rows) > 0 and sc.name +'.'+ ta.name IN ( 'dbo.EFAT_ZARF' ) ORDER BY SUM(pa.rows) DESC";
SqlCommand com = new SqlCommand(sql, sqlCon);
SqlDataReader read = com.ExecuteReader();
string dirname = "C:/SQL_BACKUP/" + (DateTime.Now).ToString("yyyy-MM-dd");
CreateIfMissing(dirname);
dirname += "/" + (DateTime.Now).ToString("HH");
CreateIfMissing(dirname);
string rar_dir = @"" + dirname;
string rar_path = @"" + dirname + ".zip";
List<string> filess = new List<string>();
while (read.Read())
{
string fileName = dirname + "/" + read.GetValue(0) + ".csv";
filess.Add(fileName);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT * FROM " + read.GetValue(0) + " with (NOLOCK)";
sqlCmd.Connection = sqlCon;
sqlCmd.Dispose();
using (var CommandText = new SqlCommand("SELECT * FROM " + read.GetValue(0) + " with (NOLOCK)"))
using (var reader = sqlCmd.ExecuteReader())
using (StreamWriter outFile = new StreamWriter(File.Open(fileName, FileMode.Create), Encoding.UTF8))
{
string[] columnNames = GetColumnNames(reader).ToArray();
int numFields = columnNames.Length;
outFile.WriteLine(string.Join(",", columnNames));
if (reader.HasRows)
{
while (reader.Read())
{
string[] columnValues =
Enumerable.Range(0, numFields)
.Select(i => get_string( reader.GetValue(i) ))
.Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
.ToArray();
outFile.WriteLine(string.Join(",", columnValues));
}
}
}
}
sqlCon.Close();
directoryPath = rar_dir;
DirectoryInfo directorySelected = new DirectoryInfo(directoryPath);
Compress(directorySelected);
foreach (var item in filess)
{
if (File.Exists(item))
File.Delete(item);
}
}
public static string get_string( object ss )
{
if (!(ss.ToString().Length > 2))
return ss.ToString();
if (ss.ToString().Substring(0, 2) == "0x")
return System.Text.Encoding.UTF8.GetString((Byte[])ss);
return ss.ToString();
}
public static string Base64Encode(string plainText)
{
var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText);
return System.Convert.ToBase64String(plainTextBytes);
}
static void CreateIfMissing(string path)
{
bool folderExists = Directory.Exists(path);
if (!folderExists)
Directory.CreateDirectory(path);
}
static IEnumerable<string> GetColumnNames(IDataReader reader)
{
foreach (DataRow row in reader.GetSchemaTable().Rows)
{
yield return (string)row["ColumnName"];
}
}
public static void Compress(DirectoryInfo directorySelected)
{
foreach (FileInfo fileToCompress in directorySelected.GetFiles())
{
using (FileStream originalFileStream = fileToCompress.OpenRead())
{
if ((File.GetAttributes(fileToCompress.FullName) & FileAttributes.Hidden) != FileAttributes.Hidden & fileToCompress.Extension != ".gz")
{
using (FileStream compressedFileStream = File.Create(fileToCompress.FullName + ".gz"))
{
using (GZipStream compressionStream = new GZipStream(compressedFileStream,CompressionMode.Compress))
{
originalFileStream.CopyTo(compressionStream);
}
}
}
}
}
}
}
}
