/// <summary>
        /// load from outside
        /// </summary>
        /// <param name="sql"></param>
        public void LoadData(SQLNoneQuery sql)
        {
            if (editState != EnumEditState.NotLoaded)
            {
                throw new ExceptionLimnorDatabase("Calling LoadData at the state of {0}", editState);
            }

            dbConn = sql.Connection;
            //load table names into lstTable
            QueryParser._UseLowerCase = sql.Connection.ConnectionObject.LowerCaseSqlKeywords;
            System.Data.DataTable tbl = sql.Connection.ConnectionObject.GetTables();
            if (tbl != null)
            {
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    if (tbl.Rows[i]["Table_Name"] != null)
                    {
                        string s = tbl.Rows[i]["Table_Name"].ToString();
                        int    n = lstTable.Items.Add(s);
                        if (string.Compare(s, sql.TableName, StringComparison.OrdinalIgnoreCase) == 0)
                        {
                            lstTable.SelectedIndex = n;
                        }
                    }
                }
            }
            objRet = ((SQLNoneQuery)sql.Clone());

            int k = (int)(objRet.CommandType);

            if (k >= 0 && k < cbxType.Items.Count)
            {
                cbxType.SelectedIndex = k;
            }

            txtWhere.Text          = objRet.Filter;
            txtSQL.Text            = objRet.SQL;
            btQueryBuilder.Enabled = (objRet.CommandType != enmNonQueryType.Insert);

            if (!string.IsNullOrEmpty(objRet.TableName))
            {
                for (int i = 0; i < lstTable.Items.Count; i++)
                {
                    if (string.Compare(objRet.TableName, lstTable.Items[i].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
                    {
                        lstTable.SelectedIndex = i;
                        break;
                    }
                }
            }
            else
            {
                if (lstTable.Items.Count > 0)
                {
                    lstTable.SelectedIndex = 0;
                }
            }
            editState = EnumEditState.Ready;
            lstTable_SelectedIndexChanged(this, null);
        }
Esempio n. 2
0
 public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
 {
     if (context != null && context.Instance != null && provider != null)
     {
         IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
         if (edSvc != null)
         {
             EasyUpdator eu = context.Instance as EasyUpdator;
             if (eu == null)
             {
                 object   pointer;
                 IClassId ic = context.Instance as IClassId;
                 if (ic != null)
                 {
                     pointer = ic.ObjectInstance;
                 }
                 else
                 {
                     pointer = context.Instance;
                 }
                 if (pointer != null)
                 {
                     eu = VPLUtil.GetObject(pointer) as EasyUpdator;
                 }
             }
             if (eu != null)
             {
                 SQLNoneQuery sq = value as SQLNoneQuery;
                 if (sq == null)
                 {
                     sq = new SQLNoneQuery();
                 }
                 sq.SetConnection(eu.DatabaseConnection);
                 bool bOK = eu.DatabaseConnection.ConnectionObject.IsConnectionReady;
                 if (!bOK)
                 {
                     DlgConnectionManager dlgC = new DlgConnectionManager();
                     dlgC.UseProjectScope = true;
                     if (edSvc.ShowDialog(dlgC) == System.Windows.Forms.DialogResult.OK)
                     {
                         eu.DatabaseConnection = dlgC.SelectedConnection;
                         sq.SetConnection(dlgC.SelectedConnection);
                         bOK = true;
                     }
                 }
                 if (bOK)
                 {
                     dlgPropSQLNonQuery dlg = new dlgPropSQLNonQuery();
                     dlg.LoadData(sq);
                     if (edSvc.ShowDialog(dlg) == System.Windows.Forms.DialogResult.OK)
                     {
                         value = dlg.objRet;
                     }
                 }
             }
         }
     }
     return(value);
 }
        bool parseSQLNoneQuery(string sSQL, SQLNoneQuery sql)
        {
            string sMsg;

            if (string.IsNullOrEmpty(sql.TableName) && objRet != null)
            {
                sql.TableName = objRet.TableName;
            }
            sql.SetConnection(dbConn);
            bool b = sql.parseSQLNoneQuery(this, sSQL, out sMsg);

            txtInfo.Text = sMsg;
            return(b);
        }
        /// <summary>
        /// load SQLNoneQuery from cache
        /// </summary>
        /// <returns>true:loaded objRet from cache; false current objRet is already good</returns>
        private bool loadFromCache()
        {
            enmNonQueryType qt = enmNonQueryType.Insert;

            if (cbxType.SelectedIndex >= 0)
            {
                qt = (enmNonQueryType)(cbxType.SelectedIndex);
            }
            if (dt != null)
            {
                if (objRet != null)
                {
                    if (objRet.CommandType == qt && string.Compare(objRet.TableName, dt.TableName, StringComparison.OrdinalIgnoreCase) == 0)
                    {
                        saveTocCache();
                        return(false);
                    }
                }
                SQLNoneQuery nq = null;

                if (_commandCache != null)
                {
                    Dictionary <string, SQLNoneQuery> c;
                    if (_commandCache.TryGetValue(qt, out c))
                    {
                        c.TryGetValue(dt.TableName, out nq);
                    }
                }

                if (nq == null)
                {
                    nq             = new SQLNoneQuery();
                    nq.TableName   = dt.TableName;
                    nq.CommandType = qt;
                    objRet         = nq;
                    saveTocCache();
                }
                else
                {
                    objRet = nq;
                }
            }
            return(true);
        }
        private bool checkSyntax()
        {
            if (bSQLChanged)
            {
                QueryParser._UseLowerCase = dbConn.LowerCaseSqlKeywords;
                SQLNoneQuery sql = new SQLNoneQuery();
                sql.SetConnection(dbConn);
                ParameterList ps = objRet.ParseParams();
                if (ps != null)
                {
                    sql.SetParameters(ps);
                }
                if (parseSQLNoneQuery(txtSQL.Text, sql))
                {
                    string tblName = objRet.TableName;
                    objRet = sql;
                    if (string.IsNullOrEmpty(objRet.TableName))
                    {
                        objRet.TableName = tblName;
                    }

                    enmNonQueryType qt = enmNonQueryType.Insert;
                    if (cbxType.SelectedIndex >= 0)
                    {
                        qt = (enmNonQueryType)(cbxType.SelectedIndex);
                    }
                    if (qt != objRet.CommandType)
                    {
                        cbxType.SelectedIndex = (int)qt;
                    }
                    for (int i = 0; i < lstTable.Items.Count; i++)
                    {
                        if (string.Compare(objRet.TableName, lstTable.Items[i].ToString(), StringComparison.OrdinalIgnoreCase) == 0)
                        {
                            if (i != lstTable.SelectedIndex)
                            {
                                lstTable.SelectedIndex = i;
                                EnumEditState st = editState;
                                editState = EnumEditState.Ready;
                                lstTable_SelectedIndexChanged(this, EventArgs.Empty);
                                editState = st;
                            }
                            break;
                        }
                    }
                    setFieldTypes();
                    saveTocCache();
                    if (ds != null)
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            ds.Tables[0].Rows[j][1] = false;
                            ds.Tables[0].Rows[j][2] = "";
                        }
                        for (int i = 0; i < objRet.FieldCount; i++)
                        {
                            EPField f = objRet.GetField(i);
                            if (f != null)
                            {
                                for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                                {
                                    string fn = ds.Tables[0].Rows[j][0].ToString();
                                    if (string.Compare(f.Name, fn, StringComparison.OrdinalIgnoreCase) == 0)
                                    {
                                        ds.Tables[0].Rows[j][1] = true;
                                        ds.Tables[0].Rows[j][2] = f.FieldText;
                                        break;
                                    }
                                }
                            }
                        }
                    }
                    return(true);
                }
                else
                {
                    txtSQL.ForeColor = System.Drawing.Color.Red;
                    return(false);
                }
            }
            return(true);
        }
 public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
 {
     if (context != null && context.Instance != null && provider != null)
     {
         IWindowsFormsEditorService edSvc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
         if (edSvc != null)
         {
             DatabaseExecuter der = context.Instance as DatabaseExecuter;
             if (der != null)
             {
                 string          sText = der.ExecutionCommand.SQL;
                 ExecParameter[] ps    = new ExecParameter[der.ExecutionCommand.ParamCount];
                 for (int i = 0; i < der.ExecutionCommand.ParamCount; i++)
                 {
                     ExecParameter p = new ExecParameter();
                     p.Name      = der.ExecutionCommand.Param_Name[i];
                     p.Type      = der.ExecutionCommand.Param_OleDbType[i];
                     p.DataSize  = der.Param_DataSize[i];
                     p.Direction = der.Param_Directions[i];
                     ps[i]       = p;
                 }
                 DialogDbCommand dlg = new DialogDbCommand();
                 dlg.LoadData(sText, der.IsStoredProc, ps);
                 if (edSvc.ShowDialog(dlg) == System.Windows.Forms.DialogResult.OK)
                 {
                     SQLNoneQuery sq = new SQLNoneQuery();
                     sq.CommandType = enmNonQueryType.StoredProcedure;
                     sq.SQL         = dlg.RetSQL;
                     ps             = dlg.RetParameters;
                     string[]             names = new string[dlg.RetParameters.Length];
                     OleDbType[]          types = new OleDbType[dlg.RetParameters.Length];
                     int[]                sizes = new int[dlg.RetParameters.Length];
                     ParameterDirection[] pds   = new ParameterDirection[dlg.RetParameters.Length];
                     //
                     for (int i = 0; i < dlg.RetParameters.Length; i++)
                     {
                         names[i] = dlg.RetParameters[i].Name;
                         types[i] = dlg.RetParameters[i].Type;
                         sizes[i] = dlg.RetParameters[i].DataSize;
                         pds[i]   = dlg.RetParameters[i].Direction;
                     }
                     //
                     sq.Param_Name        = names;
                     sq.Param_OleDbType   = types;
                     der.ExecutionCommand = sq;
                     DbParameterList pl = new DbParameterList(sq.Parameters);
                     der.SetParameterList(pl);
                     der.Param_DataSize   = sizes;
                     der.Param_Directions = pds;
                     if (dlg.IsStoredProc)
                     {
                         sq.CommandType = enmNonQueryType.StoredProcedure;
                     }
                     else
                     {
                         sq.CommandType = enmNonQueryType.Script;
                     }
                     value = sq;
                 }
             }
         }
     }
     return(value);
 }
        public override string Execute()
        {
            string sMsg = string.Empty;

            SetError(sMsg);
            ResetAffectedRows();
            SQLNoneQuery   sql         = base.ExecutionCommand;
            ConnectionItem connect     = Connection;
            DbTransaction  transaction = Transaction;

            if (sql != null && connect != null)
            {
                DbCommand cmd = connect.ConnectionObject.CreateCommand();
                if (transaction != null)
                {
                    cmd.Transaction = transaction;
                }
                bool bClosed = !connect.ConnectionObject.Opened;
                if (bClosed)
                {
                    connect.ConnectionObject.Open();
                }
                if (connect.ConnectionObject.Opened)
                {
                    try
                    {
                        int i;
                        EnumParameterStyle pstyle = connect.ParameterStyle;
                        cmd.CommandText = sql.SQL;
                        if (sql.CommandType == enmNonQueryType.StoredProcedure)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            cmd.CommandType = CommandType.Text;
                        }
                        int nCount = ParameterCount;
                        for (i = 0; i < nCount; i++)
                        {
                            DbParameter pam = cmd.CreateParameter();
                            if (pstyle == EnumParameterStyle.LeadingQuestionMark)
                            {
                                if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase))
                                {
                                    pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i].Substring(1));
                                }
                                else if (sql.Param_Name[i].StartsWith("?", StringComparison.OrdinalIgnoreCase))
                                {
                                    pam.ParameterName = sql.Param_Name[i];
                                }
                                else
                                {
                                    pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i]);
                                }
                            }
                            else
                            {
                                if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase))
                                {
                                    pam.ParameterName = sql.Param_Name[i];
                                }
                                else
                                {
                                    pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "@{0}", sql.Param_Name[i]);
                                }
                            }
                            pam.DbType    = ValueConvertor.OleDbTypeToDbType(sql.Param_OleDbType[i]);
                            pam.Direction = this.Param_Directions[i];
                            pam.Size      = EPField.FieldDataSize(sql.Param_OleDbType[i], this.Param_DataSize[i]);
                            pam.Value     = ValueConvertor.ConvertByOleDbType(sql.Parameters[i].Value, sql.Param_OleDbType[i]);
                            cmd.Parameters.Add(pam);
                        }
                        cmd.Prepare();
                        DbDataReader dr = cmd.ExecuteReader();

                        _dataset = new DataSet("Results");
                        int n = 1;
                        while (true)
                        {
                            DataTable tbl = new DataTable(string.Format("Table{0}", n));
                            for (i = 0; i < dr.FieldCount; i++)
                            {
                                DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i));
                                tbl.Columns.Add(dc);
                            }
                            _dataset.Tables.Add(tbl);
                            while (dr.Read())
                            {
                                object[] vs = new object[dr.FieldCount];
                                for (int k = 0; k < dr.FieldCount; k++)
                                {
                                    vs[k] = dr.GetValue(k);
                                }
                                tbl.Rows.Add(vs);
                            }
                            n++;
                            if (!dr.NextResult())
                            {
                                break;
                            }
                        }
                        dr.Close();
                        if (bClosed)
                        {
                            closeConnections();
                        }
                        for (i = 0; i < nCount; i++)
                        {
                            ParameterDirection pt = cmd.Parameters[i].Direction;
                            if (pt != ParameterDirection.Input)
                            {
                                sql.Parameters[i].Value = cmd.Parameters[i].Value;
                            }
                        }
                        FireExecuteFinish();
                    }
                    catch (Exception er)
                    {
                        if (transaction != null)
                        {
                            transaction.Rollback();
                            transaction.Dispose();
                            ResetTransaction();
                            throw;
                        }
                        else
                        {
                            sMsg = ExceptionLimnorDatabase.FormExceptionText(er);
                        }
                    }
                    finally
                    {
                        if (bClosed)
                        {
                            if (connect.ConnectionObject.State != ConnectionState.Closed)
                            {
                                connect.ConnectionObject.Close();
                            }
                        }
                    }
                }
                else
                {
                    sMsg = "Database connection not set";
                }
            }
            else
            {
                sMsg = "SQL statement not set";
            }
            if (!string.IsNullOrEmpty(sMsg))
            {
                SetError(sMsg);
            }
            return(sMsg);
        }