Excel与数据库交互技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/smileberry1/article/details/4880992

2009-10-23

10.取Excel数据

imporString = @"select 0 as id,里程碑类型 as milestone_type,系统编码 as con_systemcode,里程碑代码 as code,是否收付款 as is_recanddef,收付款比例 as recanddef_proportion,收付款金额 as recanddef_sum,批次关系 as batch_relation from [里程碑$] where 系统编码<>'' ";

11.去掉加上表约束

this.ContractDataSet.Tables["con_batch"].Constraints.Clear();

this.ContractDataSet.Tables["con_milestone"].Constraints.Add(new System.Data.UniqueConstraint("id", new System.Data.DataColumn[]

{ this.ContractDataSet.Tables["con_milestone"].Columns["id"] }, true));

2009-10-28

12．取Excel保留字问题

,备注 as [memo],说明内容 as [value]

13．Gridview控件某列值改变

protected override void gridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)

{

switch (e.Column.FieldName)

{

case "fccode":

if (e.Value != null && e.ToString().Length > 0)

{

decimal d_fc_er=1.000000m;

DataRow[] drs = currencyInfoDt.Select("fc_code='" + e.Value.ToString() + "'");

if (drs.Length > 0 && drs[0]["fc_er"]!=DBNull.Value)

{

d_fc_er = Convert.ToDecimal(drs[0]["fc_er"].ToString());

}

this.gridView1.SetRowCellValue(e.RowHandle, this.gridView1.Columns["exchrate"], drs[0]["fc_er"]);

}

break;

case "transit_prc"://单价

if (e.Value != null && e.ToString().Length > 0)

{

DataRow row = this.gridView1.GetDataRow(e.RowHandle);

row["transit_money"] = Convert.ToDecimal(e.Value.ToString()) * Convert.ToDecimal(row["transit_mile"].ToString());

}

break;

case "transit_mile"://里程

if (e.Value != null && e.ToString().Length > 0)

{

DataRow row = this.gridView1.GetDataRow(e.RowHandle);

row["transit_money"] = Convert.ToDecimal(e.Value.ToString()) * Convert.ToDecimal(row["transit_prc"].ToString());

}

break;

default:

break;

}

2009-10-29

14.弱类型和强类型（不用实体和用实体比较）

不用实体:

DataRow dr_con_innercon = ds.Tables["con_innercon"].NewRow();

i++;

dr_con_innercon["id"] = I;

ds.Tables["con_innercon"].Rows.Add(dr_con_innercon);

用实体:

Contract.Entity.Contract contract = dataSet as Contract.Entity.Contract;

Contract.Entity.Contract.con_descriptionRow dataRow = contract.con_description.Newcon_descriptionRow();

_iMaxID++;

dataRow.id = _iMaxID;

contract.con_description.Rows.Add(dataRow);

15．Sql left函数用法

left(运输方式,2) as shiptype,

2009-10-30

16. SQL iif 语句用法

iif(len(运输方式) > 0,left(运输方式,2),0) as shiptype,

17. Excel 操作

数据à有效性à序列

01汽运,02铁运,03航空

2009-10-31

18.获取gridview显示的数据

int rowsCount = gridview.RowCount;

int colsCount = gridview.Columns.Count - 1;

for (int i = 0; i < rowsCount;i++ )

{

DataRow dr = ContractTable.NewRow();

for (int j = 0; j < colsCount; j++)

{

dr[j] = gridview.GetRowCellDisplayText(i, gridview.Columns[j]).ToString();

}

ContractTable.Rows.Add(dr);

}

19.二维数组转成Datatable

string[,] StringArray = new string[rowsCount, colsCount];

for (int a = 0; a < StringArray.GetLength(0); a++)

{

DataRow dr = ContractTable.NewRow();

for (int b = 0; b < StringArray.GetLength(1); b++)

{

dr[b] = StringArray[a, b];

}

ContractTable.Rows.Add(dr);

}

20，XML数据生成表结构字段

DataSet temp = new DataSet();

string fileName = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Configure//" + ConfigureConstString.Con_innerexesString);

temp.ReadXml(fileName);

foreach (DataRow dr in temp.Tables[0].Rows)

{

ContractTable.Columns.Add(dr["Caption"].ToString(), typeof(string));

}

2009-11-4

21.sql查询带有 ’ 的字符串

Select * from 表名 where 字段 = ‘I’’m a boy’;

22.sql通配符查询

select * from con_contract where concode like 'H[a-z][^m]%'

23.用having语句查询重复记录和记录条数

Select count(*),#### from *** group by &&& having count(*) >1

23. group by all 不符合条件的也显示

24. 删除表中一列字段

alter table con_projectsurveillance drop column retcode

25. 取字段长度

select col_length('con_contract','con_systemcode')

26.取表中某一列名

select col_name(object_id('con_contract'),1)

27.只显示列信息

set fmtonly on

select * from con_contract

28.时间返回加一天

select dateadd(day,1,update_datetime) as update_datetime from con_contract

29.时间返回只当前日期差几天

select datediff(day,update_datetime,getdate()) as update_datetime from con_contract

30.时间返回当前月份

select datename(month,getdate()) as 'Month Name'

31.是NULL值用返回0

select isnull(con_evacode,0) from con_contract

32.性别是男的用NULL来代替

select nullif(sex,'男') as sex from students

33.返回查询结构前几名或百分比

select top 10 percent * from con_contract

34.不等于查询

select * from con_contract where not con_systemcode = 'SDJ1[2009]021'

select * from con_contract where con_systemcode <> 'SDJ1[2009]021'

35.case语句

select con_systemcode =

case

when con_systemcode > 20 then '3232'

when con_systemcode < 20 then '4343'

else '6665'

end

from con_contract

36.查询表中所有列名

select name from syscolumns where id =object_id('con_contract')

37.别名用法

select

con_systemcode "哈哈",

contype 哈哈,

contractname'哈哈',

subtype as 哈哈

from con_contract

38.类型转换

select cast( con_systemcode as varchar(100)) from con_contract

select * from con_contract where convert(char(50),con_systemcode) like '%d%'

2009-11-9

39.重新获得焦点

this.ActiveWrapper.Form.gdvBalancePlanList.FocusedRowHandle = -1;

DataSet ds = new DataSet();

ds = this._mileproxy.GetMilesDescription();

string name = this.ActiveWrapper.Form.luereMilestoneCode.GetDisplayValueByKeyValue(this.ActiveWrapper.Form.gdvBalancePlanList.GetRowCellValue(this.ActiveWrapper.Form.gdvBalancePlanList.FocusedRowHandle,this.ActiveWrapper.Form.colmilestone_code)) as string;

foreach (DataRow dr in ds.Tables[0].Rows)

{

if (name == dr["dictionaryname"].ToString())

{

this.ActiveWrapper.Form.gdvBalancePlanList.SetFocusedRowCellValue(this.ActiveWrapper.Form.colmiles_description, dr["memo"].ToString());

}

2009-11-20

40.模糊查询有无Append不同语句

filter.Add("insureid like" +DbConvert.ToSqlString("%"+filter["insureidnew"].ToString()+"%"));

sb.Append("concode like '%").Append(filter["concode"].ToString()).Append("%'");

sb.Append(" and contractname like ").Append(DbConvert.ToSqlString('%' + filter["contractname"].ToString() + '%'));

41.清空时间列