📄 customform.cs
字号:
info.itemname = rd["itemname"].ToString();
info.itemtype = (EnumCstmFrmItemType)Enum.Parse(typeof(EnumCstmFrmItemType), rd["itemtype"].ToString());
if (rd["defaultvalue"] != DBNull.Value) info.defaultvalue = rd["defaultvalue"].ToString();
info.isnotnull = Convert.ToBoolean(rd["isnotnull"].ToString());
if (rd["itemsize"] != DBNull.Value) info.itemsize = Convert.ToInt32(rd["itemsize"].ToString());
info.islock = Convert.ToBoolean(rd["islock"].ToString());
if (rd["prompt"] != DBNull.Value) info.prompt = rd["prompt"].ToString();
if (rd["selectitem"] != DBNull.Value) info.selectitem = rd["selectitem"].ToString();
flag = true;
}
rd.Close();
if (!flag)
throw new Exception("未找到相关的自定义表单项");
return info;
}
int ICustomForm.GetItemCount(int formid)
{
string Sql = "select count(id) from " + Pre + "customform_item where formid=" + formid;
return Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, Sql, null));
}
void ICustomForm.EditFormItem(CustomFormItemInfo info)
{
if (info.id > 0)
UpdateItem(info);
else
AddItem(info);
}
/// <summary>
/// 修改表单项
/// </summary>
/// <param name="info"></param>
protected void UpdateItem(CustomFormItemInfo info)
{
SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
cn.Open();
try
{
string Sql = "select count(id) from " + Pre + "customform_item where formid=" + info.formid + " and itemname=@itemname and id<>" + info.id;
SqlParameter Param1 = new SqlParameter("@itemname", info.itemname);
if (Convert.ToInt32(DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, Param1)) > 0)
throw new Exception("该自定义表单已存在相同的表单项名称");
#region 检查序号
string UpOldSql = string.Empty;
Sql = "select id from " + Pre + "customform_item where formid=" + info.formid + " and seriesnumber=" + info.seriesnumber;
object obj = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (obj != null && obj != DBNull.Value)
{
int oldsnid = Convert.ToInt32(obj);
Sql = "select seriesnumber from " + Pre + "customform_item where id=" + info.id;
object oldsn = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (oldsn != null && oldsn != DBNull.Value)
{
int nold = Convert.ToInt32(oldsn);
UpOldSql = "update " + Pre + "customform_item set seriesnumber=" + nold + " where id=" + oldsnid;
}
}
#endregion
SqlTransaction tran = cn.BeginTransaction();
try
{
Sql = "update " + Pre + "customform_item set seriesnumber=@seriesnumber,itemname=@itemname,";
Sql += "defaultvalue=@defaultvalue,isnotnull=@isnotnull,itemsize=@itemsize,islock=@islock,prompt=@prompt,";
Sql += "selectitem=@selectitem where id=" + info.id;
DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, GetItemEditParams(info));
if (UpOldSql != string.Empty)
DbHelper.ExecuteNonQuery(tran, CommandType.Text, UpOldSql, null);
tran.Commit();
}
catch
{
try
{
tran.Rollback();
}
catch
{ }
throw;
}
}
catch
{
throw;
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
/// <summary>
/// 新增表单项
/// </summary>
/// <param name="info"></param>
protected void AddItem(CustomFormItemInfo info)
{
SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
cn.Open();
try
{
#region 检查字段名是否重复
string Sql = "select count(id) from " + Pre + "customform_item where formid=" + info.formid + " and itemname=@itemname";
SqlParameter Param1 = new SqlParameter("@itemname", info.itemname);
if (Convert.ToInt32(DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, Param1)) > 0)
throw new Exception("该自定义表单已存在相同的表单项名称");
Sql = "select count(id) from " + Pre + "customform_item where formid=" + info.formid + " and fieldname=@fieldname";
SqlParameter Param2 = new SqlParameter("@fieldname", info.fieldname);
if (Convert.ToInt32(DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, Param2)) > 0)
throw new Exception("该自定义表单中已存在相同的字段名称");
#endregion
#region 检查字段名是否重复
#endregion
#region 获取数据表名
Sql = "select formtablename from " + Pre + "customform where id=" + info.formid;
object obj = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (obj == null)
throw new Exception("没有找到相关的表单记录");
else if (obj == DBNull.Value)
throw new Exception("相关的数据表名为空");
string tbnm = obj.ToString();
#endregion
#region 检查实际表中是否存在相同名称
bool flag = false;
Sql = "select * from " + tbnm + " where 1=0";
IDataReader rd = DbHelper.ExecuteReader(cn, CommandType.Text, Sql, null);
DataTable dt = rd.GetSchemaTable();
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.ToLower().Trim() == info.fieldname.ToLower().Trim())
{
flag = true;
break;
}
}
rd.Close();
dt.Dispose();
if (flag)
throw new Exception("该自定义表单的数据表中已存在相同的字段名称,或者您使用了系统默认的字段名");
#endregion
#region 检查数据表是否存在
Sql = "IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[" + tbnm + "]') AND ";
Sql += "OBJECTPROPERTY(id, N'IsUserTable') = 1) SELECT 1 ELSE SELECT 0";
obj = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (obj != null && obj != DBNull.Value)
{
if (obj.ToString() == "0")
{
throw new Exception("该自定义表单数据表不存在!");
}
}
else
{
throw new Exception("该自定义表单数据表不存在(null)!");
}
#endregion
#region 查看序列号是否已被占用
string UpOldSql = string.Empty;
Sql = "select id from " + Pre + "customform_item where formid=" + info.formid + " and seriesnumber=" + info.seriesnumber;
obj = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (obj != null && obj != DBNull.Value)
{
int oldsnid = Convert.ToInt32(obj);
Sql = "select max(seriesnumber) from " + Pre + "customform_item where formid=" + info.formid;
object maxid = DbHelper.ExecuteScalar(cn, CommandType.Text, Sql, null);
if (maxid != null && maxid != DBNull.Value)
{
int nmax = Convert.ToInt32(maxid);
nmax++;
UpOldSql = "update " + Pre + "customform_item set seriesnumber=" + nmax + " where id=" + oldsnid;
}
}
#endregion
SqlTransaction tran = cn.BeginTransaction();
try
{
Sql = "insert into " + Pre + "customform_item (seriesnumber,formid,fieldname,itemname,itemtype,defaultvalue,isnotnull,";
Sql += "itemsize,islock,prompt,selectitem,addtime) values (";
Sql += "@seriesnumber,@formid,@fieldname,@itemname,@itemtype,@defaultvalue,@isnotnull,";
Sql += "@itemsize,@islock,@prompt,@selectitem,'" + DateTime.Now + "')";
DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, GetItemEditParams(info));
if (UpOldSql != string.Empty)
DbHelper.ExecuteNonQuery(tran, CommandType.Text, UpOldSql, null);
string fldtype = "nvarchar(100)";
switch (info.itemtype)
{
case EnumCstmFrmItemType.Numberic:
fldtype = "numeric(10,4)";
break;
case EnumCstmFrmItemType.DateTime:
fldtype = "datetime";
break;
case EnumCstmFrmItemType.MultiLineText:
fldtype = "ntext";
break;
}
Sql = "ALTER TABLE [" + tbnm + "] ADD " + info.fieldname + " " + fldtype + " NULL";
DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, null);
tran.Commit();
}
catch
{
try
{
tran.Rollback();
}
catch
{ }
throw;
}
}
catch
{
throw;
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
}
protected SqlParameter[] GetItemEditParams(CustomFormItemInfo info)
{
SqlParameter[] Param = new SqlParameter[11];
Param[0] = new SqlParameter("@seriesnumber", SqlDbType.Int);
Param[0].Value = info.seriesnumber;
Param[1] = new SqlParameter("@formid", SqlDbType.Int);
Param[1].Value = info.formid;
Param[2] = new SqlParameter("@fieldname", SqlDbType.NVarChar, 50);
Param[2].Value = info.fieldname;
Param[3] = new SqlParameter("@itemname", SqlDbType.NVarChar, 50);
Param[3].Value = info.itemname;
Param[4] = new SqlParameter("@itemtype", SqlDbType.NVarChar, 50);
Param[4].Value = info.itemtype.ToString();
Param[5] = new SqlParameter("@defaultvalue", SqlDbType.NVarChar, 50);
if (info.defaultvalue.Trim() == string.Empty)
Param[5].Value = DBNull.Value;
else
Param[5].Value = info.defaultvalue;
Param[6] = new SqlParameter("@isnotnull", SqlDbType.Bit);
Param[6].Value = info.isnotnull;
Param[7] = new SqlParameter("@itemsize", SqlDbType.Int);
Param[7].Value = info.itemsize;
Param[8] = new SqlParameter("@islock", SqlDbType.Bit);
Param[8].Value = info.islock;
Param[9] = new SqlParameter("@prompt", SqlDbType.NVarChar, 255);
if (info.prompt.Trim() == string.Empty)
Param[9].Value = DBNull.Value;
else
Param[9].Value = info.prompt;
Param[10] = new SqlParameter("@selectitem", SqlDbType.NText);
if (info.selectitem.Trim() == string.Empty)
Param[10].Value = DBNull.Value;
else
Param[10].Value = info.selectitem;
return Param;
}
void ICustomForm.DeleteFormItem(int itemid)
{
SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
cn.Open();
try
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -