Data Access through Web Services, Stored Procedures and SQL Query

INTRODUCTION

Web Services has been the most revolutionaryaspect of the .NET framework developed by Microsoft. This articlepresents the three different modes through which data can be accessedviz. through web services, stored procedures and SQL Query using thepower of ADO.NET and XML.

Accessing Data through Stored Procedures/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

Note:

To run the above files include these files ina project (create a new one or add to the existing one)and then buildthe solution , then debug and run in VS.NET.