public string saveArea(mdlArea areainfo) { string result = ""; Db_Connection dbconn = new Db_Connection(); try { dbconn.openConnection(); string strSQL = @"SELECT ID FROM AREAS WHERE AREA=@AREA"; SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn); cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { result = "Area already exist"; } else { dr.Dispose(); cmd.Dispose(); strSQL = @"INSERT INTO AREAS(AREA,ACTIVE,ENCODEDBY,MODIFIEDBY,DATEMODIFIED,DATEENCODED,AREATYPE) VALUES(@AREA,@ACTIVE,@ENCODEDBY,@MODIFIEDBY,GETDATE(),GETDATE(),@AREATYPE);SELECT SCOPE_IDENTITY()"; cmd = new SqlCommand(strSQL, dbconn.DbConn); cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA; cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = areainfo.ACTIVE; cmd.Parameters.Add("ENCODEDBY", SqlDbType.VarChar).Value = "currentuser"; cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser"; cmd.Parameters.Add("AREATYPE", SqlDbType.VarChar).Value = areainfo.AREATYPE; var id = cmd.ExecuteScalar(); result = id.ToString(); } dr.Dispose(); cmd.Dispose(); dbconn.closeConnection(); return(result); } catch (Exception ex) { return(ex.ToString()); } finally { dbconn.closeConnection(); } }
public ActionResult getAreas() { List <mdlArea> model = new List <mdlArea>(); Db_Connection dbconn = new Db_Connection(); try { dbconn.openConnection(); string strSQL = @"SELECT dbo.areas.ID, dbo.areas.AREA, Case When dbo.areas.ACTIVE = 'A' Then 'Active' Else 'Inactive' End AS ACTIVE , dbo.areatype.AREATYPE, dbo.areas.MAP FROM dbo.areas LEFT OUTER JOIN dbo.areatype ON dbo.areas.AREATYPE = dbo.areatype.ID ORDER BY dbo.areas.AREA"; SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn); SqlDataReader dr = cmd.ExecuteReader(); mdlArea list = new mdlArea(); while (dr.Read()) { list = new mdlArea(); list.ID = dr["ID"].ToString(); list.AREA = dr["AREA"].ToString(); list.AREATYPE = dr["AREATYPE"].ToString(); list.ACTIVE = dr["ACTIVE"].ToString(); list.MAP = dr["MAP"].ToString(); model.Add(list); } dr.Dispose(); cmd.Dispose(); dbconn.closeConnection(); return(Json(model, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(model, JsonRequestBehavior.AllowGet)); } finally { dbconn.closeConnection(); } }
public ActionResult getAreaInfo(string id) { mdlArea model = new mdlArea(); Db_Connection dbconn = new Db_Connection(); try { dbconn.openConnection(); string strSQL = @"SELECT ID,AREA,AREATYPE,ACTIVE FROM AREAS WHERE ID=@ID"; SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn); cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = id; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); model.ID = dr["ID"].ToString(); model.AREATYPE = dr["AREATYPE"].ToString(); model.AREA = dr["AREA"].ToString(); model.ACTIVE = dr["ACTIVE"].ToString(); } dr.Dispose(); cmd.Dispose(); dbconn.closeConnection(); return(Json(model, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(model, JsonRequestBehavior.AllowGet)); } finally { dbconn.closeConnection(); } }
public ActionResult getAreaType() { List <mdlArea> model = new List <mdlArea>(); Db_Connection dbconn = new Db_Connection(); try { dbconn.openConnection(); string strSQL = @"SELECT ID,AREATYPE FROM AREATYPE WHERE ACTIVE='A' "; SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn); SqlDataReader dr = cmd.ExecuteReader(); mdlArea list = new mdlArea(); while (dr.Read()) { list = new mdlArea(); list.ID = dr["ID"].ToString(); list.AREATYPE = dr["AREATYPE"].ToString(); model.Add(list); } dr.Dispose(); cmd.Dispose(); dbconn.closeConnection(); return(Json(model, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(model, JsonRequestBehavior.AllowGet)); } finally { dbconn.closeConnection(); } }
public string updateArea(mdlArea areainfo) { string result = ""; Db_Connection dbconn = new Db_Connection(); try { dbconn.openConnection(); string strSQL = @"SELECT ID FROM AREAS WHERE AREA=@AREA"; SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn); cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); if (dr["ID"].ToString() != areainfo.ID) { result = "Area already exist"; } } dr.Dispose(); cmd.Dispose(); if (result == "") { strSQL = @"UPDATE AREAS SET AREA=@AREA,AREATYPE=@AREATYPE,ACTIVE=@ACTIVE,MODIFIEDBY=@MODIFIEDBY,DATEMODIFIED=GETDATE() WHERE ID=@ID"; cmd = new SqlCommand(strSQL, dbconn.DbConn); cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = areainfo.AREA; cmd.Parameters.Add("AREATYPE", SqlDbType.VarChar).Value = areainfo.AREATYPE; cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = areainfo.ACTIVE; cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser"; cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = areainfo.ID; cmd.ExecuteNonQuery(); dr.Dispose(); cmd.Dispose(); result = "Area successfully updated"; } dr.Dispose(); cmd.Dispose(); dbconn.closeConnection(); return(result); } catch (Exception ex) { return(ex.ToString()); } finally { dbconn.closeConnection(); } }