| public static ExecuteXmlReader ( SqlConnection connection, CommandType commandType, string commandText ) : XmlReader | ||
| connection | SqlConnection | a valid SqlConnection |
| commandType | CommandType | the CommandType (stored procedure, text, etc.) |
| commandText | string | the stored procedure name or T-SQL command using "FOR XML AUTO" |
| 리턴 | XmlReader | |
private XmlNode importXml()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(SqlHelper.ExecuteXmlReader("select xml from cmsContentXml where nodeID = " + this.Id.ToString()));
return(xmlDoc.FirstChild);
}
//---------------------------------------------------------------------------------------
public static XmlReader GetXML(string SPName, params SqlParameter[] SqlPrms)
{
XmlReader XmlR = SqlHelper.ExecuteXmlReader(
new SqlConnection(Utility.SessionManager.ConnectionString.ToString()),
CommandType.StoredProcedure,
SPName,
SqlPrms);
return(XmlR);
}
private void cmdSample6_Click(object sender, System.EventArgs e)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = GetConnection(txtConnectionString.Text);
}
catch
{
MessageBox.Show("The connection with the database can´t be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Call ExecuteXmlReader static method of SqlHelper class that returns an XmlReader
// We pass in an open database connection object, command type, and command text
XmlReader xreader = SqlHelper.ExecuteXmlReader(connection, CommandType.Text, "SELECT * FROM Products FOR XML AUTO ");
// read the contents of xml reader and populate the results text box:
txtResults.Clear();
while (!xreader.EOF)
{
if (xreader.IsStartElement())
{
txtResults.Text += xreader.ReadOuterXml() + Environment.NewLine;
}
}
// close XmlReader
xreader.Close();
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
MessageBox.Show(string.Format("There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
+ Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the Install Quickstart option on the Start menu.", errMessage),
"Application error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public override SupplierLicense Get(int id)
{
using (SqlConnection connection = (SqlConnection)_dataSource.Value.DbConnection)
{
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@id", SqlDbType.Int);
parameters[0].Value = id;
parameters[1] = new SqlParameter("@ReturnValue", SqlDbType.Int);
parameters[1].Direction = ParameterDirection.ReturnValue;
XmlReader reader = SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, "GetSupplierLicenseXml_XX", parameters);
return(Deserialize(reader));
}
}
/// <summary>
/// An Xmlrepresentation of a Content object.
/// </summary>
/// <param name="xd">Xmldocument context</param>
/// <param name="Deep">If true, the Contents children are appended to the Xmlnode recursive</param>
/// <returns>The Xmlrepresentation of the data on the Content object</returns>
public override XmlNode ToXml(XmlDocument xd, bool Deep)
{
if (_xml == null)
{
XmlDocument xmlDoc = new XmlDocument();
// we add a try/catch clause here, as the xmlreader will throw an exception if there's no xml in the table
// after the empty catch we'll generate the xml which is why we don't do anything in the catch part
try
{
XmlReader xr = SqlHelper.ExecuteXmlReader("select xml from cmsContentXml where nodeID = " + this.Id.ToString());
if (xr.MoveToContent() != System.Xml.XmlNodeType.None)
{
xmlDoc.Load(xr);
_xml = xmlDoc.FirstChild;
}
xr.Close();
}
catch
{
}
// Generate xml if xml still null (then it hasn't been initialized before)
if (_xml == null)
{
this.XmlGenerate(new XmlDocument());
_xml = importXml();
}
}
XmlNode x = xd.ImportNode(_xml, true);
if (Deep)
{
var childs = this.Children;
foreach (BusinessLogic.console.IconI c in childs)
{
try
{
x.AppendChild(new Content(c.Id).ToXml(xd, true));
}
catch (Exception mExp)
{
System.Web.HttpContext.Current.Trace.Warn("Content", "Error adding node to xml: " + mExp.ToString());
}
}
}
return(x);
}
protected virtual XmlNode GetPreviewXml(XmlDocument xd, Guid version)
{
XmlDocument xmlDoc = new XmlDocument();
using (XmlReader xmlRdr = SqlHelper.ExecuteXmlReader(
"select xml from cmsPreviewXml where nodeID = @nodeId and versionId = @versionId",
SqlHelper.CreateParameter("@nodeId", Id),
SqlHelper.CreateParameter("@versionId", version)))
{
xmlDoc.Load(xmlRdr);
}
return(xd.ImportNode(xmlDoc.FirstChild, true));
}
/// <summary>
/// 查询返回Xml，带parms
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parms">参数</param>
/// <returns>XmlReader对象</returns>
public XmlReader QueryXml(string sql, SqlParameter[] parms)
{
try
{
return(SqlHelper.ExecuteXmlReader(this.sqlConnectionString, CommandType.Text, sql, parms));
}
catch (Exception ex)
{
return(null);
}
finally
{
this.Close();
}
}
/// <summary>
/// Execute a stored procedure that takes parameters and returns a XmlReader.
/// </summary>
/// <param name="sSPName">The name of stored procedure</param>
/// <param name="aParameters">An array of SqlParameters to be assigned as the input values of the stored procedure</param>
/// <returns>XmlReader object</returns>
public XmlReader ExecXmlReaderbySP(string sSPName, params SqlParameter[] aParameters)
{
try
{
if (_oTransaction == null)
{
return(SqlHelper.ExecuteXmlReader(_oConnection, CommandType.StoredProcedure, sSPName, aParameters));
}
else
{
return(SqlHelper.ExecuteXmlReader(_oTransaction, CommandType.StoredProcedure, sSPName, aParameters));
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Execute a stored procedure that takes object parameters and returns a XmlReader.
/// </summary>
/// <param name="sSPName">The name of stored procedure</param>
/// <param name="aParameters">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>XmlReader object</returns>
public XmlReader ExecXmlReaderbySP(string sSPName, params object[] aParameters)
{
try
{
if (_oTransaction == null)
{
return(SqlHelper.ExecuteXmlReader(_oConnection, sSPName, aParameters));
}
else
{
return(SqlHelper.ExecuteXmlReader(_oTransaction, sSPName, aParameters));
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Execute a query that takes parameters and returns a XmlReader.
/// </summary>
/// <param name="sQuery">Query string</param>
/// <param name="cmdParameters">Array of parameters</param>
/// <returns>XmlReader object</returns>
public XmlReader ExecXmlReaderbyQuery(string sQuery, params SqlParameter[] cmdParameters)
{
try
{
if (_oTransaction == null)
{
return(SqlHelper.ExecuteXmlReader(_oConnection, CommandType.Text, sQuery, cmdParameters));
}
else
{
return(SqlHelper.ExecuteXmlReader(_oTransaction, CommandType.Text, sQuery, cmdParameters));
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary></summary>
protected override System.Xml.XmlReader executeXmlReader(string spName, object[] paramValues)
{
SqlConnection oConn = null;
System.Xml.XmlReader oReader = null;
try {
oConn = new SqlConnection(base.mConnection);
oConn.Open();
oReader = SqlHelper.ExecuteXmlReader(oConn, spName, paramValues);
base.setStatusNotification(true);
}
catch (Exception ex) { throw ex; }
finally { if (oConn.State == ConnectionState.Open)
{
oConn.Close();
}
}
return(oReader);
}
public XmlReader ExecuteXmlReader(IDbTransaction transaction, CommandType commandType, string commandText)
{
return(SqlHelper.ExecuteXmlReader((SqlTransaction)transaction, commandType, commandText));
}
public XmlReader ExecuteXmlReader(CommandType commandType, string commandText)
{
return(SqlHelper.ExecuteXmlReader(Connection, commandType, commandText));
}
public XmlReader ExecuteXmlReader(string spName, params object[] parameterValues)
{
return(SqlHelper.ExecuteXmlReader(Connection, spName, parameterValues));
}
public XmlReader ExecuteXmlReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
return(SqlHelper.ExecuteXmlReader(Connection, commandType, commandText, commandParameters));
}
public override IEnumerable <SupplierLicense> FindByCriteria(string finderType, object[] criteria)
{
String methodName = "FindByCriteria() - " + finderType + " - " + _dataSource.Value.Name;
////if (_logger.IsDebugEnabled)
////{
//// LoggingUtility.logMethodEntry(_logger, methodName);
////}
try
{
////SqlTransaction transaction = (SqlTransaction)this.GetContextTransaction(dataSource);
////if (transaction == null) // Not in a transaction.
////{
var x = _dataSource.Value;
using (SqlConnection connection = (SqlConnection)_dataSource.Value.DbConnection)
{
switch ((string)finderType)
{
case FIND_LICENSE_BY_SUPPLIER:
{
int supplierId = (int)criteria[0];
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@supplierId", SqlDbType.Int)
{
Value = supplierId
};
parameters[1] = new SqlParameter("@ReturnValue", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
XmlReader reader = SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, "FindLicenseXml_xx", parameters);
return(DeserializeCollection(reader));
}
default:
return(null);
}
}
////}
////else
////{
//// switch ((string)finderType)
//// {
//// case FIND_LICENSE_BY_SUPPLIER:
//// {
//// int supplierId = (int)criteria[0];
//// XmlReader reader = FindLicenseXml(transaction,
//// supplierId);
//// return DeserializeCollection(reader);
//// }
//// case FIND_LICENSE_BY_STATUS:
//// {
//// int status = (int)criteria[0];
//// XmlReader reader = FindLicenseByStatusXml(transaction,
//// status);
//// return DeserializeCollection(reader);
//// }
//// case FIND_LICENSE_BY_BATCHJOB:
//// {
//// string jobName = (string)criteria[0];
//// int dueDate = (int)criteria[1];
//// XmlReader reader = FindLicenseByBatchJobXml(transaction,
//// jobName, dueDate);
//// return DeserializeCollection(reader);
//// }
//// default:
//// return null;
//// }
////}
}
catch (System.Exception e)
{
////_logger.Error(methodName, e);
return(null);
}
}
public XmlReader ExecuteXmlReader(IDbTransaction transaction, string spName, params object[] parameterValues)
{
return(SqlHelper.ExecuteXmlReader((SqlTransaction)transaction, spName, parameterValues));
}
public XmlReader ExecuteXmlReader(IDbTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
return(SqlHelper.ExecuteXmlReader((SqlTransaction)transaction, commandType, commandText, commandParameters));
}