//测试是否能连接并执行
public string CheckCanExecute(string ObjectType, string ObjectName)
{
string result = string.Empty;
try
{
DataTable dt = null;
string sql = string.Empty;
if (ObjectType == "TABLE" || ObjectType == "VIEW")
{
sql = "select top 1 * from " + ObjectName;
//sql = "SELECT column_name as name,ordinal_position,data_type,data_type+';'+column_name as cv FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + ObjectName + "' ";
}
if (ObjectType == "PROC")
{
List <SqlParameter> parameterlist = new List <SqlParameter>();
SqlParameter[] parameterss = null;
sql = "select colid,name from syscolumns where id in (select id from sysobjects where xtype='p' and name=N'" + ObjectName + "') order by colid ";
DataTable dvlist = dbhelper.ExecuteTable(CommandType.Text, sql, null);//首先获取存储过程中所有参数
if (dvlist != null && dvlist.Rows.Count > 0)
{
foreach (DataRow dr in dvlist.Rows)
{
SqlParameter sp = new SqlParameter(dr["name"].ToString(), DBNull.Value);
parameterlist.Add(sp);
}
}
if (parameterlist.Count > 0)
{
parameterss = parameterlist.ToArray();
}
DataTable dtt = dbhelper.ExecuteTable(CommandType.StoredProcedure, ObjectName, parameterss);
DataTable NewT = new DataTable();
NewT.Columns.Add("name", Type.GetType("System.String"));
NewT.Columns.Add("data_type", Type.GetType("System.String"));
NewT.Columns.Add("cv", Type.GetType("System.String"));
foreach (DataColumn dc in dtt.Columns)
{
string dtype = dc.DataType.ToString().ToLower().Replace("system.", "");
DataRow dr = NewT.NewRow();
dr["name"] = dc.ColumnName;
dr["data_type"] = dtype;
dr["cv"] = dtype + ";" + dc.ColumnName;
NewT.Rows.Add(dr);
}
dt = NewT;
}
else
{
dt = dbhelper.ExecuteTable(CommandType.Text, sql, null);
}
}catch (Exception ex) {
BaseComponent.Error("ConnectionConfigComponent-CheckCanExecute出错：" + ex.Message);
result = ex.Message;
}
return(result);
}
private void AddOUUsers(List <AdModel> list, SPWeb web, string groupName)
{
foreach (AdModel user in list)
{
string userName = string.Empty;
try
{
userName = user.LoginName;
AddUserToGroup(web, groupName, userName);
}
catch (Exception ex)
{
BaseComponent.Error("向" + groupName + "添加用户：" + userName + "失败。" + ex.Message);
continue;
}
}
}
/// <summary>
/// 添加Group
/// </summary>
/// <param name="list"></param>
/// <param name="siteUrl"></param>
/// <param name="currentUser"></param>
private void AddGroupByOU(List <AdModel> list, string siteUrl)
{
List <AdModel> listOU = list.FindAll(d => d.TypeId == (int)ADHelper.TypeEnum.OU);
if (listOU.Count > 0)
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.RootWeb)
{
foreach (AdModel ou in listOU)
{
string groupName = string.Empty;
try
{
groupName = ou.Name;
string ouGuid = ou.Id;
//新增Group
AddGroupRole(web, groupName, ouGuid);
List <AdModel> listUser = list.FindAll(d => d.TypeId == (int)ADHelper.TypeEnum.USER && d.ParentId == ouGuid);
//新增该Group下的人员
AddOUUsers(listUser, web, groupName);
}
catch (Exception ex)
{
BaseComponent.Error("添加Group失败，组名：" + groupName + " " + ex.Message);
continue;
}
}
}
}
});
}
}
public bool UpdateTemplateStatus(string templateID)
{
bool result = true;
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update BS_TEMPLATE_MAIN set TemplateStatus='FREE'");
strSql.Append(" where TemplateID=@TemplateID and TemplateStatus!='ENABLE' ");
SqlParameter[] parameters =
{
new SqlParameter("@TemplateID", SqlDbType.NVarChar, 50)
};
parameters[0].Value = templateID;
int obj = dbhelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
}
catch (Exception ee)
{
result = false;
BaseComponent.Error(ee.Message);
}
return(result);
}
public Company GetCompanyInfo(string loginName)
{
Company company = new Company();
if (loginName == "")
{
return(company);
}
loginName = loginName.Split('\\')[1];
var de = ADHelper.GetDirectoryEntryByAccount(loginName);
if (de == null)
{
BaseComponent.Error("LoginName:" + loginName + " cannot find in AD");
return(company);
}
//TODO:
company.Costomer = ADHelper.GetProperty(de, "company");
//company.linkMan = ADHelper.GetProperty(de, "givenName");
company.phone = ADHelper.GetProperty(de, "mobile");
return(company);
}
/// <summary>
/// 检查Excel数据的必填字段和唯一性验证
/// </summary>
/// <param name="dtData"></param>
/// <returns></returns>
private string CheckData(DataTable dtData)
{
try
{
string checkOnlyResult = string.Empty;
string checkEmptyResult = string.Empty;
string emptyRow = string.Empty;
string emptyColumnName = string.Empty;
string notOnlyRow = string.Empty;
List <BS_CODEMAPPING> listCodeMapping = new List <BS_CODEMAPPING>();
for (int rowNum = dtData.Rows.Count - 1; rowNum >= 0; rowNum--)
{
emptyColumnName = "";
BS_CODEMAPPING codeMapping = new BS_CODEMAPPING();
codeMapping.CustomerID = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.CustomerID)]).Trim();
if (codeMapping.CustomerID.Contains("注"))
{
dtData.Rows[rowNum].Delete();
continue;
}
codeMapping.SemanticDesc = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.SemanticDesc)]).Trim();
codeMapping.BusinessCode = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.BusinessCode)]).Trim();
codeMapping.TargetCode = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.TargetCode)]).Trim();
codeMapping.CMICTCode = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.CMICTCode)]).Trim();
string startDate = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.StartDate)]).Trim();
string ExpireDate = Convert.ToString(dtData.Rows[rowNum][GetEnumDescription(ConstantClass.CodeMappingColumn.ExpireDate)]).Trim();
if (string.IsNullOrWhiteSpace(startDate))
{
codeMapping.StartDate = DateTime.MinValue.Date;
//codeMapping.StartDate = Convert.ToDateTime("1753/1/1 00:00:00");
}
else
{
if (startDate.Length < 10)
{
startDate = startDate + " 00:00:00";
}
codeMapping.StartDate = Convert.ToDateTime(startDate);
}
if (string.IsNullOrWhiteSpace(ExpireDate))
{
codeMapping.ExpireDate = DateTime.MaxValue.Date;
}
else
{
if (ExpireDate.Length < 10)
{
ExpireDate = ExpireDate + " 00:00:00";
}
codeMapping.ExpireDate = Convert.ToDateTime(ExpireDate);
}
if (string.IsNullOrWhiteSpace(codeMapping.CustomerID) || string.IsNullOrWhiteSpace(codeMapping.TargetCode) ||
string.IsNullOrWhiteSpace(codeMapping.CMICTCode))
{
emptyRow = Convert.ToString(rowNum);
}
if (string.IsNullOrWhiteSpace(codeMapping.CustomerID))
{
emptyColumnName = emptyColumnName + "，" + GetEnumDescription(ConstantClass.CodeMappingColumn.CustomerID);
}
if (string.IsNullOrWhiteSpace(codeMapping.TargetCode))
{
emptyColumnName = emptyColumnName + "，" + GetEnumDescription(ConstantClass.CodeMappingColumn.TargetCode);
}
if (string.IsNullOrWhiteSpace(codeMapping.CMICTCode))
{
emptyColumnName = emptyColumnName + "，" + GetEnumDescription(ConstantClass.CodeMappingColumn.CMICTCode);
}
//唯一性检查
bool isOnly = CheckCodeMappingOnly(codeMapping, false);
if (!isOnly)
{
notOnlyRow = rowNum + "," + notOnlyRow;
}
if (emptyRow.Length > 0)
{
if (emptyColumnName.Length > 0)
{
emptyColumnName = emptyColumnName.Substring(1);
}
checkEmptyResult = checkEmptyResult + "\n第" + emptyRow + "行," + emptyColumnName + "为空，请修正后上传！";
}
}
if (notOnlyRow.Length > 0)
{
notOnlyRow = notOnlyRow.Substring(0, notOnlyRow.Length - 1);
}
//if (emptyRow.Length > 0)
//{
// emptyRow = emptyRow.Substring(1);
//}
//if (!string.IsNullOrWhiteSpace(emptyRow))
//{
// checkEmptyResult = "第" + emptyRow + "行," + emptyColumnName + "为空，请修正后上传！";
//}
if (!string.IsNullOrWhiteSpace(notOnlyRow))
{
checkOnlyResult = "第" + notOnlyRow + "行的唯一性索引冲突，请修正后上传！";
}
if (string.IsNullOrWhiteSpace(checkOnlyResult))
{
return(checkEmptyResult);
}
else
{
return(checkOnlyResult);
}
}
catch (Exception ex)
{
BaseComponent.Error("代码映射批量导入，文件导入： " + ex.Message + "--------" + ex.StackTrace);
return("请按照系统提供的模板进行上传。");
}
}
private static string serverDir = ConfigurationManager.AppSettings["serverDir"].ToString(); //上传文件夹
//filename 为本地文件的绝对路径
//serverDir为服务器上的目录
public static bool Upload(string filename)
{
bool result = true;
try
{
FileInfo fileInf = new FileInfo(filename);
string uri = string.Empty;
if (!string.IsNullOrEmpty(serverDir))
{
uri = string.Format("ftp://{0}/{1}/{2}", ftpServerIP, serverDir, fileInf.Name);
}
else
{
uri = string.Format("ftp://{0}/{1}", ftpServerIP, fileInf.Name);
}
FtpWebRequest reqFTP;
// 根据uri创建FtpWebRequest对象
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(uri));
// ftp用户名和密码
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
// 默认为true，连接不会被关闭
// 在一个命令之后被执行
reqFTP.KeepAlive = false;
// 指定执行什么命令
reqFTP.Method = WebRequestMethods.Ftp.UploadFile;
// 指定数据传输类型
reqFTP.UseBinary = true;
// 上传文件时通知服务器文件的大小
reqFTP.ContentLength = fileInf.Length;
// 缓冲大小设置为2kb
int buffLength = 2048;
byte[] buff = new byte[buffLength];
int contentLen;
// 打开一个文件流 (System.IO.FileStream) 去读上传的文件
FileStream fs = fileInf.OpenRead();
// 把上传的文件写入流
Stream strm = reqFTP.GetRequestStream();
// 每次读文件流的2kb
contentLen = fs.Read(buff, 0, buffLength);
// 流内容没有结束
while (contentLen != 0)
{
// 把内容从file stream 写入 upload stream
strm.Write(buff, 0, contentLen);
contentLen = fs.Read(buff, 0, buffLength);
}
// 关闭两个流
strm.Close();
fs.Close();
}
catch (Exception ex)
{
BaseComponent.Error("上传到ftp错误：" + ex.Message);
result = false;
// MessageBox.Show(ex.Message, "Upload Error");
//Response.Write("Upload Error：" + ex.Message);
}
return(result);
}