Click here to Skip to main content
15,936,119 members
Home / Discussions / C#
   

C#

 
GeneralRe: change focous when enter texbox Pin
Dave Kreskowiak24-Jul-15 2:12
mveDave Kreskowiak24-Jul-15 2:12 
QuestionRe: change focous when enter texbox Pin
Cenator24-Jul-15 2:46
Cenator24-Jul-15 2:46 
AnswerRe: change focous when enter texbox Pin
Dave Kreskowiak24-Jul-15 3:37
mveDave Kreskowiak24-Jul-15 3:37 
GeneralRe: change focous when enter texbox Pin
Cenator24-Jul-15 3:54
Cenator24-Jul-15 3:54 
QuestionChanging IP Address with program. Pin
Member 1129387623-Jul-15 22:14
Member 1129387623-Jul-15 22:14 
AnswerRe: Changing IP Address with program. Pin
OriginalGriff23-Jul-15 23:24
mveOriginalGriff23-Jul-15 23:24 
QuestionC# program for my SSIS script task help Pin
czaar99923-Jul-15 21:16
czaar99923-Jul-15 21:16 
AnswerRe: C# program for my SSIS script task help Pin
Richard Deeming24-Jul-15 2:32
mveRichard Deeming24-Jul-15 2:32 
Something like this would work to parse the files:
C#
public sealed class FlatFileLine
{
    private sealed class MetaData
    {
        public readonly int? SampleID;
        public readonly int? RepNo;
        public readonly int? Protein;
        public readonly int? TS;
        public readonly int? Fat;
        
        public MetaData(string[] fields)
        {
            for (int index = 0; index < fields.Length; index++)
            {
                string field = fields[index].Trim();
                if (string.Equals(field, "sampleID", StringComparison.OrdinalIgnoreCase))
                {
                    SampleID = index;
                }
                else if (string.Equals(field, "rep#", StringComparison.OrdinalIgnoreCase))
                {
                    RepNo = index;
                }
                else if (string.Equals(field, "protein", StringComparison.OrdinalIgnoreCase))
                {
                    Protein = index;
                }
                else if (string.Equals(field, "TS", StringComparison.OrdinalIgnoreCase))
                {
                    TS = index;
                }
                else if (string.Equals(field, "Fat", StringComparison.OrdinalIgnoreCase))
                {
                    Fat = index;
                }
            }
        }
    }
    
    public string SampleID { get; private set; }
    public string RepNo { get; private set; }
    public string Protein { get; private set; }
    public string TS { get; private set; }
    public string Fat { get; private set; }
    
    public static IEnumerable<FlatFileLine> ParseFile(TextReader reader, string delimiter)
    {
        if (reader == null) throw new ArgumentNullException("reader");
        return ParseLines(ReadLines(reader, delimiter));
    }
    
    private static IEnumerable<string[]> ReadLines(TextReader reader, string delimiter)
    {
        string line;
        while ((line = reader.ReadLine()) != null)
        {
            yield return line.Split(new[] { delimiter }, StringSplitOptions.None);
        }
    }
    
    private static IEnumerable<FlatFileLine> ParseLines(IEnumerable<string[]> lines)
    {
        MetaData columns = null;
        foreach (string[] fields in lines)
        {
            if (columns == null)
            {
                columns = new MetaData(fields);
            }
            else
            {
                yield return new FlatFileLine(fields, columns);
            }
        }
    }
    
    private FlatFileLine(string[] fields, MetaData columns)
    {
        if (columns.SampleID < fields.Length)
        {
            SampleID = fields[columns.SampleID.Value];
        }
        if (columns.RepNo < fields.Length)
        {
            RepNo = fields[columns.RepNo.Value];
        }
        if (columns.Protein < fields.Length)
        {
            Protein = fields[columns.Protein.Value];
        }
        if (columns.TS < fields.Length)
        {
            TS = fields[columns.TS.Value];
        }
        if (columns.Fat < fields.Length)
        {
            Fat = fields[columns.Fat.Value];
        }
    }
}

With that in place, your Main method would look something like this:
C#
public void Main()
{
    string delimiter = Dts.Variables["User::Delimiter"].Value.ToString();
    string TableName = Dts.Variables["User::TableName"].Value.ToString();
    string SourceDirectory = Dts.Variables["User::SourceFolder"].Value.ToString();
    
    string query = @"INSERT INTO [" + TableName + "] (sampleID, [rep#], protein, TS, Fat) VALUES (@sampleID, @RepNo, @protein, @TS, @Fat)";
    
    using (SqlConnection connection = Dts.Connections["BIReports"].AcquireConnection(Dts.Transaction))
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        foreach (string fileName = Directory.EnumerateFiles(SourceDirectory))
        {
            using (StreamReader reader = File.OpenText(fileName))
            {
                foreach (FlatFileLine line in FlatFileLine.ParseFile(reader, delimiter))
                {
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@sampleID", line.SampleID ?? string.Empty);
                    command.Parameters.AddWithValue("@RepNo", line.RepNo ?? string.Empty);
                    command.Parameters.AddWithValue("@protein", line.Protein ?? string.Empty);
                    command.Parameters.AddWithValue("@TS", line.TS ?? string.Empty);
                    command.Parameters.AddWithValue("@Fat", line.Fat ?? string.Empty);
                    command.ExecuteNonQuery();
                }
            }
        }
    }
    
    Dts.TaskResult = (int)ScriptResults.Success;
}


NB: "rep#" is a bad name for a column in a SQL database, because of the "#" character. If possible, you should consider changing the name to something that only uses alpha-numeric characters - for example, "RepNo".



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: C# program for my SSIS script task help Pin
czaar99924-Jul-15 21:42
czaar99924-Jul-15 21:42 
QuestionWays to store and insert x rows from mvc application to database Pin
ShikhaSC23-Jul-15 20:34
ShikhaSC23-Jul-15 20:34 
AnswerRe: Ways to store and insert x rows from mvc application to database Pin
Herman<T>.Instance23-Jul-15 21:21
Herman<T>.Instance23-Jul-15 21:21 
QuestionHow to raise font Height ? Pin
goldsoft23-Jul-15 9:38
goldsoft23-Jul-15 9:38 
AnswerRe: How to raise font Height ? Pin
Richard Deeming23-Jul-15 9:52
mveRichard Deeming23-Jul-15 9:52 
AnswerRe: How to raise font Height ? Pin
Sascha Lefèvre23-Jul-15 10:00
professionalSascha Lefèvre23-Jul-15 10:00 
GeneralRe: How to raise font Height ? Pin
goldsoft23-Jul-15 11:39
goldsoft23-Jul-15 11:39 
GeneralRe: How to raise font Height ? Pin
Richard MacCutchan23-Jul-15 20:54
mveRichard MacCutchan23-Jul-15 20:54 
GeneralRe: How to raise font Height ? Pin
Eddy Vluggen23-Jul-15 21:42
professionalEddy Vluggen23-Jul-15 21:42 
Questiontext to audio Pin
Member 1185276323-Jul-15 7:49
Member 1185276323-Jul-15 7:49 
AnswerRe: text to audio Pin
Dave Kreskowiak23-Jul-15 8:21
mveDave Kreskowiak23-Jul-15 8:21 
QuestionObject does not match target type Pin
Gilbert Consellado23-Jul-15 2:47
professionalGilbert Consellado23-Jul-15 2:47 
AnswerRe: Object does not match target type Pin
Eddy Vluggen23-Jul-15 3:31
professionalEddy Vluggen23-Jul-15 3:31 
GeneralRe: Object does not match target type Pin
Gilbert Consellado23-Jul-15 21:18
professionalGilbert Consellado23-Jul-15 21:18 
Questionc# error/warning code Pin
jamesmc153522-Jul-15 4:01
jamesmc153522-Jul-15 4:01 
AnswerRe: c# error/warning code Pin
CHill6022-Jul-15 4:07
mveCHill6022-Jul-15 4:07 
GeneralRe: c# error/warning code Pin
jamesmc153522-Jul-15 4:10
jamesmc153522-Jul-15 4:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.