Home > Uncategorized > Code For CLR Stored Procedure Dynamic Table as a Return

Code For CLR Stored Procedure Dynamic Table as a Return

using
System;

using
System.Data;

using
System.Data.SqlClient;

using
System.Data.SqlTypes;

using
Microsoft.SqlServer.Server;

using
System.Collections.Generic;

 

 

public
partial
class
StoredProcedures

{


public
readonly
static
string[] months = { “Oct”, “Nov”, “Dec”, “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep” };

 

[Microsoft.SqlServer.Server.SqlProcedure]


public
static
void
TA_GetEstimating(int
Contract_ID, string
type, string
fy)

{

 


using (SqlConnection
connection = new
SqlConnection(“context connection=true”))

{


connection.Open();


SqlCommand
command = null; // new SqlCommand(“SELECT * FROM TA_Period where Period_ID in(” + strPeriods + “)”, connection);


SqlDataReader
reader = null;// command.ExecuteReader();

 


//Setup the table to return


SqlMetaData[] parms = GetColumns(type);


// Create the record and specify the metadata for the columns.


SqlDataRecord
record = new
SqlDataRecord(parms);

 

#region Get the people


command = new
SqlCommand(“SELECT Contract_ID, TA_Person_Contract.Person_ID, Company_Name, Last_Name + ‘, ‘ + First_Name AS Name, Authorized_Hours, Estimated_Hours, “ +


“(SELECT MAX(TA_Contract_Task_Labor.Rate) AS Expr1 FROM TA_Contract_Task_Labor_Person INNER JOIN “ +


“TA_Contract_Task_Labor ON TA_Contract_Task_Labor_Person.Contract_ID = TA_Contract_Task_Labor.Contract_ID AND “ +


“TA_Contract_Task_Labor_Person.Task_ID = TA_Contract_Task_Labor.Task_ID AND “ +


“TA_Contract_Task_Labor_Person.Labor_ID = TA_Contract_Task_Labor.Labor_ID “ +


“WHERE (TA_Contract_Task_Labor_Person.Contract_ID = @Contract_ID) AND (TA_Contract_Task_Labor_Person.Person_ID = TA_Person_Contract.Person_ID)) as Rate “ +


“FROM TA_Person_Contract INNER JOIN TA_Person ON TA_Person_Contract.Person_ID = TA_Person.Person_ID WHERE (Contract_ID = @Contract_ID) ORDER BY Name”, connection);


SqlParameter
p = new
SqlParameter(“@Contract_ID”, SqlDbType.Int);


p.Value = Contract_ID;


command.Parameters.Add(p);


reader = command.ExecuteReader();

 


List<Person> listPeople = new
List<Person>();


using (reader)

{


while (reader.Read())

{


Person
person = new
Person();


person.PersonID = (int)reader[“Person_ID”];


person.Company = reader[“Company_Name”].ToString();


person.Name = reader[“Name”].ToString();


person.EstimatedHours = (decimal)reader[“Estimated_Hours”];


person.Rate = (decimal)reader[“Rate”];


listPeople.Add(person);

}

}

#endregion

 


// Mark the begining of the result-set.


SqlContext.Pipe.SendResultsStart(record);

 

 


List<string> messages = new
List<string>();


foreach (Person
person
in
listPeople)

{


//throw new Exception(“Got Here”);


record.SetInt32(0, person.PersonID);

 


SqlString
s = new
SqlString(person.Name);


record.SetSqlString(1, s);

 


s = new
SqlString(person.Company);


record.SetSqlString(2, s);

 


for (int
i = 0; i < 12; i++)

{


int
index = 4 * i;


string
periods = GetPeriods(i + 1, connection, type, fy);


messages.Add(“Periods for “ + i.ToString() + ” – “ + periods);

 


//Actual Hours


record.SetDecimal(index + 3, GetMonthAH(connection, Contract_ID, person.PersonID, periods));


//Actual Cost


record.SetDecimal(index + 4, GetMonthAC(connection, Contract_ID, person.PersonID, periods));

 


//Get the estimated hours


decimal
eh = GetMonthEH(connection, Contract_ID, person.PersonID, person.EstimatedHours, periods);


//Estimated hours


record.SetDecimal(index + 5, eh);


//Estimated Cost EH * (max rate)


record.SetDecimal(index + 6, eh * person.Rate);

}


// Send the row back to the client.


SqlContext.Pipe.SendResultsRow(record);

 

}


// Mark the end of the result-set.


SqlContext.Pipe.SendResultsEnd();


foreach (string
message
in
messages)


SqlContext.Pipe.Send(message);

}

}


private
static
string
GetPeriods(int
monthOrder, SqlConnection
connection, string
type, string
fy)

{


string
period = “”;


switch (type)

{


case
“Month”:


default:


SqlCommand
command = new
SqlCommand(“select period_ID from TA_Period where fy = @fy and MonthOrder = @Month”, connection);

 


SqlParameter
p = new
SqlParameter(“@fy”, SqlDbType.VarChar, 2);


p.Value = fy;


command.Parameters.Add(p);

 


p = new
SqlParameter(“@Month”, SqlDbType.Int);


p.Value = monthOrder;


command.Parameters.Add(p);

 

 


SqlDataReader
reader = command.ExecuteReader();


while (reader.Read())

{


if (period != “”)


period += “, “;


period += reader[“Period_ID”].ToString();

}


reader.Close();


break;

}


return
period;

}

 


private
static
SqlMetaData[] GetColumns(string
type)

{


SqlMetaData[] parms = null;

 


switch (type)

{


case
“Month”:


default:

#region Month columns


parms = new
SqlMetaData[51];

 


parms[0] = new
SqlMetaData(“Person_ID”, SqlDbType.Int);


parms[1] = new
SqlMetaData(“Name”, SqlDbType.VarChar, 100);


parms[2] = new
SqlMetaData(“Company”, SqlDbType.VarChar, 100);

 


for (int
i = 0; i < 12; i++ )

{


int
index = 4 * i;


parms[index + 3 ] = new
SqlMetaData(months[i] + “AH”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 4] = new
SqlMetaData(months[i] + “AC”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 5] = new
SqlMetaData(months[i] + “EH”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));


parms[index + 6] = new
SqlMetaData(months[i] + “EC”, SqlDbType.Decimal, byte.Parse(“20”), byte.Parse(“2”));

}

 

#endregion


break;


case
“Period”:


break;

}


return
parms;

}


private
static
decimal
GetMonthAH(SqlConnection
connection, int
contract, int
person, string
period)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL(Sum(Hours), 0) FROM TA_TimeEntry where “ +


“Contract_ID = @Contract_ID and “ +


“Person_ID = @Person_ID and “ +


“Period_ID in(“ + period + “)”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


hours = (Decimal)comPeriod.ExecuteScalar();

 


return
hours;

}


private
static
decimal
GetMonthAC(SqlConnection
connection, int
contract, int
person, string
period)

{


decimal
hours = 0;

 


string
sql = “SELECT ISNULL(Sum(TA_Contract_Task_Labor.Rate * TA_TimeEntry.Hours), 0) “ +


“FROM TA_Contract_Task_Labor INNER JOIN TA_TimeEntry ON TA_Contract_Task_Labor.Contract_ID = TA_TimeEntry.Contract_ID AND TA_Contract_Task_Labor.Labor_ID = TA_TimeEntry.Labor_ID AND TA_Contract_Task_Labor.Task_ID = TA_TimeEntry.Task_ID “ +


“WHERE “ +


“TA_TimeEntry.Contract_ID = @Contract_ID and “ +


“Person_ID = @Person_ID and “ +


“Period_ID in(“ + period + “)”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


hours = (Decimal)comPeriod.ExecuteScalar();

 


return
hours;

}


private
static
decimal
GetMonthEH(SqlConnection
connection, int
contract, int
person, decimal
estHours, string
period)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL((SELECT EstHours FROM TA_Person_Contract_Period AS pcp WHERE Person_ID = @Person_ID AND Contract_ID = TA_Contract_Period.Contract_ID AND Period_ID = TA_Contract_Period.Period_ID), @EstHours) AS EstHours “ +


“FROM TA_Contract_Period INNER JOIN TA_Period ON TA_Contract_Period.Period_ID = TA_Period.Period_ID WHERE “ +


“(TA_Contract_Period.Contract_ID = @Contract_ID) AND “ +


“(TA_Contract_Period.Period_ID IN (“ + period + “))”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


comPeriod.Parameters.Add(new
SqlParameter(“@EstHours”, SqlDbType.Decimal));


comPeriod.Parameters[2].Value = estHours;


SqlDataReader
reader = comPeriod.ExecuteReader();

 


while (reader.Read())

{


hours += (decimal)reader[“EstHours”];

}


reader.Close();


return
hours;

}


private
static
decimal
GetMonthEC(SqlConnection
connection, int
contract, int
person, decimal
estHours, string
period, decimal
rate)

{


decimal
hours = 0;


string
sql = “SELECT ISNULL((SELECT EstHours FROM TA_Person_Contract_Period AS pcp WHERE Person_ID = @Person_ID AND Contract_ID = TA_Contract_Period.Contract_ID AND Period_ID = TA_Contract_Period.Period_ID), @EstHours) AS EstHours “ +


“FROM TA_Contract_Period INNER JOIN TA_Period ON TA_Contract_Period.Period_ID = TA_Period.Period_ID WHERE “ +


“(TA_Contract_Period.Contract_ID = @Contract_ID) AND “ +


“(TA_Contract_Period.Period_ID IN (“ + period + “))”;


SqlCommand
comPeriod = new
SqlCommand(sql, connection);


comPeriod.Parameters.Add(new
SqlParameter(“@Person_ID”, SqlDbType.Int));


comPeriod.Parameters[0].Value = person;


comPeriod.Parameters.Add(new
SqlParameter(“@Contract_ID”, SqlDbType.Int));


comPeriod.Parameters[1].Value = contract;


comPeriod.Parameters.Add(new
SqlParameter(“@EstHours”, SqlDbType.Decimal));


comPeriod.Parameters[2].Value = estHours;


SqlDataReader
reader = comPeriod.ExecuteReader();

 


while (reader.Read())

{


hours += (decimal)reader[“EstHours”];

}


reader.Close();


return
hours;

}

};

 

internal
class
Person

{


public
int
PersonID;


public
string
Company;


public
string
Name;


public
decimal
EstimatedHours;


public
decimal
Rate;

}

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: