/// <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()); } } }
/* * 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); } }