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;
}
