public bool CheckTable(ServerModel server, string tablename)
{
try
{
string query = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='" + tablename + "') SELECT 1 ELSE SELECT 0";
var tablelist = new List<TableModel>();
using (SqlCommand cmd = new SqlCommand(query, ConnectionManager.Connection(server)))
{
int res = Convert.ToInt32(cmd.ExecuteScalar());
if (res == 1)
return true;
else
return false;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ConnectionManager.Close();
}
}
public static SqlConnection Connection(ServerModel server)
{
conn.ConnectionString = GetConnectionString(server == null ? AppTimeConfiguration.MainServer : server);
if (conn.State == ConnectionState.Closed)
conn.Open();
return conn;
}
public string GetScript(ServerModel server, string spname)
{
try
{
using (SqlCommand cmd = new SqlCommand("sys.sp_helptext", ConnectionManager.Connection(server)))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objname", spname);
DataSet ds = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = cmd;
sqlDataAdapter.Fill(ds);
return CommonHelper.DataTableToString(ds.Tables[0]); ;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ConnectionManager.Close();
}
return null;
}
public List<string> GetServerDatabaseList(ServerModel server)
{
try
{
string query = "SELECT name FROM master.sys.databases";
var dblist = new List<string>();
using (SqlCommand cmd = new SqlCommand(query, ConnectionManager.Connection(server)))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dblist.Add(dr["name"].ToString());
}
return dblist;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ConnectionManager.Close();
}
return null;
}
public List<TableModel> SearchTable(ServerModel server,string tablename)
{
try
{
string query = "select name,object_id from sys.tables WHERE name LIKE '%" + tablename + "%' ORDER BY name";
var tablelist = new List<TableModel>();
using (SqlCommand cmd = new SqlCommand(query, ConnectionManager.Connection(server)))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
tablelist.Add(new TableModel()
{
TableId = Convert.ToInt32(dr["object_id"].ToString()),
TableName = dr["name"].ToString()
});
}
return tablelist;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ConnectionManager.Close();
}
return null;
}
public List<SPModel> SearchStoredProcedure(ServerModel server, string spname)
{
try
{
string query = "select SPECIFIC_NAME from " + server.Database + ".information_schema.routines where routine_type = 'PROCEDURE'";
query += " SPECIFIC_NAME LIKE '%" + spname + "%'";
var splist = new List<SPModel>();
using (SqlCommand cmd = new SqlCommand(query, ConnectionManager.Connection(server)))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
splist.Add(new SPModel() { SPName = dr["SPECIFIC_NAME"].ToString() });
}
return splist;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ConnectionManager.Close();
}
return null;
}
public int AddServer(ServerModel server)
{
try
{
DBServer ns = new DBServer();
ns.Servername = server.ServerName;
ns.Username = server.Username;
ns.Password = server.Password;
ns.Database = server.Database;
ns.DBName = server.DbAlias;
ns.IsMain = server.IsMain;
ns.IsVisible = server.IsVisible;
using (DevKitEntities db = new DevKitEntities())
{
db.DBServers.Add(ns);
db.SaveChanges();
return ns.ServerID;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public frmDBObject()
{
InitializeComponent();
dgvallTables.AutoGenerateColumns = false;
dgvtblSelected.AutoGenerateColumns = false;
selectedtables = new List<TableModel>();
maindb = new ServerModel();
LoadLastSavedSession();
}
public object ExecuteQuery(ServerModel server, string query)
{
try
{
return spData.ExecuteQuery(server,query);
}
catch (Exception ex)
{
throw ex;
}
}
public List<TableModel> SearchTable(ServerModel server, string tablename)
{
try
{
return tdata.SearchTable(server, tablename);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List<SPModel> GetStoredProcedureList(ServerModel server)
{
try
{
return spData.GetStoredProcedureList(server);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List<string> GetServerDatabaseList(ServerModel server)
{
try
{
return connectServerData.GetServerDatabaseList(server);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List<TableModel> GetTableList(ServerModel server)
{
try
{
return tdata.GetTableList(server);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void GenerateTableSript(ServerModel server, ServerModel depserver, List<TableModel> tablelist)
{
try
{
thdata.GenerateTableSript(server,depserver,tablelist);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string GetScript(ServerModel server, string spname)
{
try
{
return spData.GetScript(server, spname);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int AddNewDbServer(ServerModel newServer)
{
try
{
return entityData.AddServer(newServer);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables)
{
try
{
return scriptData.GenerateTableData(server, gentype, tables);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static string GetConnectionString(ServerModel server)
{
if (server != null)
{
ConnString = "Server=" + server.ServerName + ";" +
"Database=" + server.Database + ";" +
"User Id=" + server.Username + ";" +
"Password="******";";
return ConnString;
}
return null;
}
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables)
{
try
{
var output = new StringBuilder();
if (gentype == DataGenType.Truncate)
{
foreach (var t in tables)
{
var scpt = GetDataHeaderQuery(gentype, t.TableName);
output.AppendLine(scpt);
}
}
else
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
Database genDb = srv.Databases[server.Database];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = false;
Scripter scripter = new Scripter(srv) { Options = scriptOptions };
foreach (var t in tables)
{
var gen = new StringBuilder();
var tbl = genDb.Tables[t.TableName, "dbo"];
var script = scripter.EnumScript(new SqlSmoObject[] { tbl });
foreach (var line in script)
gen.AppendLine(line);
var scpt = GetDataHeaderQuery(gentype, t.TableName);
scpt = scpt.Replace("{query}", gen.ToString());
output.AppendLine(scpt);
}
}
return output.ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void GenerateTableSript(ServerModel server, ServerModel depserver, List<TableModel> tablelist)
{
//Server srv = new Server(new ServerConnection(ConnectionManager.Connection(server)));
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
string dbName = server.Database;
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
List<Table> newtables = new List<Table>();
List<TableModel> modtables = new List<TableModel>();
foreach (var tbl in tablelist)
{
if (!CheckTable(depserver, tbl.TableName))
{
newtables.Add(db.Tables[tbl.TableName]);
}
else
{
modtables.Add(tbl);
}
}
foreach (Table tbl in newtables)
{
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
}
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
}
void AddNewServer()
{
ServerModel ns = new ServerModel();
ns.ServerName = cmbServerName.Text.Trim();
ns.Username = txtUsername.Text.Trim();
ns.Password = txtPassword.Text.Trim();
ns.Database = cmbDatabase.Text.Trim();
ns.DbAlias = txtDatabase.Text.Trim();
EntityBusiness business = new EntityBusiness();
int res = business.AddNewDbServer(ns);
ClearForm();
if (res > 0)
MessageBox.Show("Details saved succesfully", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public object ExecuteQuery(ServerModel server, string query)
{
try
{
Server smoserver = new Server(new ServerConnection(ConnectionManager.Connection(server)));
var res = smoserver.ConnectionContext.ExecuteNonQuery(query);
return res;
}
catch (Exception ex)
{
throw ex;
}
finally
{
ConnectionManager.Close();
}
}
void LoadDatabases()
{
ServerModel sm = new ServerModel();
sm.ServerName = cmbServerName.Text.Trim();
sm.Username = txtUsername.Text.Trim();
sm.Password = txtPassword.Text.Trim();
ConnectServerBusiness business = new ConnectServerBusiness();
var dblist = business.GetServerDatabaseList(sm);
cmbDatabase.DataSource = dblist;
}
public void GenereateAlterScripts(ServerModel server, ServerModel depserver, List<TableModel> tablelist)
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
string dbName = server.Database;
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
var modtables = new List<Table>();
foreach (var tbl in tablelist)
{
modtables.Add(db.Tables[tbl.TableName]);
}
List<Column> collist = new List<Column>();
foreach (Table tbl in modtables)
{
//ScriptingOptions options = new ScriptingOptions();
//options.ClusteredIndexes = true;
//options.Default = true;
//options.DriAll = true;
//options.Indexes = true;
//options.IncludeHeaders = true;
//StringCollection coll = tbl.Script(options);
//foreach (string str in coll)
//{
// sb.Append(str);
// sb.Append(Environment.NewLine);
//}
foreach (Column col in tbl.Columns)
{
collist.Add(col);
}
tbl.Columns.Add(collist[0]);
tbl.Alter();
}
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
}
private void tscomserver_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (tscomserver.ComboBox.Items.Count > 0)
{
if (tscomserver.ComboBox.SelectedIndex != -1)
{
int id = Convert.ToInt32(tscomserver.ComboBox.SelectedValue);
EntityBusiness data = new EntityBusiness();
var servers = data.GetServerList();
maindb = servers.Where(x => x.ServerID == id).FirstOrDefault();
LoadTables();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void LoadDatabases()
{
tscomserver.SelectedIndexChanged -= tscomserver_SelectedIndexChanged;
EntityBusiness entityBusiness = new EntityBusiness();
var dbs = entityBusiness.GetServerList();
tscomserver.ComboBox.DisplayMember = "DbAlias";
tscomserver.ComboBox.ValueMember = "ServerID";
tscomserver.ComboBox.DataSource = dbs;
string mainserver = new ConfigurationHelper().GetConfigurationValue("mainserver");
if (mainserver != "0" && mainserver != "")
{
tscomserver.ComboBox.SelectedValue = Convert.ToInt32(mainserver);
maindb = dbs.Where(x => x.ServerID == Convert.ToInt32(mainserver)).FirstOrDefault();
//LoadOtherDBs(dbs, Convert.ToInt32(mainserver));
}
else
{
tscomserver.ComboBox.SelectedIndex = -1;
//LoadOtherDBs(dbs, 0);
}
tscomserver.SelectedIndexChanged += tscomserver_SelectedIndexChanged;
}
private void ExecuteQueryOnServer(ServerModel server, string query)
{
StoredProcedureBusiness spdata = new StoredProcedureBusiness();
var res = spdata.ExecuteQuery(server == null ? AppTimeConfiguration.MainServer : server, query);
}
public string GenerateTableStructure(ServerModel server, StructureType gentype,List<TableModel> tables)
{
try
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
Scripter scripter = new Scripter(srv);
Database genDb = srv.Databases[server.Database];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriPrimaryKey = true;
scriptOptions.AnsiFile = true;
scriptOptions.IncludeHeaders = false;
scriptOptions.ScriptOwner = false;
scriptOptions.AppendToFile = false;
scriptOptions.AllowSystemObjects = false;
scriptOptions.ScriptDrops = false;
scriptOptions.WithDependencies = false;
scriptOptions.SchemaQualify = false;
scriptOptions.SchemaQualifyForeignKeysReferences = false;
scriptOptions.ScriptBatchTerminator = false;
scriptOptions.Indexes = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.NoCollation = true;
scriptOptions.DriAll = true;
scriptOptions.DriIncludeSystemNames = false;
scriptOptions.ToFileOnly = true;
scriptOptions.Permissions = true;
StringBuilder sb = new StringBuilder();
foreach (var t in tables)
{
string tbScr = "";
var tbl = genDb.Tables[t.TableName, "dbo"];
StringCollection tableScripts = tbl.Script(scriptOptions);
foreach (string script in tableScripts)
tbScr += script + "\n";
var scpt = GetStructureHeaderQuery(gentype, t.TableName);
scpt = scpt.Replace("{query}", tbScr);
sb.AppendLine(scpt);
sb.AppendLine("\n");
}
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return null;
}