示例#1
0
        public DataView FindWhere(List <String> tables, List <DbJoin> joins = null)
        {
            string           sqlString   = "";
            DataView         dv          = new DataView();
            MySqlConnection  conn        = DatabaseManager.GetConnection();
            MySqlDataAdapter listAdapter = new MySqlDataAdapter();

            conn.Open();

            MySqlCommand cmd = new MySqlCommand();

            cmd.Connection = conn;

            string tableList = "";

            foreach (string table in tables)
            {
                if (!String.IsNullOrEmpty(tableList))
                {
                    tableList += ", ";
                }

                tableList += table;
            }

            sqlString = "SELECT " + BuildFieldValues() + " FROM " + tableList;

            if (joins != null)
            {
                foreach (DbJoin join in joins)
                {
                    switch (join.JoinType)
                    {
                    case JoinType.LeftJoin:
                        sqlString += " left join ";
                        break;

                    case JoinType.RightJoin:
                        sqlString += " right join ";
                        break;

                    case JoinType.FullJoin:
                        sqlString += " full join ";
                        break;
                    }

                    sqlString += join.Table + " on " + join.Criteria;
                }
            }

            if (!String.IsNullOrEmpty(_whereClause))
            {
                sqlString += " WHERE " + _whereClause;
            }

            if (_parameters != null && _parameters.Count > 0)
            {
                BindParameterList(cmd);
            }

            if (_orderByFields != null && _orderByFields.Count > 0)
            {
                sqlString += " ORDER BY " + BuildOrderBy();
            }


            cmd.CommandText = sqlString;
            cmd.CommandType = CommandType.Text;

            try
            {
                listAdapter.SelectCommand = cmd;

                DataTable dataTable = new DataTable();
                listAdapter.Fill(dataTable);
                dv = new DataView(dataTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return(dv);
        }
示例#2
0
        public Dictionary <string, object> FindById(string table, string keyField, int keyValue)
        {
            MySqlConnection conn = DatabaseManager.GetConnection();
            MySqlDataReader dr   = null;

            conn.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = conn;
                cmd.CommandText = "SELECT " + BuildFieldValues() + " FROM " + table + " WHERE " + keyField + " = :" + keyField;
                cmd.CommandType = CommandType.Text;

                //cmd.Parameters.Add(":" + keyField, keyValue);

                MySqlParameter parameter = new MySqlParameter();
                parameter.DbType = DbType.Int32;
                //sqlLiteParameter.DbType = (System.Data.DbType)parameter.Type;
                parameter.ParameterName = "@" + keyField;
                parameter.Value         = keyValue;
                cmd.Parameters.Add(parameter);


                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    Dictionary <String, object> vals = new Dictionary <string, object>();
                    int i = 0;
                    foreach (string s in _fields.Keys)
                    {
                        DbField f = (DbField)_fields[s];

                        switch (f.Type)
                        {
                        case DbType.Int32:
                            try { vals.Add(s, dr.GetDecimal(i)); }
                            catch { vals.Add(s, 0); }
                            break;

                        case DbType.String:
                            try { vals.Add(s, dr.GetString(i)); }
                            catch { vals.Add(s, ""); }
                            break;

                        //case DbTypes.Varchar:
                        //    try { vals.Add(s, dr.GetString(i)); }
                        //    catch { vals.Add(s, ""); }
                        //    break;
                        case DbType.DateTime:
                            try { vals.Add(s, dr.GetDateTime(i)); }
                            catch { vals.Add(s, null); }
                            break;

                        case DbType.Binary:
                            try { vals.Add(s, dr.GetValue(i)); }
                            catch { vals.Add(s, null); }
                            break;

                        case DbType.Boolean:
                            try { vals.Add(s, dr.GetValue(i)); }
                            catch { vals.Add(s, ""); }
                            break;

                        case DbType.Double:
                            try { vals.Add(s, dr.GetValue(i)); }
                            catch { vals.Add(s, 0); }
                            break;
                        }
                        i++;
                    }
                    return(vals);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Dispose();
                }

                conn.Close();
            }
            return(null);
        }