Ask C# everyone God, about SQLiteHelper increase delete to modify the static to directly call the way

Dhjjf

The class I use in the project is a SQLite database that helps static classes. It can be called directly without using instantiation.
Originally there are a lot of related classes on the Internet. After using a class, you can't call it.

private void TextBox2_TextChanged(object sender, EventArgs e) //新增插入数据
{
SQLiteCommand cmdInsert = new SQLiteCommand(mConn);
string sql = "INSERT INTO KD(postID,ddtime)values(@postID,@ddtime)";
SQLiteParameter[] cmdParms = new SQLiteParameter[]{
new SQLiteParameter("@postID", (textBox2.Text)),
new SQLiteParameter("@ddtime", DateTime.Now)
};
cmd.ExecuteNonQuery(sql,cmdParms);
}

The SQLiteHelper class source is as follows

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SQLiteQueryBrowser
{
/// /// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化
///
public static class SQLiteHelper
{
/// /// 数据库连接字符串
///
//public static string connectionString = "Data Source=" + Application.StartupPath + "\\" + System.Configuration.ConfigurationSettings.AppSettings["Contr"];
public static string connectionString = "Data Source=" + Application.StartupPath + "/KDDB.db";

    #region 执行数据库操作(新增、更新或删除)，返回影响行数
    /// /// 执行数据库操作(新增、更新或删除)
    /// 
    /// SqlCommand对象
    /// 所受影响的行数
    public static int ExecuteNonQuery(SQLiteCommand cmd)
    {
        int result = 0;
        if (connectionString == null || connectionString.Length == 0)
            throw new ArgumentNullException("connectionString");
        using (SQLiteConnection con = new SQLiteConnection(connectionString))
        {
            SQLiteTransaction trans = null;
            PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);
            try
            {
                result = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

BillWoodruff

I suggest you leave "God" out of this, and edit this post so that the issue you are having is clearly stated. Rather than dump a bunch of code, describe errors or unexpected results. Show where in the code the errors occur. Describe what you have done to isolate the errors or unexpected results.

«Where is the Life we have lost in living? Where is the wisdom we have lost in knowledge? Where is the knowledge we have lost in information?» T. S. Elliot

Lost User

Before you read my complaints at the bottom, it is quite good code; rather readable, and already using parameterized queries. If this were in a project, I would not change it. I do have some suggestions of course.

Dhjjf wrote:

It can be called directly without using instantiation.

That's mostly limiting the existing class. Being able to create multiple connections and commands gives an added flexibility.

private void TextBox2_TextChanged(object sender, EventArgs e) //新增插入数据
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
using (SQLiteCommand cmd = con.CreateCommand())// this way u need not set con. property
{
try
{
cmd.CommandText = "INSERT INTO KD(postID,ddtime)values(@postID,@ddtime)";
SQLiteParameter[] cmdParms = new SQLiteParameter[]{
new SQLiteParameter("@postID", (textBox2.Text)),
new SQLiteParameter("@ddtime", DateTime.Now)
};
int recordsAffected = cmd.ExecuteNonQuery(cmdParms);
Debug.Assert(recordsAffected != 1); //should only affect 1 record
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString()); // gives the complete message+stacktrace in debug window
throw; // don't throw ex, just throw
}
}
}

Also, your version would be harder to use than the OO-version; the original I can wrap in a decorator-pattern, extending its functionality without changing the original classes.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

OriginalGriff

Three things: 1) Your error description makes no sense: "After using a class, you can't call it." You don;t use any classes, you are just calling static methods of a static class. You need to explain in a lot more detail exactly what you are doing, and what happens as a result. 2) Don't store data files - including databases - in your app folder. It will work in development, but it can fail in production because the app is installed under the "Program Files" folder which is a target for virus activity and is thus often protected from changes. See here for better ideas: Where should I store my data?[^] 3) Please learn about string.IsNullOrWhitespace rather than doing "empty string" tests yourself. But we probably can't help you that much - this requires you app running along with your database (and probably user input) to work out what the problem might be and we have no access to any of those. So, it's going to be up to you. Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it, then a quick Google for "Visual Studio debugger" should give you the info you need. Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why. Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

Sent from my Amstrad PC 1640 Never throw anything away, Griff Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!

realJSOP

This is the way I do it. This class uses ADO.Net to access the database. It can populate any specified model, as long as the property names/types in the specified model match what is returned in the dataset. It uses reflect to perform this process. By default, it will NOT throw an exception if the data returrned from the dataset cannot be matched up to the model, but that can be enabled as needed. I have several dusiness layer objects that inherit this code that all hit different databases. Ninety-nine percent of our database access is performed via stored procs, but there is support in this object for using parameterized queries as well. Catching exceptions is the responsibility of the method that calls the BLL methods. I don't recall if this code relies on any other code I've written, and if it does, it's nothing more than extension methods. I can provide those if they're needed. The class is reasonably well commented, so you shouldn't have many/any problems understanding what I've done, and adapting it to sqllight shouldn't be a problem.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using ObjectExtensions;

namespace DataModels
{
public partial class DBObject
{
/// /// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should
/// throw an exception if the DataTable retrieved by the query does not match the model
/// being created (it compares the number of datatable columns with the number of assigned
/// values in the model). The default falue is false.
///
public bool FailOnMismatch { get; set; }
public int TimeoutSecs { get; set; }
public string ConnectionString { get; set; }

    public DBObject(string connStr)
    {
        this.ConnectionString = connStr;
        // five minutes should be enough, right?
        this.TimeoutSecs      = 300;
        this.FailOnMismatch   = false;
    }

    /// /// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
    /// 
    /// The storedproc or query to execute
    /// The parameters to use in the storedproc/query
    /// 
    protected DataTab

Lost User

#realJSOP wrote:

This is the way I do it

Not programming against the IDb interfaces? You can have the DbProvider create the appropriate connection, use the CreateCommand-factorymethod to create a command for that specific connection. It'd work the same, but suddenly for every possible DB-provider. Still, very nice code, and very happy that you return the amount of records affected. Checking how many records have been affected (versus expected) is a simple way of validating.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

realJSOP

We only use Sql Server, so there's no need to use the IDB stuff. I don't know if there's tangible reazliation of performance improvements, but hey, this is the way the boss wanted to do it. I made it generic so we could present a reasonable alternative to using EF (which nobody in our shop likes - at all). It's light, and as fast as you can expect ADO to be despite our use of reflection. Most of the BLL methods in the inheriting classes are no more than two lines of code, and even most of those can be reduced to one. All of our models that save to the db have a property that returns a SqlParameter[] array, so all we have to do is pass in (to the BLL class) the object we want to save, and the method uses a ToSqlParameters property in the call to the base DAL class. When in motion, it's a thing of beauty. :)

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Lost User

Sounds reasonable :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Richard Deeming

As far as I can see, the only call to ConvertFromDBValue<T> will always use object as the type parameter. I don't think there's any benefit to making the method generic. :)

protected static object ConvertFromDBValue(object obj, object defaultValue)
{
object result = (obj == null || obj == DBNull.Value) ? defaultValue : obj;
return result;
}

I could be wrong, because you've used var for the defaultValue variable, and haven't included the GetDefaultValue extension method. But I can't see how that method could possibly be declared as returning anything other than object. Also, if you're targeting .NET 4.0 or later, you can just use null as the default value. According to the remarks of the PropertyInfo.SetValue method:

PropertyInfo.SetValue Method (System.Reflection) | Microsoft Docs[^]:

If this PropertyInfo object is a value type and value is null, then the property will be set to the default value for that type.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

realJSOP

I always do things the long way around. :)

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013