CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Help using SQL in C#

    Hey guys,

    I'm a bit new to C# and brand new to SQL. I've been playing around with Visual Studio 2010 for a few weeks now and my few years of C++ back in my college years makes C# not too much of a pain to learn. I've run into a bit of a problem now though, my knowledge of SQL is near none. Let me try to explain what I'm trying to do first

    I'm writing a program to help me at work. At my job we have many (~100) tools called PModules and each has a bunch of information tied to them. Now at first my basic understanding of programming got me thinking I could make a PModule class and make a ton of PModule objects. Something along the lines of:

    public PModule(string iSN, string iLoc, int iNumChambers)
    {
    string SN = iSN;
    string loc = iLoc;
    int numChambers = iNumChambers;

    }

    PModule J01 = new PModule("H0125", "Bay C, 3rd PM on the right", 4);

    PModule J06 = new PModule("H0173", "Bay D, 1st PM on the left", 6);

    Now I've looked into it and realized I can have an SQL database with columns that hold all this info. I've watched a dozen YouTube videos and read a bunch of tutorials but I can't figure out how to do exactly what I'm trying to do, they all seem to dump the whole database on a form. All I've basically figured out from these that helps me is how to make a database. What I want to try to do is search the database, which I'm guessing I could do with a loop For Loop. Say I want to find PModule J01...

    for (int i = 1; i <= lenghtOfDB; i++)
    {
    if (db.id(i).PModuleName == "J01")
    {
    found = true;
    break;
    }
    }

    Now I'm not sure how to get the length of the database, which would be the condition as it counts through the IDs, then however you call that column that has the names and would check it till I found it. Once I found it I would like to make, say, textBox1.text = the number of chambers.

    In the end I'm looking at making a drop down that has all these PMs (J01, J02, J03) then when they select one, they can hit a button that says Get Number of Chambers and a textBox will be set to have that number. Sorry for my ignorance and long winded'ness here but I'm at my wits end and I wanna make sure I cover all my bases. Thanks a ton for any information that can help me out here.

  2. #2
    Join Date
    Aug 2006
    Posts
    134

    Re: Help using SQL in C#

    Here's a very rough pseudocode example using old-style coding (not using ADO.Net):

    Code:
    SomeDatabaseClass db = SomeDatabaseClass.Open("my database name");
    SomeTableClass table = db.Open("my table name");
    // The EOF() function checks to see if there is any more data in the table.  "EOF" is an acronym for "End Of File".
    while (!table.EOF)
    {
        DoSomethingWithTheCurrentRow();
        table.MoveNext();
    }
    table.Close();
    db.Close();

  3. #3
    Join Date
    Oct 2006
    Posts
    14

    Re: Help using SQL in C#

    If you have an SQL backend you can use a connection string to connect your project with your database. Then add a FormView connect it to the DB and add a search function to search through the columns that you want to search through.

  4. #4
    Join Date
    May 2011
    Posts
    41

    Re: Help using SQL in C#

    Personally, I think you should go for the gusto! The best method is to use the EF(Entity Framework) code first approach.
    you create you POCO(Plain O'le CLR Objects) classes. You can use annotations to set up your datatypes, lengths, constraints etc.

    Then you create a DbContext that coagulates a collection of your business objects. Then you can use LINQ to SQL to interact with the DbContext which will manage all your CRUD operations. The best part is that it handles all the object mappings. You get to write C# code, interact with your business objects and let the EF worry about your interactions with the database. It even creates the tables that correlate to your classes. It is even smart enough to create foreign-key constraints based on aggregated classes that are Types for your public properties.

    It may take a little longer to learn than just straight ADO.NET, but YOU WILL SAVE A TON OF TIME after you get the swing of it, and itis a lot easier.

    2 great ways to learn EF is on pluralsight.com or safariBooksonline.com

    Hope this helps!

  5. #5
    Join Date
    Jul 2012
    Posts
    90

    Re: Help using SQL in C#

    The "SQL" in SQL Server stands for Structured Query Language, which is a language specifically designed to allow you to ask the database for a specific group of records. You should not be searching the database in your C# code, but let the database do the search for you (that's what it is designed to do - and does it well).

    Let's say, just as a simple example, you have a table with the following columns:

    ID INT - primary key
    iSN VARCHAR(50)
    iLoc VARCHAR(256)
    iNumChambers INT

    And you want all of the records that have 4 chambers. The query would be as follows:

    SELECT * FROM Our_Table WHERE iNumChambers = 4

    When the DataSet returns it will contain only those records that have iNumChambers set to 4.

    Do some research on Transact-SQL (Microsoft's flavor of the SQL langusge). There is a lot of information on-line, as well as in the documentation installed along with your SQL Server Management tools.
    Last edited by CGKevin; November 28th, 2012 at 06:20 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured