ASP.NET GridView usage

Дөнгөж үүсгэж байх үеийн Event дээр нь DropDownList нүдэнд өгөгдлийг нь өгөхийн тулд:

If you are now using a GridView. Please write something in the GridView_GridViewRowDataBound event and do this:
protected void GridView1_GridViewRowDataBound(object sender, GridViewRowEventArgs e)
{
 if(e.Row.RowType == DataControl.DataRow)
 {
  //Find the Dropdownlist
  Dropdownlist ddrList = (Dropdownlist)e.Row.FindControl("id of dropdownlist");
  ddrList.DataSource = PrimaryKeys from the "Column" class.
  ddrList.DateTextField = "xxx";
  ddrList.DataValueField = "yyy";
  ddrList.DataBind();
 }
}

MSSQL Database Creation & RelationShip simple

Жишээ 1.

CREATE TABLE [dbo].[EmployeeTerritory](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [RowVersion] [int] NOT NULL,
 CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
(
    [EmployeeID] ASC, [TerritoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmployeeTerritory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeTerritory_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[EmployeeTerritory] CHECK CONSTRAINT [FK_EmployeeTerritory_Employee]
GO

ALTER TABLE [dbo].[EmployeeTerritory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeTerritory_Territory] FOREIGN KEY([TerritoryID])
REFERENCES [dbo].[Territory] ([TerritoryID])
GO

ALTER TABLE [dbo].[EmployeeTerritory] CHECK CONSTRAINT [FK_EmployeeTerritory_Territory]
GO

ALTER TABLE [dbo].[EmployeeTerritory] ADD  CONSTRAINT [DF_EmployeeTerritory_RowVersion]  DEFAULT ((0)) FOR [RowVersion]
GO


Жишээ 2.

USE [MyDatabaseName]
GO
CREATE TABLE [dbo].[UserRole](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL,
    [RoleId] [bigint] NOT NULL,
 CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_Role]
GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([Id])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_User]
GO

MSSQL SERVER vs MYSQL

MSSQL SERVER

CREATE TABLE [dbo].[table1](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](10) primary key NOT NULL,
    [col] [nvarchar](20) NOT NULL
)

exec sp_RENAME 'table1.col', 'age', 'COLUMN'

ALTER TABLE tableName
ALTER COLUMN age int NOT NULL


MYSQL SERVER

mysql> create table table1(
    -> id int(10) unsigned primary key auto_increment,
    -> name varchar(100) not null,
    -> col smallint);
Query OK, 0 rows affected (0.38 sec)

mysql> alter table table1
    -> change column col age int;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)     | NO   |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.13 sec)

MSSQL бүтээгдэхүүний ӨРТӨГ ОРЛОГО ТООЛЛОГО ЗАРЛАГА АШИГ тооцох

Санхүү эсвэл бүтээгдэхүүн үйлчилгээний програм хийхийг хүсдэг залуусд зориулж нэг ийм жишээ гаргалаа. MSSQL Editor дээр хуулж тавиад ажиллуулаад үзээрэй энэ их хэрэгтэй жишээ болсон байх гэж бодож байна. Бүтээгдэхүүн үйлчилгээний өртөг зардал орлого ашиг тооцоход энэ жишээ хэрэг болно. Миний зүгээс зориулж байна

USE [test]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 06/21/2011 19:28:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
    [ProductPkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Size] [int] NOT NULL,
    [Price] [money] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Income]    Script Date: 06/21/2011 19:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Income](
    [IncomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [IncomeSize] [int] NOT NULL,
    [IncomeAmount] [money] NOT NULL,
    [IncomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Outcome]    Script Date: 06/21/2011 19:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Outcome](
    [OutcomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [OutcomeSize] [int] NOT NULL,
    [OutcomeAmount] [money] NOT NULL,
    [OutcomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


Өгөгдөлөөр дүүргие

insert Product values('Talh',1,950)
insert Product values('Tamhi',20,2000)
insert Product values('Juus',1,850)
insert Product values('Haraa',750,7500)

insert Income values(1,10,7500,'2011-06-21')--10sh 750
insert Income values(2,100,8000,'2011-06-21')--5 bottle 1600
insert Income values(3,16,11200,'2011-06-21')--16sh 700
insert Income values(3,8,5600,'2011-06-21')--8sh 700
insert Income values(4,3000,22000,'2011-06-21')--4 bottle 5500

insert Outcome values(1,8,950,'2011-06-21')--8sh 950
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,10,1000,'2011-06-21')--10sh 1000
insert Outcome values(3,10,8500,'2011-06-21')--10sh 850
insert Outcome values(3,2,1700,'2011-06-21')--2sh 850
insert Outcome values(3,1,850,'2011-06-21')--1sh 850
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,750,7500,'2011-06-21')--1 bottle 7500
insert Outcome values(4,300,3000,'2011-06-21')--50ml 500


Ашиг орлого тооцие


FirstAmount зарагдсан барааг анх худалдаж авсан дүн
GetAmount зарагдсан бараанаас олсон ашиг

select I.*, O.OutcomeSize, O.OutcomeAmount
, (I.IncomeSize-O.OutcomeSize) as ChangeSize
, ((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize) as FirstAmount
, (O.OutcomeAmount -((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize)) as GetAmount
from (select ProductPkID, sum(IncomeSize) as IncomeSize, sum(IncomeAmount) as IncomeAmount, IncomeDate
    from Income group by ProductPkID, IncomeDate) I
inner join (select ProductPkID, sum(OutcomeSize) as OutcomeSize, sum(OutcomeAmount) as OutcomeAmount, OutcomeDate
    from Outcome group by ProductPkID, OutcomeDate) O
on I.ProductPkID=O.ProductPkID
order by ProductPkID

select * from Income order by ProductPkID
select * from Outcome order by ProductPkID

Java Hibernate with MSSQL EXPRESS hibernate simple utilities

 Хэрэв sql express 2005 дээр hibernate хийх гэж байгаа бол connection error гарвал дараах байдлаар асуудлыг ингэж тохируулж болно. энэ хэсэг нэг их чухал биш угаасаа

NetBeans iin hibernate category -оос сонгоод оруулчина. дараалал нь миний блог дээр байгаа

IDE: Netbeans 6.5

Framework Hibernate:3.2.5

Problems Faced:
Step 1. Start NetBeans.
Open Services.
Right click on Database.
Click new connection.
Do the entries: According to the database exists in SQL SERVER 2005. Like: Driver name:MS SQL Server 2005
host: localhost
database: abc
user: zyx
password: ******
Click OK.

Problem 1: First I am going to make new connection with Database: MS SQL Server 2005 Express, through sqljdbc dirver 1.2. It wasn't be connected earlier.
SQL Server is running.

It gave an Exception "The TCP/IP connection to the host is failed. java.net.ConnectException. Connection refused."


Now, I open SQL Server 2005 Configuration Manager and there I open SQL Server Network Configuration>protocols for SQLEXPRESS>TCP/IP(right click) Properties>IP Addresses tab> IPAll> TCP Port:1433(changed)> apply and ok.




Энэ бол жишээ код ингэж олон серверт тохируулан програмчилж болно
үзээд тохирох газар нь байрлуулаарай

PropertyConfiguration бол зүгээр л нэг Properties уншиж бичдэг класс өөрсдөө зохиогоод эсвэл шууд хатуу текст бичээд шалгачихсан ч болно.

HibernateSession.java

// Create the SessionFactory from standard (hibernate.cfg.xml)
            // config file.
            AnnotationConfiguration anno = new AnnotationConfiguration().configure();
            try {
                if (PropertyConfiguration.index != null) {
                    String serverName = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_ServerName);
                    String databaseName = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_DatabaseName);
                    String connType = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_ConnectionType);
                    if (connType.toLowerCase().equals(SqlConnectionType.Mssql.name().toLowerCase()))
                    {
                        String url = "jdbc:sqlserver://" + serverName + ":1433;databaseName=" + databaseName;
                        anno.setProperty("hibernate.connection.url", url);
                    }
                    else if (connType.toLowerCase().equals(SqlConnectionType.MssqlExpress.name().toLowerCase()))
                    {
                        String url = "jdbc:microsoft:sqlserver://" + serverName + ":1433;databaseName=" + databaseName;
                        anno.setProperty("hibernate.connection.url", url);
                    }
                }
            } catch (Exception ex) {
                System.err.println("new AnnotationConfiguration().configure() finished.");
                System.err.println("Initial SessionFactory configuration failed." + ex);
            }


HibernateUtil.java
Энэ бол бэлэн код юм ямар нэг класс үүсгээд дотор нь хуулчих тэгээд HibernateSession.java тайгаа хослоод ажиллачина.
Нэг main() { дотроо туршаад үзээрэй }

public static List execute(String procedure, List<DataHolderEntity> filter) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();

            Query query = (procedure.startsWith("select ") || procedure.startsWith("from "))
                    ? newSession.createQuery(procedure) : newSession.getNamedQuery(procedure);
            addQueryParameters(query, filter, false);
            List data = query.list();

            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static int execute(String procedure, List<DataHolderEntity> filter, boolean onlyUseIndex0){
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();

            Query query = (procedure.startsWith("select ") || procedure.startsWith("from "))
                    ? newSession.createQuery(procedure) : newSession.getNamedQuery(procedure);
            addQueryParameters(query, filter, onlyUseIndex0);
            int ret = query.executeUpdate();

            newSession.close();

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }

    public static int executeSave(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.save(model);

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeUpdate(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.saveOrUpdate(model);

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeDelete(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.delete(model);
           
            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeDelete(String tableName, String keyField, Long id) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();

            Query q = newSession.createQuery("delete from " + tableName + " as s where s." + keyField + " =:" + keyField);
            q.setLong(keyField, id);
            int rowCount = q.executeUpdate();

            newTransaction.commit();
            newSession.close();

            return rowCount;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }

    public static Object executeNonQuery(String hql, Object parameters, boolean scaler) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            boolean b = hql.startsWith("select ") || hql.startsWith("from ");
            Query query = b ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            if (parameters != null) {
                if (b) {
                    Object[] iparams = (Object[])parameters;
                    for(int i = 0; i < iparams.length; i++) {
                        query.setParameter(i, iparams[i]);
                    }
                } else {
                    String[] pars = query.getNamedParameters();
                    Map pmap = (Map)parameters;
                    for (String par : pars) {
                        query.setParameter(par, pmap.get(par));
                    }
                }
            }
            Object object = null;
            if(scaler) {
                object = query.uniqueResult();
            } else {
                object = query.executeUpdate();
            }
            newTransaction.commit();
            newSession.close();

            return object;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }
   
    public static List executeQuery(String hql) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            Query query = (hql.startsWith("select ") || hql.startsWith("from "))
                    ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            List data = query.list();

            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static List executeQuery(String hql, Object[] parameters) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
            boolean b = hql.startsWith("select ") || hql.startsWith("from ");
            Query query = b ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            if (parameters != null) {
                if (b) {
                    for(int i = 0; i < parameters.length; i++) {
                        query.setParameter(i, parameters[i]);
                    }
                } else {
                    String[] pars = query.getNamedParameters();
                    int i = 0;
                    for (String par : pars) {
                        query.setParameter(par, parameters[i]);
                        i++;
                    }
                }
            }
            List data = query.list();

            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static List executeCriteriaQuery(CriteriaHandler criteriaHandler, Class<?> type) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            Criteria crit = newSession.createCriteria(type);
            crit = criteriaHandler.CriteriaConfigure(crit);
            List data = crit.list();
           
            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static boolean Login(String tableName, String name, String password) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();

            Query query = newSession.createQuery("from "
                    + (tableName == null ? "UserInfo" : tableName) + " where ( Email=? or Name=? ) and Password=?");
            query.setString(0, name);
            query.setString(1, name);
            query.setString(2, password);
            List l = query.list();
           
            newTransaction.commit();
            newSession.close();
           
            if (l.size() > 0) {
                ProgramSession.User.fill(l.get(0));
                return true;
            } else {
                return false;
            }           
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return false;
        }
    }