[C#] DataTable에서 유용하게 사용 되는 것들

DB저장시 널인지 체크

row["aaaa"] == DBNull.Value ? null : ((DateTime)row["aaaa"]).ToString("yyyyMMdd")

--------------------------------------------------------------------------------------

 

최대값 구하기DataTable의 int형 컬럼에 null 입력하기

ds.Tables[0].Columns["COLNM"].AllowDBNull = true;    //안해도 될 것 같은데 혹시 몰라서 DataRow newRow; newRow["COLNM"] = A == B ? 123 : DBNull.Value;

--------------------------------------------------------------------------------------

 

최대값 구하기

Tables[0].Select("컬럼1='조건1'").Max(x => (int)x["컬럼1"])

--------------------------------------------------------------------------------------

 

Select로 원하는 데이터 조회 

DataRow[] drs = dt.Select("(detailID = 1) AND (detailTypeID = 2)"); var rows = dthiddenColumn.Select("UseYN='Y'", "ColumnName ASC");      rows = dt.Select(string.Format("ITEM_CD = '{0}'",code));

--------------------------------------------------------------------------------------

 

조건에 대해서 선별, 정렬 그리고 DataTable로 변환

var drs = ds.Tables[0].Select("", "BUDAT, BELNR ASC");                                bindTable = drs.CopyToDataTable();

------------------------------------------------------------------------------------- 

 

DaTable Linq

var dt = (from row in dtPrintCnt1.AsEnumerable()           where row.Field<string>("CD").ToString() == changeDt.Rows[i]["CD1"].ToString()             &&                                                     row.Field<string>("RPT").ToString() != changeDt.Rows[i]["Cnt1"].ToString()                             select row); </string></string>

--------------------------------------------------------------------------------------

 

 

데이터  컬럼 정의 및 데이터 넣기

DataTable tbl = new DataTable(); tbl.TableName = "Data"; tbl.Columns.Add("CODE", typeof(string)); tbl.Columns.Add("NAME", typeof(string)); tbl.Rows.Add(new object[] { "", "전체" }); tbl.Rows.Add(new object[] { "Y", "승인" }); tbl.Rows.Add(new object[] { "N", "미승인" });

-------------------------------------------------------------------------------------

 

DataSet복사 편하게

DataRow dr = bgvView.GetDataRow(bgvView.FocusedRowHandle); DataTable dt = new DataTable(); dt = bindTable.Clone(); DataRow row = dt.NewRow(); row.ItemArray = dr.ItemArray; dt.Rows.Add(row);

--------------------------------------------------------------------------------------

 

테이블 Dataset에 복사

DataSet ds = new DataSet();   DataTable dtDetail = dac.Test1(ItemCd, UserId).Tables[0].Copy(); dtDetail.TableName = "detail"; DataTable dtReference = dac.Test2(ItemCd, UserId).Tables[0].Copy(); dtReference.TableName = "reference";               ds.Tables.Add(dtDetail); ds.Tables.Add(dtReference);

--------------------------------------------------------------------------------------

 

다른 테이블에 있는 datarow를 새로운 테이블에 넣을 때 사용

var dr =  dtDetail.Select(string.Format("ColumnName = '{0}'", gvHistory.GetFocusedRowCellValue("ColumnName"))); dtDetailSelect.Clear(); foreach (DataRow d in dr)     dtDetailSelect.ImportRow(d);   grdDetail.DataSource = dtDetailSelect;

--------------------------------------------------------------------------------------

 

datarow 배열을 datatable로 변환한다.

var dr =  dtDetail.Select(string.Format("colName = '{0}'", gvHistory.GetFocusedRowCellValue("colName "))); dtDetailSelect = dr.CopyToDataTable();  grdDetail.DataSource = dtDetailSelect;