goodssave.cs.txt

本源码实现了对数据库的最基本的操作

/*******************************************************************************
** File Name（文件名）：　                  GoodsSave.cs
** Storage Path（存储路径）：　             App_Code\
** Author（作者）：                         Whale
** Create Time（建立日期及时间）：          2006-07-20 17:04
** Last Modify Time（最后修改日期及时间）： 2006-09-11 09:13
** File Version（本文件版本号）：X.X.XXX    1.0.01
** File Action describe（文件功能描述）：　 库存类
** File Memo（备注）：
******************************************************************************/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// 库存类
/// </summary>
public class GoodsSave
{
    #region 初始化

    SqlCommand cmd;
    SqlConnection conn;
    protected string strConn = "";
    protected string isDebug = ConfigurationManager.AppSettings["debug"];
    public GoodsSave()
	{
        if (isDebug == "true")
        {
            strConn = ConfigurationManager.AppSettings["strConn_debug"];
        }
        else
        {
            strConn = ConfigurationManager.AppSettings["strConn"];
        }
        conn = new SqlConnection(strConn);
        cmd = new SqlCommand();
        cmd.Connection = conn;
    }

    #endregion

    #region 方法

    /// <summary>
    /// 提取库存配置信息
    /// </summary>
    /// <returns>库存配置信息DataSet</returns>
    public DataSet GetGoodsSaveInfo()
    {
        cmd.CommandText = string.Format("select * from GoodsSaveInfo order by GoodsSaveInfoID desc");
        SqlDataAdapter dad = new SqlDataAdapter(cmd);
        DataSet dts = new DataSet();
        try
        {
            conn.Open();
            dad.Fill(dts);
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return dts;
    }

    /// <summary>
    /// 修改库存配置信息
    /// </summary>
    /// <param name="goodsSaveInfoID">库存配置ID</param>
    /// <param name="allNum">商品满库数量</param>
    /// <param name="alertNum">库存预警数量</param>
    /// <returns>flag标记</returns>
    public int ModifyGoodsSaveInfo(int goodsSaveInfoID, int allNum, int alertNum)
    {
        int flag = 0;
        if (goodsSaveInfoID > 0)
        {
            cmd.CommandText = string.Format("update GoodsSaveInfo set AllNum = {0},AlertNum = {1},AddTime = getdate() where GoodsSaveInfoID = {2}", allNum, alertNum, goodsSaveInfoID);
        }
        else
        {
            cmd.CommandText = string.Format("insert into GoodsSaveInfo (AllNum,AlertNum) values ({0},{1})", allNum, alertNum);
        }
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            flag = 1;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return flag;
    }

    /// <summary>
    /// 提取库存预警商品信息
    /// </summary>
    /// <param name="goodsID">商品信息ID</param>
    /// <returns>预警商品信息DataTable</returns>
    public DataTable GetGoodsSaveAlertByID(int goodsID)
    {
        cmd.CommandText = string.Format("select top 1 a.GoodsID,a.SaveNum,a.SaveAddNum,a.SaveTime,a.SaveAddTime,b.GoodsName from GoodsSave a,GoodsInfo b where a.GoodsID = {0} and a.GoodsID = b.GoodsID ", goodsID);
        SqlDataAdapter dad = new SqlDataAdapter(cmd);
        DataSet dts = new DataSet();
        DataTable dtb = new DataTable();
        try
        {
            conn.Open();
            dad.Fill(dts);
            dtb = dts.Tables[0];
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return dtb;
    }

    /// <summary>
    /// 修改库存预警商品的数量
    /// </summary>
    /// <param name="goodsID">商品信息ID</param>
    /// <param name="saveNum">库存数量</param>
    /// <param name="goodsName">商品名称</param>
    /// <returns>flag标记</returns>
    public int ModifyGoodsSaveAlert(int goodsID, int saveNum, string goodsName)
    {
        int flag = 0;
        cmd.CommandText = string.Format("update GoodsSave set SaveNum = {0},SaveAddNum = {0}-SaveNum,SaveAddTime = getdate(),GoodsName = '{2}' where GoodsID = {1}", saveNum, goodsID, goodsName);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            flag = 1;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return flag;
    }

    /// <summary>
    /// 把预警商品全部满库
    /// </summary>
    /// <param name="allNum">商品满库数量</param>
    /// <param name="alertNum">商品预警数量</param>
    /// <returns>flag标记</returns>
    public int SetGoodsSaveAll(int allNum, int alertNum)
    {
        int flag = 0;
        cmd.CommandText = string.Format("update GoodsSave set SaveNum = {0},SaveAddNum = {0}-SaveNum,SaveAddTime = getdate() where SaveNum < {1}", allNum, alertNum);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            flag = 1;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return flag;
    }

    /// <summary>
    /// 提取库存信息数字
    /// </summary>
    /// <param name="typeID">库存信息种类</param>
    /// <returns>相关库存信息数字</returns>
    public int GetGoodsSaveInfoNum(int typeID)
    {
        int flag = 0;
        if (typeID == 0)
        {
            cmd.CommandText = string.Format("select top 1 AlertNum from GoodsSaveInfo order by GoodsSaveInfoID desc");
        }
        else
        {
            cmd.CommandText = string.Format("select top 1 AllNum from GoodsSaveInfo order by GoodsSaveInfoID desc");
        }
        try
        {
            conn.Open();
            flag = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
        return flag;
    }
    #endregion
}