Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Pass DataSet values to a webservice

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
mousemat24
Asked:
mousemat24
  • 4
  • 3
1 Solution
 
rameedevCommented:
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
 
rameedevCommented:
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
 
mousemat24Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rameedevCommented:
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
 
mousemat24Author Commented:
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
 
rameedevCommented:
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
 
mousemat24Author Commented:
THANK YOU rameedev, FOR BEING THERE FOR ME!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now