C#与存储过程

时间：2011-03-01 来源：[email protected]

IDataAccess数据访问层接口：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections;
using System.Data;
namespace IDataAccess
{
    public interface ISQLHelper
    {
        IList ExecuteDataSet(DbCommand cmd);
        object ExecuteScalar(DbCommand cmd);
        IDataReader ExecuteReader(DbCommand cmd);
        bool AddData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase;
        bool DeleteData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase;
        bool UpdateData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase;
    }
}

DataAccess继承实现IDataAccess接口：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections;
using System.Data;
using Castle.ActiveRecord.Framework;
using Castle.ActiveRecord;
using Model;
using IDataAccess;
namespace DataAccess
{
    public sealed class SQLHelper : ISQLHelper
    {
        readonly static SQLHelper _sqlhelper = new SQLHelper();
        public static SQLHelper GetSQLHelper
        {
            get
            {
                return _sqlhelper;
            }
        }
        public readonly static Database db = DatabaseFactory.CreateDatabase();
        #region ISQLHelper 成员
        public IList ExecuteDataSet(DbCommand cmd)
        {
            return db.ExecuteDataSet(cmd).Tables[0].DefaultView as IList;
        }
        public object ExecuteScalar(DbCommand cmd){
            return db.ExecuteScalar(cmd);
        }
        public IDataReader ExecuteReader(DbCommand cmd)
        {
            return db.ExecuteReader(cmd);
        }
        public bool AddData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase
        {
            try
            {
                t.Create();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool DeleteData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase
        {
            try
            {
                t.Delete();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool UpdateData<T>(T t) where T : Castle.ActiveRecord.ActiveRecordBase
        {
            try
            {
                t.Update();
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion
    }
}

设计castle在winform中链接代码：

        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);
            Application.ThreadException += new System.Threading.ThreadExceptionEventHandler(Application_ThreadException);
            InPlaceConfigurationSource source = new InPlaceConfigurationSource();
            AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);
            Hashtable properties = new Hashtable();
            properties.Add("hibernate.connection.driver_class", "NHibernate.Driver.SqlClientDriver");
            properties.Add("hibernate.dialect", "NHibernate.Dialect.MsSql2000Dialect");
            properties.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
            properties.Add("hibernate.connection.connection_string", "UID=sa;Password=sasa;Initial Catalog=Project;Data Source=My-ThinkPad");
            source.Add(typeof(ActiveRecordBase), properties);
            ActiveRecordStarter.Initialize(source, typeof(CustomerName), typeof(PaperConnector), typeof(PaperCoreDiameter), typeof(PaperGrade), typeof(PaperInspector), typeof(PaperMachineNo), typeof(PaperQuantitativeRoll), typeof(PaperRollDiameter), typeof(PaperRollInfo), typeof(PaperRollLayer), typeof(PaperRollLength), typeof(PaperRollSpecification), typeof(PaperType), typeof(ProductionTeam), typeof(Certificate),typeof(Printer));
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            bool bCreatedNew;
            Mutex m = new Mutex(false, "Project", out bCreatedNew);
            if (bCreatedNew)
                Application.Run(new MainForm());
        }

业务层中抽象工厂：

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.Common;
namespace BusinessFactory
{
    public interface AbstractFactory
    {
        IList GetAll();
        string Add<T>(T t);
        string DelByIdentity<T>(T t);
        string UpdByIdentity<T>(T t);
        T GetByIdentity<T>(T t);
    }
}

涉及实际中业务层：

 //从存储过程返回数据
 public string GetBarcode(PaperRollInfo LocalEntity)
        {
            DbCommand dcmd = SQLHelper.db.GetStoredProcCommand("proc_GetBarcode");
            SQLHelper.db.AddInParameter(dcmd, "@specification", DbType.Int32, LocalEntity.PRSValue);
            SQLHelper.db.AddInParameter(dcmd, "@papertype", DbType.Int32, LocalEntity.PtValue);
            SQLHelper.db.AddInParameter(dcmd, "@quantitative", DbType.Int32, LocalEntity.PQRValue);
            SQLHelper.db.AddInParameter(dcmd, "@time", DbType.DateTime, LocalEntity.MFDate);
            SQLHelper.db.AddInParameter(dcmd, "@productTeam", DbType.Int32, LocalEntity.PTEValue);
            SQLHelper.db.AddInParameter(dcmd, "@coreDiameter", DbType.Int32, LocalEntity.PCDValue);
            SQLHelper.db.AddInParameter(dcmd, "@rollLayer", DbType.Int32, LocalEntity.PRLEValue);
            SQLHelper.db.AddInParameter(dcmd, "@customerno", DbType.Int32, LocalEntity.CNValue);
            SQLHelper.db.AddOutParameter(dcmd, "@newbarcode", DbType.String, 3);
            SQLHelper.db.AddOutParameter(dcmd, "@barcodestate", DbType.Int32, 10);
            SQLHelper.db.ExecuteNonQuery(dcmd);
            string barcode = dcmd.Parameters["@newbarcode"].Value.ToString();
            string state = dcmd.Parameters["@barcodestate"].Value.ToString();

            return barcode + state.ToString();
        }

//返回一个实体
public PaperRollInfo GetByIdentity(PaperRollInfo LocalEntity)
        {
            PaperRollInfo _PaperRollInfo = null;
            DbCommand dcmd = SQLHelper.db.GetStoredProcCommand("proc_GetEntityByIdentiy");
            SQLHelper.db.AddInParameter(dcmd, "@table", DbType.String, "PaperRollInfo");
            SQLHelper.db.AddInParameter(dcmd, "@identity", DbType.String, "PRIId");
            SQLHelper.db.AddInParameter(dcmd, "@identityvalue", DbType.Int32, LocalEntity.PRIId);
            using (IDataReader reader = SQLHelper.GetSQLHelper.ExecuteReader(dcmd))
            {
                while (reader.Read())
                {
                    _PaperRollInfo = new PaperRollInfo();
                    _PaperRollInfo.PRIId = Convert.ToInt32(reader["PRIId"]);
                    _PaperRollInfo.CNValue = Convert.ToInt32(reader["CNValue"].ToString());
                    _PaperRollInfo.PaperCoatedweight = Convert.ToDecimal(reader["PaperCoatedweight"]);
                    _PaperRollInfo.PaperCoreRe = Convert.ToDecimal(reader["PaperCoreRe"]);
                    _PaperRollInfo.PaperWeigh = Convert.ToDecimal(reader["PaperWeigh"]);
                    _PaperRollInfo.PaperWhereabouts = Convert.ToInt32(reader["PaperWhereabouts"]);
                    _PaperRollInfo.ParperPackage = Convert.ToInt32(reader["ParperPackage"]);
                    _PaperRollInfo.PCDValue = Convert.ToInt32(reader["PCDValue"]);
                    _PaperRollInfo.PCValue = Convert.ToInt32(reader["PCValue"]);
                    _PaperRollInfo.PMDValue = Convert.ToInt32(reader["PMDValue"]);
                    _PaperRollInfo.PIValue = Convert.ToInt32(reader["PIValue"]);
                    _PaperRollInfo.PQRValue = Convert.ToDecimal(reader["PQRValue"]);
                    _PaperRollInfo.PRDValue = Convert.ToInt32(reader["PRDValue"]);
                    _PaperRollInfo.PRLEValue = Convert.ToInt32(reader["PRLEValue"]);
                    _PaperRollInfo.PRLValue = Convert.ToInt32(reader["PRLValue"]);
                    _PaperRollInfo.PRSValue = Convert.ToInt32(reader["PRSValue"]);
                    _PaperRollInfo.PTEValue = Convert.ToInt32(reader["PTEValue"]);
                    _PaperRollInfo.PtValue = Convert.ToInt32(reader["PtValue"]);
                    _PaperRollInfo.Remark = reader["Remark"].ToString();
                    _PaperRollInfo.PgValue = Convert.ToInt32(reader["PgValue"]);
                    _PaperRollInfo.PackingQty = Convert.ToInt32(reader["PackingQty"]);
                    _PaperRollInfo.MFDate = Convert.ToDateTime(reader["MFDate"]);
                    _PaperRollInfo.PrintTime = Convert.ToDateTime(reader["PrintTime"]);
                    _PaperRollInfo.Barcode = Convert.ToString(reader["Barcode"]);
                }
            }
            return _PaperRollInfo;

        }
 public string DelByIdentity(PaperRollInfo LocalEntity)
        {
            bool rs = SQLHelper.GetSQLHelper.DeleteData<PaperRollInfo>(LocalEntity);
            string msg = rs == true ? "ok" : "fail.";
            return msg;
        }
public string UpdByIdentity(PaperRollInfo LocalEntity)
        {
            bool rs = SQLHelper.GetSQLHelper.UpdateData<PaperRollInfo>(LocalEntity);
            string msg = rs == true ? "ok": "fail.";
            return msg;
        }
        public IList Printer()
        {
            DbCommand dcmd = SQLHelper.db.GetStoredProcCommand("proc_print");
            return SQLHelper.GetSQLHelper.ExecuteDataSet(dcmd);
        }

从刚才一个基本三层结构涉及存储过程的话，如果表设计时候比较范式话，那在操作存储过程时候会写很多代码的，所以可以由一个类封装对存储过程来对其操作完成，这样达到减少代码量的效果。

using System;
public class Class1
{
    public Class1()
    {
    }
    public static DataSet CommonProcSel(string procname, params object[] obj)
    {
        using (SqlConnection sqlcon = new SqlConnection("server=SAMSUNG-B76C7F0;database=ASPNETDB;uid=sa;pwd=sasa;"))
        {
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = sqlcon;
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = procname;
            SqlCommandBuilder.DeriveParameters(sqlcmd);
            sqlcmd.Parameters.RemoveAt(0);
            for (int i = 0; i < sqlcmd.Parameters.Count; i++)
            {
                sqlcmd.Parameters[i].Value = obj[i];
            }
            SqlDataAdapter sqldap = new SqlDataAdapter();
            sqldap.SelectCommand = sqlcmd;
            DataSet ds = new DataSet();
            sqldap.Fill(ds);
            return ds;
        }
    }
    public static bool CommonProcTSG(string procname, params object[] obj)
    {
        using (SqlConnection sqlcon = new SqlConnection("server=SAMSUNG-B76C7F0;database=ASPNETDB;uid=sa;pwd=sasa;"))
        {
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = procname;
            sqlcmd.Connection = sqlcon;
            SqlCommandBuilder.DeriveParameters(sqlcmd);
            sqlcmd.Parameters.RemoveAt(0);
            for (int i = 0; i < sqlcmd.Parameters.Count; i++)
            {
                sqlcmd.Parameters[i].Value = obj[i];
            }
            try
            {
                sqlcmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                sqlcon.Close();
            }
        }
    }
}