Link to home
Create AccountLog in
Avatar of sbrent40
sbrent40

asked on

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

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.
Avatar of Gautham Janardhan
Gautham Janardhan

                 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();
                        }
                  }
connection string will be like

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

ASKER

Thanks, I will give this a try.
How would this work with SqlDataAdapter?
ASKER CERTIFIED SOLUTION
Avatar of Gautham Janardhan
Gautham Janardhan

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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
        }
    }
}
private static void Publishmydisk()

// us missed that return type when u declared the function
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
        }
    }
   
}
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.