public int Update(工号表 工号表, string old_workno)
{
string sql =
"UPDATE 工号表 " +
"SET " +
" 工作令号 = @工作令号" +
" ,名称 = @名称"
+ ", noused = @noused"
+ ", nocalc =@nocalc"
+ ", 行业类别 = @行业类别"
+ ", 产品类别 = @产品类别"
+ ", 合同号 = @合同号"
+ ", 订货单位 = @订货单位"
+ ", 购货单位 = @购货单位"
+ ", 工号类型 = @工号类型"
+ " WHERE 工作令号 = @old_workno";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@工作令号", 工号表.工作令号)
, new SqlParameter("@名称", ToDBValue(工号表.称))
, new SqlParameter("@noused", ToDBValue(工号表.Noused))
, new SqlParameter("@nocalc", ToDBValue(工号表.Nocalc))
, new SqlParameter("@行业类别", ToDBValue(工号表.行业类别))
, new SqlParameter("@产品类别", ToDBValue(工号表.产品类别))
, new SqlParameter("@合同号", ToDBValue(工号表.合同号))
, new SqlParameter("@订货单位", ToDBValue(工号表.订货单位))
, new SqlParameter("@购货单位", ToDBValue(工号表.购货单位))
, new SqlParameter("@工号类型", ToDBValue(工号表.工号类型))
, new SqlParameter("@old_workno", ToDBValue(old_workno))
};
return(SqlHelper.ExecuteNonQuery(sql, para));
}
public 工号表 ToModel(SqlDataReader reader)
{
工号表 工号表 = new 工号表();
工号表.工作令号 = (string)ToModelValue(reader, "工作令号");
工号表.称 = (string)ToModelValue(reader, "名称");
工号表.Noused = (bool)ToModelValue(reader, "noused");
工号表.Nocalc = (bool)ToModelValue(reader, "nocalc");
工号表.行业类别 = (short)ToModelValue(reader, "行业类别");
工号表.产品类别 = (short)ToModelValue(reader, "产品类别");
工号表.合同号 = (string)ToModelValue(reader, "合同号");
工号表.购货单位 = (string)ToModelValue(reader, "购货单位");
工号表.订货单位 = (short)ToModelValue(reader, "订货单位");
工号表.工号类型 = (string)ToModelValue(reader, "工号类型");
return(工号表);
}
private void insertWorkno()
{
工号表DAL dal = new 工号表DAL();
工号表 mod = new 工号表();
mod.工作令号 = txt_workno.get_value();
mod.称 = txt_name.get_value();
mod.Noused = radioButton2.Checked;
mod.Nocalc = chk_calc.Checked;
mod.产品类别 = short.Parse(cmb_cplb.SelectedValue.ToString());
mod.行业类别 = short.Parse(cmb_hylb.SelectedValue.ToString());
mod.合同号 = txt_hth.get_value();
mod.订货单位 = short.Parse(cmb_dhdw.SelectedValue.ToString());
mod.购货单位 = txt_ghdw.get_value();
mod.工号类型 = cmb_worknotype.SelectedValue.ToString();
dal.Add(mod);
}
// 以上是手工增加的功能
public 工号表 Add(工号表 工号表)
{
string sql = "INSERT INTO 工号表 (工作令号,名称, noused, nocalc,行业类别,产品类别,合同号,订货单位,购货单位,工号类型) " +
"VALUES (@工作令号,@名称, @noused,@nocalc,@行业类别,@产品类别,@合同号,@订货单位,@购货单位, @工号类型)";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@工作令号", ToDBValue(工号表.工作令号)),
new SqlParameter("@名称", ToDBValue(工号表.称)),
new SqlParameter("@noused", ToDBValue(工号表.Noused)),
new SqlParameter("@nocalc", ToDBValue(工号表.Nocalc))
, new SqlParameter("@行业类别", ToDBValue(工号表.行业类别))
, new SqlParameter("@产品类别", ToDBValue(工号表.产品类别))
, new SqlParameter("@合同号", ToDBValue(工号表.合同号))
, new SqlParameter("@订货单位", ToDBValue(工号表.订货单位))
, new SqlParameter("@购货单位", ToDBValue(工号表.购货单位))
, new SqlParameter("@工号类型", ToDBValue(工号表.工号类型))
};
SqlHelper.ExecuteNonQuery(sql, para);
return(工号表);
}
private void button2_Click(object sender, EventArgs e)
{
if (!grd.has_selectrow())
{
MessageBox.Show("请先选中一个工号！");
return;
}
addflag = false;
工号表DAL dal = new 工号表DAL();
工号表 mod = new 工号表();
groupBox1.Enabled = true;
string workno = grd.get_value(grd.get_selectrow_index(), 0).ToString();
txt_workno.set_value(workno);
mod = dal.GetBy工作令号(workno);
old_workno = workno;
txt_name.set_value(mod.称);
radioButton1.Checked = !mod.Noused;
radioButton2.Checked = mod.Noused;
chk_calc.Checked = mod.Nocalc;
txt_hth.set_value(mod.合同号);
txt_ghdw.set_value(mod.购货单位);
cmb_cplb.SelectedValue = mod.产品类别;
cmb_dhdw.SelectedValue = mod.订货单位;
cmb_hylb.SelectedValue = mod.行业类别;
cmb_worknotype.SelectedValue = mod.工号类型;
//cmb_dhdw.set_value(grd.get_value(grd.get_selectrow_index(), 7).ToString());
//txt_ghdw.set_value(grd.get_value(grd.get_selectrow_index(), 8).ToString());
}
private void mydo(string filename)
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + txt_filename.get_value() + ";" + "Extended Properties=Excel 8.0;";
//OleDbConnection conn = new OleDbConnection(strConn);
//conn.Open();
//string strExcel = "";
//OleDbDataReader dr;
//OleDbCommand myCommand = new OleDbCommand();
//// 外贸用
//// strExcel = @"select 行业类别, 产品类别,订货单位, 工作令号,iif(isnull(合同号) ,'', 合同号), iif(isnull(购货单位名称),'',购货单位名称), iif(isnull(产品名称),'', 产品名称)
////from [工号信息$] ";
//strExcel = @"select 产品类别, iif(isnull(行业类别) ,'', 行业类别), 订货单位, 工作令号,iif(isnull(合同号) ,'', 合同号), iif(isnull(购货单位名称),'',购货单位名称), iif(isnull(产品名称),'', 产品名称) from [工号信息$] ";
//myCommand.Connection = conn;
//myCommand.CommandText = strExcel;
//dr = myCommand.ExecuteReader();
工号表DAL dal = new 工号表DAL();
工号表 mycls = new 工号表();
List <工号表> mylist = new List <工号表>();
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();;
Microsoft.Office.Interop.Excel.Workbook wk;
Microsoft.Office.Interop.Excel.Worksheet sh;
int myrow;
if (app == null)
{
return;
}
wk = app.Workbooks.Open(filename);
sh = wk.Worksheets["工号信息"];
myrow = 3;
try
{
while (((Range)sh.Cells[myrow, 4]).Text.ToString() != "")
{
mycls = new 工号表();
cmb_cp.Text = ((Range)sh.Cells[myrow, 1]).Text;
mycls.产品类别 = (short)cmb_cp.SelectedIndex;
cmb_hy.Text = ((Range)sh.Cells[myrow, 2]).Text;
mycls.行业类别 = (short)cmb_hy.SelectedIndex;
cmb_dept.Text = ((Range)sh.Cells[myrow, 3]).Text;
mycls.订货单位 = (short)cmb_dept.SelectedIndex;
mycls.工作令号 = ((Range)sh.Cells[myrow, 4]).Text;
mycls.合同号 = ((Range)sh.Cells[myrow, 5]).Text;
mycls.购货单位 = ((Range)sh.Cells[myrow, 6]).Text;
mycls.称 = ((Range)sh.Cells[myrow, 7]).Text;
mycls.工号类型 = "正常";
mycls.Nocalc = false;
mycls.Noused = false;
mylist.Add(mycls);
myrow++;
}
app.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
app.Quit();
return;
}
foreach (工号表 mycls1 in mylist)
{
if (dal.IsIn工号表(mycls1.工作令号))
{
dal.Update(mycls1, mycls1.工作令号);
}
else
{
dal.Add(mycls1);
}
}
MessageBox.Show("完成");
}