시퀸스 생성하는 프로시저 만들기

엔티티 모델에서 프로시저 등록하기

가져오기

        public int GetProgramSeq()
        {
            int i = 0;
            using (PbotEntities ctx = new PbotEntities())
            {
             
                var results = ctx.SP_GET_SEQ_PROGRAM();
                long? nextSequenceValue = results.SingleOrDefault();
             
                if( int.TryParse( nextSequenceValue.Value.ToString() , out i ))
                {
                    i = int.Parse(nextSequenceValue.Value.ToString());
                }

                return i;
            }
        }

Linq Distinct & Group by 하기

var result = myList.GroupBy(test => test.id)
                   .Select(grp => grp.First())
                   .ToList();

        var q = from c in dt.AsEnumerable()
                group c by c.Field<string>("WSOperate") into g
                select new
                {
                    WSOperate = g.Key
                };

        void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            Orders orderList = new Orders();
            Order searchOrder = new Order(1, 1, "kojaedoo", 100, "kojaedoo", DateTime.Now);

           List<Order> list = orderList.ToList();

           var result = list.Where(delegate(Order a)
            {
                if (a.Name.Contains(searchOrder.Name))
                {
                    return true;
                }
                else
                {
                    return false;
                }

            });

        }

.

기존의 엔티티를 사용하면서 직접쿼리를 아래와 같이 할수있습니다.

엔티티로는 안되는  SEQUENCE 값이라든지(평션등을 이용하면 가능하지만 귀찮음) 

이런걸 사용 할 때  사용하면 좋을 꺼 같습니다.

Oracle

            OracleParameter op = new OracleParameter("NameVal", "kojaedoo");
            OracleParameter op2 = new OracleParameter("CompanyCodeVal", "0001");

            string query = @"INSERT INTO KOJAEDOO.PERSON (EMP_CODE, ""NAME"", COMPANY_CODE) VALUES(SEQUENCE1.NEXTVAL,:NameVal, :CompanyCodeVal )";
            db.ExecuteStoreCommand(query, op , op2);

MS Sql

예를 들어 다음의 두 메서드 호출은 동일합니다.

context.ExecuteStoreQuery<Product>("select * from Products where pid = {0}", 1);

context.ExecuteStoreQuery<Product>("select * from Products where pid = @p0", new SqlParameter { ParameterName = "p0", Value = 1 });
 

MSDN

using (SchoolEntities context =
    new SchoolEntities())
{
    // The following three queries demonstrate 
    // three different ways of passing a parameter.
    // The queries return a string result type.

    // Use the parameter substitution pattern.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < {0}", 5))
    {
        Console.WriteLine(name);
    }

    // Use parameter syntax with object values.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < @p0", 5))
    {
        Console.WriteLine(name);
    }
    // Use an explicit SqlParameter.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < @p0",
            new SqlParameter { ParameterName = "p0", Value = 5 }))
    {
        Console.WriteLine(name);
    }
}

데이터그리드에 바인딩되어져있는데이터를 검색 합니다.

CollectionViewSource에 데이터를 담아두고

ICollectionView _customerView = CollectionViewSource.GetDefaultView(__CollectionViewSource.View);
_customerView.Filter = CustomerFilter; 

필터 검색을 실행한다.

엔티티

데이터서비스

http://localhost:56323/WcfDataService1.svc

namespace WcfRestService1
{
    public class WcfDataService1 : DataService<NorthwindEntities>
    {


        public WcfDataService1()
        {

        }
       // 이 메서드는 서비스 전반적인 정책을 초기화하는 데 한 번만 호출됩니다.
        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: 규칙을 설정하여 어떤 엔터티 집합과 서비스 작업을 표시할 수 있고 업데이트할 수 있는지 등을 표시합니다.
            // 예제:
            config.SetEntitySetAccessRule("*", EntitySetRights.All);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;

            //데이터베이스에서 자세한 오류가 반환되는지 여부
            config.UseVerboseErrors = true;

        }


    }
}

MainWindow.xaml

<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:i="http://schemas.microsoft.com/expression/2010/interactivity" xmlns:ei="http://schemas.microsoft.com/expression/2010/interactions" xmlns:local="clr-namespace:테스트용" mc:Ignorable="d" x:Class="테스트용.MainWindow"
        Title="MainWindow" Height="350" Width="525" xmlns:my="http://schemas.microsoft.com/winfx/2006/xaml/presentation/ribbon">
    <Window.Resources>
 
    </Window.Resources>
    <Grid>
        <Grid.Background>
            <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                <GradientStop Color="#FFA3A3A3" Offset="0.009"/>
                <GradientStop Color="#FF5E5E5E" Offset="0.997"/>
                <GradientStop Color="#FF2B2B2B" Offset="0.519"/>
            </LinearGradientBrush>
        </Grid.Background>
        
        <Grid.RowDefinitions>
            <RowDefinition/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition/>
        </Grid.ColumnDefinitions>
        <DataGrid Height="230" HorizontalAlignment="Left" Margin="12,12,0,0" Name="dataGrid1" VerticalAlignment="Top" Width="479" />
        <Button Content="검색" Height="23" HorizontalAlignment="Left" Margin="416,248,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
        <TextBox Height="22" HorizontalAlignment="Left" Margin="290,249,0,0" Name="textBox1" VerticalAlignment="Top" Width="120" />
        <Label Content="CompanyName" Margin="191.933,247,223,0" Name="label1" VerticalAlignment="Top" Foreground="White" d:LayoutOverrides="Width" />
    </Grid>
</Window>

MainWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Microsoft.Reporting.WinForms;
using System.Net;
using System.IO;
using System.Data.Services.Client;
using System.ComponentModel;

namespace 테스트용
{
   
    /// <summary>
    /// MainWindow.xaml에 대한 상호 작용 논리
    /// </summary>
    public partial class MainWindow : Window
    {

        
       
        CollectionViewSource __CollectionViewSource = null;
        //엔티티 만들기
        ServiceReference1.NorthwindEntities __Context = new ServiceReference1.NorthwindEntities(new Uri("http://localhost:56323/WcfDataService1.svc"));
       
        public MainWindow()
        {
            this.Loaded += new RoutedEventHandler(MainWindow_Loaded);           
        }

        void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            //Customers 데이터를 CollectionViewSource 담아준다.
            //
            var query = from c in __Context.Customers
                        select c;

            __CollectionViewSource = new CollectionViewSource();
            __CollectionViewSource.Source = query.ToList();
            this.dataGrid1.ItemsSource = __CollectionViewSource.View;
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            ICollectionView _customerView = CollectionViewSource.GetDefaultView(__CollectionViewSource.View);
            _customerView.Filter = CustomerFilter;
        }

        //검색 메소드
        private bool CustomerFilter(object item)
        {
            string searchKeyworld = this.textBox1.Text;
            ServiceReference1.Customers customer = item as ServiceReference1.Customers;
            
            return customer.CompanyName.Contains(searchKeyworld);

        }
    }
}

DataService<T>.OnStartProcessingRequest 메서드

각 요청을 처리하기 전에 호출됩니다. 일괄 요청의 경우 최상위 일괄 요청에 대해 한 번, 일괄 처리의 각 작업에 대해 한 번 호출됩니다.

네임스페이스: System.Data.Services
어셈블리: System.Data.Services(System.Data.Services.dll)

http://localhost:56323/WcfDataService1.svc/

우선프로그램 인증(로그인)을 받을떄

웹서비스 사이트에도 세션인증을 해줍니다.

그리고 OnStartProcessingRequest 이 일어날 때 세션체크 또는 폼 인증을 해주고 없으면 오류를 발생 시켜주면된다.

http://localhost:56323/WcfDataService1.svc/

In this tutorial you will learn how to create a Windows Forms Data Source from an Entity in an Entity Data Model. This tutorial assumes that you have installed the World example database, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page. It will also be convenient for you to create a connection to the World database after it is installed. For instructions on how to do this see Section 20.2.3.1, “Making a connection”.

Creating a new Windows Forms application

The first step is to create a new Windows Forms application.

Adding an Entity Data Model

You will now add an Entity Data Model to your solution.

Adding a new Data Source

You will now add a new Data Source to your project and see how it can be used to read and write to the database.

Using the Data Source in a Windows Form

You will now learn how to use the Data Source in a Windows Form.

Adding Code to Populate the Data Grid View

You are now ready to add code to ensure that the Data Grid View control will be populated with data from the City database table.

Adding Code to Save Changes to the Database

You will now add code to enable you to save changes to the database.

The Binding source component ensures that changes made in the Data Grid View control are also made to the Entity classes bound to it. However, that data needs to be saved back from the entities to the database itself. This can be achieved by the enabling of the Save button in the Navigator control, and the addition of some code.

아직 베타지만 그래도 ㄳ

http://www.oracle.com/technetwork/topics/dotnet/downloads/oracleefbeta-302521.html

Linq Null Value SELECT

아래와 같이 ResourcesName 이라는 필드에 값이Null 일때 오류를 뱉어낸다

            var query = list.AsQueryable();

            var result = from c in query
                         where c.EngName.ToLower().Contains(this.txtSearchString.Text.ToLower()) || c.KorName.ToLower().Contains(this.txtSearchString.Text.ToLower())
                         || (c.ResourcesName == null ? "" : c.ResourcesName).Contains(this.txtSearchString.Text)
                         select c;

뭐 일단 이렇게 하니깐 잘되네??

DataSet ds = new DataSet();
FillOrders(ds); // this method fills the DataSet from a database

DataTable orders = ds.Tables["SalesOrderHeader"];

var query = from o in orders.ToQueryable()
            where o.Field<bool>("OnlineOrderFlag") == true
            select new { SalesOrderID = o.Field<int>("SalesOrderID"),
                         OrderDate = o.Field<DateTime>("OrderDate") };

foreach(var order in query) {
    Console.WriteLine("{0}\t{1:d}", order.SalesOrderID, order.OrderDate);
}