Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

sqlserver c# returnign the array from a function call

i am using the below code to open a connection and read the data from the table but what i woudl lke to do is to return the data set back to the calling location so i can do more things with it, so i need to know please how to set these two functions to return the data
thanks for you help

private void connectsqlserver_Click(object sender, EventArgs e)
        {
            readrec("select * from tblSearchTerms");
}

private void readrec(string sqlstring)
{
  SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            connection1.Open();
            SqlCommand sqlComm = new SqlCommand("select * from tblSearchTerms", connection1);
            SqlDataReader myCommand = sqlComm.ExecuteReader();
            while (myCommand.Read())
            {
                   MessageBox.Show(myCommand["SearchTermStart"].ToString());
            }
            myCommand.Close();
 // this is were i need the rec source returned also the setup for the void and return function set up syntax
}
0
sydneyguy
Asked:
sydneyguy
  • 12
  • 7
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could return the data reader, for example:

private SqlDataReader readrec(string sqlstring)
{
  SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            connection1.Open();
            SqlCommand sqlComm = new SqlCommand("select * from tblSearchTerms", connection1);
            SqlDataReader myCommand = sqlComm.ExecuteReader();
            return myCommand;
}

Open in new window


you could also, instead, fill a dataset from the command (via a sqldataadapter), that would help to close the connection quickly.
you could also, instead, fill an array/list/dictionary/,... from the reader

it really depends on what you need to do with the data from the reader ...


0
 
sydneyguyAuthor Commented:
ReadRec("select * from tblSearchTerms");
calls the read the data source but how do i view the returned data when it is returned
0
 
mayank_joshiCommented:
example of returning a DataTable:-

private SqlDataReader readrec(string sqlstring)
{
  SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            SqlCommand sqlComm = new SqlCommand("select * from tblSearchTerms", connection1);
            SqlDataAdapter sda = new SqlDataAdapter(sqlComm );
           DataTable dtData = new DataTable();
           sda.Fill(dtData);
           return dtData;
}

Open in new window


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mayank_joshiCommented:
to view the returned data you can bind the datatable to a gridview.
0
 
mayank_joshiCommented:
if you want to view the data while debugging follow this link:-

http://stackoverflow.com/questions/1337084/how-to-view-a-datatable-while-debuging
0
 
mayank_joshiCommented:
another link which describes how to view data in a datatable at run time using DataTable Visualizer :-

http://msmvps.com/blogs/deborahk/archive/2009/07/10/dal-datatable-visualizer.aspx
0
 
sydneyguyAuthor Commented:
not to view the data in a data drid what i wanted to see was the data in the debuger mode and look through the record set
have included a screen shot of what i want to access.
at the moment i am still not able to return the myCommand data
recordset.png
0
 
sydneyguyAuthor Commented:
dont i have to call the read function
myCommand = ReadRec("select * from tblSearchTerms"); or something
0
 
mayank_joshiCommented:
to view the data in debugger mode you should use a datatable  and view the data in datatable visaulizer
as stated above.
0
 
mayank_joshiCommented:
sorry some corrections, create this function:-
private DataTable readrec(string sqlstring)
{
  SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            SqlCommand sqlComm = new SqlCommand(sqlstring, connection1);
            SqlDataAdapter sda = new SqlDataAdapter(sqlComm );
           DataTable dtData = new DataTable();
           sda.Fill(dtData);
           return dtData;
}

Open in new window



use the function  wherever you need it as follows:-

DataTable dt=New DataTable();
dt=readrec("select * from tblSearchTerms");

Open in new window


Mark a breakpoint on the last statement then follow the the steps in the below link:-
http://msmvps.com/blogs/deborahk/archive/2009/07/10/dal-datatable-visualizer.aspx




0
 
sydneyguyAuthor Commented:

return dtData;

Cannot implicitly convert type 'System.Data.DataTable' to 'System.Data.SqlClient.SqlDataReader'      
0
 
sydneyguyAuthor Commented:
DataTable dt=New DataTable();
also throws up a
Error      1      ; expected
0
 
sydneyguyAuthor Commented:
DataTable dt=New DataTable();
also throws up a
Error      1      ; expected
should be new just a typo exscuse last post
0
 
sydneyguyAuthor Commented:
SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            SqlCommand sqlComm = new SqlCommand(sqlstring, connection1);
            SqlDataAdapter sda = new SqlDataAdapter(sqlComm);
            DataTable dtData = new DataTable();
            sda.Fill(dtData);
            return dtData;

throws up a Error 22 Cannot implicitly convert type 'System.Data.DataTable' to 'System.Data.SqlClient.SqlDataReader'
0
 
mayank_joshiCommented:
please use the below function i ve changed the return type from  datareader to datatable :-

private DataTable readrec(string sqlstring)
{
  SqlConnection connection1 = new SqlConnection(@"Data Source=DICKSMITHMIR-PC\SQLEXPRESS;Initial Catalog=PubMedExtract;Integrated Security=SSPI;");
            SqlCommand sqlComm = new SqlCommand(sqlstring, connection1);
            SqlDataAdapter sda = new SqlDataAdapter(sqlComm );
           DataTable dtData = new DataTable();
           sda.Fill(dtData);
           return dtData;
}

Open in new window

0
 
sydneyguyAuthor Commented:
in the old way i could use the below code to cycle through the recs how can i do it using the DataTable please

 while (dt.Read())
            {
              //  Console.WriteLine(r["SearchTermStart"].ToString());
                MessageBox.Show(dt["SearchTermStart"].ToString());
            }
0
 
sydneyguyAuthor Commented:
this is what i am trying

DataTable dt = new DataTable();
dt = readrec("select * from tblSearchTerms");
MessageBox.Show(dt.ToString());
0
 
sydneyguyAuthor Commented:
i want to cycle through each record pulling certain data out of the fields and then call some other functions
0
 
sydneyguyAuthor Commented:
ok found it

foreach (DataRow dr in dt.Rows)
            {
                MessageBox.Show(dr["SearchTermStart"].ToString());
            }
0
 
sydneyguyAuthor Commented:
thanks for spending all the time that you did to get me up an running
garry
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now