public void Update(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, DATA0011 data0011)
{
#region 创建语法
StringBuilder strSql = new StringBuilder();
strSql.Append("update DATA0011 set ");
strSql.Append("FILE_POINTER=@FILE_POINTER,");
strSql.Append("SOURCE_TYPE=@SOURCE_TYPE,");
strSql.Append("NOTE_PAD_LINE_1=@NOTE_PAD_LINE_1,");
strSql.Append("NOTE_PAD_LINE_2=@NOTE_PAD_LINE_2,");
strSql.Append("NOTE_PAD_LINE_3=@NOTE_PAD_LINE_3,");
strSql.Append("NOTE_PAD_LINE_4=@NOTE_PAD_LINE_4");
strSql.Append(" where RKEY=@RKEY ");
SqlParameter[] parameters =
{
new SqlParameter("@RKEY", SqlDbType.Decimal, 9),
new SqlParameter("@FILE_POINTER", SqlDbType.Decimal, 9),
new SqlParameter("@SOURCE_TYPE", SqlDbType.Decimal, 9),
new SqlParameter("@NOTE_PAD_LINE_1", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_2", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_3", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_4", SqlDbType.VarChar, 70)
};
parameters[0].Value = data0011.RKEY;
parameters[1].Value = data0011.FILE_POINTER;
parameters[2].Value = data0011.SOURCE_TYPE;
parameters[3].Value = data0011.NOTE_PAD_LINE_1;
parameters[4].Value = data0011.NOTE_PAD_LINE_2;
parameters[5].Value = data0011.NOTE_PAD_LINE_3;
parameters[6].Value = data0011.NOTE_PAD_LINE_4;
#endregion
#region 操作
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = strSql.ToString();
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
#endregion
}
public void BinData(string strWhere)
{
try
{
DATA0011 info = new DATA0011();
DATA0011BLL bll = new DATA0011BLL(Frm.DBConnection);
info = bll.getDATA0011ByRKEY(Frm.id_rkey);
if (info.RKEY > 0)
{
Frm.textBox.Text = info.NOTE_PAD_LINE_1.Trim() + info.NOTE_PAD_LINE_2.Trim() + info.NOTE_PAD_LINE_3.Trim() + info.NOTE_PAD_LINE_4.Trim();
}
else
{
Frm.textBox.Text = "";
}
if (Frm.is_memo.Trim().Length > 0)
{
Frm.textBox.Text = Frm.is_memo.Trim();
}
if (!Frm.ib_Edit)
{
Frm.textBox.ReadOnly = true;
Frm.buttonFind.Visible = false;
}
}
catch (Exception e1)
{
log.PrintInfo(e1);
return;
}
}
///<sumary>
/// 通过SQL语句获取数据对象
///</sumary>
/// <param name="sqlWhere">sqlWhere参数条件</param>
///<returns>IList<DATA0011>数据集合</returns>
public IList <DATA0011> FindBySql(string sqlWhere)
{
#region SQL
string sql = @"select
isNull(rkey,0) as rkey
,
isNull(file_pointer,0) as file_pointer
,
isNull(source_type,0) as source_type
,
isNull(note_pad_line_1,'') as note_pad_line_1
,
isNull(note_pad_line_2,'') as note_pad_line_2
,
isNull(note_pad_line_3,'') as note_pad_line_3
,
isNull(note_pad_line_4,'') as note_pad_line_4
from DATA0011 with (nolock)" ;
if (sqlWhere.Length > 0)
{
sql = sql + " where " + sqlWhere;
}
#endregion
IList <DATA0011> resultList = new List <DATA0011>();
#region 操作
try
{
using (DataTable tb = dbHelper.GetDataSet(sql))
{
foreach (DataRow row in tb.Rows)
{
DATA0011 data0011 = new DATA0011();
data0011.RKEY = decimal.Parse(row["RKEY"].ToString());
data0011.FILE_POINTER = decimal.Parse(row["FILE_POINTER"].ToString());
data0011.SOURCE_TYPE = decimal.Parse(row["SOURCE_TYPE"].ToString());
data0011.NOTE_PAD_LINE_1 = row["NOTE_PAD_LINE_1"].ToString();
data0011.NOTE_PAD_LINE_2 = row["NOTE_PAD_LINE_2"].ToString();
data0011.NOTE_PAD_LINE_3 = row["NOTE_PAD_LINE_3"].ToString();
data0011.NOTE_PAD_LINE_4 = row["NOTE_PAD_LINE_4"].ToString();
resultList.Add(data0011);
}
}
}
catch (Exception e)
{
log.Info("FID=" + this.factoryID.ToString() + ";userAD=" + this.userAD + ";FindBySql function:" + e.Message, e);
throw e;
}
#endregion
return(resultList);
}
public int Add(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, DATA0011 data0011)
{
// Validate input
if (data0011 == null)
{
return(0);
}
return(data0011Dal.Add(cmd, conn, trans, data0011));
}
/// <summary>
/// 向数据库中插入一条新记录。
/// </summary>
/// <param name="DATA0011">data0011对象</param>
/// <returns>新插入记录的编号</returns>
public int Add(DATA0011 data0011)
{
// Validate input
if (data0011 == null)
{
return(0);
}
return(data0011Dal.Add(data0011));
}
public int Delete(DATA0011 data0011)
{
// Validate input
if (data0011 == null)
{
return(0);
}
return(data0011Dal.Delete(data0011));
}
public void Update(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, DATA0011 data0011)
{
// Validate input
if (data0011 == null)
{
return;
}
data0011Dal.Update(cmd, conn, trans, data0011);
}
/// <summary>
/// 向数据库中插入一条新记录。
/// </summary>
/// <param name="DATA0011">data0011对象</param>
/// <returns>新插入记录的编号</returns>
public int Add(DATA0011 data0011)
{
#region 调用SQL存储过程进行添加
string sql = "sp_DATA0011_Add";
///存储过程名
SqlParameter[] parameters =
{
new SqlParameter("@returnID", SqlDbType.Int),
new SqlParameter("@userAD", SqlDbType.VarChar),
///new SqlParameter("@RKEY",SqlDbType.Decimal,9),
new SqlParameter("@RKEY", SqlDbType.Float),
new SqlParameter("@FILE_POINTER", SqlDbType.Decimal, 9),
new SqlParameter("@SOURCE_TYPE", SqlDbType.Decimal, 9),
new SqlParameter("@NOTE_PAD_LINE_1", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_2", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_3", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_4", SqlDbType.VarChar, 70)
};
parameters[0].Value = 0;
parameters[0].Direction = ParameterDirection.InputOutput;
parameters[1].Value = this.userAD;
parameters[2].Direction = ParameterDirection.InputOutput;
parameters[2].Value = data0011.RKEY;
parameters[3].Value = data0011.FILE_POINTER;
parameters[4].Value = data0011.SOURCE_TYPE;
parameters[5].Value = data0011.NOTE_PAD_LINE_1;
parameters[6].Value = data0011.NOTE_PAD_LINE_2;
parameters[7].Value = data0011.NOTE_PAD_LINE_3;
parameters[8].Value = data0011.NOTE_PAD_LINE_4;
#endregion
#region 数据库操作
int result = 0;
try
{
dbHelper.ExecuteCommandProc(sql, parameters);
result = int.Parse(parameters[0].Value.ToString());
data0011.RKEY = decimal.Parse(parameters[2].Value.ToString());
// log.Info("FID="+this.factoryID.ToString()+";userAD="+this.userAD+";DATA0011,save successful");
}
catch (Exception e)
{
///message ID
result = 2;
log.Error("FID=" + this.factoryID.ToString() + ";userAD=" + this.userAD + ";" + e.Message, e);
}
#endregion
return(result);
}
///<sumary>
/// 通过主键获取数据对象
///</sumary>
/// <param name="RKEY">rkey</param>
///<returns>DATA0011对象</returns>
public DATA0011 getDATA0011ByRKEY(decimal rkey)
{
#region SQL
string sql = @"select top 1
isNull(rkey,0) as rkey
,
isNull(file_pointer,0) as file_pointer
,
isNull(source_type,0) as source_type
,
isNull(note_pad_line_1,'') as note_pad_line_1
,
isNull(note_pad_line_2,'') as note_pad_line_2
,
isNull(note_pad_line_3,'') as note_pad_line_3
,
isNull(note_pad_line_4,'') as note_pad_line_4
from DATA0011 with (nolock) where RKEY='{0}'" ;
#endregion
///定义返回对象
DATA0011 data0011 = null;
#region 数据库操作
try
{
data0011 = new DATA0011();
using (DataTable tb = dbHelper.GetDataSet(string.Format(sql, rkey)))
{
foreach (DataRow row in tb.Rows)
{
data0011.RKEY = decimal.Parse(row["RKEY"].ToString());
data0011.FILE_POINTER = decimal.Parse(row["FILE_POINTER"].ToString());
data0011.SOURCE_TYPE = decimal.Parse(row["SOURCE_TYPE"].ToString());
data0011.NOTE_PAD_LINE_1 = row["NOTE_PAD_LINE_1"].ToString();
data0011.NOTE_PAD_LINE_2 = row["NOTE_PAD_LINE_2"].ToString();
data0011.NOTE_PAD_LINE_3 = row["NOTE_PAD_LINE_3"].ToString();
data0011.NOTE_PAD_LINE_4 = row["NOTE_PAD_LINE_4"].ToString();
}
}
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
log.Info("FID=" + this.factoryID.ToString() + ";userAD=" + this.userAD + ";get function:" + e.Message, e);
}
#endregion
return(data0011);
}
public void BinData(string strWhere)
{
try
{
IList <DATA0583> info = new List <DATA0583>();
DATA0011 infoD11 = new DATA0011();
DATA0583BLL bll = new DATA0583BLL(Frm.DBConnection);
DATA0011BLL bllD11 = new DATA0011BLL(Frm.DBConnection);
string s_temp;
info = bll.FindBySql("SOURCE_PTR = " + Frm.id_rkey.ToString() + " and SOURCE_TYPE = " + Frm.id_TYPE.ToString());
for (int i = 0; i < info.Count; i++)
{
s_temp = "";
if (info[i].ACTION == 1)
{
s_temp = "原值暂缓";
}
if (info[i].ACTION == 2)
{
s_temp = "原值激活";
}
infoD11 = bllD11.getDATA0011ByRKEY(info[i].NOTEPAD_PTR);
Frm.textBox.Text = "日期：" + info[i].TDATE.ToShortDateString() + " " + s_temp + "\r\n" +
infoD11.NOTE_PAD_LINE_1.Trim() + infoD11.NOTE_PAD_LINE_2.Trim() + infoD11.NOTE_PAD_LINE_3.Trim() + infoD11.NOTE_PAD_LINE_4.Trim() + "\r\n\r\n";
}
if (info.Count <= 0)
{
Frm.textBox.Text = "没有暂缓历史记录！";
}
if (!Frm.ib_Edit)
{
Frm.textBox.ReadOnly = true;
Frm.textBoxRecord.ReadOnly = true;
}
}
catch (Exception e1)
{
log.PrintInfo(e1);
return;
}
}
///<sumary>
/// 删除
///</sumary>
/// <param name="data0011">对象</param>
///<returns>返回INT类型号, 0为操作成功, 非0操作失败.</returns>
public int Delete(DATA0011 data0011)
{
#region 调用SQL存储过程进行删除
string sql = "sp_DATA0011_Delete";
//=========================
SqlParameter[] parameters =
{
new SqlParameter("@returnID", SqlDbType.Int),
new SqlParameter("@userAD", SqlDbType.VarChar),
new SqlParameter("@RKEY", SqlDbType.Decimal, 9)
};
parameters[0].Value = 1;
parameters[0].Direction = ParameterDirection.InputOutput;
parameters[1].Value = this.userAD;
parameters[2].Value = data0011.RKEY;
//=========================
#endregion
#region 数据库操作
int result = 0;
try
{
dbHelper.ExecuteCommandProc(sql, parameters);
result = int.Parse(parameters[0].Value.ToString());
// log.Info("FID="+this.factoryID.ToString()+";userAD="+this.userAD+";DATA0011,delete successful");
}
catch (Exception e)
{
result = 2;
log.Info("FID=" + this.factoryID.ToString() + ";userAD=" + this.userAD + ";" + e.Message, e);
}
#endregion
return(result);
}
public int Add(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, DATA0011 data0011)
{
#region 创建SQL语法
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into DATA0011(");
strSql.Append("FILE_POINTER,SOURCE_TYPE,NOTE_PAD_LINE_1,NOTE_PAD_LINE_2,NOTE_PAD_LINE_3,NOTE_PAD_LINE_4");
strSql.Append(" ) values (");
strSql.Append("@FILE_POINTER,@SOURCE_TYPE,@NOTE_PAD_LINE_1,@NOTE_PAD_LINE_2,@NOTE_PAD_LINE_3,@NOTE_PAD_LINE_4");
strSql.Append(");select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@FILE_POINTER", SqlDbType.Decimal, 9),
new SqlParameter("@SOURCE_TYPE", SqlDbType.Decimal, 9),
new SqlParameter("@NOTE_PAD_LINE_1", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_2", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_3", SqlDbType.VarChar, 70),
new SqlParameter("@NOTE_PAD_LINE_4", SqlDbType.VarChar, 70)
};
parameters[0].Value = data0011.FILE_POINTER;
parameters[1].Value = data0011.SOURCE_TYPE;
parameters[2].Value = data0011.NOTE_PAD_LINE_1;
parameters[3].Value = data0011.NOTE_PAD_LINE_2;
parameters[4].Value = data0011.NOTE_PAD_LINE_3;
parameters[5].Value = data0011.NOTE_PAD_LINE_4;
#endregion
#region 操作
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = strSql.ToString();
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
int indentity = 0;
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
indentity = 0;
}
else
{
indentity = int.Parse(obj.ToString());
}
#endregion
return(indentity);
}
public void BinData(string strWhere)
{
try
{
DATA0011 info = new DATA0011();
DATA0011BLL bll = new DATA0011BLL(Frm.DBConnection);
DataTable tb;
string s_SQL = "";
info = bll.getDATA0011ByRKEY(Frm.id_rkey);
if (info.RKEY > 0)
{
Frm.textBox.Text = info.NOTE_PAD_LINE_1.Trim() + info.NOTE_PAD_LINE_2.Trim() + info.NOTE_PAD_LINE_3.Trim() + info.NOTE_PAD_LINE_4.Trim();
}
else
{
Frm.textBox.Text = "";
}
if (Frm.ii_Sort == 500)
{//拒绝
s_SQL = @"
select *
from data0011 with (nolock),data0500 with (nolock)
where data0011.FILE_POINTER = data0500.rkey
and data0011.SOURCE_TYPE = 500
and data0500.CUSTOMER_PART_PTR = " + Frm.id_Sort_RKEY.ToString() + @"
order by data0011.rkey
";
}
if (Frm.ii_Sort == 501)
{//拒绝
s_SQL = @"
select *
from data0011 with (nolock),data0500 with (nolock)
where data0011.FILE_POINTER = data0500.rkey
and data0011.SOURCE_TYPE = 501
and data0500.CUSTOMER_PART_PTR = " + Frm.id_Sort_RKEY.ToString() + @"
order by data0011.rkey
";
}
tb = bll.getDataSet(s_SQL);
Frm.textBoxOLD.Text = "";
if (tb.Rows.Count > 0)
{
Frm.textBoxOLD.Text += tb.Rows[0]["NOTE_PAD_LINE_1"].ToString().Trim();
Frm.textBoxOLD.Text += tb.Rows[0]["NOTE_PAD_LINE_2"].ToString().Trim();
Frm.textBoxOLD.Text += tb.Rows[0]["NOTE_PAD_LINE_3"].ToString().Trim();
Frm.textBoxOLD.Text += tb.Rows[0]["NOTE_PAD_LINE_4"].ToString().Trim();
Frm.textBoxOLD.Text += "\r\n\r\n";
}
if (!Frm.ib_Edit)
{
Frm.textBox.ReadOnly = true;
Frm.buttonFind.Visible = false;
}
}
catch (Exception e1)
{
log.PrintInfo(e1);
return;
}
}