Going from VisualStudio generated Database stuff to Programmer Generated

Question

I'd like to be able to better access the database so I can execute queries (primarily because I don't understand/know the API for it, but I do know SQL). I don't want to remove everything Visual Studio has done because a lot is already built upon it, but how can I get an object that I can use to execute SQL queries.

This is Visual Studio 2008, C#, and MSSQL

Answer 1

Are you asking what .NET libraries you can use to execute SQL against a database? If so, start by looking at SqlConnection and SqlCommand (if you're using SQL Server, otherwise use OleDbConnection and OleDbCommand). SqlCommand has several Execute() methods that will do what you're looking for.

ADO.NET is a pretty big beast but there are tons of quickstarts and sample code out there.

Answer 2

I'm not sure from your question what you've got going on, but if you just want to learn how to use a SqlConnection, SqlCommand, and a DataReader object to retrieve items from a database, check this out:

http://msdn.microsoft.com/en-us/library/haa3afyz(VS.71).aspx

Answer 3

Try something like this:

using (SqlConnection conn = new SqlConnection("Connection String Goes Here"))
{
    conn.Open();
    using (SqlCommand comm = new SqlCommand("SELECT * FROM TABLE", conn))
    {
        return command.ExecuteScalar() as string;
    }
}

Don't forget to add:

using System.Data;
using System.Data.SqlClient;

Answer 4

Not 100% sure what you're really asking for :-), but assuming you're interested in knowing how to programatically execute a SQL query, you'll need (assuming SQL Server as the backend):

• a "using System.Data;" and "using System.Data.SqlClient;" using clause
• a "SqlConnection" object to establish your connection to SQL Server (usually combined with a ConnectionString, stored in some form of a config file)
• a "SqlCommand" object to formulate your SQL query and execute it
• some way of dealing with possible results from that query

You'll have something like:

SqlConnection connection = new SqlConnection(-connection string-);

SqlCommand command = new SqlCommand("...your sql query here...", connection);

connection.Open();

command.ExecuteScalar / command.ExecuteReader / command.ExecuteNonQuery
(depending on your needs)

connection.Close();

plus of course, some error handling.... :-)

Does that help that at all??

Answer 5

You might also look into the Data Access Application Block - just a DLL you install in your app, which allows you to execute SQL queries and such much more simply:

DataSet ds = SqlHelper.ExecuteDataset(cs,CommandType.StoredProcedure,"SELECT_DATA");

Answer 6

Personally, I would always use the free Ideablade DevForce:

http://www.ideablade.com/

And use no SQL at all!