inwaredaoimpl.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DaFanRongMIS.Model.Material;
using DaFanRongMIS.Model.Common;

namespace DaFanRongMIS.Model.InWarehoures
{
    class InWareDAOImpl:InWareDAO
    {
        public string InWare(InWareMainEntity InWareMain, InWareDetailEntity[] InWareDetail, InWareStoreEntity[] InWareStore,int flag)
        {
            string str = "";

            //声明并设置SqlCommand命令

            SqlCommand cmd = new SqlCommand();
            SqlTransaction tran =null;
            try
            {   cmd.Connection = ConnectionDataBase.getConOpen();
                tran = cmd.Connection.BeginTransaction("Transaction Of InWare");
                cmd.Transaction = tran;
                

                #region 材料管理主表
                cmd.CommandText = "insert into MaterialMain values(@ID,@ShopID,@Operator,@BusinessTime,@BusinessType,@Destination,@Memo)";
                //结束声明和设置SqlCommand命令

                //参数赋值

                SqlParameter IDAdd = new SqlParameter("@ID", SqlDbType.VarChar, 20);
                IDAdd.Value = InWareMain.ID;
                cmd.Parameters.Add(IDAdd);

                SqlParameter ShopIDAdd = new SqlParameter("@ShopID", SqlDbType.VarChar, 2);
                ShopIDAdd.Value = InWareMain.ShopID;
                cmd.Parameters.Add(ShopIDAdd);

                SqlParameter OperatorAdd = new SqlParameter("@Operator", SqlDbType.VarChar, 20);
                OperatorAdd.Value = InWareMain.Operator;
                cmd.Parameters.Add(OperatorAdd);

                SqlParameter BusinessTimeAdd = new SqlParameter("@BusinessTime", SqlDbType.VarChar, 60);
                BusinessTimeAdd.Value = InWareMain.BusinessTime;
                cmd.Parameters.Add(BusinessTimeAdd);

                SqlParameter BusinessTypeAdd = new SqlParameter("@BusinessType", SqlDbType.VarChar, 10);
                BusinessTypeAdd.Value = InWareMain.BusinessType; ;
                cmd.Parameters.Add(BusinessTypeAdd);

                SqlParameter DestinationAdd = new SqlParameter("@Destination", SqlDbType.VarChar, 40);
                DestinationAdd.Value = InWareMain.Destination;
                cmd.Parameters.Add(DestinationAdd);

                SqlParameter MemoAdd = new SqlParameter("@Memo", SqlDbType.VarChar, 2000);
                MemoAdd.Value = InWareMain.Memo;
                cmd.Parameters.Add(MemoAdd);

                //打开库,执行,关闭库
                cmd.ExecuteNonQuery();

                //清除参数
                cmd.Parameters.Clear();
                #endregion
             
                #region 材料管理明细表
                for (int i = 0; i < InWareDetail.Length; i++)
                {
                    cmd.CommandText = "insert into MaterialDetail values(@MainID,@MaterialID,@BusinessCount,@MemberPrice)";
                    //结束声明和设置SqlCommand命令

                    SqlParameter MainIDAdd = new SqlParameter("@MainID", SqlDbType.VarChar, 20);
                    MainIDAdd.Value = InWareDetail[i].MainID;
                    cmd.Parameters.Add(MainIDAdd);

                    SqlParameter MaterialIDAdd = new SqlParameter("@MaterialID", SqlDbType.VarChar, 20);
                    MaterialIDAdd.Value = InWareDetail[i].Materialid;
                    cmd.Parameters.Add(MaterialIDAdd);

                    SqlParameter BusinessCountAdd = new SqlParameter("@BusinessCount", SqlDbType.Decimal, 8);
                    BusinessCountAdd.Value = InWareDetail[i].BusinessCount;
                    cmd.Parameters.Add(BusinessCountAdd);

                    SqlParameter MemberPriceAdd = new SqlParameter("@MemberPrice", SqlDbType.VarChar, 8);
                    MemberPriceAdd.Value = InWareDetail[i].MemberPrice;
                    cmd.Parameters.Add(MemberPriceAdd);

                    //打开库,执行,关闭库

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                //清除参数
                
                #endregion
             
                #region 库存表
                InWareDAO IWD=new InWareDAOImpl();
                for (int j = 0; j < InWareStore.Length; j++)
                {

                    DataTable dt = new DataTable();
                      dt=IWD.SelectStore(InWareDetail[j].Materialid).Copy();
                    flag = dt.Rows.Count;
                    if (flag != 0)//判断库存中是否有该材料
                    {
                        cmd.CommandText = "update Storage set BusinessCount=BusinessCount+@BusinessCount,MemberPrice=@MemberPrice where MaterialID=@MaterialID";
                        
                        //结束声明和设置SqlCommand命令
                    }
                    else
                    {
                        cmd.CommandText = "insert into Storage values(@MaterialID,@BusinessCount,@MemberPrice)";
                    }
                    SqlParameter MaterialIDStroage = new SqlParameter("@MaterialID", SqlDbType.VarChar, 20);
                    MaterialIDStroage.Value = InWareDetail[j].Materialid;
                    cmd.Parameters.Add(MaterialIDStroage);

                    SqlParameter BusinessCountStroage = new SqlParameter("@BusinessCount", SqlDbType.Decimal, 8);
                    BusinessCountStroage.Value = InWareDetail[j].BusinessCount;
                    cmd.Parameters.Add(BusinessCountStroage);

                    SqlParameter MemberPriceStroage = new SqlParameter("@MemberPrice", SqlDbType.VarChar, 8);
                    MemberPriceStroage.Value = InWareDetail[j].MemberPrice;
                    cmd.Parameters.Add(MemberPriceStroage);

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                    //清除参数
                
                #endregion

                str = "OK";
                tran.Commit();
            }
            catch (Exception ee)
            {
                tran.Rollback();
            }
            finally
            {
                Common.ConnectionDataBase.getConClose();               
            }
            return str;
        }
      
        #region 查找（按材料编号）  返回值：DataTable对象 字段值：材料编号，数量，价格
        public DataTable SelectStore(string MaterialID)
        {DataTable dt = new DataTable();
            
            try
            {
                //声明并设置SqlCommand命令
                SqlDataAdapter da = new SqlDataAdapter();
                DataSet ds = new DataSet();
                string str = "";
                SqlCommand cmd = new SqlCommand();
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DaFanRongMis;Persist Security Info=True;User ID=sa;Pwd=sa");
                cmd.Connection = con;
                cmd.CommandText = "select * from Storage where MaterialID=@MaterialID";
                //结束声明和设置SqlCommand命令

                //参数赋值
                SqlParameter MaterialIDAdd = new SqlParameter("@MaterialID", SqlDbType.VarChar, 20);
                MaterialIDAdd.Value = MaterialID;
                cmd.Parameters.Add(MaterialIDAdd);
                da.SelectCommand = cmd;
                da.Fill(ds);
                dt = ds.Tables[0];
                cmd.Parameters.Clear();
            }
            catch (Exception ee)
            {
                dt = null;
            }
            return dt;
        }
        #endregion

        #region 查找
        public DataTable SelectMaterialMain()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Proc_SelMaxID_MaterialMain";
            cmd.Connection = Common.ConnectionDataBase.getConOpen();
            da.SelectCommand = cmd;
            da.Fill(ds);
            dt = ds.Tables[0];
            return dt;
        }
        #endregion
    }
}