private void QueryDBRaw(String sqlStr)
{
DataTable dt = new DataTable();
SqlConnection cn = null;
SqlDataAdapter da = null;
cn = new SqlConnection(cgis_connstr);
try
{
cn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
Debug.Print(sqlStr);
SqlCommand cmd = new SqlCommand(sqlStr, cn);
da = new SqlDataAdapter(cmd);
dt = new DataTable();
DataSet dSet = new DataSet();
da.Fill(dt);
// build labels
//MailingLabel
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
MailingLabel ml = new MailingLabel();
//ml.laddr = (System.String)dt.Rows[i]["Site_Address"];
ml.lname = (System.String)dt.Rows[i]["owner"];
ml.laddr_1 = (System.String)dt.Rows[i]["PEFLADDR1"];
ml.laddr_2 = (System.String)dt.Rows[i]["PEFLADDR2"];
ml.laddr_3 = (System.String)dt.Rows[i]["PEFLADDR3"];
ml.lcity = (System.String)dt.Rows[i]["PEFLCITY"];
ml.lstate = (System.String)dt.Rows[i]["PEFLST"];
ml.lcntry = (System.String)dt.Rows[i]["PEFLCNTRY"];
ml.zip = (System.String)dt.Rows[i]["PEFLZIP5"];
ml.Cleanup();
label_al.Add(ml);
}
catch
{
}
}
}
try
{
cn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
private void QueryDB()
{
String sqlStr = "SELECT * FROM CAMVIEW_PropertyList ";
if (search_type == "pin")
{
sqlStr = sqlStr + " WHERE pin like '" + search_string + "%' ";
}
else if (search_type == "address")
{
sqlStr = sqlStr + " WHERE site_address like '%" + search_string + "%' ";
}
else if (search_type == "pinlist")
{
String[] pins = search_string.Split(',');
for (int i = 0; i < pins.Length; i++)
{
pins[i] = "'" + pins[i] + "'";
}
String newWhere = String.Join(",", pins);
newWhere = "(" + newWhere + ")";
sqlStr = sqlStr + " WHERE pin in " + newWhere;
}
else if (search_type == "street")
{
sqlStr = sqlStr + " WHERE site_address like '%" + search_string + "%' ";
}
else if (search_type == "owner")
{
sqlStr = sqlStr + " WHERE owner like '" + search_string + "%' ";
}
else if (search_type == "sub")
{
sqlStr = "";
sqlStr = sqlStr + "SELECT DISTINCT pl.* FROM CAMVIEW_PropertyList pl ";
sqlStr = sqlStr + " LEFT OUTER JOIN CAMVIEW_SubdivisionLookup sb ";
sqlStr = sqlStr + " ON pl.PRPROP=sb.PAPROP ";
sqlStr = sqlStr + " WHERE SUBDDS like '%" + search_string + "%'";
}
else if (search_type == "bus")
{
sqlStr = "";
sqlStr = sqlStr + "SELECT DISTINCT PIN, Site_Address,LEDESC,HMSTD,Last_Sale,OWFLNAME as owner FROM CAMVIEW_PropertyList pl ";
sqlStr = sqlStr + " LEFT OUTER JOIN dbo.PA_WBusName bs ";
sqlStr = sqlStr + " ON pl.PRPROP=bs.owflprop ";
sqlStr = sqlStr + " WHERE OWFLNAME like '%" + search_string + "%'";
}
else if (search_type == "leg")
{
sqlStr = sqlStr + " WHERE ledesc like '%" + search_string + "%'";
}
String pssql = sqlStr.Replace("SELECT", "");
String pageSql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY PIN ) AS RowNum, ";
pageSql += pssql;
pageSql += String.Format(") AS RowConstrainedResult WHERE RowNum >= {0} AND RowNum <= {1} ORDER BY RowNum ", qpage, qpage + 50);
DataTable dt = new DataTable();
SqlConnection cn = null;
SqlDataAdapter da = null;
cn = new SqlConnection(cgis_connstr);
try
{
cn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
Debug.Print(sqlStr);
SqlCommand cmd = new SqlCommand(pageSql, cn);
da = new SqlDataAdapter(cmd);
dt = new DataTable();
DataSet dSet = new DataSet();
da.Fill(dt);
// build labels
//MailingLabel
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
MailingLabel ml = new MailingLabel();
ml.laddr = (System.String)dt.Rows[i]["Site_Address"];
ml.lname = (System.String)dt.Rows[i]["owner"];
ml.laddr_1 = (System.String)dt.Rows[i]["PEFLADDR1"];
ml.laddr_2 = (System.String)dt.Rows[i]["PEFLADDR2"];
ml.laddr_3 = (System.String)dt.Rows[i]["PEFLADDR3"];
ml.lcity = (System.String)dt.Rows[i]["PEFLCITY"];
ml.lstate = (System.String)dt.Rows[i]["PEFLST"];
ml.lcntry = (System.String)dt.Rows[i]["PEFLCNTRY"];
ml.zip = (System.String)dt.Rows[i]["PEFLZIP5"];
ml.Cleanup();
label_al.Add(ml);
}
catch
{
}
}
}
try
{
cn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
