[C#]DataTable常用操作总结

 

【引言】项目中经常使用DataTable,发现有好多东西,需要整理下.

 

 

 

   protected void Page_Load(object sender, EventArgs e) { //①Create a DataTable DataTable dt = new DataTable("Table_AX"); //②Add columns for DataTable //Method 1 dt.Columns.Add("column0", System.Type.GetType("System.String")); //Method 2 DataColumn dc = new DataColumn("column1", System.Type.GetType("System.Boolean")); dt.Columns.Add(dc); //③Add rows for DataTable //★Initialize the row DataRow dr = dt.NewRow(); dr["column0"] = "AX"; dr["column1"] = true; dt.Rows.Add(dr); //★Doesn't initialize the row DataRow dr1 = dt.NewRow(); dt.Rows.Add(dr1); //④Select row //Search the second row 如果没有赋值,则用is null来select DataRow[] drs = dt.Select("column1 is null"); DataRow[] drss = dt.Select("column0 = 'AX'"); //⑤Copy DataTable include data DataTable dtNew = dt.Copy(); //⑥Copy DataTable only scheme DataTable dtOnlyScheme = dt.Clone(); //⑦Operate one row //★都是对dt的操作 //Method 1 DataRow drOperate = dt.Rows[0]; drOperate["column0"] = "AXzhz"; drOperate["column1"] = false; //Method 2 drOperate[0] = "AXzhz"; drOperate[1] = false; //Method 3 dt.Rows[0]["column0"] = "AXzhz"; dt.Rows[0]["column1"] = false; //Method 4 dt.Rows[0][0] = "AXzhz"; dt.Rows[0][1] = false; //⑧Evaluate another DataTable's row to current Datatable dtOnlyScheme.Rows.Add(dt.Rows[0].ItemArray); //⑨Use Rowstate //■■■■■■■■■■■■■■■■■■■■■■■■■■■■ //不知道怎么才能使Row的State变为DataRowState.Deleted //More further,怎样设置Row的State //■■■■■■■■■■■■■■■■■■■■■■■■■■■■ if (dt.Rows[0].RowState == DataRowState.Unchanged) { //Your logic } //⑩Convert to string System.IO.StringWriter sw = new System.IO.StringWriter(); System.Xml.XmlTextWriter xw = new System.Xml.XmlTextWriter(sw); dt.WriteXml(xw); string s = sw.ToString(); //ⅰ.string convert to DataTable【Doesn't achieve it】 //■■■■■■■■■■■■■■■■■■■■■■■■■■■■ //没有把string成功转换为DataTable ■■已实现,参见追加■■ //■■■■■■■■■■■■■■■■■■■■■■■■■■■■ //DataTable dtConvert = new DataTable(); //System.IO.StringReader stream = new System.IO.StringReader(s); //System.Xml.XmlReader xtr = new System.Xml.XmlTextReader(stream); //dtConvert.ReadXml(xtr); //ⅱ.Filter DataTable //It's so strange that the second row has been filtered //the second row show in GridView never //It means null field will be filter always. //Filter the all conditions dt.DefaultView.RowFilter = "column1 <> true"; //dt.DefaultView.RowFilter = "column1 = true"; dt.DefaultView.RowStateFilter = DataViewRowState.Added; //ⅲ.Sort row //Stupid method DataRow[] drsss = dt.Select(String.Empty, "column0 DESC , column1 ASC"); //Clever method dt.DefaultView.Sort = "column0 , column1 ASC"; //ⅳ.Bind DataTable //绑定的其实是DefaultView gvTestDataTable.DataSource = dt; gvTestDataTable.DataBind(); } 

【追加】判断一个字符串是否为DataTable的列名

dtInfo.Columns.Contains("AX");

【追加】DataTable与XML的转换

       protected void Page_Load(object sender, EventArgs e) { DataTable dt_AX = new DataTable(); //dt_AX.Columns.Add("Sex", typeof(System.Boolean)); //DataRow dr = dt_AX.NewRow(); //dr["Sex"] = true; //dt_AX.Rows.Add(dr); string xml=ConvertBetweenDataTableAndXML_AX(dt_AX); DataTable dt = ConvertBetweenDataTableAndXML_AX(xml); } public string ConvertBetweenDataTableAndXML_AX(DataTable dtNeedCoveret) { System.IO.TextWriter tw = new System.IO.StringWriter(); //if TableName is empty, WriteXml() will throw Exception. dtNeedCoveret.TableName=dtNeedCoveret.TableName.Length==0?"Table_AX":dtNeedCoveret.TableName; dtNeedCoveret.WriteXml(tw); dtNeedCoveret.WriteXmlSchema(tw); return tw.ToString(); } public DataTable ConvertBetweenDataTableAndXML_AX(string xml) { System.IO.TextReader trDataTable = new System.IO.StringReader(xml.Substring(0, xml.IndexOf("<?xml"))); System.IO.TextReader trSchema = new System.IO.StringReader(xml.Substring(xml.IndexOf("<?xml"))); DataTable dtReturn = new DataTable(); dtReturn.ReadXmlSchema(trSchema); dtReturn.ReadXml(trDataTable); return dtReturn; }

 【追加】排序的好方法

            dt.DefaultView.Sort = "ID ,Name ASC";

            dt=dt.DefaultView.ToTable();