private void tableList()
{
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sysobjects " +
"WHERE type = \'U\' and name != \'sysdiagrams\' ";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox2.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
checkedListBox1.Items.Clear();
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
"inner join sysobjects so ON sc.id = so.id " +
"WHERE so.name = \'" + listBox2.Text + "\'";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox1.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox2.Items.Clear();
SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Database=\"" +
comboBox1.Text + "\";Integrated Security=true;");
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sys.database_principals " +
"WHERE (type <> \'r\') AND (name NOT IN (\'dbo\', \'sys\', \'INFORMATION_SCHEMA\'))";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
comboBox2.Items.Add(SqlDR.GetString(0));
}
if (comboBox2.Items.Count > 0)
{
comboBox2.SelectedIndex = 0;
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
/// <summary>
/// Load the database names from the selected instance
/// </summary>
private void LoadDatabases()
{
this.Cursor = Cursors.WaitCursor;
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
SqlCom.CommandText = "select name from sys.databases";
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
comboBoxDatabases.Items.Clear();
while (SqlDR.Read())
{
comboBoxDatabases.Items.Add(SqlDR.GetString(0));
}
}
if (comboBoxDatabases.Items.Count == 0)
{
comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDownList;
}
this.Cursor = Cursors.Default;
}
private void bdList()
{
SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Integrated Security=true;");
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sys.databases " +
"WHERE name not in (\'master\',\'tempdb\',\'model\',\'msdb\')";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
comboBox1.Items.Add(SqlDR.GetString(0));
}
comboBox1.SelectedIndex = 0;
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
/// <summary>
/// Load the srid of the selected data table
/// </summary>
private void LoadSRID()
{
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
if (cn.Database != comboBoxDatabases.Text)
{
cn.ChangeDatabase(comboBoxDatabases.Text);
}
string[] tableName = comboBoxDataTable.Text.Trim().Split(new char[] { '.' });
string geomCol = comboBoxGeomCol.Text.ToLower().Replace("(geometry)", "").Replace("(geography)", "").Trim();
if (geomCol.Length == 0)
{
return;
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
if (tableName.Length > 1)
{
SqlCom.CommandText = "select top 1 [" + geomCol + "].STSrid, [" + geomCol + "].STGeometryType() from [" + tableName[0] + "].[" + tableName[1] + "] where [" + geomCol + "] is not null";
}
else
{
return;
}
this.Cursor = Cursors.WaitCursor;
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
while (SqlDR.Read())
{
if (!SqlDR.IsDBNull(0))
{
textBoxSRID.Text = SqlDR.GetInt32(0).ToString();
}
else
{
textBoxSRID.Text = "";
}
geometryType = SqlDR.GetString(1);
break;
}
}
this.Cursor = Cursors.Default;
}
private void BD_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(strCon);
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
List <String> listBD = new List <String>();
while (SqlDR.Read())
{
listBD.Add(SqlDR.GetString(0));
}
SqlCon.Close();
treeViewList = new List <TreeViewItem>();
int pi = 0;
int id = 1;
foreach (String list in listBD)
{
SqlCon.Open();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "SELECT * FROM " + list + ".INFORMATION_SCHEMA.TABLES;";
cmd.CommandType = CommandType.Text;
cmd.Connection = SqlCon;
reader = cmd.ExecuteReader();
treeViewList.Add(new TreeViewItem()
{
ParentID = 0, ID = id, Text = list
});
while (reader.Read())
{
treeViewList.Add(new TreeViewItem()
{
ParentID = id, ID = id + 10000, Text = reader.GetString(2)
});
}
id++;
SqlCon.Close();
}
PopulateTreeView(0, null);
}
private void checkedListBox2_SelectedIndexChanged(object sender, EventArgs e)
{
checkedListBox1.Items.Clear();
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
"inner join sysobjects so ON sc.id = so.id " +
"WHERE so.name = \'" + checkedListBox2.Text + "\'";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox1.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
Load = true;
for (int i = 0; i < table.Count; i++)
{
if (table[i].name == checkedListBox2.Items[checkedListBox2.SelectedIndex].ToString())
{
for (int j = 0; j < table[i].column.Count; j++)
{
for (int r = 0; r < checkedListBox1.Items.Count; r++)
{
if (table[i].column[j] == checkedListBox1.Items[r].ToString())
{
checkedListBox1.SetItemChecked(r, true);
break;
}
}
}
break;
}
}
Load = false;
}
private void LoadDatabaseName()
{
try
{
string server = cbServer.Text.Trim();
SqlConnection SqlCon = new SqlConnection(@"server=.;integrated security=true;");
SqlCon.Open();
SqlCommand SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
string strSystemDB = "master,model,msdb,tempdb";
cbDatabase.Items.Clear();
while (SqlDR.Read())
{
string dbName = SqlDR.GetString(0);
if (strSystemDB.Contains(dbName) == false)
{
cbDatabase.Items.Add(dbName);
}
}
if (cbDatabase.Items.Count > 0)
{
cbDatabase.SelectedIndex = 0;
btnSave.Enabled = true;
}
else
{
cbDatabase.Items.Add("Không có CSDL nào");
cbDatabase.SelectedIndex = 0;
btnSave.Enabled = false;
}
}
catch (Exception ex)
{
XtraMessageBox.Show("Lỗi:" + ex.Message, "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
btnSave.Enabled = true;
}
}
public void LlenarComboBox()
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(CCadena);
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
List <String> listBD = new List <String>();
while (SqlDR.Read())
{
comboBox1.Items.Add(SqlDR.GetValue(0));
}
}
/// <summary>
/// Load the table names of the selected database
/// </summary>
private void LoadDataTables()
{
this.Cursor = Cursors.WaitCursor;
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
if (cn.Database != comboBoxDatabases.Text)
{
cn.ChangeDatabase(comboBoxDatabases.Text);
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
SqlCom.CommandText = "select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.Tables";
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
comboBoxDataTable.Items.Clear();
while (SqlDR.Read())
{
comboBoxDataTable.Items.Add(SqlDR.GetString(0) + "." + SqlDR.GetString(1));
}
}
if (comboBoxDataTable.Items.Count == 0)
{
comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDownList;
}
this.Cursor = Cursors.Default;
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(txtServer.Text) || string.IsNullOrEmpty(txtUID.Text) || string.IsNullOrEmpty(txtPassword.Text))
{
MessageBox.Show("Please enter the required details.");
IsConnected = false;
}
else
{
lstDbs.Items.Clear();
SqlConnection SqlCon = new SqlConnection("server=" + txtServer.Text + ";uid=" + txtUID.Text + ";pwd=" + txtPassword.Text);
SqlCon.Open();
SqlCommand SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
lstDbs.Items.Add(SqlDR.GetString(0));
}
SqlDR.Close();
SqlCon.Close();
IsConnected = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
IsConnected = false;
}
}
//This connects user to the selected sql server and authenticates them to it
protected void Button4_Click(object sender, EventArgs e)
{
Label1.Text = "Table Not Generated";
Label4.Text = "";
RadioButtonList2.Enabled = true;
RadioButtonList1.Enabled = true;
CheckBoxList1.Enabled = true;
RadioButtonList1.Items.Clear();
RadioButtonList2.Items.Clear();
CheckBoxList1.Items.Clear();
TextBox4.Text = "";
cache.Remove("filecontents", null);
try
{
if (CheckBox1.Checked == true)
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(@"server=" + TextBox3.Text + ";Integrated Security=SSPI;");
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
RadioButtonList2.Items.Add(SqlDR.GetString(0));
}
Button3.Enabled = true;
TextBox1.ReadOnly = true;
TextBox2.ReadOnly = true;
TextBox3.ReadOnly = true;
Label6.Visible = false;
Label7.Visible = false;
}
else if (CheckBox1.Checked == false)
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(@"server=" + TextBox3.Text + ";uid=" + TextBox1.Text + ";pwd=" + TextBox2.Text + ";");
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
RadioButtonList2.Items.Add(SqlDR.GetString(0));
}
Button3.Enabled = true;
TextBox1.ReadOnly = true;
TextBox2.ReadOnly = true;
TextBox3.ReadOnly = true;
Label6.Visible = false;
Label7.Visible = false;
}
}
catch
{
}
}
/// <summary>
/// Load the column names of the selected data table
/// </summary>
private void LoadColumns()
{
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
if (cn.Database != comboBoxDatabases.Text)
{
cn.ChangeDatabase(comboBoxDatabases.Text);
}
string[] tableName = comboBoxDataTable.Text.Trim().Split(new char[] { '.' });
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
if (tableName.Length > 1)
{
SqlCom.CommandText = "select DATA_TYPE, COLUMN_NAME from INFORMATION_SCHEMA.Columns where TABLE_SCHEMA = '" + tableName[0] + "' and TABLE_NAME = '" + tableName[1] + "'";
}
else
{
return;
}
this.Cursor = Cursors.WaitCursor;
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
comboBoxFIDCol.Items.Clear();
comboBoxGeomCol.Items.Clear();
while (SqlDR.Read())
{
string dataType = SqlDR.GetString(0);
if (dataType == "geometry")
{
comboBoxGeomCol.Items.Add(SqlDR.GetString(1));
}
else if (dataType == "geography")
{
comboBoxGeomCol.Items.Add(SqlDR.GetString(1) + "(geography)");
}
else if (dataType == "int")
{
comboBoxFIDCol.Items.Add(SqlDR.GetString(1));
}
}
}
if (comboBoxFIDCol.Items.Count == 0)
{
comboBoxFIDCol.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxFIDCol.DropDownStyle = ComboBoxStyle.DropDownList;
}
if (comboBoxGeomCol.Items.Count == 0)
{
comboBoxGeomCol.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxGeomCol.DropDownStyle = ComboBoxStyle.DropDownList;
}
this.Cursor = Cursors.Default;
}