categorydaoimpl.cs

一个超市管理系统,没有错误,非常好,里面什么都有!很使用,很有用

using System;
using System.Collections.Generic;
using System.Text;
using System.Data ;
using System.Data .SqlClient;

//引入命名空间
using DaFanRongMIS.Model.Common;

/*类名：CateGoryDAOImpl
 *作用：用于维护产品类型与材料类型
 *创建者：王丽丽
 * 创建时间：2008-01-15
 * 修改者：
 * 修改时间：
 * 修改内容：
 * 调用者：
 * 被调用者： CateGoryForm、CateGoryOperForm
 */

namespace DaFanRongMIS.Model.CateGory
{
    
    class CateGoryDAOImpl:CateGoryDAO
    {
        public string AddCateGory(CateGoryEntity CateGory, MaterialOrProduct MP)
        {
            string strret = "";
            string id = GetId(MP);
            try
            {
               
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "insert into CateGory values(@id,@Name,@Memo)";
                cmd.Parameters.Add("@id", id);
                cmd.Parameters.Add("@Name", CateGory.Name);
                cmd.Parameters.Add("@Memo", CateGory.Memo);
                cmd.Connection = ConnectionDataBase.getConOpen();                
                cmd.ExecuteNonQuery();
                strret = "OK";                              
            }
            catch (Exception ee)
            {
                strret = ee.Message;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return strret;
        }//end mothed AddCateGory

        public string UpdateCateGory(CateGoryEntity CateGory)
        {
            string strret = "";
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "update CateGory set Name=@Name,Memo=@Memo where id=@id";
                cmd.Parameters.Add("@id", CateGory.ID);
                cmd.Parameters.Add("@Name", CateGory.Name);
                cmd.Parameters.Add("@Memo", CateGory.Memo);
                cmd.Connection = ConnectionDataBase.getConOpen();
                cmd.ExecuteNonQuery();
                strret = "OK"; 
            }
            catch (Exception ee)
            {
                strret = ee.Message;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return strret;

        }//end mothed UpdateCateGory
        public string DeleteCateGory(CateGoryEntity CateGory)
        {
            string strret = "";
            try
            {
                bool Del = true ;
                //到商品表里查询类型是否正在使用，若使用则不让删除
                 SqlCommand cmd = new SqlCommand();
                 cmd.CommandText = "select count(*) from Material where CateGoryID =@CateGoryID";
                 cmd.Parameters.Add("@CateGoryID", CateGory.ID);
                 cmd.Connection = ConnectionDataBase.getConOpen();
                 int i = (int)cmd.ExecuteScalar();
                 if (i > 0)
                 {   Del = false; } //不能删除
                 cmd.Dispose();
                 ConnectionDataBase.getConClose();

                 //到材料表里查询类型是否正在使用，若使用则不让删除
                 cmd.CommandText = "select count(*) from Product where CateGoryID =@CateGoryID";
                 cmd.Connection = ConnectionDataBase.getConOpen();
                 i = (int)cmd.ExecuteScalar();
                 if (i > 0)
                 { Del = false; } //不能删除
                 cmd.Dispose();
                 ConnectionDataBase.getConClose();


                 if (Del)  //没有使用则可以删除
                 {
                     cmd = new SqlCommand();
                     cmd.CommandText = "delete from CateGory where id=@id";
                     cmd.Parameters.Add("@id", CateGory.ID);
                      cmd.Connection = ConnectionDataBase.getConOpen();
                     cmd.ExecuteNonQuery();
                     strret = "OK";
                 }
                 else
                 {
                     strret = "Sorry"; 
                 }

            }
            catch (Exception ee)
            {
                strret = ee.Message;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return strret;
        }//end mothed DeleteCateGory
        public DataTable SelectCateGory()
        {
            DataTable dtret = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select *  from CateGory ";
                cmd.Connection = ConnectionDataBase.getConOpen();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataSet ds=new DataSet ();
                da.Fill (ds,"CateGory");
                dtret=ds.Tables["CateGory"];
            }
            catch (Exception ee)
            {
                dtret = null;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return dtret;
        }//end mothed  SelectCateGory

        public enum MaterialOrProduct { Material, Product };   //定义类型查询条件为商品或者材料

        public DataTable SelectCateGoryName(MaterialOrProduct MP )//返回类型名称，条件为是材料还是产品
        {
            DataTable dtret = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                string str="";
                if (MP == MaterialOrProduct.Material)
                    str = "select Name  from CateGory where id like 'M%'";
                else
                    str = "select Name  from CateGory where id like 'P%'";
                cmd.CommandText = str;
                cmd.Connection = ConnectionDataBase.getConOpen();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds, "CateGory");
                dtret = ds.Tables["CateGory"];

            }
            catch (Exception ee)
            {
                dtret = null;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return dtret;
        }//end mothed  SelectCateGory

        private string GetId(MaterialOrProduct MP)
        {
            string strret = "";
            try
            {
                string str = "";  //设置查询条件是商品还是材料
                if (MP == MaterialOrProduct.Material)
                {
                    str = "select top 1 ID from CateGory where Id like 'M%'order by ID desc";
                }
                else
                {
                    str = "select top 1 ID from CateGory where Id like 'P%'order by ID desc";
                }
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText =str;
                cmd.Connection = ConnectionDataBase.getConOpen();
                SqlDataReader dr= cmd.ExecuteReader();
                if (dr.Read())  //已存在类型
                {
                    string strId = dr.GetString(0);
                    strId = strId.Substring(1);
                    int intId = Convert.ToInt32(strId);
                    if (intId < 9)
                    {
                        intId = intId + 1;
                        if (MP == MaterialOrProduct.Material)
                        {
                            strret = "M0" + intId.ToString ();
                        }
                        else
                        {
                            strret = "P0" + intId.ToString();
                        }
                    }
                    else
                    {
                        intId = intId + 1;
                        if (MP == MaterialOrProduct.Material)
                        {
                            strret = "M" + intId.ToString();
                        }
                        else
                        {
                            strret = "P" + intId.ToString();
                        }
                    }
                }

                else //首个类型，从１开始
                {
                    if (MP == MaterialOrProduct.Material)
                    {
                        strret = "M" +"01";
                    }
                    else
                    {
                        strret = "P" + "01";
                    }
                }
                dr.Close();
                ConnectionDataBase.getConClose();


            }
            catch (Exception ee)
            {
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return strret;
        }//end mothed GetId

        #region 此方法无条件查询，返回类别名称，用于填充Combox


        public DataTable SelectCategory()
        {
            //数据表类型变量，用于接收满足查询条件的结果
            DataTable dt;
            try
            {
                SqlCommand cmd = null;
                //声明适配器对象da
                SqlDataAdapter da = new SqlDataAdapter();
                //定义数据集对象ds
                DataSet ds = new DataSet();

                //使用参数方式查询
                cmd = new SqlCommand("select distinct name from Category ", ConnectionDataBase.getConOpen());

                //指定适配器属性SelectCommand，与命令对象绑定
                da.SelectCommand = cmd;

                //将查询结果到数据集中，以表的形式存在
                da.Fill(ds, "Category");

                //得到这张表的记录，返回给变量dt
                dt = ds.Tables["Category"];


            }
            catch (Exception ee)
            {
                dt = null;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return dt;

        }
        #endregion

        #region 此方法按类别名称条件查询，返回名称和编号，用于选择Combox中名称时得到编号

        public DataTable SelectCategorybyName(string name)
        {
            SqlCommand cmd = null;
            //数据表类型变量，用于接收满足查询条件的结果
            DataTable dt;
            try
            {
                //声明适配器对象da
                SqlDataAdapter da = new SqlDataAdapter();
                //定义数据集对象ds
                DataSet ds = new DataSet();

                //使用参数方式查询
                cmd = new SqlCommand("select name as '类别名称',id as '类别编号' from Category where  name = @name ", ConnectionDataBase.getConOpen());

                //指定参数列表

                SqlParameter paraname = new SqlParameter("@name", SqlDbType.VarChar, 21);
                paraname.Value = name;
                cmd.Parameters.Add(paraname);



                //指定适配器属性SelectCommand，与命令对象绑定
                da.SelectCommand = cmd;

                //将查询结果到数据集中，以表的形式存在
                da.Fill(ds, "Category");

                //得到这张表的记录，返回给变量dt
                dt = ds.Tables["Category"];


            }
            catch (Exception ee)
            {
                dt = null;
            }
            finally
            {
                ConnectionDataBase.getConClose();
            }
            return dt;

        }
        #endregion

        #region 全查询
        public DataTable Select()
        {
            //声明数据适配器
            SqlDataAdapter da = new SqlDataAdapter();
            //声明数据集
            DataSet ds = new DataSet();
            //声明数据表
            DataTable dt = new DataTable();
            da.SelectCommand = new SqlCommand("select * from CateGory", ConnectionDataBase.getConOpen());
            da.Fill(ds);
            dt = ds.Tables[0];
            return dt;
        }
        #endregion
    }
}