Data Access through Web Services, Stored Procedures and SQL Queries

INTRODUCTION

Web Services has been the most revolutionary aspect of the .NET framework developed by Microsoft. This article presents the three different modes through which data can be accessed viz. through Web services, stored procedures and SQL query using the power of ADO.NET and XML.

Note: To run the source code listed in here, download the attached zip file, add cs files to the projects, change database and table names and then build the solution , then debug and run in VS.NET.

Accessing Data through Stored Procedures and SQL Queries

Case: Displaying Data in a Data Grid through SQL Query involves the following main steps:

Step 1:

//Open the connection object to connect to the Data Base
SqlConnection sqlConn = new SqlConnection("Database=****;Server=****; uid = **; pwd=***"); //***- Pass the required details
sqlConn.Open ();

Step 2:

//Create the sqlcommand object and set the command //type
SqlCommand sc = new SqlCommand ();
sc.Connection = sqlConn;
sc.CommandType = CommandType.Text;//Change this to CommandType.StoredProcedure to //access thru Stored procedure

Step 3:

//set the command text
sc.CommandText = "Pass the SQL Query or STOREDPROCEDURE NAME";
/* For ex- "SELECT FunctionalAreaMaster.vFunctionalArea,nMinimumExperience,nMaximumExperience FROM FunctionalArea Or Test_StoredProcedure in case of Stored Procderues */

Step 4:

//create the data set and data adapter object
DataSet ds = new DataSet();
SqlDataAdapter myReader = new SqlDataAdapter(sc);
//fill the data adapter object with the data set
myReader.Fill(ds);
//set the grid data source as the data table
dgdFunctionArea.DataSource = dtblFunctionalArea;
//fill the data grid with details
for(int i =0;i<ds.Tables[0].Rows.Count;i++)
{
dtblFunctionalArea.LoadDataRow(arrstrFunctionalArea,true);
dgdFunctionArea[i,0] = ds.Tables[0].Rows[i].ItemArray[0].ToString();
dgdFunctionArea[i,1] = ds.Tables[0].Rows[i].ItemArray[1].ToString();
dgdFunctionArea[i,2] = ds.Tables[0].Rows[i].ItemArray[2].ToString();
}

Refer FillDataThruSpOrSQlQuery.cs for source code

Case: Displaying Data in a Data Grid through Web Service involves the following main steps:

//create the web service object to access web method

MaintainData.DataFill obj = new MaintainData.DataFill();

//Call the web method thru the above created object(say GetDetails here) and pass the //required xml string and collect the result in the form of xml in a string variable
string strViewResult = obj.GetDetails("PasstheXMLString"); //For ex- //<A><B>BE</B><B>MBA</B></A>
obj = null;
//here pass the above string to the method which will parse the xml from the string //passed and returns a xml element object
XmlElement XmlElementViewResult = GetDocumentElement(strViewResult);
//xmlelement returned will have Records tag if there are records else some other tag(this //depends on the tag returned from the data tier)
if(XmlElementViewResult.GetElementsByTagName("Records").Count > 0)
{
//now fill the rows in data grid by running the loop number of times there are childnodes
for (int intRow=0;intRow<XmlElementViewResult.ChildNodes.Count;intRow++)//row
{
//first fill the data table and then the respective cell in the data grid
dtblFunctionalArea.LoadDataRow(arrstrGeneral,true);
dgdGeneral[intRow,0] = XmlElementViewResult.GetElementsByTagName("SpecialisedArea").Item(intRow).InnerText;
dgdGeneral[intRow,1] = XmlElementViewResult.GetElementsByTagName("MinExperience").Item(intRow).InnerText;
dgdGeneral[intRow,2] = XmlElementViewResult.GetElementsByTagName("MaxExperience").Item(intRow).InnerText;
}
}

Refer FillDataThruWebService.cs for complete source code.