DataTable一些小應用_1
手動建立新的DataTable，輸入欄位名稱及一個二維陣列當值
/// <summary>
/// 建立datatable
/// </summary>
/// <param name="ColumnName">欄位名用逗號分隔</param>
/// <param name="value">data陣列 , rowmajor</param>
/// <returns>DataTable</returns>
public static DataTable CreateDataTable(string ColumnName, string[,] value)
{
/* 輸入範例
string cname = " name , sex ";
string[,] aaz = new string[4, 2];
for (int q = 0; q < 4; q++)
for (int r = 0; r < 2; r++)
aaz[q, r] = "1";
dataGridView1.DataSource = NewMediaTest1.Model.Utility.DataSetUtil.CreateDataTable(cname, aaz);
*/
int i,j;
DataTable ResultTable = new DataTable();
string[] sep = new string[] { "," };
string[] TempColName = ColumnName.Split(sep, StringSplitOptions.RemoveEmptyEntries);
DataColumn[] CName = new DataColumn[TempColName.Length];
for (i = 0; i < TempColName.Length; i++)
{
DataColumn c1 = new DataColumn(TempColName[i].ToString().Trim(), typeof(object));
ResultTable.Columns.Add(c1);
}
if (value != null)
{
for (i = 0; i < value.GetLength(0); i++)
{
DataRow newrow = ResultTable.NewRow();
for (j = 0; j < TempColName.Length; j++)
{
newrow[j] = string.Copy(value[i, j].ToString());
}
ResultTable.Rows.Add(newrow);
}
}
return ResultTable;
}建立空的DataTable從資料庫的表格定義
/// <summary>
/// 建立空的DataTable從資料庫的表格定義
/// </summary>
/// <param name="connectString">The connect string.</param>
/// <param name="TableName">Name of the table.</param>
/// <returns></returns>
public static DataTable CreateEmptyDataTableFromDb(string connectString, string TableName)
{
DataTable dt = new DataTable();
string SqlCmd = "Select * from " + TableName + " where 1 <> 1";
using (SqlConnection cn = new SqlConnection(connectString))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(SqlCmd, cn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
}
}
}
return dt;
}將SqlDataSource select之後的資料轉成DataTable
/// <summary>
/// 將SqlDataSource select之後的資料轉成DataTable
/// </summary>
/// <param name="Source">The source.</param>
/// <returns></returns>
public static DataTable SqlDataSourceToDataTable(SqlDataSource Source)
{
DataView dv = (DataView)Source.Select(DataSourceSelectArguments.Empty);
return dv.Table;
}得到查詢後的結果
/// <summary>
/// Gets the query result.
/// </summary>
/// <param name="connectString">The connect string.</param>
/// <param name="SqlCmd">The SQL CMD.</param>
/// <returns></returns>
public static DataTable GetQueryResult(string connectString, string SqlCmd)
{
DataTable dt = new DataTable();
using (SqlConnection cn = new SqlConnection(connectString))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(SqlCmd, cn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
}
}
}
return dt;
}將DataTable中選取的列轉為字串陣列
將字串陣列轉為DataRow根據DataTable定義
/// <summary>
/// 將DataTable中選取的列轉為字串陣列
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="SelectIndex">Index of the select.</param>
/// <returns></returns>
public static string[] DataTableRowToString(DataTable dt, int SelectIndex)
{
try
{
string[] result = new string[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
result[i] = dt.Rows[SelectIndex][i].ToString();
return result;
}
catch (Exception e)
{
return null;
}
}
/// <summary>
/// 將字串陣列轉為DataRow根據DataTable定義
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="InputData">The input data.</param>
/// <returns></returns>
public static DataRow StringToDataRow(DataTable dt, string[] InputData)
{
try
{
DataRow a = dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
a[i] = string.Copy(InputData[i]);
return a;
}
catch (Exception e)
{
return null;
}
}
選出DataRow從DataTable
/// <summary>
/// 選出DataRow從DataTable
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="Condition">The condition.</param>
/// <returns></returns>
public static DataRow[] SelectFromDataTable(DataTable dt, string Condition)
{
if (Condition != string.Empty)
return dt.Select(Condition);
else
return dt.Select();
}將DataTable根據欄位名稱順向排序
/// <summary>
/// 將DataTable根據欄位名稱順向排序
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="ColumnName">Name of the column.</param>
/// <returns></returns>
public static DataRow[] SortDataTableAsc(DataTable dt, string ColumnName)
{
return dt.Select("", ColumnName + " Asc");
}將DataTable根據欄位名稱逆向排序
/// <summary>
/// 將DataTable根據欄位名稱逆向排序
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="ColumnName">Name of the column.</param>
/// <returns></returns>
public static DataRow[] SortDataTableDesc(DataTable dt, string ColumnName)
{
return dt.Select("", ColumnName + " desc");
}將DataTable欄位名稱位置交換
/// <summary>
/// 將DataTable欄位名稱位置交換
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="ColumnName">Name of the column.</param>
/// <param name="ColumnIndex">Index of the column.</param>
/// <returns></returns>
public static DataTable ExchangeDataColumn(DataTable dtSource, string ColumnName, string ColumnIndex)
{
DataTable dt = new DataTable();
dt = dtSource;
//變更欄位
dt.Columns[ColumnName].SetOrdinal(Convert.ToInt16(ColumnIndex));
return dt;
}從DataTable找單主鍵值符合的資料
/// <summary>
/// 從DataTable找單主鍵值符合的資料.
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="ColumnName">Name of the column.</param>
/// <param name="ColumnValue">The column value.</param>
/// <returns></returns>
public static DataTable FindDataTableSingleKey(DataTable dtSource, string ColumnName, string ColumnValue)
{
DataTable dt = dtSource;
//1.設定主鍵
dt.PrimaryKey = new DataColumn[] { dt.Columns[ColumnName] };
//2.搜尋DataRow
DataRow dr = dt.Rows.Find(ColumnValue);
if (dr != null)
{
//3.將找到的資料放到另一個DataTable
DataTable newdt = new DataTable();
foreach (DataColumn column in dt.Columns)
{
newdt.Columns.Add(column.ToString());
}
DataRow newdr = newdt.NewRow();
int i = 0;
//讀取過濾的資料
newdr = newdt.NewRow();
foreach (object item in dr.ItemArray)
{
newdr[i] = dr.ItemArray[i];
i++;
}
newdt.Rows.Add(newdr);
return newdt;
}
else
{
return null;
}
}從DataTable找多重主鍵值符合的資料
/// <summary>
/// 從DataTable找多重主鍵值符合的資料.
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="ColumnName">Name of the column.</param>
/// <param name="ColumnValue">The column value.</param>
/// <returns></returns>
public static DataTable FindDataTableMultiKey(DataTable dtSource, string[] ColumnName, string[] ColumnValue)
{
DataTable dt = dtSource;
//1.設定多個主鍵
DataColumn[] MultiKey = new DataColumn[ColumnName.Length];
for (int j = 0; j < ColumnName.Length; j++)
MultiKey[j] = dt.Columns[ColumnName[j].ToString()];
dt.PrimaryKey = MultiKey;
//2.欲查詢的資料
object[] search = new object[ColumnValue.Length];
for (int i = 0; i < search.Length; i++)
{
search[i] = string.Copy(ColumnValue[i]);
}
//3.搜尋DataRow
DataRow dr = dt.Rows.Find(search);
if (dr != null)
{
//4.將找到的資料放到另一個DataTable
DataTable newdt = new DataTable();
foreach (DataColumn column in dt.Columns)
{
newdt.Columns.Add(column.ToString());
}
DataRow newdr = newdt.NewRow();
int i = 0;
//讀取過濾的資料
newdr = newdt.NewRow();
foreach (object item in dr.ItemArray)
{
newdr[i] = dr.ItemArray[i];
i++;
}
newdt.Rows.Add(newdr);
return newdt;
}
else
{
return null;
}
}將DataRow加入DataTable
/// <summary>
/// 將DataRow加入DataTable.
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public static DataTable MergeSearchDataToTable(DataTable dtSource, DataRow[] dr)
{
//建立新的DataTable
DataTable dt = new DataTable();
//建立新的DataTable Columns
foreach (DataColumn dc in dtSource.Columns)
{
dt.Columns.Add(dc.ToString());
dt.Columns[dc.ToString()].DataType = dc.DataType;
}
int i = 0; //讀取過濾的資料
foreach (DataRow item in dr)
{
DataRow row = dt.NewRow();
i = 0;
foreach (DataColumn dc in dtSource.Columns)
{
//建立DataRow的資料
row[dc.ToString()] = item.ItemArray[i];
i++;
}
dt.Rows.Add(row);
}
return dt;
}