SQL Intro

[mdavis28]

Hi,

I have recently started working with SQL databases of which I have 0 previous experience. I have added the datasource to my project using the Visual Studio wizard and the DataSet appears in my solution explorer.

Everything that I have read thus far has shown connecting to the SQL server and then sending query commands, but since I have added the direct reference to my project is this necessary. I thought since I had added the reference to my project I would have direct accesbility to it, but it appears that is not so.

Also to help the searching process a view has been created on the SQL server that polls all of my desired fields in a single shot. Any recommendations on how I can access the data from that view?

I apologize for the vagueness of my questions, but I am not really 100% sure the questions I need to be asking. I appreciate the help.

Thanks

[JOSheaIV]

May I ask what is the purpose of the database connection?

I ask this because I personally have never used that DataSet and what not but instead use the SqlConnection and SqlCommand objects. I then populate the data into a DataTable, a local variable, not a new item in a project. That way I code everything, and my connection settings aren't hardcoded.

[mdavis28]

Hi,

Yeah not a problem. My companies MRP system is contained on an SQL database and we have a secondary software that accesses this and displays it in a user friendly manner.

I am currently trying to develop some software that can take customer order information, poll the SQL database and search out any relavant information pertaining back to that customer order. Once that information is gathered the software will automatically populate some drawing templates accordingly.

One of my co-workers who has a little more familiarity with the SQL stuff developed a view for me. How I understand it is the view contains all of the desired information I am looking for and if I poll that view with my customer order information the other desired fields will come up with it automatically. I have already gone into the add a new datasource wizard I am able to find my database and I can specify the view of interest. After doing so a dataset appears in my solution explorer so I should be able to access that view through the dataset without needing to re-establish a connection each time. However, I have been unsuccessful with this and the resources online about how to do this have been very limited.

I have seen alot of information about doing as you suggested and making the connection each time in the code. So do you propose I should move forward doing it this way? Also do have any links to some helpful resources and references?

Thanks

[JOSheaIV]

I personal like the 2nd option because it's very lightweight and dynamic (I think those wizards and such you have to setup the connection string ahead of time). When I get home from work tonight, I'll try and remember to post some code for you. I am in the process of developing two libraries (one for SQL and one for SQLite) that allow for very easy interaction with SQL. That being said, they are not ready to be released as I need to test and perfect them more, but I can share some of the code snippets you'd need.

As for the view, the way I look at them is a custom Database Table whos data is built off SQL Queries so to speak. For instance I had a program I wrote that logged the computer playing the card game War. The data was logged as which player played what card, and who won. Each record was one card dealed from each player, and represented by a numerical value.

I then went and wrote a view that condensed that data to show me how many time one card was played against another. I had another one that simply showed me how many times each player won. It's almost like a table, but it's got logic behind it so when you access it, it runs your SQL under the covers instead of you have to run it everytimes you can do a simple select. Hopefully that makes sense

[JOSheaIV]

Sorry I didn't get this out last night but here's snippets you will probably be interested in

This first snippet will simply setup the connections. They aren't open yet, they are just set up. Note that if you do change the connection string for
connection, make sure you close the connection, and I would advise dispoing of it. I do not think you have to re-assign the connection to the command, but I could be wrong. I realize I do in my code, but I also dispose of all my variables before hand if needed

using System.Data.SqlClient;

//you only need to declare these once, these for instance I have a local variable to a class
private SqlConnection connection;
private SqlCommand command;

//Now to connect (note the parameters I use in the String.Format are properties in my class)
connection = new SqlConnection(String.Format("Server={0};Database={1};User ID={2};Password={3};", ServerName, DatabaseName, Username, Password));
//you can also this is you want to use Windows Authentication
//connection = new SqlConnection(String.Format("Server={0};Database={1};Trusted_Connection=True;", ServerName, DatabaseName));

//Next up you need to assign the connection to the command
command = new SqlCommand();
command.Connection = connection;

Next we'll perform a basic SELECT

//first open the connection. You could do this earlier on to make sure the connection string you setup was correct, adding in a try/catch around it
connection.Open(); 

//Assign your SQL Statement whatever it my be
command.CommandText = "SELECT * FROM MyTable";

//Now I have already created a DataTable, called "FetchedValues" as a variable in the class.
//DataTable FetchedValues = new DataTable();

//This will execute your SQL query and drop the results into the DataTable.
using (SqlDataReader reader = DBConnection.command.ExecuteReader())
{
    FetchedValues.Load(reader);
}

//I'd advise closing the connection when done (even putting this in a try/finally type code). Then on your next query just call the Open piece again
connection.Close(); 

That's a good starter for you, I'll let you absorb that first. Note that running items like Insert/Update/Delete you should slightly different code (And I mean just slightly). But I'll hold off for now so you can get the hang of this.

Also a quick note for you. When the Load occurs for the DataTable it drops in the columns and rows. Should you want to fetch a different table, or somehow the columns have changed in a later fetch. Make sure to do FetchedValues.Reset();. That will clear out any columns. IF you don't this can blow an error if you try working with a new table. Also if you just want to clear out old rows you only neeed FetchedValues.Clear();

Hopefully that can help you get started. Feel free to message if you have more questions. I hope I didn't rush this to quick and lose you at all

[mdavis28]

Hey JOSheaIV ,

Thanks alot for the detailed response. So I took your advice and abandoned using the DataSource Wizard and I am starting to make some progress. I am able to querry values and post them to a form, which is all I really need to do so I think my SQL nightmare is finally over. Thanks again for the help.

[JOSheaIV]

Perfect, that's good to hear. Feel free to come back if you have anymore questions.