0

I need to compare filenames(.mdf) with existing databases on SQL server, but I don't know how. The databases that are not attached must show in datagridview.

Here is the code so far:

private void loadDB_Click_1(object sender, EventArgs e)
{
    String[] files = Directory.GetFiles(@"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\", "*.mdf", SearchOption.AllDirectories);

    DataTable table = new DataTable();
    table.Columns.Add("File Name");
    table.Columns.Add("Path");

    for (int i = 0; i < files.Length; i++)
    {
        FileInfo file = new FileInfo(files[i]);
        table.Rows.Add((Path.GetFileNameWithoutExtension(file.Name)),Path.GetFullPath(file.Name));
    }     

    dataGridView2.DataSource = table;
}
nvoigt
  • 75,013
  • 26
  • 93
  • 142
Димитър Грудев
  • 189
  • 2
  • 2
  • 12
  • Maybe try to use this in SqlCommad: http://stackoverflow.com/a/147662/5358389 and then comare results to your file names. – daniell89 Mar 15 '17 at 09:29

1 Answers1

0
 DataTable dbTables = new DataTable();
 SqlDataAdapter adap;
 SqlConnection connection = new SqlConnection("yourConnectionString");            
 SqlCommand cmd = new SqlCommand(@"SELECT name FROM master.dbo.sysdatabases ", connection);           

  adap = new SqlDataAdapter(cmd);
  adap.Fill(dbTables);

  string[] dbTableNames = dbTables.AsEnumerable().Select(x => x.Field<string>("name")).ToArray();

  string[] diff = files.Except(dbTableNames).ToArray(); // one of pssibility to check differents

to populate you DataGridView do this:

dataGridView1.Columns.Add("FileName", "File Name");
for (int i = 0; i < diff.Length; i++)
{
     dataGridView1.Rows.Add(new object[] { diff[i] });
}
daniell89
  • 1,832
  • 16
  • 28
  • you could also select filename from db to get filePath- SELECT name, filename FROM master.dbo.sysdatabases – daniell89 Mar 15 '17 at 10:45