public object[] db_access(string strSQL) { ADODB.Connection objCon; ADODB.Recordset objRec; object[,] dataRows; object[] dataSuite; string strCon; objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //establish the connection string and open the database connection strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; objCon.Open(strCon); //execute the SQL and return the recrodset of results objRec = objCon.Execute(strSQL, out missing, 0); //populate a two dinmensional object array with the results dataRows = objRec.GetRows(); //get a one dimensional array that can be placed into the Test Suite dropdown dataSuite = thinArray(dataRows); //close the recordset objRec.Close(); //close the database connection objCon.Close(); return dataSuite; }
override internal void LoadAll() { ADODB.Connection cnn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); ADOX.Catalog cat = new ADOX.Catalog(); // Open the Connection cnn.Open(dbRoot.ConnectionString, null, null, 0); cat.ActiveConnection = cnn; ADOX.Procedure proc = cat.Procedures[this.Procedure.Name]; // Retrieve Parameter information rs.Source = proc.Command as ADODB.Command; rs.Fields.Refresh(); Pervasive.PervasiveResultColumn resultColumn; if(rs.Fields.Count > 0) { int ordinal = 0; foreach(ADODB.Field field in rs.Fields) { resultColumn = this.dbRoot.ClassFactory.CreateResultColumn() as Pervasive.PervasiveResultColumn; resultColumn.dbRoot = this.dbRoot; resultColumn.ResultColumns = this; resultColumn.name = field.Name; resultColumn.ordinal = ordinal++; resultColumn.typeName = field.Type.ToString(); this._array.Add(resultColumn); } } cnn.Close(); }
public void TrazerFaturamento(string JsonChamada) { SerializerFO Serializer = new SerializerFO(); var SQL = new LibOrgm.SQL(); var cn = new ADODB.Connection(); try { Context.Response.Clear(); Context.Response.ContentType = "application/json"; SQL.AbrirConexao(cn); var TrazerFaturamento = FatoFaturamentoIVELDA.GetFaturamentoIVEL(JsonChamada, cn); Context.Response.Write(Serializer.Serializador(TrazerFaturamento)); } catch (Exception Ex) { Context.Response.Write(TratarErro(Ex)); } finally { SQL.FecharConexao(cn); } }
public void LoginUsuario(string JsonChamada) { SerializerFO Serializer = new SerializerFO(); var SQL = new LibOrgm.SQL(); var cn = new ADODB.Connection(); try { Context.Response.Clear(); Context.Response.ContentType = "application/json"; SQL.AbrirConexao(cn); var LoginUsuario = UsuariosFO.LogarUsuario(JsonChamada, cn); Context.Response.Write(Serializer.Serializador(LoginUsuario)); } catch (Exception Ex) { Context.Response.Write(TratarErro(Ex)); } finally { SQL.FecharConexao(cn); } }
public bool Execute(string sql) { bool stat = false; object obj; try { if (Connected() == true) { if (cnn == null) { cnn = new ADODB.Connection(); } if (cnn.State == 0) { cnn.Open(cnn_str, "", "", 0); } if (cnn.State == 1) { cnn.Execute(sql, out obj, 0); cnn.Close(); stat = true; } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return(stat); }
private static void PopulateStoredProcedures(ADOX.Catalog catalog, ADODB.Connection connection, NodeViewModel storedProcedureContainer) { foreach (ADOX.Procedure procedure in catalog.Procedures) { storedProcedureContainer.Children.Add(new StoredProcedureNodeViewModel(procedure, connection, storedProcedureContainer)); } }
public void ConnectionTestDB() { ADODB.Connection conn = null; AdoHelper helper = null; AdoHelper helperRef = null; AdoHelper newHelper = null; try { helper = AdoHelper.ThreadInstance("DSN={2};DB={2};UID={0};PWD={1};HOST=192.6.4.*;PORT=5900", DbProviderType.Odbc); conn = helper.Connection(); //是否成功连接到数据库。 Assert.AreEqual(conn.State, 1); helperRef = AdoHelper.ThreadInstance("DSN={2};DB={2};UID={0};PWD={1};HOST=192.6.4.*;PORT=5900", DbProviderType.Odbc); //线程实例是否有效 Assert.AreEqual(helper, helperRef); using (newHelper = AdoHelper.ThreadInstance("DSN={2};DB={2};UID={0};PWD={1};HOST=192.6.4.*;PORT=5900", DbProviderType.Odbc)) { //一个新的helper Assert.AreNotEqual(helper, newHelper); } } catch (Exception ex) { Console.WriteLine(string.Format("{0}\n{1}", ex.Message, ex.ToString())); throw; } finally { helper.Dispose(); //是否正常关闭了数据库。 Assert.AreEqual(conn.State, 0); } }
public static bool SameOperNumDiffNameExists(string opName, int opNum, int prodId, int opId, string connectionString, LogFiles logFiles) { bool exists = false; ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rec = new ADODB.Recordset(); opName = opName.ToUpper(); bool adoOpen = DbUse.OpenAdo(conn, connectionString); string queryString = "SELECT OpID, OpNam, OpNum, Prodfore FROM tblOper WHERE Prodfore = " + prodId + " AND OpNam <> '" + opName + "' AND OpNum = " + opNum + " AND OpID <> " + opId; bool recOpen = DbUse.OpenAdoRec(conn, rec, queryString); try { if (adoOpen && recOpen) { exists = !rec.EOF; } } catch (Exception ex) { logFiles.ErrorLog(ex); } finally { DbUse.CloseAdoRec(rec); DbUse.CloseAdo(conn); } return(exists); }
public static bool TableExist(string dbName, string tblName, string pwd = "") { try { ADOX.Catalog cat = new ADOX.Catalog(); ADODB.Connection cn = new ADODB.Connection(); cn.Open(Provider + @"Data Source=" + dbName + ";" + Password + "=" + pwd); cat.ActiveConnection = cn; for (int i = 0; i < cat.Tables.Count; ++i) { ADOX.Table t = cat.Tables[i]; if (t.Name == tblName) { return(true); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(false); } return(false); }
public static string ReadExcel(string FilePath, int C, int R) { ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); string Sql = "Select * From [Sheet1$]"; //Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码 conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + FilePath); rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1); object ary = rs.GetRows(); object[,] aa = (object[, ])ary; rs.Close(); conn.Close(); if ((string)aa[C - 1, R - 2] == null) { return(""); } return((string)aa[C - 1, R - 2]); }
public static bool OpenAdoMysql(ADODB.Connection conn) { bool opened; // close the connection if it already exists CloseAdo(conn); try { // gwwd - nont here at start ... conn.DefaultDatabase = ""; //conn.ConnectionString = "Provider=MySQLProv;Data Source=webmpx; Persist Security Info = False"; //conn.ConnectionString = "Provider=MySQLProv;" + GetUserDatabaseConnection().ToString(); /* using (OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString)) { * connection.Open(); * */ conn.ConnectionString = ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString; conn.Mode = (ADODB.ConnectModeEnum)ADODB.CursorLocationEnum.adUseServer; conn.Open(); opened = true; } catch (Exception e) { // MyUtilities.MsgBox("Error in opening database connection: " + conn.ConnectionString + " (" + e.Message + ")"); opened = false; LogFiles logFiles = new LogFiles(); logFiles.ErrorLog(e); //throw new Exception(e.Message); } return(opened); }
private void ErgebnisseForm_LoadZugang(ADODB.Connection cn, ADODB.Recordset rs) { rs.Open("Select * From Lagerzugang", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid1.Rows.Add(); metroGrid1.Rows[i].Cells["Artikelnummer_Zugang"].Value = rs.Fields["article"].Value; metroGrid1.Rows[i].Cells["Bestellperiode_Zugang"].Value = rs.Fields["orderperiod"].Value; // Überprüfung auf Eil oder Normalbestellung if (Convert.ToInt32(rs.Fields["modus"].Value) == 4) { metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Eil"; } else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5) { metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Normal"; } metroGrid1.Rows[i].Cells["Lieferzeit_Zugang"].Value = rs.Fields["Lieferzeit"].Value; metroGrid1.Rows[i].Cells["Menge_Zugang"].Value = rs.Fields["amount"].Value; metroGrid1.Rows[i].Cells["Preis_Zugang"].Value = rs.Fields["price"].Value; rs.MoveNext(); ++i; } i = 0; rs.Close(); }
/// <summary> /// Extracts the data contained in the ADO recordset and stores it in /// XmlSource. If there is another recordset, then the first was actually /// Extended attributes metadata, so that document is move to XmlExtendedAttributes, /// and the data in the second recordset is store into XmlSource. /// </summary> private void GetSource() { ADODB.Connection oConn = null; if (oRs == null) { OpenRecordset(CreateCommand(ref oConn)); } MSXML2.DOMDocument D1 = new MSXML2.DOMDocumentClass(); // load the 1st dataset oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML); XmlSource = new XmlDocument(); XmlSource.LoadXml(D1.xml); object RecordsAffected; oRs = oRs.NextRecordset(out RecordsAffected); if (oRs != null && oRs.State == 1) { XmlExtendedAttributes = XmlSource; // Load the 2nd dataset oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML); XmlSource = new XmlDocument(); XmlSource.LoadXml(D1.xml); } if (oRs != null && oRs.State == 1) { oRs.Close(); } if (oConn != null) { oConn.Close(); } }
private void ErgebnisseForm_LoadBestellungen(ADODB.Connection cn, ADODB.Recordset rs) { rs.Open("Select * From myOrderlist", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid1.Rows.Add(); metroGrid1.Rows[i].Cells["Artikelnummer_Bestellung"].Value = rs.Fields["article"].Value; metroGrid1.Rows[i].Cells["Menge_Bestellung"].Value = rs.Fields["quantity"].Value; // Überprüfung auf Eil oder Normalbestellung if (Convert.ToInt32(rs.Fields["modus"].Value) == 4) { metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Eil"; } else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5) { metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Normal"; } rs.MoveNext(); ++i; } i = 0; rs.Close(); }
static List <MyParagraph> getTitleParagraphs(ADODB.Connection cnn, UInt64 titleId) { var paragraphLst = new List <MyParagraph>(); //get title var qry = "select * from paragraphs where titleId = ? "; var rst = new ADODB.Recordset(); rst.Open(qry.Replace("?", titleId.ToString()), cnn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, (int)ADODB.CommandTypeEnum.adCmdText); rst.MoveFirst(); while (!rst.EOF) { var par = new MyParagraph(); par.order = Convert.ToInt32(rst.Fields["order"].Value); par.alignment = Convert.ToInt32(rst.Fields["alignment"].Value); par.leftIndent = Convert.ToInt32(rst.Fields["leftIndent"].Value); par.fontSize = Convert.ToInt32(rst.Fields["fontSize"].Value); par.fontBold = Convert.ToInt32(rst.Fields["fontBold"].Value); par.fontItalic = Convert.ToInt32(rst.Fields["fontItalic"].Value); par.content = Convert.ToString(rst.Fields["content"].Value); paragraphLst.Add(par); rst.MoveNext(); } rst.Close(); return(paragraphLst); }
public static void CreateFile(string fileName, string pwd = "") { string dir = Path.GetDirectoryName(fileName); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } if (!File.Exists(fileName)) { ADOX.Catalog cat = new ADOX.Catalog(); cat.Create(Provider + @"Data Source=" + fileName + ";" + Password + "=" + pwd); ADODB.Connection con = cat.ActiveConnection as ADODB.Connection; if (con != null) { con.Close(); } cat.ActiveConnection = null; Marshal.ReleaseComObject(cat); cat = null; GC.Collect(); } }
/// <summary> /// This function will create the database that will be used for retreiving the PRPO data. /// </summary> /// <exception cref="DatabaseCreationFailureException"></exception> /// <returns> /// A boolean value indicating whether or not he database was created. /// </returns> public static bool CreateAccessDB() { bool result = false; ADOX.Catalog cat = new ADOX.Catalog(); if (!File.Exists(AI.Path)) { try { cat.Create(AI.connectionString()); ADODB.Connection con = cat.ActiveConnection as ADODB.Connection; if (con != null) { con.Close(); } result = true; AccessDatabaseUtils.US_PRPO_TableExists = false; AccessDatabaseUtils.MX_PRPO_TableExists = false; } catch (Exception) { throw new DatabaseCreationFailureException("There was an error while creating the MS Access Database."); } } return(result); }
private void cmdSelComp_Click(System.Object eventSender, System.EventArgs eventArgs) { //frmSelComp.show 1 ADODB.Connection cn = default(ADODB.Connection); ADODB.Recordset rj = default(ADODB.Recordset); loadDBStr = My.MyProject.Forms.frmSelComp.loadDB(); if (string.IsNullOrEmpty(loadDBStr)) { } else { cn = modRecordSet.openSComp(ref loadDBStr); if (cn == null) { } else { rj = modRecordSet.getRSwaitron(ref "SELECT Company_Name FROM Company;", ref cn); lblSComp.Text = rj.Fields("Company_Name").Value; cmdAdd.Enabled = true; cmdDelete.Enabled = true; } } }
public static void ReadDB(string tableName, string fileName) { string filePath = @"D:\xl2cad.mdb"; if (File.Exists(filePath)) { //select * into 建立 新的表。 //[[Excel 8.0;database= excel名].[sheet名] 如果是新建sheet表不能加$,如果向sheet里插入数据要加$. //sheet最多存储65535条数据。 ADODB.Connection cn = null; try { object ra = null; string sql = "select top 65535 * into [Excel 8.0;database=" + fileName + "].[" + tableName + "] from " + tableName; cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1); cn.Execute(sql, out ra, -1); cn.Close(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn); } catch (System.Exception ex) { MessageBox.Show(ex.ToString()); } finally { cn.Close(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn); System.GC.Collect(); } } }
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); }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="sql"></param> /// <returns></returns> public object ExecSql(ref ADODB.Connection conn, string sql) { object recordsAffected; conn.Execute(sql, out recordsAffected, -1); return(recordsAffected); }
public void TestDbRunSql() { ADODB.Connection conn = null; AdoHelper helper = null; try { using (helper = AdoHelper.ThreadInstance("", DbProviderType.Odbc)) { conn = helper.Connection(); Assert.AreEqual(conn.State, 1); string sql = " "; ADODB.Recordset rs = new ADODB.Recordset(); rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1); int a = int.Parse(rs.Fields[0].Value.ToString()); rs.Close(); Assert.IsTrue((a > 0)); } } catch (Exception ex) { Console.WriteLine(string.Format("{0}\n{1}", ex.Message, ex.ToString())); throw; } Assert.AreEqual(conn.State, 0); }
//修改数据库密码 public void editDBPwd_B(string old_password, string new_password) { try { string fileName = Application.StartupPath + @"\source\StuContact.mdb";//数据库路径 string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Jet OLEDB:Database password="******"ALTER DATABASE PASSWORD " + new_password + " " + old_password; ADODB.Connection cn = new ADODB.Connection(); cn.Mode = ADODB.ConnectModeEnum.adModeShareExclusive;//以独占模式打开数据库,不然不能修改密码 cn.Open(conn, null, null, -1); // 执行 SQL 语句以更改密码 object num; cn.Execute(sql, out num, -1); cn.Close(); //开始修改配置文件 config.writeConfig_IsEditDBandAddPwd(true, new_password); MessageBox.Show("密码修改成功,请牢记您的密码", "修改提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("数据库密码未修改,因为:" + ex.Message, "提示", MessageBoxButtons.OK); } }
//在指定的Access数据库中创建配置的表格 public static bool CreateConfTable() { Catalog catalog = new Catalog(); ADODB.Connection cn = new ADODB.Connection(); try { cn.Open(DBLink); catalog.ActiveConnection = cn; Table table = new Table(); table.ParentCatalog = catalog; table.Name = CONF_TABLE; //字段 Column col = new Column(); col.ParentCatalog = catalog; col.Name = "id"; table.Columns.Append(col, DataTypeEnum.adVarWChar, 50); //默认数据类型和字段大小 table.Keys.Append("pk_id", KeyTypeEnum.adKeyPrimary, col, null, null); Column col2 = new Column(); col2.ParentCatalog = catalog; col2.Name = "bgtime"; col2.Attributes = ColumnAttributesEnum.adColNullable; //允许空值 table.Columns.Append(col2, DataTypeEnum.adDBTime); //默认数据类型和字段大小 catalog.Tables.Append(table); cn.Close(); logger.Info("完成创建" + CONF_TABLE + "表"); } catch (System.Exception ex) { //Trace.TraceWarning("Access连接打开失败", ex); logger.Error(ex, "创建" + CONF_TABLE + "表失败"); return(false); } return(true); }
/// <summary> /// 在access数据库中创建表 /// </summary> /// <param name="filePath">数据库表文件全路径如D:\\NewDb.mdb 没有则创建 </param> /// <param name="tableName">表名</param> /// <param name="colums">ADOX.Column对象数组</param> public static bool CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums) { ADOX.Catalog catalog = new Catalog(); //数据库文件不存在则创建 if (!File.Exists(filePath)) { try { catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5"); } catch (System.Exception ex) { return(false); } } ADODB.Connection cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1); catalog.ActiveConnection = cn; ADOX.Table table = new ADOX.Table(); table.Name = tableName; foreach (var column in colums) { table.Columns.Append(column); } colums[0].ParentCatalog = catalog; colums[0].Properties["AutoIncrement"].Value = true; //设置自动增长 table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, colums[0], null, null); //定义主键 catalog.Tables.Append(table); cn.Close(); return(true); }
private void Form1_Load(object sender, EventArgs e) { String Sqlstr1, Sqlstr2; Cn = new ADODB.Connection(); Rscar = new ADODB.Recordset(); Rsuser = new ADODB.Recordset(); Cn.Provider = "Microsoft.Jet.Oledb.4.0"; Cn.ConnectionString = "DataBase\\Cars.mdb"; Cn.Open(); Sqlstr1 = "select * from Car"; Sqlstr2 = "select * from Customer"; Rscar.Open(Sqlstr1, Cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); Rsuser.Open(Sqlstr2, Cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); menu.Parent = tab; info.Parent = null; customer.Parent = null; modify.Parent = null; returnCar.Parent = null; Rscar.MoveFirst(); //Rsuser.MoveFirst(); }
public static string GetMysqlDatabaseField(string field, string key, int keyValue, string table) { string entry; ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rec = new ADODB.Recordset(); bool adoOpened = DbUse.OpenAdoMysql(conn); string commandString = "SELECT " + key + ", " + field + " FROM " + table + " WHERE " + key + " = " + keyValue + ";"; bool adoRecOpened = DbUse.OpenAdoRec(conn, rec, commandString); if (!adoOpened || !adoRecOpened) { throw new Exception("Error in opening database/dataset."); } try { entry = rec.Fields[field].Value.ToString(); } catch (Exception) { throw new Exception("Field '" + field + "' not found in the table '" + table + "'."); } DbUse.CloseAdo(conn); DbUse.CloseAdoRec(rec); return(entry); }
private void menge_Produkt3_Button_Click(object sender, EventArgs e) { metroGrid3.Rows.Clear(); //Recordset ADODB.Connection cn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); try { cn.Open(cnStr); rs.Open("Select Artikel, Sum(Anzahl) as Anzahl_Menge From Listen where Endprodukt = 3 group by Artikel", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid3.Rows.Add(); metroGrid3.Rows[i].Cells["Artikel_Mengen"].Value = Convert.ToInt32(rs.Fields["Artikel"].Value); metroGrid3.Rows[i].Cells["Anzahl_Menge"].Value = Convert.ToInt32(rs.Fields["Anzahl_Menge"].Value); rs.MoveNext(); i++; } rs.Close(); } catch (Exception fehler) { Console.WriteLine("Ein Fehler ist aufgetreten", fehler); //Try catch überprüfen.... } finally { cn.Close(); } }
private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'clientDBDataSet.ClientTable' table. You can move, or remove it, as needed. //this.clientTableTableAdapter.Fill(this.clientDBDataSet.ClientTable); Con = new ADODB.Connection(); Rs = new ADODB.Recordset(); //Connection calling partner object Provider, Path file, access string Con.Provider = Obj.MSAccessObj.Provider; Con.ConnectionString = Obj.MSAccessObj.PathFile; Con.Open(); //partnerObj.CAccess.AccessString = "select * from " in Class String accessStr; accessStr = Obj.MSAccessObj.AccessString + accessTableName; Rs.Open(accessStr, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); // connection in sql DB cn = new System.Data.OleDb.OleDbConnection(); // A dataset for sql table ds = new DataSet(); //sqlCon.ConnectionString = Obj.MSSQLObj.DataSource + Obj.MSSQLObj.AttachedDB + Obj.MSSQLObj.IntSecurity; cn.ConnectionString = "Provider = SQLNCLI11.0;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\ITD\\Term 3\\C#\\assignment\\assignment7\\AwareObject\\AwareObject\\AwareSQLDB.mdf;Integrated Security = SSPI;database=thingy"; cn.Open(); // Attempting to access Admin Table String sqlstr; sqlstr = Obj.MSSQLObj.SqlStr + sqlTableName; da = new System.Data.OleDb.OleDbDataAdapter(sqlstr, cn); da.Fill(ds, sqlTableName); RecordCount = ds.Tables[sqlTableName].Rows.Count; }
private void Button_Click(object sender, RoutedEventArgs e) { //创建数据库文件 ADOX.Catalog catalog = new Catalog(); catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;Jet OLEDB:Engine Type=5"); Debug.WriteLine("DataBase created"); //建表 ADODB.Connection cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb", null, null, -1); catalog.ActiveConnection = cn; ADOX.Table table = new ADOX.Table(); table.Name = "FirstTable"; ADOX.Column column = new ADOX.Column(); column.ParentCatalog = catalog; column.Name = "RecordId"; column.Type = DataTypeEnum.adInteger; column.DefinedSize = 9; column.Properties["AutoIncrement"].Value = true; table.Columns.Append(column, DataTypeEnum.adInteger, 9); table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null); table.Columns.Append("CustomerName", DataTypeEnum.adVarWChar, 50); table.Columns.Append("Age", DataTypeEnum.adInteger, 9); table.Columns.Append("Birthday", DataTypeEnum.adDate, 0); catalog.Tables.Append(table); cn.Close(); }
/// <summary> /// Upgrades a Version 1.0 database to Version 1.1. This upgrade is the /// addition of the field "TerminalParameters" to the table tblFunctionSet. /// </summary> /// <returns></returns> private static bool UpgradeDatabase() { try { ADODB.Connection con = new ADODB.Connection(); con.Open(ConnectionString, "", "", 0); Catalog dbCatalog = new Catalog(); dbCatalog.ActiveConnection = con; Table dbTable = dbCatalog.Tables["tblFunctionSet"]; dbTable.Columns.Append("TerminalParameters", ADOX.DataTypeEnum.adBoolean, 1); dbTable.Columns[dbTable.Columns.Count - 1].Properties["Default"].Value = "False"; con.Close(); } catch (Exception) { MessageBox.Show("Failed to correctly upgrade the database"); return(false); } // // If the upgrade succeeded, update the version value in the admin table return(UpdateField(1, "tblAdministration", "Description", GPEnums.DATABASE_VERSION)); }
public static string GetConnectionString(string connectionString) { if (string.IsNullOrEmpty(connectionString)) { return(GetConnectionString()); } try { // C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL MSDASC.DataLinks dataLinks = new MSDASC.DataLinks(); // C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\PublicAssemblies\adodb.dll ADODB._Connection connection; connection = new ADODB.Connection(); connection.ConnectionString = connectionString; object oConnection = connection; if (dataLinks.PromptEdit(ref oConnection)) { return(connection.ConnectionString); } } catch (Exception) { } return(null); }
public static void closeConnection() { // ERROR: Not supported in C#: OnErrorStatement cnnDB.Close(); cnnDB = null; }
//结束 /// <summary> /// 根据传入的参数,得到sql语句,调用Gf_CodeFind() /// 方法,得到CD_SHORT_NAME或CD_NAME或CD_SHORT_ENG或CD_FULL_ENG的值。 /// </summary> /// <param name="Conn"></param> /// <param name="Cd_Mana_No"></param> /// <param name="Code"></param> /// <param name="nameType"></param> /// <returns></returns> public static string Gf_CommNameFind(ADODB.Connection Conn, string Cd_Mana_No, string Code, string nameType) { string sQuery; switch (nameType) { case "1": //Short Name sQuery = "SELECT CD_SHORT_NAME FROM ZP_CD WHERE CD_MANA_NO = \'" + Cd_Mana_No + "\' AND CD = \'" + Code + "\' "; break; case "2": //Full Name sQuery = "SELECT CD_NAME FROM ZP_CD WHERE CD_MANA_NO = \'" + Cd_Mana_No + "\' AND CD = \'" + Code + "\' "; break; case "3": //Short Eng Name sQuery = "SELECT CD_SHORT_ENG FROM ZP_CD WHERE CD_MANA_NO = \'" + Cd_Mana_No + "\' AND CD = \'" + Code + "\' "; break; case "4": //Full Eng Name sQuery = "SELECT CD_FULL_ENG FROM ZP_CD WHERE CD_MANA_NO = \'" + Cd_Mana_No + "\' AND CD = \'" + Code + "\' "; break; default: //Full Name sQuery = "SELECT CD_NAME FROM ZP_CD WHERE CD_MANA_NO = \'" + Cd_Mana_No + "\' AND CD = \'" + Code + "\' "; break; } return(GeneralCommon.Gf_CodeFind(GeneralCommon.M_CN1, sQuery)); }
public static ADODB.Connection ConnectExchange(string Path) { ADODB.Connection Cnn = new ADODB.Connection(); Cnn.Provider = "msdaipp.dso"; Cnn.Mode = ADODB.ConnectModeEnum.adModeReadWrite; Cnn.Open(Path, "Namwah", "ParaW0rld", -1); return Cnn; }
public Geocode(string roadsTableName, string pointsTableName, ADODB.Connection odbcDatabaseConnection, double Lon, double Lat) { this.roadsTableName = roadsTableName; this.pointsTableName = pointsTableName; this.odbcDatabaseConnection = odbcDatabaseConnection; this.Lon = Lon; this.Lat = Lat; }
public static void LoadItem() { string partPath = "http://nwszmail/public/namwah/Parts2/Parts/"; ADODB.Connection cn = new ADODB.Connection(); ADODB.Record rst; cn.Provider = "msdaipp.dso"; cn.CommandTimeout = 3600; cn.Mode = ADODB.ConnectModeEnum.adModeReadWrite; cn.Open(partPath, "Namwah", "ParaW0rld", -1); }
public static void configGrReporting() { MSDASC.DataLinks dls = new MSDASC.DataLinks(); ADODB.Connection conn = new ADODB.Connection(); conn.ConnectionString = tempConnStr; Object obj = conn; //ADODB.Connection conn = dls.PromptNew(); dls.PromptEdit(ref obj); tempConnStr = conn.ConnectionString; NotePad.runNotePad(tempConnStr); }
public static string GetConnectionStringDialog() { ADODB.Connection adoCon = new ADODB.Connection(); object connection = (object)adoCon; MSDASC.DataLinks dlg = new MSDASC.DataLinks(); if (dlg.PromptEdit(ref connection)) { return adoCon.ConnectionString; } else { return String.Empty; } }
/// <summary> /// 建立 优惠劵 数据库 /// </summary> public static void Coupons_InitDataBase() { // string sqlstr = "INSERT INTO WX_Coupons(CUserName, CUserPhone, CUserqq, WXOpenID) VALUES (N'" + CUserName + "',N'" + CUserPhone + "',N'" + CUserqq + "',N'" + Session["WXOpenID"] + "')"; string PathDataBase = System.IO.Path.Combine(SYSTEMDIR, "USER_DIR\\SYSUSER\\Coupons\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" + if (System.IO.File.Exists(PathDataBase) == true ) { return; } try { ADOX.Catalog catalog = new Catalog(); catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase + ";Jet OLEDB:Engine Type=5"); ADODB.Connection cn = new ADODB.Connection(); cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase, null, null, -1); catalog.ActiveConnection = cn; if (true) { ADOX.Table table = new ADOX.Table(); table.Name = "WX_Coupons"; ADOX.Column column = new ADOX.Column(); column.ParentCatalog = catalog; column.Name = "WXOpenID"; column.Type = DataTypeEnum.adInteger; column.DefinedSize = 7; column.Properties["AutoIncrement"].Value = true; table.Columns.Append(column, DataTypeEnum.adInteger, 7); table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null); // string sqlstr = "INSERT INTO WX_Coupons(CUserName, CUserPhone, CUserqq, WXOpenID) VALUES (N'" + CUserName + "',N'" + CUserPhone + "',N'" + CUserqq + "',N'" + Session["WXOpenID"] + "')"; table.Columns.Append("CUserName", DataTypeEnum.adVarWChar, 200); table.Columns.Append("CUserPhone", DataTypeEnum.adVarWChar, 200); table.Columns.Append("CUserqq", DataTypeEnum.adVarWChar, 200); catalog.Tables.Append(table); } cn.Close(); } catch { } }
public Boolean CreateTable(String tableName, params ASColumn[] columns) { if (!CheckParams()) throw new ArgumentNullException("请先绑定文件名"); if (!File.Exists(base.FileName)) throw new ArgumentNullException("文件不存在"); if (String.IsNullOrEmpty(tableName)) return false; try { ADOX.Catalog catalog = new Catalog(); ADODB.Connection conn = new ADODB.Connection(); conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + base.FileName, null, null, -1); catalog.ActiveConnection = conn; ADOX.Table table = new Table(); table.Name = tableName; for (int i = 0; i < columns.Length; i++) { ADOX.Column column = new Column(); column.ParentCatalog = catalog; column.Name = columns[i].Name; column.Type = columns[i].Type; column.Attributes = columns[i].Attribute; column.DefinedSize = columns[i].DefinedSize; table.Columns.Append(column, column.Type, column.DefinedSize); if (columns[i].Key != null) table.Keys.Append(columns[i].Name + columns[i].Key.Type, columns[i].Key.Type, column, null, null); } catalog.Tables.Append(table); conn.Close(); return true; } catch { return false; } }
private static void AddTable(OleDbConnectionStringBuilder csb) { // check if table exists var con = new ADODB.Connection(); con.Open(csb.ToString()); var db = new ADOX.Catalog(); db.ActiveConnection = con; try { var table = db.Tables[_logFileProcessor.TableName]; } catch (COMException) { db.Tables.Append(_logFileProcessor.GetTable()); } finally { con.Close(); } }
public static string[] GetTableExcel(string strFileName) { string[] strTables = new string[100]; Catalog oCatlog = new Catalog(); ADOX.Table oTable = new ADOX.Table(); ADODB.Connection oConn = new ADODB.Connection(); oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0); oCatlog.ActiveConnection = oConn; if (oCatlog.Tables.Count > 0) { int item = 0; foreach (ADOX.Table tab in oCatlog.Tables) { if (tab.Type == "TABLE") { strTables[item] = tab.Name; item++; } } } return strTables; }
public static void configDataSet(DbName name, DbType type) { String key = getAppKey(name, type); if (name != DbName.WinCE) { Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); MSDASC.DataLinks mydlg = new MSDASC.DataLinks(); ADODB.Connection ADOcon = new ADODB.Connection(); ADOcon.ConnectionString = config.AppSettings.Settings[key].Value; Dictionary<string, string> ht = splitConnectionString(ADOcon.ConnectionString); //Cast the generic object that PromptNew returns to an ADODB.Connection. //ADOcon = (ADODB.Connection)mydlg.PromptNew(); Object obj = ADOcon; if (mydlg.PromptEdit(ref obj)) { if (name == DbName.MySql) config.AppSettings.Settings[key].Value = ADOcon.ConnectionString + ";Ip=" + ht["ip"]; else config.AppSettings.Settings[key].Value = ADOcon.ConnectionString; //调试的时候外部App.config文件不修改 config.Save(); } } else { Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); String connectString = config.AppSettings.Settings[key].Value; Dictionary<string, string> ht = splitConnectionString(connectString); OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.FileName = ""; openFileDialog1.Filter = "*.sdf | *.sdf"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { config.AppSettings.Settings[key].Value = "Persist Security Info = " + ht["persist security info"] + "; Password = '******'; Max Database Size = " + ht["max database size"] + "; Max Buffer Size = " + ht["max buffer size"] + ";Data Source='" + openFileDialog1.FileName + "'"; config.Save(); } } }
public static void closeConnectionReport() { cnnDBreport.Close(); //UPGRADE_NOTE: Object cnnDBreport may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' cnnDBreport = null; }
public static bool openConsReport(ref string strL) { bool functionReturnValue = false; string strTable = null; bool createDayend = false; string strDBPath = null; // ERROR: Not supported in C#: OnErrorStatement createDayend = false; cnnDBConsReport = new ADODB.Connection(); strDBPath = modRecordSet.StrLocRep + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb"; cnnDBConsReport.Open("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source= " + strDBPath + ";"); openConnection_Error: functionReturnValue = false; Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " openConsReport object has not been made."); return functionReturnValue; }
public static bool openConnectionReport() { bool functionReturnValue = false; string Path = null; // ERROR: Not supported in C#: OnErrorStatement bool createDayend = false; string strDBPath = null; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); createDayend = false; functionReturnValue = true; cnnDBreport = new ADODB.Connection(); strDBPath = modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb"; var _with1 = cnnDBreport; _with1.Provider = "Microsoft.ACE.OLEDB.12.0"; _with1.Properties("Jet OLEDB:System Database").Value = modRecordSet.serverPath + "Secured.mdw"; _with1.Open(strDBPath, "liquid", "lqd"); return functionReturnValue; withPass: //If serverPath = "" Then setNewServerPath //If serverPath <> "" Then cnnDBreport = new ADODB.Connection(); strDBPath = modRecordSet.serverPath + "report" + My.MyProject.Forms.frmMenu.lblUser.Tag + ".mdb"; //UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"' Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd"; cnnDBreport.CursorLocation = ADODB.CursorLocationEnum.adUseClient; //UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"' cnnDBreport.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source= " + Path); functionReturnValue = true; return functionReturnValue; openConnection_Error: //End If if (Err().Description == "[Microsoft][ODBC Microsoft Access Driver] Not a valid password.") { goto withPass; } else if (Err().Description == "Not a valid password.") { goto withPass; } functionReturnValue = false; Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " openConnectionReport object has not been made."); return functionReturnValue; }
/// <summary> /// Displays a Connection String Builder (DataLinks) dialog. /// /// Credits: /// http://www.codeproject.com/cs/database/DataLinks.asp /// http://www.codeproject.com/cs/database/DataLinks.asp?df=100&forumid=33457&select=1560237#xx1560237xx /// /// Required COM references: /// %PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll /// %PROGRAMFILES%\Common Files\System\Ole DB\OLEDB32.DLL /// </summary> /// <param name="currentConnectionString">Previous database connection string</param> /// <returns>Selected connection string</returns> private string PromptForConnectionString(string currentConnectionString) { MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass(); ADODB.Connection dialogConnection; string generatedConnectionString = string.Empty; if (currentConnectionString == String.Empty) { dialogConnection = (ADODB.Connection)dataLinks.PromptNew(); generatedConnectionString = dialogConnection.ConnectionString.ToString(); } else { dialogConnection = new ADODB.Connection(); dialogConnection.Provider = "SQLOLEDB.1"; ADODB.Property persistProperty = dialogConnection.Properties["Persist Security Info"]; persistProperty.Value = true; dialogConnection.ConnectionString = currentConnectionString; dataLinks = new MSDASC.DataLinks(); object objConn = dialogConnection; if (dataLinks.PromptEdit(ref objConn)) { generatedConnectionString = dialogConnection.ConnectionString.ToString(); } else { return currentConnectionString; } } generatedConnectionString = generatedConnectionString.Replace("Provider=SQLOLEDB.1;", string.Empty); if ( !generatedConnectionString.Contains("Integrated Security=SSPI") && !generatedConnectionString.Contains("Trusted_Connection=True") && !generatedConnectionString.Contains("Password="******"Pwd=") ) { if (dialogConnection.Properties["Password"] != null) generatedConnectionString += ";Password="******"Password"].Value; } return generatedConnectionString; }
/// <summary> /// Removes all entries from given table /// </summary> /// <param name="table">Table name</param> /// <returns>True if success</returns> public bool DeleteAllData(string table) { string strSQL = string.Format(DELETE_ALL, table); ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } con.Close(); return true; }
public bool openConnectionWaitron() { bool functionReturnValue = false; // ERROR: Not supported in C#: OnErrorStatement bool createDayend = false; string strDBPath = null; createDayend = false; functionReturnValue = true; cnnDBWaitron = new ADODB.Connection(); strDBPath = modRecordSet.serverPath + "Waitron.mdb"; var _with2 = cnnDBWaitron; _with2.Provider = "Microsoft.ACE.OLEDB.12.0"; _with2.Properties("Jet OLEDB:System Database").Value = modRecordSet.serverPath + "Secured.mdw"; _with2.Open(strDBPath, "liquid", "lqd"); return functionReturnValue; openConnection_Error: functionReturnValue = false; return functionReturnValue; }
static public IConnection UpDateFromDataConnectionLink(IConnection oldConnection) { object AdoConnection; MSDASC.DataLinks dataLink = new MSDASC.DataLinks(); IConnection connection = null; AdoConnection = new ADODB.Connection(); (AdoConnection as ADODB.Connection).ConnectionString = oldConnection.ConnectionString; if (dataLink.PromptEdit(ref AdoConnection)) { connection = CreateConnectionObject((AdoConnection as ADODB.Connection).ConnectionString); } return connection; }
override internal void LoadAll() { DataTable metaData = CreateDataTable(); ADODB.Connection cnn = new ADODB.Connection(); ADOX.Catalog cat = new ADOX.Catalog(); // Open the Connection cnn.Open(dbRoot.ConnectionString, null, null, 0); cat.ActiveConnection = cnn; ADOX.Procedure proc = cat.Procedures[this.Procedure.Name]; ADODB.Command cmd = proc.Command as ADODB.Command; // Retrieve Parameter information cmd.Parameters.Refresh(); if(cmd.Parameters.Count > 0) { int ordinal = 0; foreach(ADODB.Parameter param in cmd.Parameters) { DataRow row = metaData.NewRow(); string hyperlink = "False"; try { hyperlink = param.Properties["Jet OLEDB:Hyperlink"].Value.ToString(); } catch {} row["TYPE_NAME"] = hyperlink == "False" ? param.Type.ToString() : "Hyperlink"; row["PROCEDURE_CATALOG"] = this.Procedure.Database; row["PROCEDURE_SCHEMA"] = null; row["PROCEDURE_NAME"] = this.Procedure.Name; row["PARAMETER_NAME"] = param.Name; row["ORDINAL_POSITION"] = ordinal++; row["PARAMETER_TYPE"] = param.Type;//.ToString(); row["PARAMETER_HASDEFAULT"] = false; row["PARAMETER_DEFAULT"] = null; row["IS_NULLABLE"] = false; row["DATA_TYPE"] = param.Type;//.ToString(); row["CHARACTER_MAXIMUM_LENGTH"] = 0; row["CHARACTER_OCTET_LENGTH"] = 0; row["NUMERIC_PRECISION"] = param.Precision; row["NUMERIC_SCALE"] = param.NumericScale; row["DESCRIPTION"] = ""; // row["TYPE_NAME"] = ""; row["LOCAL_TYPE_NAME"] = ""; metaData.Rows.Add(row); } } cnn.Close(); base.PopulateArray(metaData); }
/// <summary> /// Saves information about XML Files into database /// </summary> /// <param name="filesArray">List with informations about XML Files</param> /// <returns>True if success</returns> public bool SaveFiles(XmlFile[] filesArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { return false; } for (int i = 0; i < filesArray.Length; i++) { strSQL = string.Format(NEW_XML_FILE, filesArray[i].Id, filesArray[i].Name, filesArray[i].Content, filesArray[i].TimeStamp); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
/// <summary> /// Saves relations between TAGs and XML rows /// </summary> /// <param name="relationsArray">List with TAG IDs and XML FIELD IDs</param> /// <returns>True if success</returns> public bool SaveRelations(XmlTagRelation[] relationsArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } for (int i = 0; i < relationsArray.Length; i++) { strSQL = string.Format(NEW_XML_TAG_RELATION, relationsArray[i].TagID, relationsArray[i].XmlFileID); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
/// <summary> /// Saves TAGs list into database /// </summary> /// <param name="tagsArray">List of TAGs</param> /// <returns>True if success</returns> public bool SaveTags(Tag[] tagsArray) { string strSQL; ADODB.Connection con = new ADODB.Connection(); object obj = new object(); try { con.Open(GenerateConnectionString(), "", "", 0); } catch (Exception) { //TODO: Error logging return false; } for (int i = 0; i < tagsArray.Length; i++) { strSQL = string.Format(NEW_TAG, tagsArray[i].Id, tagsArray[i].Name); try { con.Execute(strSQL, out obj, 0); } catch (Exception) { //TODO: Error logging con.Close(); return false; } } con.Close(); return true; }
/// <summary> /// 创建新的信息数据库 /// </summary> private void CreatDb() { string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dac.accdb"; ADOX.Catalog catalog = new Catalog(); catalog.Create(con);//创建新的数据库 catalog.ActiveConnection(); ADODB.Connection connection = new ADODB.Connection(); connection.Open(con, null, null, -1); //新建主体信息数据表 ADOX.Table table = new Table(); table.Name = "主体信息表"; ADOX.Column subjectName = new Column();//主体名称 subjectName.Name = "主体名称"; ADOX.Column pwd = new Column();//主体登录密码 pwd.Name = "密码"; ADOX.Column SubjectRegisterTime = new Column();//主体登录密码 SubjectRegisterTime.Name = "注册时间"; table.Columns.Append(subjectName);//将列添加到表中 table.Columns.Append(pwd); table.Columns.Append(SubjectRegisterTime); catalog.Tables.Append(table); //新建角色信息数据表 table = new Table(); table.Name = "角色信息表"; ADOX.Column RoleName = new Column();//角色名称 RoleName.Name = "角色名称"; ADOX.Column RoleRegisterTime= new Column();//角色注册时间 RoleRegisterTime.Name = "创建时间"; table.Columns.Append(RoleName);//将列添加到表中 table.Columns.Append(RoleRegisterTime); catalog.Tables.Append(table); //新建角互斥色信息数据表 table = new Table(); table.Name = "互斥角色信息表"; ADOX.Column ExclusionRoleName = new Column();//角色名称 ExclusionRoleName.Name = "角色1名称"; ADOX.Column ExclusionRoleName1 = new Column();//角色名称 ExclusionRoleName1.Name = "角色2名称"; table.Columns.Append(ExclusionRoleName);//将列添加到表中 table.Columns.Append(ExclusionRoleName1); catalog.Tables.Append(table); //新建角色关系信息数据表 table = new Table(); table.Name = "角色关系信息表"; ADOX.Column FatherRoleName = new Column();//角色名称 FatherRoleName.Name = "父角色名称"; ADOX.Column ChildRoleName = new Column();//角色名称 ChildRoleName.Name = "子角色名称"; table.Columns.Append(FatherRoleName);//将列添加到表中 table.Columns.Append(ChildRoleName); catalog.Tables.Append(table); //新建权限信息表 table = new Table(); table.Name = "权限信息表"; ADOX.Column PermissionName = new Column();//权限名称 PermissionName.Name = "权限名称"; ADOX.Column PermissionRegisterTime = new Column();//权限创建注册时间 PermissionRegisterTime.Name = "创建时间"; table.Columns.Append(PermissionName);//将列添加到表中 table.Columns.Append(PermissionRegisterTime); catalog.Tables.Append(table); //新建用户角色指派信息表 table = new Table(); table.Name = "用户-角色指派信息表"; ADOX.Column UserName = new Column();//用户名称 UserName.Name = "用户名称"; RoleName = new Column();//角色名称 RoleName.Name = "角色名称"; table.Columns.Append(UserName);//将列添加到表中 table.Columns.Append(RoleName); catalog.Tables.Append(table); //新建角色到权限的指派信息表 table = new Table(); table.Name = "角色-权限指派信息表"; RoleName = new Column();//角色名称 RoleName.Name = "角色名称"; PermissionName = new Column();//权限名称 PermissionName.Name = "权限名称"; table.Columns.Append(RoleName); table.Columns.Append(PermissionName); catalog.Tables.Append(table); //新建注册管理表 table = new Table(); table.Name = "注册管理表"; ADOX.Column subject_Name1 = new Column();//客体名称 subject_Name1.Name = "主体名称"; ADOX.Column time = new Column();//客体名称 time.Name = "注册时间"; ADOX.Column pwd1 = new Column();//客体名称 pwd1.Name = "密码"; table.Columns.Append(subject_Name1); table.Columns.Append(time); table.Columns.Append(pwd1); catalog.Tables.Append(table); connection.Close();//关闭连接 }
public void closeConnectionWaitron() { cnnDBWaitron.Close(); cnnDBWaitron = null; }
private void doMonthEnd() { string sql = null; string lPath = null; ADODB.Recordset rs = default(ADODB.Recordset); Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); ADODB.Connection dbcnnMonth = default(ADODB.Connection); //fix Server Path in Month End DBs string databaseName = null; this.Cursor = System.Windows.Forms.Cursors.WaitCursor; System.Windows.Forms.Application.DoEvents(); //BuildAll rs = modRecordSet.getRS(ref "SELECT Company_MonthEndID, Company_OpenDebtor From Company"); lPath = modRecordSet.serverPath + "month" + rs.Fields("Company_MonthEndID").Value + ".mdb"; //fix Server Path in Month End DBs databaseName = "month" + rs.Fields("Company_MonthEndID").Value + ".mdb"; sql = "UPDATE Company Set Company.Company_MonthEndID = Company.Company_MonthEndID + 1;"; modRecordSet.cnnDB.Execute(sql); sql = "INSERT INTO MonthEnd ( MonthEndID, MonthEnd_Date, MonthEnd_Days, MonthEnd_BudgetSales, MonthEnd_BudgetPurchases ) SELECT Company.Company_MonthEndID, Now(), 20, 100000, 100000 FROM Company;"; modRecordSet.cnnDB.Execute(sql); modRecordSet.cnnDB.Execute("UPDATE Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID SET DayEnd.DayEnd_MonthEndID = [Company]![Company_MonthEndID];"); fso.CopyFile(modRecordSet.serverPath + "template.mdb", lPath, true); dbcnnMonth = new ADODB.Connection(); var _with1 = dbcnnMonth; _with1.Provider = "Microsoft.ACE.OLEDB.12.0"; _with1.Properties("Jet OLEDB:System Database").Value = "" + modRecordSet.serverPath + "Secured.mdw"; _with1.Open(lPath, "liquid", "lqd"); sql = "UPDATE StockitemHistory SET StockitemHistory.StockitemHistory_Month12 = [StockitemHistory]![StockitemHistory_Month11], StockitemHistory.StockitemHistory_Month11 = [StockitemHistory]![StockitemHistory_Month10], StockitemHistory.StockitemHistory_Month10 = [StockitemHistory]![StockitemHistory_Month9], StockitemHistory.StockitemHistory_Month9 = [StockitemHistory]![StockitemHistory_Month8], StockitemHistory.StockitemHistory_Month8 = [StockitemHistory]![StockitemHistory_Month7], StockitemHistory.StockitemHistory_Month7 = [StockitemHistory]![StockitemHistory_Month6], StockitemHistory.StockitemHistory_Month6 = [StockitemHistory]![StockitemHistory_Month5], StockitemHistory.StockitemHistory_Month5 = [StockitemHistory]![StockitemHistory_Month4], "; sql = sql + "StockitemHistory.StockitemHistory_Month4 = [StockitemHistory]![StockitemHistory_Month3], StockitemHistory.StockitemHistory_Month3 = [StockitemHistory]![StockitemHistory_Month2], StockitemHistory.StockitemHistory_Month2 = [StockitemHistory]![StockitemHistory_Month1], StockitemHistory.StockitemHistory_Month1 = 0;"; modRecordSet.cnnDB.Execute(sql); sql = "INSERT INTO Customer ( CustomerID, Customer_ChannelID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Fax, Customer_Email, Customer_Disabled, Customer_Terms, Customer_CreditLimit, Customer_Current, Customer_30Days, Customer_60Days, Customer_90Days, Customer_120Days, Customer_150Days, Customer_PrintStatement,Customer_VATNumber ) "; sql = sql + "SELECT M_Customer.CustomerID, M_Customer.Customer_ChannelID, M_Customer.Customer_InvoiceName, M_Customer.Customer_DepartmentName, M_Customer.Customer_FirstName, M_Customer.Customer_Surname, M_Customer.Customer_PhysicalAddress, M_Customer.Customer_PostalAddress, M_Customer.Customer_Telephone, M_Customer.Customer_Fax, M_Customer.Customer_Email, M_Customer.Customer_Disabled, M_Customer.Customer_Terms, M_Customer.Customer_CreditLimit, M_Customer.Customer_Current, M_Customer.Customer_30Days, M_Customer.Customer_60Days, M_Customer.Customer_90Days, M_Customer.Customer_120Days, M_Customer.Customer_150Days, M_Customer.Customer_PrintStatement, M_Customer.Customer_VATNumber FROM M_Customer;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO CustomerTransaction ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) "; sql = sql + "SELECT M_CustomerTransaction.CustomerTransactionID, M_CustomerTransaction.CustomerTransaction_CustomerID, M_CustomerTransaction.CustomerTransaction_TransactionTypeID, M_CustomerTransaction.CustomerTransaction_DayEndID, M_CustomerTransaction.CustomerTransaction_MonthEndID, M_CustomerTransaction.CustomerTransaction_ReferenceID, M_CustomerTransaction.CustomerTransaction_Date, M_CustomerTransaction.CustomerTransaction_Description, M_CustomerTransaction.CustomerTransaction_Amount, M_CustomerTransaction.CustomerTransaction_Reference, M_CustomerTransaction.CustomerTransaction_PersonName, M_CustomerTransaction.CustomerTransaction_Done, M_CustomerTransaction.CustomerTransaction_Main, M_CustomerTransaction.CustomerTransaction_Child, M_CustomerTransaction.CustomerTransaction_Allocated FROM M_CustomerTransaction;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO CustomerTransactionAlloc ( CustomerTransactionAllocID, CustomerTransactionAlloc_CustomerID, CustomerTransactionAlloc_MainID, CustomerTransactionAlloc_ChildID, CustomerTransactionAlloc_Date, CustomerTransactionAlloc_Description, CustomerTransactionAlloc_Amount, CustomerTransactionAlloc_Reference, CustomerTransactionAlloc_PersonName ) "; sql = sql + "SELECT M_CustomerTransactionAlloc.CustomerTransactionAllocID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_CustomerID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_MainID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_ChildID, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Date, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Description, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Amount, M_CustomerTransactionAlloc.CustomerTransactionAlloc_Reference, M_CustomerTransactionAlloc.CustomerTransactionAlloc_PersonName FROM M_CustomerTransactionAlloc;"; dbcnnMonth.Execute(sql); if (rs.Fields("Company_OpenDebtor").Value == true) { //sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction WHERE (((M_CustomerTransaction.CustomerTransaction_Allocated)=[M_CustomerTransaction].[CustomerTransaction_Amount]) AND ((M_CustomerTransaction.CustomerTransaction_Allocated)<>0));" //dbcnnMonth.Execute sql sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;"; dbcnnMonth.Execute(sql); //version 1 problem //sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) " //sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & ' B/F' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated " //sql = sql & "From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) AND (((CustomerTransaction.CustomerTransaction_Allocated)<>0));" //dbcnnMonth.Execute sql //version 2 //sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) " //sql = sql & "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" & " *" & "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done " //sql = sql & "FROM CustomerTransaction, M_Company WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;" //dbcnnMonth.Execute sql //version 3 sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) "; sql = sql + "SELECT CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, (CustomerTransaction.CustomerTransaction_Amount-CustomerTransaction.CustomerTransaction_Allocated) AS SumOfCustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference & '" + " *" + "' AS ref, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Done "; sql = sql + "FROM CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_Allocated)<>[CustomerTransaction].[CustomerTransaction_Amount])) ORDER BY CustomerTransaction.CustomerTransactionID;"; dbcnnMonth.Execute(sql); } else { //DO THE OLD WAY sql = "DELETE M_CustomerTransaction.* FROM M_CustomerTransaction;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO M_CustomerTransaction ( CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName ) SELECT CustomerTransaction.CustomerTransaction_CustomerID, 7 AS transType, M_Company.Company_DayEndID, M_Company.Company_MonthEndID, 0 AS reference, Now() AS [date], '' AS [desc], Sum(CustomerTransaction.CustomerTransaction_Amount) AS SumOfCustomerTransaction_Amount, 'Month End' AS ref, 'System' AS person From CustomerTransaction, M_Company GROUP BY CustomerTransaction.CustomerTransaction_CustomerID, M_Company.Company_DayEndID, M_Company.Company_MonthEndID;"; dbcnnMonth.Execute(sql); } sql = "UPDATE M_Customer SET M_Customer.Customer_150Days = [M_Customer]![Customer_150Days]+[M_Customer]![Customer_120Days], M_Customer.Customer_120Days = [M_Customer]![Customer_90Days], M_Customer.Customer_90Days = [M_Customer]![Customer_60Days], M_Customer.Customer_60Days = [M_Customer]![Customer_30Days], M_Customer.Customer_30Days = [M_Customer]![Customer_Current], M_Customer.Customer_Current = 0;"; dbcnnMonth.Execute(sql); //Debtor Age shifting if Credit dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_150Days]<0),([M_Customer]![Customer_120Days]+[M_Customer]![Customer_150Days]),[M_Customer]![Customer_120Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_150Days = iif(([M_Customer]![Customer_150Days]<0),0,[M_Customer]![Customer_150Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_120Days]<0),([M_Customer]![Customer_90Days]+[M_Customer]![Customer_120Days]),[M_Customer]![Customer_90Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_120Days = iif(([M_Customer]![Customer_120Days]<0),0,[M_Customer]![Customer_120Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_90Days]<0),([M_Customer]![Customer_60Days]+[M_Customer]![Customer_90Days]),[M_Customer]![Customer_60Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_90Days = iif(([M_Customer]![Customer_90Days]<0),0,[M_Customer]![Customer_90Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_60Days]<0),([M_Customer]![Customer_30Days]+[M_Customer]![Customer_60Days]),[M_Customer]![Customer_30Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_60Days = iif(([M_Customer]![Customer_60Days]<0),0,[M_Customer]![Customer_60Days]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_Current = iif(([M_Customer]![Customer_30Days]<0),([M_Customer]![Customer_Current]+[M_Customer]![Customer_30Days]),[M_Customer]![Customer_Current]);"); dbcnnMonth.Execute("UPDATE M_Customer SET M_Customer.Customer_30Days = iif(([M_Customer]![Customer_30Days]<0),0,[M_Customer]![Customer_30Days]);"); //Debtor Age shifting if Credit //Tranfer change sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));" sql = "INSERT INTO DayEndStockItemLnk ( DayEndStockItemLnk_DayEndID, DayEndStockItemLnk_StockItemID, DayEndStockItemLnk_Quantity, DayEndStockItemLnk_QuantitySales, DayEndStockItemLnk_QuantityShrink, DayEndStockItemLnk_QuantityGRV, DayEndStockItemLnk_QuantityTransafer, DayEndStockItemLnk_ListCost, DayEndStockItemLnk_ActualCost, DayEndStockItemLnk_Warehouse ) SELECT M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, M_DayEndStockItemLnk.DayEndStockItemLnk_StockItemID, M_DayEndStockItemLnk.DayEndStockItemLnk_Quantity, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantitySales, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityShrink, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityGRV, M_DayEndStockItemLnk.DayEndStockItemLnk_QuantityTransafer, M_DayEndStockItemLnk.DayEndStockItemLnk_ListCost, M_DayEndStockItemLnk.DayEndStockItemLnk_ActualCost, M_DayEndStockItemLnk.DayEndStockItemLnk_Warehouse "; sql = sql + "From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));"; dbcnnMonth.Execute(sql); sql = "DELETE M_DayEndStockItemLnk.* From M_DayEndStockItemLnk, M_Company WHERE (((M_DayEndStockItemLnk.DayEndStockItemLnk_DayEndID)<>[M_Company]![Company_DayEndID]));"; dbcnnMonth.Execute(sql); //Tranfer change sql = "INSERT INTO StockTransferWH ( StockTransferWH_Date, StockTransferWH_DayEndID, StockTransferWH_PersonID, StockTransferWH_WHFrom, StockTransferWH_WHTo, StockTransferWH_StockItemID, StockTransferWH_Qty ) SELECT M_StockTransferWH.StockTransferWH_Date, M_StockTransferWH.StockTransferWH_DayEndID, M_StockTransferWH.StockTransferWH_PersonID, M_StockTransferWH.StockTransferWH_WHFrom, M_StockTransferWH.StockTransferWH_WHTo, M_StockTransferWH.StockTransferWH_StockItemID, M_StockTransferWH.StockTransferWH_Qty "; sql = sql + "From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));"; dbcnnMonth.Execute(sql); sql = "DELETE M_StockTransferWH.* From M_StockTransferWH, M_Company WHERE (((M_StockTransferWH.StockTransferWH_DayEndID)<>[M_Company]![Company_DayEndID]));"; dbcnnMonth.Execute(sql); //Tranfer change sql = "INSERT INTO Declaration ( DeclarationID, Declaration_POSID, Declaration_DayEndID, Declaration_Date, Declaration_Cash, Declaration_CashServer, Declaration_CashCount, Declaration_Cheque, Declaration_ChequeServer, Declaration_ChequeCount, Declaration_Card, Declaration_CardServer, Declaration_CardCount, Declaration_Payout, Declaration_PayoutServer, Declaration_PayoutCount, Declaration_Total, Declaration_TotalServer, Declaration_TotalCount ) "; sql = sql + "SELECT M_Declaration.DeclarationID, M_Declaration.Declaration_POSID, M_Declaration.Declaration_DayEndID, M_Declaration.Declaration_Date, M_Declaration.Declaration_Cash, M_Declaration.Declaration_CashServer, M_Declaration.Declaration_CashCount, M_Declaration.Declaration_Cheque, M_Declaration.Declaration_ChequeServer, M_Declaration.Declaration_ChequeCount, M_Declaration.Declaration_Card, M_Declaration.Declaration_CardServer, M_Declaration.Declaration_CardCount, M_Declaration.Declaration_Payout, M_Declaration.Declaration_PayoutServer, M_Declaration.Declaration_PayoutCount, M_Declaration.Declaration_Total, M_Declaration.Declaration_TotalServer, M_Declaration.Declaration_TotalCount FROM M_Declaration;"; dbcnnMonth.Execute(sql); sql = "DELETE M_Declaration.* FROM M_Declaration;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO Sale ( SaleID, Sale_PosID, Sale_DeclarationID, Sale_ChannelID, Sale_PersonID, Sale_ManagerID, Sale_DayEndID, Sale_Date, Sale_DatePOS, Sale_SubTotal, Sale_Discount, Sale_Total, Sale_Tender, Sale_Slip, Sale_PaymentType, Sale_Reference,Sale_CardRef,Sale_OrderRef,Sale_SerialRef,Sale_Cash,Sale_Card,Sale_Cheque,Sale_CDebit,Sale_PersonShiftID,Sale_TableNumber,Sale_GuestCount,Sale_SlipCount,Sale_Gratuity,Sale_DisChk,Sale_SaleChk ) "; sql = sql + "SELECT M_Sale.SaleID, M_Sale.Sale_PosID, M_Sale.Sale_DeclarationID, M_Sale.Sale_ChannelID, M_Sale.Sale_PersonID, M_Sale.Sale_ManagerID, M_Sale.Sale_DayEndID, M_Sale.Sale_Date, M_Sale.Sale_DatePOS, M_Sale.Sale_SubTotal, M_Sale.Sale_Discount, M_Sale.Sale_Total, M_Sale.Sale_Tender, M_Sale.Sale_Slip, M_Sale.Sale_PaymentType, Sale_Reference,M_Sale.Sale_CardRef,M_Sale.Sale_OrderRef,M_Sale.Sale_SerialRef, M_Sale.Sale_Cash,M_Sale.Sale_Card,M_Sale.Sale_Cheque,M_Sale.Sale_CDebit,M_Sale.Sale_PersonShiftID,M_Sale.Sale_TableNumber,M_Sale.Sale_GuestCount,M_Sale.Sale_SlipCount,M_Sale.Sale_Gratuity,M_Sale.Sale_DisChk,M_Sale.Sale_SaleChk FROM M_Sale;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO SaleItem ( SaleItemID, SaleItem_SaleID, SaleItem_StockItemID, SaleItem_ShrinkQuantity, SaleItem_Quantity, SaleItem_LineNo, SaleItem_Vat, SaleItem_PriceOriginal, SaleItem_Price, SaleItem_Revoke, SaleItem_Reversal, SaleItem_DepositType, SaleItem_DepositCost, SaleItem_ActualCost, SaleItem_ListCost, SaleItem_SetID, SaleItem_WarehouseID ) SELECT M_SaleItem.SaleItemID, M_SaleItem.SaleItem_SaleID, M_SaleItem.SaleItem_StockItemID, M_SaleItem.SaleItem_ShrinkQuantity, M_SaleItem.SaleItem_Quantity, M_SaleItem.SaleItem_LineNo, M_SaleItem.SaleItem_Vat, M_SaleItem.SaleItem_PriceOriginal, M_SaleItem.SaleItem_Price, M_SaleItem.SaleItem_Revoke, M_SaleItem.SaleItem_Reversal, M_SaleItem.SaleItem_DepositType, M_SaleItem.SaleItem_DepositCost, M_SaleItem.SaleItem_ActualCost, M_SaleItem.SaleItem_ListCost, M_SaleItem.SaleItem_SetID, M_SaleItem.SaleItem_WarehouseID FROM M_SaleItem;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO SaleItemReciept ( SaleItemReciept_SaleItemID, SaleItemReciept_StockitemID, SaleItemReciept_Quantity, SaleItemReciept_DepositCost, SaleItemReciept_ListCost, SaleItemReciept_ActualCost, SaleItemReciept_Price ) SELECT M_SaleItemReciept.SaleItemReciept_SaleItemID, M_SaleItemReciept.SaleItemReciept_StockitemID, M_SaleItemReciept.SaleItemReciept_Quantity, M_SaleItemReciept.SaleItemReciept_DepositCost, M_SaleItemReciept.SaleItemReciept_ListCost, M_SaleItemReciept.SaleItemReciept_ActualCost, M_SaleItemReciept.SaleItemReciept_Price FROM M_SaleItemReciept;"; dbcnnMonth.Execute(sql); sql = "DELETE M_SaleItemReciept.* FROM M_SaleItemReciept;"; dbcnnMonth.Execute(sql); sql = "DELETE M_SaleItem.* FROM M_SaleItem;"; dbcnnMonth.Execute(sql); sql = "DELETE M_Sale.* FROM M_Sale;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO Supplier ( SupplierID, Supplier_SystemID, Supplier_Name, Supplier_PostalAddress, Supplier_PhysicalAddress, Supplier_Telephone, Supplier_Facimile, Supplier_RepresentativeName, Supplier_RepresentativeNumber, Supplier_ShippingCode, Supplier_OrderAttentionLine, Supplier_Terms, Supplier_Ullage, Supplier_discountCOD, Supplier_discount15days, Supplier_discount30days, Supplier_discount60days, Supplier_discount90days, Supplier_discount120days, Supplier_discountSmartCard, Supplier_discountDefault, Supplier_Current, Supplier_30Days, Supplier_60Days, Supplier_90Days, Supplier_120Days, Supplier_GRVtype ) "; sql = sql + "SELECT M_Supplier.SupplierID, M_Supplier.Supplier_SystemID, M_Supplier.Supplier_Name, M_Supplier.Supplier_PostalAddress, M_Supplier.Supplier_PhysicalAddress, M_Supplier.Supplier_Telephone, M_Supplier.Supplier_Facimile, M_Supplier.Supplier_RepresentativeName, M_Supplier.Supplier_RepresentativeNumber, M_Supplier.Supplier_ShippingCode, M_Supplier.Supplier_OrderAttentionLine, M_Supplier.Supplier_Terms, M_Supplier.Supplier_Ullage, M_Supplier.Supplier_discountCOD, M_Supplier.Supplier_discount15days, M_Supplier.Supplier_discount30days, M_Supplier.Supplier_discount60days, M_Supplier.Supplier_discount90days, M_Supplier.Supplier_discount120days, M_Supplier.Supplier_discountSmartCard, M_Supplier.Supplier_discountDefault, M_Supplier.Supplier_Current, M_Supplier.Supplier_30Days, M_Supplier.Supplier_60Days, M_Supplier.Supplier_90Days, M_Supplier.Supplier_120Days, M_Supplier.Supplier_GRVtype FROM M_Supplier;"; dbcnnMonth.Execute(sql); sql = "INSERT INTO SupplierTransaction ( SupplierTransactionID, SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) "; sql = sql + "SELECT M_SupplierTransaction.SupplierTransactionID, M_SupplierTransaction.SupplierTransaction_SupplierID, M_SupplierTransaction.SupplierTransaction_PersonID, M_SupplierTransaction.SupplierTransaction_TransactionTypeID, M_SupplierTransaction.SupplierTransaction_MonthEndID, M_SupplierTransaction.SupplierTransaction_MonthEndIDFor, M_SupplierTransaction.SupplierTransaction_DayEndID, M_SupplierTransaction.SupplierTransaction_ReferenceID, M_SupplierTransaction.SupplierTransaction_Date, M_SupplierTransaction.SupplierTransaction_Description, M_SupplierTransaction.SupplierTransaction_Amount, M_SupplierTransaction.SupplierTransaction_Reference FROM M_SupplierTransaction;"; dbcnnMonth.Execute(sql); sql = "DELETE M_SupplierTransaction.* FROM M_SupplierTransaction;"; dbcnnMonth.Execute(sql); //********************* sql = "INSERT INTO M_SupplierTransaction ( SupplierTransaction_SupplierID, SupplierTransaction_PersonID, SupplierTransaction_TransactionTypeID, SupplierTransaction_MonthEndID, SupplierTransaction_MonthEndIDFor, SupplierTransaction_DayEndID, SupplierTransaction_ReferenceID, SupplierTransaction_Date, SupplierTransaction_Description, SupplierTransaction_Amount, SupplierTransaction_Reference ) SELECT SupplierTransaction.SupplierTransaction_SupplierID, 1 AS person, 7 AS tranType, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID, 0 AS refID, Now() AS [date], '' AS [desc], Sum(SupplierTransaction.SupplierTransaction_Amount) AS SumOfSupplierTransaction_Amount, 'Month End' AS ref From SupplierTransaction, M_Company GROUP BY SupplierTransaction.SupplierTransaction_SupplierID, M_Company.Company_MonthEndID, M_Company.Company_MonthEndID, M_Company.Company_DayEndID;"; dbcnnMonth.Execute(sql); sql = "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = [M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_90Days], M_Supplier.Supplier_90Days = [M_Supplier]![Supplier_60Days], M_Supplier.Supplier_60Days = [M_Supplier]![Supplier_30Days], M_Supplier.Supplier_30Days = [M_Supplier]![Supplier_Current], M_Supplier.Supplier_Current = 0;"; dbcnnMonth.Execute(sql); //Debtor Age shifting if Credit //dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_150Days]<0),([M_Supplier]![Supplier_120Days]+[M_Supplier]![Supplier_150Days]),[M_Supplier]![Supplier_120Days]);" //dbcnnMonth.Execute "UPDATE M_Supplier SET M_Supplier.Supplier_150Days = iif(([M_Supplier]![Supplier_150Days]<0),0,[M_Supplier]![Supplier_150Days]);" dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_120Days]<0),([M_Supplier]![Supplier_90Days]+[M_Supplier]![Supplier_120Days]),[M_Supplier]![Supplier_90Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_120Days = iif(([M_Supplier]![Supplier_120Days]<0),0,[M_Supplier]![Supplier_120Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_90Days]<0),([M_Supplier]![Supplier_60Days]+[M_Supplier]![Supplier_90Days]),[M_Supplier]![Supplier_60Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_90Days = iif(([M_Supplier]![Supplier_90Days]<0),0,[M_Supplier]![Supplier_90Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_60Days]<0),([M_Supplier]![Supplier_30Days]+[M_Supplier]![Supplier_60Days]),[M_Supplier]![Supplier_30Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_60Days = iif(([M_Supplier]![Supplier_60Days]<0),0,[M_Supplier]![Supplier_60Days]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_Current = iif(([M_Supplier]![Supplier_30Days]<0),([M_Supplier]![Supplier_Current]+[M_Supplier]![Supplier_30Days]),[M_Supplier]![Supplier_Current]);"); dbcnnMonth.Execute("UPDATE M_Supplier SET M_Supplier.Supplier_30Days = iif(([M_Supplier]![Supplier_30Days]<0),0,[M_Supplier]![Supplier_30Days]);"); //Debtor Age shifting if Credit this.Cursor = System.Windows.Forms.Cursors.Default; //fix Server Path in Month End DBs ADODB.Recordset rsPOSList = default(ADODB.Recordset); string strSvrName = null; //Create a buffer strSvrName = new string(Strings.Chr(0), 255); //Get the computer name GetComputerName(strSvrName, ref 255); //remove the unnecessary chr$(0)'s strSvrName = Strings.Left(strSvrName, Strings.InStr(1, strSvrName, Strings.Chr(0))); strSvrName = Strings.Left(strSvrName, Strings.Len(strSvrName) - 1); //MsgBox strSvrName rsPOSList = modRecordSet.getRS(ref "SELECT * FROM POS;"); if (rsPOSList.RecordCount > 1) { //if more then 1 POS //Set rsMonthList = getRS("SELECT MonthEndID FROM MonthEnd;") //If rsMonthList.RecordCount Then // Do While rsMonthList.EOF = False // databaseName = "Month" & rsMonthList("MonthEndID") & ".mdb" if (fso.FileExists(modRecordSet.serverPath + databaseName)) { buildPath1_Month(ref databaseName, ref strSvrName); System.Windows.Forms.Application.DoEvents(); buildPath1_Month(ref databaseName, ref strSvrName); } // rsMonthList.moveNext // Loop //End If } //fix Server Path in Month End DBs //If rs("Company_OpenDebtor") = True Then //Else // If MsgBox("Would you like to Enable 'OPEN DEBTOR' option from starting month?" & vbCrLf & vbCrLf & "NOTE: It is recommended to turn this option now if you wish to use." & vbCrLf & vbCrLf & "You can enable it later from 'Store Setup and Security -> General Parameters'.", vbYesNo) = vbYes Then // sql = "UPDATE Company Set Company.Company_OpenDebtor = True;" // cnnDB.Execute sql // End If //End If //For Auto UpdatePOS on MonthEnd if (Interaction.MsgBox("You are requested to do UpdatePOS at this stage, to run some Reports." + Constants.vbCrLf + Constants.vbCrLf + "NOTE: If you have changed Prices for some items, UpdatePOS will update Terminals." + Constants.vbCrLf + Constants.vbCrLf + "If you want to Run UpdatePOS now select 'YES' or click 'NO' If you don't want to change the prices on terminals.", MsgBoxStyle.YesNo) == MsgBoxResult.Yes) { modApplication.blMEndUpdatePOS = true; My.MyProject.Forms.frmUpdatePOScriteria.ShowDialog(); } else { modApplication.blMEndUpdatePOS = false; } modApplication.blMEndUpdatePOS = false; }
private bool getMasterDB() { bool functionReturnValue = false; // ERROR: Not supported in C#: OnErrorStatement //getMasterDB = True cnnDBmaster = new ADODB.Connection(); string filename = null; if (modWinVer.Win7Ver() == true) { filename = "c:\\4posmaster\\4posmaster.mdb"; } else { filename = "4posmaster.mdb"; } if (System.IO.File.Exists(filename) == true) { var _with1 = cnnDBmaster; //.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" _with1.Provider = "Microsoft.ACE.OLEDB.12.0"; _with1.Open(filename); //.Open() functionReturnValue = true; } else { functionReturnValue = false; } //cnnDBmaster.Open("4posMaster") //gMasterPath = Split(Split(cnnDBmaster.ConnectionString, ";DBQ=")(1), ";")(0) //gMasterPath = Split(LCase(gMasterPath), "4posmaster.mdb")(0) ' //win 7 Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); Scripting.TextStream textstream = default(Scripting.TextStream); string lString = null; if (modWinVer.Win7Ver() == true) { //If fso.FileExists("C:\4POS\4POSWinPath.txt") Then // Set textstream = fso.OpenTextFile("C:\4POS\4POSWinPath.txt", ForReading, True) // lString = textstream.ReadAll // serverPath = lString '& "pricing.mdb" //Else // serverPath = "C:\4POSServer\" '"pricing.mdb" //End If gMasterPath = "c:\\4posmaster\\"; return functionReturnValue; //getMasterDB = True } //win 7 gMasterPath = Strings.Split(Strings.Split(cnnDBmaster.ConnectionString, ";DBQ=")[1], ";")[0]; gMasterPath = Strings.Split(Strings.LCase(gMasterPath), "4posmaster.mdb")[0]; return functionReturnValue; openConnection_Error: // functionReturnValue = false; return functionReturnValue; }
private void btnConnectionString_Click(object sender, EventArgs e) { ADODB.Connection Conn = null; String ConnectionString = tbConnectionString.Text; MSDASC.DataLinks dataLinks = new MSDASC.DataLinksClass(); if (ConnectionString == string.Empty) { Conn = (ADODB.Connection)dataLinks.PromptNew(); } else { Conn = new ADODB.Connection(); Conn.ConnectionString = tbConnectionString.Text; object TempConn = Conn; if (dataLinks.PromptEdit(ref TempConn)) ConnectionString = Conn.ConnectionString; } tbConnectionString.Text = Conn.ConnectionString; }