示例#1
0
        protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            if (_s == DBStatus.Close)
            {
                DoOpen();
            }

            string orderby4page = orderby;

            if (orderby == null || orderby == "")
            {
                orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
            }
            if (sql.Trim().EndsWith(";"))
            {
                sql = sql.Trim().Substring(0, sql.Trim().Length - 1);
            }

            string newsql = @"select * from "
                            + "(select table2.*,ROW_NUMBER() OVER (order by " + orderby4page + " ) as RowNumber from (" + sql + ") table2) a "
                            + " where RowNumber > " + (count_of_page * (toPage - 1)) + " and RowNumber <=" + (count_of_page * toPage);

            DataSetStd dss = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
示例#2
0
        protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            string orderby4page = orderby;

            if (orderby == null || orderby == "")
            {
                orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
            }

            string     newsql = @"select *,NEWID() as _page_row_guid into #tmptt from(
                    " + sql + @")a

                    SELECT TOP " + count_of_page + @" *
                    FROM #tmptt
                    WHERE _page_row_guid NOT IN
                              (
                              SELECT TOP " + (count_of_page * (toPage - 1)) + @"  _page_row_guid FROM #tmptt ORDER BY " + orderby4page + @"
                              )
                    ORDER BY " + orderby4page + @"

                    drop table #tmptt";
            DataSetStd dss    = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
示例#3
0
        protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            string orderby4page = orderby;

            if (orderby == null || orderby == "")
            {
                orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
            }

            string newsql = @"WITH [__test] AS "
                            + "(select *,ROW_NUMBER() OVER (order by " + orderby4page + " ) as RowNumber from (" + sql + ") table2)"
                            + "SELECT TOP " + count_of_page + " * "
                            + "FROM [__test] "
                            + "WHERE (RowNumber > "
                            + "(SELECT ISNULL(max(RowNumber),0) "
                            + "FROM (SELECT TOP (" + (count_of_page * (toPage - 1)) + ") RowNumber "
                            + "FROM [__test] "
                            + "ORDER BY RowNumber) AS T)) ";
            DataSetStd dss = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
        public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
        {
            DBDataCollection rtn = new DBDataCollection();

            rtn.IsSuccess = false;

            DataSetStd    ds = new DataSetStd();
            NpgsqlCommand dc = null;//new SqlCommand(p.StoreProcureName, this.sqlconn);

            if (this._s == DBStatus.Begin_Trans)
            {
                dc = new NpgsqlCommand(sp_name, this.conn, this.tran);
            }
            else
            {
                dc = new NpgsqlCommand(sp_name, this.conn);
            }
            //dc.CommandTimeout = 90;
            dc.CommandType    = CommandType.StoredProcedure;
            dc.CommandTimeout = CommandTimeOut;
            FillParametersToCommand(dc, dbp);
            NpgsqlDataReader ddr = null;

            try
            {
                if (isReturnDataSet)
                {
                    ddr = dc.ExecuteReader();
                    ds  = DataSetStd.FillData(ddr);
                    rtn.ReturnDataSet = ds;
                }
                else
                {
                    dc.ExecuteNonQuery();
                }
                //獲取返回值
                foreach (SqlParameter sp in dc.Parameters)
                {
                    if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
                    {
                        rtn.SetValue(sp.ParameterName.Replace(ParameterFlagChar, ""), sp.Value);
                    }
                }

                rtn.IsSuccess = true;
            }
            finally
            {
                if (ddr != null)
                {
                    ddr.Close();
                    ddr.Dispose();
                }
                dc.Dispose();
                dc = null;
            }

            return(rtn);
        }
示例#5
0
        public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
        {
            if (_s == DBStatus.Close)
            {
                DoOpen();
            }

            OracleCommand cmd;
            DataSetStd    ds     = new DataSetStd();
            var           sqlarr = ToSQLArray(sql);

            foreach (var s in sqlarr)
            {
                if (s.Trim() == "")
                {
                    continue;
                }
                using (cmd = new OracleCommand(s, conn))
                {
                    if (cmd.Connection.State == ConnectionState.Closed)
                    {
                        cmd.Connection.Open();
                    }
                    try
                    {
                        //如果事務開啟,則使用事務的方式
                        if (this._s == DBStatus.Begin_Trans)
                        {
                            cmd.Transaction = this.tran;
                        }

                        cmd.CommandText = s;
                        //如果有參數
                        if (dbp != null)
                        {
                            FillParametersToCommand(cmd, dbp, s);
                        }
                        if (_s == DBStatus.Begin_Trans)
                        {
                            cmd.Transaction = this.tran;
                        }

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        cmd = null;
                    }
                }
            }
        }
示例#6
0
        public override Data.DataSetStd Query(string sql, DBOParameterCollection dbp)
        {
            if (_s == DBStatus.Close)
            {
                DoOpen();
            }

            OracleCommand    cmd;
            DataSetStd       ds  = new DataSetStd();
            OracleDataReader ddr = null;
            var sqlarr           = ToSQLArray(sql);
            var index            = 1;

            foreach (string s in sqlarr)
            {
                if (s.Trim() == "")
                {
                    continue;
                }
                using (cmd = new OracleCommand(s, conn))
                {
                    try
                    {
                        //如果事務開啟,則使用事務的方式
                        if (this._s == DBStatus.Begin_Trans)
                        {
                            cmd.Transaction = this.tran;
                        }

                        //如果有參數
                        if (dbp != null)
                        {
                            FillParametersToCommand(cmd, dbp);
                        }

                        if (s.Trim().ToLower().StartsWith("select") || s.ToLower().IndexOf(" into ") < 0)
                        {
                            ddr = cmd.ExecuteReader();
                            ds.Tables.AddRange(DataSetStd.FillData(ddr).Tables);
                            index++;
                        }
                        else
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                    finally
                    {
                        cmd.Dispose();
                        cmd = null;
                    }
                }
            }

            return(ds);
        }
示例#7
0
        public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
        {
            DBDataCollection rtn = new DBDataCollection();

            rtn.IsSuccess = false;
            if (_s == DBStatus.Close)
            {
                DoOpen();
            }

            DataSetStd    ds = new DataSetStd();
            OracleCommand dc = null;

            if (this._s == DBStatus.Begin_Trans)
            {
                dc             = new OracleCommand(sp_name, conn);
                dc.Transaction = tran;
            }
            else
            {
                dc = new OracleCommand(sp_name, conn);
            }
            dc.CommandType = CommandType.StoredProcedure;
            FillParametersToCommand(dc, dbp);
            try
            {
                if (isReturnDataSet)
                {
                    OracleDataAdapter sqlDa = new OracleDataAdapter();
                    sqlDa.SelectCommand = dc;
                    sqlDa.Fill(ds);
                    rtn.ReturnDataSet = ds;
                }
                else
                {
                    dc.ExecuteNonQuery();
                }
                //獲取返回值
                foreach (OracleParameter sp in dc.Parameters)
                {
                    if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
                    {
                        rtn.SetValue(sp.ParameterName.Replace(":", ""), sp.Value);
                    }
                }

                rtn.IsSuccess = true;
            }
            finally
            {
                dc.Cancel();
                dc = null;
            }

            return(rtn);
        }
        public override DataSetStd Query(string sql, DBOParameterCollection dbp)
        {
            var newsql = ConvertSQL(sql);

            if (sqlcomm == null)
            {
                sqlcomm = new NpgsqlCommand(newsql, this.conn);
                sqlcomm.CommandTimeout = CommandTimeOut;
            }
            else
            {
                sqlcomm.CommandText = newsql;
            }

            //如果事務開啟,則使用事務的方式
            if (this._s == DBStatus.Begin_Trans)
            {
                sqlcomm.Transaction = this.tran;
            }
            DataSetStd       ds  = new DataSetStd();
            NpgsqlDataReader ddr = null;

            try
            {
                //如果有參數
                if (dbp != null)
                {
                    FillParametersToCommand(sqlcomm, dbp);
                }

                ddr = sqlcomm.ExecuteReader();

                ds = DataSetStd.FillData(ddr);
            }
            //catch (Exception ex)
            //{
            //    Console.WriteLine(ex.Message);
            //    throw ex;
            //}
            finally
            {
                if (ddr != null)
                {
                    ddr.Close();
                    ddr.Dispose();
                }
                sqlcomm.Dispose();
                sqlcomm = null;
            }
            return(ds);
        }
示例#9
0
        public override DataSetStd Query(string sql, DBOParameterCollection dbp)
        {
            if (sqlcomm == null)
            {
                sqlcomm = new SqlCommand(sql, this.sqlconn);
                sqlcomm.CommandTimeout = 90;
            }
            else
            {
                sqlcomm.CommandText = sql;
            }
            //如果事務開啟,則使用事務的方式
            if (this._s == DBStatus.Begin_Trans)
            {
                sqlcomm.Transaction = this.trans;
            }


            DataSetStd    ds  = new DataSetStd();
            SqlDataReader ddr = null;

            try
            {
                //如果有參數
                if (dbp != null)
                {
                    FillParametersToCommand(sqlcomm, dbp);
                }
                ddr = sqlcomm.ExecuteReader();

                ds = DataSetStd.FillData(ddr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (ddr != null)
                {
                    ddr.Close();
                    ddr.Dispose();
                }

                sqlcomm.Cancel();
                sqlcomm = null;
            }
            return(ds);
        }
示例#10
0
        /// <summary>
        /// 通過sql獲得相關的欄位列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        private string[] GetColumnsNameBySql(string sql, DBOParameterCollection p)
        {
            if (sql != sql4PageOrder || columnsName4PageOrder == null)
            {
                sql4PageOrder = sql;

                DataSetStd dss = Query(sql, p);
                if (dss != null)
                {
                    columnsName4PageOrder = dss[0].ColumnNames;
                }
            }

            return(columnsName4PageOrder);
        }
示例#11
0
        /// <summary>
        /// 設定翻頁條件,啟動翻頁機制
        /// </summary>
        /// <param name="p"></param>
        public void StartPageByCondition(DBAPageP p)
        {
            this._Count_of_OnePage           = 0;
            this._current_page               = 0;
            this._source_sql_page            = "";
            this._source_sql_parameters_page = null;
            this._to_Page            = 0;
            this._total_page         = 0;
            this._is_page            = false;
            this._rownumber_order_by = "";
            this._row_count          = 0;

            this._Count_of_OnePage           = p.Count_of_OnePage;
            this._source_sql_page            = p.SQL;
            this._source_sql_parameters_page = p.SQL_Parameters;
            if (!string.IsNullOrEmpty(p.OrderBy))
            {
                this._rownumber_order_by = p.OrderBy;
            }
            this._is_page = true;
            if (p.CurrentPage >= 0)
            {
                this._current_page = p.CurrentPage;
            }

            if (this._source_sql_page.Trim().EndsWith(";"))
            {
                this._source_sql_page = this._source_sql_page.Trim().Substring(0, this._source_sql_page.Trim().Length - 1);
            }
            string sql = "select count(1) from (" + this._source_sql_page + ") t";
            DBOParameterCollection SQL_Parameters = this._source_sql_parameters_page;
            DataSetStd             dss            = Query(sql, SQL_Parameters);
            IntStd totalcount = IntStd.ParseStd(dss.GetValue(0, 0));

            if (totalcount != null && totalcount != 0)
            {
                _row_count = totalcount;

                if (totalcount.Value % this._Count_of_OnePage == 0)
                {
                    this._total_page = (int)(totalcount.Value / this._Count_of_OnePage);
                }
                else
                {
                    this._total_page = (int)(totalcount.Value / this._Count_of_OnePage) + 1;
                }
            }
        }
示例#12
0
        public DataSetStd QueryWithKey(string sql, DBOParameterCollection dbp)
        {
            if (sqlcomm == null)
            {
                sqlcomm = new SqlCommand(sql, this.sqlconn);
                sqlcomm.CommandTimeout = 90;
            }
            else
            {
                sqlcomm.CommandText = sql;
            }
            //如果事務開啟,則使用事務的方式
            if (this._s == DBStatus.Begin_Trans)
            {
                sqlcomm.Transaction = this.trans;
            }


            DataSetStd ds = new DataSetStd();

            try
            {
                //如果有參數
                if (dbp != null)
                {
                    FillParametersToCommand(sqlcomm, dbp);
                }
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(this.sqlcomm);
                sqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlcomm.Cancel();
                sqlcomm = null;
            }
            return(ds);
        }
        protected override DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            var newsql = sql;

            if (orderby != null && orderby != "")
            {
                newsql += " order by " + orderby;
            }

            newsql = string.Format(newsql + " limit {0} offset {1}", count_of_page, (startRow - 1));
            DataSetStd dss = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
示例#14
0
        public override void ExecuteNoQuery(string sql, DBOParameterCollection dbp)
        {
            DB2Command cmd;
            DataSetStd ds = new DataSetStd();

            using (cmd = new DB2Command(sql, conn))
            {
                try
                {
                    //如果事務開啟,則使用事務的方式
                    if (this._s == DBStatus.Begin_Trans)
                    {
                        cmd.Transaction = this.tran;
                    }

                    cmd.CommandText = sql;
                    //如果有參數
                    if (dbp != null)
                    {
                        FillParametersToCommand(cmd, dbp);
                    }
                    if (_s == DBStatus.Begin_Trans)
                    {
                        cmd.Transaction = this.tran;
                    }

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Cancel();
                    cmd = null;
                }
            }
        }
示例#15
0
        protected override Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            var newsql = sql;

            if (orderby != null && orderby != "")
            {
                newsql += " order by " + orderby;
            }

            newsql = string.Format(newsql + " limit {0} offset {1}", count_of_page, startRow - 1);
            //GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"MySql QueryByPage Sql={newsql}");
            DataSetStd dss = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
示例#16
0
        protected override Net.Base.Data.DataTableStd QueryByPage(int startRow, int endRow, int toPage, int count_of_page, string sql, string orderby, DBOParameterCollection p)
        {
            string orderby4page = orderby;

            if (orderby == null || orderby == "")
            {
                orderby4page = GetColumnsNameBySql(sql, p)[0] + " ASC ";
            }

            string newsql = @"
select * from (" + sql + @") order by " + orderby4page + @" limit " + count_of_page + " offset " + (count_of_page * (toPage - 1));

            DataSetStd dss = Query(newsql, p);

            if (dss == null)
            {
                return(null);
            }
            else
            {
                return(dss[0]);
            }
        }
示例#17
0
        public override Data.DataSetStd Query(string sql, DBOParameterCollection dbp)
        {
            DB2Command cmd;
            DataSetStd ds = new DataSetStd();

            using (cmd = new DB2Command(sql, conn))
            {
                try
                {
                    //如果事務開啟,則使用事務的方式
                    if (this._s == DBStatus.Begin_Trans)
                    {
                        cmd.Transaction = this.tran;
                    }

                    DB2DataAdapter rd = new DB2DataAdapter(cmd);
                    //如果有參數
                    if (dbp != null)
                    {
                        FillParametersToCommand(cmd, dbp);
                    }

                    rd.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Cancel();
                    cmd = null;
                }
            }

            return(ds);
        }
示例#18
0
        /// <summary>
        /// 根据数据集批量删除对应table中的数据
        /// </summary>
        /// <param name="data"></param>
        /// <param name="toTable"></param>
        public override void Delete(DataTable data, string toTable)
        {
            if (data == null)
            {
                return;
            }
            string[] keycols = DataTableStd.GetPKName(data);
            string   sql     = "delete from " + toTable;

            string where = "";
            if (keycols.Length <= 0)
            {
                keycols = DataTableStd.GetColumnName(data);
            }

            foreach (string key in keycols)
            {
                if (where == "")
                {
                    where += " where " + key + "=@" + key;
                }
                else
                {
                    where += " and " + key + "=@" + key;
                }
            }

            sql = sql + where;

            SqlCommand tsqlcomm = new SqlCommand("select * from " + toTable + " where 1=0 ", this.sqlconn);

            tsqlcomm.CommandTimeout = 90;


            if (this._s == DBStatus.Begin_Trans)
            {
                tsqlcomm.Transaction = this.trans;
            }

            SqlDataAdapter sda = new SqlDataAdapter(tsqlcomm);
            DataSetStd     ds  = new DataSetStd();

            sda.FillSchema(ds, SchemaType.Source);
            tsqlcomm.CommandText = "SET NOCOUNT ON " + sql;
            sda.DeleteCommand    = tsqlcomm;
            sda.DeleteCommand.Parameters.Clear();
            foreach (string key in keycols)
            {
                DataColumn dc = data.Columns[key];
                sda.DeleteCommand.Parameters.Add("@" + key, ConvertBy(dc.DataType), dc.MaxLength, key);
            }
            sda.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
            sda.UpdateBatchSize = 0;
            SqlCommandBuilder scb = new SqlCommandBuilder();

            scb.ConflictOption = ConflictOption.OverwriteChanges;
            scb.SetAllValues   = false;
            scb.DataAdapter    = sda;

            DataTableStd dtsdata = DataTableStd.ParseStd(data);

            try
            {
                for (int count = 0; count < dtsdata.RowLength; count++)
                {
                    foreach (string colname in ds[0].ColumnNames)
                    {
                        ds[0].SetNewRowValue(dtsdata[count, colname], colname);
                    }
                    ds[0].AddNewRow();
                    if (((count + 1) % 200 == 0) || ((count + 1) == dtsdata.RowLength))
                    {
                        ds.AcceptChanges();

                        for (int i = 0; i < 200; i++)
                        {
                            if (i >= ds[0].Value.Rows.Count)
                            {
                                break;
                            }

                            ds[0].Value.Rows[i].BeginEdit();
                            ds[0].Value.Rows[i].Delete();
                            ds[0].Value.Rows[i].EndEdit();
                        }
                        sda.Update(ds[0].Value);
                        ds[0].ClearData();
                    }
                }
            }
            finally
            {
                tsqlcomm.Cancel();
                tsqlcomm.Dispose();
                sda.Dispose();
                ds.Dispose();
            }
        }