/// <summary> /// Call DB query /// </summary> /// <param name="inputParameter"></param> /// <param name="outputParameter"></param> /// <param name="sqlQuery"></param> /// <returns></returns> public MemoryStream CallDBquery(InputParameter inputParameter, OutputParameter outputParameter, string sqlQuery) { var xmlUtility = new XmlUtility(); sqlQuery = sqlQuery.Replace("\n", string.Empty); if (Connection.State != System.Data.ConnectionState.Open) { Connection.Open(); } using (var cmd = Connection.CreateCommand()) { cmd.Connection = Connection; cmd.CommandText = sqlQuery; cmd.CommandTimeout = 300; cmd.CommandType = System.Data.CommandType.Text; foreach (var item in inputParameter.Properties) { cmd.Parameters.AddWithValue(string.Format("@{0}", item.PropertyName), item.PropertyValue); } using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { foreach (var item in outputParameter.Properties) { item.PropertyValue = reader[item.PropertyName].ToString(); } } reader.Close(); reader.Dispose(); } } var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties); return stream; }
/// <summary> /// Run Procedure /// </summary> /// <param name="storeProcName"></param> /// <param name="parameters"></param> /// <param name="inputParameter"></param> /// <returns></returns> private InputParameter RunProcedure(string storeProcName, List<SqlParameter> parameters, InputParameter inputParameter) { if (Connection.State != System.Data.ConnectionState.Open) { Connection.Open(); } SqlCommand command = BuildQueryCommand(storeProcName, parameters); command.ExecuteNonQuery(); foreach (var item in inputParameter.Properties) { item.PropertyValue = command.Parameters[string.Format("@{0}", item.PropertyName)].Value.ToString(); } Connection.Close(); return inputParameter; }
/// <summary> /// Call Stored Proc /// </summary> /// <param name="inputParameter"></param> /// <param name="outputParameter"></param> /// <param name="storeProcName"></param> /// <returns></returns> public MemoryStream CallStoredProcedure(InputParameter inputParameter, OutputParameter outputParameter, string storedProcName) { List<SqlParameter> parameters = new List<SqlParameter>(); bool isOutput = inputParameter.Properties.Where(t => t.PropertyParameterType.Contains("True")).Any(); foreach (var item in inputParameter.Properties) { SqlParameter pa = CreateNewParameter(item.PropertyName,item.PropertyValue,item.PropertyParameterType,item.PropertyType,item.PropertyLength); parameters.Add(pa); } if (isOutput || outputParameter.Properties.Count() == 0) { inputParameter = RunProcedure(storedProcName, parameters, inputParameter); } else { var reader = RunProcedure(storedProcName, parameters); while (reader.Read()) { foreach (var item in outputParameter.Properties) { item.PropertyValue = reader[item.PropertyName].ToString(); } } reader.Close(); reader.Dispose(); } var xmlUtility = new XmlUtility(); var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties); return stream; }