private DataTableReader GetData(string query, Properties props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
return(results.CreateDataReader());
}
//sample: joining two List methods with a JOIN operator, filtering and returnign TOP N items
static void ExecuteSelectStatementJoin()
{
Console.WriteLine("**Executing SmartObject list method with the ADO Provider SELECT statement");
Console.WriteLine("(return top 20 active workflow instances, join to Activity instances)**");
using (SOConnection soServerConnection = EstablishK2Connection())
{
soServerConnection.Open();
//build up the SQL-92 Query. Notice the user of System Names, not display names
//you can filter with normal WHERE clauses
string query = "SELECT TOP 20 * FROM Process_Instance JOIN Activity_Instance ON Process_Instance.ProcessInstanceID = Activity_Instance.ProcessInstanceID WHERE Process_Instance.Status = 'Active'";
using (SOCommand command = new SOCommand(query, soServerConnection))
{
//in this sample we'll use a Data Adpater so we can fill a datatable
using (SODataAdapter adapter = new SODataAdapter(command))
{
soServerConnection.DirectExecution = true; //direct execution will show performance improvements for SQL-based smartobjects
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("Folio: " + dr["Folio"].ToString()
+ " | ProcInstID: " + dr["ProcessInstanceID"].ToString()
+ " | ActivityName: " + dr["ActivityName"].ToString());
}
}
}
}
Console.WriteLine("**Completed SmartObject list method JOIN with the ADO.NET provider**");
Console.ReadLine();
}
public static DataTableReader GetData(string connStr, string query, Properties props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(connStr))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
return results.CreateDataReader();
}
private void ADOQuery2Excel()
{
ServiceObject serviceObject = ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
string fileName = GetStringProperty(Constants.SOProperties.ExportToExcel.FileName, true);
string query = GetStringProperty(Constants.SOProperties.ExportToExcel.ADOQuery, true);
DataTable SOQueryResult = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
adapter.Fill(SOQueryResult);
}
DataRow dr = results.NewRow();
//Calling the helper method with dataresult and expecting a File in return.
CreateExcel excel = new CreateExcel();
dr[Constants.SOProperties.ExportToExcel.ExcelFile] = excel.ConvertDataTable2Excelfile(SOQueryResult, fileName).ToString();
results.Rows.Add(dr);
}
private void ExecuteAdoQuery()
{
var adoQuery = GetStringParameter(Constants.Parameters.AdoQuery);
SoDefinition soDefinition =
SoDefCollection.GetSoDefinitionByName(ServiceBroker.Service.ServiceObjects[0].Name);
ServiceBroker.Service.ServiceObjects[0].Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
DataTable dataTable = new DataTable();
using (SOConnection soConnection = new SOConnection(ServiceBroker.K2Connection.GetSOConnectionString()))
using (SOCommand command = new SOCommand(adoQuery, soConnection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
soConnection.Open();
adapter.Fill(dataTable);
}
foreach (DataRow dr in dataTable.Rows)
{
DataRow dRow = results.NewRow();
foreach (var prop in soDefinition.Properties)
{
dRow[prop.Name] = dr[prop.Name];
}
results.Rows.Add(dRow);
}
}
//sample: executing a scalar method (Read) with the ADO.NET provider
static void ExecuteScalarReadStatement()
{
Console.WriteLine("**executing a scalar READ method with the ADO.NET provider");
Console.WriteLine("(get display name and email for current user)**");
using (SOConnection soServerConnection = EstablishK2Connection())
{
//constrcut the current username (assuming the K2 label and current user id)
string currentUserFQN = "K2:" + System.Security.Principal.WindowsIdentity.GetCurrent().Name;
soServerConnection.Open();
//EXEC a scalar method with an input paramter
string query = @"EXEC [Users_and_Groups.Get_E_mail_For_User] @User_Fully_Qualified_Name = '" + currentUserFQN + "'";
using (SOCommand command = new SOCommand(query, soServerConnection))
//in this sample we'll use a SQL data reader to read the records one by one
using (SODataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
Console.WriteLine("Display Name: " + reader["Display_Name"].ToString()
+ " Email: " + reader["E_mail"].ToString());
}
Console.WriteLine("**completed executing the scalar READ method with the ADO.NET provider**");
Console.ReadLine();
}
}
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
//在information_schema数据库中没有找到存储存储过程参数的表，可以考虑从存储过程DDL脚本解析出来
string cmdText = string.Format(@"use {0};select a.name,b.name as typename,a.length,a.status from syscolumns a
LEFT JOIN systypes b on a.xtype=b.xtype
where ID in (SELECT id FROM sysobjects as a WHERE xtype='P' and id = object_id(N'{1}')) ", command.Parent.Database.Name, command.Name);
List <SOCommandParameter> commandParList = new List <SOCommandParameter>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOCommandParameter comParameter = new SOCommandParameter {
Parent = command,
Name = row["name"].ToString(),
Length = int.Parse(row["length"].ToString()),
NativeType = row["typename"].ToString(),
};
int status = int.Parse(row["status"].ToString());
if (status == 8)
{
comParameter.Direction = ParameterDirection.Input;
}
else if (status == 72)
{
comParameter.Direction = ParameterDirection.Output;
}
commandParList.Add(comParameter);
}
return(commandParList);
}
public static DataTable GetSchema(string connStr, string query, Dictionary<string,string> props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(connStr))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (KeyValuePair<string,string> prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Key, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.FillSchema(results, SchemaType.Source);
}
}
connection.Close();
}
return results;
}
private DataTable GetSchema(string query, Dictionary <string, string> props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (KeyValuePair <string, string> prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Key, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.FillSchema(results, SchemaType.Source);
}
}
connection.Close();
}
return(results);
}
private void Join()
{
string query = GetStringProperty(Constants.SOProperties.ADOHelper.ADOQuery, true);
string delimiter = GetStringProperty(Constants.SOProperties.ADOHelper.Delimiter, true);
ServiceObject serviceObject = base.ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = base.ServiceBroker.ServicePackage.ResultTable;
DataTable adoResults = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
connection.Open();
adapter.Fill(adoResults);
}
string result = "";
foreach (DataRow dRow in adoResults.Rows)
{
result += dRow[0].ToString() + delimiter;
}
result = result.Remove(result.LastIndexOf(delimiter));
DataRow resultsRow = results.NewRow();
resultsRow[Constants.SOProperties.ADOHelper.Result] = result;
results.Rows.Add(resultsRow);
}
private void ListQuery()
{
ServiceObject serviceObject = base.ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = base.ServiceBroker.ServicePackage.ResultTable;
string query = serviceObject.Methods[0].MetaData.GetServiceElement <string>("Query");
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in serviceObject.Properties)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
}
//查看存储过程定义
private void menuItemSpSql_Click(object sender, EventArgs e)
{
TreeNode tn = tvDatabase.SelectedNode;
SOCommand p = tn.Tag as SOCommand;
base.MainForm.NewDockDocument(p.Name, CodeType.TSQL, p.SqlText);
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override string GetCommandSqlText(SOCommand command)
{
string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_schema='{0}' AND routine_type='PROCEDURE' AND routine_catalog='{1}' AND routine_name='{2}';",
command.Owner ?? "dbo", command.Database.Name, command.Name);
string text = this.DbProvider.ExecuteScalar(System.Data.CommandType.Text, cmdText).ToString();
return(text);
}
private void BuildCodeBySPSchema(object item)
{
SOCommand sp = item as SOCommand;
//List<SOCommandParameter> paramList = DbSchemaHelper.Instance.CurrentSchema.GetCommandParameterList(sp);
//生成代码文件
CommandHost host = new CommandHost();
host.SP = sp;
//host.ParamList = paramList;
host.TemplateFile = templateFile;
foreach (object obj in listBox3.Items)
{
string[] ss = obj.ToString().Split('|');
host.SetValue(ss[0], ss[1].Replace("[<->]", "|"));
}
Engine engine = new Engine();
string fileName = string.Empty;
string separator = txtFileNamePrefix.Text.Trim();
if (separator != "")
{
fileName = string.Format("{0}{1}", sp.Name.RemovePrefix(separator, 10), host.FileExtention);
}
else
{
fileName = string.Format("{0}{1}", sp.Name, host.FileExtention);
}
string outputContent = engine.ProcessTemplate(File.ReadAllText(templateFile), host);
string outputFile = Path.Combine(outputPath, fileName);
StringBuilder sb = new StringBuilder();
if (host.ErrorCollection.HasErrors)
{
foreach (CompilerError err in host.ErrorCollection)
{
sb.AppendLine(err.ToString());
}
outputContent = outputContent + Environment.NewLine + sb.ToString();
outputFile = outputFile + ".error";
}
if (Directory.Exists(outputPath) == false)
{
Directory.CreateDirectory(outputPath);
}
File.WriteAllText(outputFile, outputContent, Encoding.UTF8);
}
private void FilesToZipMethod()
{
ServiceObject serviceObject = ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
string fileName = GetStringProperty(Constants.SOProperties.FilesToZip.FileName, true);
string query = GetStringProperty(Constants.SOProperties.FilesToZip.ADOSMOQuery, true);
DataTable SOQueryResult = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
adapter.Fill(SOQueryResult);
}
string xmlZipFile = string.Empty;
if (SOQueryResult.Rows.Count > 0)
{
using (var memoryStream = new MemoryStream())
{
using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
{
foreach (DataRow row in SOQueryResult.Rows)
{
XElement fileXml = XElement.Parse(row[0].ToString());
var demoFile = archive.CreateEntry(fileXml.Element("name").Value);
using (var entryStream = demoFile.Open())
using (var b = new BinaryWriter(entryStream))
{
b.Write(Convert.FromBase64String(fileXml.Element("content").Value));
}
}
}
string content = Convert.ToBase64String(memoryStream.ToArray());
xmlZipFile = string.Format("<file><name>{0}.zip</name><content>{1}</content></file>", fileName, content);
}
}
DataRow dr = results.NewRow();
//Calling the helper method with dataresult and expecting a File in return.
CreateExcel excel = new CreateExcel();
dr[Constants.SOProperties.FilesToZip.ZipFile] = xmlZipFile;
results.Rows.Add(dr);
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public virtual List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
List <SOCommandParameter> list = new List <SOCommandParameter>();
string[] restrictions = new string[4];
restrictions[0] = command.Database.Name;
restrictions[2] = command.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Parameters, restrictions);
///todo:转换数据
return(list);
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override string GetCommandSqlText(SOCommand command)
{
string cmdText = string.Format(@"select TEXT from dba_source where type='PROCEDURE' and owner='{0}' and name='{1}' order by line", command.Database.Name, command.Name);
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
StringBuilder sb = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
sb.AppendLine(row["TEXT"].ToString());
}
return(sb.ToString());
}
// /// <summary>
// /// 获取视图所拥有的索引列表
// /// </summary>
// /// <param name="view"></param>
// /// <returns></returns>
// public override List<SOIndex> GetViewIndexList(SOView view)
// {
// string cmdText = string.Format(@"SELECT *
// FROM INFORMATION_SCHEMA.`constraints`
// WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);
// List<SOIndex> indexList = new List<SOIndex>();
// List<SOColumn> columnList = GetViewColumnList(view);
// DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
// foreach (DataRow row in dt.Rows)
// {
// SOIndex index = new SOIndex
// {
// Parent = view,
// Name = row["constraint_name"].ToString(),
// Comment = row["constraint_name"].ToString(),
// IsCluster = false,
// IsFullText = row["constraint_type"].ToString() == "Full Text",
// IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
// IsUnique = row["constraint_type"].ToString() == "UNIQUE"
// };
// indexList.Add(index);
// string cmdText2 = string.Format(@"SELECT column_name
// FROM INFORMATION_SCHEMA.`statistics`
// WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);
// DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
// index.Columns = new List<SOColumn>();
// foreach (DataRow row2 in dt2.Rows)
// {
// foreach (SOColumn column in columnList)
// {
// if (row2[0].ToString() == column.Name) index.Columns.Add(column);
// }
// }
// }
// return indexList;
// }
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List <SOCommand> GetCommandList(SODatabase db)
{
string cmdText = string.Format(@"use [{0}];SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_catalog='{0}' AND routine_type='PROCEDURE';", db.Name);
List <SOCommand> commandList = new List <SOCommand>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOCommand command = new SOCommand {
Parent = db, Name = row["routine_name"].ToString(), Comment = row["routine_name"].ToString()
};
commandList.Add(command);
}
return(commandList);
}
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List <SOCommand> GetCommandList(SODatabase db)
{
string cmdText = string.Format(@"select PROCEDURE_NAME from dba_procedures where owner='{0}'", db.Name);
List <SOCommand> commandList = new List <SOCommand>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOCommand command = new SOCommand {
Parent = db, Name = row["PROCEDURE_NAME"].ToString(), Comment = row["PROCEDURE_NAME"].ToString()
};
commandList.Add(command);
}
return(commandList);
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
string cmdText = string.Format(@"select * from all_arguments where owner='{0}' and package_name={1} order by position", command.Database.Name, command.Name);
if (!IsDBA)
{
cmdText = string.Format(@"select * from all_arguments where owner='{0}' and package_name={1} order by position", command.Database.Name, command.Name);
}
List <SOCommandParameter> columnList = new List <SOCommandParameter>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
ParameterDirection direction = ParameterDirection.ReturnValue;
string pmode = row["IN_OUT"].ToString();
if (pmode == "IN")
{
direction = ParameterDirection.Input;
}
if (pmode == "OUT")
{
direction = ParameterDirection.Output;
}
if (pmode == "IN/OUT")
{
direction = ParameterDirection.InputOutput;
}
SOCommandParameter param = new SOCommandParameter
{
Parent = command,
Name = row["ARGUMENT_NAME"].ToString(),
Direction = direction,
NativeType = row["DATA_TYPE"].ToString(),
Length = ConvertUtil.ToInt32(row["CHAR_LENGTH"], 0),
Precision = ConvertUtil.ToInt32(row["DATA_PRECISION"], 0),
Scale = ConvertUtil.ToInt32(row["DATA_SCALE"], 0),
};
param.DataType = this.GetDbType(param.NativeType);
columnList.Add(param);
}
return(columnList);
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
string cmdText = string.Format(@"USE [{1}];SELECT routine_definition FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_schema='{0}' AND SPECIFIC_type='PROCEDURE' AND SPECIFIC_catalog='{1}' AND SPECIFIC_name='{2}';",
command.Owner ?? "dbo", command.Database.Name, command.Name);
List <SOCommandParameter> columnList = new List <SOCommandParameter>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
ParameterDirection direction = ParameterDirection.ReturnValue;
string pmode = row["PARAMETER_MODE"].ToString();
if (pmode == "IN")
{
direction = ParameterDirection.Input;
}
if (pmode == "OUT")
{
direction = ParameterDirection.Output;
}
if (pmode == "INOUT")
{
direction = ParameterDirection.InputOutput;
}
SOCommandParameter param = new SOCommandParameter
{
Parent = command,
Name = row["PARAMETER_NAME"].ToString(),
Direction = direction,
NativeType = row["DATA_TYPE"].ToString().Replace(" identity", ""),
Length = ConvertUtil.ToInt32(row["CHARACTER_OCTET_LENGTH"], -1),
Precision = ConvertUtil.ToInt32(row["NUMERIC_PRECISION"], -1),
Scale = ConvertUtil.ToInt32(row["NUMERIC_SCALE"], -1),
};
param.DataType = this.GetDbType(param.NativeType);
columnList.Add(param);
}
return(columnList);
}
/// <summary>
/// 获取存储过程列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public virtual List <SOCommand> GetCommandList(SODatabase db)
{
List <SOCommand> list = new List <SOCommand>();
string[] restrictions = new string[4];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Procedures, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOCommand cmd = new SOCommand();
cmd.Name = dr["routine_name"].ToString();
cmd.Comment = cmd.Name;
cmd.Parent = db;
list.Add(cmd);
}
return(list);
}
public void GetAllValues(Dictionary <string, string> inputIds, EmailTemplateServiceBroker sb)
{
if (Items.Count == 0)
{
return;
}
using (SOConnection soConnection = new SOConnection(sb.K2Connection.GetSOConnectionString()))
{
soConnection.DirectExecution = true;
soConnection.Open();
foreach (var p in Items)
{
var query = p.AdoQuery;
if (query.ToLower().StartsWith("delete ") || query.ToLower().StartsWith("update "))
{
throw new ArgumentException(string.Format(Resources.QueryCannotStartDeleteUpdate, query));
}
foreach (var item in inputIds)
{
var searchValue = Wrapper + item.Key + Wrapper;
query = query.Replace(searchValue, item.Value);
}
using (SOCommand soCommand = new SOCommand(query, soConnection))
using (SODataReader soReader = soCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
if (soReader.HasRows)
{
while (soReader.Read())
{
p.Value = soReader[p.ReturnProperty].ToString();
}
}
}
}
}
}
/// <summary>
/// Smartobject ADO query
/// </summary>
/// <param name="sql">SQL statment to execute </param>
/// <param name="parameters">query paramters, if used then command type of StoredProcedure is used</param>
/// <param name="directExecution">Will this query use direct execution</param>
/// <returns>A Datatable of results </returns>
public DataTable SmartObjectADOQuery(string sql, Dictionary<string, object> parameters, bool directExecution)
{
SOCommand command = null;
DataTable dataTable = null;
try
{
command = new SOCommand();
command.Connection = new SOConnection(this.SMOServer.Connection.Host, int.Parse(this.SMOServer.Connection.Port.ToString()));
command.Connection.DirectExecution = directExecution;
command.Connection.Port = 5555;
command.CommandText = sql;
if (parameters.Count > 0)
{
command.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> item in parameters)
{
command.Parameters.Add(new SOParameter(item.Key, item.Value.ToString()));
}
}
SODataAdapter adapter = new SODataAdapter(command);
dataTable = new DataTable();
adapter.Fill(dataTable);
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
}
return dataTable;
}
private DataTable GetData(string query, Properties props, bool schemaOnly)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
if (schemaOnly)
{
adapter.FillSchema(results, SchemaType.Source);
}
else
{
adapter.Fill(results);
}
}
}
connection.Close();
}
return results;
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public virtual string GetCommandSqlText(SOCommand command)
{
return("该方法目前还没有实现");
}
public static SOCommand ToSOCommand(PDProcedure sp)
{
SOCommand cmd = new SOCommand();
return(cmd);
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
//在information_schema数据库中没有找到存储存储过程参数的表，可以考虑从存储过程DDL脚本解析出来
throw new NotImplementedException();
}
/// <summary>
/// 获取存储过程的Sql脚本
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public override string GetCommandSqlText(SOCommand command)
{
throw new NotImplementedException();
}
public DbSPViewer(SOCommand sp)
{
InitializeComponent();
currentSP = sp;
}
public override List <SOCommandParameter> GetCommandParameterList(SOCommand command)
{
return(new List <SOCommandParameter>());
}
