void AddTwo(SqlCommand cmd, HengDaEntity.FormMainTwo _modelTwo, StringBuilder strSql, SqlTransaction tran, SqlConnection conn)
{
strSql = new StringBuilder( );
strSql.Append("INSERT INTO HDSHF (");
strSql.Append("SHF001,SHF002,SHF003,SHF004,SHF005,SHF006,SHF007,SHF008)");
strSql.Append(" VALUES (");
strSql.Append("@SHF001,@SHF002,@SHF003,@SHF004,@SHF005,@SHF006,@SHF007,@SHF008)");
SqlParameter[] parameter =
{
new SqlParameter("@SHF001", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF002", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF003", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF004", SqlDbType.Int),
new SqlParameter("@SHF005", SqlDbType.Decimal, 6),
new SqlParameter("@SHF006", SqlDbType.Int),
new SqlParameter("@SHF007", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF008", SqlDbType.NVarChar, 255)
};
parameter[0].Value = _modelTwo.SHF001;
parameter[1].Value = _modelTwo.SHF002;
parameter[2].Value = _modelTwo.SHF003;
parameter[3].Value = _modelTwo.SHF004;
parameter[4].Value = _modelTwo.SHF005;
parameter[5].Value = _modelTwo.SHF006;
parameter[6].Value = _modelTwo.SHF007;
parameter[7].Value = _modelTwo.SHF008;
cmd.Parameters.Clear( );
SqlHelper.PrepareCommand(cmd, conn, tran, strSql.ToString( ), parameter);
cmd.CommandText = strSql.ToString( );
cmd.ExecuteNonQuery( );
}
void DeleteTwo(SqlCommand cmd, HengDaEntity.FormMainTwo _modelTwo, StringBuilder strSql, SqlTransaction tran, SqlConnection conn)
{
strSql = new StringBuilder( );
strSql.Append("DELETE FROM HDSHF");
strSql.Append(" WHERE SHF001=@SHF001");
strSql.Append(" AND SHF002=@SHF002");
strSql.Append(" AND SHF003=@SHF003");
SqlParameter[] parameter =
{
new SqlParameter("@SHF001", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF002", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF003", SqlDbType.NVarChar, 20)
};
parameter[0].Value = _modelTwo.SHF001;
parameter[1].Value = _modelTwo.SHF002;
parameter[2].Value = _modelTwo.SHF003;
cmd.Parameters.Clear( );
SqlHelper.PrepareCommand(cmd, conn, tran, strSql.ToString( ), parameter);
cmd.CommandText = strSql.ToString( );
cmd.ExecuteNonQuery( );
}
void UpdateTwo(SqlCommand cmd, HengDaEntity.FormMainTwo _modelTwo, StringBuilder strSql, SqlTransaction tran, SqlConnection conn)
{
strSql = new StringBuilder( );
strSql.Append("UPDATE HDSHF SET ");
strSql.Append("SHF004=@SHF004,");
strSql.Append("SHF005=@SHF005,");
strSql.Append("SHF006=@SHF006,");
strSql.Append("SHF007=@SHF007,");
strSql.Append("SHF008=@SHF008");
strSql.Append(" WHERE SHF001=@SHF001");
strSql.Append(" AND SHF002=@SHF002");
strSql.Append(" AND SHF003=@SHF003");
SqlParameter[] parameter =
{
new SqlParameter("@SHF001", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF002", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF003", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF004", SqlDbType.Int),
new SqlParameter("@SHF005", SqlDbType.Decimal, 6),
new SqlParameter("@SHF006", SqlDbType.Int),
new SqlParameter("@SHF007", SqlDbType.NVarChar, 20),
new SqlParameter("@SHF008", SqlDbType.NVarChar, 255)
};
parameter[0].Value = _modelTwo.SHF001;
parameter[1].Value = _modelTwo.SHF002;
parameter[2].Value = _modelTwo.SHF003;
parameter[3].Value = _modelTwo.SHF004;
parameter[4].Value = _modelTwo.SHF005;
parameter[5].Value = _modelTwo.SHF006;
parameter[6].Value = _modelTwo.SHF007;
parameter[7].Value = _modelTwo.SHF008;
cmd.Parameters.Clear( );
SqlHelper.PrepareCommand(cmd, conn, tran, strSql.ToString( ), parameter);
cmd.CommandText = strSql.ToString( );
cmd.ExecuteNonQuery( );
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="tableOne">单身一</param>
/// <param name="tableTwo">单身二</param>
/// <param name="_modelHeader">单头</param>
/// <returns></returns>
public bool Save(DataTable tableOne, DataTable tableTwo, HengDaEntity.FormMainHeader _modelHeader)
{
using (SqlConnection conn = new SqlConnection(SqlHelper.connstr))
{
conn.Open( );
SqlCommand cmd = new SqlCommand( );
cmd.Connection = conn;
SqlTransaction tran = conn.BeginTransaction( );
cmd.Transaction = tran;
try
{
ArrayList SQLString = new ArrayList( );
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT COUNT(1) FROM HDSHD WHERE SHD001=@SHD001");
SqlParameter[] paramete =
{
new SqlParameter("@SHD001", SqlDbType.NVarChar, 20)
};
paramete[0].Value = _modelHeader.SHD001;
if (SqlHelper.Exists(strSql.ToString( ), paramete) == true)
{
UpdateMain(cmd, _modelHeader, strSql, tran, conn);
}
else
{
AddMain(cmd, _modelHeader, strSql, tran, conn);
}
DataTable tableChoisOne;
if (tableOne != null && tableOne.Rows.Count > 0)
{
HengDaEntity.FormMainOne _modelOne = new HengDaEntity.FormMainOne( );
_modelOne.SHE001 = /*tableOne.Rows[0]["SHE001"].ToString( );*/ _modelHeader.SHD001;
tableChoisOne = SqlHelper.ExecuteDataTable("SELECT SHE002,SHE003 FROM HDSHE WHERE SHE001='" + _modelOne.SHE001 + "'");
for (int i = 0; i < tableOne.Rows.Count; i++)
{
_modelOne.SHE002 = tableOne.Rows[i]["SHE002"].ToString( );
_modelOne.SHE003 = tableOne.Rows[i]["SHE003"].ToString( );
_modelOne.SHE004 = tableOne.Rows[i]["SHE004"].ToString( );
_modelOne.SHE005 = tableOne.Rows[i]["SHE005"].ToString( );
_modelOne.SHE006 = tableOne.Rows[i]["SHE006"].ToString( );
_modelOne.SHE007 = string.IsNullOrEmpty(tableOne.Rows[i]["SHE007"].ToString( )) == true ? 0 : Convert.ToInt32(tableOne.Rows[i]["SHE007"].ToString( ));
_modelOne.SHE008 = string.IsNullOrEmpty(tableOne.Rows[i]["SHE008"].ToString( )) == true ? 0 : Convert.ToDecimal(tableOne.Rows[i]["SHE008"].ToString( ));
_modelOne.SHE009 = tableOne.Rows[i]["SHE009"].ToString( );
if (!string.IsNullOrEmpty(_modelOne.SHE004) && !string.IsNullOrEmpty(_modelOne.SHE005))
{
if (tableChoisOne.Select("SHE002='" + _modelOne.SHE002 + "' AND SHE003='" + _modelOne.SHE003 + "'").Length > 0)
{
UpdateOne(cmd, _modelOne, strSql, tran, conn);
}
else
{
AddOne(cmd, _modelOne, strSql, tran, conn);
}
}
}
if (tableChoisOne != null && tableChoisOne.Rows.Count > 0)
{
for (int k = 0; k < tableChoisOne.Rows.Count; k++)
{
_modelOne.SHE002 = tableChoisOne.Rows[k]["SHE002"].ToString( );
_modelOne.SHE003 = tableChoisOne.Rows[k]["SHE003"].ToString( );
if (tableOne.Select("SHE001='" + _modelOne.SHE001 + "' AND SHE002='" + _modelOne.SHE002 + "' AND SHE003='" + _modelOne.SHE003 + "'").Length < 1)
{
DeleteOne(cmd, _modelOne, strSql, tran, conn);
}
}
}
}
if (tableTwo != null && tableTwo.Rows.Count > 0)
{
HengDaEntity.FormMainTwo _modelTwo = new HengDaEntity.FormMainTwo( );
_modelTwo.SHF001 = /* tableTwo.Rows[0]["SHF001"].ToString( )*/ _modelHeader.SHD001;
tableChoisOne = SqlHelper.ExecuteDataTable("SELECT SHF002,SHF003 FROM HDSHF WHERE SHF001='" + _modelTwo.SHF001 + "'");
for (int i = 0; i < tableTwo.Rows.Count; i++)
{
_modelTwo.SHF002 = tableTwo.Rows[i]["SHF002"].ToString( );
_modelTwo.SHF003 = tableTwo.Rows[i]["SHF003"].ToString( );
_modelTwo.SHF004 = string.IsNullOrEmpty(tableTwo.Rows[i]["SHF004"].ToString( )) == true ? 0 : Convert.ToInt32(tableTwo.Rows[i]["SHF004"].ToString( ));
_modelTwo.SHF005 = string.IsNullOrEmpty(tableTwo.Rows[i]["SHF005"].ToString( )) == true ? 0 : Convert.ToDecimal(tableTwo.Rows[i]["SHF005"].ToString( ));
_modelTwo.SHF006 = string.IsNullOrEmpty(tableTwo.Rows[i]["SHF006"].ToString( )) == true ? 0 : Convert.ToInt32(tableTwo.Rows[i]["SHF006"].ToString( ));
_modelTwo.SHF007 = tableTwo.Rows[i]["SHF007"].ToString( );
_modelTwo.SHF008 = tableTwo.Rows[i]["SHF008"].ToString( );
if (!string.IsNullOrEmpty(_modelTwo.SHF007))
{
if (tableChoisOne.Select("SHF002='" + _modelTwo.SHF002 + "' AND SHF003='" + _modelTwo.SHF003 + "'").Length > 0)
{
UpdateTwo(cmd, _modelTwo, strSql, tran, conn);
}
else
{
AddTwo(cmd, _modelTwo, strSql, tran, conn);
}
}
}
if (tableChoisOne != null && tableChoisOne.Rows.Count > 0)
{
for (int k = 0; k < tableChoisOne.Rows.Count; k++)
{
_modelTwo.SHF002 = tableChoisOne.Rows[k]["SHF002"].ToString( );
_modelTwo.SHF003 = tableChoisOne.Rows[k]["SHF003"].ToString( );
if (tableTwo.Select("SHF001='" + _modelTwo.SHF001 + "' AND SHF002='" + _modelTwo.SHF002 + "' AND SHF003='" + _modelTwo.SHF003 + "'").Length < 1)
{
DeleteTwo(cmd, _modelTwo, strSql, tran, conn);
}
}
}
}
tran.Commit( );
return(true);
}
catch {
tran.Rollback( );
return(false);
}
finally
{
cmd.Dispose( );
conn.Close( );
}
}
}
