How to get data in string from database

Question

i am working on android client and .NET server application, in which i parse data into xml and then convert into string and then send this string to android client.now i am facing problem in getting data from SQL Server in xml format and convering it into string.here is my code..

 UserLogin userLogin = converter.GetObjectFromXml<UserLogin>(xml);
    String query = @"Select StdBD.First_NameEn As name from TblStudentBioData As StdBD Join TblStudentDetail As StdDet ON StdBD.Student_ID = StdDet.Student_ID
       join TblClassSchedule As ClsSch on StdDet.ClassID = ClsSch.ClassSchID
     join TblClass As Cls on ClsSch.ClassID = Cls.ClassID 
     join TblSemAssigning As SemAs on SemAs.SemAssId = ClsSch.SemAssId
     join TblAcademicYear As Acd on SemAs.AcademicYearId = Acd.AcademicYearId
     where Acd.AcademicYearId = " + userLogin.userId + "FOR XML RAW('Student'),Root('Students'),Elements";
     String outputXml = General.ExecuteSimpleSelectQuery(General.connectionString, query, "Table user");
                   Console.WriteLine("xmllll = "+outputXml);

and

   class General
   {
    public static String ServerIp = "192.168.1.2";
    public static String ServerPort = "8060";
    public static String connectionString =    NetService2.Properties.Settings.Default.ConnString.ToString();

    public static String ExecuteSimpleSelectQuery(string ConnectionString, string _Query, string DataTableName)
    {      
        SqlConnection conn = new SqlConnection(ConnectionString);            
        SqlCommand cmd = new SqlCommand(_Query,conn);
        SqlDataReader reader = null;          
        conn.Open();
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine("name = " + reader[0].ToString());
        }
        reader.Close();
        conn.Close();
        return "";
    }

Output:

by using this code i am getting data in SqlDataReader instance but not in string,so is there any way to directly get data into string or convert SqlDataReader instance data into string,so i can use it.

i want output like this:

String xml = "<Students>
  <Student>
    <name>Aliya</name>
  </Student>
  <Student>
    <name>Fahad</name>
  </Student>
  <Student>
    <name>iqra</name>
  </Student>
  <Student>
    <name>iqra</name>
  </Student>
  <Student>
    <name>khurram</name>
  </Student>
  <Student>
    <name>Zainab</name>
  </Student>
  <Student>
    <name>Fatima</name>
  </Student>
  <Student>
    <name>Fahad</name>
  </Student>
</Students>";

replace this hard coded xml to the xml, getting from database.

Answer 1

just get your XML data in a DataTable it will give you an xml at its first position and then convert it into an string.

    SqlConnection conn = new SqlConnection(yourConnectionString);            
    SqlCommand cmd = new SqlCommand(your query,conn);
    SqlDataAdapter SDA = new SqlDataAdapter();
    DataTable dt = new DataTable(DataTableName);
    conn.Open();
    SDA.Fill(dt);
    conn.Close();
    String xml =  dt.Rows[0].ItemArray[0].ToString();
    return xml;

Answer 2

Simple put your query into storedprocedure

 Create PROCEDURE yourprocedurename
 AS
 BEGIN
 Select StdBD.First_NameEn As name from TblStudentBioData As StdBD 
 Join TblStudentDetail As StdDet ON StdBD.Student_ID = StdDet.Student_ID
 join TblClassSchedule As ClsSch on StdDet.ClassID = ClsSch.ClassSchID
 join TblClass As Cls on ClsSch.ClassID = Cls.ClassID 
 join TblSemAssigning As SemAs on SemAs.SemAssId = ClsSch.SemAssId
 join TblAcademicYear As Acd on SemAs.AcademicYearId = Acd.AcademicYearId
 where Acd.AcademicYearId = " + userLogin.userId + "FOR XML 
 RAW('Student'),Root('Students'),Elements";
 END

and from coding side do something like this

public static String ExecuteSimpleSelectQuery(string ConnectionString, 
                                  string _Query, string DataTableName)
    {      
        SqlConnection conn = new SqlConnection(ConnectionString);            
        SqlCommand cmd = new SqlCommand("yourstoredprocedurename",conn);
        SqlDataAdapter SDA = new SqlDataAdapter();
        DataTable dt = new DataTable(DataTableName);
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        SDA.SelectCommand = cmd;
        SDA.Fill(dt);
        conn.Close();
        return dt.Rows[0].ItemArray[0].ToString();
    }

hopefully it will give you the required output

Answer 3

Yes, you can capture your column data into a string. By default, all of your data types will be considered an object. Which means you'll have to:

• Use the SqlDataReader built in function.
• Use the ToString()
• Use a (string) or as string Cast.

Before I provide an example, something you should do before you attempt to work with the data, a null check.

if(reader[0] != DBNull.Value)
{
     // Do Something...
}

The reason, is if you perform certain manipulations on the data you'll receive an Exception. Another useful thing, wouldn't be to use [0] but rather the ["NameOfColumn"] as later on maintaining the code, will make the specific data easier to read and see what it does.

if(reader["NameOfColumn"] != DBNull.Value)
     example = reader["NameOfColumn"].ToString();

if(reader["NameOfColumn"] != DBNull.Value)
     example = (string)reader["NameOfColumn"];

example = reader["NameOfColumn"] as string;

Those are some primitive examples, the last one if it fails will assign a null. So you'll want to anticipate that bubbling potentially in your code. The first example, is the most common and simple.

However, if you want to avoid repeating said code.

public static class DataReaderExtension
{
     public static string GetStringOrNull(this IDataReader reader, int ordinal)
     {
          var value = null;
          if(!reader.IsDBNull(reader.GetOrdinal(ordinal)
               reader.GetString(ordinal);

          return value;
     }

     public static string GetStringOrNull(this IDataReader reader, string columnName)
     {
          return reader.GetStringOrNull(reader.GetOrdinal(columnName));
     }
}

Those are several different approaches. Hopefully that indeed helps you.

Answer 4

Your function will always return empty string as you return "" after every time function is called. Try change your function as:

public static String ExecuteSimpleSelectQuery(string ConnectionString, string _Query, string DataTableName)
    {      
        SqlConnection conn = new SqlConnection(ConnectionString);            
        SqlCommand cmd = new SqlCommand(_Query,conn);
            string result;
        conn.Open();
        var dt = new DataTable();
        dt.Load( cmd.ExecuteReader());


        using (StringWriter sw = new StringWriter()) {
        dt.WriteXml(sw);
         result = sw.ToString();
       }

        conn.Close();
        return result;
    }

Answer 5

You could use String.Join after you've loaded all records into a List<string>:

public static String ExecuteSimpleSelectQuery(string ConnectionString, string _Query, string DataTableName)
{
    List<string> list = new List<string>();
    using(SqlConnection conn = new SqlConnection(ConnectionString))
    using (SqlCommand cmd = new SqlCommand(_Query, conn))
    {                
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                list.Add("name = " + reader.GetString(0));
            }
        }
    }
    return string.Join(Environment.NewLine, list);
}

However, don't use string concatenation for your sql commands because you are vulnerable to sql-injection. So don't pass the query to the method ExecuteSimpleSelectQuery. Instead use sql-parameters.