Пример #1
0
        public static int ExecSql(string connName, string sql, string param, ref string msg)
        {
            msg = string.Empty;
            int rows = 0;

            try
            {
                DbConnection conn = DbConnManager.GetDbConnection(connName);
                DbCommand    cmd  = conn.CreateCommand();
                try
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception e)
                    {
                        throw new MyDbException(e.Message, true);
                    }
                    DbTransaction dbTrans = conn.BeginTransaction();
                    try
                    {
                        cmd.Transaction = dbTrans;
                        cmd.CommandText = sql;
                        AddParamToCommand(cmd, param);
                        rows = cmd.ExecuteNonQuery();
                        dbTrans.Commit();
                    }
                    catch (Exception e)
                    {
                        dbTrans.Rollback();
                        if (e is MyDbException)
                        {
                            throw e;
                        }
                        else
                        {
                            throw new MyDbException(e.Message, cmd.CommandText);
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }
            return(rows);
        }
Пример #2
0
        public static DataTable GetDataToTable(string connName, string sql, string param, ref string msg)
        {
            msg = string.Empty;
            DataTable dataTable = new DataTable();

            try
            {
                DbConnection  conn    = DbConnManager.GetDbConnection(connName);
                DbDataAdapter adapter = DbConnManager.CreateDbDataAdapter(connName);
                DbCommand     cmd     = conn.CreateCommand();
                try
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception e)
                    {
                        throw new MyDbException(e.Message, true);
                    }
                    try
                    {
                        adapter.SelectCommand = cmd;
                        cmd.CommandText       = sql;
                        AddParamToCommand(cmd, param);
                        adapter.Fill(dataTable);
                    }
                    catch (Exception e)
                    {
                        if (e is MyDbException)
                        {
                            throw e;
                        }
                        else
                        {
                            throw new MyDbException(e.Message, cmd.CommandText);
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }
            return(dataTable);
        }
Пример #3
0
        public static int GetSeq(string connName, int dbId, String tableName, int step = 1)
        {
            DbConnection  conn = DbConnManager.GetDbConnection(connName);
            DbCommand     cmd  = conn.CreateCommand();
            StringBuilder sql  = new StringBuilder();
            int           seq  = step;

            try
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw new MyDbException(e.Message, true);
                }

                DbTransaction dbTrans = conn.BeginTransaction();
                try
                {
                    cmd.Transaction = dbTrans;
                    sql.Append("update BHZT set REC_NUM = REC_NUM+").Append(step).Append(" where TBLNAME = '").Append(tableName).Append("'");
                    cmd.CommandText = sql.ToString();
                    //if (DbConnManager.CheckNoRecordsAffectedAfterExecSql(connName, cmd.ExecuteNonQuery()))
                    if (cmd.ExecuteNonQuery() == 0)
                    {
                        sql.Length = 0;
                        sql.Append("insert into BHZT (TBLNAME,REC_NUM) values ('").Append(tableName).Append("', ").Append(step).Append(")");
                        cmd.CommandText = sql.ToString();
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        sql.Length = 0;
                        sql.Append("select REC_NUM from BHZT where TBLNAME = '").Append(tableName).Append("'");
                        cmd.CommandText = sql.ToString();
                        DbDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            seq = DbUtils.GetInt(reader, 0);
                        }
                        reader.Close();
                    }
                    dbTrans.Commit();
                }
                catch (Exception e)
                {
                    dbTrans.Rollback();
                    if (e is MyDbException)
                    {
                        throw e;
                    }
                    else
                    {
                        throw new MyDbException(e.Message, cmd.CommandText);
                    }
                }
            }
            finally
            {
                conn.Close();
            }
            return(dbId * 100000000 + seq);
        }
Пример #4
0
        public static int ExecSqlList(string connName, ArrayList sqlList, ref string msg)
        {
            msg = string.Empty;
            int rows = 0;

            try
            {
                DbConnection conn = DbConnManager.GetDbConnection(connName);
                DbCommand    cmd  = conn.CreateCommand();
                try
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception e)
                    {
                        throw new MyDbException(e.Message, true);
                    }
                    DbTransaction dbTrans = conn.BeginTransaction();
                    try
                    {
                        cmd.Transaction = dbTrans;

                        foreach (string sql in sqlList)
                        {
                            string[] s = sql.Split('~');
                            if (s.Length > 0)
                            {
                                cmd.CommandText = s[0];
                                if (s.Length > 1)
                                {
                                    AddParamToCommand(cmd, s[1]);
                                }
                                rows += cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                        }
                        dbTrans.Commit();
                    }
                    catch (Exception e)
                    {
                        dbTrans.Rollback();
                        if (e is MyDbException)
                        {
                            throw e;
                        }
                        else
                        {
                            throw new MyDbException(e.Message, cmd.CommandText);
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }
            return(rows);
        }