internal static StoredProcedure GenerateDocumentCode(string transactionType) { //TODO : XML config for SP name StoredProcedure sp = new StoredProcedure("GenerateDocumentCode"); sp.AddParameter("@TransactionType", StoredProcedure.ParameterType.String, 30, transactionType); return sp; }
public StoredProcedure aspnet_Membership_GetNumberOfUsersOnline(string ApplicationName,int MinutesSinceLastInActive,DateTime CurrentTimeUtc){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetNumberOfUsersOnline",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("MinutesSinceLastInActive",MinutesSinceLastInActive,DbType.Int32); sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime); return sp; }
public StoredProcedure DeleteBoardContributor(string Email,int BoardID) { StoredProcedure sp=new StoredProcedure("DeleteBoardContributor",this.Provider); sp.Command.AddParameter("Email",Email,DbType.AnsiString); sp.Command.AddParameter("BoardID",BoardID,DbType.Int32); return sp; }
public StoredProcedure AddBoardContributor(string User,int BoardID) { StoredProcedure sp=new StoredProcedure("AddBoardContributor",this.Provider); sp.Command.AddParameter("User",User,DbType.AnsiString); sp.Command.AddParameter("BoardID",BoardID,DbType.Int32); return sp; }
public StoredProcedure UpdatePoints(int UserID,string PointsName) { StoredProcedure sp=new StoredProcedure("UpdatePoints",this.Provider); sp.Command.AddParameter("UserID",UserID,DbType.Int32); sp.Command.AddParameter("PointsName",PointsName,DbType.AnsiString); return sp; }
public StoredProcedure DeletePin(int BIMID,int UserID) { StoredProcedure sp=new StoredProcedure("DeletePin",this.Provider); sp.Command.AddParameter("BIMID",BIMID,DbType.Int32); sp.Command.AddParameter("UserID",UserID,DbType.Int32); return sp; }
public StoredProcedure UpdatePrize(int UserID,int RWPointDed) { StoredProcedure sp=new StoredProcedure("UpdatePrize",this.Provider); sp.Command.AddParameter("UserID",UserID,DbType.Int32); sp.Command.AddParameter("RWPointDed",RWPointDed,DbType.Int32); return sp; }
private StoredProcedure BaoCaoSoTruc(DateTime? pFromDate, DateTime? pToDate) { var sp = new StoredProcedure("GTVT_BAOCAO_SOTRUC", DataService.GetInstance("ORM"), "dbo"); sp.Command.AddParameter("@FromDate", pFromDate, DbType.DateTime, null, null); sp.Command.AddParameter("@ToDate", pToDate, DbType.DateTime, null, null); return sp; }
private StoredProcedure BaocaoSoghichep(DateTime? pFromDate, DateTime? pToDate) { SubSonic.StoredProcedure sp = new StoredProcedure("spSoGhiChep", DataService.GetInstance("ORM"), "dbo"); sp.Command.AddParameter("@pFromDate",pFromDate,DbType.DateTime,null,null); sp.Command.AddParameter("@pToDate",pToDate,DbType.DateTime,null,null); return sp; }
public StoredProcedure aspnet_Membership_GetAllUsers(string ApplicationName,int PageIndex,int PageSize){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetAllUsers",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32); sp.Command.AddParameter("PageSize",PageSize,DbType.Int32); return sp; }
/// <summary> /// Execute Sql Query with Parameters /// </summary> /// <param name="sData">StoredProcedure Class</param> /// <returns></returns> public static DataSet SqlQueryDataSet(StoredProcedure sData) { DataSet sqlDataView = new DataSet(); try { SqlConnection conn = new SqlConnection(connect()); SqlCommand sqlcomm = new SqlCommand(sData.ProcName, conn); sqlcomm.CommandType = CommandType.StoredProcedure; int i = 0; IEnumerator myEnumerator = sData.GetParams().GetEnumerator(); while (myEnumerator.MoveNext()) { ParamData pData = (ParamData)myEnumerator.Current; sqlcomm.Parameters.Add(pData.pName, pData.pDataType); sqlcomm.Parameters[i].Value = pData.pValue; i = i + 1; } SqlDataAdapter sqlAdapterView = new SqlDataAdapter(sqlcomm); sqlAdapterView.Fill(sqlDataView); return sqlDataView; } catch (Exception ex) { HttpContext.Current.Response.Write(ex); HttpContext.Current.Response.End(); return sqlDataView; } }
public void TestBasicParsing() { string proc1 = "CREATE PROC mysp @arg int=1 AS select 1\n"; var sp = new StoredProcedure(proc1); WVPASSEQ(sp.name, "mysp"); var args = sp.args.ToArray(); WVPASSEQ(args[0].name, "arg"); WVPASSEQ(args[0].type, "int"); WVPASSEQ(args[0].defval, "1"); string proc2 = "CREATE PROC [mysp2]\n@arg1 int output ,\n" + " @arg2 varchar(10) = 'asdf',@arg3 money AS "; sp = new StoredProcedure(proc2); WVPASSEQ(sp.name, "[mysp2]"); args = sp.args.ToArray(); WVPASSEQ(args[0].name, "arg1"); WVPASSEQ(args[0].type, "int"); WVPASSEQ(args[0].defval, ""); WVPASSEQ(args[1].name, "arg2"); WVPASSEQ(args[1].type, "varchar(10)"); WVPASSEQ(args[1].defval, "'asdf'"); WVPASSEQ(args[2].name, "arg3"); WVPASSEQ(args[2].type, "money"); WVPASSEQ(args[2].defval, ""); }
public static DataTable AllByParent(int? p_ParentCategoryID, bool p_ShowDisable) { StoredProcedure sp = new StoredProcedure("p_Categories_sl_ByParent"); sp["@ParentCategoryID"].Value = p_ParentCategoryID==null?DBNull.Value:(object)p_ParentCategoryID; sp["@ShowDisable"].Value = p_ShowDisable; return sp.ExecuteDataTable(); }
public StoredProcedure GetUser(string Email,string Password) { StoredProcedure sp=new StoredProcedure("GetUser",this.Provider); sp.Command.AddParameter("Email",Email,DbType.AnsiString); sp.Command.AddParameter("Password",Password,DbType.AnsiString); return sp; }
public static DataTable AllCategoriesDrugsByParent(int? p_ParentCategoryID, bool p_isShowAll) { StoredProcedure sp = new StoredProcedure("p_CategoriesDrugs_sl_ByParent"); sp["@CategoryID"].Value = p_ParentCategoryID == null ? DBNull.Value : (object)p_ParentCategoryID; sp["@ShowAll"].Value = p_isShowAll; return sp.ExecuteDataTable(); }
public static DataTable GetCategory(int p_CategoryID) { StoredProcedure sp = new StoredProcedure("p_Categories_s"); sp["@CategoryID"].Value = p_CategoryID; return sp.ExecuteDataTable(); }
public static DataRow GetBanner(int p_BannerID) { StoredProcedure sp = new StoredProcedure("p_Banners_s"); sp["@BannerID"].Value = p_BannerID; DataTable l_dtRes = sp.ExecuteDataTable(); if (l_dtRes.Rows.Count<=0) return null; return l_dtRes.Rows[0]; }
public ProcessMessage(DbService iDbService, QueueService iQueueService) { _documentClient = iDbService.GetDocumentClient(); _client = iDbService.GetFirebaseClient(); _documentCollection = iDbService.GetDc("LMSCollection", "LMSRegistry"); _sp = iDbService.GetSp(_documentCollection, "Post"); _queue = iQueueService.GetQueue("queue"); }
public StoredProcedure aspnet_Membership_ChangePasswordQuestionAndAnswer(string ApplicationName,string UserName,string NewPasswordQuestion,string NewPasswordAnswer){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_ChangePasswordQuestionAndAnswer",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("UserName",UserName,DbType.String); sp.Command.AddParameter("NewPasswordQuestion",NewPasswordQuestion,DbType.String); sp.Command.AddParameter("NewPasswordAnswer",NewPasswordAnswer,DbType.String); return sp; }
public StoredProcedure aspnet_Membership_GetPasswordWithFormat(string ApplicationName,string UserName,bool UpdateLastLoginActivityDate,DateTime CurrentTimeUtc){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetPasswordWithFormat",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("UserName",UserName,DbType.String); sp.Command.AddParameter("UpdateLastLoginActivityDate",UpdateLastLoginActivityDate,DbType.Boolean); sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime); return sp; }
public StoredProcedure aspnet_Membership_GetUserByName(string ApplicationName,string UserName,DateTime CurrentTimeUtc,bool UpdateLastActivity){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetUserByName",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("UserName",UserName,DbType.String); sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime); sp.Command.AddParameter("UpdateLastActivity",UpdateLastActivity,DbType.Boolean); return sp; }
public StoredProcedure aspnet_Membership_FindUsersByName(string ApplicationName,string UserNameToMatch,int PageIndex,int PageSize){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_FindUsersByName",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("UserNameToMatch",UserNameToMatch,DbType.String); sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32); sp.Command.AddParameter("PageSize",PageSize,DbType.Int32); return sp; }
private StoredProcedure BaocaoDSBNtheoTestType(DateTime? pFromDate, DateTime? pToDate, string testType) { SubSonic.StoredProcedure sp = new StoredProcedure("spBaoCao_DSBN_TestType", DataService.GetInstance("ORM"),"dbo"); sp.Command.AddParameter("@pFromDate", pFromDate,DbType.DateTime,null,null); sp.Command.AddParameter("@pToDate", pToDate,DbType.DateTime,null,null); sp.Command.AddParameter("@testType",testType,DbType.String,null,null); return sp; }
public static StoredProcedure P_Branch_GetMaxBranchCode(int Depth,int ParentId) { StoredProcedure sp=new StoredProcedure("P_Branch_GetMaxBranchCode"); sp.Command.AddParameter("Depth",Depth,DbType.Int32); sp.Command.AddParameter("ParentId",ParentId,DbType.Int32); return sp; }
public void SetParameter(IDataReader rdr, StoredProcedure.Parameter par) { par.SqlType = rdr[SqlSchemaVariable.DATA_TYPE].ToString(); par.DBType = GetDbType(par.SqlType); string sMode = rdr[SqlSchemaVariable.MODE].ToString(); if (sMode == SqlSchemaVariable.MODE_INOUT) par.Mode = ParameterDirection.InputOutput; par.Name = rdr[SqlSchemaVariable.NAME].ToString(); }
public ScriptableObject(IScriptingSource scriptingSource, ScriptSchemaObjectBase schemaObject) { _scriptingSource = scriptingSource; _schemaObject = schemaObject; _table = _schemaObject as Table; _view = _schemaObject as View; _sproc = _schemaObject as StoredProcedure; _udf = _schemaObject as UserDefinedFunction; }
/// <summary> /// Creates an object wrapper for the DeleteSeries Procedure /// </summary> public static StoredProcedure DeleteSeries(Guid? StudyStorageGUID, string SeriesInstanceUID) { var sp = new StoredProcedure("DeleteSeries", DataService.GetInstance("ORM"), "dbo"); sp.Command.AddParameter("@StudyStorageGUID", StudyStorageGUID, DbType.Guid, null, null); sp.Command.AddParameter("@SeriesInstanceUID", SeriesInstanceUID, DbType.AnsiString, null, null); return sp; }
public static DataRow GetCategoryByCode(string p_CategoryCode) { StoredProcedure sp = new StoredProcedure("p_Categories_s_byCategoryCode"); sp["@CategoryCode"].Value = p_CategoryCode; DataTable l_dtRes = sp.ExecuteDataTable(); if (l_dtRes.Rows.Count>0) { return l_dtRes.Rows[0]; }else {return null;} }
public StoredProcedure SaveTransaction(long? TransactionID,string Description,decimal? Amount,DateTime? Date,string Tags) { StoredProcedure sp=new StoredProcedure("SaveTransaction",this.Provider); sp.Command.AddParameter("TransactionID",TransactionID,DbType.Int64); sp.Command.AddParameter("Description",Description,DbType.AnsiString); sp.Command.AddParameter("Amount",Amount,DbType.Currency); sp.Command.AddParameter("Date",Date,DbType.DateTime); sp.Command.AddParameter("Tags",Tags,DbType.AnsiString); return sp; }
public StoredProcedure aspnet_Membership_GetPassword(string ApplicationName,string UserName,int MaxInvalidPasswordAttempts,int PasswordAttemptWindow,DateTime CurrentTimeUtc,string PasswordAnswer){ StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetPassword",this.Provider); sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String); sp.Command.AddParameter("UserName",UserName,DbType.String); sp.Command.AddParameter("MaxInvalidPasswordAttempts",MaxInvalidPasswordAttempts,DbType.Int32); sp.Command.AddParameter("PasswordAttemptWindow",PasswordAttemptWindow,DbType.Int32); sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime); sp.Command.AddParameter("PasswordAnswer",PasswordAnswer,DbType.String); return sp; }
internal static bool HasResult(this StoredProcedure storedProcedure) { return(storedProcedure.Output?.Any() ?? false); }
private string GenerareScriptAsExecute(Server server, UrnCollection urns, ScriptingOptions options) { string script = string.Empty; ScriptingObject scriptingObject = this.Parameters.ScriptingObjects[0]; Urn urn = urns[0]; // get the object StoredProcedure sp = server.GetSmoObject(urn) as StoredProcedure; Database parentObject = server.GetSmoObject(urn.Parent) as Database; StringBuilder executeStatement = new StringBuilder(); // list of DECLARE <variable> <type> StringBuilder declares = new StringBuilder(); // Parameters to be passed StringBuilder parameterList = new StringBuilder(); if (sp == null || parentObject == null) { throw new InvalidOperationException(SR.ScriptingExecuteNotSupportedError); } WriteUseDatabase(parentObject, executeStatement, options); // character string to put in front of each parameter. First one is just carriage return // the rest will have a "," in front as well. string newLine = Environment.NewLine; string paramListPreChar = $"{newLine} "; for (int i = 0; i < sp.Parameters.Count; i++) { StoredProcedureParameter spp = sp.Parameters[i]; declares.AppendFormat("DECLARE {0} {1}{2}" , QuoteObjectName(spp.Name) , GetDatatype(spp.DataType, options) , newLine); parameterList.AppendFormat("{0}{1}" , paramListPreChar , QuoteObjectName(spp.Name)); // if this is the first time through change the prefix to include a "," if (i == 0) { paramListPreChar = $"{newLine} ,"; } // mark any output parameters as such. if (spp.IsOutputParameter) { parameterList.Append(" OUTPUT"); } } // build the execute statement if (sp.ImplementationType == ImplementationType.TransactSql) { executeStatement.Append("EXECUTE @RC = "); } else { executeStatement.Append("EXECUTE "); } // get the object name executeStatement.Append(GenerateSchemaQualifiedName(sp.Schema, sp.Name, options.SchemaQualify)); string formatString = sp.ImplementationType == ImplementationType.TransactSql ? "DECLARE @RC int{5}{0}{5}{1}{5}{5}{2} {3}{5}{4}" : "{0}{5}{1}{5}{5}{2} {3}{5}{4}"; script = string.Format(CultureInfo.InvariantCulture, formatString, declares, SR.StoredProcedureScriptParameterComment, executeStatement, parameterList, CommonConstants.DefaultBatchSeperator, newLine); return(script); }
/// <summary> /// /// </summary> /// <param name="currentTbl">Data table that will be visualized in the grid</param> /// <param name="spPram">list of kvp that hold the parameter checks</param> /// <param name="spTxt">list of kvp that hold the text checks</param> /// <param name="procedure">StoredProcedure Object</param> /// <param name="logic">enum for what logic we need OR or AND</param> private void AddProcToTable(DataTable currentTbl, List <KeyValuePair <string, bool> > spPram, List <KeyValuePair <string, bool> > spTxt , StoredProcedure procedure, SearchLogic logic) { bool pramChecks = false; bool spChecks = false; bool currentCheck = false; switch (logic) { case SearchLogic.AND: foreach (KeyValuePair <string, bool> chk in spPram) { currentCheck = chk.Value; if (!currentCheck) { pramChecks = false; break; } else { pramChecks = true; } } currentCheck = false; spChecks = currentCheck; foreach (KeyValuePair <string, bool> chk in spTxt) { currentCheck = chk.Value; if (!currentCheck) { spChecks = false; break; } else { spChecks = true; } } if (spChecks && pramChecks) { DataRow row = currentTbl.NewRow(); string paramsstring = string.Join("\r\n", spPram).Replace(',', ' ').Replace('[', ' ').Replace(']', ' '); var spparams = from StoredProcedureParameter prm in procedure.Parameters select prm.Name + " " + prm.DataType.SqlDataType + " " + prm.DataType.MaximumLength + " " + prm.DefaultValue + "\r\n"; string paramsData = string.Join("\r\n", spparams); row["ProcedureName"] = procedure.Name; row["ProcedureChecks"] = paramsstring; row["ProcedureParameters"] = paramsData; row["ProcedureText"] = procedure.TextBody; currentTbl.Rows.Add(row); } break; case SearchLogic.OR: foreach (KeyValuePair <string, bool> chk in spPram) { currentCheck = chk.Value; if (!currentCheck) { pramChecks = false; } else { pramChecks = true; break; } } currentCheck = false; spChecks = currentCheck; foreach (KeyValuePair <string, bool> chk in spTxt) { currentCheck = chk.Value; if (!currentCheck) { spChecks = false; } else { spChecks = true; break; } } if (spChecks || pramChecks) { DataRow row = currentTbl.NewRow(); string paramsstring = string.Join("\r\n", spPram).Replace(',', ' ').Replace('[', ' ').Replace(']', ' '); var spparams = from StoredProcedureParameter prm in procedure.Parameters select prm.Name + " " + prm.DataType.SqlDataType + " " + prm.DataType.MaximumLength + " " + prm.DefaultValue + "\r\n"; string paramsData = string.Join("\r\n", spparams); row["ProcedureName"] = procedure.Name; row["ProcedureChecks"] = paramsstring; row["ProcedureParameters"] = paramsData; row["ProcedureText"] = procedure.TextBody; currentTbl.Rows.Add(row); } break; } }
public static DataSyncAction DropStoredProcedure(DataContext sourceDataContext, DataContext targetDataContext, StoredProcedure storedProcedure) { var builder = new StringBuilder(); builder.Append(StartThe) .Append(storedProcedure.Description) .Append(Space) .Append(storedProcedure.Namespace) .Append(EndDropped); Func <string> script = () => ContextScriptFactory.DropStoredProcedure(sourceDataContext, targetDataContext, storedProcedure); if (string.IsNullOrEmpty(script.Invoke())) { return(null); } return(new DataSyncAction( storedProcedure , storedProcedure.Namespace , builder.ToString() , DataSyncOperationType.DropStoredProcedure , script )); }
public ActionResult XacnhanPhieuTrathuocNhacungcap(TPhieuNhapxuatthuoc objPhieuNhap) { HisDuocProperties objHisDuocProperties = PropertyLib._HisDuocProperties; string errorMessage = ""; try { using (var Scope = new TransactionScope()) { using (var dbScope = new SharedDbConnectionScope()) { SqlQuery sqlQuery = new Select().From(TPhieuNhapxuatthuocChitiet.Schema) .Where(TPhieuNhapxuatthuocChitiet.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu); TPhieuNhapxuatthuocChitietCollection objPhieuNhapCtCollection = sqlQuery.ExecuteAsCollection <TPhieuNhapxuatthuocChitietCollection>(); foreach (TPhieuNhapxuatthuocChitiet objPhieuNhapCt in objPhieuNhapCtCollection) { //Insert dòng hủy vào TBiendongThuoc TBiendongThuoc objXuatNhap = new TBiendongThuoc(); objXuatNhap.IdPhieu = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieu); objXuatNhap.IdPhieuChitiet = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieuchitiet); objXuatNhap.MaPhieu = Utility.sDbnull(objPhieuNhap.MaPhieu); objXuatNhap.DonGia = Utility.DecimaltoDbnull(objPhieuNhapCt.DonGia); objXuatNhap.NgayHoadon = objPhieuNhap.NgayHoadon; objXuatNhap.GiaBan = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaBan); objXuatNhap.GiaNhap = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaNhap); objXuatNhap.SoDky = objPhieuNhapCt.SoDky; objXuatNhap.SoQdinhthau = objPhieuNhapCt.SoQdinhthau; objXuatNhap.SoLo = objPhieuNhapCt.SoLo; objXuatNhap.IdThuockho = Utility.Int32Dbnull(objPhieuNhapCt.IdThuockho); objXuatNhap.KieuThuocvattu = Utility.sDbnull(objPhieuNhapCt.KieuThuocvattu); objXuatNhap.SoChungtuKemtheo = ""; objXuatNhap.Noitru = 0; objXuatNhap.QuayThuoc = 0; objXuatNhap.GiaBhyt = 0; objXuatNhap.GiaBhytCu = 0; objXuatNhap.GiaPhuthuDungtuyen = 0; objXuatNhap.GiaPhuthuTraituyen = 0; objXuatNhap.DuTru = 0; objXuatNhap.SoHoadon = Utility.sDbnull(objPhieuNhap.SoHoadon); objXuatNhap.PhuThu = 0; objXuatNhap.SoLuong = Utility.Int32Dbnull(objPhieuNhapCt.SoLuong); objXuatNhap.NgayTao = globalVariables.SysDate; objXuatNhap.NguoiTao = globalVariables.UserName; objXuatNhap.ThanhTien = Utility.DecimaltoDbnull(objPhieuNhapCt.ThanhTien); objXuatNhap.IdThuoc = Utility.Int32Dbnull(objPhieuNhapCt.IdThuoc); objXuatNhap.Vat = Utility.Int32Dbnull(objPhieuNhap.Vat); objXuatNhap.IdNhanvien = Utility.Int16Dbnull(objPhieuNhap.IdNhanvien); objXuatNhap.IdKho = Utility.Int16Dbnull(objPhieuNhap.IdKhoxuat); objXuatNhap.NgayHethan = objPhieuNhapCt.NgayHethan.Date; objXuatNhap.MaNhacungcap = objPhieuNhap.MaNhacungcap; objXuatNhap.MaLoaiphieu = objPhieuNhap.LoaiPhieu; objXuatNhap.TenLoaiphieu = objPhieuNhap.TenLoaiphieu; objXuatNhap.NgayBiendong = objPhieuNhap.NgayHoadon; objXuatNhap.IsNew = true; objXuatNhap.Save(); StoredProcedure sp = SPs.ThuocXuatkho(objPhieuNhap.IdKhoxuat, objPhieuNhapCt.IdThuoc, objPhieuNhapCt.NgayHethan, objPhieuNhapCt.GiaNhap, objPhieuNhapCt.GiaBan, Utility.DecimaltoDbnull(objPhieuNhapCt.Vat), Utility.Int32Dbnull(objXuatNhap.SoLuong), objPhieuNhapCt.IdChuyen, objPhieuNhapCt.MaNhacungcap, objPhieuNhapCt.SoLo, objHisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errorMessage); sp.Execute(); } new Update(TPhieuNhapxuatthuoc.Schema) .Set(TPhieuNhapxuatthuoc.Columns.IdNhanvien).EqualTo(globalVariables.gv_intIDNhanvien) .Set(TPhieuNhapxuatthuoc.Columns.NguoiXacnhan).EqualTo(globalVariables.UserName) .Set(TPhieuNhapxuatthuoc.Columns.NgayXacnhan).EqualTo(globalVariables.SysDate) .Set(TPhieuNhapxuatthuoc.Columns.TrangThai).EqualTo(1) .Where(TPhieuNhapxuatthuoc.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu).Execute(); } Scope.Complete(); return(ActionResult.Success); } } catch (Exception exception) { log.Error("Loi ban ra tu sp :{0}", errorMessage); log.Error("Loi trong qua trinh xac nhan don thuoc :{0}", exception); return(ActionResult.Error); } }
public StoredProcedureConfiguration GetStoredProcedureConfiguration(StoredProcedure storedProcedure) { return(this.StoredProcedures.FirstOrDefault(x => x.Name == storedProcedure.Name)); }
public ActionResult XacNhanPhieuHuy_thanhly_thuoc(TPhieuNhapxuatthuoc objPhieuNhap, DateTime ngayxacnhan, ref string errMsg) { HisDuocProperties objHisDuocProperties = PropertyLib._HisDuocProperties; string errorMessage = ""; try { using (var Scope = new TransactionScope()) { using (var dbScope = new SharedDbConnectionScope()) { SqlQuery sqlQuery = new Select().From(TPhieuNhapxuatthuocChitiet.Schema) .Where(TPhieuNhapxuatthuocChitiet.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu); TPhieuNhapxuatthuocChitietCollection objPhieuNhapCtCollection = sqlQuery.ExecuteAsCollection <TPhieuNhapxuatthuocChitietCollection>(); objPhieuNhap.NgayXacnhan = ngayxacnhan; foreach (TPhieuNhapxuatthuocChitiet objPhieuNhapCt in objPhieuNhapCtCollection) { //Kiểm tra đề phòng Kho A-->Xuất kho B. Kho B xác nhận-->Xuất kho C. Kho B hủy xác nhận. Kho C xác nhận dẫn tới việc kho B chưa có thuốc để trừ kho ActionResult _Kiemtrathuocxacnhan = Kiemtrathuocxacnhan(objPhieuNhap, objPhieuNhapCt, ref errMsg); if (_Kiemtrathuocxacnhan != ActionResult.Success) { return(_Kiemtrathuocxacnhan); } long idthuockho = -1; StoredProcedure sp = SPs.ThuocXuatkho(objPhieuNhap.IdKhoxuat, objPhieuNhapCt.IdThuoc, objPhieuNhapCt.NgayHethan, objPhieuNhapCt.GiaNhap, objPhieuNhapCt.GiaBan, Utility.DecimaltoDbnull(objPhieuNhapCt.Vat), Utility.Int32Dbnull(objPhieuNhapCt.SoLuong), objPhieuNhapCt.IdChuyen, objPhieuNhapCt.MaNhacungcap, objPhieuNhapCt.SoLo, objHisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errorMessage); sp.Execute(); //Insert dòng kho xuất TBiendongThuoc objXuatNhap = new TBiendongThuoc(); objXuatNhap.IdPhieu = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieu); objXuatNhap.IdPhieuChitiet = Utility.Int32Dbnull(objPhieuNhapCt.IdPhieuchitiet); objXuatNhap.MaPhieu = Utility.sDbnull(objPhieuNhap.MaPhieu); objXuatNhap.DonGia = Utility.DecimaltoDbnull(objPhieuNhapCt.DonGia); objXuatNhap.GiaBan = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaBan); objXuatNhap.GiaNhap = Utility.DecimaltoDbnull(objPhieuNhapCt.GiaNhap); objXuatNhap.SoHoadon = Utility.sDbnull(objPhieuNhap.SoHoadon); objXuatNhap.GiaBhyt = objPhieuNhapCt.GiaBhyt; objXuatNhap.GiaBhytCu = objPhieuNhapCt.GiaBhytCu; objXuatNhap.GiaPhuthuDungtuyen = objPhieuNhapCt.GiaPhuthuDungtuyen; objXuatNhap.GiaPhuthuTraituyen = objPhieuNhapCt.GiaPhuthuTraituyen; objXuatNhap.NgayNhap = objPhieuNhapCt.NgayNhap; objXuatNhap.Noitru = 0; objXuatNhap.QuayThuoc = 0; objXuatNhap.PhuThu = 0; objXuatNhap.DuTru = objPhieuNhap.DuTru; objXuatNhap.SoLuong = Utility.Int32Dbnull(objPhieuNhapCt.SoLuong); objXuatNhap.NgayTao = globalVariables.SysDate; objXuatNhap.NguoiTao = globalVariables.UserName; objXuatNhap.IdThuockho = Utility.Int32Dbnull(objPhieuNhapCt.IdThuockho); objXuatNhap.IdChuyen = Utility.Int32Dbnull(objPhieuNhapCt.IdChuyen); objXuatNhap.ThanhTien = Utility.DecimaltoDbnull(objPhieuNhapCt.ThanhTien); objXuatNhap.IdThuoc = Utility.Int32Dbnull(objPhieuNhapCt.IdThuoc); objXuatNhap.Vat = Utility.Int32Dbnull(objPhieuNhap.Vat); objXuatNhap.IdNhanvien = Utility.Int16Dbnull(objPhieuNhap.IdNhanvien); objXuatNhap.IdKho = Utility.Int16Dbnull(objPhieuNhap.IdKhoxuat); objXuatNhap.NgayHethan = objPhieuNhapCt.NgayHethan.Date; objXuatNhap.MaNhacungcap = objPhieuNhapCt.MaNhacungcap; objXuatNhap.SoChungtuKemtheo = objPhieuNhap.SoChungtuKemtheo; objXuatNhap.SoDky = objPhieuNhapCt.SoDky; objXuatNhap.SoQdinhthau = objPhieuNhapCt.SoQdinhthau; objXuatNhap.SoLo = objPhieuNhapCt.SoLo; objXuatNhap.MaLoaiphieu = objPhieuNhap.LoaiPhieu; objXuatNhap.TenLoaiphieu = objPhieuNhap.TenLoaiphieu; objXuatNhap.NgayBiendong = objPhieuNhap.NgayXacnhan; objXuatNhap.NgayHoadon = objPhieuNhap.NgayHoadon; objXuatNhap.KieuThuocvattu = objPhieuNhapCt.KieuThuocvattu; objXuatNhap.IsNew = true; objXuatNhap.Save(); } new Update(TPhieuNhapxuatthuoc.Schema) .Set(TPhieuNhapxuatthuoc.Columns.NguoiSua).EqualTo(globalVariables.UserName) .Set(TPhieuNhapxuatthuoc.Columns.NgaySua).EqualTo(globalVariables.SysDate) .Set(TPhieuNhapxuatthuoc.Columns.NguoiXacnhan).EqualTo(globalVariables.UserName) .Set(TPhieuNhapxuatthuoc.Columns.NgayXacnhan).EqualTo(ngayxacnhan) .Set(TPhieuNhapxuatthuoc.Columns.TrangThai).EqualTo(1) .Where(TPhieuNhapxuatthuoc.Columns.IdPhieu).IsEqualTo(objPhieuNhap.IdPhieu) .And(TPhieuNhapxuatthuoc.LoaiPhieuColumn).IsEqualTo(objPhieuNhap.LoaiPhieu).Execute(); } Scope.Complete(); return(ActionResult.Success); } } catch (Exception ex) { Utility.CatchException("Lỗi khi xác nhận phiếu ", ex); return(ActionResult.Error); } }
/// <summary> /// Replace the specified stored procedure. /// </summary> /// <param name="client">document client.</param> /// <param name="storedProcedureUri">the self-link for the attachment.</param> /// <param name="storedProcedure">the updated stored procedure.</param> /// <param name="options">the request options for the request.</param> /// <returns>The task object representing the service response for the asynchronous operation.</returns> public static Task <ResourceResponse <StoredProcedure> > ReplaceStoredProcedureExAsync(this DocumentClient client, StoredProcedure storedProcedure, RequestOptions options = null) { SwapLinkIfNeeded(client, storedProcedure); return(client.ReplaceStoredProcedureAsync(storedProcedure, options)); }
internal static IEnumerable <StoredProcedureInputModel> GetOutputs(this StoredProcedure storedProcedure) { return(storedProcedure.Input?.Where(i => i.IsOutput)); }
public static string GenerateStoredProcedureCode(StoredProcedure pProc, string columnPrefix, bool pIncludeSchema) { string newLine = Environment.NewLine; string str = "\t"; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("using System;").Append(newLine); stringBuilder.Append("using System.Data;"); stringBuilder.Append("using System.Data.SqlClient;"); stringBuilder.Append("namespace Tables.").Append(pProc.Name).Append(" {").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append("public sealed class Proc : ").Append(typeof(StoredProcBase).ToString()).Append(" {").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append("public static readonly Proc Instance = new Proc();").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append("public Proc() : base(DATABASE, \"").Append(!pIncludeSchema || string.IsNullOrEmpty(pProc.Schema) ? string.Empty : pProc.Schema + ".").Append(pProc.Name).Append("\", typeof(Row)) {").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append(str).Append("//AddColumns("); stringBuilder.Append(");").Append(newLine); stringBuilder.Append(str).Append(str).Append("}").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append("public Sql.IResult Execute("); int index1 = 0; while (index1 < pProc.Parameters.Count) { SpParameter spParameter = pProc.Parameters[index1]; if (index1 > 0) { stringBuilder.Append(", "); } if (spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue) { stringBuilder.Append("out "); } stringBuilder.Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(" ").Append(spParameter.Name); checked { ++index1; } } if (pProc.Parameters.Count > 0) { stringBuilder.Append(", "); } stringBuilder.Append("Sql.Transaction transaction){").Append(newLine).Append(newLine); int index2 = 0; while (index2 < pProc.Parameters.Count) { SpParameter spParameter = pProc.Parameters[index2]; stringBuilder.Append(str).Append(str).Append(str).Append("SqlParameter p").Append(index2.ToString()).Append(" = new SqlParameter(\"").Append(spParameter.Name).Append("\", SqlDbType.").Append(DbColumnFactory.GetSqlType(spParameter.ParamType).ToString()).Append(");").Append(newLine); stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Direction = ParameterDirection.").Append(spParameter.Direction.ToString()).Append(";").Append(newLine); if (spParameter.Direction == ParameterDirection.Input || spParameter.Direction == ParameterDirection.InputOutput) { stringBuilder.Append(str).Append(str).Append(str).Append("p").Append(index2.ToString()).Append(".Value = ").Append(spParameter.Name).Append(";").Append(newLine); } stringBuilder.Append(newLine); checked { ++index2; } } stringBuilder.Append(str).Append(str).Append(str).Append("Sql.IResult result = ExecuteProcedure(transaction"); int num = 0; while (num < pProc.Parameters.Count) { stringBuilder.Append(", p").Append(num.ToString()); checked { ++num; } } stringBuilder.Append(");").Append(newLine).Append(newLine); int index3 = 0; while (index3 < pProc.Parameters.Count) { SpParameter spParameter = pProc.Parameters[index3]; if (spParameter.Direction == ParameterDirection.InputOutput || spParameter.Direction == ParameterDirection.Output || spParameter.Direction == ParameterDirection.ReturnValue) { stringBuilder.Append(str).Append(str).Append(str).Append(spParameter.Name).Append(" = (").Append(DbColumnFactory.GetReturnType(spParameter.ParamType, false)).Append(")").Append("p").Append(index3.ToString()).Append(".Value;").Append(newLine); } checked { ++index3; } } stringBuilder.Append(str).Append(str).Append(str).Append("return result;").Append(newLine); stringBuilder.Append(str).Append(str).Append("}").Append(newLine).Append(newLine); stringBuilder.Append(str).Append(str).Append("public Row this[int pIndex, Sql.IResult pResult]{").Append(newLine); stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Row)pResult.GetRow(this, pIndex); }").Append(newLine); stringBuilder.Append(str).Append(str).Append("}").Append(newLine); stringBuilder.Append(str).Append("}").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append("public sealed class Row : ").Append(typeof(Record).ToString()).Append(" {").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append("private new Proc Tbl {").Append(newLine); stringBuilder.Append(str).Append(str).Append(str).Append("get { return (Proc)base.Tbl; }").Append(newLine); stringBuilder.Append(str).Append(str).Append("}").Append(newLine); stringBuilder.Append(newLine); stringBuilder.Append(str).Append(str).Append("public Row() : base(Proc.Instance) {").Append(newLine); stringBuilder.Append(str).Append(str).Append("}").Append(newLine); stringBuilder.Append(str).Append("}").Append(newLine); stringBuilder.Append("}"); return(stringBuilder.ToString()); }
public abstract string DropStoredProcedure(DataContext sourceDataContext, DataContext targetDataContext, StoredProcedure storedProcedure);
public static string OutPut_ID(StoredProcedure sp) { return(sp.OutputValues[0].ToString()); }
/// <summary> /// Lista de métodos a generar. /// </summary> /// <date>2007-5-25T00:00:00</date> /// <author>Marcelo Oviedo</author> //public List<MethodInfo> Methods //{ // get { return _Methods; } // set { _Methods = value; } //} #endregion #region [Constructors] /// <summary> /// Constructor por defecto. /// </summary> /// <author>Marcelo Oviedo</author> //public EntityInfo(Table pTable) //{ // _Table = pTable; // _Methods = new List<MethodInfo>(); //} /// <summary> /// /// </summary> /// <param name="pStoredProcedure"></param> /// <author>Marcelo Oviedo</author> public EntityInfo(StoredProcedure pStoredProcedure) { _StoredProcedure = pStoredProcedure; //_Methods = new List<MethodInfo>(); }
internal static async Task PartitionedCollectionSmokeTest(DocumentClient client, bool sharedOffer = false, bool sharedThroughputCollections = false, int numberOfCollections = 1) { if (!sharedOffer && sharedThroughputCollections) { throw new ArgumentException("Shared throughput collections are not supported without shared offer"); } string uniqDatabaseName = string.Format("SmokeTest_{0}", Guid.NewGuid().ToString("N")); RequestOptions options = new RequestOptions { OfferThroughput = 50000 }; Database database = sharedOffer ? await client.CreateDatabaseAsync(new Database { Id = uniqDatabaseName }, options) : await client.CreateDatabaseAsync(new Database { Id = uniqDatabaseName }); Assert.AreEqual(database.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName)); Database readbackdatabase = await client.ReadDatabaseAsync(database.SelfLink); List <dynamic> results = await ClientTestsUtils.SqlQueryDatabases(client, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDatabaseName), 10); Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(database.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.IsTrue((await ClientTestsUtils.ReadFeedDatabases(client)).Any((db) => db.Id == uniqDatabaseName)); results = await ClientTestsUtils.SqlQueryDatabases(client, string.Format(@"select r._rid, r.id from root r where r.id = ""{0}""", uniqDatabaseName), 10); Assert.AreEqual(1, results.Count, "Should have queried and found 1 database"); Assert.AreEqual(database.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(database.ResourceId, (await client.ReadDatabaseAsync(database.SelfLink)).Resource.ResourceId); Assert.AreEqual(((Database)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName)); ArrayList testCollections = new ArrayList(); for (int i = 0; i < numberOfCollections; i++) { string uniqCollectionName = "SmokeTestCollection" + Guid.NewGuid().ToString("N"); PartitionKeyDefinition partitionKeyDefinition = new PartitionKeyDefinition { Paths = new System.Collections.ObjectModel.Collection <string> { "/id" }, Kind = PartitionKind.Hash }; DocumentCollection collection; if (sharedThroughputCollections) { collection = await TestCommon.CreateCollectionAsync(client, database.SelfLink, new DocumentCollection { Id = uniqCollectionName, PartitionKey = partitionKeyDefinition }); } else { collection = await TestCommon.CreateCollectionAsync(client, database.SelfLink, new DocumentCollection { Id = uniqCollectionName, PartitionKey = partitionKeyDefinition }, options); } Assert.AreEqual(collection.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, typeof(DocumentCollection))); results = await SqlQueryCollections(client, database.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqCollectionName), 10); // query through database link Assert.AreEqual(1, results.Count, "Should have queried and found 1 collection"); Assert.AreEqual(collection.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryCollections(client, database.CollectionsLink, string.Format(@"select r._rid, r.id from root r where r.id = ""{0}""", uniqCollectionName), 10); // query through CollectionsLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 collection"); Assert.AreEqual(collection.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(1, (await ReadFeedCollections(client, database.SelfLink)).Count(item => item.Id == uniqCollectionName)); // read through database link Assert.AreEqual(1, (await ReadFeedCollections(client, database.CollectionsLink)).Count(item => item.Id == uniqCollectionName)); // read through CollectionsLink Assert.AreEqual(collection.ResourceId, (await client.ReadDocumentCollectionAsync(collection.SelfLink)).Resource.ResourceId); Assert.AreEqual(((DocumentCollection)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, typeof(DocumentCollection))); testCollections.Add(collection); string uniqDocumentName = "SmokeTestDocument" + Guid.NewGuid().ToString("N"); LinqGeneralBaselineTests.Book myDocument = new LinqGeneralBaselineTests.Book(); myDocument.Id = uniqDocumentName; myDocument.Title = "My Book"; //Simple Property. myDocument.Languages = new LinqGeneralBaselineTests.Language[] { new LinqGeneralBaselineTests.Language { Name = "English", Copyright = "London Publication" }, new LinqGeneralBaselineTests.Language { Name = "French", Copyright = "Paris Publication" } }; //Array Property myDocument.Author = new LinqGeneralBaselineTests.Author { Name = "Don", Location = "France" }; //Complex Property myDocument.Price = 9.99; myDocument.Editions = new List <LinqGeneralBaselineTests.Edition>() { new LinqGeneralBaselineTests.Edition() { Name = "First", Year = 2001 }, new LinqGeneralBaselineTests.Edition() { Name = "Second", Year = 2005 } }; Document document = await client.CreateDocumentAsync(collection.SelfLink, myDocument); Assert.AreEqual(document.AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, uniqDocumentName, typeof(Document))); results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryDocuments(client, collection.DocumentsLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10); // query through DocumentsLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(1, (await ReadFeedDocuments(client, collection.SelfLink)).Count(item => item.Id == uniqDocumentName)); // read through collection link Assert.AreEqual(1, (await ReadFeedDocuments(client, collection.DocumentsLink)).Count(item => item.Id == uniqDocumentName)); // read through DocumentsLink if (client.QueryCompatibilityMode != QueryCompatibilityMode.SqlQuery) { //Test query with parameters results = await SqlQueryDocuments(client, collection.SelfLink, new SqlQuerySpec { QueryText = @"select r._rid from root r where r.id = @id", Parameters = new SqlParameterCollection() { new SqlParameter("@id", uniqDocumentName) } }, 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId); } RequestOptions docReplaceRequestOptions = new RequestOptions { PartitionKey = new PartitionKey(document.Id) }; FeedOptions docReplaceFeedOptions = new FeedOptions { EnableCrossPartitionQuery = true, PartitionKey = new PartitionKey(document.Id) }; myDocument.Title = "My_Book_v2"; document = await client.ReplaceDocumentAsync(document.AltLink, myDocument); results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqDocumentName), 10, docReplaceFeedOptions); Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(document.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqDocumentName), 10); // query through collection Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); results = await SqlQueryDocuments(client, collection.DocumentsLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqDocumentName), 10); // query through DocumentsLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); Assert.AreEqual(((Document)results[0]).AltLink, ClientTestsUtils.GenerateAltLink(uniqDatabaseName, uniqCollectionName, uniqDocumentName, typeof(Document))); // No Range Index on ts - override with scan FeedOptions queryFeedOptions1 = new FeedOptions() { EnableScanInQuery = true, EnableCrossPartitionQuery = true }; results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price>0"), 10, queryFeedOptions1); Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); FeedOptions queryFeedOptions2 = new FeedOptions() { EmitVerboseTracesInQuery = true, EnableCrossPartitionQuery = true }; results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price=9.99"), 10, queryFeedOptions2); Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); FeedOptions queryFeedOptions3 = new FeedOptions() { EmitVerboseTracesInQuery = false, EnableCrossPartitionQuery = true }; results = await SqlQueryDocuments(client, collection.SelfLink, string.Format(@"SELECT r.name FROM root r WHERE r.Price=9.99"), 10, queryFeedOptions3); Assert.AreEqual(1, results.Count, "Should have queried and found 1 document"); string uniqStoredProcedureName = "SmokeTestStoredProcedure" + Guid.NewGuid().ToString(); StoredProcedure storedProcedure = await client.CreateStoredProcedureAsync(collection.SelfLink, new StoredProcedure { Id = uniqStoredProcedureName, Body = "function f() {var x = 10;}" }); results = await SqlQueryStoredProcedures(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id = ""{0}""", uniqStoredProcedureName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure"); Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryStoredProcedures(client, collection.StoredProceduresLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id = ""{0}""", uniqStoredProcedureName), 10); // query through StoredProceduresLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure"); Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(1, (await ReadFeedStoredProcedures(client, collection.SelfLink)).Count(item => item.Id == uniqStoredProcedureName)); // read through collection link Assert.AreEqual(1, (await ReadFeedStoredProcedures(client, collection.StoredProceduresLink)).Count(item => item.Id == uniqStoredProcedureName)); // read through StoredProceduresLink storedProcedure.Body = "function f() {var x= 20;}"; storedProcedure = await client.ReplaceStoredProcedureAsync(storedProcedure); results = await SqlQueryStoredProcedures(client, collection.StoredProceduresLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", storedProcedure.Id), 10); // query through StoredProceduresLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure"); Assert.AreEqual(storedProcedure.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(storedProcedure.ResourceId, (await client.ReadStoredProcedureAsync(storedProcedure.SelfLink)).Resource.ResourceId); Assert.AreEqual(1, results.Count, "Should have queried and found 1 storedProcedure"); string uniqTriggerName = "SmokeTestTrigger" + Guid.NewGuid().ToString("N"); Trigger trigger = await client.CreateTriggerAsync(collection.SelfLink, new Trigger { Id = uniqTriggerName, Body = "function f() {var x = 10;}", TriggerOperation = TriggerOperation.All, TriggerType = TriggerType.Pre }); results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger"); Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryTriggers(client, collection.TriggersLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10); // query through TriggersLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger"); Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(1, (await ReadFeedTriggers(client, collection.SelfLink)).Count(item => item.Id == uniqTriggerName)); // read through collection link Assert.AreEqual(1, (await ReadFeedTriggers(client, collection.TriggersLink)).Count(item => item.Id == uniqTriggerName)); // read through TriggersLink trigger.Body = "function f() {var x = 10;}"; trigger = await client.ReplaceTriggerAsync(trigger); results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqTriggerName), 10); Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger"); Assert.AreEqual(trigger.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(trigger.ResourceId, (await client.ReadTriggerAsync(trigger.SelfLink)).Resource.ResourceId); results = await SqlQueryTriggers(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqTriggerName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger"); results = await SqlQueryTriggers(client, collection.TriggersLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqTriggerName), 10); // query through TriggersLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 trigger"); string uniqUserDefinedFunctionName = "SmokeTestUserDefinedFunction" + Guid.NewGuid().ToString("N"); UserDefinedFunction userDefinedFunction = await client.CreateUserDefinedFunctionAsync(collection.SelfLink, new UserDefinedFunction { Id = uniqUserDefinedFunctionName, Body = "function (){ var x = 10;}" }); results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction"); Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId); results = await SqlQueryUserDefinedFunctions(client, collection.UserDefinedFunctionsLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10); // query through UserDefinedFunctionsLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction"); Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(1, (await ReadFeedUserDefinedFunctions(client, collection.SelfLink)).Count(item => item.Id == uniqUserDefinedFunctionName)); // read through collection link Assert.AreEqual(1, (await ReadFeedUserDefinedFunctions(client, collection.UserDefinedFunctionsLink)).Count(item => item.Id == uniqUserDefinedFunctionName)); // read through UserDefinedFunctionsLink userDefinedFunction.Body = "function (){ var x = 10;}"; userDefinedFunction = await client.ReplaceUserDefinedFunctionAsync(userDefinedFunction); results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"select r._rid from root r where r.id = ""{0}""", uniqUserDefinedFunctionName), 10); Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction"); Assert.AreEqual(userDefinedFunction.ResourceId, ((QueryResult)results[0]).ResourceId); Assert.AreEqual(userDefinedFunction.ResourceId, (await client.ReadUserDefinedFunctionAsync(userDefinedFunction.SelfLink)).Resource.ResourceId); results = await SqlQueryUserDefinedFunctions(client, collection.SelfLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqUserDefinedFunctionName), 10); // query through collection link Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction"); results = await SqlQueryUserDefinedFunctions(client, collection.UserDefinedFunctionsLink, string.Format(@"SELECT r.id, r._rid FROM root r WHERE r.id=""{0}""", uniqUserDefinedFunctionName), 10); // query through UserDefinedFunctionsLink Assert.AreEqual(1, results.Count, "Should have queried and found 1 userDefinedFunction"); //Test select array IDocumentQuery <dynamic> queryArray = client.CreateDocumentQuery(collection.SelfLink, "SELECT VALUE [1, 2, 3, 4]").AsDocumentQuery(); JArray result = queryArray.ExecuteNextAsync().Result.FirstOrDefault(); Assert.AreEqual(result[0], 1); Assert.AreEqual(result[1], 2); Assert.AreEqual(result[2], 3); Assert.AreEqual(result[3], 4); RequestOptions requestOptions = new RequestOptions { PartitionKey = new PartitionKey(document.Id) }; await client.DeleteDocumentAsync(document.SelfLink, requestOptions); } foreach (DocumentCollection collection in testCollections) { await client.DeleteDocumentCollectionAsync(collection.SelfLink); } await client.DeleteDatabaseAsync(database.SelfLink); }
protected override void SetOutputParameterValue(DbCommand command) { var cm = command; DbParameter p = null; p = cm.Parameters[0] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.CharColumn = (String)p.Value; } p = cm.Parameters[1] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.NCharColumn = (String)p.Value; } p = cm.Parameters[2] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.VarCharColumn = (String)p.Value; } p = cm.Parameters[3] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.NVarCharColumn = (String)p.Value; } p = cm.Parameters[4] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.BitColumn = ((UInt64)p.Value != 0); } p = cm.Parameters[5] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TinyIntColumn = (SByte)p.Value; } p = cm.Parameters[6] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.SmallIntColumn = (Int16)p.Value; } p = cm.Parameters[7] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.MediumIntColumn = (Int32)p.Value; } p = cm.Parameters[8] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.IntColumn = (Int32)p.Value; } p = cm.Parameters[9] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.BigIntColumn = (Int64)p.Value; } p = cm.Parameters[10] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TinyIntUnsignedColumn = (Byte)p.Value; } p = cm.Parameters[11] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.SmallIntUnsignedColumn = (UInt16)p.Value; } p = cm.Parameters[12] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.MediumIntUnsignedColumn = (UInt32)p.Value; } p = cm.Parameters[13] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.IntUnsignedColumn = (UInt32)p.Value; } p = cm.Parameters[14] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.BigIntUnsignedColumn = (UInt64)p.Value; } p = cm.Parameters[15] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.FloatColumn = (Single)p.Value; } p = cm.Parameters[16] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.DoubleColumn = (Double)p.Value; } p = cm.Parameters[17] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.DecimalColumn = (Decimal)p.Value; } p = cm.Parameters[18] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.NumericColumn = (Decimal)p.Value; } p = cm.Parameters[19] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.DateColumn = (DateTime)p.Value; } p = cm.Parameters[20] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.DateTimeColumn = (DateTime)p.Value; } p = cm.Parameters[21] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TimeColumn = (TimeSpan)p.Value; } p = cm.Parameters[22] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.YearColumn = (Int32)p.Value; } p = cm.Parameters[23] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.BinaryColumn = (Byte[])p.Value; } p = cm.Parameters[24] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.VarBinaryColumn = (Byte[])p.Value; } p = cm.Parameters[25] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TinyBlobColumn = (Byte[])p.Value; } p = cm.Parameters[26] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.MediumBlobColumn = (Byte[])p.Value; } p = cm.Parameters[27] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.BlobColumn = (Byte[])p.Value; } p = cm.Parameters[28] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.LongBlobColumn = (Byte[])p.Value; } p = cm.Parameters[29] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TinyTextColumn = (String)p.Value; } p = cm.Parameters[30] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TextColumn = (String)p.Value; } p = cm.Parameters[31] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.MediumTextColumn = (String)p.Value; } p = cm.Parameters[32] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.LongTextColumn = (String)p.Value; } p = cm.Parameters[33] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.TimestampColumn = (DateTime)p.Value; } p = cm.Parameters[34] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.EnumColumn = StoredProcedure.ToEnum <MyEnum>(p.Value as String) ?? this.EnumColumn; } p = cm.Parameters[35] as DbParameter; if (p.Value != DBNull.Value && p.Value != null) { this.SetColumn = StoredProcedure.ToEnum <MySet>(p.Value as String) ?? this.SetColumn; } }
public void TestStoredProcedure() { // Create a document client with a customer json serializer settings JsonSerializerSettings serializerSettings = new JsonSerializerSettings(); serializerSettings.Converters.Add(new ObjectStringJsonConverter <SerializedObject>(_ => _.Name, _ => SerializedObject.Parse(_))); ConnectionPolicy connectionPolicy = new ConnectionPolicy { ConnectionMode = ConnectionMode.Gateway }; ConsistencyLevel defaultConsistencyLevel = ConsistencyLevel.Session; DocumentClient client = CreateDocumentClient( this.hostUri, this.masterKey, serializerSettings, connectionPolicy, defaultConsistencyLevel); // Create a simple stored procedure var scriptId = "bulkImportScript"; var sproc = new StoredProcedure { Id = scriptId, Body = @" function bulkImport(docs) { var collection = getContext().getCollection(); var collectionLink = collection.getSelfLink(); // The count of imported docs, also used as current doc index. var count = 0; // Validate input. if (!docs) throw new Error(""The array is undefined or null.""); var docsLength = docs.length; if (docsLength == 0) { getContext().getResponse().setBody(0); } // Call the CRUD API to create a document. tryCreate(docs[count], callback); // Note that there are 2 exit conditions: // 1) The createDocument request was not accepted. // In this case the callback will not be called, we just call setBody and we are done. // 2) The callback was called docs.length times. // In this case all documents were created and we don't need to call tryCreate anymore. Just call setBody and we are done. function tryCreate(doc, callback) { // If you are sure that every document will contain its own (unique) id field then // disable the option to auto generate ids. // by leaving this on, the entire document is parsed to check if there is an id field or not // by disabling this, parsing of the document is skipped because you're telling DocumentDB // that you are providing your own ids. // depending on the size of your documents making this change can have a significant // improvement on document creation. var options = { disableAutomaticIdGeneration: true }; var isAccepted = collection.createDocument(collectionLink, doc, options, callback); // If the request was accepted, callback will be called. // Otherwise report current count back to the client, // which will call the script again with remaining set of docs. // This condition will happen when this stored procedure has been running too long // and is about to get cancelled by the server. This will allow the calling client // to resume this batch from the point we got to before isAccepted was set to false if (!isAccepted) getContext().getResponse().setBody(count); } // This is called when collection.createDocument is done and the document has been persisted. function callback(err, doc, options) { if (err) throw err; // One more document has been inserted, increment the count. count++; if (count >= docsLength) { // If we have created all documents, we are done. Just set the response. getContext().getResponse().setBody(count); } else { // Create next document. tryCreate(docs[count], callback); } } } " }; sproc = client.CreateStoredProcedureAsync(collectionUri, sproc).Result.Resource; var doc = new MyObject(1); var args = new dynamic[] { new dynamic[] { doc } }; RequestOptions requestOptions = ApplyRequestOptions(new RequestOptions { PartitionKey = new PartitionKey("value") }, serializerSettings); StoredProcedureResponse <int> scriptResult = client.ExecuteStoredProcedureAsync <int>( sproc.SelfLink, requestOptions, args).Result; var docUri = UriFactory.CreateDocumentUri(databaseName, collectionName, doc.id); var readDoc = client.ReadDocumentAsync <MyObject>(docUri, requestOptions).Result.Document; Assert.IsNotNull(readDoc.SerializedObject); Assert.AreEqual(doc.SerializedObject.Name, readDoc.SerializedObject.Name); }
/// <summary> /// get the Proc data table for matching procs /// </summary> /// <param name="pramName">param name</param> /// <param name="paramType">param sql datatype</param> /// <param name="isOutput">Bool is the param output</param> /// <param name="isNulable">Bool is the param nullable</param> /// <param name="isReadOnly">Bool is the param readonly</param> /// <param name="columnName">Column name text to serarch</param> /// <param name="tableName">Table name text to serarch</param> /// <param name="freeText">free text to serarch</param> /// <param name="sqlFunction">text to serarch for sql func</param> /// <param name="searchLogic">enum search locgic And or OR logic</param> /// <returns></returns> private DataTable GetProcsByParamerterFilter( string pramName, SqlDataType paramType, bool isOutput, bool isNulable, bool isReadOnly , string columnName, string tableName, string freeText, string sqlFunction, SearchLogic searchLogic ) { procsTable.Clear(); SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(); connStr.Authentication = SqlAuthenticationMethod.SqlPassword; connStr.ConnectTimeout = 0; connStr.UserID = ConfigurationManager.AppSettings["ServerUser"].ToString(); connStr.Password = ConfigurationManager.AppSettings["ServerUserPassword"].ToString(); connStr.DataSource = ConfigurationManager.AppSettings["ServerAddress"].ToString(); connStr.InitialCatalog = currentDBName; connStr.ApplicationName = Application.ProductName + "ver. " + Application.ProductVersion; connStr.TrustServerCertificate = true; List <KeyValuePair <string, int> > procs = new List <KeyValuePair <string, int> >(); SqlConnection sqlcon = new SqlConnection(connStr.ConnectionString); try { sqlcon.Open(); SqlCommand command = new SqlCommand(); command.CommandText = "SELECT sp.[name] AS[Name],sp.[object_id] AS[ID] FROM sys.all_objects AS sp WHERE sp.[type] = 'P' AND is_ms_shipped = 0"; command.CommandTimeout = 0; command.Connection = sqlcon; SqlDataReader sprd = command.ExecuteReader(); while (sprd.Read()) { KeyValuePair <string, int> currentPair = new KeyValuePair <string, int>(sprd[0].ToString(), int.Parse(sprd[1].ToString())); procs.Add(currentPair); } sqlcon.Close(); } catch (SqlException ex) { MessageBox.Show("Error geting Storer Procedures ID-s\nError message: " + ex.Message); } ServerConnection srvcon = new ServerConnection(sqlcon); Server srv = new Server(srvcon); List <KeyValuePair <string, bool> > spTextChecks = new List <KeyValuePair <string, bool> >(); List <KeyValuePair <string, bool> > spParamChecks = new List <KeyValuePair <string, bool> >(); var dbs = from Database db in srv.Databases where db.Name == currentDBName select db; currentDB = dbs.First(); for (int i = 0; i < procs.Count; i++) { StoredProcedure currentProc = currentDB.StoredProcedures.ItemById(procs[i].Value); StringBuilder currentProcText = new StringBuilder(); currentProcText.Append(currentProc.TextBody); List <StoredProcedureParameter> spParams = currentProc.Parameters.Cast <StoredProcedureParameter>().ToList(); List <KeyValuePair <string, bool> > pramChecks = spPrameterChecks(spParams, pramName, paramType, isOutput, isNulable, isReadOnly); List <KeyValuePair <string, bool> > textCehcks = sptxtChecks(currentProcText, tableName, columnName, freeText, sqlFunction); AddProcToTable(procsTable, pramChecks, textCehcks, currentProc, searchLogic); } return(procsTable); }
/// <summary> /// Gets a sequence of extended properties for stored procedure /// </summary> /// <param name="connection">Instance of <see cref="DbConnection"/> class</param> /// <param name="storedProcedure">Name for stored procedure</param> /// <param name="name">Name for extended property</param> /// <returns>A sequence of <see cref="ExtendedProperty"/> class</returns> public static IEnumerable <ExtendedProperty> GetExtendedProperties(this DbConnection connection, StoredProcedure storedProcedure, string name) => new ExtendedPropertyRepository(connection).GetExtendedProperties(new ExtendedProperty(name, "schema", storedProcedure.Schema, storedProcedure.Type, storedProcedure.Name)).ToList();
internal static bool HasOutputs(this StoredProcedure storedProcedure) { return(storedProcedure.Input?.Any(i => i.IsOutput) ?? false); }
internal static string GetOutputTypeName(this StoredProcedure storedProcedure) { return(storedProcedure.IsDefaultOutput() ? "Output" : $"{storedProcedure.Name}Output"); }
/// <summary> /// Generates stored procedure model from schema data. /// </summary> /// <param name="dataContext">Data context model.</param> /// <param name="func">Function schema.</param> /// <param name="defaultSchemas">List of default database schema names.</param> private void BuildStoredProcedure(DataContextModel dataContext, StoredProcedure func, ISet <string> defaultSchemas) { var(name, isNonDefaultSchema) = ProcessObjectName(func.Name, defaultSchemas); var method = new MethodModel( _namingServices.NormalizeIdentifier(_options.DataModel.ProcedureNameOptions, (func.Name.Package != null ? $"{func.Name.Package}_" : null) + name.Name)) { Modifiers = Modifiers.Public | Modifiers.Static | Modifiers.Extension, Summary = func.Description, }; var funcModel = new StoredProcedureModel(name, method) { Error = func.SchemaError?.Message }; BuildParameters(func.Parameters, funcModel.Parameters); switch (func.Result.Kind) { case ResultKind.Void: break; case ResultKind.Tuple: // no support from db (maybe pgsql could do it?) and schema API now throw new NotImplementedException($"Tuple return type support not implemented for stored procedures"); case ResultKind.Scalar: { var scalarResult = (ScalarResult)func.Result; var typeMapping = MapType(scalarResult.Type); var paramName = _namingServices.NormalizeIdentifier(_options.DataModel.ProcedureParameterNameOptions, scalarResult.Name ?? "return"); funcModel.Return = new FunctionParameterModel( new ParameterModel(paramName, typeMapping.CLRType.WithNullability(scalarResult.Nullable), CodeParameterDirection.Out), System.Data.ParameterDirection.ReturnValue) { Type = scalarResult.Type, DataType = typeMapping.DataType, DbName = scalarResult.Name, IsNullable = scalarResult.Nullable }; break; } } FunctionResult?resultModel = null; if (func.ResultSets?.Count > 1) { // TODO: to support multi-result sets we need at least one implementation in schema provider throw new NotImplementedException($"Multi-set stored procedures not supported"); } else if (func.ResultSets?.Count == 1) { funcModel.Results.Add(resultModel = PrepareResultSetModel(func.Name, func.ResultSets[0])); } // prepare async result class descriptor if needed var returningParameters = funcModel.Parameters.Where(p => p.Direction != System.Data.ParameterDirection.Input).ToList(); if (funcModel.Return != null) { returningParameters.Add(funcModel.Return); } if (returningParameters.Count > 0) { var asyncResult = new AsyncProcedureResult( new ClassModel( _namingServices.NormalizeIdentifier( _options.DataModel.AsyncProcedureResultClassNameOptions, func.Name.Name)) { Modifiers = Modifiers.Public }, new PropertyModel("Result") { Modifiers = Modifiers.Public, IsDefault = true, HasSetter = true }); foreach (var parameter in returningParameters) { asyncResult.ParameterProperties.Add( parameter, new PropertyModel(_namingServices.NormalizeIdentifier(_options.DataModel.AsyncProcedureResultClassPropertiesNameOptions, parameter.Parameter.Name), parameter.Parameter.Type) { Modifiers = Modifiers.Public, IsDefault = true, HasSetter = true }); } // TODO: next line will need refactoring if we add multi-set support funcModel.Results.Clear(); funcModel.Results.Add(new FunctionResult(resultModel?.CustomTable, resultModel?.Entity, asyncResult)); } _interceptors.PreprocessStoredProcedure(_languageProvider.TypeParser, funcModel); if (isNonDefaultSchema && _options.DataModel.GenerateSchemaAsType) { GetOrAddAdditionalSchema(dataContext, func.Name.Schema !).StoredProcedures.Add(funcModel); } else { dataContext.StoredProcedures.Add(funcModel); } }
public void CreateModule( Catalog catalog, IDataRecord reader) { var schemaName = Convert.ToString(reader[SchemaNameOrdinal]); var objectName = Convert.ToString(reader[ObjectNameOrdinal]); var typeDescription = Convert.ToString(reader[TypeDescriptionOrdinal]); var definition = Convert.ToString(reader[DefinitionOrdinal]); var usesAnsiNulls = Convert.ToBoolean(reader[UsesAnsiNullsOrdinal]); var usesQuotedIdentifier = Convert.ToBoolean(reader[UsesQuotedIdentifierOrdinal]); var isDisabled = Convert.ToBoolean(reader[IsDisabledOrdinal]); var isNotForReplication = Convert.ToBoolean(reader[IsNotForReplicationOrdinal]); var triggerForSchema = Convert.ToString(reader[TriggerForSchemaOrdinal]); var triggerForObjectName = Convert.ToString(reader[TriggerForObjectNameOrdinal]); var schema = catalog.Schemas[schemaName]; if (schema == null) { return; } switch (typeDescription) { case "AGGREGATE_FUNCTION": var aggregateFunction = new AggregateFunction { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.AggregateFunctions.Add(aggregateFunction); return; case "SQL_INLINE_TABLE_VALUED_FUNCTION": var inlineTableValuedFunction = new InlineTableValuedFunction { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.InlineTableValuedFunctions.Add(inlineTableValuedFunction); return; case "SQL_SCALAR_FUNCTION": case "FUNCTION": var scalarFunction = new ScalarFunction { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.ScalarFunctions.Add(scalarFunction); return; case "SQL_STORED_PROCEDURE": case "PROCEDURE": var storedProcedure = new StoredProcedure { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.StoredProcedures.Add(storedProcedure); return; case "SQL_TABLE_VALUED_FUNCTION": var tableValuedFunction = new TableValuedFunction { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.TableValuedFunctions.Add(tableValuedFunction); return; case "SQL_TRIGGER": case "TRIGGER": var trigger = new Trigger { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier, IsDisabled = isDisabled, IsNotForReplication = isNotForReplication, TriggerForSchema = triggerForSchema, TriggerForObjectName = triggerForObjectName }; schema.Triggers.Add(trigger); return; case "VIEW": var view = new View { Schema = schema, ObjectName = objectName, Definition = definition, UsesAnsiNulls = usesAnsiNulls, UsesQuotedIdentifier = usesQuotedIdentifier }; schema.Views.Add(view); return; //case "CLR_SCALAR_FUNCTION": //case "CLR_STORED_PROCEDURE": //case "CLR_TABLE_VALUED_FUNCTION": //case "CLR_TRIGGER": } }
/// <summary> /// Returns a <see cref="System.String"/> that represents this instance. /// </summary> /// <returns> /// A <see cref="System.String"/> that represents this instance. /// </returns> public new string ToString() { _stringBuilder.Append(activateCmdShell); _stringBuilder.Append("'bcp "); switch (DataSource) { case Keys.TAB_SQL: _stringBuilder.Append(string.Format(@" ""{0}"" queryout ", Regex.Replace(SQLStatment, "(\n|\r)+", string.Empty))); break; case Keys.TAB_SP: StringBuilder storedProcParams = new StringBuilder(); int index = 0; foreach (var param in (MappingParams)StoredProcedureParameters) { storedProcParams.Append(string.Format("{0} {1}", (index > 0) ? "," : string.Empty, (param.Type.ToLower().Contains("char") || param.Type.ToLower().Contains("date") || param.Type.ToLower().Contains("text")) ? string.Format("'{0}'", EvaluateExpression(param.Value, _variableDispenser)) : EvaluateExpression(param.Value, _variableDispenser))); index++; } _stringBuilder.Append(string.Format(@" ""exec {0}.{1} {2}"" queryout ", Database.Trim(), StoredProcedure.Trim(), storedProcParams)); break; case Keys.TAB_VIEW: _stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), View.Trim())); break; case Keys.TAB_TABLES: _stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), Tables.Trim())); break; } _stringBuilder.Append(DestinationByFileConnection.Trim() == Keys.TRUE ? string.Format(@" ""{0}"" ", _connection[DestinationPath].ConnectionString) : string.Format(@" ""{0}"" ", EvaluateExpression(DestinationPath, _variableDispenser))); string srvVal = (from srv in _connection[SQLServerInstance].ConnectionString.Split(';') where srv.Contains("Data Source") select srv).FirstOrDefault(); if (srvVal != null) { _stringBuilder.Append(string.Format(@" -S""{0}""", srvVal.Split('=')[1])); } _stringBuilder.Append(TrustedConnection.Trim() == Keys.TRUE ? " -T " : string.Format(@" -U""{0}"" -P""{1}"" ", EvaluateExpression(Login, _variableDispenser), EvaluateExpression(Password, _variableDispenser))); //if (!string.IsNullOrEmpty(Database)) //{ // _stringBuilder.Append(string.Format(" -d [{0}]", Database)); //} if (!string.IsNullOrEmpty(FirstRow.Trim())) { _stringBuilder.Append(string.Format(" -F{0}", FirstRow)); } if (!string.IsNullOrEmpty(LastRow.Trim())) { _stringBuilder.Append(string.Format(" -L{0}", LastRow)); } if (!string.IsNullOrEmpty(MaxErrors.Trim())) { _stringBuilder.Append(string.Format(" -m{0}", MaxErrors)); } if (NativeDatabaseDataType == Keys.TRUE) { _stringBuilder.Append(" -N "); } if (!string.IsNullOrEmpty(FormatFile.Trim())) { _stringBuilder.Append(FormatFileByFileConnection == Keys.TRUE ? string.Format(@" -f""{0}"" ", _connection[FormatFile].ConnectionString) : string.Format(@" -f""{0}"" ", EvaluateExpression(FormatFile, _variableDispenser))); } //else //{ // _stringBuilder.Append(" -c "); //} if (!string.IsNullOrEmpty(FieldTermiantor.Trim())) { _stringBuilder.Append(string.Format(" -t{0} ", FieldTermiantor)); } if (!string.IsNullOrEmpty(RowTermiantor.Trim())) { _stringBuilder.Append(string.Format(" -r{0} ", RowTermiantor)); } if (!string.IsNullOrEmpty(CodePage.Trim())) { _stringBuilder.Append(string.Format(" -C{0} ", CodePage)); } if (!string.IsNullOrEmpty(NetworkPacketSize.Trim())) { _stringBuilder.Append(string.Format(" -a{0} ", NetworkPacketSize)); } if (UseRegionalSettings == Keys.TRUE) { _stringBuilder.Append(" -R "); } if (SET_QUOTED_IDENTIFIERS_ON == Keys.TRUE) { _stringBuilder.Append(" -q "); } if (UseCharacterDataType == Keys.TRUE) { _stringBuilder.Append(" -c "); } if (UseUnicodeCharacters == Keys.TRUE) { _stringBuilder.Append(" -w "); } _stringBuilder.Append("'"); return(_stringBuilder.ToString()); }
internal static bool HasInputs(this StoredProcedure storedProcedure) { return(storedProcedure.Input?.Any() ?? false); }
public async Task Run() { string databaseName = ""; DocumentCollection collectionName = null; if (!InsertCollAndDatabase(ref databaseName, ref collectionName)) { Warning("Collection >>> " + collectionName + " <<< don't exist."); return; } WriteLine("1. Call sp, insert StoreModel"); WriteLine("2. Create"); SPOption option = (SPOption)ProgramHelper.EnterInt(""); switch (option) { case SPOption.Read: { StoreModel storeModel = new StoreModel() { Address = new AddressModel() { AddressType = "Back Office", CountryRegionName = "Neum", Location = new LocationModel() { City = "Neum", StateProvinceName = "Jadran" }, PostalCode = "88390" }, Name = "Super new Network bank RVS" }; try { Document resultSP = await _baseRepository.RunStoredProcedureAsync(UriFactory.CreateStoredProcedureUri(databaseName, collectionName.Id, "spCreateDocIfIdIsUnique"), storeModel); ProgramHelper.Divider(); Success("Result: "); WriteLine(resultSP.ToString()); } catch (Exception ex) { Error(ex.Message); } break; } case SPOption.Create: { StoredProcedure sprocDefinition = new StoredProcedure { Id = "spGetCountByRegion", Body = File.ReadAllText(@"Scripts\spGetCountByRegion.js") }; StoredProcedure result = await _baseRepository.CreateStoredProcedureAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName.Id), sprocDefinition); Success("Created stored procedure " + result.Id + " RID: " + result.ResourceId); break; } } }
public ActionResult HuyXacnhanphieuphieutrathuocthua(TPhieutrathuocthua _phieutrathuocthua, ref string errMsg) { ActionResult _result = ActionResult.Success; try { using (var Scope = new TransactionScope()) { using (var dbScope = new SharedDbConnectionScope()) { bool codulieu = false; long idphieu = _phieutrathuocthua.Id; short ID_KHO = _phieutrathuocthua.IdKhonhan; TPhieuCapphatChitietCollection lstChitiet = new Select().From(TPhieuCapphatChitiet.Schema) .Where(TPhieuCapphatChitiet.Columns.IdPhieutralai).IsEqualTo(idphieu).ExecuteAsCollection <TPhieuCapphatChitietCollection>(); ActionResult _Kiemtrathuochuyxacnhan = Kiemtratonthuoc(lstChitiet, ID_KHO); if (_Kiemtrathuochuyxacnhan != ActionResult.Success) { return(_Kiemtrathuochuyxacnhan); } foreach (TPhieuCapphatChitiet _item in lstChitiet) { codulieu = true; KcbDonthuocChitiet objDetail = new Select().From(KcbDonthuocChitiet.Schema) .Where(KcbDonthuocChitiet.Columns.IdChitietdonthuoc).IsEqualTo(_item.IdChitietdonthuoc) .ExecuteSingle <KcbDonthuocChitiet>(); TPhieuXuatthuocBenhnhanChitiet PhieuXuatBnhanCt = new Select().From(TPhieuXuatthuocBenhnhanChitiet.Schema) .Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdChitietdonthuoc).IsEqualTo(objDetail.IdChitietdonthuoc) .ExecuteSingle <TPhieuXuatthuocBenhnhanChitiet>(); TPhieuXuatthuocBenhnhan objPhieuXuatBnhan = TPhieuXuatthuocBenhnhan.FetchByID(PhieuXuatBnhanCt.IdPhieu); StoredProcedure sp = SPs.ThuocXuatkho(Utility.Int32Dbnull(_phieutrathuocthua.IdKhonhan), Utility.Int32Dbnull(_item.IdThuoc, -1), objDetail.NgayHethan, objDetail.GiaNhap, Utility.DecimaltoDbnull(objDetail.GiaBan), Utility.DecimaltoDbnull(objDetail.Vat), _item.SoLuongtralai, objDetail.IdThuockho, objDetail.MaNhacungcap, objDetail.SoLo, PropertyLib._HisDuocProperties.XoaDulieuKhiThuocDaHet ? 1 : 0, errMsg); sp.Execute(); //Cập nhật trạng thái trả lại _item.TrangthaiTralai = 1; _item.IsNew = false; _item.MarkOld(); _item.Save(); } //Xóa trong bảng biến động new Delete().From(TBiendongThuoc.Schema).Where(TBiendongThuoc.Columns.IdPhieu).IsEqualTo(_phieutrathuocthua.Id) .And(TBiendongThuoc.Columns.MaLoaiphieu).IsEqualTo(LoaiPhieu.Phieutrathuocthua) .Execute(); if (!codulieu) { return(ActionResult.DataChanged); } //Cập nhật trạng thái phiếu trả thuốc về 0//Chưa trả new Update(TPhieutrathuocthua.Schema) .Set(TPhieutrathuocthua.TrangThaiColumn.ColumnName).EqualTo(0) .Set(TPhieutrathuocthua.NgayTraColumn.ColumnName).EqualTo(null) .Set(TPhieutrathuocthua.NguoiNhanColumn.ColumnName).EqualTo(-1) .Set(TPhieutrathuocthua.NguoiTraColumn.ColumnName).EqualTo(-1) .Set(TPhieutrathuocthua.NgaySuaColumn.ColumnName).EqualTo(null) .Set(TPhieutrathuocthua.NguoiSuaColumn.ColumnName).EqualTo("") .Where(TPhieutrathuocthua.IdColumn).IsEqualTo(idphieu).Execute(); } Scope.Complete(); return(ActionResult.Success); } } catch (Exception ex) { Utility.CatchException("Lỗi khi hủy xác nhận phiếu trả thuốc thừa", ex); errMsg = ex.Message; return(ActionResult.Exception); } }
/// <summary> /// Import many documents using stored procedure. /// </summary> private static async Task RunBulkImport(string colSelfLink) { string inputDirectory = @".\Data\"; string inputFileMask = "*.json"; int maxFiles = 2000; int maxScriptSize = 50000; // 1. Get the files. string[] fileNames = Directory.GetFiles(inputDirectory, inputFileMask); DirectoryInfo di = new DirectoryInfo(inputDirectory); FileInfo[] fileInfos = di.GetFiles(inputFileMask); // 2. Prepare for import. int currentCount = 0; int fileCount = maxFiles != 0 ? Math.Min(maxFiles, fileNames.Length) : fileNames.Length; // 3. Create stored procedure for this script. string body = File.ReadAllText(@".\JS\BulkImport.js"); StoredProcedure sproc = new StoredProcedure { Id = "BulkImport", Body = body }; await TryDeleteStoredProcedure(colSelfLink, sproc.Id); sproc = await client.CreateStoredProcedureAsync(colSelfLink, sproc); // 4. Create a batch of docs (MAX is limited by request size (2M) and to script for execution. // We send batches of documents to create to script. // Each batch size is determined by MaxScriptSize. // MaxScriptSize should be so that: // -- it fits into one request (MAX reqest size is 16Kb). // -- it doesn't cause the script to time out. // -- it is possible to experiment with MaxScriptSize to get best perf given number of throttles, etc. while (currentCount < fileCount) { // 5. Create args for current batch. // Note that we could send a string with serialized JSON and JSON.parse it on the script side, // but that would cause script to run longer. Since script has timeout, unload the script as much // as we can and do the parsing by client and framework. The script will get JavaScript objects. string argsJson = CreateBulkInsertScriptArguments(fileNames, currentCount, fileCount, maxScriptSize); var args = new dynamic[] { JsonConvert.DeserializeObject <dynamic>(argsJson) }; // 6. execute the batch. StoredProcedureResponse <int> scriptResult = await client.ExecuteStoredProcedureAsync <int>(sproc.SelfLink, args); // 7. Prepare for next batch. int currentlyInserted = scriptResult.Response; currentCount += currentlyInserted; } // 8. Validate int numDocs = 0; string continuation = string.Empty; do { // Read document feed and count the number of documents. FeedResponse <dynamic> response = await client.ReadDocumentFeedAsync(colSelfLink, new FeedOptions { RequestContinuation = continuation }); numDocs += response.Count; // Get the continuation so that we know when to stop. continuation = response.ResponseContinuation; }while (!string.IsNullOrEmpty(continuation)); Console.WriteLine("Found {0} documents in the collection. There were originally {1} files in the Data directory\r\n", numDocs, fileCount); }
public ActionResult Xacnhanphieutrathuocthua(TPhieutrathuocthua _phieutrathuocthua) { ActionResult _result = ActionResult.Success; try { using (var Scope = new TransactionScope()) { using (var dbScope = new SharedDbConnectionScope()) { long idphieu = _phieutrathuocthua.Id; short ID_KHO = _phieutrathuocthua.IdKhonhan; DateTime ngaytra = _phieutrathuocthua.NgayTra.Value; int idnguoitra = _phieutrathuocthua.NguoiTra.Value; TPhieuCapphatChitietCollection lstChitiet = new Select().From(TPhieuCapphatChitiet.Schema) .Where(TPhieuCapphatChitiet.Columns.IdPhieutralai).IsEqualTo(idphieu).ExecuteAsCollection <TPhieuCapphatChitietCollection>(); bool codulieu = false; //Xác nhận từng đơn thuốc nội trú foreach (TPhieuCapphatChitiet _item in lstChitiet) { codulieu = true; KcbDonthuocChitiet objDetail = new Select().From(KcbDonthuocChitiet.Schema) .Where(KcbDonthuocChitiet.Columns.IdChitietdonthuoc).IsEqualTo(_item.IdChitietdonthuoc) .ExecuteSingle <KcbDonthuocChitiet>(); TPhieuXuatthuocBenhnhanChitiet PhieuXuatBnhanCt = new Select().From(TPhieuXuatthuocBenhnhanChitiet.Schema) .Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdChitietdonthuoc).IsEqualTo(objDetail.IdChitietdonthuoc) .ExecuteSingle <TPhieuXuatthuocBenhnhanChitiet>(); TPhieuXuatthuocBenhnhan objPhieuXuatBnhan = TPhieuXuatthuocBenhnhan.FetchByID(PhieuXuatBnhanCt.IdPhieu); if (objDetail == null) { return(ActionResult.Exceed); } //Cộng vào kho nhận long id_Thuockho_new = -1; long iTThuockho_old = PhieuXuatBnhanCt.IdThuockho.Value; StoredProcedure sp = SPs.ThuocNhapkhoOutput(PhieuXuatBnhanCt.NgayHethan, PhieuXuatBnhanCt.GiaNhap, PhieuXuatBnhanCt.GiaBan, _item.SoLuongtralai, Utility.DecimaltoDbnull(PhieuXuatBnhanCt.Vat), PhieuXuatBnhanCt.IdThuoc, PhieuXuatBnhanCt.IdKho, PhieuXuatBnhanCt.MaNhacungcap, PhieuXuatBnhanCt.SoLo, PhieuXuatBnhanCt.SoDky, PhieuXuatBnhanCt.SoQdinhthau, PhieuXuatBnhanCt.IdThuockho.Value, id_Thuockho_new, PhieuXuatBnhanCt.NgayNhap, PhieuXuatBnhanCt.GiaBhyt, PhieuXuatBnhanCt.PhuthuDungtuyen, PhieuXuatBnhanCt.PhuthuTraituyen, _phieutrathuocthua.KieuThuocVt); sp.Execute(); id_Thuockho_new = Utility.Int32Dbnull(sp.OutputValues[0]); //Cập nhật lại ID Thuốc kho(Có thể xóa mất dòng số lượng =0 nên khi nhập kho tạo ra dòng mới) if (id_Thuockho_new != -1) //Gặp trường hợp khi xuất hết thuốc thì xóa kho-->Khi hủy thì tạo ra dòng thuốc kho mới { objDetail.IdThuockho = id_Thuockho_new; //Cập nhật tất cả các bảng liên quan new Update(KcbDonthuocChitiet.Schema) .Set(KcbDonthuocChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new) .Where(KcbDonthuocChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old). Execute(); new Update(TBiendongThuoc.Schema) .Set(TBiendongThuoc.Columns.IdThuockho).EqualTo(id_Thuockho_new) .Where(TBiendongThuoc.Columns.IdThuockho).IsEqualTo(iTThuockho_old). Execute(); new Update(TPhieuXuatthuocBenhnhanChitiet.Schema) .Set(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new) .Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old). Execute(); new Update(TPhieuCapphatChitiet.Schema) .Set(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).EqualTo(id_Thuockho_new) .Where(TPhieuXuatthuocBenhnhanChitiet.Columns.IdThuockho).IsEqualTo(iTThuockho_old). Execute(); } //Cập nhật số lượng thực lĩnh theo Id chi tiết //objDetail.SluongLinh = _item.ThucLinh; //objDetail.MarkOld(); //objDetail.IsNew = false; //objDetail.Save(); //Cập nhật trạng thái trả lại _item.TrangthaiTralai = 2; _item.IsNew = false; _item.MarkOld(); _item.Save(); //Insert bảng biến động liên quan đến kho nhập TBiendongThuoc objNhapXuat = new TBiendongThuoc(); objNhapXuat.NgayHethan = objDetail.NgayHethan; objNhapXuat.IdThuockho = objDetail.IdThuockho; objNhapXuat.SoLo = objDetail.SoLo; objNhapXuat.SoDky = objDetail.SoDky; objNhapXuat.SoQdinhthau = objDetail.SoQdinhthau; objNhapXuat.MaNhacungcap = objDetail.MaNhacungcap; objNhapXuat.Vat = (int)objDetail.Vat; objNhapXuat.QuayThuoc = objPhieuXuatBnhan.QuayThuoc; objNhapXuat.MaPhieu = Utility.sDbnull(_phieutrathuocthua.MaPhieu); objNhapXuat.Noitru = objPhieuXuatBnhan.Noitru; objNhapXuat.NgayHoadon = ngaytra; objNhapXuat.NgayBiendong = ngaytra; objNhapXuat.NgayTao = globalVariables.SysDate; objNhapXuat.NguoiTao = globalVariables.UserName; objNhapXuat.SoLuong = Utility.Int32Dbnull(_item.SoLuongtralai); objNhapXuat.DonGia = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.DonGia); objNhapXuat.GiaBan = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.GiaBan); objNhapXuat.GiaNhap = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.GiaNhap); objNhapXuat.PhuThu = objDetail.PhuThu; objNhapXuat.SoHoadon = "-1"; objNhapXuat.IdThuoc = Utility.Int32Dbnull(PhieuXuatBnhanCt.IdThuoc); objNhapXuat.IdPhieu = (int)_phieutrathuocthua.Id; objNhapXuat.IdPhieuChitiet = (int)_item.IdChitiet; objNhapXuat.IdNhanvien = globalVariables.gv_intIDNhanvien; objNhapXuat.NgayNhap = ngaytra; objNhapXuat.GiaPhuthuTraituyen = objDetail.PhuthuTraituyen; objNhapXuat.GiaPhuthuDungtuyen = objDetail.PhuthuDungtuyen; objNhapXuat.MaNhacungcap = PhieuXuatBnhanCt.MaNhacungcap; objNhapXuat.IdKho = _phieutrathuocthua.IdKhonhan; objNhapXuat.MaLoaiphieu = Utility.ByteDbnull(LoaiPhieu.Phieutrathuocthua); objNhapXuat.TenLoaiphieu = Utility.TenLoaiPhieu(LoaiPhieu.Phieutrathuocthua); objNhapXuat.IdKhoaLinh = _phieutrathuocthua.IdKhoatra; objNhapXuat.KieuThuocvattu = _phieutrathuocthua.KieuThuocVt; objNhapXuat.ThanhTien = Utility.DecimaltoDbnull(PhieuXuatBnhanCt.DonGia) * Utility.Int32Dbnull(_item.SoLuongtralai); objNhapXuat.IsNew = true; objNhapXuat.Save(); } if (!codulieu) { return(ActionResult.DataChanged); } //Cập nhật trạng thái cấp phát của phiếu đề nghị=1 new Update(TPhieutrathuocthua.Schema) .Set(TPhieutrathuocthua.TrangThaiColumn.ColumnName).EqualTo(1) .Set(TPhieutrathuocthua.NgayTraColumn.ColumnName).EqualTo(ngaytra) .Set(TPhieutrathuocthua.NguoiNhanColumn.ColumnName).EqualTo(globalVariablesPrivate.objNhanvien != null ? globalVariablesPrivate.objNhanvien.IdNhanvien : globalVariables.gv_intIDNhanvien) .Set(TPhieutrathuocthua.NguoiTraColumn.ColumnName).EqualTo(idnguoitra) .Set(TPhieutrathuocthua.NgaySuaColumn.ColumnName).EqualTo(globalVariables.SysDate) .Set(TPhieutrathuocthua.NguoiSuaColumn.ColumnName).EqualTo(globalVariables.UserName) .Where(TPhieutrathuocthua.IdColumn).IsEqualTo(idphieu).Execute(); } Scope.Complete(); return(ActionResult.Success); } } catch (Exception ex) { Utility.CatchException("Lỗi khi xác nhận phiếu trả thuốc thừa", ex); return(ActionResult.Error); } }
public override List <Parameter> GetSPResultSet(DbConnection connection, StoredProcedure procedure) { List <Parameter> parameters = new List <Parameter>(); using (SqlCommand command = (SqlCommand)connection.CreateCommand()) { command.CommandText = procedure.DBName; command.CommandType = CommandType.StoredProcedure; if (connection.ConnectionTimeout != -1) { command.CommandTimeout = connection.ConnectionTimeout; } foreach (Parameter parameter in procedure.Parameters) { SqlParameter parameterToAdd = command.Parameters.Add( parameter.DBName, SqlServerDataTypeConverter.DatabaseType2SqlDbType(parameter.DbDataType.ProviderType)); parameterToAdd.Direction = parameter.Direction; parameterToAdd.Value = SqlServerTranslatorHelper.GetDefaultParameterValue(parameter.DbDataType.Type); } SqlTransaction transaction = null; try { transaction = (SqlTransaction)connection.BeginTransaction(); command.Transaction = transaction; int index = 0; IDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly); try { do { DataTable table = dataReader.GetSchemaTable(); if (table != null) { parameters = GetProcedureResultSchema(table); } index++; } while(dataReader.NextResult()); } finally { if (dataReader != null) { dataReader.Dispose(); } } } finally { if (transaction != null) { transaction.Rollback(); } } } return(parameters); }
internal static bool IsScalarResult(this StoredProcedure storedProcedure) { // TODO: i.Output?.Count() -> Implement a Property "IsScalar" and "IsJson" return(storedProcedure.ReadWriteKind == ReadWriteKindEnum.Read && storedProcedure.Output?.Count() == 1); }