Esempio n. 1
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);
        }
Esempio n. 2
0
        public virtual void InsertUpdateDataTable(string tableSchema, string tableName, System.Data.DataTable dt)
        {
            string strSQL = "SELECT * FROM ";

            if (tableSchema != null)
            {
                strSQL += this.QuoteObjectIfNecessary(tableSchema) + ".";
            }

            strSQL += this.QuoteObjectIfNecessary(tableName) + " WHERE (1 = 2) ";

            using (System.Data.Common.DbConnection connection = this.Connection)
            {
                using (System.Data.Common.DbDataAdapter daInsertUpdate = this.m_factory.CreateDataAdapter())
                {
                    using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = strSQL;

                        System.Data.Common.DbCommandBuilder cb = this.m_factory.CreateCommandBuilder();
                        cb.DataAdapter = daInsertUpdate;

                        daInsertUpdate.SelectCommand = cmdSelect;
                        daInsertUpdate.InsertCommand = cb.GetInsertCommand();
                        daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
                        daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();

                        daInsertUpdate.Update(dt);
                    } // End Using cmdSelect
                }     // End Using daInsertUpdate
            }         // End Using connection
        }             // End Sub InsertUpdateDataTable
Esempio n. 3
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);
        }
Esempio n. 4
0
        /// <summary>
        /// Write Data Table
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pSelectSQL">Select statement</param>
        /// <param name="pDestinationTableName">Destination Table name</param>
        /// <param name="pDatatable">Data Table</param>
        /// <returns></returns>
        public static bool WriteDataTable(string pFileString, string pSelectSQL, string pDestinationTableName, DataTable pDatatable)
        {
            bool result = false;

            System.Data.Common.DbDataAdapter    Adapter = null;
            System.Data.Common.DbCommandBuilder builder = null;
            DataSet dataSet = new DataSet();

            string connString = ParseConnectionString(pFileString);

            if (DataSource != null)
            {
                IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
                driver.ConnectionString = connString;
                Adapter = driver.GetDbAdapter(pSelectSQL);
                builder = driver.GetDbCommandBuilder(Adapter);
            }

            //code to modify data in DataSet here
            builder.GetInsertCommand();

            //Without the SqlCommandBuilder this line would fail
            Adapter.Update(dataSet, pDestinationTableName);

            /*
             *  OleDbCommand insertCommand = dataAdapter.InsertCommand;
             *  foreach (DataRow row in origTable.Rows)
             *  {
             *      foreach (OleDbParameter param in insertCommand.Parameters)
             *      {
             *          param.Value = row[param.ParameterName];
             *      }
             *      insertCommand->ExecuteNonQuery();
             *  } */

            result = true;
            return(result);
        }