Inheritance: DbParameter, IDataParameter, IDbDataParameter, ICloneable
Exemplo n.º 1
0
        private HassiumObject query(HassiumObject[] args)
        {
            MySqlCommand command = new MySqlCommand(null, Value);
            command.CommandText = "INSERT INTO " + args[0] + "(";
            HassiumObject[] vals = ((HassiumArray)args[1]).Value;
            command.CommandText += vals[0].ToString();
            for (int x = 1; x < vals.Length; x++)
                command.CommandText += ", " + vals[x].ToString();
            command.CommandText += ") VALUES (";
            command.CommandText += "@" + vals[0].ToString();
            for (int x = 1; x < vals.Length; x++)
            command.CommandText += ", @" + vals[x].ToString();
            command.CommandText += ")";

            HassiumObject[] lits = ((HassiumArray)args[2]).Value;
            for (int x = 0; x < lits.Length; x++) {
            MySqlParameter param = new MySqlParameter("@" + vals[x].ToString(), lits[x].ToString());
            param.Value = lits[x].ToString();
            command.Parameters.Add(param);
            }
            // Console.WriteLine(command.CommandText);

            command.Prepare();
            command.ExecuteNonQuery();

            return null;
        }
Exemplo n.º 2
0
        public Manager Get(int id)
        {
            //创建一个MySqlCommand对象
            MySqlCommand cmd = new MySqlCommand();

            try
            {
                string sql = "SELECT ManagerId,Account,PassWord,Name FROM Manager WHERE  ManagerId=?ManagerId";

                MySqlParameter[] parameter = new MySqlParameter[] { new MySqlParameter("?ManagerId", id) };
                //MySqlParameter[] parameter = new MySqlParameter[] { new MySqlParameter("Account", account), new MySqlParameter("PassWord", passWord) };

                //调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
                MySqlHelper.PrepareCommand(cmd, null, CommandType.Text, sql, parameter);
                //调用 MySqlCommand  的 ExecuteReader 方法
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();

                adapter.Fill(ds);

                return TNS.Db.Util.ConvertHelper.DataSetToEntity<Manager>(ds, 0);
            }
            catch (Exception e)
            {
                MySqlHelper.CloseDBConnection();
                base.ErrorMessage = "";
                throw e;
            }
            finally
            {
                //清除参数
                cmd.Parameters.Clear();
            }
        }
Exemplo n.º 3
0
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSqlInsertImg(string strSQL, List <Tuple <string, byte[]> > imgList)
        {
            int result = 0;

            try
            {
                using (MySqlCommand cmd = new MySqlCommand(strSQL, DbConn))
                {
                    foreach (var t in imgList)
                    {
                        string imgTag  = t.Item1;
                        byte[] imgData = t.Item2;
                        MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter(imgTag, imgData);
                        //myParameter.Value = content;
                        cmd.Parameters.Add(myParameter);
                    }

                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (System.Exception ex)
            {
                LogManager.WriteExceptionUseCache(strSQL + "\r\n" + ex.ToString());
            }

            return(result);
        }
Exemplo n.º 4
0
        public object ExecuteSqlGet(string sql, string content)
        {
            try
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, DbConn))
                {
                    MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", content);
                    //myParameter.Value = content;
                    cmd.Parameters.Add(myParameter);

                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return(null);
                    }
                    else
                    {
                        return(obj);
                    }
                }
            }
            catch (System.Exception ex)
            {
                LogManager.WriteExceptionUseCache(sql + "\r\n" + ex.ToString());
            }

            return(null);
        }
Exemplo n.º 5
0
        public void InsertCar(Car o)
        {
            StringBuilder sb = new StringBuilder("insert into Car (Make, Model, Year, Doors, Colour, Price) values ")
            .Append("(@make, @model, @year, @doors, @colour, @price)");
            string q = sb.ToString();

            using (MySqlCommand cm = new MySqlCommand(q, Connection))
            {
                MySqlParameter p = new MySqlParameter("@make", MySqlDbType.String);
                p.Value = o.Make;
                cm.Parameters.Add(p);

                p = new MySqlParameter("@model", MySqlDbType.String);
                p.Value = o.Model;
                cm.Parameters.Add(p);

                p = new MySqlParameter("@year", MySqlDbType.Int32);
                p.Value = o.Year;
                cm.Parameters.Add(p);

                p = new MySqlParameter("@doors", MySqlDbType.Int32);
                p.Value = o.Doors;
                cm.Parameters.Add(p);

                p = new MySqlParameter("@colour", MySqlDbType.String);
                p.Value = o.Colour;
                cm.Parameters.Add(p);

                p = new MySqlParameter("@price", MySqlDbType.Double);
                p.Value = o.Price;
                cm.Parameters.Add(p);

                cm.ExecuteNonQuery();
            }
        }
        public void DeleteInschrijving(int studentID, int evenementID)
        {
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                string insertString = @"delete from inschrijving where student_id=@studentid and evenement_id=@evenementid";

                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter studentidParam = new MySqlParameter("@studentid", MySqlDbType.Int32);
                MySqlParameter evenementidParam = new MySqlParameter("@evenementid", MySqlDbType.Int32);

                studentidParam.Value = studentID;
                evenementidParam.Value = evenementID;

                cmd.Parameters.Add(studentidParam);
                cmd.Parameters.Add(evenementidParam);
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                trans.Commit();

            }
            catch (Exception e)
            {
                trans.Rollback();
                Console.Write("Inschrijving niet verwijderd: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
Exemplo n.º 7
0
        public void efetuarLogin()
        {
            MySqlConnection con = new MySqlConnection(Form1.conexao);
            con.Open();
            MySqlCommand comando = new MySqlCommand("Select id,fcNomeAcesso,fcSenha,fcLoja from ubfuncionario where fcNomeAcesso =?Nome and fcSenha =?Senha and fcLoja=?Loja");
            comando.Connection = con;

            MySqlParameter Parametros = new MySqlParameter();

            comando.Parameters.AddWithValue("?Senha", txtSenha.Text);
            comando.Parameters.AddWithValue("?Loja", cbLojas.SelectedValue);
            comando.Parameters.AddWithValue("?Nome", txtUsuario.Text);

            MySqlDataAdapter daLogin = new MySqlDataAdapter(comando);
            DataTable dtLogin = new DataTable();
            daLogin.Fill(dtLogin);
            try
            {
                idLoja = Int32.Parse(dtLogin.Rows[0][3].ToString());
                idUsuario = Int32.Parse(dtLogin.Rows[0][0].ToString());
                lblStatus.Text = "LOGIN EFETUADO!";
                lblStatus.ForeColor = System.Drawing.Color.Green;

                for (int i = 231; i <= 483; i += 50)
                {
                    this.Width = i;
                }
            }
            catch
            {

                lblStatus.Text ="USUÁRIO INEXISTENTE!";
            }
        }
Exemplo n.º 8
0
 public List<LevelReplies> queryLevelReplies(long postID)
 {
     sqlconn.Open();
       List<LevelReplies> levelRepliesList = new List<LevelReplies>();
       string sql = "select * from levelReplies where postID=?postID";
       MySqlCommand cmd = new MySqlCommand(sql, sqlconn);
       MySqlParameter[] parameters = new MySqlParameter[1];
       parameters[0] = new MySqlParameter("?postID", MySqlDbType.Int64);
       parameters[0].Value = postID;
       cmd.Parameters.AddRange(parameters);
       MySqlDataReader reader = cmd.ExecuteReader();
       while (reader.Read())
       {
           LevelReplies levelReplies = new LevelReplies();
           levelReplies.LevelRepliesID = Convert.ToInt64(reader[0]);
           levelReplies.PostID = Convert.ToInt64(reader[1]);
           levelReplies.FirstRepliesID = Convert.ToInt64(reader[2]);
           levelReplies.UserName = reader[3].ToString();
           levelReplies.ToUserName = reader[4].ToString();
           levelReplies.Content = reader[5].ToString();
           levelReplies.Time = reader[6].ToString();
           levelRepliesList.Add(levelReplies);
       }
       reader.Close();
       sqlconn.Close();
       return levelRepliesList;
 }
Exemplo n.º 9
0
        public static bool DeleteByModule(int moduleId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_MediaFile ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("FileID  ");
            sqlCommand.Append("IN (");
            sqlCommand.Append("SELECT FileID FROM mp_MediaFile WHERE TrackID IN (");
            sqlCommand.Append("SELECT TrackID FROM mp_MediaTrack WHERE PlayerID IN (");
            sqlCommand.Append("SELECT PlayerID FROM mp_MediaPlayer WHERE ModuleID = ?ModuleID");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[1];

            arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = moduleId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return (rowsAffected > 0);
        }
Exemplo n.º 10
0
        public void DeleteGenre(int genreId)
        {
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                string insertString = @"delete from genre where genre_id=@id";

                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter idParam = new MySqlParameter("@id", MySqlDbType.Int32);
                idParam.Value = genreId;

                cmd.Parameters.Add(idParam);
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                trans.Commit();

            }
            catch (Exception e)
            {
                trans.Rollback();
                Console.Write("Genre niet verwijderd: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
Exemplo n.º 11
0
        public DbDataReader GetUserCountByYearMonth(int siteId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT ");
            sqlCommand.Append("YEAR(DateCreated) As Y,  ");
            sqlCommand.Append("MONTH(DateCreated) As M, ");
            sqlCommand.Append("CONCAT(YEAR(DateCreated), '-', MONTH(DateCreated)) As Label, ");
            sqlCommand.Append("COUNT(*) As Users ");

            sqlCommand.Append("FROM ");
            sqlCommand.Append("mp_Users ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("SiteID = ?SiteID ");
            sqlCommand.Append("GROUP BY YEAR(DateCreated), MONTH(DateCreated) ");
            sqlCommand.Append("ORDER BY YEAR(DateCreated), MONTH(DateCreated) ");
            sqlCommand.Append("; ");


            MySqlParameter[] arParams = new MySqlParameter[1];

            arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
            arParams[0].Value = siteId;

            return AdoHelper.ExecuteReader(
                readConnectionString,
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

        }
Exemplo n.º 12
0
        public DBCommandResult LoadOrbitalBody(StarOrbitalBody sob)
        {
            DBCommandResult res = new DBCommandResult ();

            MySqlCommand com = new MySqlCommand ("ADM_GetOrbitalBodyById", _dg.Connection);
            com.CommandType = CommandType.StoredProcedure;
            MySqlParameter spSOBID = new MySqlParameter("pSOBId", sob.OBID);
            com.Parameters.Add(spSOBID);
            try
            {
                DataSet ds = _dg.GetDataSet(com);
                DataRow dr = ds.Tables[0].Rows[0];
                sob.OBID = Convert.ToInt32(dr["OBID"]);
                sob.OrbitNo = Convert.ToInt32(dr["OrbitNo"]);

                sob.Attributes = new List<OAttribute>();
                //sob.Attributes.ParentObject = sob;
                //sob.Attributes.Load(LoadAttributes(ds.Tables[1]));

                res.Tag = sob;
                res.ResultCode = 0;
                res.ResultMsg = "Ok";
            }
            catch (Exception ex) {
                res.ResultCode = -2;
                res.ResultMsg = String.Format ("{0} ----> {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : "");
            }
            return res;
        }
Exemplo n.º 13
0
        public DbDataReader GetSiteSettingsExList(int siteId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT  e.* ");

            sqlCommand.Append("FROM	mp_SiteSettingsEx e ");

            sqlCommand.Append("JOIN ");
            sqlCommand.Append("mp_SiteSettingsExDef d ");
            sqlCommand.Append("ON ");
            sqlCommand.Append("e.KeyName = d.KeyName ");
            sqlCommand.Append("AND e.GroupName = d.GroupName ");

            sqlCommand.Append("WHERE ");
            sqlCommand.Append("e.SiteID = ?SiteID ");

            sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[1];

            arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
            arParams[0].Value = siteId;

            return AdoHelper.ExecuteReader(
                readConnectionString,
                sqlCommand.ToString(),
                arParams);

        }
        private void btnEnvoyer_Click(object sender, EventArgs e)
        {
            MySqlCommand cmd;

            //Global.Connection.Open();
            try
            {
                if (comboBox1.Text == "Sélectionnez la nature de votre requête")
                {
                    MessageBox.Show("Veuillez renseigner la nature de votre requête", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }else if(rtbContenu.Text == "")
                {
                    MessageBox.Show("Veuillez écrire votre requête", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } else {
                    cmd = new MySqlCommand("INSERT INTO requetecontact(idUtilisateur, contenu, dateContact, dateTraitement) VALUES(@idUtilisateur, @contenu, now(), '1000-01-01 00:00:00')", Global.Connection);
                    MySqlParameter pIdUtilisateur = new MySqlParameter("@idUtilisateur", MySqlDbType.Int16);
                    MySqlParameter pContenu = new MySqlParameter("@contenu", MySqlDbType.Text);
                    pIdUtilisateur.Value = Global.userId;
                    pContenu.Value = comboBox1.Text + " : " + rtbContenu.Text;
                    cmd.Parameters.Add(pIdUtilisateur);
                    cmd.Parameters.Add(pContenu);
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Votre reqûete a bien été prise en compte, nos équipes sont au travail ! ", "Succès", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    rtbContenu.Text = "";
                    comboBox1.SelectedItem = "Sélectionnez la nature de votre requête";
                }
            } catch (MySqlException) {
                MessageBox.Show("Une erreur est survenue. Impossible de contiuer.", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                rd.Close();
                Global.Connection.Close();
            }
        }
Exemplo n.º 15
0
 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString, string content)
 {
     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {
         MySqlCommand cmd = new MySqlCommand(SQLString, connection);
         MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
         myParameter.Value = content;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows = cmd.ExecuteNonQuery();
             return(rows);
         }
         catch (MySql.Data.MySqlClient.MySqlException e)
         {
             throw e;
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
Exemplo n.º 16
0
 /// <summary>
 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
 /// </summary>
 /// <param name="strSQL">SQL语句</param>
 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
 {
     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {
         MySqlCommand cmd = new MySqlCommand(strSQL, connection);
         MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
         myParameter.Value = fs;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             int rows = cmd.ExecuteNonQuery();
             return(rows);
         }
         catch (MySql.Data.MySqlClient.MySqlException e)
         {
             throw e;
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
Exemplo n.º 17
0
        public static bool Delete(
            string loginProvider,
            string providerKey,
            string userId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_UserLogins ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("LoginProvider = ?LoginProvider AND ");
            sqlCommand.Append("ProviderKey = ?ProviderKey AND ");
            sqlCommand.Append("UserId = ?UserId ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[3];

            arParams[0] = new MySqlParameter("?LoginProvider", MySqlDbType.VarChar, 128);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = loginProvider;

            arParams[1] = new MySqlParameter("?ProviderKey", MySqlDbType.VarChar, 128);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = providerKey;

            arParams[2] = new MySqlParameter("?UserId", MySqlDbType.VarChar, 128);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = userId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return (rowsAffected > 0);
        }
Exemplo n.º 18
0
        public static bool DeleteDescription(
            Guid statusGuid,
            Guid languageGuid)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM ws_OrderStatusDescription ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("StatusGuid = ?StatusGuid AND ");
            sqlCommand.Append("LanguageGuid = ?LanguageGuid ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[2];

            arParams[0] = new MySqlParameter("?StatusGuid", MySqlDbType.VarChar, 36);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = statusGuid.ToString();

            arParams[1] = new MySqlParameter("?LanguageGuid", MySqlDbType.VarChar, 36);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = languageGuid.ToString();

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return (rowsAffected > 0);
        }
Exemplo n.º 19
0
        private static void RunSqlQueryWithReturnId(string queryText, Dictionary<string, object> parametters, CommandType commandType, Action<int> action)
        {
            using (MySqlConnection oConn = new MySqlConnection(dbSetting.Default.MySqlConnectionString))
            {
                using (MySqlCommand oRS = oConn.CreateCommand())
                {
                    oConn.Open();
                    oRS.CommandType = commandType;
                    oRS.CommandText = queryText;
                    if (parametters != null && parametters.Count > 0)
                    {
                        foreach (var parametter in parametters)
                        {
                            oRS.Parameters.AddWithValue(parametter.Key, parametter.Value);
                        }
                    }

                    // output parameters
                    MySqlParameter param = new MySqlParameter("@ID", MySqlDbType.Int16);
                    param.Direction = ParameterDirection.Output;
                    oRS.Parameters.Add(param);

                    oRS.ExecuteNonQuery();

                    if (action != null)
                    {
                        action(int.Parse(oRS.LastInsertedId.ToString()));
                    }
                }
            }
        }
Exemplo n.º 20
0
        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox2.Text.Equals("a1") || textBox2.Text.Equals("a2"))
            {
                comm = new MySqlCommand();
                FileStream fs;
                fs = new FileStream(imagename, FileMode.Open, FileAccess.Read);
                byte[] picbyte = new byte[fs.Length];
                fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
                fs.Close();
                connect1();
                comm.Connection  = conn;
                comm.CommandText = "insert into movie2 values('" + comboBox1.Text + "','" + textBox2.Text + "',@pic); ";

                MySql.Data.MySqlClient.MySqlParameter picparameter = new MySql.Data.MySqlClient.MySqlParameter();
                comm.Parameters.Add("@pic", MySqlDbType.Blob);

                comm.Parameters["@pic"].Value = picbyte;
                if (richTextBox1.Text.Contains("'"))
                {
                    MessageBox.Show("enter a valid description without any /' ");
                }
                else
                {
                    comm.Parameters.Add(picparameter);
                    comm.ExecuteNonQuery();
                    comm.CommandText = "insert into movie1 values('" + textBox1.Text + "','" + richTextBox1.Text + "'); ";
                    comm.ExecuteNonQuery();
                }
            }
            else
            {
                MessageBox.Show("WRONG AUDITORIUM NAME TRY AGAIN");
            }
        }
Exemplo n.º 21
0
 /// <summary>
 /// 执行带一个存储过程参数的的SQL语句。
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
 /// <returns>影响的记录数</returns>
 public static object ExecuteSqlGet(string SQLString, string content)
 {
     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {
         MySqlCommand cmd = new MySqlCommand(SQLString, connection);
         MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
         myParameter.Value = content;
         cmd.Parameters.Add(myParameter);
         try
         {
             connection.Open();
             object obj = cmd.ExecuteScalar();
             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
             {
                 return(null);
             }
             else
             {
                 return(obj);
             }
         }
         catch (MySql.Data.MySqlClient.MySqlException e)
         {
             throw e;
         }
         finally
         {
             cmd.Dispose();
             connection.Close();
         }
     }
 }
Exemplo n.º 22
0
        public static Course fetchCoursebyId(int _courseid)
        {
            dbManager db = new dbManager();
                Course cs = null;

                string sqlCommand;
                MySqlParameter[] param = new MySqlParameter[1];

                param[0] = new MySqlParameter("@courseid", _courseid);

                sqlCommand = "select * from Course where courseid=@courseid";

                DataTable ds = db.fetchRows(sqlCommand, param);

                int i = 0;

                foreach (DataRow dr in ds.Rows)
                {
                    cs = new Course(Convert.ToInt32(dr["courseid"].ToString()),
                                        dr["coursename"].ToString(),
                                        (DateTime)dr["startdate"],
                                        (DateTime)dr["enddate"],
                                        dr["classondays"].ToString(),
                                        (TimeSpan)dr["classstarttime"],
                                        (TimeSpan)dr["classendtime"]);
                    i++;
                }

                return cs;
        }
Exemplo n.º 23
0
 public static MySqlParameter CreateParameter(ParameterDirection direction, string paramName, MySqlDbType dbtype, int size, object value)
 {
     MySqlParameter param = new MySqlParameter(paramName, dbtype, size);
     param.Value = value;
     param.Direction = direction;
     return param;
 }
Exemplo n.º 24
0
 private static void SearchForBook(string input)
 {
     using (var mySqlConnection = new MySqlConnection(ConnectionString))
     {
         mySqlConnection.Open();
         using (mySqlConnection)
         {
             const string SqlStr = "USE `bookStores` ; SELECT AuthorName, titleBook, publishDate, ISBN  FROM books "
                                   + "JOIN authors " + "ON authors.Books_idBooks = books.idBooks "
                                   + "WHERE titleBook LIKE @input";
             var cmdParam = new MySqlParameter("@input", "%" + input + "%");
             var cmd = new MySqlCommand(SqlStr, mySqlConnection);
             cmd.Parameters.Add(cmdParam);
             var reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 var author = (string)reader["AuthorName"];
                 var title = (string)reader["titleBook"];
                 var date = (DateTime)reader["publishDate"];
                 var isbn = (long)reader["ISBN"];
                 Console.WriteLine("{0}: {1} {2} {3}", author, title, date, isbn);
             }
         }
     }
 }
Exemplo n.º 25
0
        public int addOnlyExam(Examination exam)
        {
            string strSql = "insert into ex_examination(EXAM_CAT, EXAM_NAME, START_TIME, TOTAL_MINS, SCORES, EX_TYPE,NUM,EX_TYPE_LX) values(?examCat, ?examName, ?startTime, ?totalMins, ?scores, ?exType, ?Num,?exType_lx); select last_insert_id();";
            MySqlParameter[] parames = new MySqlParameter[] {
                new MySqlParameter("?examCat", MySqlDbType.VarChar),
                new MySqlParameter("?examName", MySqlDbType.VarChar),
                new MySqlParameter("?startTime", MySqlDbType.VarChar),
                new MySqlParameter("?totalMins", MySqlDbType.Int32),
                new MySqlParameter("?scores", MySqlDbType.VarChar),
                new MySqlParameter("?exType", MySqlDbType.VarChar),
                new MySqlParameter("?Num", MySqlDbType.Int32),
                new MySqlParameter("?exType_lx", MySqlDbType.VarChar)
            };
            parames[0].Value = exam.ExamCat;
            parames[1].Value = exam.ExamName;
            parames[2].Value = exam.StartTime;
            parames[3].Value = exam.TotalMins;
            parames[4].Value = exam.Scores;
            parames[5].Value = "1";
            parames[6].Value = exam.Num;
            parames[7].Value = exam.ExType_lx;//选题方式

            int examId = Convert.ToInt32(MySqlHelper.ExecuteScalar(strSql, parames));
            exam.ExaminationId = examId;
            return examId;
        }
        public void AnnulerenBestelling(int bestellingscode)
        {
            try
            {
                conn.Open();

                string insertString = @"UPDATE factuur " +
                                        "SET status=@status " +
                                         "WHERE factuur_code=@factuur_code";

                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter bestellingscodeParam = new MySqlParameter("@factuur_code", MySqlDbType.Int32);
                MySqlParameter statusParam = new MySqlParameter("@status", MySqlDbType.Enum);

                bestellingscodeParam.Value = bestellingscode;
                statusParam.Value = "Geannulleerd";

                cmd.Parameters.Add(bestellingscodeParam);
                cmd.Parameters.Add(statusParam);

                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.Write("Updaten bestelling niet gelukt: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
Exemplo n.º 27
0
        public static int CountActiveByProduct(Guid siteGuid, Guid productGuid)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT  Count(*) ");
            sqlCommand.Append("FROM	sts_MembershipTicket ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("SiteGuid = ?SiteGuid ");
            sqlCommand.Append("AND ProductGuid = ?ProductGuid ");
            sqlCommand.Append("AND OrderStatusGuid = '70443443-f665-42c9-b69f-48cbf011a14b' "); //fulfillable status
            sqlCommand.Append("AND ?CurrentTimeUtc < TIMESTAMPADD(DAY, GracePeriodDays, EndDateUtc) "); //before end of grace period
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[3];

            arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = siteGuid.ToString();

            arParams[1] = new MySqlParameter("?ProductGuid", MySqlDbType.VarChar, 36);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = productGuid.ToString();

            arParams[2] = new MySqlParameter("?CurrentTimeUtc", MySqlDbType.DateTime);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = DateTime.UtcNow;

            return Convert.ToInt32(MySqlHelper.ExecuteScalar(
                GetReadConnectionString(),
                sqlCommand.ToString(),
                arParams));
        }
        public void DeleteCategorie(int categorie_id)
        {
            try
            {
                conn.Open();

                string insertString = @"delete from categorie where categorie_Id=@categorie_id";
                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter sportParam = new MySqlParameter("@categorie_id", MySqlDbType.Int32);

                sportParam.Value = categorie_id;

                cmd.Parameters.Add(sportParam);

                cmd.Prepare();

                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.Write("categorie niet verwijderd: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
Exemplo n.º 29
0
        public static int AddProduct(string Product_Name, string Source, int Cost)
        {
            string query = "INSERT INTO Product (Product_Name, Cost, Source_Name) VALUES (@prod, @cost, @source)";
            MySqlParameter[] parameters = new MySqlParameter[]
                {
                    new MySqlParameter("@prod", Product_Name),
                    new MySqlParameter("@cost", Cost),
                    new MySqlParameter("@source", Source)

                };

            DataAccess.OpenConn();
            WriteData(query, parameters);

            query = "SELECT LAST_INSERT_ID()";
            parameters = new MySqlParameter[]
            {
            };
            MySqlDataReader rdr = ReadData(query, parameters);
            rdr.Read();
            int retval = rdr.GetInt32(0);

            DataAccess.CloseConn();
            return retval;
        }
Exemplo n.º 30
0
        protected void DataListSzukaj_ItemDataBound(object sender, DataListItemEventArgs e)
        {
            if (e.Item.DataItem == null) return;

            int ilosc = Int32.Parse(((DataRowView)e.Item.DataItem)["ilosc"].ToString());

            if (ilosc < 1) ((Button)e.Item.FindControl("ButtonDoKoszyka")).Enabled = false;

            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader rdr;
            MySqlParameter param;

            conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CS"].ConnectionString);

            conn.Open();

            cmd = new MySqlCommand("SELECT obrazek FROM produkty_obrazki WHERE produktID = @produktID LIMIT 1;");
            cmd.Connection = conn;

            param = new MySqlParameter("produktID", MySqlDbType.Int32);
            param.Value = ((DataRowView)e.Item.DataItem)["produktID"].ToString();
            cmd.Parameters.Add(param);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                ((Image)e.Item.FindControl("ImageObrazek")).ImageUrl = "~/images/products/" + ((DataRowView)e.Item.DataItem)["produktID"].ToString() + "/" + rdr.GetString(rdr.GetOrdinal("obrazek"));
            }

            conn.Close();
        }
Exemplo n.º 31
0
        /// <summary>
        /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value
        /// greater than the provided trackOrder.
        /// </summary>
        /// <param name="playerID">The ID of the Player.</param>
        /// <param name="trackOrder">The TrackOrder value.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_MediaTrack ");
            sqlCommand.Append("SET TrackOrder = TrackOrder - 1 ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("PlayerID = ?PlayerID ");
            sqlCommand.Append("AND TrackOrder > ?TrackOrder ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[2];

            arParams[0] = new MySqlParameter("?TrackOrder", MySqlDbType.Int32);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = trackOrder;

            arParams[1] = new MySqlParameter("?PlayerID", MySqlDbType.Int32);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = playerId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return rowsAffected;
        }
Exemplo n.º 32
0
        public static int AddClient(string bagType, int pday, string first, string last, string gender, DateTime dob, string street, string apt, string city, string state, string zip, string phone, string aid, string start)
        {
            string query = "INSERT INTO Client (FirstName, LastName, Phone, Street, Apt_No, City, State, Zipcode, PickUpDay, Start, Date_Of_Birth, Gender, Bag_Type) VALUES (@first, @last, @phone, @street, @apt, @city, @state, @zip, @day, @start, @dob, @gender, @bagType)";
            MySqlParameter[] parameters = new MySqlParameter[]
                {
                    new MySqlParameter("@first", first),
                    new MySqlParameter("@last", last),
                    new MySqlParameter("@phone", phone),
                    new MySqlParameter("@street", street),
                    new MySqlParameter("@apt", apt),
                    new MySqlParameter("@city", city),
                    new MySqlParameter("@state", state),
                    new MySqlParameter("@zip", zip),
                    new MySqlParameter("@day", pday),
                    new MySqlParameter("@start", start),
                    new MySqlParameter("@dob", dob),
                    new MySqlParameter("@gender", gender),
                    new MySqlParameter("@bagType", bagType)
                };

            DataAccess.OpenConn();
            WriteData(query, parameters);

            query = "SELECT LAST_INSERT_ID()";
            parameters = new MySqlParameter[]
            {
            };
            MySqlDataReader rdr = ReadData(query, parameters);
            rdr.Read();
            int retval = rdr.GetInt32(0);

            DataAccess.CloseConn();
            return retval;
        }
Exemplo n.º 33
0
 public GZScenery queryAll(long gzSceneryID)
 {
     conn.Open();
     GZScenery gzScenery = new GZScenery();
     string sql = "select * from gzScenery where gzSceneryID = ?gzSceneryID";
     MySqlCommand cmd = new MySqlCommand(sql, conn);
     MySqlParameter[] parameters = new MySqlParameter[1];
     parameters[0] = new MySqlParameter("?gzSceneryID", MySqlDbType.Int64);
     parameters[0].Value = gzSceneryID;
     cmd.Parameters.AddRange(parameters);
     MySqlDataReader reader = cmd.ExecuteReader();
     reader.Read();
     gzScenery.GZSceneryLinePlay = reader[1].ToString();
     gzScenery.GZSceneryIntroduce = reader[2].ToString();
     gzScenery.GZSceneryName = reader[3].ToString();
     gzScenery.GZSceneryCity = reader[4].ToString();
     gzScenery.GZSceneryDateTime = reader[5].ToString();
     gzScenery.GZSceneryTripDay = reader[6].ToString();
     gzScenery.GZSceneryAdultPrice = reader[7].ToString();
     gzScenery.GZSceneryChildren = reader[8].ToString();
     gzScenery.GZScenerySingleRoom = reader[9].ToString();
     gzScenery.GZSceneryTravelDetails = reader[10].ToString();
     gzScenery.GZSceneryGridInformation = reader[11].ToString();
     gzScenery.GZSceneryCost = reader[12].ToString();
     return gzScenery;
 }
Exemplo n.º 34
0
        ///<summary>
        ///</summary>
        ///<param name="paramName"></param>
        ///<param name="dbType"></param>
        ///<param name="size"></param>
        ///<param name="direction"></param>
        ///<param name="value"></param>
        ///<returns></returns>
        ///<exception cref="ArgumentOutOfRangeException"></exception>
        public static MySqlParameter MakeParam(string paramName, MySqlDbType dbType, int size, ParameterDirection direction, object value)
        {
            MySqlParameter sqlParameter = null;
            try
            {
                paramName = paramName ?? string.Empty;

                //modify reason:验证花时比较大
                //if (!MathUtils.IsMachVarName(paramName))
                //{
                //    throw new ArgumentOutOfRangeException("paramName", "参数名格式不正确");
                //}
                if (size > 0)
                {
                    sqlParameter = new MySqlParameter(FormatParamName(paramName), dbType, size);
                }
                else
                {
                    sqlParameter = new MySqlParameter(FormatParamName(paramName), dbType);
                }
                sqlParameter.Direction = direction;
                if (direction != ParameterDirection.Output || value != null)
                {
                    sqlParameter.Value = value;
                }
            }
            catch (Exception ex)
            {
                TraceLog.WriteError("{0}", ex);
            }
            return sqlParameter;
        }
Exemplo n.º 35
0
 public int Add(Maticsoft.Email.Model.MailConfig model)
 {
     StringBuilder builder = new StringBuilder();
     builder.Append("insert into Accounts_MailConfig(");
     builder.Append("UserID,Mailaddress,Username,Password,SMTPServer,SMTPPort,SMTPSSL,POPServer,POPPort,POPSSL)");
     builder.Append(" values (");
     builder.Append("?UserID,?Mailaddress,?Username,?Password,?SMTPServer,?SMTPPort,?SMTPSSL,?POPServer,?POPPort,?POPSSL)");
     builder.Append(";select ??IDENTITY");
     MySqlParameter[] cmdParms = new MySqlParameter[] { new MySqlParameter("?UserID", MySqlDbType.Int32, 4), new MySqlParameter("?Mailaddress", MySqlDbType.VarChar, 100), new MySqlParameter("?Username", MySqlDbType.VarChar, 50), new MySqlParameter("?Password", MySqlDbType.VarChar, 50), new MySqlParameter("?SMTPServer", MySqlDbType.VarChar, 50), new MySqlParameter("?SMTPPort", MySqlDbType.Int32, 4), new MySqlParameter("?SMTPSSL", MySqlDbType.Int16, 2), new MySqlParameter("?POPServer", MySqlDbType.VarChar, 50), new MySqlParameter("?POPPort", MySqlDbType.Int32, 4), new MySqlParameter("?POPSSL", MySqlDbType.Int16, 2) };
     cmdParms[0].Value = model.UserID;
     cmdParms[1].Value = model.Mailaddress;
     cmdParms[2].Value = model.Username;
     cmdParms[3].Value = model.Password;
     cmdParms[4].Value = model.SMTPServer;
     cmdParms[5].Value = model.SMTPPort;
     cmdParms[6].Value = model.SMTPSSL ? 1 : 0;
     cmdParms[7].Value = model.POPServer;
     cmdParms[8].Value = model.POPPort;
     cmdParms[9].Value = model.POPSSL ? 1 : 0;
     object single = DbHelperMySQL.GetSingle(builder.ToString(), cmdParms);
     if (single == null)
     {
         return 1;
     }
     return Convert.ToInt32(single);
 }
Exemplo n.º 36
0
        public DtoLib.ResultadoLista <DtoLibPos.Cliente.Documento.Ficha> Cliente_Documento_GetLista(DtoLibPos.Cliente.Documento.Filtro filtro)
        {
            var rt = new DtoLib.ResultadoLista <DtoLibPos.Cliente.Documento.Ficha>();

            try
            {
                using (var cnn = new PosEntities(_cnPos.ConnectionString))
                {
                    var sql_1 = @"SELECT v.auto as id, v.fecha, v.documento, v.total as monto, 
                                    v.monto_divisa as montoDivisa, v.factor_cambio as tasaDivisa, 
                                    v.estatus_anulado as estatus, v.tipo as codTipoDoc, v.serie, 
                                    v.signo, v.documento_nombre as nombreTipoDoc  ";
                    var sql_2 = " FROM ventas as v";
                    var sql_3 = " where 1=1  ";
                    var sql_4 = "";

                    var p1 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p2 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p3 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p4 = new MySql.Data.MySqlClient.MySqlParameter();

                    if (filtro.autoCliente != "")
                    {
                        p1.ParameterName = "@p1";
                        p1.Value         = filtro.autoCliente;
                        sql_3           += " and v.auto_cliente=@p1  ";
                    }
                    if (filtro.desde.HasValue)
                    {
                        p2.ParameterName = "@p2";
                        p2.Value         = filtro.desde;
                        sql_3           += " and v.fecha>=@p2 ";
                    }
                    if (filtro.hasta.HasValue)
                    {
                        p3.ParameterName = "@p3";
                        p3.Value         = filtro.hasta;
                        sql_3           += " and v.fecha<=@p3 ";
                    }
                    if (filtro.tipoDoc != "")
                    {
                        p4.ParameterName = "@p4";
                        p4.Value         = filtro.tipoDoc;
                        sql_3           += " and v.tipo=@p4 ";
                    }

                    var sql  = sql_1 + sql_2 + sql_3 + sql_4;
                    var list = cnn.Database.SqlQuery <DtoLibPos.Cliente.Documento.Ficha>(sql, p1, p2, p3, p4).ToList();
                    rt.Lista = list;
                }
            }
            catch (Exception e)
            {
                rt.Mensaje = e.Message;
                rt.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(rt);
        }
 /// <summary>
 /// 修改
 /// </summary>
 /// <param name="Operate_text">操作说明</param>
 /// <param name="OperateID">ID</param>
 /// <returns></returns>
 public bool UpdateModuleOperate(string Operate_text, int OperateID)
 {
     MySql.Data.MySqlClient.MySqlParameter[] param = new MySql.Data.MySqlClient.MySqlParameter[] {
         new MySql.Data.MySqlClient.MySqlParameter("@Operate_text", Operate_text),
         new MySql.Data.MySqlClient.MySqlParameter("@OperateID", OperateID)
     };
     return(MySqlHelper.ExecuteNonQuery(SQL_UPDATE_old, param) == 1);
 }
 public bool UpdateModuleOperateStatus(string status, int OperateID)
 {
     MySql.Data.MySqlClient.MySqlParameter[] param = new MySql.Data.MySqlClient.MySqlParameter[] {
         new MySql.Data.MySqlClient.MySqlParameter("@status", status),
         new MySql.Data.MySqlClient.MySqlParameter("@OperateID", OperateID)
     };
     return(MySqlHelper.ExecuteNonQuery(SQL_UPDATE_STATUS, param) == 1);
 }
 public bool AddModuleOperate(string Operate_text, string status)
 {
     MySql.Data.MySqlClient.MySqlParameter[] param = new MySql.Data.MySqlClient.MySqlParameter[] {
         new MySql.Data.MySqlClient.MySqlParameter("@Operate_text", Operate_text),
         new MySql.Data.MySqlClient.MySqlParameter("@status", status)
     };
     return(MySqlHelper.ExecuteNonQuery(SQL_ADD, param) == 1);
 }
Exemplo n.º 40
0
        public void executeNQDate(String query, DateTime Date)
        {
            DBB.Connection.Open();
            MySqlCommand msc = new MySqlCommand(query, DBB.Connection);

            MySql.Data.MySqlClient.MySqlParameter param = msc.Parameters.Add("?Date", MySql.Data.MySqlClient.MySqlDbType.Datetime);
            param.Value = Date;
            msc.ExecuteNonQuery();
            DBB.Connection.Close();
        }
Exemplo n.º 41
0
        public void executeNQ(String query, byte[] Data)
        {
            DBB.Connection.Open();
            MySqlCommand msc = new MySqlCommand(query, DBB.Connection);

            MySql.Data.MySqlClient.MySqlParameter param = msc.Parameters.Add("?Data", MySql.Data.MySqlClient.MySqlDbType.Blob);
            param.Value = Data;
            msc.ExecuteNonQuery();
            DBB.Connection.Close();
        }
Exemplo n.º 42
0
        protected override IDbDataParameter CreateParameter(String name, DataType dataType)
        {
            var p = new MySql.Data.MySqlClient.MySqlParameter(name, dataType.DbType.MySqlServerDbType.Value.GetMySqlDbType());

            if (dataType is SqlInputParameter dType)
            {
                p.Direction = dType.ParameterDirection;
            }
            if (p.Direction != ParameterDirection.Output)
            {
                p.Value = this.GetParameterValue(dataType, dataType.DbType.MySqlServerDbType.Value);
            }
            return(p);
        }
        public DtoLib.ResultadoLista <DtoLibPosOffLine.Monitor.ListaResumen.Ficha> Monitor_ListaResumen()
        {
            var result = new DtoLib.ResultadoLista <DtoLibPosOffLine.Monitor.ListaResumen.Ficha>();

            try
            {
                using (var cn = new MySqlConnection(_cnn2.ConnectionString))
                {
                    cn.Open();

                    var sql0      = @"SELECT MAX(cierre ) as ultCierre
                                FROM monitor_cierre";
                    var comando0  = new MySqlCommand(sql0, cn);
                    var ultCierre = comando0.ExecuteScalar();

                    var lst = new List <DtoLibPosOffLine.Monitor.ListaResumen.Ficha>();
                    var p0  = new MySql.Data.MySqlClient.MySqlParameter();
                    p0.ParameterName = "ultCierreTransmitido";
                    p0.Value         = ultCierre;
                    var sql1     = @"select substr(auto_cierre,5) as cierre 
                                from pos_arqueo 
                                where substr(auto_cierre,5)>@ultCierreTransmitido";
                    var comando1 = new MySqlCommand(sql1, cn);
                    comando1.Parameters.Clear();
                    comando1.Parameters.Add(p0);
                    var rd = comando1.ExecuteReader();
                    while (rd.Read())
                    {
                        var nr = new DtoLibPosOffLine.Monitor.ListaResumen.Ficha()
                        {
                            cierreGenerar = rd.GetString("cierre"),
                        };
                        lst.Add(nr);
                    }
                    rd.Close();
                    result.Lista = lst;
                };
            }
            catch (Exception e)
            {
                result.Mensaje = e.Message;
                result.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(result);
        }
        public DtoLib.Resultado Monitor_InsertarCierre(DtoLibPosOffLine.Monitor.InsertarCierre.Ficha ficha)
        {
            var result = new DtoLib.Resultado();

            try
            {
                using (var cn = new MySqlConnection(_cnn2.ConnectionString))
                {
                    cn.Open();
                    MySqlTransaction tr = null;

                    try
                    {
                        tr = cn.BeginTransaction();

                        var p0 = new MySql.Data.MySqlClient.MySqlParameter();
                        var p1 = new MySql.Data.MySqlClient.MySqlParameter();
                        p0.ParameterName = "cierre";
                        p0.Value         = ficha.cierre;
                        p1.ParameterName = "estatus";
                        p1.Value         = ficha.estatus;
                        var sql0     = @"INSERT INTO monitor_cierre (cierre, estatus) VALUES (@cierre,@estatus)";
                        var comando1 = new MySqlCommand(sql0, cn, tr);
                        comando1.Parameters.Clear();
                        comando1.Parameters.Add(p0);
                        comando1.Parameters.Add(p1);
                        comando1.ExecuteNonQuery();

                        tr.Commit();
                    }
                    catch (Exception ex1)
                    {
                        tr.Rollback();
                        result.Mensaje = ex1.Message;
                        result.Result  = DtoLib.Enumerados.EnumResult.isError;
                    };
                };
            }
            catch (Exception e)
            {
                result.Mensaje = e.Message;
                result.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(result);
        }
Exemplo n.º 45
0
        public dynamic AddNewUser(UserModel newuser)
        {
            if (IsRegisterEmailAddExists(newuser.EmailAddress) >= 1)
            {
                newuser.IsUserExist = true;
                return(newuser);
            }

            oCon.Open();

            oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand("sp_addnewuser", oCon);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            oMySQLData.MySqlParameter userEmail  = new oMySQLData.MySqlParameter("userEmail", newuser.EmailAddress);
            oMySQLData.MySqlParameter firstName  = new oMySQLData.MySqlParameter("firstName", newuser.FirstName);
            oMySQLData.MySqlParameter middleName = new oMySQLData.MySqlParameter("middleName", newuser.MiddleName);
            oMySQLData.MySqlParameter lastName   = new oMySQLData.MySqlParameter("lastName", newuser.LastName);
            oMySQLData.MySqlParameter pswd       = new oMySQLData.MySqlParameter("pswd", newuser.Password);

            string code = RandomString(6);

            oMySQLData.MySqlParameter activationCode = new oMySQLData.MySqlParameter("activationCode", code);

            cmd.Parameters.Add(userEmail);
            cmd.Parameters.Add(firstName);
            cmd.Parameters.Add(middleName);
            cmd.Parameters.Add(lastName);
            cmd.Parameters.Add(pswd);
            cmd.Parameters.Add(activationCode);

            int result = 0;

            result = cmd.ExecuteNonQuery();

            if (result > 0)
            {
                EmailNotification notif = new EmailNotification();
                notif.NotifyNewUsserForActivation(newuser.EmailAddress, code);

                return(newuser);
            }

            oCon.Close();
            return(result);
        }
Exemplo n.º 46
0
        public DtoLib.ResultadoLista <DtoLibPos.Cliente.Articulos.Ficha> Cliente_ArticuloVenta_GetLista(DtoLibPos.Cliente.Articulos.Filtro filtro)
        {
            var rt = new DtoLib.ResultadoLista <DtoLibPos.Cliente.Articulos.Ficha>();

            try
            {
                using (var cnn = new PosEntities(_cnPos.ConnectionString))
                {
                    var sql_1 = @"SELECT p.codigo as codigoPrd, p.nombre as nombrePrd, v.fecha, v.documento, 
                            vd.cantidad, vd.cantidad_und as cantUnd, vd.empaque, vd.estatus_anulado as estatus, 
                            vd.contenido_empaque as contenidoEmp, v.tipo as codTipoDoc, v.serie, v.factor_cambio as tasaCambio, 
                            vd.precio_und as precioUnd, v.signo, v.documento_nombre as nombreTipoDoc ";

                    var sql_2 = @" FROM ventas_detalle as vd 
                                join productos as p on vd.auto_producto=p.auto 
                                join ventas as v on vd.auto_documento=v.auto ";

                    var sql_3 = " where v.auto_cliente=@p1 and v.fecha>=@p2 and v.fecha<=@p3 and v.tipo in ('01','04') ";
                    var sql_4 = "";

                    var p1 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p2 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p3 = new MySql.Data.MySqlClient.MySqlParameter();
                    var p4 = new MySql.Data.MySqlClient.MySqlParameter();

                    p1.ParameterName = "@p1";
                    p1.Value         = filtro.autoCliente;
                    p2.ParameterName = "@p2";
                    p2.Value         = filtro.desde;
                    p3.ParameterName = "@p3";
                    p3.Value         = filtro.hasta;

                    var sql  = sql_1 + sql_2 + sql_3 + sql_4;
                    var list = cnn.Database.SqlQuery <DtoLibPos.Cliente.Articulos.Ficha>(sql, p1, p2, p3, p4).ToList();
                    rt.Lista = list;
                }
            }
            catch (Exception e)
            {
                rt.Mensaje = e.Message;
                rt.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(rt);
        }
Exemplo n.º 47
0
        public CourseListModel GetCourseDetail(string courseID)
        {
            MySqlParameter param = new MySql.Data.MySqlClient.MySqlParameter();

            param.ParameterName = "@Course";
            param.Value         = courseID;
            string sql =
                @"SELECT c.ID,c.LevelID,c.PhotoID,c.CourseName,c.SerialNum,c.StartTime,c.EndTime,c.StatusCode,c.TrueName AS TeacherName,
c.CreateCourseUserID,c.DeleteMark,o.OrgName ,c.LogoID as TeacherImg FROM (
SELECT c.ID,c.LevelID,c.PhotoID,c.CourseName,c.SerialNum,c.StartTime,c.EndTime,c.StatusCode,
c.CreateCourseUserID,c.DeleteMark,u.TrueName,u.OrgID,u.LogoID FROM course AS c 
LEFT JOIN `User`as u 
ON  c.CreateCourseUserID = u.ID) AS c 
LEFT JOIN organize as o
ON c.OrgID = o.ID
WHERE c.id = @Course";

            DbParameter[] dbParam = new DbParameter[] {
                param
            };
            var list = repository.FindList <CourseListModel>(sql, dbParam).ToList();

            if (list.Count == 1)
            {
                list[0].PhotoID = adminurl + list[0].PhotoID;
                if (DateTime.Compare(Convert.ToDateTime(list[0].StartTime), DateTime.Now) > 0)
                {
                    list[0].StatusCode = 0;//即将开课
                }
                else if (DateTime.Compare(DateTime.Now, Convert.ToDateTime(list[0].EndTime)) > 0)
                {
                    list[0].StatusCode = 2;//已结课
                }
                else
                {
                    list[0].StatusCode = 1;//开课中
                }
                return(list[0]);
            }

            return(null);
        }
        public DtoLib.Resultado MonitorBoletin_Actualizar(string boletin)
        {
            var result = new DtoLib.Resultado();

            try
            {
                using (var cn = new MySqlConnection(_cnn3.ConnectionString))
                {
                    cn.Open();
                    MySqlTransaction tr = null;

                    try
                    {
                        tr = cn.BeginTransaction();

                        var p1 = new MySql.Data.MySqlClient.MySqlParameter();
                        p1.ParameterName = "boletin";
                        p1.Value         = boletin;
                        var sql1     = @"update monitor_boletin set boletin_info=@boletin where id=1";
                        var comando1 = new MySqlCommand(sql1, cn, tr);
                        comando1.Parameters.Clear();
                        comando1.Parameters.Add(p1);
                        var idObj = comando1.ExecuteScalar();

                        tr.Commit();
                    }
                    catch (Exception ex1)
                    {
                        tr.Rollback();
                        result.Mensaje = ex1.Message;
                        result.Result  = DtoLib.Enumerados.EnumResult.isError;
                    }
                };
            }
            catch (Exception e)
            {
                result.Mensaje = e.Message;
                result.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(result);
        }
Exemplo n.º 49
0
        public IDbDataParameter CreateParameter()
        {
            var name = this.Name;

            switch (this.DbType.DatabaseServer)
            {
            case DatabaseServer.SqlServer:
            {
                var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                p.Value     = GetParameterValue(this.DbType.SqlServerDbType.Value);
                return(p);
            }

            case DatabaseServer.Oracle:
            {
                var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                //p.Value = GetParameterValue(this.OracleServerDbType.Value);
                return(p);
            }

            case DatabaseServer.MySql:
            {
                var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                p.Value     = GetParameterValue(this.DbType.MySqlServerDbType.Value);
                return(p);
            }

            case DatabaseServer.PostgreSql:
            {
                var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                //p.Value = GetParameterValue(this.MySqlDbType.Value);
                return(p);
            }

            default: throw new InvalidOperationException();
            }
        }
Exemplo n.º 50
0
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, List <Tuple <string, byte[]> > imgList)
        {
            int result = 0;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strSQL, connection))
                {
                    try
                    {
                        connection.Open();
                        foreach (var t in imgList)
                        {
                            string imgTag  = t.Item1;
                            byte[] imgData = t.Item2;
                            MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter(imgTag, imgData);
                            cmd.Parameters.Add(myParameter);
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (cmd != null)
                        {
                            cmd.Dispose();
                        }
                        if (connection != null)
                        {
                            connection.Close();
                        }
                    }
                }
            }

            return(result);
        }
Exemplo n.º 51
0
        public dynamic ValidateUserActivation(ActivateUserModel activate)
        {
            oCon.Open();

            oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand("sp_activateuser", oCon);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            oMySQLData.MySqlParameter userEmail      = new oMySQLData.MySqlParameter("userEmail", activate.UserEmail);
            oMySQLData.MySqlParameter activationCode = new oMySQLData.MySqlParameter("activationCode", activate.ActivationCode);

            cmd.Parameters.Add(userEmail);
            cmd.Parameters.Add(activationCode);

            int result = 0;

            result = cmd.ExecuteNonQuery();

            oCon.Close();

            return(result);
        }
Exemplo n.º 52
0
        public int ExecuteWithContent(string sql, string content)
        {
            int result = 0;

            try
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, DbConn))
                {
                    MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", content);
                    //myParameter.Value = content;
                    cmd.Parameters.Add(myParameter);

                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (System.Exception ex)
            {
                LogManager.WriteExceptionUseCache(sql + "\r\n" + ex.ToString());
            }

            return(result);
        }
Exemplo n.º 53
0
        public int RegisterTournamentBet(TournamentRegistrationModel register)
        {
            oCon.Open();

            oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand("sp_registerusertournamentbet", oCon);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            oMySQLData.MySqlParameter userID       = new oMySQLData.MySqlParameter("userID", register.UserID);
            oMySQLData.MySqlParameter tournamentID = new oMySQLData.MySqlParameter("tournamentID", register.TournamentID);
            oMySQLData.MySqlParameter betPoints    = new oMySQLData.MySqlParameter("betPoints", register.TournamentPoints);

            cmd.Parameters.Add(userID);
            cmd.Parameters.Add(tournamentID);
            cmd.Parameters.Add(betPoints);

            int result = 0;

            result = cmd.ExecuteNonQuery();
            oCon.Close();

            return(result);
        }
Exemplo n.º 54
0
        private CourseTeacherModel GetTeacherModel(string teacherid)
        {
            MySql.Data.MySqlClient.MySqlParameter param = new MySql.Data.MySqlClient.MySqlParameter();
            param.ParameterName = "@UserID";
            param.Value         = teacherid;
            string sql =
                @"SELECT U.ID,U.TrueName,U.ReMark,U.Department,O.OrgName FROM `User` AS U LEFT JOIN
organize AS O
ON U.OrgID = O.ID
WHERE U.ID = @UserID;";

            DbParameter[] dbParam = new DbParameter[] {
                param
            };
            var list = repository.FindList <CourseTeacherModel>(sql, dbParam).ToList();

            if (list.Count == 1)
            {
                return(list[0]);
            }
            return(null);
        }
Exemplo n.º 55
0
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteNonQuery(string SQLString, string content)
        {
            bool br = Monitor.TryEnter(this.objLock, 5000);

            if (br == false)
            {
                log.Info("can not get lock");
                return(0);
            }

            log.InfoFormat("execute sql:{0}", SQLString);
            //using (MySqlConnection connection = new MySqlConnection(connectionString))
            MySqlConnection connection = GetConnection();
            {
                MySqlCommand cmd = new MySqlCommand(SQLString, connection);
                MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    int rows = cmd.ExecuteNonQuery();
                    return(rows);
                }
                //catch (MySql.Data.MySqlClient.MySqlException e)
                catch (Exception e)
                {
                    log.Error(e);
                    //return 0;
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    ReturnConnection(connection);
                    Monitor.Exit(this.objLock);
                }
            }
        }
        public DtoLib.ResultadoLista <DtoLibSistema.GrupoUsuario.Usuario> GrupoUsuario_GetUsuarios(string auto)
        {
            var result = new DtoLib.ResultadoLista <DtoLibSistema.GrupoUsuario.Usuario>();

            try
            {
                using (var cnn = new sistemaEntities(_cnSist.ConnectionString))
                {
                    var p1  = new MySql.Data.MySqlClient.MySqlParameter("@autoGrupo", auto);
                    var sql = @"select auto as autoId, nombre, apellido, codigo, estatus 
                                from usuarios where auto_grupo=@autoGrupo";
                    var lst = cnn.Database.SqlQuery <DtoLibSistema.GrupoUsuario.Usuario>(sql, p1).ToList();
                    result.Lista = lst;
                }
            }
            catch (Exception e)
            {
                result.Mensaje = e.Message;
                result.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(result);
        }
Exemplo n.º 57
0
        public dynamic LogInUser(UserModel loginuser)
        {
            oCon.Open();

            oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand("sp_loginuser", oCon);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            oMySQLData.MySqlParameter loginEmail    = new oMySQLData.MySqlParameter("loginEmail", loginuser.EmailAddress);
            oMySQLData.MySqlParameter loginPassword = new oMySQLData.MySqlParameter("loginPassword", loginuser.Password);

            cmd.Parameters.Add(loginEmail);
            cmd.Parameters.Add(loginPassword);

            oMySQLData.MySqlDataReader reader = cmd.ExecuteReader();

            UserModel user = new UserModel();

            while (reader.Read())
            {
                user.UserID       = Convert.ToInt32(reader["flduserid"]);
                user.EmailAddress = reader["fldUserEmail"].ToString();
                user.FirstName    = reader["fldFirstName"].ToString();
                user.IsVerified   = Convert.ToBoolean(reader["fldIsVerified"]);
                user.IsUserExist  = true;
            }

            oCon.Close();

            if (user == null)
            {
                user.EmailAddress = loginuser.EmailAddress;
                user.IsUserExist  = false;
            }

            return(user);
        }
Exemplo n.º 58
0
 public void Add(MySql.Data.MySqlClient.MySqlParameter param)
 {
     collection.Add(param.ParameterName, param);
 }
        public DtoLib.ResultadoLista <DtoLibInventario.MonitorPos.Entidad.Ficha> MonitorPos_VentaResumen_GetLista(DtoLibInventario.MonitorPos.Lista.Filtro filtro)
        {
            var result = new DtoLib.ResultadoLista <DtoLibInventario.MonitorPos.Entidad.Ficha> ();

            try
            {
                using (var cn = new MySqlConnection(_cnn2.ConnectionString))
                {
                    MySqlTransaction tr = null;
                    cn.Open();

                    var lst = new List <DtoLibInventario.MonitorPos.Entidad.Ficha>();
                    try
                    {
                        var p0 = new MySql.Data.MySqlClient.MySqlParameter();
                        p0.ParameterName = "codSuc";
                        p0.Value         = filtro.codSucursal;
                        var sql0     = @"SELECT autoProducto, cnt
                                FROM venta_resumen 
                                where codSucursal=@codSuc";
                        var comando1 = new MySqlCommand(sql0, cn);
                        comando1.Parameters.Clear();
                        comando1.Parameters.Add(p0);
                        var rd = comando1.ExecuteReader();
                        while (rd.Read())
                        {
                            var nr = new DtoLibInventario.MonitorPos.Entidad.Ficha()
                            {
                                autoProducto = rd.GetString("autoProducto"),
                                cnt          = rd.GetDecimal("cnt"),
                            };
                            lst.Add(nr);
                        }
                        rd.Close();

                        tr = cn.BeginTransaction();
                        var sql1     = @"delete from venta_resumen where codSucursal=@codSuc";
                        var comando2 = new MySqlCommand(sql1, cn, tr);
                        var p1       = new MySql.Data.MySqlClient.MySqlParameter();
                        p1.ParameterName = "codSuc";
                        p1.Value         = filtro.codSucursal;
                        comando2.Parameters.Clear();
                        comando2.Parameters.Add(p1);
                        comando2.ExecuteNonQuery();
                        tr.Commit();
                    }
                    catch (Exception ex1)
                    {
                        tr.Rollback();
                        result.Mensaje = ex1.Message;
                        result.Result  = DtoLib.Enumerados.EnumResult.isError;
                    }

                    result.Lista = lst;
                };
            }
            catch (Exception e)
            {
                result.Mensaje = e.Message;
                result.Result  = DtoLib.Enumerados.EnumResult.isError;
            }

            return(result);
        }
Exemplo n.º 60
-1
        public bool AddBook(System.Data.IDbTransaction trans, System.Data.IDbConnection conn, MBook model)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" INSERT INTO Book(KeyID, Name, Author, Press, PressDateTime, IsRead)");
            sql.Append(" SELECT @KeyID, @Name, @Author, @Press, @PressDateTime, @IsRead");
            sql.Append(" FROM DUAL");
            sql.Append(" WHERE NOT EXISTS(");
            sql.Append(" SELECT *");
            sql.Append(" FROM Book");
            sql.Append(" WHERE Name = @Name");
            sql.Append(" AND Author = @Author");
            sql.Append(" AND Press = @Press");
            sql.Append(" )");
            MySqlParameter[] para = new MySqlParameter[]
            {
                new MySqlParameter("@KeyID", model.KeyID),
                new MySqlParameter("@Name", MySqlDbType.VarChar){ Value = model.Name },
                new MySqlParameter("@Author", model.Author),
                new MySqlParameter("@Press", model.Press),
                new MySqlParameter("@PressDateTime", model.PressDateTime),
                new MySqlParameter("@IsRead", model.IsRead)
            };

            return MysqlHelper.ExecuteSql(trans, conn, sql.ToString(), para) >= 1 ? true : false;
        }