asked on

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
}

Guy Hengel [angelIII / a3]

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 ...

sydneyguy

ASKER

ReadRec("select * from tblSearchTerms");
calls the read the data source but how do i view the returned data when it is returned

mayank_joshi

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

mayank_joshi

to view the returned data you can bind the datatable to a gridview.

mayank_joshi

if you want to view the data while debugging follow this link:-

http://stackoverflow.com/questions/1337084/how-to-view-a-datatable-while-debuging

mayank_joshi

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

sydneyguy

ASKER

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

sydneyguy

ASKER

dont i have to call the read function
myCommand = ReadRec("select * from tblSearchTerms"); or something

mayank_joshi

to view the data in debugger mode you should use a datatable and view the data in datatable visaulizer
as stated above.

ASKER CERTIFIED SOLUTION

mayank_joshi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sydneyguy

ASKER

return dtData;

Cannot implicitly convert type 'System.Data.DataTable' to 'System.Data.SqlClient.SqlDataReader'

sydneyguy

ASKER

DataTable dt=New DataTable();
also throws up a
Error 1 ; expected

sydneyguy

ASKER

DataTable dt=New DataTable();
also throws up a
Error 1 ; expected
should be new just a typo exscuse last post

sydneyguy

ASKER

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'

mayank_joshi

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

sydneyguy

ASKER

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());
}

sydneyguy

ASKER

this is what i am trying

DataTable dt = new DataTable();
dt = readrec("select * from tblSearchTerms");
MessageBox.Show(dt.ToString());

sydneyguy

ASKER

i want to cycle through each record pulling certain data out of the fields and then call some other functions

sydneyguy

ASKER

ok found it

foreach (DataRow dr in dt.Rows)
{
MessageBox.Show(dr["SearchTermStart"].ToString());
}

sydneyguy

ASKER

thanks for spending all the time that you did to get me up an running
garry