public ActionResult Login(ConnInfo connInfo)
{
bool bConn = false;
var ret = new QueryResult();
SqlConnUtil connector = null;
if (connInfo != null)
{
connector = new SqlConnUtil();
connector.SetConnectInfo(connInfo.host, connInfo.user, connInfo.pass);
bConn = connector.TryConnect();
}
if (bConn)
{
Session[KEY_LOGINFO] = connector;
//return RedirectToAction("QueryPage");
ret.retCode = true;
ret.sData = "QueryPage";
return(Json(ret));
}
else
{
ModelState.AddModelError("", "无法登录到目标数据库。");
//return Json(Url.Action("Index"));
ret.retCode = false;
ret.sData = "Index";
return(Json(ret));
}
//return View();
}
public void ConnTest(ConnInfo connInfo)
{
ConnTestDal connTestDal = new ConnTestDal();
string conn = string.Format("Data Source = {0};Initial Catalog = {1};User Id = {2};Password = {3};",
connInfo.Ip, connInfo.Name, connInfo.UserId, connInfo.Pwd);
connTestDal.ConnTest(conn);
}
private void ReceiveData()
{
ConnInfo connInfo = new ConnInfo();
connInfo.Socket = _socket;
_socket.BeginReceive(connInfo.Data, 0, connInfo.Data.Length, SocketFlags.None,
ReceivedCallBack, connInfo);
}
public void GetTableInfo(ConnInfo connInfoA, ConnInfo connInfoB, out List <TableInfo> tableListA, out List <TableInfo> tableListB)
{
AnalyseTableDal analyseTableDal = new AnalyseTableDal();
string conn = "Data Source = {0};Initial Catalog = {1};User Id = {2};Password = {3};";
string connA = string.Format(conn, connInfoA.Ip, connInfoA.Name, connInfoA.UserId, connInfoA.Pwd);
string connB = string.Format(conn, connInfoB.Ip, connInfoB.Name, connInfoB.UserId, connInfoB.Pwd);
analyseTableDal.GetTableInfo(connA, connB, out tableListA, out tableListB);
HandleData(tableListA);
HandleData(tableListB);
}
private void btnB_Click(object sender, EventArgs e)
{
ConnInfo connInfo = new ConnInfo();
connInfo.Ip = tbIPB.Text.Trim();
connInfo.Name = tbDBB.Text.Trim();
connInfo.UserId = tbUserNameB.Text.Trim();
connInfo.Pwd = tbPwdB.Text.Trim();;
TestConn(connInfo);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Create ConnInfo object and populate elements
ConnInfo connInfo = ConnInfo.getConnInfo(conID);
string connectionString = "";
string providerName = "";
//Set Table Datasources & fill in gridview/boxes
if (connInfo.getDatabaseType() == ConnInfo.MSSQL)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Initial Catalog=" + connInfo.getDatabaseName() + ";Persist Security Info=True;User Id=" + connInfo.getUserName() + ";Password="******"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'";
}
else if (connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = "server=" + connInfo.getServerAddress() + ";User Id=" + connInfo.getUserName() + ";password="******";Persist Security Info=True;database=" + connInfo.getDatabaseName();
providerName = "MySql.Data.MySqlClient";
SQLTables.ConnectionString = connectionString;
SQLTables.ProviderName = providerName;
SQLTables.SelectCommand = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'";
}
else if (connInfo.getDatabaseType() == ConnInfo.ORACLE)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Persist Security Info=True;User ID=" + connInfo.getUserName() + ";Password="******";Unicode=True";
providerName = "System.Data.OracleClient";
oracleTables.ConnectionString = connectionString;
oracleTables.ProviderName = providerName;
oracleTables.SelectCommand = "select TABLE_NAME from user_tables";
}
else //Default set to SQL
{
}
updateTables(connInfo.getDatabaseType());
//Get Description
Description conDesc = Description.getDescription(conID);
string descBox = conDesc.getDesc();
descriptionBox.Text = descBox;
//Garbage collection
connInfo = null;
}
}
private void TestConn(ConnInfo connInfo)
{
try
{
ConnTestBll connTestBll = new ConnTestBll();
connTestBll.ConnTest(connInfo);
MessageBox.Show("连接成功！");
}
catch (Exception ex)
{
MessageBox.Show("连接失败！" + ex.Message);
}
}
public SqlConn(
)
{
if ((dataSource == null || dataSource == "") ||
(initialCatalog == null || initialCatalog == "") ||
(schema == null || schema == "") ||
(userId == null || userId == "") ||
(password == null || password == ""))
{
string fileName = "./db_connection";
if (File.Exists(fileName))
{
Console.WriteLine("Проверка доступа к базе данных. ");
byte[] bytesInFile = File.ReadAllBytes(fileName);
string key = DecryptStringFromBytes_Aes(bytesInFile);
connInfo = JsonConvert.DeserializeObject <ConnInfo>(key);
dataSource = connInfo.dataSource;
initialCatalog = connInfo.initialCatalog;
schema = connInfo.schema;
userId = connInfo.userId;
password = connInfo.password;
}
else
{
Console.WriteLine("Проверка доступа к базе данных. ");
if (GetConnInfo())
{
byte[] license = EncryptStringToBytes_Aes(JsonConvert.SerializeObject(connInfo));
System.IO.File.WriteAllBytes(fileName, license);
dataSource = connInfo.dataSource;
initialCatalog = connInfo.initialCatalog;
schema = connInfo.schema;
userId = connInfo.userId;
password = connInfo.password;
}
}
connString = @"Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";User ID=" + userId + ";Password="******";";
this.conn = new SqlConnection(connString);
this.conn.Open();
this.conn.Close();
Console.WriteLine("\nДоступ к базе данных получен.");
}
connString = @"Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";User ID=" + userId + ";Password="******";";
this.conn = new SqlConnection(connString);
this.conn.Open();
}
protected void GridViewColumns_PageIndexChanged(object sender, EventArgs e)
{
ConnInfo connInfo = ConnInfo.getConnInfo(conID);
string connectionString = "";
string providerName = "";
//Set drop down box accordingly
if (connInfo.getDatabaseType() == ConnInfo.MSSQL)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Initial Catalog=" + connInfo.getDatabaseName() + ";Persist Security Info=True;User Id=" + connInfo.getUserName() + ";Password="******"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else if (connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = "server=" + connInfo.getServerAddress() + ";User Id=" + connInfo.getUserName() + ";password="******";Persist Security Info=True;database=" + connInfo.getDatabaseName();
providerName = "MySql.Data.MySqlClient";
ColGen.ConnectionString = connectionString;
ColGen.ProviderName = providerName;
ColGen.SelectCommand = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else if (connInfo.getDatabaseType() == ConnInfo.ORACLE)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Persist Security Info=True;User ID=" + connInfo.getUserName() + ";Password="******";Unicode=True";
providerName = "System.Data.OracleClient";
ColGen.ConnectionString = connectionString;
ColGen.ProviderName = providerName;
ColGen.SelectCommand = "SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else //Default set to SQL
{
}
//Garbage collection
connInfo = null;
}
public async Task runListener()
{
_listener.Start();
while (true)
{
var extClient = await _listener.AcceptTcpClientAsync();
var extInfo = new ConnInfo(extClient);
var serverConnInfo = new ConnInfo(serverConn);
serverConnInfo.peer = extInfo;
extInfo.peer = serverConnInfo;
// forward
serverConnInfo.conn.Client.BeginReceive(serverConnInfo.buffer, 0, serverConnInfo.buffer.Length,
SocketFlags.None,
onClientReceieveAsync, serverConnInfo);
extInfo.conn.Client.BeginReceive(extInfo.buffer, 0, extInfo.buffer.Length, SocketFlags.None,
onClientReceieveAsync, extInfo);
}
}
private void ReceivedCallBack(IAsyncResult result)
{
ConnInfo connInfo = result.AsyncState as ConnInfo;
try
{
SocketError socketError;
int socketSize = _socket.EndReceive(result, out socketError);
if (socketError == SocketError.Success)
{
byte[] messageBytes = new byte[socketSize];
Array.Copy(connInfo.Data, messageBytes, messageBytes.Length);
BHandler.Manage(messageBytes);
}
} catch (Exception e)
{
Console.WriteLine($"{e.Message}");
}
finally
{
try
{
connInfo.Socket.BeginReceive(connInfo.Data, 0, connInfo.Data.Length,
SocketFlags.None, ReceivedCallBack, connInfo);
} catch (Exception e)
{
Console.WriteLine($"{e.Message}");
connInfo.Socket.Close();
Thread.Sleep(3000);
Environment.Exit(0);
}
}
}
public mssqlAdapter(ConnInfo connInfo)
{
this.connInfo = connInfo;
connStr = string.Format("server={0};user id={1};password={2}",
connInfo.server, connInfo.userName, connInfo.password);
}
private void btnAnalyse_Click(object sender, EventArgs e)
{
AnalyseTableBll analyseTableBll = new AnalyseTableBll();
ConnInfo connInfoA = new ConnInfo();
connInfoA.Ip = tbIPA.Text.Trim();
connInfoA.Name = tbDBA.Text.Trim();
connInfoA.UserId = tbUserNameA.Text.Trim();
connInfoA.Pwd = tbPwdA.Text.Trim();;
ConnInfo connInfoB = new ConnInfo();
connInfoB.Ip = tbIPB.Text.Trim();
connInfoB.Name = tbDBB.Text.Trim();
connInfoB.UserId = tbUserNameB.Text.Trim();
connInfoB.Pwd = tbPwdB.Text.Trim();;
try
{
analyseTableBll.GetTableInfo(connInfoA, connInfoB, out _tableListA, out _tableListB);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
foreach (ListView lv in _lvArray)
{
UICommonHelper.ClearListView(lv);
}
string compareFied = "TableName";
// 求库A多于B
var resultA = _tableListA.Except(_tableListB, new ComparerHelper <TableInfo>(compareFied)).ToList();
foreach (var item in resultA)
{
UICommonHelper.RefreshListView(listView1, item.TableName);
}
// 求库B多于A
var resultB = _tableListB.Except(_tableListA, new ComparerHelper <TableInfo>(compareFied)).ToList();
foreach (var item in resultB)
{
UICommonHelper.RefreshListView(listView2, item.TableName);
}
#region 表名相同分析差异
var sameTableName = _tableListA.Intersect(_tableListB, new ComparerHelper <TableInfo>(compareFied)).ToList();
var diffTable = _tableListA.Except(_tableListB, new ComparerHelper <TableInfo>()).ToList();
// 求AB相同的表
var resultC = sameTableName.Except(diffTable, new ComparerHelper <TableInfo>(compareFied)).ToList();
foreach (var item in resultC)
{
UICommonHelper.RefreshListView(listView3, item.TableName);
}
// 求AB不同的表
var resultD = sameTableName.Intersect(diffTable, new ComparerHelper <TableInfo>(compareFied)).ToList();
foreach (var item in resultD)
{
UICommonHelper.RefreshListView(listView4, item.TableName);
}
#endregion
}
protected void addLatLong_Click(object sender, EventArgs e)
{
ConnInfo connInfo = ConnInfo.getConnInfo(conID);
string selectedTable = GridViewTables.SelectedValue.ToString();
string connectionString = "";
string providerName = "";
SqlDataSource temp = new SqlDataSource();
//Set drop down box accordingly
if (connInfo.getDatabaseType() == ConnInfo.MSSQL)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Initial Catalog=" + connInfo.getDatabaseName() + ";Persist Security Info=True;User Id=" + connInfo.getUserName() + ";Password="******"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedTable + "')";
}
else if (connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = "server=" + connInfo.getServerAddress() + ";User Id=" + connInfo.getUserName() + ";password="******";Persist Security Info=True;database=" + connInfo.getDatabaseName();
providerName = "MySql.Data.MySqlClient";
temp.ConnectionString = connectionString;
temp.ProviderName = providerName;
temp.SelectCommand = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedTable + "')";
}
else if (connInfo.getDatabaseType() == ConnInfo.ORACLE)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Persist Security Info=True;User ID=" + connInfo.getUserName() + ";Password="******";Unicode=True";
providerName = "System.Data.OracleClient";
temp.ConnectionString = connectionString;
temp.ProviderName = providerName;
temp.SelectCommand = "SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + selectedTable + "')";
}
else //Default set to SQL
{
}
Mapping conMapping = Mapping.getMapping(conID, selectedTable);
string dbTable = conMapping.getTableName();
int dbFormat = conMapping.getFormat();
string dbLat = conMapping.getLatFieldName();
string dbLong = conMapping.getLongFieldName();
//No information about the table yet
if (dbTable == null)
{
mapUpdates(temp);
}
else
{
mapUpdates(temp, dbLat, dbLong, dbFormat);
}
//Garbage collection
connInfo = null;
viewGrid.Visible = true;
saveLatLong.Visible = true;
addLatLong.Visible = false;
tblColumnsPanel.Visible = false;
mapColumnsPanel.Visible = true;
}
protected void GridViewTables_SelectedIndexChanged(object sender, EventArgs e)
{
columnButtons.Visible = true;
columnMessage.Visible = false;
string selectedTable = GridViewTables.SelectedValue.ToString();
selectedGVTable.Value = selectedTable;
string pageInfo = "viewTable.aspx?con=" + conID + "&tbl=" + selectedTable;
string window = "window.open('" + pageInfo + "'); return false;";
viewTable.Attributes.Add("onclick", window);
if (selectedTable != "")
{
ConnInfo connInfo = ConnInfo.getConnInfo(conID);
string connectionString = "";
string providerName = "";
//Set drop down box accordingly
if (connInfo.getDatabaseType() == ConnInfo.MSSQL)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Initial Catalog=" + connInfo.getDatabaseName() + ";Persist Security Info=True;User Id=" + connInfo.getUserName() + ";Password="******"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else if (connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = "server=" + connInfo.getServerAddress() + ";User Id=" + connInfo.getUserName() + ";password="******";Persist Security Info=True;database=" + connInfo.getDatabaseName();
providerName = "MySql.Data.MySqlClient";
ColGen.ConnectionString = connectionString;
ColGen.ProviderName = providerName;
ColGen.SelectCommand = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else if (connInfo.getDatabaseType() == ConnInfo.ORACLE)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Persist Security Info=True;User ID=" + connInfo.getUserName() + ";Password="******";Unicode=True";
providerName = "System.Data.OracleClient";
ColGen.ConnectionString = connectionString;
ColGen.ProviderName = providerName;
ColGen.SelectCommand = "SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + selectedGVTable.Value + "')";
ColGen.DataBind();
}
else //Default set to SQL
{
}
Mapping conMapping = Mapping.getMapping(conID, selectedTable);
string dbTable = conMapping.getTableName();
int dbFormat = conMapping.getFormat();
string dbLat = conMapping.getLatFieldName();
string dbLong = conMapping.getLongFieldName();
//No information about the table yet
if (dbTable == null)
{
mapUpdates(ColGen);
}
else
{
mapUpdates(ColGen, dbLat, dbLong, dbFormat);
}
//Garbage collection
connInfo = null;
}
else
{
iColFNBox.Items.Clear();
UpdateFieldCol.Update();
}
}
protected void iTableINBox_SelectedIndexChanged(object sender, EventArgs e)
{
string selectedTable = iTableINBox.SelectedValue.ToString();
if (selectedTable != "")
{
ConnInfo connInfo = ConnInfo.getConnInfo(conID);
string connectionString = "";
string providerName = "";
//Set drop down box accordingly
if (connInfo.getDatabaseType() == ConnInfo.MSSQL)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Initial Catalog=" + connInfo.getDatabaseName() + ";Persist Security Info=True;User Id=" + connInfo.getUserName() + ";Password="******"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedTable + "')";
iColINBox.DataSource = temp;
iColINBox.DataValueField = "COLUMN_NAME";
iColINBox.DataTextField = "COLUMN_NAME";
iColINBox.DataBind();
UpdateFieldCol.Update();
}
else if (connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = "server=" + connInfo.getServerAddress() + ";User Id=" + connInfo.getUserName() + ";password="******";Persist Security Info=True;database=" + connInfo.getDatabaseName();
providerName = "MySql.Data.MySqlClient";
SqlDataSource temp = new SqlDataSource();
temp.ConnectionString = connectionString;
temp.ProviderName = providerName;
temp.SelectCommand = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + selectedTable + "')";
iColINBox.DataSource = temp;
iColINBox.DataValueField = "COLUMN_NAME";
iColINBox.DataTextField = "COLUMN_NAME";
iColINBox.DataBind();
UpdateFieldCol.Update();
}
else if (connInfo.getDatabaseType() == ConnInfo.ORACLE)
{
connectionString = "Data Source=" + connInfo.getServerAddress() + ";Persist Security Info=True;User ID=" + connInfo.getUserName() + ";Password="******";Unicode=True";
providerName = "System.Data.OracleClient";
SqlDataSource temp = new SqlDataSource();
temp.ConnectionString = connectionString;
temp.ProviderName = providerName;
temp.SelectCommand = "SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + selectedTable + "')";
iColINBox.DataSource = temp;
iColINBox.DataValueField = "COLUMN_NAME";
iColINBox.DataTextField = "COLUMN_NAME";
iColINBox.DataBind();
UpdateFieldCol.Update();
}
else //Default set to SQL
{
}
//Garbage collection
connInfo = null;
}
else
{
iColINBox.Items.Clear();
UpdateFieldCol.Update();
}
}
