public static int insertData(NGUOI_LX obj) { int rtn = 0; string queryString = "INSERT INTO [dbo].[NguoiLX] (MaDK, DonViNhanHSo, HoDemNLX, TenNLX, HoVaTen, MaQuocTich, NgaySinh, NoiTT, NoiTT_MaDVHC, NoiTT_MaDVQL, NoiCT, NoiCT_MaDVHC, NoiCT_MaDVQL, SoCMT, NgayCapCMT, NoiCapCMT, GhiChu, TrangThai, NguoiTao, NguoiSua, NgayTao, NgaySua, GioiTinh, HoVaTenIn, SO_CMND_CU) " + "VALUES (@MaDK, @DonViNhanHSo, @HoDemNLX, @TenNLX, @HoVaTen, @MaQuocTich, @NgaySinh, @NoiTT, @NoiTT_MaDVHC, @NoiTT_MaDVQL, @NoiCT, @NoiCT_MaDVHC, @NoiCT_MaDVQL, @SoCMT, @NgayCapCMT, @NoiCapCMT, @GhiChu, @TrangThai, @NguoiTao, @NguoiSua, @NgayTao, @NgaySua, @GioiTinh, @HoVaTenIn, @SO_CMND_CU)"; SqlConnection connection = Ultils.GetDBConnectionImport(); SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@MaDK", obj.MADK); command.Parameters.AddWithValue("@DonViNhanHSo", obj.DV_NHAN_HS); command.Parameters.AddWithValue("@HoDemNLX", obj.HO_DEM_NLX); command.Parameters.AddWithValue("@TenNLX", obj.TEN_NLX); command.Parameters.AddWithValue("@HoVaTen", obj.HO_VA_TEN); command.Parameters.AddWithValue("@MaQuocTich", obj.MA_QUOC_TICH); command.Parameters.AddWithValue("@NgaySinh", obj.NGAY_SINH); command.Parameters.AddWithValue("@NoiTT", obj.NOI_TT); command.Parameters.AddWithValue("@NoiTT_MaDVHC", obj.NOI_TT_MA_DVHC); command.Parameters.AddWithValue("@NoiTT_MaDVQL", obj.NOI_TT_MA_DVQL); command.Parameters.AddWithValue("@NoiCT", obj.NOI_CT); command.Parameters.AddWithValue("@NoiCT_MaDVHC", obj.NOI_CT_MA_DVHC); command.Parameters.AddWithValue("@NoiCT_MaDVQL", obj.NOI_CT_MA_DVQL); command.Parameters.AddWithValue("@SoCMT", obj.SO_CMT); command.Parameters.AddWithValue("@NgayCapCMT", obj.NGAY_CAP_CMT); command.Parameters.AddWithValue("@NoiCapCMT", obj.NOI_CAP_CMT); command.Parameters.AddWithValue("@GhiChu", obj.GHI_CHU); command.Parameters.AddWithValue("@TrangThai", obj.TRANG_THAI); command.Parameters.AddWithValue("@NguoiTao", obj.NGUOI_TAO); command.Parameters.AddWithValue("@NguoiSua", obj.NGUOI_SUA); command.Parameters.AddWithValue("@NgayTao", obj.NGAY_TAO); command.Parameters.AddWithValue("@NgaySua", obj.NGAY_SUA); command.Parameters.AddWithValue("@GioiTinh", obj.GIOI_TINH); command.Parameters.AddWithValue("@HoVaTenIn", obj.HO_VA_TEN_IN); command.Parameters.AddWithValue("@SO_CMND_CU", obj.SO_CMND_CU); connection.Open(); int recordsAffected = command.ExecuteNonQuery(); return(rtn); }
public static int insertDataString(SqlConnection connection, SqlTransaction transaction, NGUOI_LX obj) { int rtn = 0; try { string queryString = "INSERT INTO [dbo].[NguoiLX] (MaDK, DonViNhanHSo, HoDemNLX, TenNLX, HoVaTen, MaQuocTich, NgaySinh, NoiTT, NoiTT_MaDVHC, NoiTT_MaDVQL, NoiCT, NoiCT_MaDVHC, NoiCT_MaDVQL, SoCMT, NgayCapCMT, NoiCapCMT, GhiChu, TrangThai, NguoiTao, NguoiSua, NgayTao, NgaySua, GioiTinh, HoVaTenIn, SO_CMND_CU) " + "VALUES (@MaDK, @DonViNhanHSo, @HoDemNLX, @TenNLX, @HoVaTen, @MaQuocTich, @NgaySinh, @NoiTT, @NoiTT_MaDVHC, @NoiTT_MaDVQL, @NoiCT, @NoiCT_MaDVHC, @NoiCT_MaDVQL, @SoCMT, @NgayCapCMT, @NoiCapCMT, @GhiChu, @TrangThai, @NguoiTao, @NguoiSua, @NgayTao, @NgaySua, @GioiTinh, @HoVaTenIn, @SO_CMND_CU)"; 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.DV_NHAN_HS == null || obj.DV_NHAN_HS.Trim().Length <= 0) { command.Parameters.AddWithValue("@DonViNhanHSo", DBNull.Value); } else { command.Parameters.AddWithValue("@DonViNhanHSo", obj.DV_NHAN_HS); } if (obj.HO_DEM_NLX == null || obj.HO_DEM_NLX.Trim().Length <= 0) { command.Parameters.AddWithValue("@HoDemNLX", DBNull.Value); } else { command.Parameters.AddWithValue("@HoDemNLX", obj.HO_DEM_NLX); } if (obj.TEN_NLX == null || obj.TEN_NLX.Trim().Length <= 0) { command.Parameters.AddWithValue("@TenNLX", DBNull.Value); } else { command.Parameters.AddWithValue("@TenNLX", obj.TEN_NLX); } if (obj.HO_VA_TEN == null || obj.HO_VA_TEN.Trim().Length <= 0) { command.Parameters.AddWithValue("@HoVaTen", DBNull.Value); } else { command.Parameters.AddWithValue("@HoVaTen", obj.HO_VA_TEN); } if (obj.MA_QUOC_TICH == null || obj.MA_QUOC_TICH.Trim().Length <= 0) { command.Parameters.AddWithValue("@MaQuocTich", DBNull.Value); } else { command.Parameters.AddWithValue("@MaQuocTich", obj.MA_QUOC_TICH); } if (obj.NGAY_SINH == null || obj.NGAY_SINH.Trim().Length <= 0) { command.Parameters.AddWithValue("@NgaySinh", DBNull.Value); } else { command.Parameters.AddWithValue("@NgaySinh", DateTime.ParseExact(obj.NGAY_SINH, Ultils.FORMAT_DATE, CultureInfo.InvariantCulture).ToString("yyyyMMdd")); } if (obj.NOI_TT == null || obj.NOI_TT.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiTT", ""); } else { command.Parameters.AddWithValue("@NoiTT", obj.NOI_TT); } if (obj.NOI_TT_MA_DVHC == null || obj.NOI_TT_MA_DVHC.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiTT_MaDVHC", DBNull.Value); } else { command.Parameters.AddWithValue("@NoiTT_MaDVHC", obj.NOI_TT_MA_DVHC); } if (obj.NOI_TT_MA_DVQL == null || obj.NOI_TT_MA_DVQL.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiTT_MaDVQL", DBNull.Value); } else { command.Parameters.AddWithValue("@NoiTT_MaDVQL", obj.NOI_TT_MA_DVQL); } if (obj.NOI_CT == null || obj.NOI_CT.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiCT", ""); } else { command.Parameters.AddWithValue("@NoiCT", obj.NOI_CT); } if (obj.NOI_CT_MA_DVHC == null || obj.NOI_CT_MA_DVHC.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiCT_MaDVHC", DBNull.Value); } else { command.Parameters.AddWithValue("@NoiCT_MaDVHC", obj.NOI_CT_MA_DVHC); } if (obj.NOI_CT_MA_DVQL == null || obj.NOI_CT_MA_DVQL.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiCT_MaDVQL", DBNull.Value); } else { command.Parameters.AddWithValue("@NoiCT_MaDVQL", obj.NOI_CT_MA_DVQL); } if (obj.SO_CMT == null || obj.SO_CMT.Trim().Length <= 0) { command.Parameters.AddWithValue("@SoCMT", DBNull.Value); } else { command.Parameters.AddWithValue("@SoCMT", obj.SO_CMT); } if (obj.NGAY_CAP_CMT == null || obj.NGAY_CAP_CMT.Trim().Length <= 0) { command.Parameters.AddWithValue("@NgayCapCMT", DBNull.Value); } else { command.Parameters.AddWithValue("@NgayCapCMT", DateTime.Parse(obj.NGAY_CAP_CMT)); } if (obj.NOI_CAP_CMT == null || obj.NOI_CAP_CMT.Trim().Length <= 0) { command.Parameters.AddWithValue("@NoiCapCMT", DBNull.Value); } else { command.Parameters.AddWithValue("@NoiCapCMT", obj.NOI_CAP_CMT); } if (obj.GHI_CHU == null || obj.GHI_CHU.Trim().Length <= 0) { command.Parameters.AddWithValue("@GhiChu", DBNull.Value); } else { command.Parameters.AddWithValue("@GhiChu", obj.GHI_CHU); } if (obj.TRANG_THAI == null) { command.Parameters.AddWithValue("@TrangThai", DBNull.Value); } else { command.Parameters.AddWithValue("@TrangThai", obj.TRANG_THAI); } if (obj.NGUOI_TAO == null || obj.NGUOI_TAO.Trim().Length <= 0) { command.Parameters.AddWithValue("@NguoiTao", DBNull.Value); } else { command.Parameters.AddWithValue("@NguoiTao", obj.NGUOI_TAO); } if (obj.NGUOI_SUA == null || obj.NGUOI_SUA.Trim().Length <= 0) { command.Parameters.AddWithValue("@NguoiSua", DBNull.Value); } else { command.Parameters.AddWithValue("@NguoiSua", obj.NGUOI_SUA); } if (obj.NGAY_TAO == null || obj.NGAY_TAO.Trim().Length <= 0) { command.Parameters.AddWithValue("@NgayTao", DBNull.Value); } else { command.Parameters.AddWithValue("@NgayTao", DateTime.Parse(obj.NGAY_TAO)); } if (obj.NGAY_SUA == null || obj.NGAY_SUA.Trim().Length <= 0) { command.Parameters.AddWithValue("@NgaySua", DBNull.Value); } else { command.Parameters.AddWithValue("@NgaySua", DateTime.Parse(obj.NGAY_SUA)); } if (obj.GIOI_TINH == null || obj.GIOI_TINH.Trim().Length <= 0) { command.Parameters.AddWithValue("@GioiTinh", DBNull.Value); } else { command.Parameters.AddWithValue("@GioiTinh", obj.GIOI_TINH); } if (obj.HO_VA_TEN_IN == null || obj.HO_VA_TEN_IN.Trim().Length <= 0) { command.Parameters.AddWithValue("@HoVaTenIn", DBNull.Value); } else { command.Parameters.AddWithValue("@HoVaTenIn", obj.HO_VA_TEN_IN); } if (obj.SO_CMND_CU == null || obj.SO_CMND_CU.Trim().Length <= 0) { command.Parameters.AddWithValue("@SO_CMND_CU", DBNull.Value); } else { command.Parameters.AddWithValue("@SO_CMND_CU", obj.SO_CMND_CU); } rtn = command.ExecuteNonQuery(); } catch (Exception ex) { rtn = -1; throw ex; } finally { } return(rtn); }
public static List <NGUOI_LX> getData(DateTime start, DateTime end, String trangthai, string cmnd, String lstMaDv) { List <NGUOI_LX> rtn = new List <NGUOI_LX>(); string queryString = ""; if (trangthai != null && trangthai.Trim().Length > 0) { queryString = "SELECT n.* FROM [dbo].[NguoiLX] n LEFT JOIN [dbo].[NguoiLX_HoSo] h ON n.MaDK = h.MaDK WHERE 1 = 1 "; } else { queryString = "SELECT n.* FROM [dbo].[NguoiLX] n WHERE 1 = 1 "; } if (start != null) { queryString += " AND n.NgayTao>=@StartDate "; queryString += " AND n.NgayTao>=@StartDate "; } if (end != null) { queryString += " AND n.NgayTao<=@EndDate "; } if (trangthai != null && trangthai.Trim().Length > 0) { queryString += " AND h.TT_XULY=@status "; } if (cmnd != null && cmnd.Trim().Length > 0) { queryString += " AND n.SoCMT like @cmnd "; } if (lstMaDv != null && lstMaDv.Trim().Length > 0) { queryString += " AND n.DonViNhanHSo IN (" + lstMaDv + ") "; } SqlConnection connection = Ultils.GetDBConnection(); SqlCommand command = new SqlCommand(queryString, connection); if (start != null) { command.Parameters.AddWithValue("@StartDate", start); } if (end != null) { end = end.AddDays(1); command.Parameters.AddWithValue("@EndDate", end); } if (trangthai != null && trangthai.Trim().Length > 0) { command.Parameters.AddWithValue("@status", trangthai); } if (cmnd != null && cmnd.Trim().Length > 0) { command.Parameters.AddWithValue("@cmnd", "%" + cmnd.Trim() + "%"); } connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { NGUOI_LX obj = null; int i = 1; while (reader.Read()) { obj = new NGUOI_LX(); obj.STT = i++; if (!reader.IsDBNull(reader.GetOrdinal("MaDK"))) { obj.MADK = reader.GetString(reader.GetOrdinal("MaDK")); } else { obj.MADK = ""; } if (!reader.IsDBNull(reader.GetOrdinal("DonViNhanHSo"))) { obj.DV_NHAN_HS = reader.GetString(reader.GetOrdinal("DonViNhanHSo")); } else { obj.DV_NHAN_HS = ""; } if (!reader.IsDBNull(reader.GetOrdinal("HoDemNLX"))) { obj.HO_DEM_NLX = reader.GetString(reader.GetOrdinal("HoDemNLX")); } else { obj.HO_DEM_NLX = ""; } if (!reader.IsDBNull(reader.GetOrdinal("TenNLX"))) { obj.TEN_NLX = reader.GetString(reader.GetOrdinal("TenNLX")); } else { obj.TEN_NLX = ""; } if (!reader.IsDBNull(reader.GetOrdinal("HoVaTen"))) { obj.HO_VA_TEN = reader.GetString(reader.GetOrdinal("HoVaTen")); } else { obj.HO_VA_TEN = ""; } if (!reader.IsDBNull(reader.GetOrdinal("MaQuocTich"))) { obj.MA_QUOC_TICH = reader.GetString(reader.GetOrdinal("MaQuocTich")); } else { obj.MA_QUOC_TICH = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NgaySinh"))) { obj.NGAY_SINH = DateTime.ParseExact(reader.GetString(reader.GetOrdinal("NgaySinh")), "yyyyMMdd", CultureInfo.InvariantCulture).ToString(Ultils.FORMAT_DATE); } else { obj.NGAY_SINH = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiTT"))) { obj.NOI_TT = reader.GetString(reader.GetOrdinal("NoiTT")); } else { obj.NOI_TT = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiTT_MaDVHC"))) { obj.NOI_TT_MA_DVHC = reader.GetString(reader.GetOrdinal("NoiTT_MaDVHC")); } else { obj.NOI_TT_MA_DVHC = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiTT_MaDVQL"))) { obj.NOI_TT_MA_DVQL = reader.GetString(reader.GetOrdinal("NoiTT_MaDVQL")); } else { obj.NOI_TT_MA_DVQL = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiCT"))) { obj.NOI_CT = reader.GetString(reader.GetOrdinal("NoiCT")); } else { obj.NOI_CT = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiCT_MaDVHC"))) { obj.NOI_CT_MA_DVHC = reader.GetString(reader.GetOrdinal("NoiCT_MaDVHC")); } else { obj.NOI_CT_MA_DVHC = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NoiCT_MaDVQL"))) { obj.NOI_CT_MA_DVQL = reader.GetString(reader.GetOrdinal("NoiCT_MaDVQL")); } else { obj.NOI_CT_MA_DVQL = ""; } if (!reader.IsDBNull(reader.GetOrdinal("SoCMT"))) { obj.SO_CMT = reader.GetString(reader.GetOrdinal("SoCMT")); } else { obj.SO_CMT = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NgayCapCMT"))) { obj.NGAY_CAP_CMT = reader.GetDateTime(reader.GetOrdinal("NgayCapCMT")).ToString(Ultils.FORMAT_DATETIME); } else { obj.NGAY_CAP_CMT = null; } if (!reader.IsDBNull(reader.GetOrdinal("NoiCapCMT"))) { obj.NOI_CAP_CMT = reader.GetString(reader.GetOrdinal("NoiCapCMT")); } else { obj.NOI_CAP_CMT = ""; } if (!reader.IsDBNull(reader.GetOrdinal("GhiChu"))) { obj.GHI_CHU = reader.GetString(reader.GetOrdinal("GhiChu")); } else { obj.GHI_CHU = ""; } if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) { obj.TRANG_THAI = Convert.ToInt32(reader.GetBoolean(reader.GetOrdinal("TrangThai"))); } else { obj.TRANG_THAI = null; } if (!reader.IsDBNull(reader.GetOrdinal("NguoiTao"))) { obj.NGUOI_TAO = reader.GetString(reader.GetOrdinal("NguoiTao")); } else { obj.NGUOI_TAO = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NguoiSua"))) { obj.NGUOI_SUA = reader.GetString(reader.GetOrdinal("NguoiSua")); } else { obj.NGUOI_SUA = ""; } if (!reader.IsDBNull(reader.GetOrdinal("NgayTao"))) { obj.NGAY_TAO = reader.GetDateTime(reader.GetOrdinal("NgayTao")).ToString(Ultils.FORMAT_DATETIME); } else { obj.NGAY_TAO = null; } if (!reader.IsDBNull(reader.GetOrdinal("NgaySua"))) { obj.NGAY_SUA = reader.GetDateTime(reader.GetOrdinal("NgaySua")).ToString(Ultils.FORMAT_DATETIME); } else { obj.NGAY_SUA = null; } if (!reader.IsDBNull(reader.GetOrdinal("GioiTinh"))) { obj.GIOI_TINH = reader.GetString(reader.GetOrdinal("GioiTinh")); } else { obj.GIOI_TINH = ""; } if (!reader.IsDBNull(reader.GetOrdinal("HoVaTenIn"))) { obj.HO_VA_TEN_IN = reader.GetString(reader.GetOrdinal("HoVaTenIn")); } else { obj.HO_VA_TEN_IN = ""; } if (!reader.IsDBNull(reader.GetOrdinal("SO_CMND_CU"))) { obj.SO_CMND_CU = reader.GetString(reader.GetOrdinal("SO_CMND_CU")); } else { obj.SO_CMND_CU = ""; } rtn.Add(obj); } } finally { // Always call Close when done reading. reader.Close(); } Console.WriteLine("getData: " + rtn.Count); return(rtn); }
public static String importNguoiLxeWithCommit(NGUOI_LX NguoiLx, List <NGUOILX_HOSO> ListNguoiLxHs, List <GIAY_TO> ListGiayTo) { SqlConnection connection = Ultils.GetDBConnectionImport(); // Start a local transaction. SqlTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction("importDbWithCommit"); NGUOI_LX_DA.insertDataString(connection, transaction, NguoiLx); //Ultils.testImport(bODY.ListNguoiLx, "NguoiLX"); Console.WriteLine("-------------- IMPORT NGUOI_LX DONE: "); int i = 1; foreach (NGUOILX_HOSO obj in ListNguoiLxHs) { Console.WriteLine("-------------- NGUOILX_HOSO DUONGDANANH " + i++ + ": " + obj.DUONGDANANH.ToString()); Console.WriteLine("-------------- insert NGUOILX_HOSO: " + NGUOILX_HOSO_DA.insertData(connection, transaction, obj)); } //Ultils.testImport(bODY.ListNguoiLxHs, "NguoiLX_HoSo"); Console.WriteLine("-------------- IMPORT ListNguoiLxHs DONE: "); foreach (GIAY_TO obj in ListGiayTo) { Console.WriteLine("-------------- GIAY_TO MADK " + i++ + ": " + obj.MADK.ToString()); Console.WriteLine("-------------- insert GIAY_TO: " + GIAY_TO_DA.insertData(connection, transaction, obj)); } // Attempt to commit the transaction. transaction.Commit(); return(""); } catch (Exception ex) { if (transaction != null) { try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } return(ex.Message); } finally { connection.Close(); } }