void AddOne(SqlCommand cmd, HengDaEntity.FormMainOne _modelOne, StringBuilder strSql, SqlTransaction tran, SqlConnection conn) { strSql = new StringBuilder( ); strSql.Append("INSERT INTO HDSHE ("); strSql.Append("SHE001,SHE002,SHE003,SHE004,SHE005,SHE006,SHE007,SHE008,SHE009)"); strSql.Append(" VALUES ("); strSql.Append("@SHE001,@SHE002,@SHE003,@SHE004,@SHE005,@SHE006,@SHE007,@SHE008,@SHE009)"); SqlParameter[] parameter = { new SqlParameter("@SHE001", SqlDbType.NVarChar, 20), new SqlParameter("@SHE002", SqlDbType.NVarChar, 20), new SqlParameter("@SHE003", SqlDbType.NVarChar, 20), new SqlParameter("@SHE004", SqlDbType.NVarChar, 20), new SqlParameter("@SHE005", SqlDbType.NVarChar, 20), new SqlParameter("@SHE006", SqlDbType.NVarChar, 20), new SqlParameter("@SHE007", SqlDbType.Int), new SqlParameter("@SHE008", SqlDbType.Decimal, 6), new SqlParameter("@SHE009", SqlDbType.NVarChar, 255) }; parameter[0].Value = _modelOne.SHE001; parameter[1].Value = _modelOne.SHE002; parameter[2].Value = _modelOne.SHE003; parameter[3].Value = _modelOne.SHE004; parameter[4].Value = _modelOne.SHE005; parameter[5].Value = _modelOne.SHE006; parameter[6].Value = _modelOne.SHE007; parameter[7].Value = _modelOne.SHE008; parameter[8].Value = _modelOne.SHE009; cmd.Parameters.Clear( ); SqlHelper.PrepareCommand(cmd, conn, tran, strSql.ToString( ), parameter); cmd.CommandText = strSql.ToString( ); cmd.ExecuteNonQuery( ); }
void UpdateOne(SqlCommand cmd, HengDaEntity.FormMainOne _modelOne, StringBuilder strSql, SqlTransaction tran, SqlConnection conn) { strSql = new StringBuilder( ); strSql.Append("UPDATE HDSHE SET "); strSql.Append("SHE004=@SHE004,"); strSql.Append("SHE005=@SHE005,"); strSql.Append("SHE006=@SHE006,"); strSql.Append("SHE007=@SHE007,"); strSql.Append("SHE008=@SHE008,"); strSql.Append("SHE009=@SHE009"); strSql.Append(" WHERE SHE001=@SHE001"); strSql.Append(" AND SHE002=@SHE002"); strSql.Append(" AND SHE003=@SHE003"); SqlParameter[] parameter = { new SqlParameter("@SHE001", SqlDbType.NVarChar, 20), new SqlParameter("@SHE002", SqlDbType.NVarChar, 20), new SqlParameter("@SHE003", SqlDbType.NVarChar, 20), new SqlParameter("@SHE004", SqlDbType.NVarChar, 20), new SqlParameter("@SHE005", SqlDbType.NVarChar, 20), new SqlParameter("@SHE006", SqlDbType.NVarChar, 20), new SqlParameter("@SHE007", SqlDbType.Int), new SqlParameter("@SHE008", SqlDbType.Decimal, 6), new SqlParameter("@SHE009", SqlDbType.NVarChar, 255) }; parameter[0].Value = _modelOne.SHE001; parameter[1].Value = _modelOne.SHE002; parameter[2].Value = _modelOne.SHE003; parameter[3].Value = _modelOne.SHE004; parameter[4].Value = _modelOne.SHE005; parameter[5].Value = _modelOne.SHE006; parameter[6].Value = _modelOne.SHE007; parameter[7].Value = _modelOne.SHE008; parameter[8].Value = _modelOne.SHE009; cmd.Parameters.Clear( ); SqlHelper.PrepareCommand(cmd, conn, tran, strSql.ToString( ), parameter); cmd.CommandText = strSql.ToString( ); cmd.ExecuteNonQuery( ); }
void DeleteOne(SqlCommand cmd, HengDaEntity.FormMainOne _modelOne, StringBuilder strSql, SqlTransaction tran, SqlConnection conn) { strSql = new StringBuilder( ); strSql.Append("DELETE FROM HDSHE"); strSql.Append(" WHERE SHE001=@SHE001"); strSql.Append(" AND SHE002=@SHE002"); strSql.Append(" AND SHE003=@SHE003"); SqlParameter[] parameter = { new SqlParameter("@SHE001", SqlDbType.NVarChar, 20), new SqlParameter("@SHE002", SqlDbType.NVarChar, 20), new SqlParameter("@SHE003", SqlDbType.NVarChar, 20) }; parameter[0].Value = _modelOne.SHE001; parameter[1].Value = _modelOne.SHE002; parameter[2].Value = _modelOne.SHE003; 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( ); } } }