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