ADO.NET 2.0 利用 partial class 擴充 TableAdapter method
TableAdapter如下：
C# Code
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
namespace ABCTableAdapters
{ public class myBOOTableAdapter : BOOTableAdapter { public myBOOTableAdapter() { this.conn = (SqlConnection)getConnFactory.GetDBConnection(); } } public partial class BOOTableAdapter { protected SqlConnection conn = new SqlConnection(); private SqlDataAdapter m_myAdapter; protected SqlDataAdapter myAdapter { get { if (this.m_myAdapter == null) { this.m_myAdapter = new SqlDataAdapter(); this.InitAdapter(); DataTableMapping tableMapping = new DataTableMapping(); tableMapping.SourceTable = "Table"; tableMapping.DataSetTable = "BOO"; for (int i = 0; i < _adapter.TableMappings[0].ColumnMappings.Count; i++) tableMapping.ColumnMappings.Add(_adapter.TableMappings[0].ColumnMappings[i].SourceColumn, _adapter.TableMappings[0].ColumnMappings[i].DataSetColumn); m_myAdapter.TableMappings.Add(tableMapping); } return this.m_myAdapter; } } //給 gridview 用的 select metohd [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Select, true)] public virtual ABC.BOODataTable myGetDatabyCond(string key_field, string is_enable) { string sqlStr = @" SELECT * FROM BOO WHERE (key_field = @key_field OR ISNULL(@key_field, '') = '') AND (is_enable = @is_enable OR ISNULL(@is_enable, '') = '') "; SqlCommand cmd = new SqlCommand(sqlStr, conn); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add(new SqlParameter("@key_field", SqlDbType.VarChar, 7, ParameterDirection.Input, 0, 0, "key_field", DataRowVersion.Current, false, key_field == null ? "" : key_field, "", "", "")); cmd.Parameters.Add(new SqlParameter("@is_enable", SqlDbType.VarChar, 1, ParameterDirection.Input, 0, 0, "is_enable", DataRowVersion.Current, false, is_enable == null ? "" : is_enable, "", "", "")); myAdapter.SelectCommand = cmd; ABC.BOODataTable dataTable = new ABC.BOODataTable(); myAdapter.Fill(dataTable); return dataTable; } //取某一個 row 的 metohd public virtual ABC.BOORow myGetDataRow(string key_field) { ABC.BOODataTable dt = this.myGetDatabyCond(key_field,""); if (dt.Rows.Count > 0) return (ABC.BOORow)dt.Rows[0]; else return null; } //批次update 一個 row 的 metohd public virtual int myUpdate(Training.HR_Training_Std_CourseRow row) { myAdapter.UpdateCommand = new SqlCommand(); myAdapter.UpdateCommand.Connection = conn; myAdapter.UpdateCommand.CommandText = @" UPDATE BOO SET field2 = @field2 WHERE (key_field = @key_field); "; myAdapter.UpdateCommand.CommandType = CommandType.Text; myAdapter.UpdateCommand.Parameters.Add(new SqlParameter("@key_field", SqlDbType.VarChar, 7, ParameterDirection.Input, 0, 0, "key_field", DataRowVersion.Current, false, null, "", "", "")); myAdapter.UpdateCommand.Parameters.Add(new SqlParameter("@field2", SqlDbType.NVarChar, 10, ParameterDirection.Input, 0, 0, "field2", DataRowVersion.Current, false, null, "", "", "")); myAdapter.InsertCommand = new SqlCommand(); myAdapter.InsertCommand.Connection = conn; myAdapter.InsertCommand.CommandText = @" INSERT INTO BOO ([key_field], [field2]) VALUES (@key_field, @field2); "; myAdapter.InsertCommand.CommandType = CommandType.Text; myAdapter.InsertCommand.Parameters.Add(new SqlParameter("@key_field", SqlDbType.VarChar, 7, ParameterDirection.Input, 0, 0, "key_field", DataRowVersion.Current, false, null, "", "", "")); myAdapter.InsertCommand.Parameters.Add(new SqlParameter("@field2", SqlDbType.NVarChar, 10, ParameterDirection.Input, 0, 0, "field2", DataRowVersion.Current, false, null, "", "", "")); DataRow[] row_arr = new DataRow[1]; row_arr[0] = (DataRow)row; try { int returnValue = myAdapter.Update(row_arr); return returnValue; } finally { myAdapter.InsertCommand.Connection.Close(); myAdapter.UpdateCommand.Connection.Close(); } } //call sql stored procedure 的 methd public int ImportData(string modify_user) { BaseBLL bl = new BaseBLL(); SqlParameter[] sp = { new SqlParameter("@modify_user", SqlDbType.VarChar, 10, ParameterDirection.Input, 0, 0, "modify_user", DataRowVersion.Current, false, modify_user, "", "", "") }; int r = bl.ExecuteByStoredProcedure("sp_Import_BOO", sp); return r; } } } |
使用方式如下：
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
// 給Server 端 asp.net 程式使用
ABCTableAdapters.myBOOTableAdapter da = new ABCTableAdapters.myBOOTableAdapter(); ABC.BOODataTable dt = new ABC.BOODataTable(); ABC.BOORow row; // update //row = da.myGetDataRow(key_field.Text,); //dt.AddBOORow(row); // insert row = dt.NewBOORow(); row.field2 = field2.Text; da.myUpdate(row) // 給Gridview 用的 ObjectDataSource <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="myGetDatabyCond" TypeName="ABCTableAdapters.myBOOTableAdapter" onselected="ObjectDataSource1_Selected"> <SelectParameters> <asp:ControlParameter ControlID="Qtxtkey_field" Name="key_field" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="Qtxtis_enable" Name="is_enable" PropertyName="Text" Type="String" /> </SelectParameters> </asp:ObjectDataSource> |
