private void AddNew()
{
Connection conn = new Connection();
string message = Language.Resource.Message;
Security security = new Security();
bool check = conn.CheckExists(tbxName.Text);
if (check)
{
MessageBox.Show(Language.Resource.NameExists, message);
}
else
{
conn.Name = tbxName.Text;
conn.Server = tbxServer.Text;
conn.Database = tbxDatabase.Text;
conn.Username = tbxUser.Text;
conn.Password = tbxPassword.Text;
conn.Provider = tbxProvider.Text;
int result = conn.Add();
if (result == 1)
{
frmManager manager = (frmManager)Application.OpenForms["frmManager"];
manager.SelectTab("tabConnections");
this.Close();
}
}
}
public void DeleteConnection(string name)
{
Connection connection = new Connection();
connection.Name = name;
bool result = connection.Delete();
if (result)
{
LoadConnection();
}
}
public void UpdateConnection(string name)
{
Connection conn = new Connection();
conn.Name = tbxName.Text;
conn.Server = tbxServer.Text;
conn.Database = tbxDatabase.Text;
conn.Username = tbxUser.Text;
conn.Password = tbxPassword.Text;
conn.Provider = tbxProvider.Text;
int result = conn.Update();
if (result == 1)
{
frmManager manager = (frmManager)Application.OpenForms["frmManager"];
manager.SelectTab("tabConnections");
this.Close();
}
}
public frmConnectionUpdate(string name)
{
_name = name;
_isAdd = false;
InitializeComponent();
if (User.Current != null)
{
Connection connection = new Connection();
var currentConnection = connection.GetBy(name);
if (currentConnection != null)
{
tbxName.Text = currentConnection.Name;
tbxServer.Text = currentConnection.Server;
tbxDatabase.Text = currentConnection.Database;
tbxUser.Text = currentConnection.Username;
tbxPassword.Text = currentConnection.Password;
tbxProvider.Text = currentConnection.Provider;
}
}
}
public string GetTables(string name, string dbName)
{
string result = "";
if (User.Current != null)
{
Connection conn = new Connection();
conn = GetBy(name);
List<string> tables = new List<string>();
if (conn != null)
{
if (conn.Provider == "System.Data.SqlClient")
{
string connection = "Data Source=" + conn.Server + ";User ID=" + conn.Username + ";Password="******";";
using (SqlConnection sqlConn = new SqlConnection(connection))
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
if (string.IsNullOrEmpty(dbName))
{
cmd.CommandText = "USE " + conn.Database + " SELECT * FROM sys.Tables";
}
else
{
cmd.CommandText = "USE " + dbName + " SELECT * FROM sys.Tables";
}
cmd.Connection = sqlConn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tables.Add(reader[0].ToString());
}
sqlConn.Close();
result = new JavaScriptSerializer().Serialize(tables);
}
}
}
}
return result;
}
public string GetRows(string name, string dbName, string sql)
{
string result = "";
if (User.Current != null)
{
Connection conn = new Connection();
conn = GetBy(name);
DataTable Tables = new DataTable();
if (conn != null)
{
if (conn.Provider == "System.Data.SqlClient")
{
string connection = "Data Source=" + conn.Server + ";User ID=" + conn.Username + ";Password="******";";
using (SqlConnection sqlConn = new SqlConnection(connection))
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "USE " + (string.IsNullOrEmpty(dbName) ? conn.Database : dbName) + " " + sql;
cmd.Connection = sqlConn;
SqlDataReader reader = cmd.ExecuteReader();
Tables.Load(reader);
if (Tables.Rows.Count > 0)
{
string rowDelimiter = "";
StringBuilder sb = new StringBuilder("[");
foreach (DataRow row in Tables.Rows)
{
sb.Append(rowDelimiter);
sb.Append(FromDataRow(row));
rowDelimiter = ",";
}
sb.Append("]");
result = sb.ToString();
}
sqlConn.Close();
}
}
}
}
return result;
}
public string GetDatabases(string name)
{
string result = "";
if (User.Current != null)
{
Connection conn = new Connection();
conn = GetBy(name);
if (conn != null)
{
if (conn.Provider == "System.Data.SqlClient")
{
string connection = "Data Source=" + conn.Server + ";User ID=" + conn.Username + ";Password="******";";
using (SqlConnection sqlConn = new SqlConnection(connection))
{
sqlConn.Open();
List<string> databases = new List<string>();
DataTable dt = sqlConn.GetSchema("Databases");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[0];
databases.Add(tablename);
}
sqlConn.Close();
result = new JavaScriptSerializer().Serialize(databases);
}
}
}
}
return result;
}
public string GetColumns(string name, string dbName, string tableName)
{
string result = "";
if (User.Current != null)
{
Connection conn = new Connection();
conn = GetBy(name);
List<string> tables = new List<string>();
if (conn != null)
{
if (conn.Provider == "System.Data.SqlClient")
{
string connection = "Data Source=" + conn.Server + ";User ID=" + conn.Username + ";Password="******";";
using (SqlConnection sqlConn = new SqlConnection(connection))
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "USE " + (string.IsNullOrEmpty(dbName) ? conn.Database : dbName) + " Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'";
cmd.Connection = sqlConn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tables.Add(reader[3].ToString());
}
sqlConn.Close();
result = new JavaScriptSerializer().Serialize(tables);
}
}
}
}
return result;
}
public string GetByJSON(string name)
{
string result = "";
Connection conn = new Connection();
if (User.Current != null)
{
Security security = new Security();
XmlDocument connections = security.ReadConnectionConfiguration(User.Current.Path);
XmlNode node = connections.SelectSingleNode("/root/connections/connection[name='" + name + "']");
if (node != null)
{
conn.Name = node.SelectSingleNode("name").InnerText;
conn.Server = node.SelectSingleNode("server").InnerText;
conn.Database = node.SelectSingleNode("dbname").InnerText;
conn.Username = node.SelectSingleNode("dbuser").InnerText;
conn.Password = node.SelectSingleNode("dbpass").InnerText;
conn.Provider = node.SelectSingleNode("provider").InnerText;
result = new JavaScriptSerializer().Serialize(conn);
}
}
return result;
}
public Connection GetBy(string name)
{
Connection conn = new Connection();
if (User.Current != null)
{
Security security = new Security();
XmlDocument connections = security.ReadConnectionConfiguration(User.Current.Path);
XmlNode node = connections.SelectSingleNode("/root/connections/connection[name='" + name + "']");
if (node != null)
{
conn.Name = node.SelectSingleNode("name").InnerText;
conn.Server = node.SelectSingleNode("server").InnerText;
conn.Database = node.SelectSingleNode("dbname").InnerText;
conn.Username = node.SelectSingleNode("dbuser").InnerText;
conn.Password = node.SelectSingleNode("dbpass").InnerText;
conn.Provider = node.SelectSingleNode("provider").InnerText;
}
else
{
conn = null;
}
}
else
{
conn = null;
}
return conn;
}
public string GetAllJSON()
{
string result = "";
List<Connection> listConnection = new List<Connection>();
if (User.Current != null)
{
Security security = new Security();
XmlDocument connections = security.ReadConnectionConfiguration(User.Current.Path);
foreach (XmlNode node in connections.SelectNodes("root/connections/connection"))
{
Connection conn = new Connection();
conn.Name = node.SelectSingleNode("name").InnerText;
conn.Server = node.SelectSingleNode("server").InnerText;
conn.Database = node.SelectSingleNode("dbname").InnerText;
conn.Username = node.SelectSingleNode("dbuser").InnerText;
conn.Password = node.SelectSingleNode("dbpass").InnerText;
conn.Provider = node.SelectSingleNode("provider").InnerText;
listConnection.Add(conn);
}
result = new JavaScriptSerializer().Serialize(listConnection);
}
return result;
}
public List<Connection> GetAll()
{
List<Connection> listConnection = new List<Connection>();
if (User.Current != null)
{
Security security = new Security();
XmlDocument connections = security.ReadConnectionConfiguration(User.Current.Path);
foreach (XmlNode node in connections.SelectNodes("root/connections/connection"))
{
Connection conn = new Connection();
conn.Name = node.SelectSingleNode("name").InnerText;
conn.Server = node.SelectSingleNode("server").InnerText;
conn.Database = node.SelectSingleNode("dbname").InnerText;
conn.Username = node.SelectSingleNode("dbuser").InnerText;
conn.Password = node.SelectSingleNode("dbpass").InnerText;
conn.Provider = node.SelectSingleNode("provider").InnerText;
listConnection.Add(conn);
}
}
return listConnection;
}
public int ExcuteQuery(string name, string dbName, string sql)
{
int result = -1;
if (User.Current != null)
{
Connection conn = new Connection();
conn = GetBy(name);
DataTable Tables = new DataTable();
if (conn != null)
{
if (conn.Provider == "System.Data.SqlClient")
{
string connection = "Data Source=" + conn.Server + ";User ID=" + conn.Username + ";Password="******";";
using (SqlConnection sqlConn = new SqlConnection(connection))
{
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "USE " + (string.IsNullOrEmpty(dbName) ? conn.Database : dbName) + " " + sql;
cmd.Connection = sqlConn;
result = cmd.ExecuteNonQuery();
sqlConn.Close();
}
}
}
}
return result;
}
private void LoadConnection()
{
Connection connection = new Connection();
Security security = new Security();
List<Connection> lstConnection = connection.GetAll();
lvConnections.Items.Clear();
foreach (Connection conn in lstConnection)
{
ListViewItem item = new ListViewItem(new[] { conn.Name, conn.Server, conn.Database, conn.Username, security.encrypt(User.Current.PublicKey, conn.Password), conn.Provider });
lvConnections.Items.Add(item);
}
}
