I have a SQL Server Database filled with Reports all having the same schema.
What I've done is created a .NET application to automate the process of adding all reports to a new table.
How can the code be modified to increase performance? Is there a better approach then using: conn.Close(); dr.Close(); conn.Open();
successively to insert the tables?
static void Main(string[] args)
{
//Base Report Must Have some form of "Base" in Table Name
string repColsString = "[";
// Connection strings exist in working code
string sqlConnMove = @"";
string sqlConnBaseline = @"";
Console.Write("What database has the reports? ");
string mainDB = Console.ReadLine();
List<string> connStringSplit = sqlConnMove.Split(';').ToList();
connStringSplit[1] = "Initial Catalog=" + mainDB;
string connString = string.Join(";", connStringSplit);
Console.Write("Enter Shared Name Across All Reports: ");
string commonName = Console.ReadLine();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
//BUILD DB
Console.Write("Enter the New Database Name: ");
string DBname = Console.ReadLine();
using (SqlConnection conn2 = new SqlConnection(sqlConnMove))
{
conn2.Open();
var createDB = conn2.CreateCommand();
createDB.CommandText = "CREATE DATABASE " + DBname;
createDB.ExecuteNonQuery();
Console.WriteLine("DB " + DBname + " created.");
}
List<string> tables = new List<string>();
DataTable t = conn.GetSchema("Tables");
Console.Write("Enter Destination Combined Table Name: ");
string destinationTableName = Console.ReadLine();
string baseReport = "";
foreach (DataRow row in t.Rows)
{
string tablename = (string)row[2];
if (tablename.Contains(commonName)) { tables.Add(tablename); }
if (tablename.ToLower().Contains("base")) { baseReport = tablename; };
if (tables.Contains(baseReport)) { tables.Remove(baseReport); }
}
//ADD BASE REPORT TO NEW DB
String insertBaseToDb = String.Format("SELECT * INTO [{0}].[dbo].[{1}] " +
"FROM [XMfb].[dbo].[{2}];", DBname ,destinationTableName, baseReport);
using (SqlCommand addBaseReport = new SqlCommand(insertBaseToDb, conn))
{
addBaseReport.ExecuteNonQuery();
Console.WriteLine("Base Report: " + baseReport + " added." );
}
foreach (var insertTable in tables)
{
string colQuery = String.Format("SELECT * FROM [XMfb].[dbo].[{0}]", insertTable );
using (conn = new SqlConnection(sqlConnBaseline))
{
conn.Open();
//Console.WriteLine("State" + conn.State);
//Console.WriteLine("Timeout" + conn.ConnectionTimeout);
using (SqlCommand cmd = new SqlCommand(colQuery, conn))
{
try
{
SqlDataReader dr = cmd.ExecuteReader();
cmd.CommandTimeout = 45;
while (dr.Read())
{
var columns = new List<string>();
for (int i = 0; i < dr.FieldCount; i++)
{
string colName = dr.GetName(i);
columns.Add(dr.GetName(i));
repColsString += colName;
}
repColsString = String.Join("],[", columns);
repColsString = "[" + repColsString + "]";
// Modify this?
conn.Close();
dr.Close();
conn.Open();
// THIS WORRIES ME
using (SqlCommand insCom = conn.CreateCommand())
{
insCom.CommandType = CommandType.Text;
string insertQuery = "INSERT INTO [" + DBname + " ].[dbo].["+ destinationTableName + "] (" + repColsString + ") " +
"SELECT " + repColsString + " FROM [XMfb].[dbo].[" + insertTable + "];";
//Console.WriteLine(insertQuery);
insCom.CommandText = insertQuery;
//insCom.Parameters.Add(new SqlParameter("@COLUMNS", "works"));
insCom.ExecuteNonQuery();
Console.WriteLine("\n" + insertTable + " added. ");
}
break;
}
}
catch (Exception ex)
{
StringBuilder sb = new StringBuilder();
Console.WriteLine(sb.Append(ex.Message) + " on table " + insertTable + ".\n");
}
}
}
}
List<string> connStringSplit = sqlConnMove.Split(';').ToList();
is pointless, sincesqlConnMove
is an empty string. You also might want to look at theSqlConnectionStringBuilder
class. \$\endgroup\$ – BCdotWEB May 9 '18 at 11:16