Thursday, February 18, 2010

Classic DB Call returns DataSet

public String DKConnectionString = "Data Source=LDI-SQL;Initial Catalog=test_db;User Id=sa;Password=admin;";
public DataSet DKDataSet = new DataSet();

private DataSet RunQuery(string queryString)
{
SqlConnection DKConnection = new SqlConnection(this.DKConnectionString);
SqlDataAdapter DKAdapter = new SqlDataAdapter();
DKDataSet = new DataSet();
DKAdapter.SelectCommand = new SqlCommand(queryString, DKConnection);
DKAdapter.Fill(DKDataSet);
DKConnection.Close();

return DKDataSet;
}

private DataSet GetEmployeeByID(int EmployeeID)
{
String SQL = string.Format(
@"SELECT EmployeeID,
FirstName,
LastName,
DoB,
Address,
City,
State,
ZipCode,
PhoneNumber,
EmailAddress,
Salary,
e.DepartmentID,
DepartmentName
FROM Employees e, Departments d
WHERE e.DepartmentID = d.DepartmentID
AND e.EmployeeID = {0};", EmployeeID);

DKDataSet = RunQuery(SQL);
return DKDataSet;
}

public DataSet InitEmployee(int EmployeeID)
{
DataSet InitMasterDS = new DataSet();
String SQL = string.Format(@"SELECT CustomerID,
CustomerName,
Address,
City,
State,
ZipCode,
Phone,
AccountManagerID
FROM Customers
WHERE AccountManagerID = {0}", EmployeeID);

InitMasterDS.Tables.Add(this.GetEmployeeByID(EmployeeID).Tables[0].Copy());
InitMasterDS.Tables[0].TableName = "Employee";
DKDataSet = RunQuery(SQL);
InitMasterDS.Tables.Add(DKDataSet.Tables[0].Copy());
InitMasterDS.Tables[1].TableName = "Customers";

return InitMasterDS;
}

No comments: