/// <summary>
/// Get ConnectionString
/// </summary>
public string GetConnectionString(string connectionString)
{
if (string.IsNullOrEmpty(connectionString))
return GetConnectionString();
/*
Reference DataLinks
NOTE: Reference
C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL
(Was MSDASC.dll)
SEE:
http://support.microsoft.com:80/support/kb/articles/Q225/1/32.asp
*/
MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
//note that a reference to:
// c:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll
//is also required to read the ADODB._Connection result
ADODB._Connection connection;
//are we editing an existing connect string or getting a new one?
// edit connection string
connection = new ADODB.ConnectionClass();
connection.ConnectionString = connectionString;
//set local COM compatible data type
object oConnection = connection;
//prompt user to edit the given connect string
if ((bool)dataLinks.PromptEdit(ref oConnection))
{
return connection.ConnectionString;
}
return string.Empty;
}
//string ReadFileFromAssembly(string filename)
//{
// string filecontents = String.Empty;
// Assembly assem = System.Reflection.Assembly.GetCallingAssembly();
// Stream stream = assem.GetManifestResourceStream(filename);
// if (stream != null)
// {
// StreamReader tr = new StreamReader(stream);
// filecontents = tr.ReadToEnd();
// tr.Close();
// stream.Close();
// }
// return filecontents;
//}
static void PopulateDatasetList(SasServer ss, string libname)
{
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ss.Workspace.UniqueIdentifier, "", "", 0);
// use the SASHELP.VMEMBER view to get names of all of the datasets and views in the specified library
string selectclause = "select memname, memtype from sashelp.vmember where libname='" + libname + "' and memtype in ('DATA', 'VIEW')";
adorecordset.Open(selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText);
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
da.Fill(ds, adorecordset, "data");
}
catch
{
}
finally
{
adoconnect.Close();
}
}
public static string GetBuiltConnectionString(string inputConnectionString)
{
MSDASC.DataLinks oDL = null;
string res = string.Empty;
try
{
oDL = new MSDASC.DataLinksClass();
ADODB.Connection conn = new ADODB.ConnectionClass();
conn.ConnectionString = inputConnectionString;
object oConn = (object)conn;
if (oDL.PromptEdit(ref oConn))
{
res = conn.ConnectionString;
}
}
catch
{
try
{
ADODB._Connection oConn = (ADODB._Connection)oDL.PromptNew();
if (oConn != null)
{
res = oConn.ConnectionString.ToString();
}
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
}
return(string.IsNullOrEmpty(res) ? null : res);
}
private static string PromptConnectionString(string initial, IntPtr handle)
{
bool ok = false;
ADODB.Connection conn = null;
MSDASC.DataLinks dlinks = null;
try
{
// create objects we'll need
dlinks = new MSDASC.DataLinks();
conn = new ADODB.ConnectionClass();
// initialize object
if (initial != null && initial.Length > 0)
{
conn.ConnectionString = initial;
}
// show connection picker dialog
object obj = conn;
dlinks.hWnd = (int)handle; // << make it modal
ok = dlinks.PromptEdit(ref obj);
}
catch (Exception x)
{
MessageBox.Show("Cannot build connection string because:\r\n" + x.Message);
}
// return what we got
return((ok)? conn.ConnectionString: initial);
}
private static bool GetModifiedConnectionStringFromWizard(string existingConnectionString, out string modifiedConnectionString)
{
modifiedConnectionString = null;
try
{
DataLinks dataLinks = new DataLinksClass();
ADODB.Connection connection = new ADODB.ConnectionClass {
ConnectionString = existingConnectionString
};
object refObject = connection;
if (dataLinks.PromptEdit(ref refObject))
{
modifiedConnectionString = connection.ConnectionString;
return(true);
}
return(false);
}
catch
{
string newConnectionString;
if (GetNewConnectionStringFromWizard(out newConnectionString))
{
modifiedConnectionString = newConnectionString;
return(true);
}
return(false);
}
}
private void btnSetConnect_Click(object sender, EventArgs e)
{
MSDASC.DataLinks datalinks = new MSDASC.DataLinksClass();
ADODB._Connection tmpconc = new ADODB.ConnectionClass();
if (TxtDbstr.Text == String.Empty)
{
tmpconc = (ADODB._Connection)datalinks.PromptNew();
if (tmpconc != null)
{
TxtDbstr.Text = tmpconc.ConnectionString;
}
}
else
{
Object oconc = tmpconc;
tmpconc.ConnectionString = TxtDbstr.Text;
try
{
if (datalinks.PromptEdit(ref oconc))
{
TxtDbstr.Text = tmpconc.ConnectionString;
}
}
catch
{
tmpconc = (ADODB._Connection)datalinks.PromptNew();
if (tmpconc != null)
{
TxtDbstr.Text = tmpconc.ConnectionString;
}
}
}
}
private static string PromptConnectionString(string initial, IntPtr handle)
{
bool ok = false;
ADODB.Connection conn = null;
MSDASC.DataLinksClass dlinks = null;
try
{
// create objects we'll need
dlinks = new MSDASC.DataLinksClass();
conn = new ADODB.ConnectionClass();
// initialize object
if (initial != null && initial.Length > 0)
{
conn.ConnectionString = initial;
}
// show connection picker dialog
object obj = conn;
dlinks.hWnd = (int)handle; // << make it modal
ok = dlinks.PromptEdit(ref obj);
}
catch (Exception x)
{
FlexReportDesignerApp.MessageForm.Error(string.Format(FlexReportDesignerApp.Strings.SqlBuilderDialog.ErrCannotBuildConnFmt, x.Message));
}
// return what we got
return(ok ? conn.ConnectionString : initial);
}
// read list of catalog entries for the given catalog
private void PopulateMembers(string lib, string cat)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
lvMembers.BeginUpdate();
lvMembers.Items.Clear();
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!", ex);
}
if (currentServer.Length > 0 && ws != null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VCATALG view to get all of the catalog entries in the specified library/catalog
string selectclause = "select * from sashelp.vcatalg where libname='" + lib + "' and memname = '" + cat + "'";
adorecordset.Open(selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = adorecordset.Fields["objname"].Value.ToString();
lvi.SubItems.Add(adorecordset.Fields["objtype"].Value.ToString());
lvi.SubItems.Add(adorecordset.Fields["objdesc"].Value.ToString());
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["created"].Value.ToString()));
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["modified"].Value.ToString()));
lvi.ImageIndex = GetImageIndexForEntry(adorecordset.Fields["objtype"].Value.ToString());
lvi.Tag = string.Format("{0}.{1}.{2}.{3}", lib, cat, lvi.Text, adorecordset.Fields["objtype"].Value.ToString());
lvMembers.Items.Add(lvi);
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
lvMembers.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
private void GenerateXMLFromMDB(string pFilename)
{
mfSysTableExport = false;
mfSysTablePVEntityGenerator = false;
mDBDefinition = new dbdefinition();
mDBDefinition.generatedon = DateTime.Now;
// Generate entities
ArrayList alEntities = new ArrayList();
ADODB.Connection con = new ADODB.ConnectionClass();
try {
con.Open(DBServerHelper.getDatabaseOleDbConnectionString(pFilename), "", "", 0);
}
catch (Exception ex) {
throw new Exception("Unable to open database file:\n" + ex.Message);
}
ADOX.Catalog cat = new ADOX.Catalog();
cat.ActiveConnection = con;
mStatusHandler.InitStatus("Analyzing MDB table structure...", cat.Tables.Count);
int intTable = 0;
int intTableRealCount = 0;
foreach (ADOX.Table tbl in cat.Tables)
{
intTable++;
if (!IsSystemTableName(tbl.Name))
{
intTableRealCount++;
mStatusHandler.SetStatus("Analyzing table '" + tbl.Name + "'...", intTable);
alEntities.Add(GenerateEntity(tbl, con));
}
else
{
// Check if settings from older versions of SQLExporter or PVEntityGenerator exist
if (!mfSysTableExport)
{
mfSysTableExport = tbl.Name.ToLower().Equals("_export");
}
if (!mfSysTablePVEntityGenerator)
{
mfSysTablePVEntityGenerator = tbl.Name.ToLower().Equals("_pventitygenerator");
}
}
}
con.Close();
if (alEntities.Count != 0)
{
mDBDefinition.entities = (dbdefinitionEntity[])alEntities.ToArray(typeof(dbdefinitionEntity));
}
mStatusHandler.ClearStatus("Analyzed " + intTableRealCount + " table(s).");
}
// read list of catalogs for the given library (in the tree node)
private void PopulateCatalogs(TreeNode tn)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
tvLibsCats.BeginUpdate();
if (tn != null)
{
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!", ex);
}
if (currentServer.Length > 0 && ws != null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VMEMBER view to get names of all of the catalogs in the specified library
string selectclause = "select memname from sashelp.vmember where libname='" + tn.Text + "' and memtype in ('CATALOG')";
adorecordset.Open(selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
TreeNode cat = tn.Nodes.Add(adorecordset.Fields["memname"].Value.ToString());
cat.ImageIndex = (int)CatImages.Catalog;
cat.SelectedImageIndex = (int)CatImages.Catalog;
cat.Tag = "CATALOG";
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
}
tvLibsCats.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
public static ADODB.Connection GetMDBConnectionADODB(string pFilename)
{
ADODB.Connection con = new ADODB.ConnectionClass();
try {
con.Open(DBServerHelper.getDatabaseOleDbConnectionString(pFilename), "", "", 0);
return(con);
}
catch (Exception ex) {
throw new Exception("Unable to open database file:\n" + ex.Message);
}
}
/// <summary>
/// Creates the connection object and opens it with the specified connection string.
/// </summary>
private void OpenConnection(ref ADODB.Connection oConn)
{
ConnectionString = Config.Setting(ConnectionName);
if (Config.bSetting(ConnectionName + ".Encrypted"))
{
ConnectionString = CSAAWeb.Cryptor.Decrypt(ConnectionString, CSAAWeb.Constants.CS_STRING);
}
if (ConnectionString.ToLower().IndexOf("dsn=") == -1 &&
ConnectionString.ToLower().IndexOf("provider=") == -1)
{
ConnectionString = "Provider=SQLOLEDB.1;" + ConnectionString;
}
oConn = new ADODB.ConnectionClass();
oConn.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oConn, new object[] { ConnectionString });
}
private string PromptForConnectionString(string connectionString)
{
string newConnectionString = string.Empty;
try
{
ADODB.Connection connection = null;
MSDASC.DataLinks links = new MSDASC.DataLinksClass();
if ((connectionString == null) || (connectionString.Length == 0))
{
connectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost";
}
else
{
if (connectionString.IndexOf("Provider=SQLOLEDB.1") < 0)
{
connectionString = string.Concat("Provider=SQLOLEDB.1;", connectionString);
}
}
connection = new ADODB.ConnectionClass();
connection.ConnectionString = connectionString;
System.Object connectionObject = connection;
bool success = links.PromptEdit(ref connectionObject);
if (success)
{
connection = (ADODB.Connection)connectionObject;
newConnectionString = connection.ConnectionString;
// need to rip off the provider
string provider = string.Concat("Provider=", ((ADODB.Connection)connectionObject).Provider);
int index = newConnectionString.IndexOf(provider);
newConnectionString = newConnectionString.Remove(index, provider.Length + 1);
}
else
{
newConnectionString = connectionString;
}
}
catch
{
newConnectionString = string.Empty;
}
return(newConnectionString);
}
/// <summary>
/// Prompts the user and edits a connection string.
/// </summary>
public static string EditConnectionString(IWin32Window owner, string connString)
{
try
{
// create objects we'll need
var dlinks = new MSDASC.DataLinksClass();
var conn = new ADODB.ConnectionClass();
// sanity
if (dlinks == null || conn == null)
{
Warning(owner, @"Failed to create DataLinks.
Please check that oledb32.dll is properly installed and registered.
(the usual location is c:\Program Files\Common Files\System\Ole DB\oledb32.dll).");
return(connString);
}
// initialize object
if (!string.IsNullOrEmpty(connString))
{
conn.ConnectionString = connString;
}
// show connection picker dialog
object obj = conn;
if (owner != null)
{
dlinks.hWnd = (int)owner.Handle;
}
if (dlinks.PromptEdit(ref obj))
{
connString = conn.ConnectionString;
}
}
catch (Exception x)
{
Warning(owner, @"Failed to build connection string:
{0}", x.Message);
}
// done
return(connString);
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
Cursor.Current = Cursors.WaitCursor;
string tmpReturn = string.Empty;
MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
ADODB._Connection connection = null;
if (value.ToString() == String.Empty)
{
try
{
connection = (ADODB._Connection)dataLinks.PromptNew();
if (connection != null) //if cancel is pressed connection is null
{
tmpReturn = connection.ConnectionString;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
connection = new ADODB.ConnectionClass();
connection.ConnectionString = value.ToString();
object oConnection = connection;
try
{
if ((bool)dataLinks.PromptEdit(ref oConnection))
{
tmpReturn = connection.ConnectionString;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
Cursor.Current = Cursors.Default;
return(tmpReturn);
}
private ObservableCollection <NodeViewModel> GetGenericDatabaseObjectsAsync()
{
ObservableCollection <NodeViewModel> databaseObjects = new ObservableCollection <NodeViewModel>();
ADODB.Connection connection = null;
try
{
connection = new ADODB.ConnectionClass();
connection.Open(this.connectionString, "", "", (int)ADODB.ConnectOptionEnum.adConnectUnspecified);
ADOX.Catalog catalog = new ADOX.CatalogClass();
catalog.ActiveConnection = connection;
var tableContainer = new ComplexNodeViewModel("Tables");
var viewContainer = new ComplexNodeViewModel("Views");
PopulateTablesAndViews(catalog, tableContainer, viewContainer);
var storedProcedureContainer = new ComplexNodeViewModel("Stored Procedures");
PopulateStoredProcedures(catalog, connection, storedProcedureContainer);
databaseObjects.Add(tableContainer);
databaseObjects.Add(viewContainer);
databaseObjects.Add(storedProcedureContainer);
}
catch (Exception exception)
{
databaseObjects.Clear();
databaseObjects.Add(new SimpleNodeViewModel(exception.Message));
}
finally
{
if (null != connection)
{
try
{
connection.Close();
}
catch { }
}
}
return(databaseObjects);
}
private void btnConn_Click(object sender, System.EventArgs e)
{
ADODB._Connection cn;
MSDASC.DataLinks dl = new MSDASC.DataLinksClass();
try
{
cn = new ADODB.ConnectionClass();
cn.ConnectionString = this.txtConnString.Text + ";Provider=SQLOLEDB.1";
object oConnection = cn;
if ((bool)dl.PromptEdit(ref oConnection))
{
this.txtConnString.Text = cn.ConnectionString.Replace("Provider=SQLOLEDB.1;", string.Empty);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button3_Click(object sender, System.EventArgs e)
{
MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
ADODB._Connection connection;
if (connectionString.Text == String.Empty)
{
try
{
connection = (ADODB._Connection)dataLinks.PromptNew();
connectionString.Text = connection.ConnectionString.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
connection = new ADODB.ConnectionClass();
connection.ConnectionString = connectionString.Text;
object oConnection = connection;
try
{
if (dataLinks.PromptEdit(ref oConnection))
{
connectionString.Text = connection.ConnectionString;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void Form2_Load(object sender, System.EventArgs e)
{
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
ADODB.ConnectionClass cn = new ADODB.ConnectionClass();
cn.Open("Provider=MSOLAP; Datasource=LocalHost; Initial Catalog=FoodMart 2000","sa","",(int)ADODB.ConnectOptionEnum.adConnectUnspecified);
rs.ActiveConnection=cn;
rs.Open("select Order({[Store].Members},PROFIT,desc) on rows, {Crossjoin({[All products]},{ [Store Cost],[Store Sales]})} on columns from Sales ",cn,ADODB.CursorTypeEnum.adOpenDynamic ,ADODB.LockTypeEnum.adLockUnspecified,1);
MessageBox.Show(rs.Fields[0].Name);
MessageBox.Show(rs.GetString(ADODB.StringFormatEnum.adClipString ,rs.RecordCount," ","\n","<null>"));
//object obj = rs.GetRows(-1,1,null);
// rs.MoveFirst();
// String str=string.Empty;
// while(rs.EOF)
// {
// foreach(ADODB.Field f in rs.Fields )
// {
// str += f.Value ;
//
// }
// str +="\n";
// rs.MoveNext();
// }
//MessageBox.Show(str);
}
public DataTable GetViewData(string connectionString, ViewSchema view)
{
string sqlstring = @"
SELECT
*
FROM
" + view.Name;
ADODB.Connection cn = new ADODB.ConnectionClass();
cn.Open(connectionString, null, null, 0);
ADODB.Recordset rs = new ADODB.RecordsetClass();
rs.Open(sqlstring, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
DataTable viewData = new DataTable();
dataAdapter.Fill(viewData, rs);
rs.Close();
cn.Close();
return(viewData);
}
private void Form2_Load(object sender, System.EventArgs e)
{
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
ADODB.ConnectionClass cn = new ADODB.ConnectionClass();
cn.Open("Provider=MSOLAP; Datasource=LocalHost; Initial Catalog=FoodMart 2000", "sa", "", (int)ADODB.ConnectOptionEnum.adConnectUnspecified);
rs.ActiveConnection = cn;
rs.Open("select Order({[Store].Members},PROFIT,desc) on rows, {Crossjoin({[All products]},{ [Store Cost],[Store Sales]})} on columns from Sales ", cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockUnspecified, 1);
MessageBox.Show(rs.Fields[0].Name);
MessageBox.Show(rs.GetString(ADODB.StringFormatEnum.adClipString, rs.RecordCount, " ", "\n", "<null>"));
//object obj = rs.GetRows(-1,1,null);
// rs.MoveFirst();
// String str=string.Empty;
// while(rs.EOF)
// {
// foreach(ADODB.Field f in rs.Fields )
// {
// str += f.Value ;
//
// }
// str +="\n";
// rs.MoveNext();
// }
//MessageBox.Show(str);
}
/// <summary>
/// Creates a new DataConnection object
/// </summary>
public ADOConnectionWrapper()
: base()
{
this.pADOConnection = new ADODB.ConnectionClass();
}
private string PromptForConnectionString(string connectionString)
{
string newConnectionString = string.Empty;
try
{
ADODB.Connection connection = null;
MSDASC.DataLinks links = new MSDASC.DataLinksClass();
if ((connectionString == null) || (connectionString.Length == 0))
{
connectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost";
}
else
{
if (connectionString.IndexOf("Provider=SQLOLEDB.1") < 0)
{
connectionString = string.Concat("Provider=SQLOLEDB.1;", connectionString);
}
}
connection = new ADODB.ConnectionClass();
connection.ConnectionString = connectionString;
System.Object connectionObject = connection;
bool success = links.PromptEdit(ref connectionObject);
if (success)
{
connection = (ADODB.Connection)connectionObject;
newConnectionString = connection.ConnectionString;
// need to rip off the provider
string provider = string.Concat("Provider=", ((ADODB.Connection)connectionObject).Provider);
int index = newConnectionString.IndexOf(provider);
newConnectionString = newConnectionString.Remove(index, provider.Length + 1);
}
else
{
newConnectionString = connectionString;
}
}
catch
{
newConnectionString = string.Empty;
}
return newConnectionString;
}
private void toolStripButtonConnect_Click(object sender, EventArgs e)
{
MSDASC.DataLinks mydlg = new MSDASC.DataLinks();
CDataAdaHelper OleCon = new CDataAdaHelper();
ADODB._Connection ADOcon;
bool bEdit = false;
//OleCon.ConnectString = CAppOption.m_sDbConnectString;
if (CAppOption.m_sDbConnectString == String.Empty)
{
try
{
//Cast the generic object that PromptNew returns to an ADODB._Connection.
ADOcon = (ADODB._Connection)mydlg.PromptNew();
OleCon.ConnectString = ADOcon.ConnectionString;
bEdit = true;
}
catch (Exception ex)
{
CGlobalInstance.Instance.WriteErrorLog(YyLogger.LogSeverity.error, "设置数据库连接", ex);
}
}
else
{
ADOcon = new ADODB.ConnectionClass();
ADOcon.ConnectionString = CAppOption.m_sDbConnectString;
//set local COM compatible data type
object oConnection = ADOcon;
try
{
//prompt user to edit the given connect string
if ((bool)mydlg.PromptEdit(ref oConnection))
{
//处理
}
OleCon.ConnectString = ADOcon.ConnectionString;
bEdit = true;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("服务连接不成功，请重新设置连接!");
CGlobalInstance.Instance.WriteErrorLog(YyLogger.LogSeverity.error, "设置数据库连接", ex);
}
}
if (bEdit == true)
{
try
{
//OleCon.Db.Open();
OleCon.Open();
if (OleCon.IsOpen)
{
CAppOption.m_sDbConnectString = OleCon.ConnectString;
//OleCon.Db.Close();
OleCon.Close();
CGlobalInstance.Instance.DbAdaHelper.ConnectString = CAppOption.m_sDbConnectString;
CAppOption.SaveData();
}
else
{
MessageBox.Show("连接无效,无法连接数据库");
}
}
catch (Exception ex)
{
MessageBox.Show("连接无效,无法连接数据库（注意请选择允许保存密码）！");
CGlobalInstance.Instance.WriteErrorLog(YyLogger.LogSeverity.error, "设置数据库连接", ex);
}
}
}
/// <summary>
/// Mose Like Excel
/// Just different in Connection String
/// </summary>
/// <param name="csvFile"></param>
/// <param name="sdfFile"></param>
/// <param name="p"></param>
/// <param name="p_4"></param>
/// <returns></returns>
internal static bool ConvertCSVToSdf(string csvFile, string sdfFile, bool NeedCopyData, string targetDbPwd, bool isFirstRowIsColumnName)
{
bool result = false;
if (!File.Exists(csvFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.CSV).X_Handler;
srcEngine.Open(new LoginInfo_CSV()
{
Database = csvFile, IsFirstRowIsColumnName = isFirstRowIsColumnName
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
List <string> tableList = srcEngine.GetTableListInDatabase();
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
//IF the ce database not existed ,then create it .
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite
});
List <string> targetDbList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDbList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
string strconnection = CoreEA.ConnSTR.DbConnectionString.TxtFile.OleDb_DelimitedColumns(csvFile, true);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("{0} {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
throw new Exception(string.Format("Create table {0} error", tableName));
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
}
return(result);
}
public static bool ImportDatabase(string filename)
{
// check if file exists
if(!File.Exists(filename))
{
MessageBox.Show("File does not exist: "+filename);
return false;
}
Exit(); // force all tables to save
Init(); // reopen database connection
// verify tables exist
ADODB.Connection adodb_conn = new ADODB.ConnectionClass();
adodb_conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data source="+filename, "", "", 0);
Catalog cat = new CatalogClass();
cat.ActiveConnection = adodb_conn;
ArrayList a = new ArrayList();
foreach(Table t in cat.Tables)
a.Add(t.Name);
// all tables found -- close the catalog
adodb_conn.Close();
// open old database
OleDbConnection c = new OleDbConnection();
c.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Persist Security Info=False;" +
"Data source="+filename;
c.Open();
// copy table data
try
{
// State table
if(a.BinarySearch("State") >= 0)
CopyFormTable("State", c);
// Options Table
if(a.BinarySearch("Options") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Options", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "Options");
foreach(DataRow dr in ds.Tables["Options"].Rows)
{
DataRow[] found = dsOptions.Tables["Options"].Select("ID = '"+dr["ID"]+"'");
if(found.Length > 0)
found[0]["SaveValue"] = dr["SaveValue"];
else
{
DataRow new_dr = dsOptions.Tables["Options"].NewRow();
new_dr["ID"] = dr["ID"];
new_dr["SaveValue"] = dr["SaveValue"];
dsOptions.Tables["Options"].Rows.Add(new_dr);
}
}
optionsAdapter.Update(dsOptions, "Options");
}
// CWX Table
if(a.BinarySearch("CWX") >= 0)
CopyFormTable("CWX", c);
// GroupList Table
if(a.BinarySearch("GroupList") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM GroupList", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "GroupList");
OleDbDataAdapter old_adapter = new OleDbDataAdapter("SELECT * FROM GroupList", conn);
DataSet old_ds = new DataSet();
old_adapter.Fill(old_ds, "GroupList");
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(old_adapter);
foreach(DataRow dr in ds.Tables["GroupList"].Rows)
{
DataRow[] found = old_ds.Tables["GroupList"].Select("GroupID = '"+dr["GroupID"]+"'");
if(found.Length > 0)
found[0]["GroupName"] = dr["GroupName"];
else
{
DataRow new_dr = old_ds.Tables["GroupList"].NewRow();
new_dr[0] = dr[0];
new_dr[1] = dr[1];
old_ds.Tables["GroupList"].Rows.Add(new_dr);
}
}
old_adapter.Update(old_ds, "GroupList");
}
// BandStack Table
if(a.BinarySearch("BandStack") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM BandStack", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "BandStack");
foreach(DataRow dr in dsBandStack.Tables["BandStack"].Rows)
dr.Delete();
foreach(DataRow dr in ds.Tables["BandStack"].Rows)
{
DataRow new_dr = dsBandStack.Tables["BandStack"].NewRow();
for(int i=0; i<ds.Tables["BandStack"].Columns.Count; i++)
new_dr[i] = dr[i];
dsBandStack.Tables["BandStack"].Rows.Add(new_dr);
}
bandStackAdapter.Update(dsBandStack, "BandStack");
}
/*
// BandText Table
if(a.BinarySearch("BandText") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM BandText", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "BandText");
foreach(DataRow dr in ds.Tables["BandText"].Rows)
{
DataRow[] found = dsBandText.Tables["BandText"].Select("Low = '"+dr["Low"]+"'");
if(found.Length > 0)
{
found[0]["High"] = dr["High"];
found[0]["Name"] = dr["Name"];
found[0]["Transmit"] = dr["Transmit"];
}
else
{
DataRow new_dr = dsBandText.Tables["BandText"].NewRow();
for(int i=0; i<ds.Tables["BandText"].Columns.Count; i++)
new_dr[i] = dr[i];
dsBandText.Tables["BandText"].Rows.Add(new_dr);
}
}
bandTextAdapter.Update(dsBandText, "BandText");
}
*/
// Memory Table
if(a.BinarySearch("Memory") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Memory", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "Memory");
foreach(DataRow dr in dsMemory.Tables["Memory"].Rows)
dr.Delete();
foreach(DataRow dr in ds.Tables["Memory"].Rows)
{
DataRow new_dr = dsMemory.Tables["Memory"].NewRow();
for(int i=0; i<ds.Tables["Memory"].Columns.Count; i++)
new_dr[i] = dr[i];
dsMemory.Tables["Memory"].Rows.Add(new_dr);
}
memoryAdapter.Update(dsMemory, "Memory");
}
// UCB Table
if(a.BinarySearch("UCB") >= 0)
CopyFormTable("UCB", c);
// TxProfile Table
if(a.BinarySearch("TxProfile") >= 0)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM TxProfile", c);
DataSet ds = new DataSet();
adapter.Fill(ds, "TxProfile");
foreach(DataRow dr in dsTxProfile.Tables["TxProfile"].Rows)
dr.Delete();
foreach(DataRow dr in ds.Tables["TxProfile"].Rows)
{
if((string)dr["Name"] == "ESSB+") dr["Name"] = "ESSB Plus";
DataRow new_dr = dsTxProfile.Tables["TxProfile"].NewRow();
new_dr["DXOn"] = false;
new_dr["DXLevel"] = 3;
for(int i=0; i<ds.Tables["TxProfile"].Columns.Count; i++)
{
string col_name = ds.Tables["TxProfile"].Columns[i].Caption;
try
{
new_dr[col_name] = dr[i];
}
catch(Exception)
{
// do nothing ... ignore changed columns
}
}
dsTxProfile.Tables["TxProfile"].Rows.Add(new_dr);
}
txProfileAdapter.Update(dsTxProfile, "TxProfile");
}
// XVTR Table
if(a.BinarySearch("XVTR") >= 0)
CopyFormTable("XVTR", c);
// Production Table
if(a.BinarySearch("ProdTest") >= 0)
CopyFormTable("ProdTest", c);
if(a.BinarySearch("WaveOptions") >= 0)
CopyFormTable("WaveOptions", c);
if(a.BinarySearch("EQForm") >= 0)
CopyFormTable("EQForm", c);
if(a.BinarySearch("FWCAnt") >= 0)
CopyFormTable("FWCAnt", c);
if(a.BinarySearch("FWCATU") >= 0)
CopyFormTable("FWCATU", c);
if(a.BinarySearch("FWCMixer") >= 0)
CopyFormTable("FWCMixer", c);
CheckBandTextValid();
c.Close();
return true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message+"\n\n\n"+ex.StackTrace);
return false;
}
}
private void button3_Click(object sender, System.EventArgs e)
{
MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass();
ADODB._Connection connection;
if (connectionString.Text == String.Empty)
{
try
{
connection = (ADODB._Connection) dataLinks.PromptNew();
connectionString.Text = connection.ConnectionString.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else
{
connection = new ADODB.ConnectionClass();
connection.ConnectionString = connectionString.Text;
object oConnection = connection;
try
{
if (dataLinks.PromptEdit(ref oConnection))
{
connectionString.Text = connection.ConnectionString;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
/// <summary>
/// Creates a new DataConnection object
/// </summary>
public ADOConnectionWrapper()
: base()
{
this.pADOConnection = new ADODB.ConnectionClass();
}
/*
* Logs a user in when they click the login button.
* ANDREAS---------------------
*
*/
private void loginButton_Click(object sender, EventArgs e)
{
//Console.WriteLine("button has been pressed");
MySqlCommand cmd = null;
MySqlConnection cn = null;
MySqlDataReader dr = null;
//if the button is currently used to log a user in...
if (buttonIsLogin)
{
//When we have the login db done, check the username/password combo vs the
//database. Remove the dummy checks when you add the database checks. If the username
//or password is wrong, clear the textfields and bring up a popup like is already done below.
//also when they login, change the 'buttonIsLogin' variable to false, so the button changes to logout.
//Change the userTypeBox to reflect the current level of access the login has.
//(this means the query should check the successfully logged in user in the database
//and see what type they are [manager, employee, or customer] to display)
adodb = new ADODB.ConnectionClass();
try
{
//get user's input
currentUserEmail = emailBox.Text;
currentUserPassword = passwordBox.Text;
// Console.WriteLine("u: "+currentUserEmail);
// Console.WriteLine("p: "+currentUserPassword);
string sql = "select ID from USER_INFO where Email='" + currentUserEmail + "' and Password ='******'";
// Console.WriteLine("sql=" + sql);
adodb.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='Login.mdb';", "", "", 0);
// Console.WriteLine("connected to login db");
recset = new ADODB.RecordsetClass();
// Console.WriteLine("recset obj created");
recset.Open(sql, adodb, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, 0); //System.Runtime.InteropServices.COMException
// Console.WriteLine("recset opened");
//if recordset is empty the user name and password combination does not exist (see the sql statement)
//else record is found
if (recset.EOF)
{
MessageBox.Show("Invalid user name or password.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
clearForm();
}
else
{
// Console.WriteLine("in the else statement");
//assign the user's id to a variable
string id = recset.Fields["ID"].Value.ToString();
Console.WriteLine("valid user with ID: " + id);
// connection to the MySql database starts here
cn = new MySqlConnection();//MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
// Console.WriteLine("sql connection instantiated");
cn.ConnectionString = MyConString;
// Console.WriteLine("sonnection string created");
cn.Open();
// Console.WriteLine("connection to sql opened");
// checking user type starts here
cmd = cn.CreateCommand();
// Console.WriteLine("sql command instantiated");
cmd.CommandText = "select ID from Manager where ID=" + id;
// Console.WriteLine("sql statement assigned to cmd");
dr = cmd.ExecuteReader();
// Console.WriteLine("sql statement executed via datareader");
bool manager = false;
//true if such record exists wich means that he is an employee
if (dr.Read())
{
// Console.WriteLine("person is manager");
showForm("Manager");
populateQueueList();
userTypeBox.Text = "Manager";
buttonIsLogin = false;
loginButton.Text = "Log out";
manager = true;
}//otherwise it ignores it and checks the other two alternatives
dr.Close();
if (manager == false)
{
cmd = null;
cmd = cn.CreateCommand();
//change the statement so we can check if he is a manager
cmd.CommandText = "select ID from Employee where ID=" + id;
// Console.WriteLine("new sql that checks manager");
dr = cmd.ExecuteReader();
// Console.WriteLine("execute new manager statement");
// true if such record exists which means that he is a manager
if (dr.Read())
{
// Console.WriteLine("person is employee");
showForm("Employee");
populateQueueList();
userTypeBox.Text = "Employee";
buttonIsLogin = false;
loginButton.Text = "Log out";
}
else // otherwise he is a customer
{
// Console.WriteLine("person is customer");
showForm("Customer");
populateQueueList();
fillPersonalInfoForCustomer();
userTypeBox.Text = "Customer";
buttonIsLogin = false;
loginButton.Text = "Log out";
}
}
dr.Close();
cn.Close();
}// end of else statement
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);//better message box?
}
}
else
{
//(Don't need to change this part)
//button is being used to log out, so log out when you press it...
buttonIsLogin = true;
loginButton.Text = "Log in";
showForm("Login");
clearForm();
currentUserEmail = "";
}
}
// read list of catalog entries for the given catalog
private void PopulateMembers(string lib, string cat)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
lvMembers.BeginUpdate();
lvMembers.Items.Clear();
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!",ex);
}
if (currentServer.Length>0 && ws!=null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VCATALG view to get all of the catalog entries in the specified library/catalog
string selectclause = "select * from sashelp.vcatalg where libname='" + lib + "' and memname = '" + cat + "'";
adorecordset.Open( selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int) ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = adorecordset.Fields["objname"].Value.ToString();
lvi.SubItems.Add(adorecordset.Fields["objtype"].Value.ToString());
lvi.SubItems.Add(adorecordset.Fields["objdesc"].Value.ToString());
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["created"].Value.ToString()));
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["modified"].Value.ToString()));
lvi.ImageIndex = GetImageIndexForEntry(adorecordset.Fields["objtype"].Value.ToString());
lvi.Tag = string.Format("{0}.{1}.{2}.{3}", lib, cat, lvi.Text, adorecordset.Fields["objtype"].Value.ToString());
lvMembers.Items.Add(lvi);
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
lvMembers.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
/// <summary>
/// Accesss to sqlce
/// </summary>
/// <param name="p"></param>
/// <param name="sdfFile"></param>
public static bool SyncMdbToSdf(string mdbFile, string sdfFile, bool NeedCopyData, string targetDbPwd)
{
bool result = false;
if (!File.Exists(mdbFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.OleDb).X_Handler;
srcEngine.Open(new LoginInfo_Oledb()
{
Database = mdbFile
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
//Filter system table
List <string> tableList = new List <string>();
foreach (string item in srcEngine.GetTableListInDatabase())
{
if (!item.StartsWith("MSys"))
{
tableList.Add(item);
}
}
if (tableList == null)
{
"ImportData_NoTable".GetFromResourece().Notify();
return(false);
}
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile, IsEncrypted = false, IsCaseSensitive = false
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = targetDbPwd
});
List <string> targetDBList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDBList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
//if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
//{
// sqlCeTableName= sqlCeTableName.Replace(" ", "");
//}
string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}", mdbFile);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "Admin", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("[{0}] {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
return(false);
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
//((SqlCeDatabase)destEngine.DbHandler).CloseSharedConnection();
}
return(result);
}
// read list of catalogs for the given library (in the tree node)
private void PopulateCatalogs(TreeNode tn)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
tvLibsCats.BeginUpdate();
if (tn!=null)
{
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!",ex);
}
if (currentServer.Length>0 && ws!=null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VMEMBER view to get names of all of the catalogs in the specified library
string selectclause = "select memname from sashelp.vmember where libname='" + tn.Text + "' and memtype in ('CATALOG')";
adorecordset.Open( selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int) ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
TreeNode cat = tn.Nodes.Add(adorecordset.Fields["memname"].Value.ToString());
cat.ImageIndex = (int)CatImages.Catalog;
cat.SelectedImageIndex = (int)CatImages.Catalog;
cat.Tag = "CATALOG";
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
}
tvLibsCats.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
private bool IsColumnNullable(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
int isnullable = Convert.ToInt32(DbSession.Default.FromSql("select isnullable from syscolumns where [name] = '" + column + "' and id = " + tableid).ToScalar());
return isnullable == 1;
}
else if (radioOracle.Checked)
{
string isnullable = DbSession.Default.FromSql("select NULLABLE,COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE from user_tab_columns "
+ " where upper(table_name) ='" + name.ToUpper() + "' and upper(column_name) = '" + column.ToUpper() + "'").ToScalar<string>();
return isnullable.ToUpper() == "Y";
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return Convert.ToBoolean(row["IS_NULLABLE"].ToString());
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return row["IS_NULLABLE"].ToString().ToUpper() == "YES";
}
}
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaColumns }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column)
{
bool result = Convert.ToBoolean(rs.Fields["IS_NULLABLE"].Value);
rs.Close();
conn.Close();
return result;
}
rs.MoveNext();
}
}
return false;
}
private bool IsColumnReadOnly(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
byte status = Convert.ToByte(DbSession.Default.FromSql("select status from syscolumns where [name] = '" + column + "' and id =" + tableid).ToScalar());
return status == 128;
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaColumns }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column && ((int)rs.Fields["DATA_TYPE"].Value) == 3 && Convert.ToByte(rs.Fields["COLUMN_FLAGS"].Value) == 90)
{
rs.Close();
conn.Close();
return true;
}
rs.MoveNext();
}
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column && row["DATA_TYPE"].ToString() == "INTEGER")
{
return true;
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column && row["EXTRA"].ToString().ToUpper() == "AUTO_INCREMENT")
{
return true;
}
}
}
return false;
}
private bool IsColumnPrimaryKey(string name, string column)
{
if (radioSql.Checked || radioSql2005.Checked)
{
int tableid = Convert.ToInt32(DbSession.Default.FromSql("select id from sysobjects where [name] = '" + name + "'").ToScalar());
DataSet ds = DbSession.Default.FromSql("select a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' "
+ " and d.name<>'dtproperties' where (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND "
+ " (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) "
+ " AND (name = a.name))))))) AND (xtype = 'PK'))>0 and d.id = " + tableid).ToDataSet();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][0].ToString() == column)
{
return true;
}
}
}
else if (radioOracle.Checked)
{
DataSet ds = DbSession.Default.FromSql("select col.column_name from user_constraints con,user_cons_columns col where con.constraint_name = "
+ " col.constraint_name and con.constraint_type='P' and upper(col.table_name) = '" + name.ToUpper() + "'").ToDataSet();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][0].ToString() == column)
{
return true;
}
}
}
else if (radioAccess.Checked)
{
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rs = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaPrimaryKeys }) as ADODB.Recordset;
rs.Filter = "TABLE_NAME='" + name + "'";
while (!rs.EOF)
{
if (rs.Fields["COLUMN_NAME"].Value.ToString() == column)
{
rs.Close();
conn.Close();
return true;
}
rs.MoveNext();
}
}
else if (radioSQLite.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return Convert.ToBoolean(row["PRIMARY_KEY"].ToString());
}
}
}
else if (radioMySQL.Checked)
{
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table = conn.GetSchema("COLUMNS");
conn.Close();
foreach (DataRow row in table.Rows)
{
if (row["TABLE_NAME"].ToString() == name && row["COLUMN_NAME"].ToString() == column)
{
return row["COLUMN_KEY"].ToString().ToUpper() == "PRI";
}
}
}
return false;
}
private void btnConnect_Click(object sender, EventArgs e)
{
if (txtConnStr.Text.Trim().Length == 0)
{
MessageBox.Show("连接字符串不能为空!");
return;
}
if (btnConnect.Text == "断开服务器连接")
{
chkCreateAssembly.Checked = false;
chkCreateAssembly_CheckedChanged(sender, e);
EnableGenEntity(false);
return;
}
RefreshConnectionStringAutoComplete();
DataSet dsTables = null;
DataSet dsViews = null;
try
{
if (radioSql.Checked || radioSql2005.Checked)
{
DbProvider dbProvider = new SqlServerProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
if (radioSql2005.Checked)
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams' order by [name]").ToDataSet();
}
else
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
if (radioSql2005.Checked)
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' order by [name]").ToDataSet();
}
else
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioOracle.Checked)
{
DbProvider dbProvider = new OracleProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
dsTables = DbSession.Default.FromSql("select table_name Name from user_tables").ToDataSet();
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
dsViews = DbSession.Default.FromSql("select view_name Name from user_views").ToDataSet();
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioAccess.Checked)
{
DbProvider dbProvider = new MsAccessProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rsTables = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaTables }) as ADODB.Recordset;
ADODB.Recordset rsViews = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaViews }) as ADODB.Recordset;
while (!rsViews.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "VIEW")
{
views.Items.Add(rsViews.Fields["TABLE_NAME"].Value.ToString());
}
rsViews.MoveNext();
}
while (!rsTables.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "TABLE")
{
tables.Items.Add(rsTables.Fields["TABLE_NAME"].Value.ToString());
}
rsTables.MoveNext();
}
rsTables.Close();
rsViews.Close();
conn.Close();
}
else if (radioSQLite.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.SQLite, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString().ToUpper() == "TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
else if (radioMySQL.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.MySql, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString() == "BASE TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
EnableGenEntity(true);
}
catch (Exception ex)
{
EnableGenEntity(false);
MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
