Category Archives: Database

Pattern search using SQL

Posted on by
Reply

Instead of exact matching, the following code allows you to do a pattern search by joining two table using like operator.

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col LIKE '%' + Table2.col + '%'

LINQ to SQL return a typed class/struct

Posted on by
Reply

The trick is to project the query result into an ordinary named type with an object initializer:
Paginate is used to demonstrate how the query can be used for further processes.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
 
namespace LINQPart3_CSharp
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            int startRow = 10;
            int takeRow = 20;
 
            GridView1.DataSource = BindProducts(startRow, takeRow);
            GridView1.DataBind();
        }
 
        private IQueryable
 BindProducts(int startRow, int takeRow)
        {
            NorthwindDataContext db = new NorthwindDataContext();
 
            var products = from p in db.Products
                           where p.OrderDetails.Count >2
                           select new prod
                           {
                               ID = p.ProductID,
                               Name = p.ProductName,
                               NumOrders = p.OrderDetails.Count,
                               Revenue = String.Format("{0:C}", p.OrderDetails.Sum(o => o.UnitPrice * o.Quantity))
                           };
            //return products.Skip(startRow).Take(takeRow);
            return Paginate(products, startRow, takeRow);
        }
 
        public IQueryable Paginate(IQueryable query, int skip, int take)
        {
            return query.Skip(skip).Take(take);
        }
 
        struct prod {
            public int ID { get; set; }
            public string Name { get; set; }
            public int NumOrders { get; set; }
            public string Revenue { get; set; }
        }
    }
}

Linq to SQL (1)

Posted on by
Reply

You can crate a Plain Old CLR Object to map the underlining database table w/o the help of the LINQ-to-SQL designer. To represent a customer class, you need the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Text;
 
namespace LinqToSql
{
    [Table(Name="Customers")]
    public class Customer
    {
        [Column]
        public string customerId {get; set;}
        [Column]
        public string companyName {get; set;}
        [Column]
        public string city {get; set;}
        [Column]
        public string country {get; set;}
    }
 
    class LinqToSql
    {
        static void Main(string[] args)
        {
            string connString = @"Server=.SQLEXPRESS; 
            Database=Northwind; Trusted_Connection=Yes";
            DataContext db = new DataContext(connString);
 
            // crate typed table
            Table<Customer> customers = db.GetTable<Customer>();            
            //query database
            var custs = from c in customers
                        where c.country =="USA"
                        select c;
            //display customers
            foreach (var cust in custs)
            {
                Console.WriteLine(
                    "{0} {1} {2} {3}", 
                    cust.customerId, cust.companyName, cust.city, cust.country
                    );
            }
            db = null;
        }
    }
}