/*
* public ConnectionParams FindByServerName(string name)
* {
* foreach (ConnectionParams cp in this)
* {
* if (cp.Server.ToLowerInvariant() == name.ToLowerInvariant())
* return cp;
* }
* return null;
* }
*/
public void Delete(ConnectionParams cp)
{
int index = 0;
foreach (ConnectionParams d in this)
{
if (d.ID == cp.ID)
{
this.RemoveAt(index);
ConnectionParamsFactory.Save(this);
return;
}
index++;
}
}
public void LoadDataTypes(ConnectionParams cp, bool wantEmpty)
{
ConnectionParams tmpCp = cp;
if (tmpCp == null)
{
tmpCp = _cp;
}
if (_tbl != null)
{
_tbl.PrimaryKey = null;
_tbl = null;
}
cmbType.Items.Clear();
ClearAll();
string cmdText = ResManager.GetDBScript("Script_GetDataTypes");
using (SqlConnection conn = tmpCp.CreateSqlConnection(true, false))
{
_tbl = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);
adapter.Fill(_tbl);
}
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = _tbl.Columns["name"];
_tbl.PrimaryKey = PrimaryKeyColumns;
if (wantEmpty)
{
cmbType.Items.Add(String.Empty);
}
foreach (DataRow row in _tbl.Rows)
{
cmbType.Items.Add(((string)row["name"]).ToLowerInvariant());
}
if (cmbType.Items.Count > 0)
{
cmbType.SelectedIndex = 0;
}
EvaluateDataType();
}
public static string GenerateDropScript(ConnectionParams cp, string name, int type, bool seperateBatches, bool generateIfExists)
{
string ifPart = String.Empty;
string dropCommand = String.Empty;
string comment = "/*** Drop script generated with PragmaSQL on " + DateTime.Now.ToString() + " by [" + cp.CurrentUsername + "] ***/ \n";
string result = String.Empty;
switch (type)
{
case DBObjectType.UserTable:
return(GenerateTableDropScript(cp, name, seperateBatches, generateIfExists));
case DBObjectType.StoredProc:
ifPart = String.Format(@"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC'))", name);
dropCommand = String.Format(@"DROP PROCEDURE {0}", name);
break;
case DBObjectType.TableValuedFunction:
//Fall to next case
case DBObjectType.ScalarValuedFunction:
ifPart = String.Format(@"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))", name);
dropCommand = String.Format(@"DROP FUNCTION {0}", name);
break;
case DBObjectType.View:
ifPart = String.Format(@"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type = 'V')", name);
dropCommand = String.Format(@"DROP VIEW {0}", name);
break;
case DBObjectType.Trigger:
ifPart = String.Format(@"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type = 'TR')", name);
dropCommand = String.Format(@"DROP TRIGGER {0}", name);
break;
case DBObjectType.Synonym:
ifPart = String.Format(@"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type = 'SN')", name);
dropCommand = String.Format(@"DROP SYNONYM {0}", name);
break;
default:
return(result);
}
return(comment
+ (!String.IsNullOrEmpty(cp.Database) ? String.Format("USE[{0}]\n", cp.Database) : String.Empty)
+ (generateIfExists ? ifPart : String.Empty)
+ "\n" + dropCommand);
}
public static AsyncConnectionResult TryToOpenConnection(ConnectionParams cp, ref string error)
{
if (cp == null)
{
throw new Exception("Connection parameters instance is null!");
}
frmAsyncConnectionOpener frm = new frmAsyncConnectionOpener();
frm._cp = cp.CreateCopy();
frm._conn = null;
frm.BringToFront();
frm.ShowDialog();
error = frm._lastError;
return(frm._result);
}
private void button2_Click(object sender, EventArgs e)
{
ConnectionParams cp = GetCurrentConnectionSpec();
cp.Database = "master";
string msg =
"PragmaSQL System Database will be installed to:" + "\n"
+ " * Server : " + cp.Server + "\n"
+ " * Required Credentials : Database owner access for \"" + cp.CurrentUsername + "\" on database \"" + cp.Database + "\""
+ "\n\n"
+ "Do you want to install PragmaSQL System Database?";
DialogResult dlgRes = MessageBox.Show(msg, "Install Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (dlgRes != DialogResult.Yes)
{
return;
}
string installScript = SysDatabaseInstaller.GetDatabaseInstallScript();
installScript = String.Format(installScript, txtDefultDB.Text.ToLowerInvariant(), txtDefultDB.Text);
using (SqlConnection conn = new SqlConnection(cp.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(installScript, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
cp.Database = txtDefultDB.Text;
installScript = SysDatabaseInstaller.GetDatabaseObjectInstallScript();
using (SqlConnection conn = new SqlConnection(cp.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(installScript, conn);
cmd.ExecuteNonQuery();
}
MessageBox.Show("PragmaSQL System Database was installed.", "Install Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static string GetColumnDetails(ConnectionParams cp, string tblName)
{
IList <TableColumnSpec> cols = GetTableColumnsSpecification(cp, tblName);
StringBuilder sb = new StringBuilder();
sb.AppendLine("/*");
sb.AppendLine("\t" + tblName + " Columns");
sb.AppendLine("*/");
int rowCnt = 0;
string identitySpec = String.Empty;
string collationSpec = String.Empty;
foreach (TableColumnSpec col in cols)
{
rowCnt++;
if (col.IsIdentity)
{
identitySpec = " IDENTITY(" + col.IdentitySeed.ToString() + "," + col.IdentityIncrement.ToString() + ")";
}
else
{
identitySpec = String.Empty;
}
if (String.IsNullOrEmpty(col.Collation))
{
collationSpec = String.Empty;
}
else
{
collationSpec = "COLLATE " + col.Collation;
}
if (rowCnt == 1)
{
sb.AppendLine("\t" + col.Name + " " + col.FullyQualifiedDataType + identitySpec + " " + collationSpec + " " + (col.IsNullable ? " NULL" : " NOT NULL"));
}
else
{
sb.AppendLine("\t, " + col.Name + " " + col.FullyQualifiedDataType + identitySpec + " " + collationSpec + " " + (col.IsNullable ? " NULL" : " NOT NULL"));
}
}
return(sb.ToString());
}
public virtual void Add(string Provider, string IntegratedSecurity, string Name, string PersistSecurityInfo,
string InitialCatalog, string UserName, string Password, string TimeOut, bool IsConnected)
{
ConnectionParams cp = new ConnectionParams();
cp.ID = Guid.NewGuid();
//cp.Provider = Provider;
cp.IntegratedSecurity = IntegratedSecurity;
cp.Server = Name;
cp.PersistSecurityInfo = PersistSecurityInfo;
cp.Database = InitialCatalog;
cp.UserName = UserName;
cp.Password = Password;
cp.TimeOut = TimeOut;
cp.IsConnected = IsConnected;
this.Add(cp);
}
public static NodeData Create(ConnectionParams connParams, string name, int type, string dbName, long dbid, string owner)
{
if (connParams == null)
{
throw new NullReferenceException("ConnParams parameter can not be null");
}
NodeData result = new NodeData(type);
result.ConnParams = connParams;
result.DBName = dbName;
result.Name = name;
result.ServerName = connParams.Server;
result.DbId = dbid;
result.Owner = owner;
return(result);
}
public ConnectionParams CreateCopy()
{
ConnectionParams result = new ConnectionParams();
result.ID = this.ID;
//result.Provider = this.Provider;
result.IntegratedSecurity = this.IntegratedSecurity;
result.Server = this.Server;
result.FriendlyName = this.FriendlyName;
result.PersistSecurityInfo = this.PersistSecurityInfo;
result.Database = this.Database;
result.UserName = this.UserName;
result.Password = this.Password;
result.TimeOut = this.TimeOut;
result.IsConnected = this.IsConnected;
result.Connection = this.Connection;
result.IsEncrypted = this.IsEncrypted;
return(result);
}
public bool LoadContent(ConfigurationContent configContent)
{
if (configContent == null)
{
throw new NullParameterException("Configuration content param is null!");
}
if (configContent.PragmaSqlDbConn == null)
{
throw new NullPropertyException("Configuration content does not contain PragmaSqlDbConn item!");
}
_configContent = configContent;
_connParams = _configContent.PragmaSqlDbConn;
LoadInitial();
_isContentLoaded = true;
return(true);
}
public static string GenerateTableDropScript(ConnectionParams cp, string tblName, bool seperateBatches, bool generateIfExists)
{
IList <string> fkeys = GetForeignKeys(cp, tblName);
string dropKeysPart = String.Empty;
string dropTblPart = String.Empty;
string usePart = (!String.IsNullOrEmpty(cp.Database) ? String.Format("USE[{0}]\n", cp.Database) : String.Empty);
string comment = "/*** Drop script generated with PragmaSQL on " + DateTime.Now.ToString() + " by [" + cp.CurrentUsername + "] ***/ \n";
string tmp = String.Empty;
string tmpFkExist = @"IF EXISTS (SELECT * FROM sysforeignkeys WHERE constid = OBJECT_ID(N'{0}') AND parent_obj = OBJECT_ID(N'[{1}]'))" + "\n";
string tmpDropConstraint = @"ALTER TABLE {1} DROP CONSTRAINT [{0}]" + "\n";
string tmpTableExist = @"IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND type in (N'U'))" + "\n";
string tmpDropTable = @"DROP TABLE {0}" + "\n";
// 1- Drop foregin keys part
foreach (string fk in fkeys)
{
tmp = (generateIfExists ? tmpFkExist : String.Empty)
+ tmpDropConstraint
+ (seperateBatches ? "GO\n" : String.Empty);
tmp = String.Format(tmp, fk, tblName);
dropKeysPart += tmp;
}
if (fkeys.Count > 0)
{
dropKeysPart = usePart
+ (seperateBatches ? "\nGO\n" : String.Empty)
+ dropKeysPart;
}
// 2- Drop table part
tmp = (generateIfExists ? tmpTableExist : String.Empty) + tmpDropTable;
tmp = String.Format(tmp, tblName);
dropTblPart = usePart
+ (seperateBatches ? "\nGO\n" : String.Empty)
+ tmp;
return(comment + dropKeysPart + dropTblPart);
}
public static ProjectItem CreateConnectionSpec(Project prj, ConnectionParams cp)
{
if (cp == null)
{
throw new NullParameterException("ConnectionParams paramater is null!");
}
ProjectItem result = new ProjectItem(ProjectItemType.ConnectionSpec);
result.Name = cp.Server + " {" + cp.Database + "}";
if (prj != null)
{
if (!prj.ConnectedItems.ContainsKey(cp.ID.ToString()))
{
prj.ConnectedItems.Add(cp.ID.ToString(), result);
}
}
result.IsConnectedItem = true;
result.ConnectionSpec = cp.CreateCopy();
return(result);
}
public ConnectionParams GetCurrentConnectionSpec( )
{
ConnectionParams connSpec = new ConnectionParams();
connSpec.ID = Guid.NewGuid();
connSpec.Database = txtDefultDB.Text;
if (rbUseIntegratedSecurity.Checked)
{
connSpec.IntegratedSecurity = "SSPI";
}
else
{
connSpec.IntegratedSecurity = "";
}
connSpec.IsConnected = true;
connSpec.Server = txtServer.Text;
connSpec.PersistSecurityInfo = "TRUE";
connSpec.TimeOut = txtTimeOut.Text;
connSpec.UserName = txtUserName.Text;
connSpec.Password = txtPassword.Text;
return(connSpec);
}
public void RenderConnectionParams(ConnectionParams cp)
{
txtServer.Text = cp.Server;
txtDefultDB.Text = cp.Database;
txtTimeOut.Text = cp.TimeOut;
if (cp.FriendlyName != null)
{
txtFriendlyName.Text = cp.FriendlyName;
}
if (cp.IntegratedSecurity.Length == 0)
{
rbUseIntegratedSecurity.Checked = false;
txtUserName.Text = cp.UserName;
txtPassword.Text = cp.Password;
}
else
{
rbUseIntegratedSecurity.Checked = true;
txtUserName.Text = "";
txtPassword.Text = "";
}
}
public static string PrepareConnKeyWithDb(ConnectionParams cp)
{
return(cp.Server.Trim().ToLowerInvariant() + ((Char)29).ToString() + cp.CurrentUsername.Trim().ToLowerInvariant() + ((Char)29).ToString() + cp.Database.Trim().ToLowerInvariant());
}
public static IList <TableColumnSpec> GetTableColumnsSpecification(ConnectionParams cp, string tableName)
{
IList <TableColumnSpec> result = new List <TableColumnSpec>();
IList <string> primaryKeys = new List <string>();
TableColumnSpec col = null;
TableColumnSpec identityCol = null;
using (SqlConnection conn = new SqlConnection(cp.ConnectionString))
{
conn.Open();
// 1- Read primary keys
string script = "exec sp_pkeys '{0}'";
script = String.Format(script, tableName);
SqlCommand cmd = new SqlCommand(script, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
primaryKeys.Add(((string)reader["COLUMN_NAME"]).ToLowerInvariant());
}
}
finally
{
reader.Close();
}
// 2- Read columns
script = ResManager.GetDBScript("Script_GetTableColumns");
script = String.Format(script, tableName);
cmd.CommandText = script;
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
col = new TableColumnSpec();
col.TableName = tableName;
col.TableId = (int)reader["id"];
col.Name = (string)reader["name"];
col.Collation = reader["collation"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["collation"];
col.IsComputed = (int)reader["iscomputed"] == 0 ? false : true;
col.IsNullable = (int)reader["isnullable"] == 0 ? false : true;
col.IsIdentity = (int)reader["isIdentity"] == 0 ? false : true;
col.DataType = reader["typename"].GetType() == typeof(DBNull) ? "[???]" : (string)reader["typename"];
col.Length = reader["lengthx"].GetType() == typeof(DBNull) ? (int)0 : (int)reader["lengthx"];
col.Precision = reader["prec"].GetType() == typeof(DBNull) ? (short)0 : (short)reader["prec"];
col.Scale = reader["scale"].GetType() == typeof(DBNull) ? 0 : (int)reader["scale"];
col.FullyQualifiedDataType = DBConstants.GetFullyQualifiedDataTypeName(true, col.DataType, col.Length, col.Scale, col.Precision);
col.IsInPrimaryKey = primaryKeys.Contains(col.Name.ToLowerInvariant());
result.Add(col);
if (col.IsIdentity && identityCol == null)
{
identityCol = col;
}
}
}
finally
{
reader.Close();
}
// 3- Read identity seed and increment
if (identityCol != null)
{
script = ResManager.GetDBScript("Script_GetTableIdentityIncrementAndSeed");
script = String.Format(script, tableName);
cmd.CommandText = script;
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
identityCol.IdentitySeed = reader["idSeed"].GetType() == typeof(DBNull) ? 0 : (decimal)reader["idSeed"];
identityCol.IdentityIncrement = reader["idIncrement"].GetType() == typeof(DBNull) ? 0 : (decimal)reader["idIncrement"];
break;
}
}
finally
{
reader.Close();
}
}
}
return(result);
}
public static string GenerateDropScript(ConnectionParams cp, string name, int type)
{
return(GenerateDropScript(cp, name, type, true, true));
}
public void LoadObjects(ConnectionParams cp, bool wantEmpty)
{
cmb.Items.Clear();
string inFilter = GenerateInFilter();
if (String.IsNullOrEmpty(inFilter))
{
return;
}
ConnectionParams tmpCp = cp;
if (tmpCp == null)
{
tmpCp = _cp;
}
string cmdText = "declare @cmplevel int select @cmplevel = cmptlevel from master..sysdatabases where name = DB_NAME() ";
cmdText += "select so.id, so.name, so.xtype, CASE WHEN @cmplevel < 90 THEN USER_NAME(so.uid) ELSE SCHEMA_NAME(so.uid) END 'owner' from sysobjects so";
cmdText += " WHERE so.xtype in " + inFilter;
cmdText += " order by CASE WHEN @cmplevel < 90 THEN USER_NAME(so.uid) ELSE SCHEMA_NAME(so.uid) END, so.name";
if (wantEmpty)
{
cmb.Items.Add(null);
}
using (SqlConnection conn = tmpCp.CreateSqlConnection(true, false))
{
SqlDataReader reader = null;
DbObjectSelectorItem item = null;
SqlCommand cmd = new SqlCommand(cmdText, conn);
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
item = new DbObjectSelectorItem();
item.ID = reader.GetInt32(0);
item.Name = reader.GetString(1);
item.ItemType = GetItemType(reader.GetString(2));
item.Owner = reader.GetString(3);
cmb.Items.Add(item);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
if (cmb.Items.Count > 0)
{
cmb.SelectedIndex = 0;
}
}
public string PrepareConnKeyWithDb()
{
return(ConnectionParams.PrepareConnKeyWithDb(this));
}
public virtual void Add(ConnectionParams cp)
{
this.List.Add(cp);
}
