public static Int32 chkempty() { int result; DbCommand tmpdb = BaseDb.CreatetmpCommand(); tmpdb.CommandText = "select count(*) from location"; result = Convert.ToInt32(BaseDb.ExecuteScalar(tmpdb)); if (result == 0) { tmpdb.CommandText = "select count(*) from dataparameter"; result = Convert.ToInt32(BaseDb.ExecuteScalar(tmpdb)); if (result == 0) { tmpdb.CommandText = "select count(*) from avlbldatadet"; result = Convert.ToInt32(BaseDb.ExecuteScalar(tmpdb)); if (result == 0) { tmpdb.CommandText = "select count(*) from misngdatadet"; result = Convert.ToInt32(BaseDb.ExecuteScalar(tmpdb)); } } } if (result == 0) { return(0); } else { return(1); } }
public static int getcount() { DbCommand cmd = BaseDb.CreatetmpCommand(); cmd.CommandText = "select count(*) from dataparameter where parameterdesc is null"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); return(cnt); }
public static Int32 validateuserid(string user) { DbCommand cmd = BaseDb.CreateSqlCommand(); cmd.CommandText = "select count(*) from usermaster where ltrim(rtrim(userid))='" + user + "'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); return(cnt); }
public static Int32 getparameter() { DbCommand tmpcmd = BaseDb.CreatetmpCommand(); tmpcmd.CommandText = "select count(*) from dataparameter where querytype='insert'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); return(cnt); }
public static Int32 getlocation() { DbCommand tmpcmd = BaseDb.CreatetmpCommand(); tmpcmd.CommandText = "select count(distinct realstat) from location where querytype='insert'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); return(cnt); }
public static Int32 chkduplicatedata(MetaDataEntity objm, string type) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from avlbldatadet where realstat='" + objm.Getstation + "' and parameterid='" + objm.getparameter + "' and type='" + type + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd)); return(chk); }
public static Int32 chkduplicateparameter(MetaDataEntity objm) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from DATAPARAMETER where parameterid='" + objm.getparameter + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd)); return(chk); }
public static Int32 chkduplicatelocation(MetaDataEntity objm) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from Location where realstat='" + objm.Getrealstationname + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); return(chk); }
public static int setvisibility() { DataAcessLayer dal = new DataAcessLayer(); DbCommand dbcom = BaseDb.CreateStrCommand(); dbcom.CommandText = "select count(*) from Location"; int i = Convert.ToInt32(BaseDb.ExecuteScalar(dbcom)); return(i); }
public static Int32 chkuser() { int cnt; DbCommand cmd = BaseDb.CreateSqlCommand(); cmd.CommandText = "select count(*) from viewusermaster1 where ACCESS_FOR_DATA_UPLOAD=1"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); return(cnt); }
public static Int32 chkverfied() { int i = 0, datacnt = 0, cnt = 0; DbCommand tmpcmd = BaseDb.CreatetmpCommand(); tmpcmd.CommandText = "select count(*) from location "; datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (datacnt > 0) { tmpcmd.CommandText = "select count(*) from location where verify=1"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); } tmpcmd.CommandText = "select count(*) from dataparameter "; datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (datacnt > 0) { tmpcmd.CommandText = "select count(*) from dataparameter where verify=1"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); } tmpcmd.CommandText = "select count(*) from avlbldatadet "; datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (datacnt > 0) { tmpcmd.CommandText = "select count(*) from avlbldatadet where verify=1"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); } tmpcmd.CommandText = "select count(*) from misngdatadet "; datacnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (datacnt > 0) { tmpcmd.CommandText = "select count(*) from misngdatadet where verify=1"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); } if (cnt > 0) { i = 1; } return(i); }
public static Int32 chktruncate() { int j = 0; DbCommand sqlcmd = BaseDb.CreateSqlCommand(); sqlcmd.CommandText = "select count(*) from dataparameter"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(sqlcmd).ToString()); if (cnt == 0) { sqlcmd.CommandText = "select count(*) from misngdatadet"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(sqlcmd).ToString()); if (cnt == 0) { sqlcmd.CommandText = "select count(*) from avlbldatadet"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(sqlcmd).ToString()); if (cnt == 0) { sqlcmd.CommandText = "select count(*) from datatype"; cnt = Convert.ToInt32(BaseDb.ExecuteScalar(sqlcmd).ToString()); if (cnt == 0) { j = 0; } else { j = 1; } } else { j = 1; } } else { j = 1; } } else { j = 1; } return(j); }
public static DataTable getavailability() { DbCommand cmd = BaseDb.CreatetmpCommand(); cmd.CommandText = "select realstat,datatypeid,parameterid,year from MISNGDATADET order by REALSTAT "; DataTable dt = BaseDb.ExecuteSelect(cmd); dt.Columns.Add("availability"); DataTable dtmsngyr = new DataTable(); DbCommand tmpcmd = BaseDb.CreatetmpCommand(); DbCommand localcmd = BaseDb.CreateStrCommand(); tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid from misngdatadet where cancel=0 "; DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd); dt1.Columns.Add("availability"); if (dt1.Rows.Count > 0) { foreach (DataRow dr in dt1.Rows) { string avlbl = ""; tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (cnt == 0) { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString()); if (cnt1 > 0) { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; DataTable dt2 = BaseDb.ExecuteSelect(localcmd); if (dt2.Rows.Count > 0) { avlbl = dt2.Rows[0]["from_date"] + " - " + dt2.Rows[0]["to_date"]; } } } else { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; DataTable dt3 = BaseDb.ExecuteSelect(tmpcmd); if (dt3.Rows.Count > 0) { avlbl = dt3.Rows[0]["from_date"] + " - " + dt3.Rows[0]["to_date"]; } } dr["availability"] = avlbl.ToString(); //tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; //DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd); //string avlblyear=""; //if (dtmsngavlbl.Rows.Count > 0) //{ // foreach (DataRow msngrow in dtmsngavlbl.Rows) // { // string avlblyear1 = msngrow["year"].ToString(); // avlblyear = avlblyear + avlblyear1 + ","; // } //} //if (avlblyear == "") //{ // dr["msngavlbl"] = ""; //} //else //{ // dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1); //} } } if (dt1.Rows.Count > 0) { foreach (DataRow drow in dt.Rows) { foreach (DataRow dr in dt1.Rows) { if (dr["realstat"].ToString() == drow["realstat"].ToString() && dr["parameterid"].ToString() == drow["parameterid"].ToString()) { drow["availability"] = dr["availability"].ToString(); } } } } return(dt); }
public static int updatedesc(string SaveLocation) { OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SaveLocation); conn.Open(); DataTable paradescdt = new DataTable(); OleDbCommand cmd = new OleDbCommand("SELECT UCase(LTrim(RTrim([Data Type]))) AS parameterid,Unit.[Data Type Description] as descr FROM Unit ", conn); OleDbDataReader rdr = null; rdr = cmd.ExecuteReader(); paradescdt.Load(rdr); try { DbCommand tmpcmd = BaseDb.CreatetmpCommand(); if (paradescdt.Rows.Count > 0) { for (int i = 0; i < paradescdt.Rows.Count; i++) { tmpcmd.CommandText = "select count(*) from dataparameter where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (cnt > 0) { tmpcmd.CommandText = "update dataparameter set parameterdesc='" + paradescdt.Rows[i]["descr"].ToString() + "' where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'"; BaseDb.ExecuteNonQuery(tmpcmd); } } } conn.Close(); DbCommand cmd1 = BaseDb.CreatetmpCommand(); cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null"; int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString()); return(cnt1); //{ // rowindex = 2 + index; // if ((int)rowindex > rg.Row) // { // break; // } // else // { // string abc = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex1]).Value2.ToString(); // string def = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex2]).Value2.ToString(); // index++; // DbCommand cmd = BaseDb.CreatetmpCommand(); // cmd.CommandText = "select count(*) from dataparameter where parameterid='" + abc + "'"; // int i = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); // if (i > 0) // { // cmd.CommandText = "update dataparameter set parameterdesc='" + def + "' where parameterid='" + abc + "'"; // BaseDb.ExecuteNonQuery(cmd); // } // } //} //DbCommand cmd1 = BaseDb.CreatetmpCommand(); //cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null"; //int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString()); //return (cnt); } catch (Exception ex) { throw (ex); } //string strconn = "Provider=Microsoft.Jet.OLEDB.4.0 ;" + // "Data Source=C:\\units1.xls;" + "Extended Properties= Excel 8.0"; }
public static DataTable getmisng() { DataTable dtmsngyr = new DataTable(); DbCommand tmpcmd = BaseDb.CreatetmpCommand(); DbCommand localcmd = BaseDb.CreateStrCommand(); tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid,tiunit,divider,frequency from misngdatadet where cancel=0 "; DataTable dt = BaseDb.ExecuteSelect(tmpcmd); dt.Columns.Add("year"); dt.Columns.Add("availability"); dt.Columns.Add("msngavlbl"); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { string myear = ""; string avlbl = ""; string avlblyear = ""; dtmsngyr.Clear(); string tiunit = dr["tiunit"].ToString(); if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; } else { tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } dtmsngyr = BaseDb.ExecuteSelect(tmpcmd); if (dtmsngyr.Rows.Count > 0) { foreach (DataRow msngrow in dtmsngyr.Rows) { string myear1 = msngrow["year"].ToString(); myear = myear + myear1 + ", "; } } if (myear == "") { dr["year"] = ""; } else { dr["year"] = myear.Substring(0, myear.Length - 2); } if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (cnt == 0) { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString()); if (cnt1 > 0) { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dt1 = BaseDb.ExecuteSelect(localcmd); if (dt1.Rows.Count > 0) { avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"]; } } } else { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; } else { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd); if (dt1.Rows.Count > 0) { avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"]; } } dr["availability"] = avlbl.ToString(); if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd); if (dtmsngavlbl.Rows.Count > 0) { foreach (DataRow msngrow in dtmsngavlbl.Rows) { string avlblyear1 = msngrow["year"].ToString(); avlblyear = avlblyear + avlblyear1 + ","; } } if (avlblyear == "") { dr["msngavlbl"] = ""; } else { dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1); } } } // tmpcmd.CommandText = "select distinct realstat,parameterid from misngdatadet where cancel=0 and querytype='update'"; return(dt); }