i am doing a program to block ip address . First i import the list of ip address from 2 table of sql database. Then the third datagridview will show the ip address that difference in the 1st and second datagridview. Here is what I have so far...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Runtime.InteropServices;
using Excel;
namespace testingpurpose
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void BindDataGrid()
{
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString = ConfigurationManager.ConnectionStrings["myConn"].ToString();
myConn.Open();
string strSQL = "SELECT * FROM FAT_Table WHERE Type IN ('NoteBook','Desktop')";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, myConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
//SqlCommand cmd = new SqlCommand("select user_id from profile where user_id = " + StaticData.ActiveUserId + "", myConn);
DataTable table = new DataTable();
dataAdapter.Fill(table);
BindingSource.DataSource = table;
dataGridView.ReadOnly = true;
dataGridView.DataSource = BindingSource;
myConn.Close();
}
private void btnBrowse_Click_1(object sender, EventArgs e)
{
OpenFileDialog openFD = new OpenFileDialog();
openFD.CheckFileExists = true;
openFD.InitialDirectory = "Desktop";
openFD.Title = "Select a File";
openFD.FileName = "";
openFD.Multiselect = false;
openFD.Filter = "All Files (*.*)|*.*|Excel Files (*.xls)|*.xls|CSV Files (*.csv*)|*.csv";
if (openFD.ShowDialog() == DialogResult.OK)
{
this.txtPath.Text = openFD.FileName;
}
}
private void btnImport_Click(object sender, EventArgs e)
{
if (txtPath.Text == "")
{
MessageBox.Show("Please select a file");
}
else
{
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString = ConfigurationManager.ConnectionStrings["myConn"].ToString();
String filename = txtPath.Text;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Open(filename,
0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 1;
object colIndex3 = 3;
object colIndex9 = 9;
object colIndex12 = 12;
object colIndex1 = 1;
try
{
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex3]).Value2 != null)
{
myConn.Open();
rowIndex = 1 + index;
string col3 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex3]).Value2.ToString();
string col9 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex9]).Value2.ToString();
string col12 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex12]).Value2.ToString();
string col1 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string insert = "INSERT INTO FAT_Table(Type,IPAddress,ComputerName,Owner)VALUES('" + col3 + "','" + col9 + "','" + col12 + "','" + col1 + "')";
SqlCommand cmd = new SqlCommand(insert, myConn);
SqlDataReader reader = cmd.ExecuteReader();
index++;
myConn.Close();
BindDataGrid();
}
}
catch (Exception ex)
{
app.Quit();
Console.WriteLine(ex.Message);
}
}
}
private void BindDataGrid2()
{
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString = ConfigurationManager.ConnectionStrings["myConn"].ToString();
myConn.Open();
string strSQL = "SELECT * FROM IPScanning_Table ";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, myConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataTable table = new DataTable();
dataAdapter.Fill(table);
BindingSource.DataSource = table;
dataGridView2.ReadOnly = true;
dataGridView2.DataSource = BindingSource;
myConn.Close();
}
private void btnBrowse2_Click(object sender, EventArgs e)
{
OpenFileDialog openFD = new OpenFileDialog();
openFD.CheckFileExists = true;
openFD.InitialDirectory = "Desktop";
openFD.Title = "Select a File";
openFD.FileName = "";
openFD.Multiselect = false;
openFD.Filter = "All Files (*.*)|*.*|Excel Files (*.xls)|*.xls|CSV Files (*.csv*)|*.csv";
if (openFD.ShowDialog() == DialogResult.OK)
{
this.txtPath2.Text = openFD.FileName;
}
}
private void btnImport2_Click(object sender, EventArgs e)
{
if (txtPath2.Text == "")
{
MessageBox.Show("Please select a file");
}
else
{
SqlConnection myConn = new SqlConnection();
myConn.ConnectionString = ConfigurationManager.ConnectionStrings["myConn"].ToString();
String filename2 = txtPath2.Text;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Open(filename2,
0,
true,
5,
"",
"",
true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
object rowIndex = 1;
object colIndex2 = 2;
object colIndex1 = 1;
object colIndex5 = 5;
object colIndex4 = 4;
try
{
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2 != null)
{
myConn.Open();
rowIndex = 1 + index;
string col2 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
string col1 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string col5 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex5]).Value2.ToString();
string col4 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex4]).Value2.ToString();
string insert = "INSERT INTO IPScanning_Table(Status,IPAddress,ComputerName,OperatingSystem)VALUES('" + col2 + "','" + col1 + "','" + col5 + "','" + col4 + "')";
SqlCommand cmd = new SqlCommand(insert, myConn);
SqlDataReader reader = cmd.ExecuteReader();
index++;
myConn.Close();
BindDataGrid2();
}
}
catch (Exception ex)
{
app.Quit();
Console.WriteLine(ex.Message);
}
}
}
}
}Please help me.
Thank you in advance.
