none
这个“SQLCEHelper”类写的有什么问题吗??? RRS feed

  • 问题

  • 问题一:这个“SQLCEHelper”类写的有什么问题吗???   是不是有什么资源没有释放???      

    问题二: 跑在WinCE下是不是有什么需要优化的地方???

    public abstract class SqlCEHelper
        {
            public delegate void SqlErrorDescript(string strMsg);
            public static SqlErrorDescript sqlError;
            public static int ExecNonQuery(string connStr, string sqlStr)
            {
                int iRet;
                try
                {
                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                        conn.Open();
                        using (SqlCeCommand cmd = new SqlCeCommand(sqlStr, conn))
    问题三:外面有一个using了,这里的using是不是可以省略掉???
                        {
                            iRet = cmd.ExecuteNonQuery();
                        }
                        //conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    if (sqlError != null)
                    {
                        sqlError("执行SQL指令错误\tSql语句:" + sqlStr + "\t错误信息:" + ex.Message + "\r\n");
                    }
                    iRet = -1;
                }
                return iRet;
            }

            public static int ExecNonQueryList(string connStr, List<string> sqlList)
            {
                int iRet = 0;
                try
                {
                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                        conn.Open();
                        SqlCeCommand cmd = new SqlCeCommand();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        foreach (string sql in sqlList)
                        {
                            cmd.CommandText = sql;
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                if (sqlError != null)
                                {
                                    sqlError("执行SQL指令错误\tSql语句:" + sql + "\t错误信息:" + ex.Message + "\r\n");
                                }
                            }
                            iRet++;
                        }
                    }
                }
                catch (Exception ex)
                {
                    if (sqlError != null)
                    {
                        sqlError("执行SQL指令列表错误\t错误信息:" + ex.Message + "\r\n");
                    }
                    iRet = -1;
                }
                return iRet;
            }

            public static int ExecCommit(string connStr, List<string> sqlList)
            {
                int iNum = 0;
                try
                {
                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                  SqlCeTransaction Tran = null;
                        SqlCeCommand cmd = null;
                        conn.Open();
                        Tran = conn.BeginTransaction();
                        cmd = conn.CreateCommand();
                        cmd.CommandType = CommandType.Text;
    问题四:这里有必要用事务吗?  就是执行SQL序列?   事务是不是资源开销很大?
                    foreach (string s in sqlList)
                        {
                            try
                            {
                                cmd.CommandText = s;
                                cmd.ExecuteNonQuery();
                                iNum++;
                            }
                            catch (Exception ex)
                            {
                                if (sqlError != null)
                                {
                                    sqlError(ex.Message);
                                }
                            }
                        }
                        Tran.Commit(CommitMode.Immediate);
                    }
                }
                catch (Exception ex)
                {
                    if (sqlError != null)
                    {
                        sqlError("执行SQL指令事务\t错误信息:" + ex.Message + "\r\n");
                    }
                    return -1;
                }
                return iNum;
            }

            public static bool TableExists(string connStr, string strTable)
            {
                object obj = null;
                try
                {
                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                        conn.Open();
                        using (SqlCeCommand cmd = new SqlCeCommand("select * from information_schema.tables where table_name='" + strTable + "'", conn))
                        {
                            obj = cmd.ExecuteScalar();
                            if (obj == null)
                            {
                                return false;
                            }
                            else
                            {
                                return true;
                            }
                        }
                    }
                }
                catch
                {
                    return false;
                }
            }

            public static DataSet ExecReader(string connStr, string sqlStr)
            {
                DataSet ds = new DataSet();
                SqlCeDataAdapter sda;
                try
                {
                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                        conn.Open();
                        using (SqlCeCommand cmd = new SqlCeCommand(sqlStr, conn))
                        {
                            sda = new SqlCeDataAdapter(cmd);
                            sda.Fill(ds);
                        }
                        //conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    if (sqlError != null)
                    {
                        sqlError("执行读取命令错误\tSql语句:" + sqlStr + "\t错误信息:" + ex.Message + "\r\n");
                    }
                    return null;
                }
                return ds;
            }

            public static bool RepairDataBase(string connStr)
            {
                SqlCeEngine engine = new SqlCeEngine(connStr);

    问题五:这里需不需要用using???
                engine.Repair(null, RepairOption.RecoverCorruptedRows);


                return true;
            }

            public static bool CreateDataBase(string strDataBase)
            {
                if (File.Exists(strDataBase) == true)
                {
                    return false;
                }
                string strPath = strDataBase.Substring(0, strDataBase.LastIndexOf("\\"));
                if (Directory.Exists(strPath) == false)
                {
                    Directory.CreateDirectory(strPath);
                }
                if (Directory.Exists(strPath) == false)
                {
                    return false;
                }
                try
                {
                    SqlCeEngine engine = new SqlCeEngine("Data Source = " + strDataBase + ";password=tyeet;encrypt database=TRUE");
                    engine.CreateDatabase();
                }
                catch (Exception)
                {
                    return false;
                }

                return true;
            }
        }
    更多0

    Science and technology is my lover.

    2014年8月12日 12:35

答案

  • 你好:

    1. 我的建议是只要一个类继承了IDisposable接口提供了Dispose方法,最好都使用using关键字,防止有非托管资源没有及时释放。

    2. 如果执行很多SQL命令的话使用事务当然是必要的了,因为当其中某一个命令执行失败的时候可以进行回滚,防止数据出错。当然开销肯定也要比执行一般的SQL命令大,但我觉得数据的正确性和完整性是首要的,多出的这点开销也是必要的,何况一般执行少量的操作的情况下跟执行普通SQL命令其实区别不大。具体我觉得你可以参考一下这篇文章:

    http://kb.cnblogs.com/page/127650/


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    2014年8月13日 7:02
    版主