public Select_DataBase_Form(Form pParentForm,DBConnection my_connection, string sTitle)
{
m_ParentForm = pParentForm;
m_Title = sTitle;
m_SQL_Connection = my_connection;
InitializeComponent();
}
public CreateDataBase_Form(ref DBConnection con)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
m_con = con;
//
// TODO: Add any constructor code after InitializeComponent call
//
}
public bool FillDataTable(DBConnection m_SQL_connection, String SqlCmd,List<SQL_Parameter> lSQL_Parameter, ref DataTable dt, ref String csError)
{
dt.TableName = TableName;
if (m_SQL_connection.FillDataTable(ref dt, SqlCmd, lSQL_Parameter,ref csError))
{
return true;
}
else
{
return false;
}
}
/*********************************************************/
public DBTableControl(Form pForm, string XmlRootName,string xmlFolder)
{
InitializeComponent();
m_con = new DBConnection();
if (XmlRootName != null)
{
m_xml = new xml(xmlFolder, XmlRootName, pForm);
string csError = null;
if (!m_xml.Load(ref csError))
{
LogFile.LogFile.Write(LogFile.LogFile.LOG_LEVEL_RUN_RELEASE, csError);
}
}
}
public ConnectionDialog(ConnectionDialog_enum ConnectionConnectionDialog_type, DBConnection con, string sTitle, NavigationButtons.Navigation xnav)
{
m_Title = sTitle;
my_ConnectionDialog_enum = ConnectionConnectionDialog_type;
InitializeComponent();
nav = xnav;
usrc_NavigationButtons1.Init(nav);
usrc_NavigationButtons1.Button_NEXT_Enabled = false;
if (con.RecentItemsFolder.Length ==0)
{
con.RecentItemsFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
}
this.cmb_ServerName.RecentItemsFolder = con.RecentItemsFolder;
this.cmb_DataBaseName.RecentItemsFolder = con.RecentItemsFolder;
this.cmb_UserName.RecentItemsFolder = con.RecentItemsFolder;
Y[0] = cmb_DataBaseName.Top;
Y[1] = rdb_UseWindowsAuthentication.Top;
Y[2] = rdb_SQL_Server_Authentication.Top;
Y[3] = cmb_UserName.Top;
Y[4] = txt_Password.Top;
m_con = con;
switch (m_con.DBType)
{
case DBConnection.eDBType.MYSQL:
rdb_UseWindowsAuthentication.Enabled = false;
rdb_SQL_Server_Authentication.Enabled = false;
this.radioButton_MicrosoftSQL.Checked = false;
this.radioButton_MySqlServer.Checked = true;
break;
case DBConnection.eDBType.MSSQL:
this.radioButton_MicrosoftSQL.Checked = true;
rdb_UseWindowsAuthentication.Enabled = true;
rdb_SQL_Server_Authentication.Enabled = true;
if (m_con.WindowsAuthentication)
{
rdb_UseWindowsAuthentication.Checked = true;
}
else
{
rdb_SQL_Server_Authentication.Checked = true;
}
break;
}
}
public TestConnectionForm(Form xParentForm,DBConnection sqlCon, bool bTimerClose, bool bDataBaseConnection,string sTitle)
{
m_ParentForm = xParentForm;
this.Owner = m_ParentForm;
m_bDataBaseConnection = bDataBaseConnection;
m_Title = sTitle;
m_bTimerClose = bTimerClose;
sqlConn = sqlCon;
InitializeComponent();
m_eStartPositionOfTestConnectionForm = sqlCon.StartPositionOfTestConnectionForm;
switch (sqlConn.DBType)
{
case DBConnection.eDBType.MYSQL:
break;
case DBConnection.eDBType.MSSQL:
break;
case DBConnection.eDBType.SQLITE:
lblUserName.Visible = false;
this.label_UserName.Visible = false;
break;
}
}
public Connection_Control(DBConnection con, NavigationButtons.Navigation xnav)
{
InitializeComponent();
m_con = con;
nav = xnav;
this.lbl_DataSourceAndDatabase.AutoSize = true;
switch (m_con.DBType)
{
case DBConnection.eDBType.SQLITE:
this.lbl_ServerType.Text = m_con.ConnectionName+":" + lngConn.s_ServerType.s + " = SQLITE";
this.lbl_DataSourceAndDatabase.Text = m_con.DataBase;
break;
case DBConnection.eDBType.MSSQL:
this.lbl_ServerType.Text = m_con.ConnectionName + ":" + lngConn.s_ServerType.s + " = MSSQL";
this.lbl_DataSourceAndDatabase.Text = lngConn.s_Server.s + " = " + m_con.DataSource + ", " + lngConn.s_DataBase.s + " = " + m_con.DataBase;
break;
case DBConnection.eDBType.MYSQL:
this.lbl_ServerType.Text = m_con.ConnectionName + ":" + lngConn.s_ServerType.s + " = MYSQL";
this.lbl_DataSourceAndDatabase.Text = lngConn.s_Server.s + " = " + m_con.DataSource + ", " + lngConn.s_DataBase.s + " = " + m_con.DataBase;
break;
}
this.Width = this.lbl_DataSourceAndDatabase.Left + this.lbl_DataSourceAndDatabase.Width + 10;
}
internal bool GetID(DBConnection xcon,string ColumnName_ID, ref long id, ref string csError)
{
while (this.pParentTable != null)
{
if (this.pParentTable.GetID(xcon,this.TableName + "_ID", ref id, ref csError))
{
string sql = "SELECT " + this.TableName + "_ID" + " FROM " + this.pParentTable.TableName + " WHERE ID = " + id.ToString() + ";";
DataTable dt = new DataTable();
if (xcon.ReadDataTable(ref dt,sql,ref csError))
{
if (dt.Rows.Count == 1)
{
object obj= dt.Rows[0][0];
if ((obj.GetType() == typeof(int)) || (obj.GetType() == typeof(long)))
{
id = Convert.ToInt64(obj);
this.tag_ID = id;
return true;
}
else
{
csError = "Error:Result is not of type int (32bit) or long (64bit) for:" + sql + " !";
return false;
}
}
else
{
csError = "Error:Result has to many rows (dt.rows.Count=" + dt.Rows.Count.ToString()+") for:" + sql + " !";
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
Column id_column = this.IdentityColumn();
if (id_column != null)
{
ID top_ID = (ID)id_column.obj;
id = top_ID.val;
this.tag_ID = id;
return true;
}
else
{
csError = "ID column not found in table:" + this.TableName + " !";
return false;
}
}
public bool GetTableView(DBConnection m_SQL_connection, ref DataTable dt, ref String csError,int iTopAmount, string sColumns ,string sWhere )
{
String SqlCmd = null;
if (m_SQL_connection.DBType==DBConnection.eDBType.SQLITE)
{
string sOrderBy;
if ((sColumns.Equals("*")) || sColumns.Contains("[ID]"))
{
sOrderBy = " order by [" + TableName + ".ID] desc;";
if (sColumns.Contains("[ID]"))
{
sColumns.Replace("[ID]", "[" + TableName + ".ID]");
}
}
else
{
sOrderBy = "";
}
if (iTopAmount > 0)
{
string sLimit = " LIMIT " + iTopAmount.ToString() + ";";
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = " SELECT " +sColumns +@"
FROM
" + ViewName +" " + sWhere + sLimit;
}
else
{
SqlCmd = @"
SELECT " + sColumns +@"
FROM
" + TableName + " " + sWhere + sOrderBy + sLimit;
}
}
else
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = " SELECT " + sColumns + " FROM " + ViewName + " " + sWhere;// +" order by ID desc;";
}
else
{
SqlCmd = " SELECT " + sColumns + " FROM " + TableName + " " + sWhere + sOrderBy;
}
}
}
else
{
if (iTopAmount > 0)
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT top " + iTopAmount.ToString() + " " + sColumns + @"
FROM
" + ViewName + " " + sWhere;// +" order by ID desc;";
}
else
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT top " + iTopAmount.ToString() + " " + sColumns + @"
FROM
" + TableName + " " + sWhere + " order by " + TableName + ".ID desc;";
}
}
else
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT " + sColumns + @"
FROM
" + " " + sWhere + ViewName;// +" order by ID desc;";
}
else
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT " + sColumns + @"
FROM
" + TableName + " " + sWhere + " order by " + TableName + ".ID desc;";
}
}
}
dt.TableName = TableName;
if (m_SQL_connection.ReadDataTable(ref dt, SqlCmd, ref csError))
{
return true;
}
else
{
return false;
}
}
public bool FillDataInputControl(DBConnection x_SQL_connection, long ID, bool bSetInitialValues,ref string csError)
{
// 1 Read row data
iFillTableData++;
this.current_row_ID = new ID_v();
this.current_row_ID.v = ID;
String sSQL_ReadRow="";
switch (x_SQL_connection.DBType)
{
case DBConnection.eDBType.MYSQL:
sSQL_ReadRow = "USE " + x_SQL_connection.DataBase + ";\n"
+ "SELECT ";
break;
case DBConnection.eDBType.MSSQL:
sSQL_ReadRow = "USE " + x_SQL_connection.DataBase + "\n"
+ "SELECT ";
break;
case DBConnection.eDBType.SQLITE:
sSQL_ReadRow = " SELECT ";
break;
}
int i=0;
foreach (Column col in Column)
{
if (!col.IsIdentity)
{
if (i == 0)
{
sSQL_ReadRow += col.Name;
}
else
{
sSQL_ReadRow += "," + col.Name;
}
i++;
}
}
sSQL_ReadRow += " FROM " + TableName + " WHERE ID = " + ID.ToString();
DataTable dt = new DataTable(this.TableName);
dt.TableName = this.TableName;
if (x_SQL_connection.ReadDataTable(ref dt, sSQL_ReadRow, ref csError))
{
// Now we have data in dt
foreach (Column col in this.Column)
{
if (col.IsIdentity)
{
ID my_ID = (ID) col.obj;
my_ID.val = ID;
}
else
{
if (col.fKey != null)
{
if (dt.Rows.Count > 0)
{
if (dt.Rows[0][col.Name].GetType() == typeof(long))
{
long Identity = (long)dt.Rows[0][col.Name];
col.fKey.reference_ID = new ID_v();
col.fKey.reference_ID.v = Identity;
if (col.fKey.fTable.FillDataInputControl(x_SQL_connection, Identity,bSetInitialValues, ref csError))
{
if (col.fKey.fTable.myGroupBox != null)
{
col.fKey.fTable.myGroupBox.Changed_up = false;
}
}
else
{
iFillTableData--;
return false;
}
}
else if (dt.Rows[0][col.Name].GetType() == typeof(System.DBNull))
{
col.fKey.reference_ID = null;
col.fKey.fTable.Hide_And_Init_Reference_ID();
if (col.fKey.fTable.myGroupBox != null)
{
col.fKey.fTable.myGroupBox.Changed_up = false;
}
}
else
{
LogFile.Error.Show("ERROR:SQLTable:FillDataInputControl:Unknown Data Column type !");
iFillTableData--;
return false;
}
}
else
{
}
}
else
{
if (col.InputControl != null)
{
object dVal = dt.Rows[0][col.Name];
col.SetValue(dVal);
if (bSetInitialValues)
{
col.InputControl.Init_SetValue(dVal);
}
else
{
col.InputControl.SetValue(dVal);
}
col.InputControl.Changed = false;
}
}
}
}
Show_And_Init_Reference_ID();
iFillTableData--;
return true;
}
else
{
iFillTableData--;
return false;
}
}
public StringBuilder MySQLcmd_DropAllTables(DBConnection xcon)
{
StringBuilder strSqlAll = new StringBuilder("USE [" + xcon.DataBase + @"]
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
");
int iTable;
int iTableCount;
iTableCount = items.Count;
foreach (DataBaseView dbv in SQL_DataBase_VIEW_List)
{
StringBuilder strTable = new StringBuilder(dbv.MySQLcmd_DropView());
strSqlAll.Append(strTable);
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
StringBuilder strTable = new StringBuilder(items[iTable].MySQLcmd_AlterTableDropConstraintForeign());
strSqlAll.Append(strTable);
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
StringBuilder strTable = new StringBuilder(items[iTable].MySQLcmd_DropTable());
strSqlAll.Append(strTable);
}
return strSqlAll;
}
public StringBuilder SQLcmd_DropAll_Views(DBConnection xcon)
{
StringBuilder strSqlAll = new StringBuilder("USE [" + xcon.DataBase + @"]
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
");
// int iTable;
int iTableCount;
iTableCount = items.Count;
foreach (DataBaseView dbv in SQL_DataBase_VIEW_List)
{
StringBuilder strTable = null;
switch (xcon.DBType)
{
case DBConnection.eDBType.MSSQL:
strTable = new StringBuilder(dbv.SQLcmd_DropView());
break;
case DBConnection.eDBType.MYSQL:
strTable = new StringBuilder(dbv.MySQLcmd_DropView());
break;
case DBConnection.eDBType.SQLITE:
strTable = new StringBuilder(dbv.SQLitecmd_DropView());
break;
}
if (strTable != null)
{
strSqlAll.Append(strTable);
}
else
{
LogFile.Error.Show("ERROR:CodeTables:DBTableControl:SQLcmd_DropAllTables:Not valid xcon.DBType : " + xcon.DBType.ToString());
}
strSqlAll.Append(strTable);
}
return strSqlAll;
}
public CheckConnectionThreadParam(DBConnection xConn, bool bDataBaseConnection)
{
m_bDataBaseConnection = bDataBaseConnection;
m_Conn = xConn;
}
public bool ValueExistInTable(DBConnection my_SQLConnection)
{
SQL_Parameter sqlpar = new SQL_Parameter();
List<SQL_Parameter> lsqlPar = new List<SQL_Parameter>();
StringBuilder sbl = new StringBuilder("\nSELECT " + this.Name + " FROM " + this.ownerTable.TableName + " WHERE " + this.Name + " = " + DBTypes.DBtypesFunc.DbValueForSql(ref this.obj,this.BasicType(), this.ownerTable.sThisVar, ref lsqlPar, this.Name));
string cs_Error = "";
int Index=0;
Object ObjRet = null;
if (my_SQLConnection.ExecuteQuerySQL(sbl, lsqlPar, ref Index, ref ObjRet, ref cs_Error, this.ownerTable.TableName))
{
if (ObjRet != null)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
public bool GetTableData(DBConnection m_SQL_connection, ref DataTable dt, ref String csError, int iLimit, string sWhere)
{
string sxWhere;
if (sWhere == null)
{
sxWhere = "";
}
else
{
sxWhere = sWhere;
}
String SqlCmd ="";
if (iLimit > 0)
{
switch (m_SQL_connection.DBType)
{
case DBConnection.eDBType.MYSQL:
SqlCmd = "USE " + m_SQL_connection.DataBase + @";
SELECT *
FROM
" + TableName + sxWhere + " order by ID LIMIT " + iLimit.ToString()+";";
break;
case DBConnection.eDBType.MSSQL:
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT top " + iLimit.ToString()+ @" *
FROM
" + TableName + sxWhere + " order by " + TableName + ".ID desc;";
break;
case DBConnection.eDBType.SQLITE:
SqlCmd = @"
SELECT *
FROM
" + TableName + sxWhere + " order by " + TableName + ".ID desc LIMIT " + iLimit.ToString();
break;
}
}
else
{
switch (m_SQL_connection.DBType)
{
case DBConnection.eDBType.MYSQL:
SqlCmd = "USE " + m_SQL_connection.DataBase + @";
SELECT *
FROM
" + TableName + sxWhere;
break;
case DBConnection.eDBType.MSSQL:
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT *
FROM
" + TableName + sxWhere;
break;
case DBConnection.eDBType.SQLITE:
SqlCmd = @"
SELECT *
FROM
" + TableName + sxWhere;
break;
}
}
dt.TableName = TableName;
if (m_SQL_connection.ReadDataTable(ref dt, SqlCmd, ref csError))
{
return true;
}
else
{
return false;
}
}
public Select_DataSource_Form(DBConnection con)
{
m_con = con;
InitializeComponent();
this.lbl_SelectedServer.Text = lngConn.s_Select_Server.s + ":";
}
public View_AccessR_Form(DBConnection con)
{
m_con = con;
InitializeComponent();
}
public RemoteDB_data(bool bReset,string inifile_prefix,int i, DBConnection.eDBType xDBType, string xConnectionName)
{
ConnectionName = xConnectionName;
DBType = xDBType;
Settings_Index = i;
m_Settings_RemoteDB = new DBConnectionControl_Settings.Settings_RemoteDB(inifile_prefix, Settings_Index);
ReadSettings(bReset);
}
private bool DropTables(DBConnection db_con)
{
switch (db_con.DBType)
{
case DBConnection.eDBType.MSSQL:
string sql_DropTables = @"
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginUsersAndLoginRoles') DROP TABLE LoginUsersAndLoginRoles;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginUsers') DROP TABLE LoginUsers;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginRoles') DROP TABLE LoginRoles;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginSession') DROP TABLE LoginSession;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginFailed') DROP TABLE LoginFailed;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginComputer') DROP TABLE LoginComputer;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginComputerUser') DROP TABLE LoginComputerUser;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginManagerJournal') DROP TABLE LoginManagerJournal;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginManagerEvent') DROP TABLE LoginManagerEvent;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
";
object Result = null;
string Err = null;
if (db_con.ExecuteNonQuerySQL(sql_DropTables, null, ref Result, ref Err))
{
return true;
}
else
{
LogFile.Error.Show("Error:LoginControl:DropTables:Err = " + Err);
return false;
}
default:
LogFile.Error.Show("Error:LoginControl:DropTables:Login_con.DBType not of type DBConnection.eDBType.MSSQL!");
return false;
}
}
internal bool GetTableView(DBConnection sQL_Connection, ViewXml m_ViewXml, ref DataTable dt, ref string csError)
{
bool bRet = true;
String SqlCmd = m_ViewXml.SQLView;
dt.TableName = TableName;
if (sQL_Connection.ReadDataTable(ref dt, SqlCmd, ref csError))
{
bRet= true;
}
else
{
bRet = false;
}
return bRet;
}
public bool DropConstraints(DBConnection db_con)
{
switch (db_con.DBType)
{
case DBConnection.eDBType.MSSQL:
string sql_DropConstraints = @"
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginUsersAndLoginRoles')
AND (CONSTRAINT_NAME = 'fkLoginUsersAndLoginRoles_LoginUsers_id'))) ALTER TABLE LoginUsersAndLoginRoles DROP CONSTRAINT fkLoginUsersAndLoginRoles_LoginUsers_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginUsersAndLoginRoles')
AND (CONSTRAINT_NAME = 'fkLoginUsersAndLoginRoles_LoginRoles_id'))) ALTER TABLE LoginUsersAndLoginRoles DROP CONSTRAINT fkLoginUsersAndLoginRoles_LoginRoles_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginSession')
AND (CONSTRAINT_NAME = 'fkLoginSession_LoginUsers_id'))) ALTER TABLE LoginSession DROP CONSTRAINT fkLoginSession_LoginUsers_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginSession')
AND (CONSTRAINT_NAME = 'fkLoginSession_LoginComputer_id'))) ALTER TABLE LoginSession DROP CONSTRAINT fkLoginSession_LoginComputer_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginSession')
AND (CONSTRAINT_NAME = 'fkLoginSession_LoginComputerUser_id'))) ALTER TABLE LoginSession DROP CONSTRAINT fkLoginSession_LoginComputerUser_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginFailed')
AND (CONSTRAINT_NAME = 'fkLoginFailed_LoginComputer_id'))) ALTER TABLE LoginFailed DROP CONSTRAINT fkLoginFailed_LoginComputer_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginFailed')
AND (CONSTRAINT_NAME = 'fkLoginFailed_LoginComputerUser_id'))) ALTER TABLE LoginFailed DROP CONSTRAINT fkLoginFailed_LoginComputerUser_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginManagerJournal')
AND (CONSTRAINT_NAME = 'fkLoginManagerJournal_LoginUsers_id'))) ALTER TABLE LoginManagerJournal DROP CONSTRAINT fkLoginManagerJournal_LoginUsers_id;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE ((TABLE_NAME = 'LoginManagerJournal')
AND (CONSTRAINT_NAME = 'fkLoginManagerJournal_LoginManagerEvent_id'))) ALTER TABLE LoginManagerJournal DROP CONSTRAINT fkLoginManagerJournal_LoginManagerEvent_id;
";
object Result = null;
string Err = null;
if (db_con.ExecuteNonQuerySQL(sql_DropConstraints, null, ref Result, ref Err))
{
return true;
}
else
{
LogFile.Error.Show("Error:LoginControl:DropConstraints:Err = " + Err);
return false;
}
default:
LogFile.Error.Show("Error:LoginControl:DropConstraints:Login_con.DBType not of type DBConnection.eDBType.MSSQL!");
return false;
}
}
public StringBuilder SQLcmd_DropAllTables(DBConnection xcon)
{
StringBuilder strSqlAll = new StringBuilder("USE [" + xcon.DataBase + @"]
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
");
int iTable;
int iTableCount;
iTableCount = items.Count;
foreach (DataBaseView dbv in SQL_DataBase_VIEW_List)
{
StringBuilder strTable = null;
switch (xcon.DBType)
{
case DBConnection.eDBType.MSSQL:
strTable = new StringBuilder(dbv.SQLcmd_DropView());
break;
case DBConnection.eDBType.MYSQL:
strTable = new StringBuilder(dbv.MySQLcmd_DropView());
break;
case DBConnection.eDBType.SQLITE:
strTable = new StringBuilder(dbv.SQLitecmd_DropView());
break;
}
if (strTable != null)
{
strSqlAll.Append(strTable);
}
else
{
LogFile.Error.Show("ERROR:CodeTables:DBTableControl:SQLcmd_DropAllTables:Not valid xcon.DBType : " + xcon.DBType.ToString());
}
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
if (DBtypesFunc.Is_DBm_Type(items[iTable]))
{
continue;
}
else
{
StringBuilder strTable = new StringBuilder(items[iTable].SQLcmd_AlterTableDropConstraintForeign());
strSqlAll.Append(strTable);
}
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
if (DBtypesFunc.Is_DBm_Type(items[iTable]))
{
continue;
}
else
{
StringBuilder strTable = new StringBuilder(items[iTable].SQLcmd_DropTable());
strSqlAll.Append(strTable);
}
}
return strSqlAll;
}
public bool Init(Form pParentForm, DBConnection con,object DBParam,int Language_id, ref string Err )
{
LoginDB_DataSet.DynSettings.LanguageID = Language_id;
Login_con = con;
if (CheckConnection(pParentForm))
{
if (this.DataTableCreationMode == eDataTableCreationMode.NONE)
{
return GetTables(ref Err);
}
else
{
if (DropConstraints(Login_con))
{
if (DropTables(Login_con))
{
return GetTables(ref Err);
}
}
return false;
}
}
else
{
if (Login_con.MakeDataBaseConnection(pParentForm, DBParam))
{
return GetTables(ref Err);
}
else
{
return false;
}
}
}
public StringBuilder SQLitecmd_DropAllTables(DBConnection xcon)
{
StringBuilder strSqlAll = new StringBuilder("");
int iTable;
int iTableCount;
iTableCount = items.Count;
foreach (DataBaseView dbv in SQL_DataBase_VIEW_List)
{
StringBuilder strTable = new StringBuilder(dbv.SQLitecmd_DropView());
strSqlAll.Append(strTable);
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
StringBuilder strTable = new StringBuilder(items[iTable].SQLitecmd_AlterTableDropConstraintForeign());
strSqlAll.Append(strTable);
}
for (iTable = iTableCount - 1; iTable >= 0; iTable--)
{
StringBuilder strTable = new StringBuilder(items[iTable].SQLitecmd_DropTable());
strSqlAll.Append(strTable);
}
return strSqlAll;
}
public void Init(DBConnection.eDBType eDBType)
{
this.m_DBTables.Init(eDBType);
}
public void Init(DBConnection.eDBType eDBType)
{
m_con.DBType = eDBType;
m_strSQLUseDatabase = new StringBuilder("\nUSE " + this.m_con.DataBase + "\n SET DATEFORMAT dmy\n\n");
StringBuilder sbAll = SQLcmd_CreateAllTables(this.m_con); // this fuction creates all fkey links !
string sAll = sbAll.ToString();
SQLcmd_DropAllTables(this.m_con);
}
public bool GetTableView(DBConnection m_SQL_connection, ref DataTable dt, ref String csError, int iTopAmount)
{
String SqlCmd;
if (m_SQL_connection.DBType == DBConnection.eDBType.SQLITE)
{
if (iTopAmount > 0)
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = @" SELECT *
FROM
" + ViewName + " LIMIT " + iTopAmount.ToString() + ";"; // +" order by ID desc;"; top " + iTopAmount.ToString() + @"
}
else
{
SqlCmd = @"
SELECT *
FROM
" + TableName + " order by " + TableName + ".ID asc LIMIT " + iTopAmount.ToString() + ";";
}
}
else
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = " SELECT * FROM " + ViewName;// +" order by ID desc;";
}
else
{
SqlCmd = " SELECT * FROM " + TableName + " order by " + TableName + ".ID asc;";
}
}
}
else
{
if (iTopAmount > 0)
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT top " + iTopAmount.ToString() + @" *
FROM
" + ViewName;// +" order by ID desc;";
}
else
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT top " + iTopAmount.ToString() + @" *
FROM
" + TableName + " order by " + TableName + ".ID desc;";
}
}
else
{
if (dt == null)
{
LogFile.Error.Show("Error in GetTableView(..) ref DataTable dt may not be null!");
}
if (this.m_Fkey.Count > 0)
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT *
FROM
" + ViewName;// +" order by ID desc;";
}
else
{
SqlCmd = "USE " + m_SQL_connection.DataBase + @"
SELECT *
FROM
" + TableName + " order by " + TableName + ".ID desc;";
}
}
}
dt.TableName = TableName;
if (m_SQL_connection.ReadDataTable(ref dt, SqlCmd, ref csError))
{
return true;
}
else
{
return false;
}
}
//public SQLTable Add(Object oTable, Column.Flags xFlags, ltext lngtblname)
//{
// SQLTable myTable = new SQLTable(oTable, xFlags, lngtblname);
// items.Add(myTable);
// return myTable;
//}
public StringBuilder SQLcmd_CreateAllTables(DBConnection xcon)
{
StringBuilder strSqlAll = new StringBuilder("");
StringBuilder strSqlTables = new StringBuilder("");
//strSql.Append(Create_Database_Table(SQL_Table tbl_eva_User, m_eva_UserSQL_Table(());) new eva_User ());
//strSql.Append(Create_Database_Table(col_evl_CmdTYPE, m_evl_CmdTYPE)),
//strSql.Append(Create_Database_Table(col_evl_History, m_evl_History));
int iTable;
int iTableCount;
iTableCount = items.Count;
List<string> UniqueConstraintNameList = new List<string>();
string sql_DBm = "";
for (iTable = 0; iTable < iTableCount; iTable++)
{
SQLTable tbl = items[iTable];
if (DBtypesFunc.Is_DBm_Type(tbl.objTable))
{
continue; // ignore DBm_* Tables
}
else
{
tbl.sql_CreateTable = tbl.SQLcmd_CreateTable(this, UniqueConstraintNameList, ref sql_DBm, null);
strSqlTables.Append(tbl.sql_CreateTable);
}
}
strSqlAll.Append(sql_DBm);
strSqlAll.Append(strSqlTables);
StringBuilder strTableAlterTable = new StringBuilder("");
//iTableCount = items.Count;
//iTable = 0;
//while (iTable <iTableCount)
//{
// SQLTable tbl = items[iTable];
// if (DBtypesFunc.Is_DBm_Type(tbl.objTable))
// {
// items.RemoveAt(iTable);
// }
// else
// {
// iTable++;
// }
// iTableCount = items.Count;
//}
for (iTable = 0; iTable < iTableCount; iTable++)
{
switch (m_con.DBType)
{
case DBConnection.eDBType.MYSQL:
strTableAlterTable.Append(items[iTable].SQLcmdMySQL_AlterTableAddConstraintForeign()).ToString();
break;
case DBConnection.eDBType.MSSQL:
strTableAlterTable.Append(items[iTable].SQLcmdMSSQL_AlterTableAddConstraintForeign());
break;
case DBConnection.eDBType.SQLITE:
//strTable = new StringBuilder(items[iTable].SQLcmd_AlterTableAddConstraintForeign());
break;
default:
break;
}
}
strSqlAll.Append(strTableAlterTable);
// Also create views! in one step
SQL_DataBase_VIEW_List.Clear();
string ErrMSSQLNameToLong = null;
for (iTable = 0; iTable < iTableCount; iTable++)
{
string table_view = null;
StringBuilder SQLCreateView_InDataBase = items[iTable].SQLCreateView_InDataBase(items);
if (SQLCreateView_InDataBase.Length > 0)
{
switch (m_con.DBType)
{
case DBConnection.eDBType.MYSQL:
break;
case DBConnection.eDBType.MSSQL:
foreach (SQLTable.Table_View.ColumnNames cnames in items[iTable].m_Table_View.View_ColumnNames_List)
{
if (cnames.Name.Length>=128)
{
if (ErrMSSQLNameToLong == null)
{
ErrMSSQLNameToLong = "ERROR:SQLTableControl.cs:SQLcmd_CreateAllTables:View column name to long (>128) fo MSSQL database:\r\n " + table_view;
}
else
{
if (table_view == null)
{
table_view = items[iTable].ViewName;
ErrMSSQLNameToLong += "\r\n " + table_view;
}
}
ErrMSSQLNameToLong += "\r\n " + cnames.Name;
}
}
if (ErrMSSQLNameToLong!= null)
{
LogFile.Error.Show(ErrMSSQLNameToLong);
}
break;
case DBConnection.eDBType.SQLITE:
break;
default:
break;
}
items[iTable].sql_CreateView = SQLCreateView_InDataBase.ToString();
DataBaseView xDataBaseView = new DataBaseView(items[iTable].ViewName, SQLCreateView_InDataBase.ToString());
SQL_DataBase_VIEW_List.Add(xDataBaseView);
}
}
return strSqlAll;
}
public bool Fill_SQLTable(DBConnection x_SQL_connection, Int64 ID, ref string csError)
{
// 1 Read row data
String sSQL_ReadRow = "";
switch (x_SQL_connection.DBType)
{
case DBConnection.eDBType.MYSQL:
sSQL_ReadRow = "USE " + x_SQL_connection.DataBase + ";\n"
+ "SELECT ";
break;
case DBConnection.eDBType.MSSQL:
sSQL_ReadRow = "USE " + x_SQL_connection.DataBase + "\n"
+ "SELECT ";
break;
case DBConnection.eDBType.SQLITE:
sSQL_ReadRow = "SELECT ";
break;
}
int i = 0;
foreach (Column col in Column)
{
if (!col.IsIdentity)
{
if (DBtypesFunc.IsValueDefined(col.obj))
{
if (i == 0)
{
sSQL_ReadRow += col.Name;
}
else
{
sSQL_ReadRow += "," + col.Name;
}
i++;
}
else if (col.fKey != null)
{
if (i == 0)
{
sSQL_ReadRow += col.Name;
}
else
{
sSQL_ReadRow += "," + col.Name;
}
i++;
}
}
else
{
sSQL_ReadRow += col.Name;
i++;
}
}
sSQL_ReadRow += " FROM " + TableName + " WHERE ID = " + ID.ToString();
DataTable dt = new DataTable(this.TableName);
dt.TableName = this.TableName;
if (x_SQL_connection.ReadDataTable(ref dt, sSQL_ReadRow, ref csError))
{
// Now we have data in dt
foreach (Column col in this.Column)
{
if (!col.IsIdentity)
{
if (col.fKey != null)
{
if (dt.Rows.Count > 0)
{
if ((dt.Rows[0][col.Name].GetType() == typeof(int)) || (dt.Rows[0][col.Name].GetType() == typeof(Int64)))
{
Int64 Identity = Convert.ToInt64(dt.Rows[0][col.Name]);
if (col.fKey.fTable.Fill_SQLTable(x_SQL_connection, Identity, ref csError))
{
}
else
{
return false;
}
}
}
}
else
{
if (DBtypesFunc.IsValueDefined(col.obj))
{
DBTypes.DBtypesFunc.SetValue(ref col.obj, dt.Rows[0][col.Name], ref csError);
}
}
}
else
{
DBTypes.DBtypesFunc.SetValue(ref col.obj, dt.Rows[0][col.Name], ref csError);
}
}
return true;
}
else
{
return false;
}
}
