869 lines
25 KiB
C#
869 lines
25 KiB
C#
/*********************************************************************************************
|
|
* Copyright 2004 - Volian Enterprises, Inc. All rights reserved.
|
|
* Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
* ------------------------------------------------------------------------------
|
|
* $Workfile: VDB.cs $ $Revision: 9 $
|
|
* $Author: Kathy $ $Date: 8/16/05 2:53p $
|
|
*
|
|
* $History: VDB.cs $
|
|
*
|
|
* ***************** Version 9 *****************
|
|
* User: Kathy Date: 8/16/05 Time: 2:53p
|
|
* Updated in $/LibSource/VDB
|
|
* B2005-030: error if missing ndx
|
|
*
|
|
* ***************** Version 8 *****************
|
|
* User: Kathy Date: 5/19/05 Time: 11:05a
|
|
* Updated in $/LibSource/VDB
|
|
* speed up approve
|
|
*
|
|
* ***************** Version 7 *****************
|
|
* User: Jsj Date: 5/17/05 Time: 11:54a
|
|
* Updated in $/LibSource/VDB
|
|
* cleanup
|
|
*
|
|
* ***************** Version 6 *****************
|
|
* User: Kathy Date: 5/11/05 Time: 9:28a
|
|
* Updated in $/LibSource/VDB
|
|
* add selectinto method
|
|
*
|
|
* ***************** Version 5 *****************
|
|
* User: Kathy Date: 4/21/05 Time: 10:18a
|
|
* Updated in $/LibSource/VDB
|
|
* if dbt is < 512, make it 512
|
|
*
|
|
* ***************** Version 4 *****************
|
|
* User: Kathy Date: 3/08/05 Time: 1:47p
|
|
* Updated in $/LibSource/VDB
|
|
* Approval
|
|
*
|
|
* ***************** Version 3 *****************
|
|
* User: Jsj Date: 8/20/04 Time: 4:44p
|
|
* Updated in $/LibSource/VDB
|
|
* backed out previous change
|
|
*
|
|
* ***************** Version 2 *****************
|
|
* User: Jsj Date: 8/20/04 Time: 1:14p
|
|
* Updated in $/LibSource/VDB
|
|
* Added logic to handle a single quote in a string that is in an SQL
|
|
* statement.
|
|
*
|
|
* ***************** Version 1 *****************
|
|
* User: Kathy Date: 7/27/04 Time: 8:40a
|
|
* Created in $/LibSource/VDB
|
|
*********************************************************************************************/
|
|
|
|
using System;
|
|
using System.Data;
|
|
using System.Data.OleDb;
|
|
using System.Collections.Specialized;
|
|
using System.IO;
|
|
using System.Windows.Forms;
|
|
using System.Text;
|
|
using VDB_ConnType;
|
|
|
|
namespace VDB
|
|
{
|
|
/// <summary>
|
|
/// This the base class for a VE-PROMS database (table)
|
|
/// The vdbDBConnType class allows us to setup a default database type (ex dBase)
|
|
/// and gives us a cential place to add logic that would allow the end user select
|
|
/// the type of database (engine) to use via a configuration file (for example).
|
|
/// The virtual functions requires the class, that inherits this base class,
|
|
/// to define the SQL statements needed for its database operations.
|
|
/// </summary>
|
|
public abstract class VDB_Base
|
|
{
|
|
private vdb_DBConnType databaseType;
|
|
// made this public so that abstract functions that define SQL statements
|
|
// can check the type of database (if needed).
|
|
public int databaseTypeValue;
|
|
private OleDbConnection dbConn;
|
|
|
|
private string strDBPath = ""; //database directory path
|
|
private string strDBFile = ""; // database file name with extension
|
|
private string strDBTable = ""; // database file name without extension
|
|
private string strSelectCommand;
|
|
private string[] DBExtensions={"DBF","DBT"};
|
|
|
|
public VDB_Base(string strPath)
|
|
{
|
|
ParsePathAndFile(strPath);
|
|
databaseType = new vdb_DBConnType(); // currently defaults to dBase
|
|
databaseTypeValue = databaseType.dbType;
|
|
|
|
// check if dbt is at least 512, if not append 508 bytes. This is to fix
|
|
// a problem where dbts were created with only 4 bytes in the 16-bit code.
|
|
if ( isdBaseFile() && File.Exists(strDBTable+".dbt") )
|
|
{
|
|
FileInfo fi = new FileInfo(strDBTable + ".dbt");
|
|
if (fi.Length<512)
|
|
{
|
|
FileStream fs = new FileStream(strDBTable+".dbt",FileMode.Open,FileAccess.Write,FileShare.ReadWrite);
|
|
BinaryWriter bw = new BinaryWriter(fs);
|
|
bw.Seek(0,SeekOrigin.End);
|
|
byte []wrBytes = new byte[512-fi.Length];
|
|
for (int i=0; i<512-fi.Length; i++) wrBytes[i]=0;
|
|
wrBytes[4]=0x02;
|
|
bw.Write(wrBytes);
|
|
bw.Close();
|
|
}
|
|
fi = null;
|
|
}
|
|
dbConn = new OleDbConnection(databaseType.ConnectionString(strDBPath,""));
|
|
|
|
// if needed, create an INF file with index file names listed or if
|
|
// index file(s) don't exist, create them (B2005-030)
|
|
if ( isdBaseFile() && File.Exists(strDBFile))
|
|
CheckIndxAndINF();
|
|
}
|
|
|
|
private void ParsePathAndFile(string strInPath)
|
|
{
|
|
// do we have a file name with the path?
|
|
if (File.Exists(strInPath))
|
|
{
|
|
// Get the Path
|
|
int lastSlash = strInPath.LastIndexOf("\\");
|
|
if (lastSlash > 0)
|
|
{
|
|
strDBPath = strInPath.Substring(0,lastSlash);
|
|
lastSlash++; // move past slash
|
|
}
|
|
else // current directory path
|
|
{
|
|
strDBPath = ".\\";
|
|
lastSlash=0;
|
|
}
|
|
|
|
// Get the file (with extension)
|
|
strDBFile = strInPath.Substring(lastSlash);
|
|
|
|
//Get the file (without extension)
|
|
strDBTable = strDBFile;
|
|
int lastDot = strDBFile.LastIndexOf(".");
|
|
if (lastDot >0 ) // trim off the extension
|
|
strDBTable = strDBFile.Substring(0,lastDot);
|
|
}
|
|
else // no file name, just a path to database directory
|
|
{
|
|
strDBPath = strInPath;
|
|
if (strDBPath.Length == 0) // empty string for path
|
|
strDBPath = ".\\"; // current directory path
|
|
strDBFile = "";
|
|
strDBTable = "";
|
|
}
|
|
|
|
}
|
|
|
|
|
|
public string DBPath //database directory path
|
|
{
|
|
get
|
|
{
|
|
return strDBPath;
|
|
}
|
|
|
|
set
|
|
{
|
|
strDBPath = value.ToString();
|
|
}
|
|
}
|
|
public string DBFile // database file name with extension
|
|
{
|
|
get
|
|
{
|
|
return strDBFile;
|
|
}
|
|
// I don't think was want to set the file name
|
|
// set
|
|
// {
|
|
// strDBFile = value.ToString();
|
|
// }
|
|
}
|
|
|
|
public string DBTable // also dBase file name without extension
|
|
{
|
|
get
|
|
{
|
|
return strDBTable;
|
|
}
|
|
set
|
|
{
|
|
string tmp = value.ToString();
|
|
// If a path was passed in inore it. The connection already
|
|
// has a path associated with it and we don't want to get it angry.
|
|
int j = tmp.LastIndexOf("\\");
|
|
if ((j>0) && tmp[j] == '\\')
|
|
tmp = tmp.Substring(j+1);
|
|
|
|
tmp = tmp.ToUpper();
|
|
// if there is a file extension, chop it off but save it to assign
|
|
// to the strDBFile property
|
|
j = tmp.LastIndexOf(".");
|
|
if ((j>0) && tmp[j] == '.') // got an extension
|
|
{
|
|
strDBFile = tmp;
|
|
strDBTable = tmp.Substring(0,j);
|
|
}
|
|
else // no extension, assign the table name
|
|
{
|
|
strDBTable = tmp;
|
|
// if we're connected to dBase, then Tables are Files,
|
|
// So create a strDBFile by added .DBF to the table name
|
|
if (isdBaseFile())
|
|
strDBFile = tmp + ".DBF";
|
|
}
|
|
|
|
// We assigned the strDBTable (DBTable property) with a table name.
|
|
// Now we need to make sure there is a corresponding INF file
|
|
// for dBase index file information.
|
|
CreateINF();
|
|
}
|
|
}
|
|
|
|
/***
|
|
// To have single quote in a sql command string, you need to
|
|
// represent it as '' (two single quotes)
|
|
private string FixSingleQuotes(string sqlcmd)
|
|
{
|
|
string tmpstr1 = "(,= ";
|
|
string tmpstr2 = "), ";
|
|
string rtnstr = "";
|
|
int idx = 0;
|
|
int st = 0;
|
|
bool twoquotes;
|
|
int len = sqlcmd.Length;
|
|
|
|
while ((idx = sqlcmd.IndexOf("'",st)) >= 0)
|
|
{
|
|
twoquotes = true;
|
|
// is there a "(", comma , "=", or a space before the quote?
|
|
// if so then we do not want to add another quote
|
|
// because it part of the sql command, not the string
|
|
if (idx > 0 && (tmpstr1.IndexOf(sqlcmd[idx-1]) >= 0))
|
|
twoquotes = false;
|
|
|
|
// is there a ")", comma, or a space after the quote?
|
|
// if so then we do not want to add another quote
|
|
// because it part of the sql command, not the string
|
|
if (twoquotes)
|
|
if (idx > 0 && (idx == len) || (tmpstr2.IndexOf(sqlcmd[idx+1]) >= 0))
|
|
twoquotes = false;
|
|
|
|
// If there is already a second quote, then increment idx past
|
|
// it and set twoqotes to false.
|
|
if (twoquotes)
|
|
{
|
|
if (sqlcmd[idx+1] == '\'')
|
|
{
|
|
idx++; // second quote already there
|
|
twoquotes = false;
|
|
}
|
|
}
|
|
|
|
rtnstr += sqlcmd.Substring(st,(idx-st)+1);
|
|
|
|
if (twoquotes)
|
|
{
|
|
rtnstr += "'"; // add a second quote
|
|
}
|
|
twoquotes = false;
|
|
idx++;
|
|
st = idx;
|
|
}
|
|
rtnstr += sqlcmd.Substring(st);
|
|
|
|
return rtnstr;
|
|
}
|
|
***/
|
|
// Process a list of SQL commands. If an error occures, return 1
|
|
private int ProcessSQLCommands (OleDbCommand cmdSQL,StringCollection strColl)
|
|
{
|
|
int errs =0;
|
|
foreach (string sqlcmd in strColl)
|
|
{
|
|
try
|
|
{
|
|
/***
|
|
string fixedSqlCmd = FixSingleQuotes(sqlcmd);
|
|
cmdSQL.CommandText = fixedSqlCmd; //sqlcmd;
|
|
***/
|
|
cmdSQL.CommandText = sqlcmd;
|
|
cmdSQL.ExecuteNonQuery();
|
|
}
|
|
catch (Exception err)
|
|
{
|
|
errs = 1;
|
|
MessageBox.Show(err.Message.ToString(),"ProcessSQLCommands Error");
|
|
}
|
|
}
|
|
return errs;
|
|
}
|
|
|
|
//memo strings of greater than 255 where getting truncated and needed a separate
|
|
// reader.
|
|
public string GetMemo(string RecId)
|
|
{
|
|
string retstr=null;
|
|
OleDbCommand cmd=null;
|
|
OleDbDataReader dr = null;
|
|
StringBuilder tmp = new StringBuilder();
|
|
tmp.Append("SELECT [TEXTM] FROM [");
|
|
tmp.Append(DBTable);
|
|
tmp.Append("] WHERE RECID = '");
|
|
tmp.Append(RecId);
|
|
tmp.Append("'");
|
|
try
|
|
{
|
|
dbConn.Open();
|
|
cmd = new OleDbCommand(tmp.ToString(),dbConn);
|
|
dr = cmd.ExecuteReader();
|
|
if (dr.Read())retstr = dr.GetString(0);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
MessageBox.Show(e.Message,"VDB Error");
|
|
retstr = null;
|
|
}
|
|
if (dr !=null) dr.Close();
|
|
if (cmd != null) cmd.Dispose();
|
|
dbConn.Close();
|
|
return retstr;
|
|
}
|
|
|
|
// The default SQL statement will get all of the records in a database
|
|
// This should be suitable for all of our database, but just in case
|
|
// it can be overloaded.
|
|
public virtual string DefaultSelectCommand()
|
|
{
|
|
StringBuilder tmp = new StringBuilder();
|
|
tmp.Append("SELECT * FROM [");
|
|
tmp.Append(strDBTable);
|
|
tmp.Append("]");
|
|
return tmp.ToString();
|
|
}
|
|
|
|
// return and set the SELECT statement used to get a DataSet (DB_Data)
|
|
public string SelectCmd
|
|
{
|
|
get
|
|
{
|
|
return strSelectCommand;
|
|
}
|
|
set
|
|
{
|
|
strSelectCommand = value.ToString();
|
|
}
|
|
|
|
}
|
|
|
|
public System.Data.DataSet DB_Data
|
|
{
|
|
// return a DataSet containing the database (table) records
|
|
get
|
|
{
|
|
dbConn.Open();
|
|
// if a select command was not created, then use the default
|
|
// select all command
|
|
if (strSelectCommand == null || strSelectCommand.Equals(""))
|
|
strSelectCommand = DefaultSelectCommand();
|
|
|
|
OleDbDataAdapter da =new OleDbDataAdapter(strSelectCommand,dbConn);
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
da.Fill(ds);
|
|
}
|
|
catch (Exception err)
|
|
{
|
|
MessageBox.Show(err.Message.ToString(),"VDB Error");
|
|
ds = null; // return null if error getting dataset
|
|
}
|
|
dbConn.Close();
|
|
return ds;
|
|
}
|
|
|
|
// update the database (table) with the changes made to the DataSet
|
|
set
|
|
{
|
|
// pass in a dataset, update the changed/added/deleted records
|
|
int err = 0;
|
|
StringCollection CommandList = new StringCollection();
|
|
// Open a database connection
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
// dbTransAct = dbConn.BeginTransaction(IsolationLevel.Serializable);
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Get only the new, modified, and deleted dataset rows
|
|
DataSet dsChanges = value.GetChanges();
|
|
|
|
// Get the database Table (the database file in dBase terms)
|
|
DataTable tbl = dsChanges.Tables[0];
|
|
|
|
// Spin through the changed rows (in the DataSet) and add
|
|
// the proper SQL command to the list of commands
|
|
foreach (DataRow row in tbl.Rows)
|
|
{
|
|
switch (row.RowState)
|
|
{
|
|
case DataRowState.Modified:
|
|
CommandList.Add(GetRecUpdateStr(tbl,row));
|
|
break;
|
|
|
|
case DataRowState.Added:
|
|
CommandList.Add(GetRecInsertStr(tbl,row));
|
|
break;
|
|
|
|
case DataRowState.Deleted:
|
|
CommandList.Add(RecDeleteStr(tbl,row));
|
|
break;
|
|
|
|
default:
|
|
// Ignore the DataRowState.Detached
|
|
// and the DataRowState.Unchanged
|
|
break;
|
|
}
|
|
}
|
|
|
|
// Process the list of SQL commands
|
|
err = ProcessSQLCommands(cmdSQL,CommandList);
|
|
|
|
// If no errors in the database transactions,
|
|
// commit the changes in both the database
|
|
// and the dataset.
|
|
if (err == 0)
|
|
{
|
|
dbTransAct.Commit();
|
|
value.AcceptChanges();
|
|
}
|
|
else
|
|
{
|
|
// If there was an error then roll back the changes
|
|
dbTransAct.Rollback();
|
|
}
|
|
dbConn.Close();
|
|
}
|
|
}
|
|
|
|
public bool ProcessACommand(string cmd)
|
|
{
|
|
// open a connection and setup for the database command
|
|
dbConn.Open();
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
try
|
|
{
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
cmdSQL.CommandText = cmd;
|
|
cmdSQL.ExecuteNonQuery();
|
|
dbTransAct.Commit();
|
|
dbConn.Close();
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
dbTransAct.Rollback();
|
|
dbConn.Close();
|
|
MessageBox.Show(e.Message,"Error writing to database");
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
// Create a function that returns an SQL statement to use for updating
|
|
// existing records in a database
|
|
public abstract string GetRecUpdateStr(DataTable tbl,DataRow row);
|
|
|
|
// Create a function that returns an SQL statement to use for inserting
|
|
// records in a database
|
|
public abstract string GetRecInsertStr(DataTable tbl,DataRow row);
|
|
|
|
// Create a function that returns an SQL statement to use for deleting
|
|
// existing records in a database
|
|
public abstract string RecDeleteStr(DataTable tbl,DataRow row);
|
|
|
|
// Create a function that returns a list of SQL commands that will create
|
|
// a new table
|
|
public abstract StringCollection CreateTableStatements(string strTblName);
|
|
|
|
// Create a function that returns a list of SQL commands that selects from
|
|
// given table into another table. (Used for copying to approved/tmpchg).
|
|
public virtual StringCollection SelectIntoStatements(string destdb){return null;}
|
|
|
|
// Create a function that returns a list of SQL commands that will create
|
|
// the index files needed for this database file.
|
|
public virtual StringCollection CreateIndexFilesStatements(){return null;}
|
|
|
|
// Create a function that returns a list of SQL commands that will create
|
|
// the first (empty) record.
|
|
public virtual StringCollection CreateFirstRecordStatement(string strTblName)
|
|
{
|
|
StringCollection rtnStrColl = new StringCollection();
|
|
// Default to no "first record" (a.k.a record zero)
|
|
// Return an empty set of SQL commands
|
|
return rtnStrColl;
|
|
}
|
|
|
|
// Override this function if post processing is needed after creating
|
|
// a table.
|
|
public virtual int PostCreateTableFunction()
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
public int CreateTable(string strTlbName)
|
|
{
|
|
int errs =0;
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
// dbTransAct = dbConn.BeginTransaction(IsolationLevel.Serializable);
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Process the list of SQL commands
|
|
// NOTE: if you create an associated INF file (for dBase indexes)
|
|
// before calling ProcessSQLCommands(), the INF file will be
|
|
// deleted (by the database engine) when the Create Table
|
|
// SQL command is run.
|
|
errs = ProcessSQLCommands(cmdSQL,CreateTableStatements(strTlbName));
|
|
|
|
// The PostCreateTableFunction() function should:
|
|
// - call CreateINF()if using dBase files with indexes
|
|
// - create the first "blank" record if required
|
|
if (errs == 0)
|
|
errs = PostCreateTableFunction();
|
|
// Create the first record
|
|
if (errs == 0)
|
|
errs = ProcessSQLCommands(cmdSQL,CreateFirstRecordStatement(strTlbName));
|
|
|
|
dbConn.Close();
|
|
return errs;
|
|
}
|
|
|
|
// Just a pass through, update string is passed in.
|
|
public bool UpdateUsing(string updstr)
|
|
{
|
|
return (ProcessACommand(updstr));
|
|
}
|
|
|
|
// Select data from the current table into a new table, where the new table
|
|
// is the parameter destfile which includes the pathname to the database
|
|
// file.
|
|
public int SelectIntoNewTable(string destfile)
|
|
{
|
|
int errs=0;
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Process the list of SQL commands, selectinto has no where clause.
|
|
// It only requires that the destination database file be included.
|
|
errs = ProcessSQLCommands(cmdSQL,SelectIntoStatements(destfile));
|
|
|
|
dbConn.Close();
|
|
return errs;
|
|
}
|
|
|
|
// make statement to insert into (an existing table). If table specific
|
|
// commands are needed, move this to the specific table code.
|
|
public StringCollection InsertIntoStatements(string destdb, string WhereStr)
|
|
{
|
|
StringCollection rtnStrColl = new StringCollection();
|
|
StringBuilder str = new StringBuilder();
|
|
|
|
str.Append("INSERT INTO ");
|
|
str.Append(DBTable);
|
|
str.Append(" IN '");
|
|
str.Append(destdb.Substring(0,destdb.LastIndexOf('\\')));
|
|
str.Append("' 'dBase III;' SELECT * FROM ");
|
|
str.Append(DBTable);
|
|
|
|
if (WhereStr.Length > 0) // Was a WHERE clause passed in?
|
|
{
|
|
// Add " WHERE " to the beginning if it is not already there
|
|
string tmpstr = WhereStr.ToUpper();
|
|
if (tmpstr[0] != ' ')
|
|
{
|
|
str.Append(" ");
|
|
if (!tmpstr.StartsWith("WHERE "))
|
|
str.Append("WHERE ");
|
|
}
|
|
else if (!tmpstr.StartsWith(" WHERE "))
|
|
str.Append(" WHERE");
|
|
// add the passed in WHERE clause
|
|
str.Append(WhereStr);
|
|
}
|
|
rtnStrColl.Add(str.ToString());
|
|
return rtnStrColl;
|
|
}
|
|
|
|
public int InsertInto(string destfile, string WhereStr)
|
|
{
|
|
int errs=0;
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Process the list of SQL commands, selectinto has no where clause, but
|
|
// requires that the index file be created ("",true arguments)
|
|
errs = ProcessSQLCommands(cmdSQL,InsertIntoStatements(destfile,WhereStr));
|
|
dbConn.Close();
|
|
return errs;
|
|
}
|
|
|
|
// make statement to delete records based on input criteria. If table specific
|
|
// commands are needed, move this to the specific table code.
|
|
public StringCollection DeleteSelectedStr(string whereclause)
|
|
{
|
|
StringCollection rtnStrColl = new StringCollection();
|
|
StringBuilder deleteStr = new StringBuilder();
|
|
deleteStr.Append("DELETE FROM [");
|
|
deleteStr.Append(DBTable);
|
|
deleteStr.Append("]");
|
|
|
|
// Add the WHERE clause
|
|
deleteStr.Append(" WHERE ");
|
|
deleteStr.Append(whereclause);
|
|
rtnStrColl.Add(deleteStr.ToString());
|
|
return rtnStrColl;
|
|
}
|
|
|
|
// Delete selected records based on criteria from WhereStr parameter.
|
|
public int DeleteSelected(string WhereStr)
|
|
{
|
|
int errs=0;
|
|
dbConn.Open();
|
|
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Process the list of SQL commands
|
|
errs = ProcessSQLCommands(cmdSQL,DeleteSelectedStr(WhereStr));
|
|
|
|
dbConn.Close();
|
|
return errs;
|
|
}
|
|
|
|
public abstract StringCollection DeleteTableStatements(string strTblName);
|
|
|
|
// Override this function if post processing is needed after removing
|
|
// a table.
|
|
public int PostDeleteTableFunction()
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
public virtual int DeleteTable(string strTblName)
|
|
{
|
|
int errs =0;
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
// dbTransAct = dbConn.BeginTransaction(IsolationLevel.Serializable);
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// Process the list of SQL commands
|
|
errs = ProcessSQLCommands(cmdSQL,DeleteTableStatements(strTblName));
|
|
|
|
// do any needed post processing
|
|
if (errs == 0)
|
|
errs = PostDeleteTableFunction();
|
|
|
|
if (errs == 0) // no errors
|
|
{
|
|
strDBTable = "";
|
|
if (databaseTypeValue.Equals(vdb_DBConnType.DBTypes.DBaseIII))
|
|
{
|
|
strDBFile = "";
|
|
}
|
|
}
|
|
dbConn.Close();
|
|
return errs;
|
|
}
|
|
|
|
// Override this function to create a text file with the same name as the
|
|
// dBase file, except give it an extension of INF instead of DBF.
|
|
// Inside this text file should be the index file names associated
|
|
// with the dBasefile. (each file name should be on its own line)
|
|
// Example, for USAGERO.DBF you should create a USAGERO.INF and inside
|
|
// the INF file should be:
|
|
// NDX1=USAGERO.NDX
|
|
// NDX2=USAGROID.NDX
|
|
//
|
|
// If the dBase does not use an index file, then there is no need to
|
|
// override this virtual funtion.
|
|
public virtual int CreateINF()
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
public bool isdBaseFile()
|
|
{
|
|
return databaseTypeValue == (int)vdb_DBConnType.DBTypes.DBaseIII;
|
|
}
|
|
public virtual string GetCreateIndx1String()
|
|
{
|
|
return null;
|
|
}
|
|
public virtual string GetCreateIndx2String()
|
|
{
|
|
return null;
|
|
}
|
|
public virtual bool IndexFilesExist()
|
|
{
|
|
return true;
|
|
}
|
|
|
|
public virtual void DeleteIndexFiles()
|
|
{
|
|
}
|
|
|
|
// use this to clean up/create index & INF Files. The INF files are used to
|
|
// define which index files are used for the given database file. However, if file
|
|
// names are listed in the INF file and the NDX file does not exist, an error was
|
|
// given - so be sure that the index files exist too. Note that the INF file
|
|
// must be created after the NDX file, because an error occurs if creating an NDX
|
|
// file when an INF file exists. This was done to fix B2005-030.
|
|
public bool CheckIndxAndINF()
|
|
{
|
|
try
|
|
{
|
|
if (!IndexFilesExist())
|
|
{
|
|
string cwd = Directory.GetCurrentDirectory();
|
|
Directory.SetCurrentDirectory(DBPath);
|
|
// if more than one index file exists for a dbf, then delete (in case
|
|
// one of them did exist)
|
|
DeleteIndexFiles();
|
|
// CreateIndexFiles creates the NDX and also INF.
|
|
CreateIndexFiles();
|
|
Directory.SetCurrentDirectory(cwd);
|
|
}
|
|
else
|
|
CreateINF(); // CreateINF creates a new copy (overwrites any existing)
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
MessageBox.Show("Could not set up index files " + e.Message,"Check For Indexes");
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
public virtual int CreateIndexFiles()
|
|
{
|
|
int errs =0;
|
|
dbConn.Open();
|
|
|
|
// Setup for a series of database transactions
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand();
|
|
System.Data.OleDb.OleDbTransaction dbTransAct;
|
|
dbTransAct = dbConn.BeginTransaction();
|
|
cmdSQL.Connection = dbConn;
|
|
cmdSQL.Transaction = dbTransAct;
|
|
|
|
// if dbase, delete the inf file. If it exists, an error occurs when
|
|
// creating the index.
|
|
if (File.Exists(strDBTable+".INF")) File.Delete(strDBTable+".INF");
|
|
// Process the list of SQL commands
|
|
errs = ProcessSQLCommands(cmdSQL,CreateIndexFilesStatements());
|
|
dbConn.Close();
|
|
if (errs==0 && isdBaseFile()) CreateINF();
|
|
return errs;
|
|
}
|
|
|
|
public void Pack()
|
|
{
|
|
// the database was not getting 'packed' on a delete, i.e. the deleted
|
|
// records were still visible in the 16-bit codes. We'll pack here by
|
|
// selecting into a temp db & renaming files.
|
|
|
|
// first see if there is an index, i.e. GetCreateIndxString will return
|
|
// a string if an index file is needed. If so, we know that we'll have
|
|
// to create the index(es) & also create an INF file.
|
|
string createindx = GetCreateIndx1String();
|
|
|
|
string origname = this.DBTable;
|
|
string newtbname=null;
|
|
try
|
|
{
|
|
if (this.DBTable.Length==8)
|
|
newtbname = "z" + this.DBTable.Substring(1,7);
|
|
else
|
|
newtbname = "z" + this.DBTable;
|
|
|
|
string sqlcmd = "select * into [" + newtbname + "] from [" + origname + "];";
|
|
|
|
// Setup for a series of database transactions
|
|
dbConn = new OleDbConnection(databaseType.ConnectionString(strDBPath,""));
|
|
dbConn.Open();
|
|
System.Data.OleDb.OleDbCommand cmdSQL = new OleDbCommand(sqlcmd,dbConn);
|
|
cmdSQL.ExecuteNonQuery();
|
|
dbConn.Close();
|
|
|
|
// delete original & rename (dbf, dbt), then make new index file & INF
|
|
// file, if they exist.
|
|
foreach(string str in DBExtensions)
|
|
{
|
|
string orig = strDBPath+"\\"+origname + "." + str;
|
|
if (File.Exists(orig))
|
|
{
|
|
File.Delete(orig);
|
|
string newnm = strDBPath + "\\" + newtbname + "." + str;
|
|
File.Move(newnm,orig);
|
|
}
|
|
}
|
|
if (createindx!=null)
|
|
{
|
|
// delete the index files...
|
|
DeleteIndexFiles();
|
|
dbConn.Open();
|
|
cmdSQL = new OleDbCommand(createindx,dbConn);
|
|
cmdSQL.ExecuteNonQuery();
|
|
createindx = GetCreateIndx2String();
|
|
if (createindx!=null)
|
|
{
|
|
cmdSQL.CommandText = createindx;
|
|
cmdSQL.ExecuteNonQuery();
|
|
}
|
|
dbConn.Close();
|
|
CreateINF();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
MessageBox.Show(e.Message,"Could not pack database, use data integrity checker on procedure");
|
|
}
|
|
}
|
|
}
|
|
}
|