DataTable CallStoreProcedure(Job job, DataTable filterTable, IdpeKey connectionStringKey) { DatabaseTypes databaseType = connectionStringKey.GetDatabaseType(); string actualConnectionString = connectionStringKey.Value; DataTable table = new DataTable(); string xmlInputData = ToXml(filterTable); IDbConnection con = null; IDal myDal = new DataAccessLayer(databaseType).Instance; try { con = myDal.CreateConnection(actualConnectionString); StoredProcedure sp = new StoredProcedure(myDal); sp.ConnectionString = actualConnectionString; IDbDataParameter dp1 = sp.CreateParameter(); dp1.ParameterName = "@dataSourceId"; dp1.DbType = DbType.Int16; dp1.Value = job.DataSource.Id; IDbDataParameter dp2 = sp.CreateParameter(); dp2.ParameterName = "@inputData"; dp2.DbType = DbType.Xml; dp2.Value = xmlInputData; sp.CacheDisconnectData = true; sp.Name = "SreDuplicateCheck"; sp.AddParameter(dp1); sp.AddParameter(dp2); con.Open(); IDataReader reader = sp.CreateDataReader(); table.Load(reader); } catch (Exception ex) { string errorMessage = "Error while checking duplicate (database communication)! " + Environment.NewLine + ex.Message; AddErrorMessage(job, errorMessage); } finally { if (con != null) { if (con.State != System.Data.ConnectionState.Closed) con.Close(); } con.Close(); con.Dispose(); } return table; }
public Dictionary <string, string> GenerateParameters(int applicationId) { Dictionary <string, string> returnParams = new Dictionary <string, string>(); IDbConnection con = null; IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance; try { //this will be called using default connectionstring of the application con = myDal.CreateConnection(_ConnectionString); StoredProcedure sp = new StoredProcedure(myDal); sp.ConnectionString = _ConnectionString; IDbDataParameter dp1 = sp.CreateParameter(); dp1.ParameterName = "@applicationId"; dp1.DbType = DbType.Int16; dp1.Value = applicationId; sp.CacheDisconnectData = true; sp.Name = "SRE_GENERATE_PARAMETERS"; sp.AddParameter(dp1); con.Open(); IDataReader reader = sp.CreateDataReader(); reader.Read(); for (int i = 0; i < reader.FieldCount; i++) { returnParams.Add(reader.GetName(i), reader.GetValue(i).ToString()); } } catch (Exception ex) { Trace.TraceError("Error while generating parameters " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace); } finally { if (con != null) { if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } con.Close(); con.Dispose(); } return(returnParams); }