求SQLITEHELPER ExecuteNonQuery 的类！

[zxvyio]

下面的代码是完整的，想改成ExecuteNonQuery(“sqlstr”)这样方便直接调用！但是遇到一个问题！不知道怎么传 和 取 @id_value, @image_value，这些的 值！

            using (SQLiteConnection conn = new SQLiteConnection("Data Source=t.db3"))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(
                    "INSERT INTO IMGS (ID , IMAGE) VALUES ( @id_value, @image_value)", conn))
                {
                    using (FileStream fileStream = File.Open("D://1.mp4", FileMode.Open))
                    {
                            
                        byte[] buffer = new byte[fileStream.Length];
                        fileStream.Read(buffer, 0, buffer.Length);
                        cmd.Parameters.Add(new SQLiteParameter("id_value", 6));
                        cmd.Parameters.Add(new SQLiteParameter("image_value", buffer.ToArray()));
                        cmd.ExecuteNonQuery();
                    }
                }
            }

下面是SQLITE——HELPER的代码，请帮忙改成能传！上面那些值的，谢谢！

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public int Sql_Execute(string Sqlstr)
        {
            int tempint = 0;
            while (!ConnOpen())
                Thread.Sleep(100);
            SQLiteCommand command = new SQLiteCommand(Sqlstr, conn);
            try
            {
                tempint = command.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                command.Dispose();
            }
            return tempint;
        }

[江南小鱼]

给你一个例子，照着改吧

/// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="cmdText">a</param>
        /// <param name="commandParameters">传入的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText, params object[] p)
        {
            SQLiteCommand command = new SQLiteCommand();
            using (SQLiteConnection connection = new SQLiteConnection(_DBConnectionString))
            {
                PrepareCommand(command, connection, cmdText, p);
                return command.ExecuteNonQuery();
            }
        }

private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            if (p != null)
            {
                foreach (object parm in p)
                    cmd.Parameters.AddWithValue(string.Empty, parm);
                //for (int i = 0; i < p.Length; i++)
                // cmd.Parameters[i].Value = p[i];
            }
        }

[zxvyio]

网上搜了，自己改成下面这样，代码更近一步了，但是提示错误，不知哪出错了，请帮忙看下！

            using (FileStream fileStream = File.Open("D://1.mp4", FileMode.Open))
            {
                byte[] buffer = new byte[fileStream.Length];
                fileStream.Read(buffer, 0, buffer.Length);

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@id_value", SqlDbType.Int);
                par[1] = new SqlParameter("@image_value", SqlDbType.Image);
                par[0].Value = 7;
                par[1].Value = buffer.ToArray();
                SQLhp.Sql_Execute_Par("INSERT INTO IMGS (ID , IMAGE) VALUES (" + par + ")");
            }


        /// <summary>
        /// 执行sql语句带参数
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public int Sql_Execute_Par(string Sqlstr, params object[] par)
        {
            int tempint = 0;
            while (!ConnOpen())
                Thread.Sleep(100);
            SQLiteCommand command = new SQLiteCommand(Sqlstr, conn);
            try
            {
                foreach (string i in par)
                {
                    command.Parameters.Add(new SQLiteParameter(i));
                }

                tempint = command.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                command.Dispose();
            }
            return tempint;
        }

[zxvyio]

看了下，好像是这里出错，但是又不知怎么改，求帮忙！

                foreach (string i in par)
                {
                    command.Parameters.Add(new SQLiteParameter(i));
                }
// 类型string 不知要改成什么？
                foreach (string i in par)
                {
                    command.Parameters.Add(i);
                }

[zxvyio]

引用 3 楼 lovelj2012 的回复:

给你一个例子，照着改吧

这代码对我来说太复杂了，看不懂，自己在网上又搜了一下！终于搞定了！分享下代码！吐糟一下，其中的SQLiteParameter 和 SQLParameter 几字之差，费了我老半天功夫！最后认真查看才发现原来差ite三个字！

        private void button1_Click(object sender, EventArgs e)
        {
            using (FileStream fileStream = File.Open("D://1.mp4", FileMode.Open))
            {
                byte[] buffer = new byte[fileStream.Length];
                fileStream.Read(buffer, 0, buffer.Length);
                SQLiteParameter[] parms = new SQLiteParameter[]
                {
                    new SQLiteParameter("@id_value" ,10), 
                    new SQLiteParameter("@image_value",buffer.ToArray())
                };
                SQLhp.Sql_Execute_Par("INSERT INTO IMGS (ID , IMAGE) VALUES (@id_value , @image_value)", parms);
            }
        }

        /// <summary>
        /// 执行sql语句带参数
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public int Sql_Execute_Par(string Sqlstr, SQLiteParameter[] parms)
        {
            int tempint = 0;
            while (!ConnOpen())
                Thread.Sleep(100);
            SQLiteCommand command = new SQLiteCommand(Sqlstr, conn);
            try
            {
                command.Parameters.AddRange(parms);
                tempint = command.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                command.Dispose();
            }
            return tempint;
        }

[zxvyio]

引用 3 楼 lovelj2012 的回复:

给你一个例子，照着改吧

还是要谢谢你热心帮忙，好吧，结贴！