Friday, December 13, 2024
Jquery unobtrusive validation with ajax
1 View
@model MesMvc.Areas.Resource.Models.EventModel
@{
ViewData["Title"] = "Create";
}
<section class="content row">
<div class="col-md-6 col-lg-6">
<div class="box box-primary">
<div class="box-body">
@using (Html.BeginForm("Create", "Event", FormMethod.Post, new { id = "createform" }))
{
@Html.AntiForgeryToken()
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.PlantCode)
@Html.DropDownListFor(model => model.PlantCode, ViewBag.PlantCodesList as IEnumerable<SelectListItem>)
</div>
<div class="form-group">
@Html.LabelFor(model => model.ProductionCell)
@Html.DropDownListFor(model => model.ProductionCell, ViewBag.ProductionCellsList as IEnumerable<SelectListItem>)
</div>
<div class="form-group">
@Html.LabelFor(model => model.EventName)
@Html.EditorFor(model => model.EventName, new { htmlAttributes = new { @class = "form-control"} })
@Html.ValidationMessageFor(model => model.EventName, "", new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(model => model.EventClass)
@Html.DropDownListFor(model => model.EventClass, ViewBag.EventClassesList as IEnumerable<SelectListItem>)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Description)
@Html.EditorFor(model => model.Description, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Description, "", new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(model => model.Optional1)
@Html.EditorFor(model => model.Optional1, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Optional1, "", new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(model => model.Optional2)
@Html.EditorFor(model => model.Optional2, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Optional2, "", new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(model => model.Optional3)
@Html.EditorFor(model => model.Optional3, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Optional3, "", new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(model => model.Optional4)
@Html.EditorFor(model => model.Optional4, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Optional4, "", new { @class = "text-danger" })
</div>
<div class="row">
<div class="col-md-4 col-lg-4">
<button type="button" class="button button-pill button-raised button-primary btn btn-primary" onclick="tabPostUrl($(currentTabContentId + ' form').serialize(), '/resource/event/create', '/resource/event/getall');">保存</button>
</div>
<div class="col-md-offset-4 col-md-4 col-lg-offset-4 col-lg-4">
<!--<button class="button button-pill button-raised button-primary btn btn-primary" type="button" onclick="location.href='@Url.Action("GetAll")'">取消</button>-->
<button class="button button-pill button-raised button-primary btn btn-primary" type="button" onclick="tabLoadUrl('', '/resource/event/getall');">取消</button>
</div>
</div>
}
</div>
</div>
</div>
</section>
<script src="~/lib/jquery/dist/jquery.js"></script>
<script src="~/lib/jquery-validation/dist/jquery.validate.js"></script>
<script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.js"></script>
<script>
function tabPostUrl(parms, url, redirectToUrl) {
//alert($form);
$.validator.unobtrusive.parse($(currentTabContentId + ' form'));
$(currentTabContentId + ' form').validate();
if ($(currentTabContentId + ' form').valid()) {
$.ajax({
'url': url,
'type': 'POST',
'data': parms,
'success': function (result) {
tabLoadUrl('', redirectToUrl);
},
error: function (data, status, e) {
alert("更新内容时发生错误。\n" + data.status);
}
});
} else {
$.each($(currentTabContentId + ' form').validate().errorList, function (key, value) {
$errorSpan = $("span[data-valmsg-for='" + value.element.id + "']");
$errorSpan.html("<span style='color:red'>" + value.message + "</span>");
$errorSpan.show();
});
}
}
</script>
2 Model
using System.ComponentModel.DataAnnotations;
using MesMvc.Models;
namespace MesMvc.Areas.Resource.Models
{
public class EventModel : BaseModel
{
public EventModel() { }
[Required]
[StringLength(10, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 1)]
[Display(Name = "Plant Code")]
public string PlantCode { get; set; }
[Required]
[StringLength(20, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 1)]
[Display(Name = "Production Cell")]
public string ProductionCell { get; set; }
[Required]
[StringLength(50, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 1)]
[Display(Name = "Event Name")]
public string EventName { get; set; }
[Display(Name = "Event Class")]
public string EventClass { get; set; }
[StringLength(100, ErrorMessage = "The {0} must be at max {1} characters long.")]
[Display(Name = "Description")]
public string Description { get; set; }
[StringLength(20, ErrorMessage = "The {0} must be at max {1} characters long.")]
[Display(Name = "Optional 1")]
public string Optional1 { get; set; }
[StringLength(20, ErrorMessage = "The {0} must be at max {1} characters long.")]
[Display(Name = "Optional 2")]
public string Optional2 { get; set; }
[StringLength(20, ErrorMessage = "The {0} must be at max {1} characters long.")]
[Display(Name = "Optional 3")]
public string Optional3 { get; set; }
[StringLength(20, ErrorMessage = "The {0} must be at max {1} characters long.")]
[Display(Name = "Optional 4")]
public string Optional4 { get; set; }
}
}
Call to .net core web api
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using Newtonsoft.Json;
namespace MesMvc.Util
{
public static class WebApiClient<T>
{
public static T Query(string url)
{
T entity = default(T);
Func<string, HttpClient, StringContent, HttpResponseMessage> access = (aurl, client, content) =>
{
return client.GetAsync(aurl).Result;
};
var response = DelegateAccess(url, default(T), access);
if (response.IsSuccessStatusCode)
{
string json = response.Content.ReadAsStringAsync().Result;
entity = JsonConvert.DeserializeObject<T>(json);
}
else
{
Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
}
return entity;
}
public static bool Update(string url, T value)
{
Func<string, HttpClient, StringContent, HttpResponseMessage> access = (aurl, client, content) =>
{
return client.PutAsync(aurl, content).Result;
};
return AccessApi(url, value, access);
}
public static bool Create(string url, T value)
{
Func<string, HttpClient, StringContent, HttpResponseMessage> access = (aurl, client, content) =>
{
return client.PostAsync(aurl, content).Result;
};
return AccessApi(url, value, access);
}
public static bool Delete(string url)
{
Func<string, HttpClient, StringContent, HttpResponseMessage> access = (aurl, client, content) =>
{
return client.DeleteAsync(aurl).Result;
};
return AccessApi(url, default(T), access);
}
private static HttpResponseMessage DelegateAccess(string url, T value, Func<string, HttpClient, StringContent, HttpResponseMessage> func)
{
HttpClient client = new HttpClient();
StringContent httpContent = GetContent(value);
var response = func(url, client, httpContent);
return response;
}
private static bool AccessApi(string url, T value, Func<string, HttpClient, StringContent, HttpResponseMessage> func)
{
var response = DelegateAccess(url, value, func);
return IsResponseOk(response);
}
private static StringContent GetContent(T value)
{
var json = JsonConvert.SerializeObject(value);
var httpContent = new StringContent(json, Encoding.UTF8, "application/json");
return httpContent;
}
private static bool IsResponseOk(HttpResponseMessage response)
{
if (response.IsSuccessStatusCode)
{
return true;
}
else
{
return false;
}
}
}
}
MVC controller:
using System;
using System.Collections.Generic;
using System.Linq;
using MesMvc.Models;
using MesMvc.Util;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
namespace MesMvc.Controllers
{
[Authorize]
[Route("[controller]/[action]")]
public class EventController : BaseController
{
public static string UrlSection = "BusinessUrl:Event";
public string ServiceUrl { private set; get; }
public EventController(IConfiguration configuration) : base(configuration) {
ServiceUrl = Configuration[UrlSection];
}
[HttpGet("/Event")]
public IActionResult GetAll(string sortOrder, string currentFilter, string searchString, int? page, int? PageSize)
{
ViewData["IsCellUser"] = IsCellUser;
ViewData["CurrentSort"] = sortOrder;
ViewData["PlantCodeSortParm"] = sortOrder == "PlantCode" ? "PlantCode_desc" : "PlantCode";
ViewData["ProductionCellSortParm"] = sortOrder == "ProductionCell" ? "ProductionCell_desc" : "ProductionCell";
ViewData["EventNameSortParm"] = sortOrder == "EventName" ? "EventName_desc" : "EventName";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
IEnumerable<EventModel> events = WebApiClient<IEnumerable<EventModel>>.Query(ServiceUrl);
if (!String.IsNullOrEmpty(searchString))
{
events = events.Where(e => !string.IsNullOrEmpty(e.Description) && e.Description.Contains(searchString));
}
switch (sortOrder)
{
case "PlantCode":
events = events.OrderBy(e => e.PlantCode);
break;
case "PlantCode_desc":
events = events.OrderByDescending(e => e.PlantCode);
break;
case "ProductionCell":
events = events.OrderBy(e => e.ProductionCell);
break;
case "ProductionCell_desc":
events = events.OrderByDescending(e => e.ProductionCell);
break;
case "EventName":
events = events.OrderBy(e => e.EventName);
break;
case "EventName_desc":
events = events.OrderByDescending(e => e.EventName);
break;
}
return View(PaginatedList<EventModel>.Create(events, page ?? 1, PageSize ?? 5));
}
[HttpGet("/Event/Get")]
public IActionResult GetById([FromQuery] string plantCode, [FromQuery] string productionCell, [FromQuery] string eventName)
{
string url = string.Format("{0}/{1}?productionCell={2}&eventName={3}", ServiceUrl, plantCode, productionCell, eventName);
EventModel item = WebApiClient<EventModel>.Query(url);
return View(item);
}
[HttpGet("/Event/Create")]
public IActionResult Create()
{
ViewBag.Title = "事件";
ViewBag.SubTitle = "事件-创建";
// ViewBag.EventClassesList = LoadEventClasses();
//ViewBag.PlantCodesList = LoadPlantCodes();
//ViewBag.ProductionCellsList = LoadProductionCells();
return View();
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind("PlantCode,ProductionCell,EventName,EventClass,Description,Optional1,Optional2,Optional3,Optional4")] EventModel item)
{
if (ModelState.IsValid)
{
WebApiClient<EventModel>.Create(ServiceUrl, item);
return Redirect("/Event");
}
//数据模型验证不通过，重新加载页面
ViewBag.Title = "事件";
ViewBag.SubTitle = "事件-创建";
//ViewBag.EventClassesList = LoadEventClasses();
//ViewBag.PlantCodesList = LoadPlantCodes();
//ViewBag.ProductionCellsList = LoadProductionCells();
return View(item);
}
[HttpGet("/Event/Update")]
public IActionResult Update(string PlantCode, string ProductionCell, string EventName)
{
ViewBag.Title = "事件";
ViewBag.SubTitle = "事件-修改";
string url = string.Format("{0}/{1}?productionCell={2}&eventName={3}", ServiceUrl, PlantCode, ProductionCell, EventName);
EventModel item = WebApiClient<EventModel>.Query(url);
if (item == null)
{
return NotFound();
}
//ViewBag.EventClassesList = LoadEventClasses();
// ViewBag.PlantCodesList = LoadPlantCodes();
//ViewBag.ProductionCellsList = LoadProductionCells();
return View(item);
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Update([Bind("PlantCode,ProductionCell,EventName,EventClass,Description,Optional1,Optional2,Optional3,Optional4,RowVersion")] EventModel item)
{
ViewBag.Title = "事件";
ViewBag.SubTitle = "事件-修改";
if (ModelState.IsValid)
{
WebApiClient<EventModel>.Update(ServiceUrl, item);
return Redirect("/Event");
}
//ViewBag.EventClassesList = LoadEventClasses();
//ViewBag.PlantCodesList = LoadPlantCodes();
//ViewBag.ProductionCellsList = LoadProductionCells();
return View(item);
}
[HttpGet("/Event/Delete")]
public IActionResult Delete(string PlantCode, string ProductionCell, string EventName)
{
ViewBag.Title = "事件";
ViewBag.SubTitle = "事件-删除";
string url = string.Format("{0}/{1}?productionCell={2}&eventName={3}", ServiceUrl, PlantCode, ProductionCell, EventName);
EventModel item = WebApiClient<EventModel>.Query(url);
if (item == null)
{
return NotFound();
}
return View(item);
}
// POST: EventClass/Delete
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(string PlantCode, string ProductionCell, string EventName)
{
string url = string.Format("{0}/{1}?productionCell={2}&eventName={3}", ServiceUrl, PlantCode, ProductionCell, EventName);
WebApiClient<EventModel>.Delete(url);
return Redirect("/Event");
}
}
}
Web API Controller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MesWebApi.Business;
using MesWebApi.Models;
using MesWebApi.Util;
using Microsoft.AspNetCore.Mvc;
// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
namespace MesWebApi.Controllers
{
[Route("api/[controller]")]
public class EventController : Controller
{
IBusiness<EventModel> _business;
public EventController(IBusiness<EventModel> business)
{
_business = business;
}
[HttpGet]
public IEnumerable<EventModel> GetAll()
{
return _business.GetAll();
}
[HttpGet("{plantCode}", Name = "GetEvent")]
public IActionResult GetById(string plantCode, [FromQuery] string productionCell, [FromQuery] string eventName)
{
var item = _business.GetById(plantCode, productionCell, eventName);
if (item == null)
{
return NotFound();
}
return new ObjectResult(item);
}
[HttpPost]
public IActionResult Create([FromBody] EventModel item)
{
if (item == null)
{
return BadRequest();
}
_business.Create(item);
return CreatedAtRoute("GetEvent", new { plantCode = item.PlantCode, productionCell = item.ProductionCell, eventName = item.EventName }, item);
}
[HttpPut]
public IActionResult Update([FromBody] EventModel item)
{
if (item == null)
{
return BadRequest();
}
bool isFound = _business.Update(item);
if (!isFound)
{
return NotFound();
}
return new NoContentResult();
}
[HttpDelete("{plantCode}")]
public IActionResult Delete(string plantCode, [FromQuery] string productionCell, [FromQuery] string eventName)
{
bool isFound = _business.Delete(plantCode, productionCell, eventName);
if (!isFound)
{
return NotFound();
}
return new NoContentResult();
}
}
}
MVC5 Page embeded by partial view
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace AdminMES.Controllers
{
public class CellController : Controller
{
// GET: Cell
[AllowAnonymous]
public ActionResult Index()
{
ViewBag.SubPage = "Content";
return View();
//return PartialView("Content");
//return RedirectToAction("Content", "Cell");
}
[AllowAnonymous]
[HttpPost]
public ActionResult Index(string subPage)
{
ViewBag.SubPage = subPage;
return View();
}
[AllowAnonymous]
[HttpPost]
public ActionResult Index2(string subPage)
{
return PartialView(subPage);
//return View(subPage);
//return RedirectToAction("Index", "Cell", subPage);
}
// GET: Cell
[AllowAnonymous]
public ActionResult Report()
{
ViewBag.SubPage3 = "a";
return View();
}
[AllowAnonymous]
[HttpPost]
public ActionResult Report2(string subPage)
{
return PartialView(subPage);
}
}
}
Index.cshtml:
@using AdminMES.Models
@model UserIndexViewModel
@{
ViewBag.Title = "生产单元";
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
<title>生产单元</title>
@Scripts.Render("~/bundles/jquery/js")
@Styles.Render("~/bundles/PagedList/css")
@Styles.Render("~/bundles/datatables/css")
@Scripts.Render("~/bundles/datatables/js")
@Styles.Render("~/bundles/bootstrap/css")
@Styles.Render("~/bundles/AdminLTE/css")
@Styles.Render("~/bundles/AdminLTE/skins/css")
@Styles.Render("~/bundles/fakeLoader/css")
@Scripts.Render("~/bundles/fakeLoader/js")
</head>
<body class="hold-transition">
<div id="a" class="ui-toolbar btn-toolbar container-fluid" role="toolbar">
<div style="float:left;">
<button onclick="updatePage('Content')" class="btn btn-flat" style="background-color:lightgreen;height:4em;text-align:left;"><img src="../Icon.png" style="height:3em;"> 主页</button>
<button onclick="updatePage('b')" class="btn btn-flat" style="background-color:lightgreen;height:4em">多功能</button>
<button src="#" onclick="updatePage('b')" class="btn btn-flat" style="background-color:lightgreen;text-align:left;height:4em"><span>用户：三体<br />三体用户 12:00:00</span></button>
<button src="#" onclick="location.href='@Url.Action("index","CellController")'" class="btn btn-flat" style="background-color:lightgreen;height:4em">工具</button>
<button src="#" onclick="getNewPage('b');" class="btn btn-flat" style="background-color:lightgreen;height:4em">帮助</button>
</div>
<div style="float:right;">
<div class="row">
<div class="col-xs" style="background-color:lightgreen"><label>用户：三体</label><img src="#" />图片</div>
</div>
<div class="row">
<div class="col-xs" style="background-color:lightgreen"><label>三体用户</label></div>
</div>
</div>
</div>
<div id="content" style="border:solid 1px black">
@Html.Partial((string)ViewBag.SubPage)
</div>
<script type="text/javascript">
function updatePage(subPage) {
$.ajax({
type: "POST",
async: true,
data: { subPage: subPage },
url: "@UrlHelper.GenerateContentUrl("~/cell/Index2", Context)",
success: function (data) {
$("#content").html(data);
},
error: function (data, status, e) {
alert("更新内容时发生错误。\n" + data.status);
}
});
}
</script>
</body>
</html>
Content.cshtml:
@using AdminMES.Models
@model UserIndexViewModel
@{
ViewBag.Title = "生产单元";
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
<title>生产单元</title>
@Scripts.Render("~/bundles/jquery/js")
@Styles.Render("~/bundles/PagedList/css")
@Styles.Render("~/bundles/datatables/css")
@Scripts.Render("~/bundles/datatables/js")
@Styles.Render("~/bundles/bootstrap/css")
@Styles.Render("~/bundles/AdminLTE/css")
@Styles.Render("~/bundles/AdminLTE/skins/css")
@Styles.Render("~/bundles/fakeLoader/css")
@Scripts.Render("~/bundles/fakeLoader/js")
</head>
<body class="hold-transition">
<div id="buttons" float="left">
<button onclick="updatePage2('a')" class="btn btn-flat">班次</button>
<button onclick="updatePage2('b')" class="btn btn-flat">班组</button>
<button src="#" onclick="updatePage2('a')" class="btn btn-flat">排产</button>
<button src="#" onclick="updatePage2('b')" class="btn btn-flat">维护和安全</button>
<button src="#" onclick="updatePage2('a');" class="btn btn-flat">消息</button>
<button src="#" onclick="updatePage2('b');" class="btn btn-flat">单元设备</button>
<button src="#" onclick="updatePage2('a');" class="btn btn-flat">暂停</button>
<button src="#" onclick="updatePage2('b');" class="btn btn-flat">事件</button>
</div>
<div id="report" style="border:solid 1px black">
@if (ViewBag.SubPage2 == null)
{
ViewBag.SubPage2 = "Report";
}
@Html.Partial((string)ViewBag.SubPage2)
</div>
<script type="text/javascript">
function updatePage2(subPage) {
$.ajax({
type: "POST",
async: true,
data: { subPage: subPage },
url: "@UrlHelper.GenerateContentUrl("~/cell/Index2", Context)",
success: function (data) {
$("#content").html(data);
},
error: function (data, status, e) {
alert("更新内容时发生错误。\n" + data.status);
}
});
}
</script>
</body>
</html>
Report.cshtml:
@using AdminMES.Models
@model UserIndexViewModel
@{
ViewBag.Title = "生产单元";
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
<title>报表</title>
@Scripts.Render("~/bundles/jquery/js")
@Styles.Render("~/bundles/PagedList/css")
@Styles.Render("~/bundles/datatables/css")
@Scripts.Render("~/bundles/datatables/js")
@Styles.Render("~/bundles/bootstrap/css")
@Styles.Render("~/bundles/AdminLTE/css")
@Styles.Render("~/bundles/AdminLTE/skins/css")
@Styles.Render("~/bundles/fakeLoader/css")
@Scripts.Render("~/bundles/fakeLoader/js")
</head>
<body class="hold-transition">
<div id="reporttext" style="border:solid 1px black">
</div>
<div id="buttons">
<button src="#" onclick="updatePage3('a');" class="btn btn-flat">Report 1</button>
<button src="#" onclick="updatePage3('b');" class="btn btn-flat">Report 2</button>
</div>
<script type="text/javascript">
function updatePage3(subPage) {
$.ajax({
type: "POST",
async: true,
data: { subPage: subPage },
url: "@UrlHelper.GenerateContentUrl("~/cell/Report2", Context)",
success: function (data) {
$("#reporttext").html(data);
},
error: function (data, status, e) {
alert("更新内容时发生错误。\n" + data.status);
}
});
}
updatePage3('a');
</script>
</body>
</html>
a.cshtml:
这是视图A
b.cshtml:
这是视图B
sp_MSforeachdb and sp_msforeachTable
EXECUTE sp_MSforeachdb
'IF ''?'' IN (SELECT DISTINCT CurrentDB FROM #TempServerInfo)
BEGIN
USE [?]
SELECT DISTINCT
so.name AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS Fragmentation
, ISNULL(sp.rows_sampled, 0) AS RowsSampled
INTO #ObjectNames
FROM sys.dm_db_index_physical_stats ((SELECT database_id FROM sys.databases WHERE name = ''?''), NULL, NULL, NULL, NULL) AS s
JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
JOIN sys.objects AS so ON s.object_id = so.object_id
JOIN sys.schemas sch ON sch.schema_id = so.schema_id
JOIN sys.stats AS stat ON s.object_id = stat.object_id AND stat.name = i.name
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE i.name like N''PK%'' AND alloc_unit_type_desc = ''IN_ROW_DATA'' AND sch.name = ''dbo''
AND s.partition_number = 1
ORDER BY TableName
INSERT INTO #TableStatistics (TableName, RowCounter, ReservedSize, DataSize, IndexSize, UnusedSize)
EXEC sp_msforeachTable @replacechar = ''#'', @Command1="sp_spaceused ''#''", @whereand="and o.name in (select TableName from #ObjectNames)"
UPDATE #TableStatistics
SET #TableStatistics.IndexName = o.IndexName
, #TableStatistics.RowsSampled = o.RowsSampled
, #TableStatistics.Fragmentation = o.Fragmentation
, #TableStatistics.DeltaRows = ABS(#TableStatistics.RowCounter - o.RowsSampled)
, #TableStatistics.DatabaseName = ''?''
FROM #ObjectNames o
WHERE #TableStatistics.TableName = o.TableName
END'
if the table exists when the batch is compiled, SQL Server will flag an error for a missing column, and this happens before your IF condition is evaluated. You need to nest the SELECT in EXEC() to push it to a deeper scope:
EXECUTE sp_msforeachdb 'USE [?]
IF (''?'' LIKE ''Tran%'' and len(''?'') = 8 and DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE'')
Begin
DECLARE @var varchar(20) = ''saurabh''
EXEC sp_executesql N''select Value from [?].DBO.intranet Where name = @var'',
N''@var varchar(20)'', @var
End'
An example to operate VSS from C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.SourceSafe.Interop;
using System.Configuration;
using System.IO;
using System.Collections;
using Infor.SyteLine.FormRename.Configuration;
using System.Data;
using System.Diagnostics;
namespace Infor.SyteLine.FormRename.BusinessService
{
/// <summary>
/// VssLatestVersionLoader to load the latest version from VSS.
/// </summary>
internal class VisualSourceSafeService : IBusinessService
{
private static VssConfigurationSection section = ConfigurationUtil.GetCustomerSection(VssConfigurationSection.Name) as VssConfigurationSection;
public static string AppRootDir { get { return section.LocalFolder + "\\SLSI9.00App"; } }
public static string ObjRootDir { get { return section.LocalFolder + "\\SLSI9.00Obj"; } }
public static string RootDir { get { return section.LocalFolder; } }
public string ExecutablePath { get; set; }
private IDictionary vssPathSettings;
private IVSSDatabase vssAppDatabase;
private IVSSDatabase vssObjDatabase;
private string originalHold;
private string newHold;
public VisualSourceSafeService()
{
vssPathSettings = ConfigurationUtil.GetSection("vssPathSettings");
}
public void Open()
{
vssAppDatabase = new VSSDatabase();
vssAppDatabase.Open(section.AppPath, section.User, section.Password);
vssObjDatabase = new VSSDatabase();
vssObjDatabase.Open(section.ObjPath, section.User, section.Password);
}
private VSSItem GetVssItem(string spec)
{
VSSItem item = null;
string[] parts = spec.Split('|');
try
{
if (parts.Length < 2)
{
item = vssAppDatabase.get_VSSItem(parts[0], false);
}
else if (parts[0].Equals("App"))
{
item = vssAppDatabase.get_VSSItem(parts[1], false); // item = vssAppDatabase.get_VSSItem("App|$/ApplicationDB/Stored Procedures/2CurrCnvtSp.sp", false);
}
else
{
item = vssObjDatabase.get_VSSItem(parts[1], false);
}
}
catch (Exception)
{
if (parts.Length < 2)
{
item = vssObjDatabase.get_VSSItem(parts[0], false);
}
else if (parts[0].Equals("App"))
{
item = vssObjDatabase.get_VSSItem(parts[1], false);
}
else
{
item = vssAppDatabase.get_VSSItem(parts[1], false);
}
}
return item;
}
public void RenameItem(string newText, string vssPath)
{
VSSItem item = GetVssItem(vssPath);
//item.Type=0:项目文件夹 item.Type=1:项目文件
if (item.Type == 1)
{
if (item.IsCheckedOut == 0)
{
originalHold = item.Name;
newHold = newText;
item.Name = newText;
}
else
{
throw new Exception("The file has been locked. So rename failed.");
}
}
else
{
throw new Exception("Do you really want to rename the file directory?");
}
}
private string GetItemSpec(DataRow row)
{
string type = row["Type"] as string;
string path = vssPathSettings[type] as string;
string spec = null;
if ("SQL_STORED_PROCEDURE".Equals(type))
{
string name = row["Name"] + ".sp";
if (!string.IsNullOrEmpty(originalHold) && name.Equals(originalHold))
{
spec = path + newHold;
}
else {
spec = path + row["Name"] + ".sp";
}
}
return spec;
}
public int Commit(DataRow row, string comment)
{
string spec = GetItemSpec(row);
VSSItem item = GetVssItem(spec);
// item.IsCheckedOut=0:未签出 item.IsCheckedOut=1:被别人签出 item.IsCheckedOut=2:被自己签出
int status = item.IsCheckedOut;
if (item.IsCheckedOut == 2)
{
string local = item.Spec;
string localSpec = local.Replace("$", RootDir);
item.Checkin(comment, localSpec, 0);
}
return status;
}
public int Unlock(DataRow row)
{
string spec = GetItemSpec(row);
VSSItem item = GetVssItem(spec);
// item.IsCheckedOut=0:未签出 item.IsCheckedOut=1:被别人签出 item.IsCheckedOut=2:被自己签出
int status = item.IsCheckedOut;
if (item.IsCheckedOut == 2)
{
string local = item.Spec;
string localSpec = local.Replace("$", RootDir);
item.UndoCheckout(localSpec, 0);
}
return status;
}
public int Process(DataRow row, string originalText, string newText, string comment)
{
string spec = GetItemSpec(row);
VSSItem item = GetVssItem(spec);
// item.IsCheckedOut=0:未签出 item.IsCheckedOut=1:被别人签出 item.IsCheckedOut=2:被自己签出
int status = item.IsCheckedOut;
if (item.IsCheckedOut == 0)
{
string local = item.Spec;
string localSpec = local.Replace("$", RootDir);
int index = localSpec.LastIndexOf("/");
local = localSpec.Remove(index, localSpec.Length - index);
Directory.CreateDirectory(local);
item.Checkout(comment, localSpec, 0);
Replace(localSpec, originalText, newText);
}
return status;
}
private void Replace(string localSpec, string originalText, string newText)
{
string path = ExecutablePath + "\\Resource\\Replace.bat";
if (File.Exists(path))
{
string batch = string.Format("\"{0}\" \"{1}\" \"{2}\"", originalText, newText, localSpec.Replace(@"/", @"\"));
var process = new Process();
process.StartInfo.FileName = path;
process.StartInfo.UseShellExecute = false;
process.StartInfo.CreateNoWindow = true;
process.StartInfo.Arguments = batch;
process.Start();
process.WaitForExit();
if (!process.HasExited)
{
process.Kill();
}
}
else {
throw new Exception("Did you remove my local batch file Replace.bat?");
}
}
public void Close()
{
if (vssAppDatabase != null)
{
vssAppDatabase.Close();
}
if (vssObjDatabase != null)
{
vssObjDatabase.Close();
}
}
}
private static void Load(string vssIni, string rootDir)
{
string rootProject = section.VssProject;
string user = section.User;
string pwd = section.Password;
try
{
vssDatabase.Open(@vssIni, user, pwd);
GetFiles(rootProject, rootDir);
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("BackupApplication", "Error occured while opening database or connecting to the project"
+ ex.Message.ToString(), System.Diagnostics.EventLogEntryType.Error);
Console.WriteLine("Failed!");
}
finally
{
vssDatabase.Close();
}
}
private static void GetFiles(string projectPath, string copyDirectory)
{
// This is the variable for holding the reference to the VSS Item (or folders).
VSSItem item = vssDatabase.get_VSSItem(projectPath, false);
LoadItem(copyDirectory, item);
foreach (VSSItem vssItem in item.get_Items(false))
{
Console.WriteLine(vssItem.Spec.ToString());
if (vssItem.Type == (int)VSSItemType.VSSITEM_PROJECT)
{
string copyDirLocal = copyDirectory + "\\" + vssItem.Name.ToString();
//vssItem.Get(ref copyDirLocal, 0);
LoadItem(copyDirLocal, vssItem);
string childProjectPath = vssItem.Spec + "/";
GetFiles(childProjectPath, copyDirLocal);
}
}
}
private static void LoadItem(string copyDirectory, VSSItem item)
{
item.Get(ref copyDirectory, 0);
}
}
Subscribe to:
Posts (Atom)
