Solved

Pass DataSet values to a webservice

Posted on 2006-11-16
7
298 Views
Last Modified: 2012-05-05
I have the following code that rameedev helped me with:

string allEntries = "[['1','1','1','1','1'], ['2','2','2','2','2', ['6','6','6','6','6'], ['7','7','7','7','7', ['11','11','11','11','11'], ['22','22','22','22','22'] ], ['8','8','8','8','8'] ], ['3','3','3','3','3'], ['12','12','12','12','12', ['121','121','121','121','121'], ['1212','1212','1212','1212','1212'], ['12123','12123','12123','12123','12123', ['qw','qw','qw','qw','qw'], ['qweqwe','qwewqe','wqeqwe','qweqwe','qweqwe']] ], ['5','5','5','5','5']]";

               allEntries.Trim();
               allEntries = allEntries.Replace("[[","[");
               //Console.WriteLine(allEntries);
               allEntries =  allEntries.Replace("]]","]");
               //Console.Write(allEntries);
               
               string[] Test = allEntries.Split(',');
               string strValues="";
               string[] strArrForDS =  new string[5];
               
               DataSet dsSplitRows      =  new DataSet();
               DataTable dtSplitRows    =  new DataTable();
               DataRow drRow;

               DataColumn dc1 = new DataColumn("Menu_Icon");
               DataColumn dc2 = new DataColumn("Menu_Title");
               DataColumn dc3 = new DataColumn("Menu_URL");
               DataColumn dc4 = new DataColumn("Menu_Target");
               DataColumn dc5 = new DataColumn("Menu_Description");

               dtSplitRows.Columns.Add(dc1);
               dtSplitRows.Columns.Add(dc2);
               dtSplitRows.Columns.Add(dc3);
               dtSplitRows.Columns.Add(dc4);
               dtSplitRows.Columns.Add(dc5);

               dsSplitRows.Tables.Add(dtSplitRows);

               int iCtrArr=0;
               foreach(string strTmp in Test)
               {
                   
                    if(strTmp.IndexOf("]") < 0)
                    {
                         strValues += strTmp + ",";
                         iCtrArr++;
                         if(iCtrArr==5)
                         {
                              strValues = strValues.Replace("[","(").Replace("]",")");
                              strValues = strValues.Remove(strValues.LastIndexOf(','),1) + ")";                              
                              strArrForDS = strValues.Replace("(","").Replace(")","").Split(',');
                              drRow = dsSplitRows.Tables[0].NewRow();
                              drRow["Menu_Icon"] = strArrForDS[0].Replace("'", "").Trim();
                              drRow["Menu_Title"] = strArrForDS[1].Replace("'", "").Trim();
                              drRow["Menu_URL"] = strArrForDS[2].Replace("'", "").Trim();
                              drRow["Menu_Target"] = strArrForDS[3].Replace("'", "").Trim();
                              drRow["Menu_Description"] = strArrForDS[4].Replace("'", "").Trim();
                              dsSplitRows.Tables[0].Rows.Add(drRow);
                             
                              strValues ="";
                              iCtrArr=0;
                         }
                    }
                    else
                    {
                         strValues += strTmp;
                         strValues = strValues.Replace("[","(").Replace("]",")");
                         iCtrArr++;
                         if(iCtrArr==5)
                         {
                              strArrForDS = strValues.Replace("(","").Replace(")","").Split(',');
                              drRow = dsSplitRows.Tables[0].NewRow();
                              drRow["Menu_Icon"] = strArrForDS[0].Replace("'","").Trim();
                              drRow["Menu_Title"] = strArrForDS[1].Replace("'","").Trim();
                              drRow["Menu_URL"] = strArrForDS[2].Replace("'","").Trim();
                              drRow["Menu_Target"] = strArrForDS[3].Replace("'","").Trim();
                              drRow["Menu_Description"] = strArrForDS[4].Replace("'","").Trim();
                              dsSplitRows.Tables[0].Rows.Add(drRow);

                              strValues ="";
                              iCtrArr=0;
                         }
                    }
               }

As you can see the allEntries is a HUGE string, this gets converted to a dataset. Would really like the ouput passed to a WEB Service, the WEB Service will call a Stored Procedure and insert the value from the dataset as individual rows to a SQL db.

Dont know if this is useful? but this are the statements I would like in the WEB service:

myCommand.Parameters.Add("@Icon", SqlDbType.VarChar, 50).Value = <dont know how to get the value from DS>
myCommand.Parameters.Add("@Title", SqlDbType.VarChar, 300).Value = <dont know how to get the value from DS>
myCommand.Parameters.Add("@URL", SqlDbType.VarChar, 300).Value = <dont know how to get the value from DS>
myCommand.Parameters.Add("@Target", SqlDbType.VarChar, 50).Value = <dont know how to get the value from DS>
myCommand.Parameters.Add("@Description", SqlDbType.VarChar, 300).Value = <dont know how to get the value from DS>

As I said, the stuff I've done above might be wrong, as we have a dataset.

My Stored procedure is as follows:

CREATE PROCEDURE [dbo].[usp_insertMenu]

@Icon             VarChar(50),
@Title             VarChar(300),
@URL              VarChar(300),
@Target       VarChar(300),
@Description      VarChar(300)

AS

Declare @ErrorText as VarChar(100)

INSERT      Menu
      (
                  Menu_Icon,
                  Menu_Title,
                  Menu_URL,
                  Menu_Target,
                  Menu_Description
                  
      )
      VALUES
      (            @Icon,
                  @Title,
                  @URL,
                  @Target,
                  @Description                        
      )

IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1)
BEGIN
      SET @ErrorText='(Could not add file)'
      RAISERROR (@ErrorText,17,100)

END
GO

Thanks man

0
Comment
Question by:mousemat24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
rameedev earned 500 total points
ID: 17955333
It's not the perfect code...but i guess you could start off in you web method..basically your method should take a Datset as parameter

[WebMethod]
public void InsertData(DataSet dsSplitRows)
{
                                                SqlConnection cnws =  new SqlConnection("ur conn str");
                  cnws.Open();
                  SqlCommand cmdws = new SqlCommand("ur sp name",cnws);
                  foreach(DataRow dr in dsSplitRows.Tables[0].Rows)
                  {
                        try
                        {
                              SqlParameter paramIcon =  new SqlParameter("@Icon",SqlDbType.VarChar,50);
                              paramIcon.Direction = ParameterDirection.Input;
                              paramIcon.Value = dr["menu_icon"].ToString();

                              SqlParameter paramTitle =  new SqlParameter("@Title",SqlDbType.VarChar,300);
                              paramIcon.Direction = ParameterDirection.Input;
                              paramTitle.Value = dr["menu_Title"].ToString();      

                              SqlParameter paramUrl =  new SqlParameter("@URL",SqlDbType.VarChar,300);
                              paramIcon.Direction = ParameterDirection.Input;
                              paramUrl.Value = dr["menu_URL"].ToString();

                              SqlParameter paramTarget =  new SqlParameter("@Target",SqlDbType.VarChar,300);
                              paramIcon.Direction = ParameterDirection.Input;
                              paramTarget.Value = dr["menu_Target"].ToString();

                              SqlParameter paramDesc =  new SqlParameter("@Description",SqlDbType.VarChar,300);
                              paramIcon.Direction = ParameterDirection.Input;
                              paramDesc.Value = dr["menu_description"].ToString();

                              cmdws.Parameters.Add(paramIcon);
                              cmdws.Parameters.Add(paramTitle);
                              cmdws.Parameters.Add(paramUrl);
                              cmdws.Parameters.Add(paramTarget);
                              cmdws.Parameters.Add(paramDesc);

                              cmdws.ExecuteNonQuery();
                              cmdws.Parameters.Clear();
                              cmdws.Cancel();
                        }
                        catch(Exception ex)
                        {
                              Console.WriteLine(ex.Message);
                        }
                  }
}
0
 
LVL 5

Expert Comment

by:rameedev
ID: 17955350
The sqlCommand might have to be moved inside the Foreach loop just check it out. if it throws error...if that is the case then after cmdws.cancel() u need to call cmd.dispose or simply just call the cmd.dispose() method
0
 

Author Comment

by:mousemat24
ID: 17955537
Thanks rameedev for writing the code for me.

How do I consume the WEB Service? from the code that creates the DS.

right at the end of your code (the one that creates the DS) I've placed the following:

                .
                .
                .
                .
                strValues ="";
                iCtrArr=0;
        }
    }
}

locahHost.test_WS aa = new locahHost.test_WS();   // placed this
aa.InsertData(dsSplitRows)                                     // placed this

It runs the WEB Service, but it dosnt populate the SQL table. Is what I've done wrong?

Thanks rameedev
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 5

Expert Comment

by:rameedev
ID: 17962670
Well assuming u have created a Web Service method that takes a Dataset as parameter what you need to do basically is simply debug the Web service code at runtime.

I would suggest add the web service project in your current solution recompile web service project and re-reference it in your web app...what this does is when u put a break point in your code say at "aa.InsertData(dsSplitRows) " line of code..it will get inside the web method and you can see what basically is happening.

the code that i gave you is not tested and i am sure it definitely has some bugs in it, so the best way to know why it is not populating the database is to debug the code.
0
 

Author Comment

by:mousemat24
ID: 17963775
rameedev

Last question, is what I've (you've) done correct? I just want to know is that the kind of thing you would of done? the fact is I do have a looooog string, that needs splitting and inserting into a SQL db using a web service. Is it correct in putting it into a DS? Please can you tell me what you whould of done if you were in my position

Thanks for all your hard work, and I really appricate all your time rameedev!!
0
 
LVL 5

Expert Comment

by:rameedev
ID: 17963849
Well mousemat i am not completely aware of your entrire project and the experts here take the problem at hand try and answer your questions or should i say try and give you a probable solution...i believe sometimes when you get a bad solution (which works) the person who has asked the question will find himself a proper solution..so the idea is to just help the question asker...

as far as what i would have done.it depends on what problem i have on my hand. with repsect to the above problem i would have pretty much done the same.
0
 

Author Comment

by:mousemat24
ID: 17964062
THANK YOU rameedev, FOR BEING THERE FOR ME!!!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

729 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