0

Currently I am using a DLL Library to get lists of custom objects.

Which is all great, for being able to use it in other projects.

But I want a dropdown box, which is a lookup of a DB table, with literally 3 options, (High, medium, low) and this isnt the only case that this is a problem.

Is there any short way of getting such info?

In ROR you could literally call SQL String to a db and get the list, with just one line of code.....

So Id like something like:

var mylist = sqldb.query("SELECT * FROM Priority");

Note: yes the above is completely made up.

Ideally I wouldnt have to set up any models either... and ideally also not have to attach a DB, rather use a query string- unless attaching is far easier and can be customised from the config file.

Update: following an example, I now ahve the following, but it says System.ArgumentException: Keyword not supported: 'initial catalog'.

    using (var db = Database.OpenConnectionString(WebLite.Properties.Settings.Default.dbConnString))
    {
        var items = db.Query("SELECT * FROM TaskPriority");
    }

My connection string: Data Source=MYPC\WEB;Initial Catalog=WEBSQL;User ID=sa;Password=password;

Improve this question
4
  • Do you want quick and dirty or solid and maintainable? Commented Nov 15, 2011 at 16:30
  • @jrummell: Everyone wants quick and maintainable but end up with dirty and solid. Commented Nov 15, 2011 at 16:33
  • Is there a reason you don't want to use the Entity Framework or LINQ to SQL? In the EF you could do a data first project and have your quick and dirty models. Commented Nov 15, 2011 at 16:34
  • MVC. And I just want something thats very light on the code. Commented Nov 15, 2011 at 16:37

5 Answers 5

Reset to default
1

You can use the Database class from WebMatrix.Data:

var items = Database.Open(...).Query("SELECT * FROM Priority");

However, in the long run, using Entity Framework will be more maintainable.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

And this will close it up afterwards? i.e. no maintance
Yes, although you should put the Database in a using block.
You can also use this with LINQ.
I put my connection strin in the brackets, and it just says connection string "mystring" was not found. :@
@Doomsknight: Put the connection string in Web.config and pass the name. Or just call OpenConnectionString. See the documentation.
|
1

If you want to access a database table to fetch a couple of rows you have a few options:

  • Linq to Sql
  • Entity Framework
  • DataSets / TableAdapter
  • Raw sql with your own connection/command.

I think the last option is the most easy. Your code would look something like this:

 string queryString = 
        "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(
            queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }

Define your connection string to your database and pass it in. Then build your sql query, open a connection, build the command and then read trough the rows returned.

Improve this answer

1 Comment

Thats currently what i do do, and in the while reader loop, I populate a custom class list. But thanks. Going to research the other methods, you and others suggest
1

The quickest solution:

  1. Create a new LINQ to SQL model (and open it).
  2. Drag the Priority table on the model (and save it).
  3. Query the model like this: (new MyDataContext()).Priorities.
Improve this answer

Comments

1

You could use NHibernate or maybe Dapper sounds closest to what you're looking for. Stolen from their example page:

   public class Dog
{
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}            

var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

dog.Count()
    .IsEqualTo(1);

dog.First().Age
    .IsNull();

dog.First().Id
    .IsEqualTo(guid);
Improve this answer

Comments

1

THe Microsoft Enterprise Library has the "Data Access Application Block" that makes connections to data extremely easy. The information can be found here http://msdn.microsoft.com/en-us/library/ff664408(v=PandP.50).aspx

You can create a connection and get data using this code (from https://web.archive.org/web/20211020111631/https://www.4guysfromrolla.com/articles/070203-1.aspx)

string strSql = "select * from products where categoryid = 1";
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";

DataGrid4.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.Text, strSql);
DataGrid4.DataBind();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.