private static string GetSQLServerConnectionString(DBConnectionType dbConn)
{
string connStr = string.Format("Data Source={0};Network Library=DBMSSOCN;Initial Catalog={1};User ID={2};Password={3};",
dbConn.SQLServerAddress, dbConn.SQLServerDBName, dbConn.SQLServerUsername, dbConn.SQLServerPassword);
return(connStr);
}
public bool isEqual(DBConnectionType other)
{
if (dbType != other.dbType)
{
return(false);
}
if (AccessFilePath != other.AccessFilePath)
{
return(false);
}
if (SQLServerAddress != other.SQLServerAddress)
{
return(false);
}
if (SQLServerDBName != other.SQLServerDBName)
{
return(false);
}
if (SQLServerUsername != other.SQLServerUsername)
{
return(false);
}
if (SQLServerPassword != other.SQLServerPassword)
{
return(false);
}
return(true);
}
private void buttonSaveConfig_Click(object sender, EventArgs e)
{
DBConnectionType dbConnType = new DBConnectionType();
if (rbAccess.Checked)
{
dbConnType.dbType = DatabaseType.Access;
string accessFilePath = this.textBoxAccessFilePath.Text.Trim();
if (false == File.Exists(accessFilePath))
{
MessageBox.Show("Access文件不存在，请重新指定文件路径。", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
dbConnType.AccessFilePath = accessFilePath;
}
else
{
dbConnType.dbType = DatabaseType.SQLServer;
string address = this.textBoxSQLServerDBAddr.Text.Trim();
string dbName = this.textBoxSQLServerDBName.Text.Trim();
string userId = this.textBoxSQLServerUserId.Text.Trim();
string password = textBoxSQLServerPassword.Text.Trim();
dbConnType.SQLServerAddress = address;
dbConnType.SQLServerDBName = dbName;
dbConnType.SQLServerUsername = userId;
dbConnType.SQLServerPassword = password;
}
dbConnType.isUsed = true;
bool result = DBConnectionUtil.SaveDBConnectionType(dbConnType);
if (result)
{
MessageBox.Show("更新数据库配置成功！\r\n你需要重新启动系统以使改动生效！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("更新数据库配置失败，请检查输入！", "失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static bool SaveDBConnectionType(DBConnectionType dbConnType)
{
//DBConnectionType usedDBConnType = GetDBConnectionType(true);
//if (usedDBConnType.isEqual(dbConnType))
// return false;
XmlNodeList dbConnNodeList = XmlDoc.SelectNodes("/Settings/DBConnection/option");
foreach (XmlNode node in dbConnNodeList)
{
DatabaseType dbType = DatabaseType.NotSpecified;
string dbTypeStr = node.Attributes["DBType"].Value;
if (string.Compare(dbTypeStr, "ACCESS") == 0)
{
dbType = DatabaseType.Access;
}
else
{
dbType = DatabaseType.SQLServer;
}
if (dbType == dbConnType.dbType)
{
node.Attributes["selected"].Value = "1";
if (dbType == DatabaseType.Access)
{
node.Attributes["value"].Value = GetAccessConnectionStringFromFilePath(dbConnType.AccessFilePath);
}
else
{
node.Attributes["value"].Value = GetSQLServerConnectionString(dbConnType);
}
}
else
{
node.Attributes["selected"].Value = "0";
}
}
XmlDoc.Save(EbayConstants.SystemSettingsXmlPath);
return(true);
}
public bool isEqual(DBConnectionType other)
{
if (dbType != other.dbType)
return false;
if (AccessFilePath != other.AccessFilePath)
return false;
if (SQLServerAddress != other.SQLServerAddress)
return false;
if (SQLServerDBName != other.SQLServerDBName)
return false;
if (SQLServerUsername != other.SQLServerUsername)
return false;
if (SQLServerPassword != other.SQLServerPassword)
return false;
return true;
}
// Database connection utilities.
public static DBConnectionType GetDBConnectionType(bool used)
{
DBConnectionType dbConnectionType = new DBConnectionType();
XmlNodeList dbConnNodeList = XmlDoc.SelectNodes("/Settings/DBConnection/option");
foreach (XmlNode node in dbConnNodeList)
{
string dbTypeStr = node.Attributes["DBType"].Value;
string selectedStr = node.Attributes["selected"].Value;
bool isUsed = 0 == string.Compare(selectedStr, "1");
string dbConnStr = node.Attributes["value"].Value;
dbConnectionType.DBConnectionString = dbConnStr;
if (isUsed == used)
{
if (0 == string.Compare(dbTypeStr, "SQLSERVER"))
{
dbConnectionType.dbType = DatabaseType.SQLServer;
}
else if (0 == string.Compare(dbTypeStr, "ACCESS"))
{
dbConnectionType.dbType = DatabaseType.Access;
}
if (DatabaseType.Access == dbConnectionType.dbType)
{
dbConnectionType.AccessFilePath = GetAccessFilePathFromConnectionString(dbConnStr);
}
else if (DatabaseType.SQLServer == dbConnectionType.dbType)
{
dbConnectionType = GetSQLServerInfoFromConnectionString(dbConnStr);
}
break;
}
}
dbConnectionType.isUsed = true;
return(dbConnectionType);
}
private void buttonSaveConfig_Click(object sender, EventArgs e)
{
DBConnectionType dbConnType = new DBConnectionType();
if (rbAccess.Checked)
{
dbConnType.dbType = DatabaseType.Access;
string accessFilePath = this.textBoxAccessFilePath.Text.Trim();
if (false == File.Exists(accessFilePath))
{
MessageBox.Show("Access文件不存在，请重新指定文件路径。", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
dbConnType.AccessFilePath = accessFilePath;
}
else
{
dbConnType.dbType = DatabaseType.SQLServer;
string address = this.textBoxSQLServerDBAddr.Text.Trim();
string dbName = this.textBoxSQLServerDBName.Text.Trim();
string userId = this.textBoxSQLServerUserId.Text.Trim();
string password = textBoxSQLServerPassword.Text.Trim();
dbConnType.SQLServerAddress = address;
dbConnType.SQLServerDBName = dbName;
dbConnType.SQLServerUsername = userId;
dbConnType.SQLServerPassword = password;
}
dbConnType.isUsed = true;
bool result = DBConnectionUtil.SaveDBConnectionType(dbConnType);
if (result)
MessageBox.Show("更新数据库配置成功！\r\n你需要重新启动系统以使改动生效！", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
MessageBox.Show("更新数据库配置失败，请检查输入！", "失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
private void FrmDBConnection_Load(object sender, EventArgs e)
{
DBConnectionType dbConnType = DBConnectionUtil.GetDBConnectionType(true);
DBConnectionType unusedDbConnType = DBConnectionUtil.GetDBConnectionType(false);
if (dbConnType.dbType == DatabaseType.Access)
{
this.panelAccess.Visible = true;
this.panelSQLServer.Visible = false;
this.rbAccess.Checked = true;
this.rbSqlServer.Checked = false;
this.textBoxAccessFilePath.Text = dbConnType.AccessFilePath;
this.textBoxSQLServerDBAddr.Text = unusedDbConnType.SQLServerAddress;
this.textBoxSQLServerDBName.Text = unusedDbConnType.SQLServerDBName;
this.textBoxSQLServerUserId.Text = unusedDbConnType.SQLServerUsername;
this.textBoxSQLServerPassword.Text = unusedDbConnType.SQLServerPassword;
}
else
{
this.panelAccess.Visible = false;
this.panelSQLServer.Visible = true;
this.panelSQLServer.Top = this.panelAccess.Top;
this.rbAccess.Checked = false;
this.rbSqlServer.Checked = true;
this.textBoxSQLServerDBAddr.Text = dbConnType.SQLServerAddress;
this.textBoxSQLServerDBName.Text = dbConnType.SQLServerDBName;
this.textBoxSQLServerUserId.Text = dbConnType.SQLServerUsername;
this.textBoxSQLServerPassword.Text = dbConnType.SQLServerPassword;
this.textBoxAccessFilePath.Text = unusedDbConnType.AccessFilePath;
}
}
// Get SQL Server info from connection string which is like:
// "Data Source=zhiwang,49172;Network Library=DBMSSOCN;Initial Catalog=ebaybachelor;User ID=sa;Password=567835;"
private static DBConnectionType GetSQLServerInfoFromConnectionString(string connStr)
{
DBConnectionType connType = new DBConnectionType();
connType.dbType = DatabaseType.SQLServer;
int indexOfDataSource = connStr.IndexOf("Data Source=");
int indexOfNetworkLibrary = connStr.IndexOf("Network Library=");
int indexOfInitialCatelog = connStr.IndexOf("Initial Catalog=");
int indexOfUserId = connStr.IndexOf("User ID=");
int indexOfPassword = connStr.IndexOf("Password=");
string dataSourceStr = connStr.Substring(indexOfDataSource + 12, indexOfNetworkLibrary - indexOfDataSource - 13);
string dbNameStr = connStr.Substring(indexOfInitialCatelog + 16, indexOfUserId - indexOfInitialCatelog - 17);
string userIdStr = connStr.Substring(indexOfUserId + 8, indexOfPassword - indexOfUserId - 9);
string passwordStr = connStr.Substring(indexOfPassword + 9).TrimEnd(new char[] { ';' });
connType.SQLServerAddress = dataSourceStr;
connType.SQLServerDBName = dbNameStr;
connType.SQLServerUsername = userIdStr;
connType.SQLServerPassword = passwordStr;
connType.DBConnectionString = connStr;
return(connType);
}
// Get SQL Server info from connection string which is like:
// "Data Source=zhiwang,49172;Network Library=DBMSSOCN;Initial Catalog=ebaybachelor;User ID=sa;Password=567835;"
private static DBConnectionType GetSQLServerInfoFromConnectionString(string connStr)
{
DBConnectionType connType = new DBConnectionType();
connType.dbType = DatabaseType.SQLServer;
int indexOfDataSource = connStr.IndexOf("Data Source=");
int indexOfNetworkLibrary = connStr.IndexOf("Network Library=");
int indexOfInitialCatelog = connStr.IndexOf("Initial Catalog=");
int indexOfUserId = connStr.IndexOf("User ID=");
int indexOfPassword = connStr.IndexOf("Password=");
string dataSourceStr = connStr.Substring(indexOfDataSource + 12, indexOfNetworkLibrary - indexOfDataSource - 13);
string dbNameStr = connStr.Substring(indexOfInitialCatelog + 16, indexOfUserId - indexOfInitialCatelog - 17);
string userIdStr = connStr.Substring(indexOfUserId + 8, indexOfPassword - indexOfUserId - 9);
string passwordStr = connStr.Substring(indexOfPassword + 9).TrimEnd(new char[] { ';' });
connType.SQLServerAddress = dataSourceStr;
connType.SQLServerDBName = dbNameStr;
connType.SQLServerUsername = userIdStr;
connType.SQLServerPassword = passwordStr;
connType.DBConnectionString = connStr;
return connType;
}
private static string GetSQLServerConnectionString(DBConnectionType dbConn)
{
string connStr = string.Format("Data Source={0};Network Library=DBMSSOCN;Initial Catalog={1};User ID={2};Password={3};",
dbConn.SQLServerAddress, dbConn.SQLServerDBName, dbConn.SQLServerUsername, dbConn.SQLServerPassword);
return connStr;
}
public static bool SaveDBConnectionType(DBConnectionType dbConnType)
{
//DBConnectionType usedDBConnType = GetDBConnectionType(true);
//if (usedDBConnType.isEqual(dbConnType))
// return false;
XmlNodeList dbConnNodeList = XmlDoc.SelectNodes("/Settings/DBConnection/option");
foreach (XmlNode node in dbConnNodeList)
{
DatabaseType dbType = DatabaseType.NotSpecified;
string dbTypeStr = node.Attributes["DBType"].Value;
if (string.Compare(dbTypeStr, "ACCESS") == 0)
dbType = DatabaseType.Access;
else
dbType = DatabaseType.SQLServer;
if (dbType == dbConnType.dbType)
{
node.Attributes["selected"].Value = "1";
if (dbType == DatabaseType.Access)
node.Attributes["value"].Value = GetAccessConnectionStringFromFilePath(dbConnType.AccessFilePath);
else
node.Attributes["value"].Value = GetSQLServerConnectionString(dbConnType);
}
else
{
node.Attributes["selected"].Value = "0";
}
}
XmlDoc.Save(EbayConstants.SystemSettingsXmlPath);
return true;
}
// Database connection utilities.
public static DBConnectionType GetDBConnectionType(bool used)
{
DBConnectionType dbConnectionType = new DBConnectionType();
XmlNodeList dbConnNodeList = XmlDoc.SelectNodes("/Settings/DBConnection/option");
foreach (XmlNode node in dbConnNodeList)
{
string dbTypeStr = node.Attributes["DBType"].Value;
string selectedStr = node.Attributes["selected"].Value;
bool isUsed = 0 == string.Compare(selectedStr, "1");
string dbConnStr = node.Attributes["value"].Value;
dbConnectionType.DBConnectionString = dbConnStr;
if (isUsed == used)
{
if (0 == string.Compare(dbTypeStr, "SQLSERVER"))
dbConnectionType.dbType = DatabaseType.SQLServer;
else if (0 == string.Compare(dbTypeStr, "ACCESS"))
dbConnectionType.dbType = DatabaseType.Access;
if (DatabaseType.Access == dbConnectionType.dbType)
{
dbConnectionType.AccessFilePath = GetAccessFilePathFromConnectionString(dbConnStr);
}
else if (DatabaseType.SQLServer == dbConnectionType.dbType)
{
dbConnectionType = GetSQLServerInfoFromConnectionString(dbConnStr);
}
break;
}
}
dbConnectionType.isUsed = true;
return dbConnectionType;
}