programmaticaly connecting and accessing a database

I have not been able to find a code example anywhere. Someone please post the code to do this. I would be forever grateful.

I would like the code to be in C#. I want to connect to a database and then select all from a table.

thanks

Forever is a very long time my new friend.

(you need to list all the fields yourself. in my example, I just have one field. You can also leave out the WHERE clause and you will get all records).

try
{
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CodeCampSV06"].ConnectionString);
sqlConnection.Open();

string sqlSelect = "select VistaSlotsId FROM attendees WHERE Username = @.Username";
SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
sqlCommand.Parameters.Add("@.Username", SqlDbType.VarChar).Value = username;
vistaIdStatus = (int)sqlCommand.ExecuteScalar();

sqlConnection.Close();
sqlConnection.Dispose();

}
catch (Exception ee)
{
throw new ApplicationException(ee.ToString());
}

|||Does this code work with asp.net 2.0? sorry i forgot to mention that.|||yes it works with asp.net 2.0. That is what I am using|||

ok thanks for all the help. However i am having trouble trying to figure out what "CodeCampSV06" is. Is it the name of the DB? if so, it is not working for me.

thanks

|||

Its the name of the connectionString in the application's web.config file.

bullpit

|||

Have a look at this:

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.connectionstrings.aspx

good luck...

bullpit

|||

I have now used the correct string for webconfig.

But Now i keep getting this error

Error 2 The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?) C:\mine\MyProjects\Work4Tips\insert.aspx 19 9 C:\...\Work4Tips\
c

how do I solve this? The page i am trying to get this to run in is using masterpages. I am placing the code between the server tags and in these tags between a clickon method. Do I have to use a directive, if so what is it?

thanks for all your help... I am just so lost right now.

|||

Add this to your using directives list at the top of your aspx.cs file:

using

System.Data.SqlClient;

using

System.Data;

bullpit

|||

what if I am using inline code and not code behind?

does not work anywhere I place it.

|||

You can use this with your page directives at the top of the aspx page.

<%@. Import Namespace="System.Data" %>

bullpit

|||

You can also use a fully qualified statement like this:

System.Data.SqlClient.SqlConnection =new System.Data.SqlClient.SqlConnection(...)

bullpit|||

Correction to last post:

System.Data.SqlClient.SqlConnection sqlConnection =new System.Data.SqlClient.SqlConnection

Programmatically Accessing an SQLDataSource with a "SELECT COUNT(*)" query.

I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be

Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)

Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString

The above only works on a single row, of course. If I needed more, I know I can loop it.

The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"

My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.

Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")

The query here is "SELECT COUNT(*) as rowcnt FROM Software"

The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.

Where am I going wrong here?

The following should work.

Dim datastuffAs System.Data.DataView =CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim drAs System.Data.DataRow = datastuff .Table.Rows(0)

Dim mycountAsString = Convert.ToInt32(dr("rowcnt")).ToString()

'Label1.Text = mycount

|||

Hi there,

Aren't you getting the row count from your SELECT COUNT query (1 row obviously)? Instead of getting the result value from that query?

gonzzas

|||

It's very similar to what I've tested out, but that exact code will show that mycount = "1" instead of the actual value.

What is interesting is the GridView control I set up on the test page is outputting the correct result.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="srcSoftwareUsage" Width="527px">
<Columns>
<asp:BoundField DataField="rowcnt" HeaderText="rowcnt" ReadOnly="True" SortExpression="rowcnt" />
</Columns>
</asp:GridView>

Now, it's obviously accessing the column named rowcnt. I can debug my code and manually look at the column values in DataView.Table.Rows(0) and it shows the value 1 and nothing more.

|||

Yes, I can use either code to get the correct count from my query. What is your SqlDataSource code?

Here is what I tested:

Dim dvAs System.Data.DataView =CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim rowAs System.Data.DataRow = dv.Table.Rows(0)

' For Each row As System.Data.DataRow In dv.Table.Rows

Dim mycountAsString = Convert.ToInt32(row("rowcnt")).ToString()

Label2.Text = mycount

' Next

|||

Murphy's Law probably applies as I didn't give you thecomplete story - I naively thought this part shouldn't matter as it'sthe table output is identical.

It appears to be my sql query.

I'm not actually looking for the count of rows in the Software table, but I'm looking for the count of times a particular row in Software is referenced by 2 other tables - RoleSoft and TeamSoft

With my simple query above, both the code and GridView worked. With this one - the GridView works, the code doesn't.

SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = @.Id) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = @.Id)) AS derivedtbl_1

I thought it was a moot point as the table output appears identical from each query. Obviously I'm wrong. I'm imagining the derivedtbl_1 is probably where I'm getting bogus data in the code.

1> SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = 2) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = 2))
AS derivedtbl_1
2> go
rowcnt
----
3

(1 rows affected)
1> SELECT COUNT(*) as rowcnt FROM Software
2> go
rowcnt
----
8

(1 rows affected)

|||

Bah. I figured it out. It wasn't even the SQL statement. I had updated the @.Id parameter in the srcSoftwareUsage_Selecting event handler and I misused a global variable. It kept setting @.Id to 1 and the count for that Id was always 1.

Now I feel stupid for wasting your time and mine on this. Thanks for the help, though.