Get data from SQL datebase and return as string

Question

Need help to get the 1st row record and return record as string in the << >> after while() loop. There are a lot of columns in one row, I'm having a problem to declare it as string st? like usually string st = new string() please help to correct it

Thanks

public string Get_aodIdeal(string SubmittedBy)
{
    String errMsg = "";
    Guid? rguid = null;
    int isOnContract = 0;
    int isFreeMM = 0;
    string _FileName;
    DateTime InstallDateTime = DateTime.Now;
    string FileDate = ToYYYYMMDD(DateTime.Now);
    errMsg = "Unknown Error.";

    SqlConnection conn = null; SqlCommand cmd = null;
    string st = null;

    conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["iDeal"].ConnectionString);
    cmd = new SqlCommand();
    string SQL = "select TOP 1 * from table1 Order by SubmittedOn desc";
    SqlDataAdapter sqd = new SqlDataAdapter(SQL, conn);
    cmd.CommandTimeout = 1200;
    conn.Open();
    SqlDataReader sqr;
    //sqd.SelectCommand.Parameters.Add("@Submitted", SqlDbType.Int).Value = PostID;
    sqr = sqd.SelectCommand.ExecuteReader();
    while (sqr.Read())
st = new string{
    rguid = cmd.Parameters["@rguid"].Value as Guid?,
                ridno = int.Parse(sqr["ridno"].ToString()),
                SubmittedOn= DateTime.Parse(sqr["SubmittedOn"].ToString()),
                SubmittingHost = sqr["SubmittingHost"].ToString(),
                ServiceAgreementNo = sqr["ServiceAgreementNo"].ToString(),
                DocumentID = sqr["DocumentID"].ToString(),
                Source = sqr["Source"].ToString(),
                FileName = sqr["FileName"].ToString(),
                FileType = sqr["FileType"].ToString(),
                FileDate = DateTime.Parse(sqr["FileDate"].ToString()),
                InstallTime = DateTime.Parse(sqr["InstallTime"].ToString()),
                CalenderCode = cmd.Parameters["CalenderCode"].Value as Guid,
                isFreeMM = bool.Parse(sqr["isFreeMM"].ToString()),
                isOnContract = bool.Parse(sqr["isOnContract"].ToString()),
                isProcessed = bool.Parse(sqr["isProcessed"].ToString()),
                ProcessedByFullName = sqr["ProcessedByFullName"].ToString(),
                isDelete = bool.Parse(sqr["isDelete"].ToString()),
                version = int.Parse(sqr["Version"].ToString()),
                LastUpdatedBy = DateTime.Parse(sqr["LastUpdatedBy"].ToString()),
                LastUpdatedOn = DateTime.Parse(sqr["LastUpdatedOn"].ToString()),
                shopGuid = sqr["shopGuid"].ToString(),
                MacID = sqr["MacID"].ToString(),
                MSISDN = sqr["MSISDN"].ToString()
}

Answer 1

You can use a StringBuilder for this purpose as like the following:

StringBuilder strBuilder= new StringBuilder(); 
while (sqr.Read())
{
    strBuilder.AppendFormat("PostID  : {0}{1}",sqr["PostID"].ToString(),Environment.NewLine);
    strBuilder.AppendFormat("dateposted  : {0}{1}",sqr["dateposted"].ToString(),Environment.NewLine);
    // And so on Build your string
}

Finally the strBuilder.ToString() will give you the required string. But More smarter way is Create a Class with necessary properties and an Overrided .ToString method for display the output.

Let AodIdeal be a class with an overrided ToString() method. And Let me defined it like the following :

public class AodIdeal
{
    public int PostID { get; set; }
    public string  dateposted { get; set; }
    public string Source { get; set; }
    // Rest of properties here

    public override string ToString()
    {
        StringBuilder ObjectStringBuilder = new StringBuilder();
        ObjectStringBuilder.AppendFormat("PostID  : {0}{1}", PostID, Environment.NewLine);
        ObjectStringBuilder.AppendFormat("dateposted  : {0}{1}",dateposted, Environment.NewLine);
        ObjectStringBuilder.AppendFormat("Source  : {0}{1}", Source, Environment.NewLine);

        // and so on
        return ObjectStringBuilder.ToString();
    }
}

Then you can create an object of the class(let it be objAodIdeal), and make use of its properties instead for the local variables. And finally objAodIdeal.ToString() will give you the required output.

Example usage

AodIdeal objAodIdeal= new AodIdeal(); 
while (sqr.Read())
{
    objAodIdeal.PostID = int.Parse(sqr["PostID"].ToString());
    objAodIdeal.dateposted= sqr["dateposted"].ToString();
    // assign rest of properties
}
string requiredString= objAodIdeal.ToString();