We help IT Professionals succeed at work.

asp.net in C# example needed to pull dataset from sql05 then read one line at time into another process

sbrent40
sbrent40 asked
on
430 Views
Last Modified: 2011-10-03
I need a procedure for a web application that when called queries a database for fieldx = 0 and then reads the resulting dataset one line at a time into another set of variables, which are then submitted one at a time into another process. Language is C# / ASP.net

1st procedure pull sql data from OrdersDB with fieldx=0
2nd read 1 line of resulting data into set of variables
3rd call 3rd party procedure which uses these variables
4th on success of "3" mark record in original database (call it OrdersDB) fieldx = 1
loop to 2nd step to read another line
repeat until all records read.
on completion give success back to page that called it.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
                 System.Data.OleDb.OleDbDataAdapter FCommand =
                        new System.Data.OleDb.OleDbDataAdapter
                        ("select * from table where filedx = 0",connectionstring);

                  DataSet Fset = new DataSet();
                  FCommand.Fill(Fset);
                  for(int i=0;i<Fset.Tables[0].Rows.Count;i++)
                  {
                        string Variable = Fset.Tables[0].Rows[i]["COLUMNNAME"].ToString();
                        //similarly for other variable
                        if(Call 3 rd party function using the variables)
                        {
                              System.Data.OleDb.OleDbConnection  FC =
                                    new System.Data.OleDb.OleDbConnection(ConnectionString);
                              System.Data.OleDb.OleDbCommand  FE=  
                                    new System.Data.OleDb.OleDbCommand
                                    ("update table set somecolumn = 0"
                                    +" where fieldx = 0");
                              FE.ExecuteNonQuery();
                        }
                  }
CERTIFIED EXPERT

Commented:
connection string will be like

strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
        "Uid=MyUsername;Pwd=MyPassword;");

Author

Commented:
Thanks, I will give this a try.

Author

Commented:
How would this work with SqlDataAdapter?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok I tried it like this. Error is commented  and reads  " private static Publishmydisk()//ERROR "Class, struct, or inteface method must have a return type"  "
Thanks for all of your help, I am getting there.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using Rimage.Rapid.Dotnet.Api;


public static class OrderPUB
{
    static SqlConnection con;

    public static bool PublishItem()
    {
        string cs = WebConfigurationManager
            .ConnectionStrings["ConnectionString"]
                .ConnectionString;
        con = new SqlConnection(cs);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        //try
        {
            cmd.CommandText = "SELECT * FROM [dbo.itempub1] WHERE [status] < 1";

            cmd.ExecuteNonQuery();

            SqlDataReader pubReader;
            pubReader = cmd.ExecuteReader();
            pubReader.Read;


            DataSet Fset = new DataSet();

            for (int i = 0; i <= pubReader.FieldCount - 1; i++)
            {
                //string Variable = Fset.Tables[0].Rows[i]["COLUMNNAME"].ToString();
                string isf = Fset.Tables[0].Rows[i]["isofile"].ToString();
                string lbf = Fset.Tables[0].Rows[i]["lblfile"].ToString();
                string qty = Fset.Tables[0].Rows[i]["quantity"].ToString();
                string cid = Fset.Tables[0].Rows[i]["catid"].ToString();
                string onm = Fset.Tables[0].Rows[i]["ordernum"].ToString();
                string prd = Fset.Tables[0].Rows[i]["productid"].ToString();
                //similarly for other variable
                if (orderpub.publishmydisk)//call the method publishmydisk here
                {

                    SqlCommand cmd2 = new SqlCommand();

                    cmd2.CommandText = ("update [dbo.itempub1] set [status] = 1"
                          + " where [status] = 0");
                    cmd2.ExecuteNonQuery();
                }
            }
        }
        return true;
        con.Close();
    }
    private static Publishmydisk()//ERROR "Class, struct, or inteface method must have a return type"
    {
        string isf; //variable for isofile field
        isf = fset.isf;
        //string lbf; //variable for lblfile field
        //string qty; //variable for quantity field
        //string cid; //variable for the catid field
        //string onm; //variable for the ordernum field
        //string prd; //variable for the productid field

        ProductionOnlyJob job = null;
        //I will work on this section
        try
        {
        job = (ProductionOnlyJob)SingleConnectionSession.GetInstance().CreateProductionOnlyJob(JobType.FromImageJobType);
        job.AddDataTrack(this.isf, ConvertToUNC.False);//isofile field data for variable isf
        job.SetProductionParam(ProductionJobParamType.MediaTypeParam,
            JobParamValues.MediaTypeDVDR);//the catid field has the data in it that would be
                                          //put here as "MediaTypeDVDR" or "MediaTypeCDR"
        }
                catch (RimageException ex)
        {
            String msg = job.JobId + " : " + job.Status.State.ToString() + " " + job.Status.Stage + " " + job.Status.PercentComplete + "%";
            this.submitErrorTxtBox.Visible = true;
            this.submitErrorTxtBox.Text = job.Status.LastStatusUpdateTime + ": " + msg + " " + ex.Message;
        }
        {
            //nothing here
        }
    }
}
CERTIFIED EXPERT

Commented:
private static void Publishmydisk()

// us missed that return type when u declared the function

Author

Commented:
Fixed that.

Now I have error on ...
            pubReader.Read;//ERROR only assignment, call, increment, decrement, and
                                       //new object expressions can be used as a statement

full code is .....

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using Rimage.Rapid.Dotnet.Api;


public static class OrderPUB
{
    static SqlConnection con;

    public static bool PublishItem()
    {
        string cs = WebConfigurationManager
            .ConnectionStrings["ConnectionString"]
                .ConnectionString;
        con = new SqlConnection(cs);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        //try
        {
            cmd.CommandText = "SELECT * FROM [dbo.itempub1] WHERE [status] < 1";

            cmd.ExecuteNonQuery();

            SqlDataReader pubReader;
            pubReader = cmd.ExecuteReader();
            pubReader.Read;//ERROR only assignment, call, increment, decrement, and
                            //new object expressions can be used as a statement

           

            DataSet Fset = new DataSet();

            for (int i = 0; i <= pubReader.FieldCount - 1; i++)
            {
                //string Variable = Fset.Tables[0].Rows[i]["COLUMNNAME"].ToString();
                string isf = Fset.Tables[0].Rows[i]["isofile"].ToString();
                string lbf = Fset.Tables[0].Rows[i]["lblfile"].ToString();
                string qty = Fset.Tables[0].Rows[i]["quantity"].ToString();
                string cid = Fset.Tables[0].Rows[i]["catid"].ToString();
                string onm = Fset.Tables[0].Rows[i]["ordernum"].ToString();
                string prd = Fset.Tables[0].Rows[i]["productid"].ToString();
                //similarly for other variable
                //if (Publishmydisk())//call the method Publishmydisk here
                //{

                //    SqlCommand cmd2 = new SqlCommand();

                //    cmd2.CommandText = ("update [dbo.itempub1] set [status] = 1"
                //          + " where [status] = 0");
                //    cmd2.ExecuteNonQuery();
                //}
            }
        }
        return true;
        //con.Close();
    }
    private static bool Publishmydisk()
    {
        string isf; //variable for isofile field
        isf = (fset.isf);//ERROR The name "fset" does not exist in the current context
        //string lbf; //variable for lblfile field
        //string qty; //variable for quantity field
        //string cid; //variable for the catid field
        //string onm; //variable for the ordernum field
        //string prd; //variable for the productid field

        ProductionOnlyJob job = null;
        //I will work on this section
        try
        {
        job = (ProductionOnlyJob)SingleConnectionSession.GetInstance().CreateProductionOnlyJob(JobType.FromImageJobType);
        job.AddDataTrack(this.isf, ConvertToUNC.False);//isofile field data for variable isf
        job.SetProductionParam(ProductionJobParamType.MediaTypeParam,
            JobParamValues.MediaTypeDVDR);//the catid field has the data in it that would be
                                          //put here as "MediaTypeDVDR" or "MediaTypeCDR"
        }
                catch (RimageException ex)
        {
            String msg = job.JobId + " : " + job.Status.State.ToString() + " " + job.Status.Stage + " " + job.Status.PercentComplete + "%";
            //this.submitErrorTxtBox.Visible = true;
            //this.submitErrorTxtBox.Text = job.Status.LastStatusUpdateTime + ": " + msg + " " + ex.Message;
        }
        {
            return true;//nothing here
        }
    }
   
}

Author

Commented:
Stupid mistake....
           pubReader.Read;//ERROR only assignment, call, increment, decrement, and
                                       //new object expressions can be used as a statement
 should be

           pubReader.Read();

On to finding the other errors.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.