1.Model
public class Table_DJB
{
public int DJB_OBJECTID { get; set; }
/// <summary>
/// 登记表唯一编号
/// </summary>
public int DJB_SLLMLDZKDJB_BH { get; set; }
public int DJB_QL_BH { get; set; }
/// <summary>
/// 登记表对应“林权证”编号
/// </summary>
public int DJB_LQZ_BH { get; set; }
public int DJB_DJBZT { get; set; }
public string DJB_SQBBH { get; set; }
public int DJB_YXH { get; set; }
public string DJB_LDSYQQLR { get; set; }
public string DJB_LDSHIYQQLR { get; set; }
public string DJB_SLHLMSYQQLR { get; set; }
public string DJB_SLHLMSHIYQQLR { get; set; }
public string DJB_ZL { get; set; }
public string DJB_XDM { get; set; }
public string DJB_LB { get; set; }
public string DJB_XB { get; set; }
public double DJB_MJ { get; set; }
public string DJB_ZYSZ { get; set; }
public int DJB_ZS { get; set; }
public string DJB_LZ { get; set; }
public int DJB_LDSYQ { get; set; }
public DateTime DJB_ZZRQ { get; set; }
public string DJB_SZD { get; set; }
public string DJB_SZN { get; set; }
public string DJB_SZX { get; set; }
public string DJB_SZB { get; set; }
public string DJB_ZJ { get; set; }
public string DJB_JBR { get; set; }
public DateTime DJB_JBRQSRQ { get; set; }
public string DJB_FZR { get; set; }
public DateTime DJB_FZRQSRQ { get; set; }
public string DJB_JGY { get; set; }
}2.DAL
public class Table_LQZ_DAL
{
public Table_LQZ ToModel(OracleDataReader pDataReader,int pQLR_BH)
{
Table_LQZ tableLQZ = new Table_LQZ();
//林权证编号
tableLQZ.LQZ_LQZ_BH = Convert.ToInt32(SqlHelper.FromDbValue(pDataReader["SERE_ID"]));
//权利人编号
tableLQZ.LQZ_QLR_BH = pQLR_BH;
//林权证状态
tableLQZ.LQZ_LQZZT = 1;
//林权证编号
if (SqlHelper.FromDbValue(pDataReader["SERE_LQZBH"]) != null)
{
tableLQZ.LQZ_ZSBH = (string)SqlHelper.FromDbValue(pDataReader["SERE_LQZBH"]);
}
else
{
tableLQZ.LQZ_ZSBH = "";
}
string strND = "";
string strBH = "";
if (SqlHelper.FromDbValue(pDataReader["SERE_ND"]) != null)
{
strND = SqlHelper.FromDbValue(pDataReader["SERE_ND"]).ToString();
}
if (SqlHelper.FromDbValue(pDataReader["SERE_LQZH"]) != null)
{
strBH = SqlHelper.FromDbValue(pDataReader["SERE_LQZH"]).ToString();
}
//林权证证号
tableLQZ.LQZ_LQZZH = "林证字(" + strND + ")第" + strBH + "号";
//证书所有人
if (SqlHelper.FromDbValue(pDataReader["SERE_FR"]) != null)
{
tableLQZ.LQZ_ZSSYR = (string)SqlHelper.FromDbValue(pDataReader["SERE_FR"]);
}
else
{
tableLQZ.LQZ_ZSSYR = "";
}
//发证机构
if (SqlHelper.FromDbValue(pDataReader["SERE_FZJG"]) != null)
{
tableLQZ.LQZ_FZJG = SqlHelper.FromDbValue(pDataReader["SERE_FZJG"]).ToString();
}
else
{
tableLQZ.LQZ_FZJG = "";
}
//发证签字
tableLQZ.LQZ_FZQZ = null;
//发证日期
if (SqlHelper.FromDbValue(pDataReader["SERE_FZRQ"]) != null)
{
tableLQZ.LQZ_FZRQ = (DateTime)SqlHelper.FromDbValue(pDataReader["SERE_FZRQ"]);
}
else
{
}
return tableLQZ;
}
/// <summary>
/// 插入
/// </summary>
/// <param name="pQlr"></param>
public void Insert(Table_LQZ pLqz)
{
SqlHelper.ExecuteNonQuery(SqlHelper.XYLQ_Connstr, @"INSERT INTO LBMS_LQZ (LQZ_BH,QLR_BH,LQZZT,ZSBH,LQZZH,ZSSYR,FZJG,FZQZ,FZRQ)
VALUES(:LQZ_BH,:QLR_BH,:LQZZT,:ZSBH,:LQZZH,:ZSSYR,:FZJG,:FZQZ,:FZRQ)", new OracleParameter(":LQZ_BH", SqlHelper.ToDbValue(pLqz.LQZ_LQZ_BH)), new OracleParameter(":QLR_BH", SqlHelper.ToDbValue(pLqz.LQZ_QLR_BH)), new OracleParameter(":LQZZT", SqlHelper.ToDbValue(pLqz.LQZ_LQZZT)), new OracleParameter(":ZSBH", SqlHelper.ToDbValue(pLqz.LQZ_ZSBH)), new OracleParameter(":LQZZH", SqlHelper.ToDbValue(pLqz.LQZ_LQZZH)), new OracleParameter(":ZSSYR", SqlHelper.ToDbValue(pLqz.LQZ_ZSSYR)), new OracleParameter(":FZJG", SqlHelper.ToDbValue(pLqz.LQZ_FZJG)), new OracleParameter(":FZQZ", SqlHelper.ToDbValue(pLqz.LQZ_FZQZ)), new OracleParameter(":FZRQ", SqlHelper.ToDbValue(pLqz.LQZ_FZRQ)));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="pQlr"></param>
public void Update(int intOldQLR_BH, int intNewQLR_BH)
{
SqlHelper.ExecuteNonQuery(SqlHelper.XYLQ_Connstr, @"Update LBMS_LQZ set QLR_BH=:OldQLR_BH Where QLR_BH=:pNewQLR_BH", new OracleParameter(":OldQLR_BH", intOldQLR_BH), new OracleParameter(":pNewQLR_BH", intNewQLR_BH));
}
3.SqlHelper
public static class SqlHelper
{
//app.config文件的继承：
//public static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
/// <summary>
/// “襄阳林权”连接字符串
/// </summary>
public static readonly string XYLQ_Connstr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.26)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=lydb)));User Id=xylqyw; Password=xylqyw";
/// <summary>
/// “湖北林权”连接字符串
/// </summary>
public static readonly string HBLQ_Connstr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.26)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=lydb)));User Id=HBLZ_NEW; Password=HBLZ_NEW";
public static int ExecuteNonQuery(string connstr,string sql,
params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string connstr, string sql,
params SqlParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string connstr, string sql,
params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(parameters);
DataSet dataset = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
public static object FromDbValue(object value)
{
if (value == DBNull.Value)
{
return null;
}
else
{
return value;
}
}
public static object ToDbValue(object value)
{
if (value == null)
{
return DBNull.Value;
}
else
{
return value;
}
}
}
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
// 创建“权利人”与“林权证”映射视图
//CREATE OR REPLACE FORCE VIEW "HBLZ_NEW"."QLRTOLQZ" ("ABCID", "APRE_BOOKID") AS
//select min(APRE_ID) as ABCID, APRE_BOOKID from LQ_APPLYREGISTER t1 where t1.APRE_ZTID != 'F' and t1.apre_sdmid=1933 group by
// APRE_BOOKID
//1.处理APRE_BOOKID不为空的情况。相同APRE_BOOKID对应着同一个“权利人”。
// 此时的问题是“一个权利人可能对于多个林权证”这时候需要删除多余的权
// 利人，同时更新之前林权证对应的权利人编号（对应到保留的那个权利人的编号）。
private void button1_Click(object sender, EventArgs e)
{
double minutes = 0;
using (OracleConnection conn = new OracleConnection(SqlHelper.HBLQ_Connstr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from QLRTOLQZ where APRE_BOOKID is not null";
OracleDataReader pReader = cmd.ExecuteReader();
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start(); // 开始监视代码运行时间
if (pReader.HasRows)
{
Table_QLRToLQZ_DAL pQlrToLqz_DAL = new Table_QLRToLQZ_DAL();
int i = 0;
while (pReader.Read())
{
i++;
Table_QLRToLQZ pTable_QLRTOLQZ = new Table_QLRToLQZ();
pTable_QLRTOLQZ = pQlrToLqz_DAL.ToModel(pReader);
//导出“林权证”
outPutLQZ(conn, pTable_QLRTOLQZ, i);
//导出“权利人”
outPutQLR(conn, pTable_QLRTOLQZ, i);
}
}
stopwatch.Stop(); // 停止监视
TimeSpan timespan = stopwatch.Elapsed; // 获取当前实例测量得出的总时间
minutes = timespan.TotalMinutes; // 总分钟
}
}
MessageBox.Show("导入完成！共耗时" + minutes + "分钟", "提示");
}
/// <summary>
/// 导出“权利人”
/// </summary>
private static void outPutQLR(OracleConnection con, Table_QLRToLQZ pTable,int j)
{
using (OracleCommand QLRcmd = con.CreateCommand())
{
QLRcmd.CommandText = "select * from LQ_APPLYREGISTER where APRE_ID=" + pTable.MinAPRE_ID;
OracleDataReader pQLRReader = QLRcmd.ExecuteReader();
if (pQLRReader.HasRows)
{
Table_QLR_DAL pQlr_DAL = new Table_QLR_DAL();
while (pQLRReader.Read())
{
Table_QLR pTable_QLR = new Table_QLR();
pTable_QLR = pQlr_DAL.ToModel(pQLRReader, j);
pQlr_DAL.Insert(pTable_QLR);
}
}
}
}
/// <summary>
/// 导出“林权证”
/// </summary>
/// <param name="conn"></param>
/// <param name="cmd"></param>
/// <param name="pTable_QLRTOLQZ"></param>
/// <param name="i"></param>
private static void outPutLQZ(OracleConnection con, Table_QLRToLQZ pTable, int j)
{
using (OracleCommand LQZcmd = con.CreateCommand())
{
LQZcmd.CommandText = "select * from LQ_SENDREGISTER where SERE_ID=" + pTable.APRE_BOOKID;
OracleDataReader pLQZReader = LQZcmd.ExecuteReader();
if (pLQZReader.HasRows)
{
Table_LQZ_DAL pLqz_DAL = new Table_LQZ_DAL();
while (pLQZReader.Read())
{
Table_LQZ pTable_LQZ = new Table_LQZ();
pTable_LQZ = pLqz_DAL.ToModel(pLQZReader, j);
pLqz_DAL.Insert(pTable_LQZ);
}
}
}
}
private static void UpdateTable_LQZ()
{
}
private static void DeleteRepeatRecord()
{
DataTable pQLRDataTable = null;
int line = 0;
while (true)
{
pQLRDataTable = SqlHelper.ExecuteDataTable(SqlHelper.XYLQ_Connstr, "select QLR_BH,DWGR,FDDBRZYFZR,SFZBH from LBMS_QLR order by SFZBH desc,QLR_BH asc");
if (line == pQLRDataTable.Rows.Count) break;
line = pQLRDataTable.Rows.Count;
for (int i = 1; i < pQLRDataTable.Rows.Count; i++)
{
//第i-1行记录
string strDWGR = pQLRDataTable.Rows[i - 1]["DWGR"].ToString().Trim();
string strFDDBRZYFZR = "";
if (pQLRDataTable.Rows[i - 1]["FDDBRZYFZR"] != null)
{
strFDDBRZYFZR = pQLRDataTable.Rows[i - 1]["FDDBRZYFZR"].ToString().Trim();
}
string strSFZBH = pQLRDataTable.Rows[i - 1]["SFZBH"].ToString().Trim();
//第i行记录值
string strDWGRAdd = pQLRDataTable.Rows[i]["DWGR"].ToString().Trim();
string strFDDBRZYFZRAdd = "";
if (pQLRDataTable.Rows[i]["FDDBRZYFZR"] != null)
{
strFDDBRZYFZRAdd = pQLRDataTable.Rows[i]["FDDBRZYFZR"].ToString().Trim();
}
string strSFZBHAdd = pQLRDataTable.Rows[i]["SFZBH"].ToString().Trim();
//排序后，如果第i行记录与第i+1行记录重复，则删除权利人第i+1行记录，且更新“林权证表（LBMS_LQZ）”对应的权利人编号（此处赋第i行QLR_BH）
if (strDWGR == strDWGRAdd && strFDDBRZYFZR == strFDDBRZYFZRAdd && strSFZBH == strSFZBHAdd)
{
int DelQLR_BH = Convert.ToInt32(pQLRDataTable.Rows[i]["QLR_BH"]);
int OLDQLR_BH = Convert.ToInt32(pQLRDataTable.Rows[i - 1]["QLR_BH"]);
int NewQLR_BH = Convert.ToInt32(pQLRDataTable.Rows[i]["QLR_BH"]);
Table_QLR_DAL pQlr_Dal = new Table_QLR_DAL();
pQlr_Dal.Delete(DelQLR_BH);
Table_LQZ_DAL pLqz_Dal = new Table_LQZ_DAL();
pLqz_Dal.Update(OLDQLR_BH, NewQLR_BH);
break;
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
DeleteRepeatRecord();
MessageBox.Show("删除重复数据完成！", "提示");
}
}<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="XiangYang_LQGX.Properties.Settings.ConnectionString"
connectionString="Data Source=192.168.2.26/lydb;Persist Security Info=True;User ID=HBLZ_NEW;Password=HBLZ_NEW;Unicode=True"
providerName="System.Data.OracleClient" />
<add name="XiangYang_LQGX.Properties.Settings.ConnectionString1"
connectionString="Data Source=192.168.2.26/lydb;Persist Security Info=True;User ID=xylqyw;Password=xylqyw;Unicode=True"
providerName="System.Data.OracleClient" />
</connectionStrings>
</configuration>
1514
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
