5 replies to this topic

[Siten0308]

Hello

Sorry to bug but more info the better my knowledge will grow right? well anyways on with the question: what is the code to search a wild card in a SQL database after successfully attaching it to your C# program? this wild card for example user can enter telephone number, first name, last name etc and it will pull up the entire user information or if there are a couple of users with the same name, there will be a list below the search bar and you can choose the correct one? i have an idea however it is in SQL running a query like select column from table where = ? or whatever, but how does that go in C# if a person types in a text box for the wild card search and clicks the button?

[gaylo565]

Here is a simple example of an sql query executed in c# on the Northwind database that is included with sql express server. It gets all the specified rows from the Orders table who's Customer Id contains intCustomerID (The variable isn't defined in the scope of this code but can be retrieved from a text box or set some other way. It is called a parameter and is only there to narrow down our search.)

try
{
    Set up the data connection    
    DataConnection.ConnectionString = 
        "Integrated Security = true;"+
        "Initial Catalog = Northwind;"+
        "Data Scource = ServerName\\SQLEXPRESS";
    dataConnection.Open()
    SqlCommand dataCommand = new SqlCommand();
    dataCommand.Connection = dataConnection;
    //set up actual sql query
    dataCommand.CommandText =
        "SELECT OrderID, OrderDate, "+
        "ShipName";
    //You can do this all in one command but 
    //I split it up to make it easier to read    
    dataCommand.CommandText +=
        "FROM Orders WHERE CustomerID '"+intCustomerID+"'";
    //call dataReader class to execute dataCommand
    SqlDataReader = dataCommand.ExecuteReader();
    //create loop to iterate through the records;
    while (dataReader.Read())
    {
        //Do something with your returned records
        intorderId = dataReader.GetInt32(0):
        if(dataReader.IsDBNull(2)
        {
             MessageBox.Show("Order {0} not yet shipped)
        }
        else
        {
            DateTime orderDate = dataReader.GetDateTime(1);
            string shipName = dataReader.GetString(3);
        }
    }
    dataReader.Close();
}
catch(Exception e)
{
    MessageBox.Show(e.Message, "Error accessing the database:", MessageBoxButtons.OK);
}
finally
{
    dataConnection.Close()
}

Keep in mind this is a very simple example. You should also look into creating a data set for your information. The method I used above locks th rows being queried while in use and is therefor considered inefficient for multiuser db applications. A data set is more complicated but worth learning for the sort of application you want to build.
This is a decent intro to data sets: Using the DataSet - Using ADO.NET with SQL Server - Developer Fusion - Visual Basic, C# Programming, ASP.NET, .NET Framework and Java Tutorials

Edited by gaylo565, 25 June 2008 - 07:52 AM.

[Siten0308]

Alright this is some bull %^$@^!, i know i saw this dang post in C# tutorial, please gaylo565 say you this post in also C# tutorial? i just logged in and find this post here, whats the deal?

[gaylo565]

???Your confusing me??? This post isn't a repeat. I put a bit of time into typing this up from a sample project I did a while back, and simplifying it to make a good example. I just looked through the c# tutorials and didn't see anything like this. Where do you say you saw this post before???....Sorry I just caught up on my post reading and now I get what it is your talking about. It wasn't in the tutorials section when I commented on it. It could have been at one point but an admin moved it if that was the case.

Edited by gaylo565, 26 June 2008 - 06:19 AM.

[Siten0308]

Sorry gaylo565, thanks for your assistance with your example posted, it was great information especially very handy to anyone who would be using it in a professional environment. on the other topic of my posts being moved, nevermind, i am confused my self, i am just going to read posts for now on instead of asking so i dont get kicked off this forum website

[gaylo565]

No worries:) Hopefully they don't get that carried away and all your posts get to the right spot.