public static void SendDataTable(DataTable dt)
{
bool[] coerceToString; // Do we need to coerce this column to string?
SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);
SqlDataRecord record = new SqlDataRecord(metaData);
SqlPipe pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
try
{
foreach (DataRow row in dt.Rows)
{
for (int index = 0; index < record.FieldCount; index++)
{
object value = row[index];
if (null != value && coerceToString[index])
{
value = value.ToString();
}
record.SetValue(index, value);
}
pipe.SendResultsRow(record);
}
}
finally
{
pipe.SendResultsEnd();
}
}
private static void ReturnSalesProcessedRecords(List <Total> _totalRevenueList)
{
SqlPipe pipe = SqlContext.Pipe;
SqlMetaData[] cols = new SqlMetaData[4];
cols[0] = new SqlMetaData("id", SqlDbType.Int);
cols[1] = new SqlMetaData("TotalRevenue", SqlDbType.Decimal, 38, 4);
cols[2] = new SqlMetaData("DatetimeStamp", SqlDbType.DateTime);
cols[3] = new SqlMetaData("TotalRevenueNew", SqlDbType.Decimal, 38, 4);
SqlDataRecord row = new SqlDataRecord(cols);
pipe.SendResultsStart(row);
//TODO: Sort the list once more time before projecting them into a table
foreach (var _totalRevenueItem in _totalRevenueList)
{
row.SetInt32(0, _totalRevenueItem.Id);
row.SetDecimal(1, _totalRevenueItem.TotalRevenue);
row.SetDateTime(2, _totalRevenueItem.DatetimeStamp);
row.SetDecimal(3, _totalRevenueItem.TotalRevenueNew);
pipe.SendResultsRow(row);
}
pipe.SendResultsEnd();
}
///<summary>RSS</summary>
public static void RSS()
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
// Retrieve the RSS feed
//XPathDocument doc = new PathDocument("http://msdn.microsoft.com/sql/rss.xml");
//XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/rss.xml");
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select("//item");
// create metadata for four columns
// three of them are string types and one of them is a datetime
SqlMetaData[] rss_results = new SqlMetaData[4];
rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
rss_results[1] = new SqlMetaData("Publication Date",
SqlDbType.DateTime);
rss_results[2] = new SqlMetaData("Description",
SqlDbType.NVarChar, 2000);
rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(rss_results);
// cache a SqlPipe instance to avoid repeated calls to
// SqlContext.GetPipe()
SqlPipe sqlpipe = SqlContext.Pipe;
// send the metadata, do not send the values in the data record
sqlpipe.SendResultsStart(record);
// for each xml node returned, extract four pieces
// of information and send back each item as a row
while (i.MoveNext())
{
record.SetString(0, (string)
i.Current.Evaluate("string(title[1]/text())"));
record.SetDateTime(1, DateTime.Parse((string)
i.Current.Evaluate("string(pubDate[1]/text())")));
record.SetString(2, (string)
i.Current.Evaluate("string(description[1]/text())"));
record.SetString(3, (string)
i.Current.Evaluate("string(link[1]/text())"));
sqlpipe.SendResultsRow(record);
}
// signal end of results
sqlpipe.SendResultsEnd();
}
}
public static void Get_ApiServiceData(string URL)
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(URL);
request.Method = "GET";
//request.ContentLength = 0;
//request.Credentials = CredentialCache.DefaultCredentials;
request.ContentType = "application/json";
request.Accept = "application/json";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Stream receiveStream = response.GetResponseStream();
StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);
string strContent = readStream.ReadToEnd();
DataSet data = JsonConvert.DeserializeObject <DataSet>(strContent);
if (data.Tables.Count > 0)
{
DataTable dt = data.Tables[0];
SqlPipe pipe = SqlContext.Pipe;
SqlMetaData[] cols = new SqlMetaData[dt.Columns.Count];
for (int col = 0; col < dt.Columns.Count; col++)
{
cols[col] = new SqlMetaData(dt.Columns[col].ColumnName, SqlDbType.NVarChar, 4000);
}
SqlDataRecord record = new SqlDataRecord(cols);
pipe.SendResultsStart(record);
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int col = 0; col < dt.Columns.Count; col++)
{
record.SetSqlString(col, new SqlString(Convert.ToString(dt.Rows[row][dt.Columns[col].ColumnName])));
}
pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
}
}
/// <summary>
/// Send the result back to the client
/// </summary>
/// <param name="dt"></param>
public static void PipeDataTable(DataTable dt)
{
SqlMetaData[] md = ExtractDataTableColumnMetaData(dt);
SqlDataRecord r = new SqlDataRecord(md);
SqlPipe p = SqlContext.Pipe;
//---------------------------------------------------------------------------------------
// First is invoked ‘SendResultsStart.’
// ‘SendResultStart’ marks the beginning of a result set to be sent back to the client,
// and uses the r parameter to construct the metadata that describes the result set
//---------------------------------------------------------------------------------------
p.SendResultsStart(r);
try
{
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < r.FieldCount; i++)
{
object v = row[i];
if (DBNull.Value != v)
{
v = v.ToString();
}
else
{
v = DBNull.Value;
}
r.SetValue(i, v);
}
p.SendResultsRow(r);
}
}
finally
{
//
// 'SendResultEnd’ marks the end of a result set and returns the SqlPipe instance to the initial state.
//
p.SendResultsEnd();
}
}
public static void ShredXml(SqlXml InputXml,
[SqlFacet(IsNullable = true), Optional] SqlByte AttributeElementHandling,
[SqlFacet(IsNullable = true), Optional] SqlByte ConversionHandling,
[SqlFacet(MaxSize = 255, IsNullable = true), Optional, DefaultParameterValue(null)] string RootElementName
)
{
//Assume the "AttributeElementHandling" value provided was valid (TODO: error-handling here)
AttributeElementHandlingPreference attributeElementHandling = AttributeElementHandlingPreference.DetectFromSchema;
if (!AttributeElementHandling.IsNull)
{
attributeElementHandling = (AttributeElementHandlingPreference)AttributeElementHandling.Value;
}
//Assume the "ConversionHandling" value provided was valid (TODO: error-handling here)
TypeConversionHandlingPreference conversionHandling = TypeConversionHandlingPreference.NoConversion;
if (!ConversionHandling.IsNull)
{
conversionHandling = (TypeConversionHandlingPreference)ConversionHandling.Value;
}
if (conversionHandling == TypeConversionHandlingPreference.AllConversion)
{
throw new Exception("Sorry, the \"All Conversion\" option has not been implemented yet! " +
"Are you sure you need it? (Standard SQL Server type precedence will auto-convert " +
"the NVarChar(Max) data to the original corresponding types without issue!)");
}
SqlPipe pipe = SqlContext.Pipe;
using (XmlReader inputReader = InputXml.CreateReader())
{
bool requestedRootFound = string.IsNullOrEmpty(RootElementName);
bool firstElementFound = false;
List <string> firstRowValues = null;
SqlMetaData[] outputColumns = null;
SqlDataRecord outputRecord = null;
string rowElementName = null;
while (inputReader.Read())
{
if (!requestedRootFound && inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals(RootElementName))
{
requestedRootFound = true;
continue;
}
if (requestedRootFound && inputReader.NodeType == XmlNodeType.EndElement && inputReader.Name.Equals(RootElementName))
{
requestedRootFound = false;
continue;
}
if (requestedRootFound)
{
if (inputReader.NodeType == XmlNodeType.Element && !firstElementFound)
{
if (inputReader.Name.Equals("xsd:schema"))
{
outputColumns = BuildColumnsFromSchema(inputReader, ref rowElementName, conversionHandling, ref attributeElementHandling);
}
else
{
if (attributeElementHandling == AttributeElementHandlingPreference.DetectFromSchema)
{
throw new Exception("Attribute/Element handling preference was set to \"Detect from Schema\", but " +
"no Schema is present in the Xml. Please specify 1 for Attribute-centric Xml or 2 for " +
"Element-centric Xml.");
}
if (conversionHandling == TypeConversionHandlingPreference.BinaryConversionOnly ||
conversionHandling == TypeConversionHandlingPreference.AllConversion)
{
throw new Exception("Conversion handling preference must be set to 0 (\"no conversion\") because " +
"there is no Schema in the provided Xml.");
}
if (attributeElementHandling == AttributeElementHandlingPreference.Elements)
{
outputColumns = BuildColumnsFromFirstRowElements(inputReader, ref firstRowValues, ref rowElementName);
}
else if (attributeElementHandling == AttributeElementHandlingPreference.Attributes)
{
outputColumns = BuildColumnsFromFirstRowAttributes(inputReader, ref firstRowValues, ref rowElementName);
}
else
{
throw new Exception("invalid option specified for Attribute/Element handling preference.");
}
}
outputRecord = new SqlDataRecord(outputColumns);
pipe.SendResultsStart(outputRecord);
if (firstRowValues != null)
{
for (int i = 0; i < firstRowValues.Count; i++)
{
SetRecordValueFromString(outputRecord, outputColumns, i, firstRowValues[i]);
}
pipe.SendResultsRow(outputRecord);
}
firstElementFound = true;
}
else if (inputReader.NodeType == XmlNodeType.Element && inputReader.Name.Equals(rowElementName))
{
if (attributeElementHandling == AttributeElementHandlingPreference.Elements)
{
FillRecordFromElements(inputReader, outputRecord, outputColumns, rowElementName);
}
else if (attributeElementHandling == AttributeElementHandlingPreference.Attributes)
{
FillRecordFromAttributes(inputReader, outputRecord, outputColumns);
}
pipe.SendResultsRow(outputRecord);
}
}
}
if (pipe.IsSendingResults)
{
pipe.SendResultsEnd();
}
}
}
public static SqlInt32 ExecuteDE(SqlString exe, SqlString arg, SqlInt32 timeout, SqlString options)
{
WindowsIdentity clientId = null;
//WindowsImpersonationContext impersonatedUser = null;
clientId = SqlContext.WindowsIdentity;
bool debug = options.ToString().Contains("debug");
OutputType output = OutputType.None;
if (!options.ToString().Contains("noutput"))
{
output = options.ToString().Contains("rowset") ? OutputType.Rowset : OutputType.Print;
}
int ret = 0;
try
{
Version v = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
SqlPipe pipe = SqlContext.Pipe;
if (output == OutputType.Rowset)
{
SqlDataRecord rec = new SqlDataRecord(m);
pipe.SendResultsStart(rec);
}
Send(pipe, String.Format("SqlClr ExecuteDE Version {0} Executing as {1}", v, clientId.Name), debug);
try
{
//impersonatedUser = clientId.Impersonate();
//if (impersonatedUser != null)
//{
// use this for impersonation
//}
using (DeProcess p = new DeProcess())
{
p.Timeout = timeout.Value;
p.haveOutput += delegate(object sender, HaveOutputEventArgs e)
{
Send(pipe, e.Output, (output != OutputType.None));
};
ret = p.Execute(exe.Value, arg.Value);
}
}
catch (Exception ex)
{
ret = 1;
Send(pipe, "SqlClr ExecuteDE Failed: " + ex.Message, true);
throw ex;
}
finally
{
//if (impersonatedUser != null)
// impersonatedUser.Undo();
Send(pipe, String.Format("SqlClr ExecuteDE Completed with ExitCode: {0}", ret), debug);
if (pipe.IsSendingResults)
{
pipe.SendResultsEnd();
}
}
}
catch
{
throw;
}
return(ret);
}
public static void GetPayRiseDates()
{
// Open the context connection
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
// Get a new SqlDataReader with the details of the
// employees who are due a pay rise
string sql = @"SELECT EmployeeID,
MAX(RateChangeDate) AS LastRateChange,
DATEADD(year, 2, MAX(RateChangeDate)) AS DueRateChange
FROM HumanResources.EmployeePayHistory
GROUP BY EmployeeID
HAVING MAX(RateChangeDate) < DATEADD(year, -2, GETDATE())";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader reader = cmd.ExecuteReader();
// Get the SqlPipe
SqlPipe pipe = SqlContext.Pipe;
// Create the SqlMetaData objects for the rowset
SqlMetaData idMeta = new SqlMetaData("Id", SqlDbType.Int);
SqlMetaData lastRaiseMeta = new SqlMetaData("Last Rate Change", SqlDbType.DateTime);
SqlMetaData dueRaiseMeta = new SqlMetaData("Due Rate Change", SqlDbType.DateTime);
SqlMetaData[] rowMetaData = new SqlMetaData[] { idMeta, lastRaiseMeta, dueRaiseMeta };
// Keep track of whether it's the first row or not
bool firstRow = true;
// Iterate through the rows, update if necessary,
// and send them back to the caller
while (reader.Read())
{
// Create a new SqlDataRecord for each row
SqlDataRecord row = new SqlDataRecord(rowMetaData);
// Add the ID and Last Rate Change values to the row
row.SetInt32(FIELD_ID, (int)reader[FIELD_ID]);
row.SetDateTime(FIELD_LASTCHANGE, (DateTime)reader[FIELD_LASTCHANGE]);
// Store the change due date in a local variable
DateTime dueDate = (DateTime)reader[FIELD_DUECHANGE];
// If it's over six months overdue, set pay rise for
// a month's time; otherwise, put it back seven months
if (dueDate < DateTime.Now.AddMonths(-6))
{
row.SetDateTime(FIELD_DUECHANGE, DateTime.Now.AddMonths(1));
}
else
{
row.SetDateTime(FIELD_DUECHANGE, dueDate.AddMonths(7));
}
// If it's the first row, we need to call
// SendResultsStart; otherwise, we call SendResultsRow
if (firstRow == true)
{
pipe.SendResultsStart(row);
firstRow = false;
}
else
{
pipe.SendResultsRow(row);
}
}
// Close the SqlDataReader once we've finished iterating through it
reader.Close();
// Call SendResultsEnd after the loop has finished, to
// say we're done
pipe.SendResultsEnd();
}
}
public static void SqlStoredProcedureJobs()
{
//remove Certificate policy - for POC scope
ServicePointManager.ServerCertificateValidationCallback =
((sender, certificate, chain, sslPolicyErrors) => true);
var jobsIDs = new List <int>();
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://localhost:44377/api/v1/jobs/xml"); //63227 - 44377
request.Method = "GET";
request.ContentLength = 0;
request.Credentials = CredentialCache.DefaultCredentials;
request.ContentType = "application/xml";
request.Accept = "application/xml";
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream receiveStream = response.GetResponseStream())
{
using (StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8))
{
string strContent = readStream.ReadToEnd();
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(strContent);
SqlPipe pipe = SqlContext.Pipe;
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("ID", SqlDbType.Int);
for (int i = 0; i < xdoc.ChildNodes.Count; i++)
{
int id = int.Parse(xdoc.ChildNodes[i].Attributes["ID"].Value);
if (id == 0)
{
break;
}
jobsIDs.Add(id);
}
pipe.SendResultsEnd();
}
}
}
foreach (int jobid in jobsIDs)
{
HttpWebRequest processRequest = (HttpWebRequest)WebRequest.Create($"http://localhost:63227/api/v1/directprocess/processjob/?jobId={jobid}");
processRequest.Method = "GET";
processRequest.ContentLength = 0;
processRequest.Credentials = CredentialCache.DefaultCredentials;
processRequest.ContentType = "application/xml";
processRequest.Accept = "application/xml";
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream receiveStream = response.GetResponseStream())
{
using (StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8))
{
string strContent = readStream.ReadToEnd();
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(strContent);
for (int i = 0; i < xdoc.ChildNodes.Count; i++)
{
int id = int.Parse(xdoc.ChildNodes[i].Attributes["ID"].Value);
jobsIDs.Add(id);
}
}
}
}
}
}
/// <summary>
/// Per each type combine flat SELECT fields by real names in Metrics
/// </summary>
/// <param name="accountId"></param>
/// <param name="stagingTableName"></param>
/// <param name="connection"></param>
/// <param name="pipe">Pipe to send SQL rows reply</param>
public static void GetObjectsView(int accountId, string stagingTableName, SqlConnection connection, SqlPipe pipe)
{
// load configuration
var edgeTypes = EdgeObjectConfigLoader.LoadEdgeTypes(accountId, connection);
var edgeFields = EdgeObjectConfigLoader.LoadEdgeFields(accountId, edgeTypes, connection);
EdgeObjectConfigLoader.SetEdgeTypeEdgeFieldRelation(accountId, edgeTypes, edgeFields, connection);
var fieldsMap = LoadStageFields(stagingTableName, edgeFields, edgeTypes.Values.ToList(), connection);
// prepare result record
var record = new SqlDataRecord(new[]
{
new SqlMetaData("TypeID", SqlDbType.Int),
new SqlMetaData("Name", SqlDbType.NVarChar, 50),
new SqlMetaData("FieldList", SqlDbType.NVarChar, 1000),
new SqlMetaData("Select", SqlDbType.NVarChar, 1000)
});
pipe.SendResultsStart(record);
foreach (var type in edgeTypes.Values.Where(x => x.IsAbstract == false))
{
// prepare type fields SELECT
var fieldsStr = String.Empty;
foreach (var field in type.Fields)
{
fieldsStr = String.Format("{0}{1} AS {2}, ", fieldsStr, field.ColumnNameGK.StartsWith("obj") ? String.Format("COALESCE({0},-1)", field.ColumnNameGK) :
field.ColumnNameGK, field.FieldNameGK);
if (field.Field.FieldEdgeType == null)
{
continue;
}
// add to select all options of child edge types
foreach (var childType in EdgeObjectConfigLoader.FindEdgeTypeInheritors(field.Field.FieldEdgeType, edgeTypes))
{
if (childType == field.Field.FieldEdgeType)
{
continue;
}
fieldsStr = String.Format("{0}{1} AS {2}_{3}_gk, ", fieldsStr, field.ColumnNameGK.StartsWith("obj") ? String.Format("COALESCE({0},-1)", field.ColumnNameGK) :
field.ColumnNameGK, field.Field.Name, childType.Name);
}
}
if (fieldsStr.Length <= 0)
{
continue;
}
fieldsStr = fieldsStr.Remove(fieldsStr.Length - 2, 2);
var select = String.Format("SELECT GK,AccountID,RootAccountId,ChannelID,CreatedOn,LastUpdatedOn,{0} FROM {1} WHERE TYPEID={2}", fieldsStr, type.TableName, type.TypeID);
// set report and and it
record.SetInt32(0, type.TypeID);
record.SetString(1, type.Name);
record.SetString(2, fieldsMap.ContainsKey(type.TypeID) ? String.Join(",", fieldsMap[type.TypeID]) : "");
record.SetString(3, select);
pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
}
public static void LogEntries(string logtype, Int32 count)
{
string beginMsg = "Logtype must be either Application or System";
bool canProcess = false;
if (logtype.ToLower() == "application" ||
logtype.ToLower() == "system")
{
beginMsg = "Using log type: " + logtype;
canProcess = true;
}
using (SqlConnection dbConnection =
new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
SqlDataRecord record;
pipe.Send(beginMsg);
if (canProcess)
{
SqlMetaData[] metadata = new SqlMetaData[7];
metadata[0] = new SqlMetaData("Index", SqlDbType.Int);
metadata[1] = new SqlMetaData("ID", SqlDbType.BigInt);
metadata[2] = new SqlMetaData("TimeWritten", SqlDbType.DateTime);
metadata[3] = new SqlMetaData("MachineName", SqlDbType.NVarChar, 256);
metadata[4] = new SqlMetaData("Source", SqlDbType.NVarChar, 256);
metadata[5] = new SqlMetaData("UserName", SqlDbType.NVarChar, 256);
metadata[6] = new SqlMetaData("Message", SqlDbType.NVarChar, -1);
EventLog eLog = new EventLog(logtype);
int logLength = eLog.Entries.Count;
if (count == 0 || count > logLength)
{
count = logLength;
}
pipe.Send("Processing " + count.ToString() + " of "
+ logLength.ToString() + "entries");
for (int i = logLength - 1; i >= 0; i--)
{
record = buildRecord(metadata, eLog.Entries[i]);
if (i == logLength - 1)
{
pipe.SendResultsStart(record);
}
else
{
pipe.SendResultsRow(record);
}
if (count-- < 1)
{
break;
}
}
pipe.SendResultsEnd();
}
}
}
public static void GetSPList(SqlString SPUrl, SqlString list_name)
{
SPWebService WebService = new SPWebService(SPUrl.ToString());
SPList MyList = WebService.GetSPList(list_name.ToString());
List <SqlMetaData> Cols = new List <SqlMetaData>();
//
// The logic is to iterate through each ColumDef from the SharePoint list and add a corresponding
// column metadata for the result set to SQL Server. Basically, this loop matches up data types
// instead of having the SQL resultset have all columns with varchar (which probably would have been fine
// for most purposes)
//
foreach (ColumnDef CD in MyList.GetColumnDefs())
{
switch (CD.ColType.ToUpper())
{
case "TEXT":
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.NVarChar, CD.ColLen));
break;
case "NUMBER":
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Decimal, 18, 5));
break;
case "CURRENCY":
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Money));
break;
case "DATETIME":
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.DateTime2));
break;
case "BOOLEAN":
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.Bit));
break;
default:
Cols.Add(new SqlMetaData(CD.ColName, SqlDbType.NVarChar, 4000));
break;
}
}
SqlPipe pipe = SqlContext.Pipe;
SqlDataRecord rec = new SqlDataRecord(Cols.ToArray());
pipe.SendResultsStart(rec);
int i;
decimal tempdec;
DateTime tempdt;
//
// The logic here is similar to the columns above, but it is being done for each row
// in the result set. The list data comes out of the webservice as XML which I left as strings.
// For each row, it iterates through each column and compares what the datatype is in SharePoint (ColumnDef)
// and does a conversion for the resultset
//
foreach (var listrow in MyList.GetRows())
{
i = -1;
foreach (ColumnDef CD in MyList.GetColumnDefs())
{
i++;
tempdec = 0;
switch (CD.ColType.ToUpper())
{
case "TEXT":
rec.SetSqlString(i, listrow[i]);
break;
case "NUMBER":
decimal.TryParse(listrow[i], out tempdec);
rec.SetSqlDecimal(i, tempdec);
break;
case "CURRENCY":
decimal.TryParse(listrow[i], out tempdec);
rec.SetSqlMoney(i, tempdec);
break;
case "DATETIME":
if (!DateTime.TryParse(listrow[i], out tempdt))
{
tempdt = new DateTime(1900, 1, 1);
}
rec.SetSqlDateTime(i, tempdt);
break;
case "BOOLEAN":
if (listrow[i].Equals("1"))
{
rec.SetSqlBoolean(i, true);
}
else
{
rec.SetSqlBoolean(i, false);
}
break;
default:
rec.SetSqlString(i, listrow[i]);
break;
}
}
pipe.SendResultsRow(rec);
}
pipe.SendResultsEnd();
}
public static void sp_help2(string Name, string ShowType)
{
string definition;
string database;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
//DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Item", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("Description", System.Type.GetType("System.String")));
SqlCommand qry = new SqlCommand();
if (ShowType.ToUpper() != "T" && ShowType.ToUpper() != "S")
{
throw new System.ArgumentException("Invalid parameter value, valid values are T=Table Format, S=String Format", "ShowType");
}
SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);
qry.Parameters.Add(nameParam);
nameParam.Value = Name;
qry.Connection = conn;
qry.CommandText = @"
declare @db varchar(128);
select @db = db_name(resource_database_id)
from sys.dm_tran_locks
where request_session_id = @@spid
and resource_type = 'database'
and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE';
select dbname=isnull(@db, db_name());
" ;
conn.Open();
database = (string)qry.ExecuteScalar();
qry.CommandText = "use [" + database + "]; \r\n";
qry.CommandText += "select isnull(definition, '') from sys.sql_modules where object_id=object_id(@Name);";
definition = (String)qry.ExecuteScalar();
if (string.IsNullOrEmpty(definition))
{
definition = string.Format("[{0}] not found in database [{1}] ", Name, database);
}
else
{
Dictionary <string, string> dict = new Dictionary <string, string>();
// initialize the first record
dict.Add("Name", Name + "\r\n");
string patt = @"(?s)/\*.+?\*/";
Regex rex = new Regex(patt);
Match m = rex.Match(definition);
// follwing is the key word list, and can be modified to meet your own requirement
patt = @"(?i)^\s*\.(function|parameter|example|CreatedBy|CreatedOn|ModifiedBy|Modification|Note):?$";
rex = new Regex(patt);
string key = string.Empty;
string value = m.Value.Replace("/*", "");
value = value.Replace("*/", "");
if (!string.IsNullOrEmpty(value))
{
using (StringReader reader = new StringReader(value))
{
string line = string.Empty;
while ((line = reader.ReadLine()) != null)
{
line = line.Trim();
if (string.IsNullOrEmpty(line))
{
continue;
}
if (line.Length >= 2 && line.Substring(0, 2) == "--") //check if the line is commented out
{
continue;
}
if (rex.IsMatch(line))
{
var mt = rex.Match(line);
key = mt.Groups[1].Value;
if (!dict.ContainsKey(key))
{
dict.Add(key, "");
}
}
else
{
if (!String.IsNullOrEmpty(key))
{
dict[key] += line + Environment.NewLine;
}
}
}
}
if (dict.Count > 0)
{
StringBuilder sb = new StringBuilder("");
// populate definition with dict values
foreach (var v in dict)
{
sb.AppendLine(v.Key);
sb.AppendLine(v.Value);
//sb.AppendLine("");
DataRow dr = dt.NewRow();
dr["Item"] = v.Key;
dr["Description"] = v.Value;
dt.Rows.Add(dr);
}
definition = sb.ToString();
}
}
}
conn.Close();
if (ShowType == "S")
{
SqlContext.Pipe.Send(definition);
}
else
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("item", SqlDbType.VarChar, 4000),
new SqlMetaData("description", SqlDbType.VarChar, 4000));
SqlPipe pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
try
{
foreach (DataRow row in dt.Rows)
{
for (int index = 0; index < record.FieldCount; index++)
{
string value = row[index].ToString();
record.SetValue(index, value);
}
pipe.SendResultsRow(record);
}
}
finally
{
pipe.SendResultsEnd();
}
}
}
}