示例#1
0
        public virtual System.Data.DataSet GetDataSet(System.Data.IDbCommand cmd)
        {
            string datasetName = "NewDataSet";

            System.Data.DataSet ds = new System.Data.DataSet(datasetName);

            using (System.Data.IDbConnection idbc = this.Connection)
            {
                try
                {
                    cmd.Connection = idbc;

                    using (System.Data.Common.DbDataAdapter daQueryTable = this.m_factory.CreateDataAdapter())
                    {
                        daQueryTable.SelectCommand = (System.Data.Common.DbCommand)cmd;
                        daQueryTable.Fill(ds);
                    } // End Using daQueryTable
                }     // End Try
                catch (System.Data.Common.DbException ex)
                {
                    //if (Log("cDAL.GetDataTable(System.Data.IDbCommand cmd)", ex, cmd.CommandText))
                    throw;
                }// End Catch
                finally
                {
                    if (idbc.State != System.Data.ConnectionState.Closed)
                    {
                        idbc.Close();
                    }
                } // End Finally
            }     // End Using idbc

            return(ds);
        } // End Function GetDataSet
示例#2
0
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pSelectSQL">Select SQL</param>
        /// <param name="pDataReader">Data Reader</param>
        /// <returns>bool</returns>
        public static bool InsertData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
        {
            bool result = false;

            System.Data.Common.DbConnection     Conn    = null;
            System.Data.Common.DbDataAdapter    Adapter = null;
            System.Data.Common.DbCommandBuilder builder = null;
            System.Data.Common.DbCommand        cmd     = null;
            DataSet   dataSet = new DataSet();
            DataTable Temp    = new DataTable();

            string connString = ParseConnectionString(pFileString);

            if (DataSource != null)
            {
                IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
                driver.ConnectionString = connString;
                Conn    = (System.Data.Common.DbConnection)driver.GetConnection();
                Adapter = driver.GetDbAdapter(pSelectSQL);
                Adapter.FillSchema(dataSet, SchemaType.Source);
                Adapter.Fill(Temp);
                builder             = driver.GetDbCommandBuilder(Adapter);
                builder.QuotePrefix = "[";
                builder.QuoteSuffix = "]";
                try
                {
                    Conn.Open();

                    cmd = builder.GetInsertCommand(true);
                    cmd.CommandTimeout = 1500;
                    cmd.Connection     = Conn;
                    //builder.GetInsertCommand((true);

                    while (pDataReader.Read())
                    {
                        foreach (System.Data.Common.DbParameter param in cmd.Parameters)
                        {
                            //string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
                            string FieldName = param.SourceColumn;
                            if (FieldName.ToUpperInvariant() == "UNIQUEKEY")
                            {
                                param.Value = null;
                            }
                            else
                            {
                                param.Value = pDataReader[FieldName];
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    Conn.Close();
                }
            }

            result = true;
            return(result);
        }
示例#3
0
 /// <summary>
 /// 根据 sql 语句,查询默认数据库,返回一个 DataTable
 /// </summary>
 /// <param name="sql"></param>
 /// <returns></returns>
 public static DataTable RunTable(String sql)
 {
     using (IDbConnection cn = db.getConnection()) {
         DataTable dataTable = new DataTable();
         System.Data.Common.DbDataAdapter adapter = DataFactory.GetAdapter(sql, cn);
         adapter.Fill(dataTable);
         return(dataTable);
     }
 }
        public override DataTable SelectBySql(string strSql)
        {
            DataTable datatable = new DataTable();

            System.Data.Common.DbDataAdapter oraAdapter = this.CreateDbDataAdapter() as System.Data.Common.DbDataAdapter;;
            System.Data.IDbCommand           command    = this.CreateDbCommand(strSql);
            (oraAdapter as IDbDataAdapter).SelectCommand = command;
            oraAdapter.Fill(datatable);
            return(datatable);
        }
示例#5
0
        public static System.Data.DataTable GetDataTable(string SQL)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            using (System.Data.Common.DbDataAdapter da = GetAdapter(SQL))
            {
                da.Fill(dt);
            }

            return(dt);
        }
示例#6
0
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pSelectSQL">Select Statement</param>
        /// <param name="pDataRows">DataRows</param>
        /// <returns>bool</returns>
        public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
        {
            bool result = false;

            System.Data.Common.DbConnection     Conn    = null;
            System.Data.Common.DbDataAdapter    Adapter = null;
            System.Data.Common.DbCommandBuilder builder = null;
            System.Data.Common.DbCommand        cmd     = null;
            DataSet   dataSet = new DataSet();
            DataTable Temp    = new DataTable();

            string connString = ParseConnectionString(pFileString);

            if (DataSource != null)
            {
                IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
                driver.ConnectionString = connString;
                Conn    = (System.Data.Common.DbConnection)driver.GetConnection();
                Adapter = driver.GetDbAdapter(pSelectSQL);
                Adapter.FillSchema(dataSet, SchemaType.Source);
                Adapter.Fill(Temp);
                builder             = driver.GetDbCommandBuilder(Adapter);
                builder.QuotePrefix = "[";
                builder.QuoteSuffix = "]";

                try
                {
                    Conn.Open();

                    //cmd = builder.GetInsertCommand(true);
                    cmd = Conn.CreateCommand();
                    cmd.CommandTimeout = 1500;

                    //builder.GetInsertCommand((true);

                    foreach (DataRow R in pDataRows)
                    {
                        //string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
                        string SQL = GetInsertSQL(R.Table.TableName, R);
                        cmd.CommandText = SQL;
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    Conn.Close();
                }
            }

            result = true;
            return(result);
        }
示例#7
0
        /// <summary>
        /// 填充 DataSet
        /// </summary>
        /// <param name="ds">目标 DataSet</param>
        /// <returns></returns>
        public int Fill(System.Data.DataSet ds)
        {
            int nRtn = 0;

            Common.DbConnectionHelper        ConnectionHelper = new Common.DbConnectionHelper(Connection);
            System.Data.Common.DbDataAdapter Adapter          = ConnectionHelper.CreateAdapter();
            ConnectionHelper.Open();

            #region 表
            Adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}",
                                                              Apq.Data.SqlClient.Common.EncodeString(Apq_Tables)
                                                              );
            nRtn += Adapter.Fill(ds, Apq_Tables);
            #endregion

            #region 列
            Adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}",
                                                              Apq.Data.SqlClient.Common.EncodeString(Apq_Columns)
                                                              );
            nRtn += Adapter.Fill(ds, Apq_Columns);
            #endregion

            return(nRtn);
        }
示例#8
0
        /*
         * Public Sub Bind()
         *  If _Cmd Is Nothing Or _Ad Is Nothing Then
         *      Throw New Exception("The database connection is not set")
         *  End If
         *  _Ad.Fill(Me)
         * End Sub
         */

        /// <summary>
        /// Executes the query and fills this datatable.
        /// </summary>
        public void Bind()
        {
            if (_Cmd == null || _Ad == null)
            {
                throw (new TenorException("The database connection is not set"));
            }

            bool     exception = false;
            DateTime sqlTime   = DateTime.Now;

            try
            {
                if (_ActiveConnection.State == ConnectionState.Closed)
                {
                    Helper.OpenConnection(_ActiveConnection);
                }
                sqlTime = DateTime.Now;
                //Diagnostics.Debug.DebugSQL("Tenor.Data.DataTable", this.CommandText, null, currentConnection);
                _Ad.Fill(this);
            }
            catch (Exception up)
            {
                exception = true;
                up.Data.Add("CommandText", CommandText);
                throw;
            }
            finally
            {
                try
                {
                    if (exception)
                    {
                        System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec. (SqlException)");
                    }
                    else
                    {
                        System.Diagnostics.Trace.TraceInformation("DataTable.Bind Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec.");
                    }
                }
                catch
                {
                }
                if (_ActiveConnection.State != ConnectionState.Closed)
                {
                    _ActiveConnection.Close();
                }
            }
        }
示例#9
0
        /// <summary>
        /// Update Data
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pSelectSQL">Select Statement</param>
        /// <param name="pDataReader">DataReader</param>
        /// <returns>bool</returns>
        public static bool UpdateData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
        {
            bool result = false;

            System.Data.Common.DbConnection     Conn    = null;
            System.Data.Common.DbDataAdapter    Adapter = null;
            System.Data.Common.DbCommandBuilder builder = null;
            System.Data.Common.DbCommand        cmd     = null;
            DataSet   dataSet = new DataSet();
            DataTable Temp    = new DataTable();

            string connString = ParseConnectionString(pFileString);

            if (DataSource != null)
            {
                IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
                driver.ConnectionString = connString;
                Conn    = (System.Data.Common.DbConnection)driver.GetConnection();
                Adapter = driver.GetDbAdapter(pSelectSQL);
                Adapter.FillSchema(dataSet, SchemaType.Source);
                Adapter.Fill(Temp);
                builder = driver.GetDbCommandBuilder(Adapter);
            }
            try
            {
                Conn.Open();
                cmd = builder.GetInsertCommand(true);
                cmd.CommandTimeout = 1500;

                while (pDataReader.Read())
                {
                    foreach (System.Data.Common.DbParameter param in cmd.Parameters)
                    {
                        param.Value = pDataReader[param.SourceColumn];
                    }
                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                Conn.Close();
            }

            result = true;
            return(result);
        }
示例#10
0
        //public static System.Data.DataTable ExecuteStoredProcedure(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade
        // )
        //{
        //    return ExecuteStoredProcedure(databaseFacade, "foo", ("abc", "def"), ("abc", 5));
        //}


        public static System.Data.DataTable ExecuteStoredProcedure(
            this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade,
            string procedureName,
            params System.ValueTuple <string, object>[] parameters
            )
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            // using (System.Data.Common.DbConnection con = databaseFacade.GetDbConnection())
            System.Data.Common.DbConnection con = databaseFacade.GetDbConnection();
            {
                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = procedureName;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    if (parameters != null)
                    {
                        for (int i = 0; i < parameters.Length; ++i)
                        {
                            System.Data.Common.DbParameter p = cmd.CreateParameter();
                            p.ParameterName = parameters[i].Item1;
                            p.Value         = parameters[i].Item2;
                            p.DbType        = GetDbType(p.Value);

                            if (p.Value == null)
                            {
                                p.Value = System.DBNull.Value;
                            } // End if (p.Value == null)

                            cmd.Parameters.Add(p);
                        } // Next i
                    }     // End if (parameters != null)


                    // cmd.ExecuteNonQuery();
                    // cmd.ExecuteReader();
                    using (System.Data.Common.DbDataAdapter da = System.Data.Common.ProviderExtensions.CreateDataAdapter(con, cmd))
                    {
                        da.Fill(dt);
                    } // End Using da
                }     // End Using cmd
            }         // End Using con

            return(dt);
        } // End Function ExecuteStoredProcedure
示例#11
0
 private static System.Data.DataTable GetDataSourceData(System.Data.Common.DbDataAdapter adapter, System.String namecolumn, System.String mailcolumn, System.String ownercolumn, System.String book)
 {
     if (adapter == null)
     {
         return(null);
     }
     System.Data.DataTable data = GetDataSourceDataTable(namecolumn, mailcolumn, ownercolumn, book);
     try {
         adapter.Fill(data);
     } catch (System.Exception e) {
         if (log.IsErrorEnabled)
         {
             log.Error("Error while doing query", e);
         }
         return(null);
     }
     return(data);
 }
示例#12
0
        public static System.Data.DataTable GetDataTable(System.Data.Common.DbCommand cmd)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            using (System.Data.Common.DbConnection con = GetConnection())
            {
                cmd.Connection = con;

                using (System.Data.Common.DbDataAdapter da = fact.CreateDataAdapter())
                {
                    da.SelectCommand = cmd;

                    da.Fill(dt);
                }
            }

            return(dt);
        }
示例#13
0
        private DataTable getSynonymDataTable(string synonymDb, string sql)
        {
            DataTable schemaDataTable = new DataTable();


            if (synonymDb != "")
            {
                try
                {
                    System.Data.Common.DbConnection Con;

                    string ConectionString = @"Server=" + server +
                                             ";User Id=" + userId +
                                             ";Password="******";Database=" + synonymDb + ";";
                    Con = new SqlConnection(ConectionString);

                    Con.Open();

                    System.Data.Common.DbCommand     vlCommand = default(System.Data.Common.DbCommand);
                    System.Data.Common.DbDataAdapter vlAdapter = default(System.Data.Common.DbDataAdapter);

                    vlCommand             = Con.CreateCommand();
                    vlCommand.CommandText = sql;

                    vlAdapter = getDbDataAdapter();

                    vlAdapter.SelectCommand = vlCommand;

                    vlAdapter.Fill(schemaDataTable);

                    Con.Close();
                }
                catch
                {
                }
            }


            return(schemaDataTable);
        }
示例#14
0
        } // End Function GetDataTable

        public virtual System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            using (System.Data.Common.DbConnection idbc = GetConnection())
            {
                lock (idbc)
                {
                    lock (cmd)
                    {
                        try
                        {
                            cmd.Connection = idbc;

                            using (System.Data.Common.DbDataAdapter daQueryTable = m_fact.CreateDataAdapter())
                            {
                                daQueryTable.SelectCommand = (System.Data.Common.DbCommand)cmd;
                                daQueryTable.Fill(dt);
                            } // End Using daQueryTable
                        }     // End Try
                        catch (System.Data.Common.DbException ex)
                        {
                            if (Log("SQL.GetDataTable(System.Data.IDbCommand cmd)", ex, cmd))
                            {
                                throw;
                            }
                        }// End Catch
                        finally
                        {
                            if (idbc.State != System.Data.ConnectionState.Closed)
                            {
                                idbc.Close();
                            }
                        } // End Finally
                    }     // End lock cmd
                }         // End lock idbc
            }             // End Using idbc

            return(dt);
        } // End Function GetDataTable
示例#15
0
        public static System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            using (System.Data.IDbConnection idbConn = GetConnection())
            {
                cmd.Connection = idbConn;

                try
                {
                    using (System.Data.Common.DbDataAdapter da = s_factory.CreateDataAdapter())
                    {
                        da.SelectCommand = (System.Data.Common.DbCommand)cmd;
                        da.Fill(dt);
                    }
                }
                catch
                {
                    throw;
                }
            }

            return(dt);
        }
示例#16
0
        public static void DataToXML()
        {
            string table_schema = "geoip";
            string table_name   = "geoip_blocks_temp";

            table_schema = "public";
            table_name   = "t_sys_language_monthnames";


            using (System.Data.DataTable dt = new System.Data.DataTable())
            {
                dt.TableName = "record";

                using (System.Data.DataSet ds = new System.Data.DataSet(table_name))
                {
                    ds.Tables.Add(dt);
                    // dt.Namespace = "foo";

                    using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
                    {
                        con.ConnectionString = GetCS();

                        using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                        {
                            cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;

                            using (System.Data.Common.DbDataAdapter da = Npgsql.NpgsqlFactory.Instance.CreateDataAdapter())
                            {
                                da.SelectCommand = cmd;

                                if (con.State != System.Data.ConnectionState.Open)
                                {
                                    con.Open();
                                }

                                da.Fill(dt);

                                if (con.State != System.Data.ConnectionState.Open)
                                {
                                    con.Close();
                                }
                            } // End Using da
                        }     // End Using cmd
                    }         // End Using con

                    string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");


                    //using (System.IO.Stream fs = System.IO.File.OpenWrite(exportFilename))
                    //{
                    //    using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
                    //    {
                    //        // System.IO.StringWriter sw = new System.IO.StringWriter();
                    //        // dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
                    //        dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
                    //    } // End Using sw

                    //} // End Using fs


                    System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
                    xs.Indent             = true;
                    xs.IndentChars        = "    ";
                    xs.NewLineChars       = System.Environment.NewLine;
                    xs.OmitXmlDeclaration = false;
                    // xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
                    xs.Encoding = new System.Text.UTF8Encoding(false);

                    // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                    using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
                    {
                        dt.WriteXml(writer, System.Data.XmlWriteMode.IgnoreSchema);
                    } // End Using writer

                    System.Console.WriteLine(dt.Rows.Count);
                } // End Using ds
            }     // End Using dt
        }         // End Sub DataToXML