public static List<GIAY_TO> getGiayTo(List<NGUOI_LX> listData)
{
List<GIAY_TO> rtn = new List<GIAY_TO>();
if (listData == null || listData.Count <= 0)
{
return rtn;
}
//Tao SQL
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM [dbo].[NguoiLXHS_GiayTo] WHERE MADK IN (");
int i = 1;
foreach (NGUOI_LX user in listData)
{
// IN clause
sb.Append("@MADK" + i.ToString() + ",");
i++;
}
sb.Length--;
sb.Append(")");
//set SqlCommand
SqlConnection connection = Ultils.GetDBConnection();
SqlCommand command = new SqlCommand(sb.ToString(), connection);
//Set param
i = 1;
foreach (NGUOI_LX user in listData)
{
// parameter
command.Parameters.AddWithValue("@MADK" + i.ToString(), user.MADK);
i++;
}
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
GIAY_TO obj = null;
while (reader.Read())
{
obj = new GIAY_TO();
if (!reader.IsDBNull(reader.GetOrdinal("MaDK"))) { obj.MADK = reader.GetString(reader.GetOrdinal("MaDK")); } else { obj.MADK = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("MaGT"))) { obj.MAGT = reader.GetInt32(reader.GetOrdinal("MaGT")).ToString(); } else { obj.MAGT = null; }
if (!reader.IsDBNull(reader.GetOrdinal("SoHoSo"))) { obj.SOHOSO = reader.GetString(reader.GetOrdinal("SoHoSo")); } else { obj.SOHOSO = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("TenGT"))) { obj.TEN_NLX = reader.GetString(reader.GetOrdinal("TenGT")); } else { obj.TEN_NLX = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) { obj.TRANGTHAI = reader.GetBoolean(reader.GetOrdinal("TrangThai")).ToString(); } else { obj.TRANGTHAI = null; }
rtn.Add(obj);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
return rtn;
}
public static int insertData(SqlConnection connection, SqlTransaction transaction, GIAY_TO obj)
{
int rtn = 0;
try
{
string queryString = "INSERT INTO [dbo].[NguoiLXHS_GiayTo] (MaDK, MaGT, SoHoSo, TenGT, TrangThai) " +
"VALUES (@MADK, @MAGT, @SOHOSO, @TEN_NLX, @TRANGTHAI)";
SqlCommand command = new SqlCommand(queryString, connection);
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
command.Parameters.AddWithValue("@MaDK", obj.MADK);
if (obj.MAGT != null)
{
command.Parameters.AddWithValue("@MaGT", Int32.Parse(obj.MAGT));
}
else
{
command.Parameters.AddWithValue("@MaGT", DBNull.Value);
}
if (obj.SOHOSO == null || obj.SOHOSO.Length <= 0)
{
command.Parameters.AddWithValue("@SoHoSo", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@SoHoSo", obj.SOHOSO);
}
if (obj.TEN_NLX == null || obj.TEN_NLX.Length <= 0)
{
command.Parameters.AddWithValue("@TEN_NLX", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@TEN_NLX", obj.TEN_NLX);
}
if (obj.TRANGTHAI == null)
{
command.Parameters.AddWithValue("@TrangThai", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@TrangThai", obj.TRANGTHAI);
}
rtn = command.ExecuteNonQuery();
}
catch (Exception ex)
{
rtn = -1;
throw ex;
}
finally
{
}
return rtn;
}
