[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Pass DataSet values to a webservice

Posted on 2006-11-16
7
Medium Priority
?
313 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

656 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