Connection class in SQL Server

Question

I'm implementing connection I implemented methods for Connect and Insert. Those methods are working. But I have problem how to implement getdata() method (get data from database). After I send query to the method and I need to know how to use SqlDataReader.

public String GetData(string _query)
{
     try
     {
         SqlCommand cmd = new SqlCommand(_query, this.dbCon);
         results = cmd.ExecuteReader().ToString();
         return results;
     }
     catch (Exception)
     {
         return "Error";
     }
}

I want to complete this get method, I'm using SQL Server and C#

dotNET · Accepted Answer · 2013-06-25 07:54:37Z

1

Do not reinvent the wheel. Typed DataSets and EF are by far and away the best methods of retrieving and manipulating data in .NET for most scenarios. MS has already written all of what you are trying to write in a MUCH better way than you'll probably ever be able to achieve. The time you spend in learning these two technologies will give you long-lasting benefit. This is more true for you since you're using it against SQL Server.

answered Jun 25, 2013 at 7:54

dotNET

35.7k29 gold badges176 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JBond · Accepted Answer · 2013-06-25 07:49:42Z

0

This is a fairly straight forward example you can look at:

public SqlDataReader ExecuteReader(SqlCommand cmd)
        {
            return cmd.ExecuteReader();
        }

Using this method, I could execute the following:

var reader = ExecuteReader(new SqlCommand("SELECT ColumnA, ColumnB FROM Table"));

string ColA = string.empty;
string ColB = string.empty;

while (reader.Read())
       ColA = reader["ColumnA"].ToString();
       ColB = reader["ColumnB"].ToString();

reader.Close();
reader.Dispose();

answered Jun 25, 2013 at 7:49

JBond

3,3025 gold badges31 silver badges33 bronze badges

2 Comments

user2490167 Over a year ago

I want to use this method method for more than one query how to change this to use for several queries

JBond Over a year ago

@user2490167 Each time you call ExecuteReader, you canpass in a different SqlCommand. Therefore it can be for whatever query you want and it will return the SqlDataReader for it.

CloudyMarble · Accepted Answer · 2013-06-25 08:09:04Z

0

Try something like this:

private void TestMethod(string sqlCmd, List<string> myColumns)
{
    try
    {
        SqlDataReader myReader = null;
        SqlCommand    myCommand = new SqlCommand(sqlCmd, myConnection);
        myReader = myCommand.ExecuteReader();
        while(myReader.Read())
        {
            foreach (var col in myColumns)
            {   
               Console.WriteLine(myReader[col].ToString());                   
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
}

In addition you can take a look at this Article.

edited Jun 25, 2013 at 8:09

answered Jun 25, 2013 at 7:47

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

3 Comments

user2490167 Over a year ago

I want to use this method method for more than one query how to change this to use for several queries

CloudyMarble Over a year ago

Extract the SQL command as a parameter so you can call the methode each time with different query.

user2490167 Over a year ago

how to change while(myReader.Read()){}

Mahesh · Accepted Answer · 2015-03-04 10:47:45Z

0

Write a method to get the data reader as below,

private SqlDataReader TestMethod(string sqlCmd, List<string> myColumns)
{
 try{
     SqlDataReader myReader = null;
     SqlCommand    myCommand = new SqlCommand(sqlCmd, myConnection);
     myReader = myCommand.ExecuteReader();
     return myReader;
   }
   catch (Exception e)
   {
     Console.WriteLine(e.ToString());
   }
}

And then just use it as,

SqlDataReader myReader  = TestMethod("your query",youtcolumnlist)
while(myReader.Read())
{

   foreach (var col in myColumns)
   {   
       Console.WriteLine(myReader[col].ToString());                   
   }
}

edited Mar 4, 2015 at 10:47

answered Jun 25, 2013 at 8:50

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

Collectives™ on Stack Overflow

Connection class in SQL Server

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related