천객만래 [千客萬來] (It has an interminable succession of visitors)


MySQL 5 C# sample code using ObjectDataSources



Introduction

I created this example because I could not find a simple explanation for using MySQL 5 with ObjectDataSources in ASP.NET 2.0.

Let me say, I am really impressed with MySQL. I was able to install it easily on my Windows XP machine and get it running in about an hour. I am a long time MS SQL user, and was very frustrated with trying to use Oracle and Firebird. I realize, the problem is that I am spoiled from MS SQL Server, but hey I''m busy and I like easy to use tools :)

If you''re getting started with MySQL and ASP.NET, then I recommend these steps:

  1. Go to the MySQL website, download and install “Current Release (recommended).
  2. Download and install: MySQL Administrator (to administer your MySQL server, the first download just installs only the server).
  3. Download and install: Connector/Net 1.0 (you need this to get your ASP.NET pages to talk to your MySQL server).
  4. You can also download: MySQL Query Browser – (a graphical client to work with your MySQL databases and run queries).
  5. Read and follow this guide: A Step-by-Step Guide to Using MySQL with ASP.NET.

Using the code

To install the code:

  1. You must have MySQL 5 up and running.
  2. Install MySQL Connector/Net 1.0.
  3. Create a MySQL 5 database named Test.
  4. Create a table in that database called Message:
    CREATE TABLE test.message (
    
        Entry_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        Name VARCHAR(45),
        Email VARCHAR(45),
        Message VARCHAR(200),
        PRIMARY KEY (Entry_ID)
        )
        AUTO_INCREMENT=32
        CHARACTER SET latin1 COLLATE latin1_swedish_ci;
  5. Create these four MySQL stored procedures in the Test database:
    PROCEDURE `test`.`DeleteMessage`(IN param1 INT)
    BEGIN
    Delete From test.message
    WHERE Entry_ID = param1;
    END
    PROCEDURE `test`.`InsertMessage`(IN param1 VARCHAR(50), IN param2 
        VARCHAR(50), IN param3 VARCHAR(200))
    BEGIN
    INSERT INTO message(Name, Email, Message)
    VALUES(param1,param2,param3);
    END
    PROCEDURE `test`.`ShowAll`()
    BEGIN
    SELECT 
      message.Entry_ID,
      message.Name, 
      message.Email, 
      message.Message
    FROM
      test.message;
    END
    PROCEDURE `test`.`UpdateMessage`(IN paramkey INT, IN param1 VARCHAR(50), 
        IN param2 VARCHAR(50), IN param3 VARCHAR(200))
    BEGIN
    UPDATE    message
    SET              Name = param1, Email = param2, Message = param3
    WHERE     (message.Entry_ID = paramkey);
    END
  6. Unzip "MySQL" and configure IIS to point to it. Make sure you configure the web server to use ASP.NET 2.0.
  7. Open "web.config" and change the line:
    <add name="MySQLConnectionString" connectionString="server=localhost; 
       user id=myuser; password=mypass; database=test; pooling=false;" 
       providerName="MySql.Data.MySqlClient"/>

    to connect to your MySQL database.

  8. Browse to the default.aspx page through IIS.

This is the class that uses Generics to supply the data that is consumed by the ObjectDataSource control:

using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.ComponentModel;

[DataObject(true)]
public static class MessagesDB
{
    private static string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings
        ["MySQLConnectionString"].ConnectionString;
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<MessageItem> GetMessages()
    {
        MySqlCommand cmd = new MySqlCommand("ShowAll", 
                           new MySqlConnection(GetConnectionString()));
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection.Open();
        MySqlDataReader dr = 
           cmd.ExecuteReader(CommandBehavior.CloseConnection);

        List<MessageItem> MessageItemlist = new List<MessageItem>();
        while (dr.Read())
        {
            MessageItem MessageItem = new MessageItem();
            MessageItem.Entry_ID = Convert.ToInt32(dr["Entry_ID"]);
            MessageItem.Message = Convert.ToString(dr["Message"]);
            MessageItem.Name = Convert.ToString(dr["Name"]);
            MessageItem.Email = Convert.ToString(dr["Email"]);
            MessageItemlist.Add(MessageItem);
        }
        dr.Close();
        return MessageItemlist;
    }

    [DataObjectMethod(DataObjectMethodType.Insert)]
    public static void InsertMessage(MessageItem MessageItem)
    {
        MySqlCommand cmd = new MySqlCommand("InsertMessage", 
                           new MySqlConnection(GetConnectionString()));
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Name));
        cmd.Parameters.Add(new MySqlParameter("param2", MessageItem.Email));
        cmd.Parameters.Add(new MySqlParameter("param3", MessageItem.Message));
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
    }

    [DataObjectMethod(DataObjectMethodType.Update)]
    public static int UpdateMessage(MessageItem MessageItem)
    {
        MySqlCommand cmd = new MySqlCommand("UpdateMessage", 
                           new MySqlConnection(GetConnectionString()));
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new MySqlParameter("paramkey", MessageItem.Entry_ID));
        cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Name));
        cmd.Parameters.Add(new MySqlParameter("param2", MessageItem.Email));
        cmd.Parameters.Add(new MySqlParameter("param3", MessageItem.Message));
        cmd.Connection.Open();
        int i = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return i;
    }

    [DataObjectMethod(DataObjectMethodType.Delete)]
    public static int DeleteMessage(MessageItem MessageItem)
    {
        MySqlCommand cmd = new MySqlCommand("DeleteMessage", 
                new MySqlConnection(GetConnectionString()));
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Entry_ID));
        cmd.Connection.Open();
        int i = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return i;
    }

The class above uses the class "MessageItem" to pass the parameters to and from the ObjectDataSource control:

using System;

public class MessageItem
{
    int _Entry_ID;
    string _Message;
    string _Name;
    string _Email;

    public MessageItem()
    {
    }

    public int Entry_ID
    {
        get
        {
        return _Entry_ID;
        }
        set
        {
        _Entry_ID = value;
        }
    }

    public string Message
    {
        get
        {
            return _Message;
        }
        set
        {
            _Message = value;
        }
    }

    public string Name
    {
        get
        {
            return _Name;
        }
        set
        {
            _Name = value;
        }
    }

    public string Email
    {
        get
        {
            return _Email;
        }
        set
        {
            _Email = value;
        }
    }
}

This is the .aspx file that contains the ObjectDataSource control as well as a GridView for editing data and a DetailsView for inserting a record:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
   TypeName="MessagesDB" OldValuesParameterFormatString="original_{0}" 
   SelectMethod="GetMessages" DataObjectTypeName="MessageItem" 
   DeleteMethod="DeleteMessage" InsertMethod="InsertMessage" 
   UpdateMethod="UpdateMessage">
</asp:ObjectDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" 
      AutoGenerateColumns="False" 
      DataSourceID="ObjectDataSource1" 
      DataKeyNames="Entry_ID">
 <Columns>
   <asp:BoundField DataField="Entry_ID" HeaderText="Entry_ID" 
           SortExpression="Entry_ID" Visible="False" />
   <asp:CommandField ShowEditButton="True" />
   <asp:CommandField ShowDeleteButton="True" />
   <asp:BoundField DataField="Name" 
         HeaderText="Name" SortExpression="Name" />
   <asp:BoundField DataField="Email" 
         HeaderText="Email" SortExpression="Email" />
   <asp:BoundField DataField="Message" 
         HeaderText="Message" SortExpression="Message" />
 </Columns>
</asp:GridView>
<br />
<strong><span style="text-decoration: underline">
      Insert New Record:</span></strong><br />

<asp:DetailsView ID="DetailsView1" runat="server" 
    AutoGenerateRows="False" BorderStyle="None"
    CellSpacing="5" DataSourceID="ObjectDataSource1" 
    DefaultMode="Insert" GridLines="None"
    Height="50px" Width="300px">
  <Fields>
    <asp:BoundField DataField="Name" HeaderText="Name" />
    <asp:BoundField DataField="Email" HeaderText="Email" />
    <asp:BoundField DataField="Message" HeaderText="Message" />
    <asp:CommandField ButtonType="Button" 
         ShowInsertButton="True" ShowCancelButton="False" />
  </Fields>
</asp:DetailsView>

Note

The assembly "MySql.Data.dll" is in the "/bin" directory so the "MySql.Data.MySqlClient" will work.

I hope this helps!



MySQL_CS_samplesource.zip



Posted by SB패밀리

오라클 테이블 존재여부 확인하기 


오라클 테이블 존재 여부 알아보기
-- table_name은 대문자로만 저장되기 때문에 찾을 문자열을 대문자로 바꿔서 비교해야 한다



SQL>SELECT TABLE_NAME 
        FROM USER_TABLES
        WHERE TABLE_NAME = UPPER('찾을 테이블 이름')

Posted by SB패밀리

** table A을 table B로 복사하자...

1. column 수와 속성이 같은 table 생성.

create table B
as
select * from A
where id=2


2. Data 복사

Insert into B (필드들)
select 동일한 필드들 from A

## 'values' 가 빠진다.

테이블 B에 insert할 지정된 필드와 SELECT 문의 필드의 순서와 이름이 동일해야한다.

MERGE문을 이용하면 나중에 변경된 Data도 수정하기 용이하다.

* 이 두작업을 한 번에 할 수 있다.

create table B
as
select * from A

4. 테이블에 다수의 레코드를 복사하는 방법

 

SELECT *

INTO Table_B

FROM Table_A

 

INSERT + SELECT 방식 말고, SELECT + INSERT를 이용하여, 테이블에 행을 넣을 수도 있다.

이 방법은 테이블 복사 할 때 많이 쓰이며,  Table_B라는 테이블을 만들면서,  Table_A의 모든 데이터를 Table_B에 넣는다.

 

INSERT SELECT은 SELECT 로 값을 가져 와서 INSERT 하는것이고,

SELECT INTO는 SELECT로 가져온것을 INTO 다 내보내는 것을 의미한다.

Posted by SB패밀리

기억이 잘 안되고 아주 가끔 사용되는 팁이라서 기록해 둠


* 방금 INSERT 한 자동증가 값(ID) 가져오기
 SELECT @@IDENTITY (현재 세션에서만 사용가능)

* 현재 테이블의 가장 큰 값 가져오기
 SELECT IDENT_CURRENT('테이블명')

* 수행된 SQL문에 영향을 받은 행의 수
 SELECT @@ROWCOUNT

* 테이블 정보보기
 EXEC SP_HELP 테이블

* 컬럼 정보보기
 EXEC SP_COLUMNS 테이블

* 엘리어스 사용
 SELECT 필드 AS 엘리어스명 FROM 테이블명 (별칭에 특수문자가 있다면 []를 사용

* 일부분만 가져오기
 SELECT TOP N 컬럼 FROM 테이블명  (N 레코드수만큼 가져오기)

* 패턴매칭 (+키워드 예약어)
 SELECT 컬럼 FROM 테이블 WHERE 컬럼 LIKE '%키워드%'
  _ : 어떤 것이든 한 문자
  % : 없거나 아무 글자가 포함되거나
  [ ] : [ ] 안에 있는 글자들 - [B-F]
  [^] : ^다음에 있는 글자를 제외한 다른 것

* 중복제거
 SELECT DISTINCT 컬럼 FROM 테이블

               

Posted by SB패밀리

-- 시작전 serveroutput을 on으로 바꾸어 output메시지를 볼수있도록 한다.

SET serveroutput on;

DECLARE
  psr_return NUMBER;
  psr_msg VARCHAR2(2000);
BEGIN

-- 파라미터 변수에 값 바인딩
  psr_return := 0000;
  psr_msg       := 'TEST';

 -- 프로시저 실행
  SP_APPROVELINE_SAVE ( 0, 'TEST', '20090925063000', '0001', psr_return, psr_msg);

  DBMS_OUTPUT.PUT_LINE('psr_return : '|| psr_return);
  DBMS_OUTPUT.PUT_LINE('psr_msg : '|| psr_msg);
END;

-- DB사용 툴에따라 빼줘야 작동 될 수 도있다.

Posted by SB패밀리