API tools faq
paste
Advertisement
Guest User

O feitais e um troxa

a guest
Jun 22nd, 2018
90
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 43.05 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. using System.Drawing;
  7. using System.IO;
  8. using System.Linq;
  9. using System.Text;
  10. using System.Threading.Tasks;
  11. using System.Windows.Forms;
  12. using System.Xml;
  13. using System.Threading;
  14.  
  15. namespace ExcelExpImp
  16. {
  17. public partial class Form1 : Form
  18. {
  19. //Variaveis globais
  20. string servidor;
  21. string db;
  22. string username;
  23. string password;
  24. string connectionString;
  25. bool IsConnected = false;
  26. int n;
  27. XmlNode dbNode;
  28.  
  29.  
  30. //Inicializacão de Controlos
  31. DataGridView dgvTables = new DataGridView();
  32. ListBox lbExport = new ListBox();
  33. Label lblExpTitle = new Label();
  34. Label lblImpTitle = new Label();
  35. Button btnImport = new Button();
  36. Button btnDelete = new Button();
  37. Button btnAdd = new Button();
  38. Button btnExport = new Button();
  39. Button btnUpdate = new Button();
  40. Button btnExportData = new Button();
  41.  
  42. public Form1()
  43. {
  44. InitializeComponent();
  45. }
  46.  
  47. private void btnClose_Click(object sender, EventArgs e)
  48. {
  49. this.Close();
  50. }
  51.  
  52. private void btnMinimize_Click(object sender, EventArgs e)
  53. {
  54. this.WindowState = FormWindowState.Minimized;
  55. }
  56.  
  57. public void SplashStart()
  58. {
  59. Application.Run(new Splashcs());
  60. }
  61.  
  62. private void btnConnect_Click(object sender, EventArgs e)
  63. {
  64. Thread t = new Thread(new ThreadStart(SplashStart));
  65. t.Start();
  66. Thread.Sleep(3000);
  67.  
  68. servidor = txtServer.Text;
  69. db = txtDb.Text;
  70. username = txtUser.Text;
  71. password = txtPass.Text;
  72.  
  73. File.Delete("conf.xml");
  74.  
  75. XmlDocument xmlDoc = new XmlDocument();
  76. XmlNode rootNode = xmlDoc.CreateElement("Configuracao");
  77. xmlDoc.AppendChild(rootNode);
  78.  
  79. XmlAttribute servAtt = xmlDoc.CreateAttribute("Servidor");
  80. servAtt.Value = servidor;
  81.  
  82. XmlAttribute dbAtt = xmlDoc.CreateAttribute("Base_De_Dados");
  83. dbAtt.Value = db;
  84.  
  85. XmlAttribute userAtt = xmlDoc.CreateAttribute("Utilizador");
  86. userAtt.Value = username;
  87.  
  88. XmlAttribute passAtt = xmlDoc.CreateAttribute("palavra-passe");
  89. passAtt.Value = password;
  90.  
  91. rootNode.Attributes.Append(servAtt);
  92. rootNode.Attributes.Append(dbAtt);
  93. rootNode.Attributes.Append(userAtt);
  94. rootNode.Attributes.Append(passAtt);
  95.  
  96. xmlDoc.Save("conf.xml");
  97.  
  98. IsConnected = true;
  99. t.Abort();
  100. MessageBox.Show("Conectado com sucesso");
  101. }
  102.  
  103. private void Form1_Load(object sender, EventArgs e)
  104. {
  105. InitialVisibleControls();
  106. LoadConf();
  107. CreateControls();
  108. }
  109.  
  110.  
  111. private void GetTables()
  112. {
  113. SetConnectionString();
  114.  
  115. SqlConnection cnn = new SqlConnection(connectionString);
  116.  
  117. cnn.Open();
  118.  
  119. List<string> result = new List<string>();
  120. SqlCommand cmd = new SqlCommand("SELECT name FROM sys.Tables", cnn);
  121. SqlDataReader reader = cmd.ExecuteReader();
  122. while (reader.Read())
  123. result.Add(reader["name"].ToString());
  124.  
  125. dgvTables.Rows.Clear();
  126. n = 0;
  127. foreach (string tableName in result)
  128. {
  129. n++;
  130. dgvTables.Rows.Add(n, tableName);
  131. }
  132.  
  133. cnn.Close();
  134. }
  135.  
  136. private void InitialVisibleControls()
  137. {
  138. dgvTables.Visible = false;
  139. lbExport.Visible = false;
  140. btnDelete.Visible = false;
  141. btnAdd.Visible = false;
  142. btnExport.Visible = false;
  143. lblExpTitle.Visible = false;
  144. btnImport.Visible = false;
  145. lblImpTitle.Visible = false;
  146. btnExportData.Visible = false;
  147. btnUpdate.Visible = false;
  148. }
  149.  
  150. private void CreateControls()
  151. {
  152. //propriedades dgvTables
  153. dgvTables.Columns.Add("numTable","Nr. Tabela");
  154. dgvTables.Columns.Add("name","Nome da Tabela");
  155. dgvTables.Columns[0].Width = 35;
  156. dgvTables.Columns[1].Width = 397;
  157. dgvTables.Location = new Point(175,64);
  158. dgvTables.Size = new Size(475,280);
  159.  
  160. //Propriedade lbExport
  161. lbExport.Text = "";
  162. lbExport.Location = new Point(660,65);
  163. lbExport.Size = new Size(175,230);
  164.  
  165. //Propriedades btnDelete
  166. btnDelete.Text = "Eliminar";
  167. btnDelete.FlatAppearance.BorderSize = 0;
  168. btnDelete.FlatStyle = FlatStyle.Flat;
  169. btnDelete.Location = new Point(660, 300);
  170. btnDelete.Size = new Size(175,40);
  171. btnDelete.BackColor = Color.FromArgb(5, 172, 201);
  172. btnDelete.Font = new Font("Verdana",12);
  173. btnDelete.ForeColor = Color.White;
  174. btnDelete.Click += BtnDelete_Click;
  175.  
  176. //Propriedades btnAdd
  177. btnAdd.Text = "Adicionar";
  178. btnAdd.FlatAppearance.BorderSize = 0;
  179. btnAdd.FlatStyle = FlatStyle.Flat;
  180. btnAdd.Location = new Point(474, 345);
  181. btnAdd.Size = new Size(175, 40);
  182. btnAdd.BackColor = Color.FromArgb(5, 172, 201);
  183. btnAdd.Font = new Font("Verdana", 12);
  184. btnAdd.ForeColor = Color.White;
  185. btnAdd.Click += BtnAdd_Click;
  186.  
  187. //Propriedades btnExport
  188. btnExport.Text = "Exportar Template";
  189. btnExport.FlatAppearance.BorderSize = 0;
  190. btnExport.FlatStyle = FlatStyle.Flat;
  191. btnExport.Location = new Point(660, 345);
  192. btnExport.Size = new Size(175, 40);
  193. btnExport.BackColor = Color.FromArgb(5, 172, 201);
  194. btnExport.Font = new Font("Verdana", 12);
  195. btnExport.ForeColor = Color.White;
  196. btnExport.Click += BtnExport_Click;
  197.  
  198. //Propriedades btnExportData
  199. btnExportData.Text = "Exportar Dados";
  200. btnExportData.FlatAppearance.BorderSize = 0;
  201. btnExportData.FlatStyle = FlatStyle.Flat;
  202. btnExportData.Location = new Point(290, 345);
  203. btnExportData.Size = new Size(175, 40);
  204. btnExportData.BackColor = Color.FromArgb(5, 172, 201);
  205. btnExportData.Font = new Font("Verdana", 12);
  206. btnExportData.ForeColor = Color.White;
  207. btnExportData.Click += BtnExportData_Click;
  208.  
  209. //Propriedade lblExpTitle
  210. lblExpTitle.Text = "Exportar";
  211. lblExpTitle.Font = new Font("Verdana", 18);
  212. lblExpTitle.Location = new Point(440,15);
  213. lblExpTitle.Size = new Size(200, 40);
  214. lblExpTitle.ForeColor = Color.White;
  215.  
  216. //Propriedade lblImpTitle
  217. lblImpTitle.Text = "Importar";
  218. lblImpTitle.Font = new Font("Verdana", 18);
  219. lblImpTitle.Location = new Point(440, 15);
  220. lblImpTitle.Size = new Size(200, 40);
  221. lblImpTitle.ForeColor = Color.White;
  222.  
  223. //Propriedades btnImport
  224. btnImport.Text = "Importar";
  225. btnImport.FlatAppearance.BorderSize = 0;
  226. btnImport.FlatStyle = FlatStyle.Flat;
  227. btnImport.Location = new Point(420, 180);
  228. btnImport.Size = new Size(175, 40);
  229. btnImport.BackColor = Color.FromArgb(5, 172, 201);
  230. btnImport.Font = new Font("Verdana", 12);
  231. btnImport.ForeColor = Color.White;
  232. btnImport.Click += BtnImport_Click;
  233.  
  234. //Propriedades btnUpdate
  235. btnUpdate.Text = "Update";
  236. btnUpdate.FlatAppearance.BorderSize = 0;
  237. btnUpdate.FlatStyle = FlatStyle.Flat;
  238. btnUpdate.Location = new Point(176, 345);
  239. btnUpdate.Size = new Size(105, 40);
  240. btnUpdate.BackColor = Color.FromArgb(5, 172, 201);
  241. btnUpdate.Font = new Font("Verdana", 12);
  242. btnUpdate.ForeColor = Color.White;
  243. btnUpdate.Click += BtnUpdate_Click;
  244.  
  245.  
  246. //adicionar os controlos ao painel
  247. panelMiddle.Controls.Add(btnUpdate);
  248. panelMiddle.Controls.Add(btnExportData);
  249. panelMiddle.Controls.Add(btnImport);
  250. panelMiddle.Controls.Add(lblImpTitle);
  251. panelMiddle.Controls.Add(lblExpTitle);
  252. panelMiddle.Controls.Add(btnExport);
  253. panelMiddle.Controls.Add(btnAdd);
  254. panelMiddle.Controls.Add(btnDelete);
  255. panelMiddle.Controls.Add(lbExport);
  256. panelMiddle.Controls.Add(dgvTables);
  257. }
  258.  
  259. private void BtnUpdate_Click(object sender, EventArgs e)
  260. {
  261. OFD.ShowDialog();
  262. string path = OFD.FileName;
  263. bool verif = false;
  264. Thread t = new Thread(new ThreadStart(SplashStart));
  265. t.Start();
  266. Thread.Sleep(500);
  267. LoadConf();
  268. SetConnectionString();
  269.  
  270.  
  271. Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
  272. Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(path);
  273. SqlConnection cnn = new SqlConnection(connectionString);
  274. cnn.Open();
  275. foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
  276. {
  277. string tablename = ws.Cells[1, 1].Value.ToString();
  278. SqlCommand sqlPrimaryKey = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND TABLE_NAME = '" + tablename + "'",cnn);
  279. string primaryKey = sqlPrimaryKey.ExecuteScalar().ToString();
  280. Microsoft.Office.Interop.Excel.Range aRange = ws.UsedRange;
  281. List<string> headers = new List<string>();
  282. string[,] data = new string[1000, 1000];
  283. int count = 0;
  284. int indexPrimaryKey = 0;
  285.  
  286. for (int i = 0; i < ws.UsedRange.Columns.Count; i++)
  287. {
  288. headers.Add(ws.Cells[2, i + 1].Value.ToString());
  289. if (ws.Cells[2, i + 1].Value.ToString() == primaryKey)
  290. {
  291. indexPrimaryKey = i;
  292. }
  293. count++;
  294. }
  295.  
  296. for (int i = 0; i < count; i++)
  297. {
  298. for (int j = 1; j <= aRange.Rows.Count - 2; j++)
  299. {
  300. SqlCommand sqlGetDataType = new SqlCommand("select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '" + tablename + "' and COLUMN_NAME = '" + ws.Cells[2, i + 1].Value.ToString() + "'", cnn);
  301. string DataType = sqlGetDataType.ExecuteScalar().ToString();
  302. if (ws.Cells[j + 2, i + 1].Value != null)
  303. {
  304. data[j - 1, i] = ws.Cells[j + 2, i + 1].Value.ToString();
  305. }
  306. else
  307. {
  308. if (DataType == "int" || DataType == "tinyint" || DataType == "decimal" || DataType == "numeric")
  309. {
  310. data[j - 1, i] = "0";
  311. }
  312. else if (DataType == "nvarchar" || DataType == "varchar")
  313. {
  314. data[j - 1, i] = " ";
  315. }
  316. else if (DataType == "date")
  317. {
  318. data[j - 1, i] = "01/01/1222";
  319. }
  320. else if (DataType == "datetime")
  321. {
  322. data[j - 1, i] = "2001-01-01 00:00:00.000";
  323. }
  324. else if (DataType == "uniqueidentifier")
  325. {
  326. data[j - 1, i] = "00000000-0000-0000-0000-000000000000";
  327. }
  328. else
  329. {
  330. data[j - 1, i] = "0";
  331. }
  332.  
  333. }
  334. }
  335. }
  336.  
  337. DataTable dt = new DataTable();
  338. SqlDataAdapter sda = new SqlDataAdapter("select [" + primaryKey + "] FROM [" + tablename + "]",cnn);
  339. sda.Fill(dt);
  340. int rows = dt.Rows.Count;
  341.  
  342. for (int i = 1; i <= aRange.Rows.Count - 2; i++)
  343. {
  344. for (int y = 0; y < rows; y++)
  345. {
  346. try
  347. {
  348. if (data[i - 1, indexPrimaryKey] == dt.Rows[y].Field<Int32>(0).ToString())
  349. {
  350. string updateCommand = "Update " + tablename + " Set ";
  351. for (int j = 0; j < count; j++)
  352. {
  353. updateCommand += headers[j] + "='" + data[i - 1, j] + "',";
  354. }
  355. updateCommand = updateCommand.Remove(updateCommand.Length - 1);
  356. updateCommand += " where " + headers[indexPrimaryKey] + "='" + data[i - 1, indexPrimaryKey] + "'";
  357. SqlCommand sqlUpdate = new SqlCommand(updateCommand, cnn);
  358. sqlUpdate.ExecuteNonQuery();
  359. }
  360. }
  361. catch (Exception)
  362. {
  363. if (data[i - 1, indexPrimaryKey] == dt.Rows[y].Field<string>(0).ToString())
  364. {
  365. string updateCommand = "Update " + tablename + " Set ";
  366. for (int j = 0; j < count; j++)
  367. {
  368. updateCommand += headers[j] + "='" + data[i - 1, j] + "',";
  369. }
  370. updateCommand = updateCommand.Remove(updateCommand.Length - 1);
  371. updateCommand += " where " + headers[indexPrimaryKey] + "='" + data[i - 1, indexPrimaryKey] + "'";
  372. SqlCommand sqlUpdate = new SqlCommand(updateCommand, cnn);
  373. sqlUpdate.ExecuteNonQuery();
  374. }
  375. }
  376. }
  377. }
  378. }
  379. t.Abort();
  380. app.Quit();
  381. cnn.Close();
  382. if (verif == false)
  383. {
  384. MessageBox.Show("Update com sucesso");
  385. }
  386. else
  387. {
  388. MessageBox.Show("Update sem sucesso");
  389. }
  390. }
  391.  
  392. private void BtnExportData_Click(object sender, EventArgs e)
  393. {
  394. if (lbExport.Items.Count != 0)
  395. {
  396. Thread t = new Thread(new ThreadStart(SplashStart));
  397. t.Start();
  398. Thread.Sleep(8000);
  399. int count = 0;
  400. int repeatedNames = 0;
  401. List<String> nomeTabela = new List<string>();
  402.  
  403. foreach (string nome in lbExport.Items)
  404. {
  405. nomeTabela.Add(nome);
  406. }
  407.  
  408. Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application
  409. {
  410. Visible = false
  411. };
  412. SqlConnection cnn = new SqlConnection(connectionString);
  413.  
  414. Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
  415. object MissingValue = System.Reflection.Missing.Value;
  416. foreach (string tableName in nomeTabela)
  417. {
  418. string excelName = null;
  419. int numCar = 0;
  420. List<string> headers = new List<string>();
  421.  
  422. SqlDataAdapter sda = new SqlDataAdapter("Select * FROM [" + tableName + "]", cnn);
  423. DataTable dt = new DataTable();
  424. dt.Clear();
  425. sda.Fill(dt);
  426.  
  427.  
  428. Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
  429. if (count != lbExport.Items.Count - 1)
  430. {
  431. wb.Worksheets.Add(ws);
  432. }
  433.  
  434. ws.Cells[1][1] = tableName;
  435. int countNumVezes = 0;
  436.  
  437. for (int i = 0; i < dt.Columns.Count; i++)
  438. {
  439. cnn.Open();
  440. SqlCommand sqlcom = new SqlCommand("select is_identity from sys.columns where OBJECT_ID = OBJECT_ID('" + tableName + "') and name = '" + dt.Columns[i].ColumnName + "';", cnn);
  441. object isIncremental = sqlcom.ExecuteScalar();
  442. if (!(Convert.ToBoolean(isIncremental)) && (dt.Columns[i].ColumnName != "timestamp"))
  443. {
  444. ws.Cells[2, i + 1 - countNumVezes] = dt.Columns[i].ColumnName;
  445. headers.Add(dt.Columns[i].ColumnName);
  446. }
  447. else
  448. {
  449. countNumVezes++;
  450. }
  451. cnn.Close();
  452. }
  453.  
  454. countNumVezes = 0;
  455. for (int i = 1; i <= dt.Rows.Count; i++)
  456. {
  457. countNumVezes = 0;
  458. for (int j = 1; j <= dt.Columns.Count; j++)
  459. {
  460. cnn.Open();
  461. SqlCommand sqlcom = new SqlCommand("select is_identity from sys.columns where OBJECT_ID = OBJECT_ID('" + tableName + "') and name = '" + dt.Columns[j-1].ColumnName + "';", cnn);
  462. object isIncremental = sqlcom.ExecuteScalar();
  463. SqlCommand sqlGetDataType = new SqlCommand("select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '" + tableName + "' and COLUMN_NAME = '" + dt.Columns[j-1].ColumnName + "'", cnn);
  464. string DataType = sqlGetDataType.ExecuteScalar().ToString();
  465. if (!(Convert.ToBoolean(isIncremental)) && (dt.Columns[j-1].ColumnName != "timestamp"))
  466. {
  467. if (DataType != "timestamp")
  468. {
  469. if (DataType == "date" || DataType == "datetime")
  470. {
  471. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<DateTime>(j - 1);
  472. }
  473. else if (DataType == "int" || DataType == "tinyint")
  474. {
  475. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<Int32>(j - 1);
  476. }
  477. else if(DataType == "decimal" || DataType == "float" || DataType == "double")
  478. {
  479. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<decimal>(j - 1);
  480. }
  481. else if (DataType == "uniqueidentifier")
  482. {
  483.  
  484. }
  485. else if(DataType == "nvarchar")
  486. {
  487. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<string>(j - 1);
  488. }
  489. else if(DataType == "datetime" || DataType == "smalldatetime" || DataType == "date")
  490. {
  491. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<DateTime>(j - 1);
  492. }
  493. else
  494. {
  495. ws.Cells[i + 2, j - countNumVezes] = dt.Rows[i - 1].Field<string>(j - 1);
  496. }
  497. }
  498. }
  499. else
  500. {
  501. countNumVezes++;
  502. }
  503. cnn.Close();
  504. }
  505. }
  506.  
  507.  
  508. ws.Columns.AutoFit();
  509.  
  510. if (tableName.ToLower().Contains("history") == true)
  511. {
  512. tableName.Replace("history", "xxxxxxx");
  513. }
  514.  
  515. foreach (char letra in tableName.ToCharArray())
  516. {
  517. if (letra == '\\' || letra == '/' || letra == '?' || letra == '*')
  518. {
  519. excelName += "_";
  520. }
  521. else if (numCar == 30)
  522. {
  523. goto KeepGoing;
  524. }
  525. else
  526. {
  527. excelName += letra;
  528. }
  529.  
  530. numCar++;
  531. }
  532.  
  533.  
  534.  
  535. KeepGoing:
  536. try
  537. {
  538. //atribuição de um nome á worksheet
  539. ws.Name = excelName;
  540. }//Se o nome já foi atribuido ele acrescenta-lhe um numero
  541. catch (System.Runtime.InteropServices.COMException)
  542. {
  543. int tamanho = excelName.Length;
  544. ws.Name = excelName.Insert(tamanho, repeatedNames.ToString());
  545. repeatedNames++;
  546. }
  547.  
  548.  
  549. if (count == 0)
  550. {
  551. //abre o dialogo e guarda o excel no caminho indicado
  552. SFD.ShowDialog();
  553. wb.SaveAs(SFD.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, MissingValue, MissingValue, MissingValue, MissingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
  554. }
  555.  
  556. //Conta o nº de tabelas
  557. count++;
  558. }
  559.  
  560. //fechar a coneccão com a base de dados
  561. cnn.Close();
  562. //fecha o workbook
  563. wb.Close(true, MissingValue, MissingValue);
  564. //fecha a aplicação excel
  565. app.Quit();
  566. //Guarda no excel as ulltimas tabelas exportadas
  567. SaveToXML(nomeTabela);
  568. t.Abort();
  569. MessageBox.Show("Exportado com Sucesso");
  570. }
  571. else
  572. {
  573. MessageBox.Show("Não tem tabelas para exportar");
  574. }
  575. }
  576.  
  577. private void BtnImport_Click(object sender, EventArgs e)
  578. {
  579. OFD.ShowDialog();
  580. string path = OFD.FileName;
  581. bool verif = false;
  582. Thread t = new Thread(new ThreadStart(SplashStart));
  583. t.Start();
  584. Thread.Sleep(500);
  585. LoadConf();
  586. SetConnectionString();
  587.  
  588.  
  589. Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
  590. Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(path);
  591. SqlConnection cnn = new SqlConnection(connectionString);
  592. cnn.Open();
  593. foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)
  594. {
  595. string tablename = ws.Cells[1, 1].Value.ToString();
  596. Microsoft.Office.Interop.Excel.Range aRange = ws.UsedRange;
  597. List<string> headers = new List<string>();
  598. string[,] data = new string[10000, 10000];
  599. int count = 0;
  600.  
  601. for (int i = 0; i < ws.UsedRange.Columns.Count; i++)
  602. {
  603. headers.Add(ws.Cells[2, i + 1].Value.ToString());
  604. count++;
  605. }
  606.  
  607. for (int i = 0; i < count; i++)
  608. {
  609. for (int j = 1; j <= aRange.Rows.Count - 1; j++)
  610. {
  611. SqlCommand sqlGetDataType = new SqlCommand("select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '" + tablename + "' and COLUMN_NAME = '" + ws.Cells[2, i + 1].Value.ToString() + "'", cnn);
  612. string DataType = sqlGetDataType.ExecuteScalar().ToString();
  613. if (ws.Cells[j + 2, i + 1].Value != null)
  614. {
  615. data[j - 1, i] = ws.Cells[j + 2, i + 1].Value.ToString();
  616.  
  617. if((!data[j-1,i].Contains("/") || !data[j - 1, i].Contains("-")) && (DataType == "datetime"))
  618. {
  619. data[j - 1, i] = "2018-01-01 00:00:00.000";
  620. }
  621.  
  622. if(data[j - 1, i].ToUpper() == "NULL" && DataType == "datetime")
  623. {
  624. data[j - 1, i] = "2018-01-01 00:00:00.000";
  625. }
  626.  
  627. if(data[j-1,i].ToUpper() == "NULL" && DataType == "date")
  628. {
  629. data[j - 1, i] = "01/01/2018";
  630. }
  631.  
  632. if(data[j - 1, i].ToUpper() == "NULL" && DataType == "smalldatetime")
  633. {
  634. data[j - 1, i] = "2018-01-01 00:00:00";
  635. }
  636.  
  637. if(data[j-1,i].Contains("Ox") && (DataType == "int" || DataType == "tinyint") || DataType == "decimal")
  638. {
  639. data[j - 1, i] = "0";
  640. }
  641. }
  642. else
  643. {
  644. if (DataType == "int" || DataType == "tinyint" || DataType == "decimal" || DataType == "numeric")
  645. {
  646. data[j - 1, i] = "0";
  647. }
  648. else if (DataType == "nvarchar" || DataType == "varchar")
  649. {
  650. data[j - 1, i] = " ";
  651. }
  652. else if (DataType == "date")
  653. {
  654. data[j - 1, i] = "01/01/2018";
  655. }
  656. else if (DataType == "datetime")
  657. {
  658. data[j - 1, i] = "2018-01-01 00:00:00.000";
  659. }
  660. else if(DataType == "smalldatetime")
  661. {
  662. data[j - 1, i] = "2018-01-01 00:00:00";
  663. }
  664. else if (DataType == "uniqueidentifier")
  665. {
  666. data[j - 1, i] = "00000000-0000-0000-0000-000000000000";
  667. }
  668. else
  669. {
  670. data[j - 1, i] = "0";
  671. }
  672.  
  673. }
  674. }
  675. }
  676. for (int j = 1; j <= aRange.Rows.Count - 1; j++)
  677. {
  678. string insertCom = "insert into [" + tablename + "] ( ";
  679. string ValuesInsert = " Values(";
  680.  
  681. for (int i = 0; i < count; i++)
  682. {
  683. insertCom += "[" + headers[i] + "]" + ",";
  684. ValuesInsert += "'" + data[j - 1, i] + "'" + ",";
  685. }
  686.  
  687. insertCom = insertCom.Remove(insertCom.Length - 1);
  688. ValuesInsert = ValuesInsert.Remove(ValuesInsert.Length - 1);
  689. insertCom += ")";
  690. ValuesInsert += ")";
  691. insertCom += ValuesInsert;
  692. SqlCommand sqlcom = new SqlCommand(insertCom, cnn);
  693. try
  694. {
  695. sqlcom.ExecuteNonQuery();
  696. }
  697. catch (SqlException ex)
  698. {
  699. t.Abort();
  700. if (ex.Message.Contains("Violation of PRIMARY KEY constraint"))
  701. {
  702. MessageBox.Show("Existe valores repetidos no excel ou na base de dados");
  703. }
  704. else
  705. {
  706. MessageBox.Show("Não foi possivel Converter valores\nLinha " + j.ToString(), "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
  707. }
  708. verif = true;
  709. }
  710. }
  711. }
  712. t.Abort();
  713. app.Quit();
  714. cnn.Close();
  715. if (verif == false)
  716. {
  717. MessageBox.Show("Importado com sucesso");
  718. }
  719. else
  720. {
  721. MessageBox.Show("Nem tudo foi importado com sucesso");
  722. }
  723. }
  724.  
  725. private void SetConnectionString()
  726. {
  727. connectionString = null;
  728. if (username != "" && password != "")
  729. {
  730. connectionString = @"Data Source=" + servidor + ";" + " Initial Catalog="
  731. + db + "; User ID=" + username + "; Password=" + password;
  732. }
  733. else
  734. {
  735. connectionString = @"Data Source=" + servidor + " ;" + " Initial Catalog="
  736. + db;
  737. }
  738.  
  739. if(cboAutentic.SelectedIndex == 0)
  740. {
  741. connectionString += "; Trusted_Connection=True";
  742. }
  743. }
  744.  
  745. private void BtnExport_Click(object sender, EventArgs e)
  746. {
  747. if (lbExport.Items.Count != 0) {
  748. Thread t = new Thread(new ThreadStart(SplashStart));
  749. t.Start();
  750. Thread.Sleep(8000);
  751. int count = 0;
  752. int repeatedNames = 0;
  753. List<String> nomeTabela = new List<string>();
  754.  
  755. foreach (string nome in lbExport.Items)
  756. {
  757. nomeTabela.Add(nome);
  758. }
  759.  
  760. Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application
  761. {
  762. Visible = false
  763. };
  764. SqlConnection cnn = new SqlConnection(connectionString);
  765.  
  766. Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
  767. object MissingValue = System.Reflection.Missing.Value;
  768. foreach (string tableName in nomeTabela)
  769. {
  770. string excelName = null;
  771. int numCar = 0;
  772. List<string> headers = new List<string>();
  773.  
  774. SqlDataAdapter sda = new SqlDataAdapter("Select * FROM [" + tableName + "]", cnn);
  775. DataTable dt = new DataTable();
  776. dt.Clear();
  777. sda.Fill(dt);
  778.  
  779.  
  780. Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
  781. if (count != lbExport.Items.Count - 1)
  782. {
  783. wb.Worksheets.Add(ws);
  784. }
  785.  
  786. ws.Cells[1][1] = tableName;
  787. int countNumVezes = 0;
  788.  
  789. for (int i = 0; i < dt.Columns.Count; i++)
  790. {
  791. cnn.Open();
  792. SqlCommand sqlcom = new SqlCommand("select is_identity from sys.columns where OBJECT_ID = OBJECT_ID('" + tableName + "') and name = '" + dt.Columns[i].ColumnName + "';", cnn);
  793. object isIncremental = sqlcom.ExecuteScalar();
  794. if (!(Convert.ToBoolean(isIncremental)) && (dt.Columns[i].ColumnName != "timestamp"))
  795. {
  796. ws.Cells[2, i + 1 - countNumVezes] = dt.Columns[i].ColumnName;
  797. headers.Add(dt.Columns[i].ColumnName);
  798. }
  799. else
  800. {
  801. countNumVezes++;
  802. }
  803. cnn.Close();
  804. }
  805.  
  806. ws.Columns.AutoFit();
  807.  
  808. if (tableName.ToLower().Contains("history") == true)
  809. {
  810. tableName.Replace("history", "xxxxxxx");
  811. }
  812.  
  813. foreach (char letra in tableName.ToCharArray())
  814. {
  815. if (letra == '\\' || letra == '/' || letra == '?' || letra == '*')
  816. {
  817. excelName += "_";
  818. }
  819. else if (numCar == 30)
  820. {
  821. goto KeepGoing;
  822. }
  823. else
  824. {
  825. excelName += letra;
  826. }
  827.  
  828. numCar++;
  829. }
  830.  
  831.  
  832.  
  833. KeepGoing:
  834. try
  835. {
  836. //atribuição de um nome á worksheet
  837. ws.Name = excelName;
  838. }//Se o nome já foi atribuido ele acrescenta-lhe um numero
  839. catch (System.Runtime.InteropServices.COMException)
  840. {
  841. int tamanho = excelName.Length;
  842. ws.Name = excelName.Insert(tamanho, repeatedNames.ToString());
  843. repeatedNames++;
  844. }
  845.  
  846.  
  847. if (count == 0)
  848. {
  849. //abre o dialogo e guarda o excel no caminho indicado
  850. SFD.ShowDialog();
  851. wb.SaveAs(SFD.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, MissingValue, MissingValue, MissingValue, MissingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
  852. }
  853.  
  854. //Conta o nº de tabelas
  855. count++;
  856. }
  857.  
  858. //fechar a coneccão com a base de dados
  859. cnn.Close();
  860. //fecha o workbook
  861. wb.Close(true, MissingValue, MissingValue);
  862. //fecha a aplicação excel
  863. app.Quit();
  864. //Guarda no excel as ulltimas tabelas exportadas
  865. SaveToXML(nomeTabela);
  866. t.Abort();
  867. MessageBox.Show("Exportado com Sucesso");
  868. }
  869. else
  870. {
  871. MessageBox.Show("Não tem tabelas para exportar");
  872. }
  873. }
  874.  
  875. private void SaveToXML(List<string> TableName)
  876. {
  877. bool ExistFile = File.Exists("LastTablesExported.xml");
  878. bool TablesExpFromDb = false;
  879. XmlDocument xmlDoc = new XmlDocument();
  880. XmlElement rootNode;
  881.  
  882.  
  883. if (ExistFile == true)
  884. {
  885. xmlDoc.Load("LastTablesExported.xml");
  886. rootNode = xmlDoc.DocumentElement;
  887.  
  888. foreach (XmlNode node in xmlDoc.SelectNodes("//DataBase"))
  889. {
  890. if (node.Attributes[0].Value.ToString() == db)
  891. {
  892. TablesExpFromDb = true;
  893. goto KeepGoing;
  894. }
  895. }
  896. }
  897. else
  898. {
  899. rootNode = xmlDoc.CreateElement("Tables");
  900. xmlDoc.AppendChild(rootNode);
  901. }
  902.  
  903. KeepGoing:
  904. if (TablesExpFromDb == false)
  905. {
  906. dbNode = xmlDoc.CreateElement("DataBase");
  907. XmlAttribute dataBase = xmlDoc.CreateAttribute("db");
  908. dataBase.Value = db;
  909. rootNode.AppendChild(dbNode);
  910. dbNode.Attributes.Append(dataBase);
  911.  
  912. foreach (string nomeTabela in TableName)
  913. {
  914. XmlNode Table = xmlDoc.CreateElement("Table");
  915. XmlAttribute ExportedTables = xmlDoc.CreateAttribute("nome");
  916. ExportedTables.Value = nomeTabela;
  917.  
  918. Table.Attributes.Append(ExportedTables);
  919. dbNode.AppendChild(Table);
  920. }
  921. }
  922. else
  923. {
  924. foreach (XmlNode node in xmlDoc.SelectNodes("//DataBase"))
  925. {
  926. if (node.Attributes[0].Value.ToString() == db)
  927. {
  928. node.RemoveAll();
  929.  
  930. XmlAttribute dataBase = xmlDoc.CreateAttribute("db");
  931. dataBase.Value = db;
  932. node.Attributes.Append(dataBase);
  933.  
  934. foreach (string nomeTabela in TableName)
  935. {
  936. XmlNode Table = xmlDoc.CreateElement("Table");
  937. XmlAttribute ExportedTables = xmlDoc.CreateAttribute("nome");
  938. ExportedTables.Value = nomeTabela;
  939.  
  940. Table.Attributes.Append(ExportedTables);
  941. node.AppendChild(Table);
  942. }
  943. }
  944.  
  945. }
  946. }
  947.  
  948.  
  949. xmlDoc.Save("LastTablesExported.xml");
  950. }
  951.  
  952. private void BtnDelete_Click(object sender, EventArgs e)
  953. {
  954. lbExport.Items.Remove(lbExport.SelectedItem);
  955. }
  956.  
  957. private void BtnAdd_Click(object sender, EventArgs e)
  958. {
  959. int selectedCells = dgvTables.SelectedCells.Count;
  960. for (int i = 0; i < selectedCells; i++)
  961. {
  962. lbExport.Items.Add(dgvTables.SelectedCells[i].Value.ToString());
  963. }
  964. }
  965.  
  966. private void LoadConf()
  967. {
  968.  
  969. try
  970. {
  971. XmlDocument FXML = new XmlDocument();
  972. FXML.Load("conf.xml");
  973.  
  974. servidor = null;
  975. db = null;
  976. username = null;
  977. password = null;
  978.  
  979.  
  980. foreach (XmlNode node in FXML.SelectNodes("Configuracao"))
  981. {
  982. servidor = node.Attributes[0].Value.ToString();
  983. db = node.Attributes[1].Value.ToString();
  984. username = node.Attributes[2].Value.ToString();
  985. password = node.Attributes[3].Value.ToString();
  986. }
  987.  
  988. txtServer.Text = servidor;
  989. txtDb.Text = db;
  990. txtUser.Text = username;
  991. txtPass.Text = password;
  992. }
  993. catch (Exception)
  994. {
  995.  
  996. }
  997. }
  998.  
  999. private void btnConf_Click(object sender, EventArgs e)
  1000. {
  1001. if (IsConnected == true)
  1002. {
  1003. dgvTables.Visible = false;
  1004. lbExport.Visible = false;
  1005. btnDelete.Visible = false;
  1006. btnAdd.Visible = false;
  1007. btnExport.Visible = false;
  1008. lblExpTitle.Visible = false;
  1009. btnImport.Visible = false;
  1010. lblImpTitle.Visible = false;
  1011. btnExportData.Visible = false;
  1012. btnUpdate.Visible = false;
  1013.  
  1014. gbConf.Visible = true;
  1015. }
  1016. }
  1017.  
  1018. private void btnExportMenu_Click(object sender, EventArgs e)
  1019. {
  1020. if (IsConnected == true)
  1021. {
  1022. gbConf.Visible = false;
  1023. btnImport.Visible = false;
  1024. lblImpTitle.Visible = false;
  1025.  
  1026.  
  1027. //Metodos
  1028. LoadConf();
  1029. GetTables();
  1030.  
  1031. btnExportData.Visible = true;
  1032. dgvTables.Visible = true;
  1033. lbExport.Visible = true;
  1034. btnDelete.Visible = true;
  1035. btnAdd.Visible = true;
  1036. btnExport.Visible = true;
  1037. lblExpTitle.Visible = true;
  1038. btnUpdate.Visible = true;
  1039. }
  1040. }
  1041.  
  1042. private void btnImportMenu_Click(object sender, EventArgs e)
  1043. {
  1044. if (IsConnected == true)
  1045. {
  1046. dgvTables.Visible = false;
  1047. lbExport.Visible = false;
  1048. btnDelete.Visible = false;
  1049. btnAdd.Visible = false;
  1050. btnExport.Visible = false;
  1051. lblExpTitle.Visible = false;
  1052. gbConf.Visible = false;
  1053. btnExportData.Visible = false;
  1054. btnUpdate.Visible = false;
  1055.  
  1056.  
  1057. btnImport.Visible = true;
  1058. lblImpTitle.Visible = true;
  1059. }
  1060. }
  1061.  
  1062. //usado para movimentar o form
  1063. public const int WM_NCLBUTTONDOWN = 0xA1;
  1064. public const int HT_CAPTION = 0x2;
  1065.  
  1066. [System.Runtime.InteropServices.DllImportAttribute("user32.dll")]
  1067. public static extern int SendMessage(IntPtr hWnd, int Msg, int wParam, int lParam);
  1068. [System.Runtime.InteropServices.DllImportAttribute("user32.dll")]
  1069. public static extern bool ReleaseCapture();
  1070.  
  1071. private void panelTop_MouseDown(object sender, MouseEventArgs e)
  1072. {
  1073. if (e.Button == MouseButtons.Left)
  1074. {
  1075. ReleaseCapture();
  1076. SendMessage(Handle, WM_NCLBUTTONDOWN, HT_CAPTION, 0);
  1077. }
  1078. }
  1079. }
  1080. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!