#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2013
    Posts
    2
    Rep Power
    0

    Reading a SQL Select statment into formated string output C#


    I'm trying to format a SQL results in a fixed length string. The SQL statement is below, and it contains two field first_name is varchar(50), last_name is varchar(50). The results need to be fixed length due to another app that will read and parse it. The SELECT statement below:
    // SELECT top 1 first_name, last_name FROM Table
    This will return the below results: (which is not fixed length)
    first_name last_name
    Mike Espinoza-Hernandez
    The desired results would be below:
    first_name last_name
    Mike Espinoza-Hernandez

    Currently I am reading the SQL results into an array, then I check if the column header is greater than the detail and will pad with spaces to get the fixed length string. I used a rich text box in Visual Basic 6, and it would "auto" place the fixed length format for me. I am still learning C#, and I am trying to find a similar way, if possible? Then I don't need the array logic. Also, this is console application that runs in the system tray for windows XP. There is no dialog interface with this application.

    Thank you in advance,
    Mike

    Code:
           private static void runQuery(string fullPath)
            {
            WaitForFile(fullPath); //THis will wait for the incoming text file with the SQL statement
    
            string connectionString = GetConnectionString();
            using (SqlConnection connection = new SqlConnection())
            using (SqlCommand cmd = new SqlCommand(text, connection))
            {
            connection.ConnectionString = connectionString;
            connection.Open();
            //cmd.ExecuteReader();
            using (SqlDataReader reader = cmd.ExecuteReader())
            //write file to out-box as redeemed
            {
            string[] aHead = new string[reader.FieldCount];
            for (int col = 0; col < reader.FieldCount; col++)
            {
            aHead[col] = reader.GetName(col).ToString();
            }
    
            string[] aDetail = new string[reader.FieldCount];
            while (reader.Read())
            {
            for (int col = 0; col < reader.FieldCount; col++)
            {
            aDetail[col] = reader.GetValue(col).ToString();
            }
            }
            for (int col = 0; col < headList.Count; col++)
            {
            if (aHead[col].Length < aDetail[col].Length)
            {
            //RIGHT PAD aHead field
            }
            else
            {
            RIGHT PAD aDetail field
            }
            }
            }
            }
            }
            static private string GetConnectionString()
            {
    
            return "Data Source=SERVER;"
            + "Initial Catalog=TEST;"
            + "User id=Mike;"
            + "Password=ABC123;";
    
            }
    
    
            }
            } 
    
        Quick reply to this message Reply   Reply With Quote Reply With Quote   Multi-Quote This Message  Faq  Report
  2. #2
  3. I do .NET for a living
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Sep 2003
    Location
    Florida
    Posts
    1,558
    Rep Power
    214
    Can't tell from the text what you're trying to accomplish. If you just want to make the firstname and lastname variables 50 characters in length you can just use PadRight.

    Code:
                char chrSpace = ' ';
                string strFirstName = reader["FirstName"].ToString();
                string strLastName = reader["LastName"].ToString();
               strFirstName = strFirstName.PadRight(50, chrSpace);
               strLastName = strLastName.PadRight(50, chrSpace);
    If you mean something else, please specify.
    Roger (.NET MCP)

Similar Threads

  1. Select statment not working
    By jellybeannn in forum Microsoft Access Help
    Replies: 4
    Last Post: August 11th, 2010, 07:23 AM
  2. Null Value in select statment
    By beanbeanbean in forum Microsoft Access Help
    Replies: 1
    Last Post: October 2nd, 2008, 08:51 AM
  3. Select statment question
    By havey in forum ASP Development
    Replies: 4
    Last Post: April 3rd, 2006, 06:53 AM
  4. Select Insert into statment...
    By edmondj in forum ASP Development
    Replies: 10
    Last Post: September 27th, 2004, 02:39 PM
  5. select statment
    By amar in forum ASP Development
    Replies: 2
    Last Post: July 30th, 2003, 08:49 AM

IMN logo majestic logo threadwatch logo seochat tools logo