?
Solved

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

Posted on 2007-10-04
9
Medium Priority
?
381 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.
0
Comment
Question by:sbrent40
  • 5
  • 4
9 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 20019487
                 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();
                        }
                  }
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 20019491
connection string will be like

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

Author Comment

by:sbrent40
ID: 20021169
Thanks, I will give this a try.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sbrent40
ID: 20068696
How would this work with SqlDataAdapter?
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 2000 total points
ID: 20070347
u will need to specify the select,Insert,Update,Delete command for the sqldataadapter and when initializig the sql dataadapter set the connection string as

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


check this example om how to work with a sql adapter

this is s ample code that updates changes made  to table employee

SQlAdapter =
                    new System.Data.SqlClient.SqlDataAdapter
                    ();
               SQlAdapter.SelectCommand =
                    (System.Data.SqlClient.SqlCommand)new SQlCommand
                    ("SELECT * FROM EMPLOYEE WHERE EMPID = @EMPID ");
               SQlAdapter.DeleteCommand =
                    (System.Data.SqlClient.SqlCommand)new SQlCommand
                    ("DELETE FROM EMPLOYEE WHERE EMPID = @EMPID ");
               SQlAdapter.UpdateCommand =
                    (System.Data.SqlClient.SqlCommand)new SQlCommand
                    ("UPDATE EMPLOYEE SET EMPNAME = @EMPNAME,"
                    +"EMPADDRESS = @EMPADDRESS WHERE EMPID = @EMPID ");
               SQlAdapter.InsertCommand =
                    (System.Data.SqlClient.SqlCommand)new SQlCommand
                    ("INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPADDRESS)VALUES("
                    +"@EMPID,@EMPNAME,@EMPADDRESS)");




SQlAdapter.SelectCommand.Parameters.Add("@EMPID","");
               ASet = new System.Data.DataSet();



               SQlAdapter.SelectCommand.Parameters["@EMPID"].Value = textBox1.Text;
               try
               {
                    SQlAdapter.Fill(ASet,"EMPLOYEE");
                    gridControl1.DataSource = ASet.Tables["EMPLOYEE"];
               }
               catch(Exception ex)
               {
                    MessageBox.Show(ex.Message);
               }



System.Data.IDataParameter dtPrmTemp;
               foreach(System.Data.DataColumn ACol in ASet.Tables["EMPLOYEE"].Columns)
               {
                     
                    dtPrmTemp     = new SqlParameter();
                    dtPrmTemp.ParameterName = "@" + ACol.ColumnName;
                    //--- Bind Parameter to Column ---
                    dtPrmTemp.SourceColumn = ACol.ColumnName;
                    //--- Parameters of Param should take Current version ---
                    dtPrmTemp.SourceVersion = System.Data.DataRowVersion.Current;
                    SQlAdapter.InsertCommand.Parameters.Add(dtPrmTemp);
                 }
                dtPrmTemp     = new SqlParameter();
               dtPrmTemp.ParameterName = "@" + "EMPID";
               //--- Bind Parameter to Column ---
               dtPrmTemp.SourceColumn = "EMPID";
               //--- Parameters of Param should take Current version ---
               dtPrmTemp.SourceVersion = System.Data.DataRowVersion.Current;
              SQlAdapter.DeleteCommand.Parameters.Add(dtPrmTemp);

                dtPrmTemp     = new SqlParameter();
               dtPrmTemp.ParameterName = "@" + "EMPID";
               //--- Bind Parameter to Column ---
               dtPrmTemp.SourceColumn = "EMPID";
               //--- Parameters of Param should take Current version ---
               dtPrmTemp.SourceVersion = System.Data.DataRowVersion.Current;
              SQlAdapter.UpdateCommand.Parameters.Add(dtPrmTemp);
0
 

Author Comment

by:sbrent40
ID: 20070863
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
        }
    }
}
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 20070995
private static void Publishmydisk()

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

Author Comment

by:sbrent40
ID: 20071368
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
        }
    }
   
}
0
 

Author Comment

by:sbrent40
ID: 20072003
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developer tools in browsers have been around for a while, yet they are still heavily underused by developers. Developers still fix html or CSS then refresh page to see effect, or they put alert or debugger in JavaScript and then try again and again …
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question