C# DataBase.TransactSql方法代碼示例

 public static bool Delete(string S_Pr)
 {
     bool retvalue = true;
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new CardDAL();
     SqlParameter[] prams =
         {
             db.MakeInParam("@Card",SqlDbType.VarChar,50,S_Pr)
         };
      try
     {
         db.TransactSql(visitor, "Delete", prams);
     }
     catch (Exception err)
     {
         db.Dispose();
         MessageBox.Show(err.Message, "��Ϣ��ʾ", MessageBoxButtons.OK, MessageBoxIcon.Information);
         return false;
     }
     finally
     {
         db.Dispose();
     }
     return retvalue;
 }

 /// <summary>
 /// 插入一條操作記錄，並返回他的編號
 /// </summary>
 /// <param name="Content"></param>
 /// <returns></returns>
 public static string Insert(string Content)
 {
     string ret_Code = MaxID().ToString();
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new WSV.DAL.MyLog.DL_Run();
     SqlParameter[] prams ={
         db.MakeInParam("@ID", SqlDbType.BigInt,8, long.Parse(ret_Code)),
         db.MakeInParam("@Code", SqlDbType.VarChar,50,ret_Code),
         db.MakeInParam("@Work", SqlDbType.VarChar,50, Content),
         db.MakeInParam("@InTime", SqlDbType.DateTime,8, DateTime.Now),
         db.MakeInParam("@OperCode", SqlDbType.VarChar,50, Model.User.LUser.Code),
         db.MakeInParam("@TableNum", SqlDbType.VarChar,50, Model.User.LUser.TableNum)
     };
     try
     {
         db.TransactSql(visitor, "Insert", prams);
     }
     catch (Exception ex)
     { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     return ret_Code;
 }

 public static string Update(Model.Video.D_Video Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Video.VideoVisitor();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
                 db.MakeInParam("@VIDEOname",SqlDbType .Char ,10,Item.VIDEO_name),
                 db.MakeInParam("@VIDEOtable",SqlDbType .VarChar ,50,Item.VIDEO_table ),
                 db.MakeInParam("@VIDEOStandard",SqlDbType.Int ,4,Item.VIDEO_Standard ),
                 db.MakeInParam("@VIDEOSource",SqlDbType.Int,4,Item.VIDEO_Source ),
                 db.MakeInParam("@VIDEOBrightness",SqlDbType.Int,4,Item.VIDEO_Brightness ),
                 db.MakeInParam("@VIDEOContrast",SqlDbType.Int,4,Item.VIDEO_Contrast ),
                 db.MakeInParam("@VIDEOHue",SqlDbType.Int,4,Item.VIDEO_Hue ),
                 db.MakeInParam("@VIDEOSaturation",SqlDbType.Int,4,Item.VIDEO_Saturation ),
                 db.MakeInParam("@VIDEOSuality",SqlDbType.Int,4,Item.VIDEO_Suality)
             };
     try
     {
         bSuc = db.TransactSql(visitor, "Set_Video", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// 添加數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static bool Insert(DC_Power Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new PowerDAL();
     bool Flag = false, ReFlag = false;
     int N = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@Id", SqlDbType.BigInt,0,PublicFun.PublicFun.MaxId("DC_Power")),
         db.MakeInParam("@Code", SqlDbType.VarChar,50, Item.Code),
         db.MakeInParam("@Name", SqlDbType.VarChar,50, Item.Name),
         db.MakeInParam("@ParentCode", SqlDbType.VarChar,50, Item.ParentCode),
         db.MakeInParam("@RuleName", SqlDbType.VarChar,50, Item.RuleName)
     };
     try
     {
         ReFlag = db.TransactSql(visitor, "Insert", prams, out N);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         db.Dispose();
     }
     if (ReFlag && N > 0)
     {
         Flag = true;
     }
     return Flag;
 }

 /// <summary>
 /// 添加數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns>"0101"表示成功，"0"表示失敗</returns>
 public static string Insert(D_CarCodeModel Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new D_CarCodeVisitor();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@ID", SqlDbType.BigInt,50, PublicFun.PublicFun.MaxId("D_CarCode")),
         db.MakeInParam("@Code", SqlDbType.VarChar,50, Item.Code),
         db.MakeInParam("@CarCode", SqlDbType.VarChar,50, Item.CarCode),
         db.MakeInParam("@Tare", SqlDbType.Float,12, Item.Tare),
         db.MakeInParam("@Driver", SqlDbType.VarChar,50, Item.Driver),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Insert", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// �������
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static string Insert(Model.Count.D_StatProjectModel Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Count.D_StatProjectDal();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
          db.MakeInParam("@ID", SqlDbType.VarChar,50, PublicFun.PublicFun.MaxId("D_StatProject")),
         db.MakeInParam("@Code", SqlDbType.VarChar,50,PublicFun.PublicFun.MaxId("D_StatProject")),
         db.MakeInParam("@Name", SqlDbType.VarChar,50, Item.Name),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Insert", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// 修改數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static bool Update(D_Bill Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new BillDAL();
     bool Flag = false, ReFlag = false;
     int N = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@BillName", SqlDbType.VarChar,50, Item.BillName),
         db.MakeInParam("@PrintTitle", SqlDbType.VarChar,50, Item.PrintTitle),
         db.MakeInParam("@Preview", SqlDbType.Bit,50, Item.Preview),
         db.MakeInParam("@Id", SqlDbType.BigInt,0,Item.Id)
     };
     try
     {
         ReFlag = db.TransactSql(visitor, "Update", prams, out N);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         db.Dispose();
     }
     if (ReFlag && N > 0)
     {
         Flag = true;
     }
     return Flag;
 }

 /// <summary>
 /// 修改數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static bool Update(C_Weight Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new WeightDAL();
     bool Flag = false, ReFlag = false;
     int N = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@Save", SqlDbType.Bit,0, Item.Save),
         db.MakeInParam("@Show", SqlDbType.Bit,0, Item.Show),
         db.MakeInParam("@Time", SqlDbType.BigInt,0, Item.Time),
         db.MakeInParam("@Id", SqlDbType.BigInt,0,Item.Id)
     };
     try
     {
         ReFlag = db.TransactSql(visitor, "Update", prams, out N);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         db.Dispose();
     }
     if (ReFlag && N > 0)
     {
         Flag = true;
     }
     return Flag;
 }

 /// <summary>
 /// 添加數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static string AddData(WSV.Model.Weight.WeightModel Weight)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new WSV.DAL.MyLog.OperData();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@ID", SqlDbType.Int,4, BLL.PublicFun.PublicFun.MaxId("D_Saver")),
         db.MakeInParam("@Code", SqlDbType.VarChar,50, Weight.Code),
         db.MakeInParam("@CarCode", SqlDbType.VarChar,50, Weight.Txt.CarCode),
         db.MakeInParam("@Item", SqlDbType.VarChar,50, Weight.Txt.Item),
         db.MakeInParam("@Specs", SqlDbType.VarChar,50, Weight.Txt.Specs),
         db.MakeInParam("@Place", SqlDbType.VarChar,50, Weight.Txt.Place),
         db.MakeInParam("@Send", SqlDbType.VarChar,50, Weight.Txt.Send),
         db.MakeInParam("@Accept", SqlDbType.VarChar,50, Weight.Txt.Accept),
         db.MakeInParam("@Carry", SqlDbType.VarChar,50, Weight.Txt.Carry),
         db.MakeInParam("@Model", SqlDbType.VarChar,50, Weight.Txt.Model),
         db.MakeInParam("@Driver", SqlDbType.VarChar,50, Weight.Txt.Driver),
         db.MakeInParam("@Spare", SqlDbType.VarChar,50, Weight.Txt.Spare),
         db.MakeInParam("@Type", SqlDbType.VarChar,50, Weight.Txt.Type),
         db.MakeInParam("@Card", SqlDbType.VarChar,50, Weight.Txt.Card),
         db.MakeInParam("@Plan_Code", SqlDbType.VarChar,50, Weight.Txt.Plan_Code),
         db.MakeInParam("@G_Table", SqlDbType.VarChar,50, Weight.StateFun.G_Table),
         db.MakeInParam("@T_Table", SqlDbType.VarChar,50, Weight.StateFun.T_Table),
         db.MakeInParam("@G_Oper", SqlDbType.VarChar,50, Weight.StateFun.G_Oper),
         db.MakeInParam("@T_Oper", SqlDbType.VarChar,50, Weight.StateFun.T_Oper),
         db.MakeInParam("@Gross", SqlDbType.Decimal, 10, Weight.Float.Gross),
         db.MakeInParam("@Tare", SqlDbType.Decimal, 10, Weight.Float.Tare),
         db.MakeInParam("@Net", SqlDbType.Decimal, 10, Weight.Float.Net),
         db.MakeInParam("@EndNet", SqlDbType.Decimal, 10, Weight.Float.EndNet),
         db.MakeInParam("@Impurity", SqlDbType.Decimal, 10, Weight.Float.Impurity),
         db.MakeInParam("@ImpurityRate", SqlDbType.Decimal, 10, Weight.Float.ImpurityRate),
         db.MakeInParam("@DeductWei", SqlDbType.Decimal, 10, Weight.Float.DeductWei),
         db.MakeInParam("@DeductRate", SqlDbType.Decimal, 10, Weight.Float.DeductRate),
         db.MakeInParam("@Cube", SqlDbType.Decimal, 10, Weight.Float.Cube),
         db.MakeInParam("@Cubety", SqlDbType.Decimal, 10, Weight.Float.Cubety),
         db.MakeInParam("@InhereWei", SqlDbType.Decimal, 10, Weight.Float.InhereWei),
         db.MakeInParam("@DifferWei", SqlDbType.Decimal, 10, Weight.Float.DifferWei),
         db.MakeInParam("@Price", SqlDbType.Decimal, 10, Weight.Float.Price),
         db.MakeInParam("@Money", SqlDbType.Decimal, 10, Weight.Float.Money),
         db.MakeInParam("@State", SqlDbType.Decimal, 10, Weight.StateFun.State),
         db.MakeInParam("@G_Time", SqlDbType.VarChar, 50, Weight.StateFun.G_Time),
         db.MakeInParam("@T_Time", SqlDbType.VarChar, 50, Weight.StateFun.T_Time),
         db.MakeInParam("@N_Time", SqlDbType.VarChar, 50, Weight.StateFun.N_Time),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "AddData", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// 刪除數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static bool Del(string Id)
 {
     DataBase db = new DataBase();
     bool Flag = false;
     try
     {
         if (db.TransactSql("delete from DC_Power where id in(" + Id + ")"))
         {
             Flag = true;
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
     return Flag;
 }

 /// <summary>
 /// 備份數據庫
 /// </summary>
 /// <param name="bakUpName"></param>
 public static bool BackUp(string bakUpName)
 {
     bool Flag = true;
     DataBase db = new DataBase();
     try
     {
         db.TransactSql("BACKUP DATABASE WSV TO DISK ='" + bakUpName + "'");
     }
     catch (Exception ex)
     {
         Flag = false;
         throw ex;
     }
     finally
     {
         db.Dispose();
     }
     return Flag;
 }

 /// <summary>
 /// 添加數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns></returns>
 public static string AddData(Model.Plan.D_Plan Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Plan.PlanVisitor();
     bool bSuc = false;
     int nEffectRows = 0;
     long id = BLL.PublicFun.PublicFun.MaxId("D_Plan");
     SqlParameter[] prams ={
         db.MakeInParam("@ID", SqlDbType.BigInt,12, BLL.PublicFun.PublicFun.MaxId("D_Plan")),
         db.MakeInParam("@Plan_Code", SqlDbType.VarChar,50, Item.Plan_Code),
         db.MakeInParam("@Plan_CountWay", SqlDbType.VarChar,50, Item.Plan_CountWay),
         db.MakeInParam("@Plan_Item", SqlDbType.VarChar,50, Item.Plan_Item),
         db.MakeInParam("@Plan_Specs", SqlDbType.VarChar,50, Item.Plan_Specs),
         db.MakeInParam("@Plan_Type", SqlDbType.VarChar,50, Item.Plan_Type),
         db.MakeInParam("@Plan_Send", SqlDbType.VarChar,50, Item.Plan_Send),
         db.MakeInParam("@Plan_Accept", SqlDbType.VarChar,50, Item.Plan_Accept),
         db.MakeInParam("@Plan_Num", SqlDbType.Decimal,50, Item.Plan_Num),
         db.MakeInParam("@Plan_SpareNum", SqlDbType.Decimal,50, Item.Plan_SpareNum),
         db.MakeInParam("@Plan_Money", SqlDbType.Decimal,50, Item.Plan_Money),
         db.MakeInParam("@Plan_SpareMoney", SqlDbType.Decimal,50, Item.Plan_SpareMoney),
         db.MakeInParam("@Remark", SqlDbType.VarChar,50, Item.Remark),
         db.MakeInParam("@Begin_Time", SqlDbType.VarChar,50, Item.Begin_Time),
         db.MakeInParam("@End_Time", SqlDbType.VarChar,50, Item.End_Time),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Insert", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// ɾ������
 /// </summary>
 /// <param name="Code">�������</param>
 /// <returns></returns>
 public static string Delete(string ProCode)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Count.D_StatProjectDal();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@Code", SqlDbType.VarChar,50, ProCode),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Delete", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// ɾ������
 /// </summary>
 /// <param name="ID">D_Saver��ID</param>
 /// <returns>"0101"��ʾ�ɹ���"0"��ʾʧ��</returns>
 public static string Delete(long ID)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Search.SearchDal();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@ID", SqlDbType.BigInt,8,ID),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Delete", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }

 /// <summary>
 /// 修改數據
 /// </summary>
 /// <param name="Item">model</param>
 /// <returns>"0101"表示成功，"0"表示失敗</returns>
 public static string Update(Model.Record.D_TypeModel Item)
 {
     DataBase db = new DataBase();
     DataBaseVisitor visitor = new DAL.Record.D_TypeVisitor();
     bool bSuc = false;
     int nEffectRows = 0;
     SqlParameter[] prams ={
         db.MakeInParam("@Name", SqlDbType.VarChar,50, Item.Name),
         db.MakeInParam("@ID", SqlDbType.BigInt,50, Item.ID),
     };
     try
     {
         bSuc = db.TransactSql(visitor, "Update", prams, out nEffectRows);
     }
     catch (Exception ex) { throw new Exception(ex.Message); }
     finally
     {
         db.Dispose();
     }
     if (bSuc && nEffectRows > 0)
         return "0101";
     else
         return "0";
 }