C#存取数据为所欲为（五）
昨天没有写，看了下世界锦标赛----举重，69公斤级，廖辉，3枚金牌：抓举，挺举，总分。
前天女子48公斤级，也是中国的，不知道叫什么了，同样3枚金牌，都是绝对的实力，一点悬念都沒有！！！
兴奋之余颇有感慨----运动员，不，应该是中国的运动员不容易啊！！遥想当年一个人的奥运.....
有情不自禁的自豪感！！
好了，来到我们的正题上了，前几天把所有的基础类都完成了，今天来说说如何使用。
由于我的机器是ORACLE的，所以就以Oracle为例了，SQLSVR就不用说了，只是几个类名
不痛罢了。
我这里举例很基础，不过大伙可想些五花八门的歪点子，，呵呵，，只要能提高效率！！！
public class mmstbItem : DataAwareObjectTest.TableItem
{
public mmstbItem(): this(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty){ }
public mmstbItem(string mmstb001, string mmstb002, string mmstb003, string mmstb004, string mmstb005, string ROWid)
{
currentData.Add("tc_mmstb001", mmstb001);
currentData.Add("tc_mmstb002", mmstb002);
currentData.Add("tc_mmstb003", mmstb003);
currentData.Add("tc_mmstb004", mmstb004);
currentData.Add("tc_mmstb005", mmstb005);
currentData.Add("rowID", ROWid);
originalData.Add("tc_mmstb001", mmstb001);
originalData.Add("tc_mmstb002", mmstb002);
originalData.Add("tc_mmstb003", mmstb003);
originalData.Add("tc_mmstb004", mmstb004);
originalData.Add("tc_mmstb005", mmstb005);
originalData.Add("rowID", ROWid);
}
public string Crowid
{
get { return currentData["rowID"] as string; }
set { currentData["rowID"] = value; }
}
public string mmstb001
{
get { return currentData["tc_mmstb001"] as string; }
set
{
currentData["tc_mmstb001"] = value;
}
}
public string mmstb002
{
get { return currentData["tc_mmstb002"] as string; }
set
{
currentData["tc_mmstb002"] = value;
}
}
public string mmstb003
{
get { return currentData["tc_mmstb003"] as string; }
set
{
currentData["tc_mmstb003"] = value;
}
}
public string mmstb004
{
get { return currentData["tc_mmstb004"] as string; }
set
{
currentData["tc_mmstb004"] = value;
}
}
public string mmstb005
{
get { return currentData["tc_mmstb005"] as string; }
set
{
currentData["tc_mmstb005"] = value;
}
}
public string ommstb001
{
get { return originalData["tc_mmstb001"] as string; }
}
public string ommstb002
{
get { return originalData["tc_mmstb002"] as string; }
}
public string ommstb003
{
get { return originalData["tc_mmstb003"] as string; }
}
public string ommstb004
{
get { return originalData["tc_mmstb004"] as string; }
}
public string ommstb005
{
get { return originalData["tc_mmstb005"] as string; }
}
public string rid
{
get { return originalData["rowID"] as string; }
}
}
{
public mmstbItem(): this(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty){ }
public mmstbItem(string mmstb001, string mmstb002, string mmstb003, string mmstb004, string mmstb005, string ROWid)
{
currentData.Add("tc_mmstb001", mmstb001);
currentData.Add("tc_mmstb002", mmstb002);
currentData.Add("tc_mmstb003", mmstb003);
currentData.Add("tc_mmstb004", mmstb004);
currentData.Add("tc_mmstb005", mmstb005);
currentData.Add("rowID", ROWid);
originalData.Add("tc_mmstb001", mmstb001);
originalData.Add("tc_mmstb002", mmstb002);
originalData.Add("tc_mmstb003", mmstb003);
originalData.Add("tc_mmstb004", mmstb004);
originalData.Add("tc_mmstb005", mmstb005);
originalData.Add("rowID", ROWid);
}
public string Crowid
{
get { return currentData["rowID"] as string; }
set { currentData["rowID"] = value; }
}
public string mmstb001
{
get { return currentData["tc_mmstb001"] as string; }
set
{
currentData["tc_mmstb001"] = value;
}
}
public string mmstb002
{
get { return currentData["tc_mmstb002"] as string; }
set
{
currentData["tc_mmstb002"] = value;
}
}
public string mmstb003
{
get { return currentData["tc_mmstb003"] as string; }
set
{
currentData["tc_mmstb003"] = value;
}
}
public string mmstb004
{
get { return currentData["tc_mmstb004"] as string; }
set
{
currentData["tc_mmstb004"] = value;
}
}
public string mmstb005
{
get { return currentData["tc_mmstb005"] as string; }
set
{
currentData["tc_mmstb005"] = value;
}
}
public string ommstb001
{
get { return originalData["tc_mmstb001"] as string; }
}
public string ommstb002
{
get { return originalData["tc_mmstb002"] as string; }
}
public string ommstb003
{
get { return originalData["tc_mmstb003"] as string; }
}
public string ommstb004
{
get { return originalData["tc_mmstb004"] as string; }
}
public string ommstb005
{
get { return originalData["tc_mmstb005"] as string; }
}
public string rid
{
get { return originalData["rowID"] as string; }
}
}
不难看出这里originalData, currentData都是基础类TableItem中的，为对比之用。
然后看看记录集合类：
public class mmstbCollections : TableCollection<mmstbItem>
{
private mmstbDataAccess dataAccess = new mmstbDataAccess();
public mmstbCollections(bool loadData) : base(loadData) { }
public override void Load()
{
dataAccess.GetData(this);
}
protected override void SaveData()
{
dataAccess.SaveData(this);
}
}
{
private mmstbDataAccess dataAccess = new mmstbDataAccess();
public mmstbCollections(bool loadData) : base(loadData) { }
public override void Load()
{
dataAccess.GetData(this);
}
protected override void SaveData()
{
dataAccess.SaveData(this);
}
}
下面是数据库操作的实体类：
public class mmstbDataAccess:TableDataAccess<mmstbItem,mmstbCollections>
{
protected override System.Data.OracleClient.OracleDataReader GetReader()
{
OracleConnection conn = DataAccess.GetConnection();
try
{
OracleCommand cmd = new OracleCommand(@"select rowid,
tc_MMSTB001,
tc_MMSTB002,
tc_MMSTB003,
tc_MMSTB004,
tc_MMSTB005
FROM whh.TC_MMSTBBAK_FILE
where tc_mmstb005 = 'mmmm'", conn);
conn.Open();
return cmd.ExecuteReader();
}
finally
{
}
}
protected override mmstbItem GetItemFromReader(System.Data.OracleClient.OracleDataReader reader)
{
string newmmstb001 = reader["tc_MMSTB001"] as string;
string newmmstb002 = reader["tc_MMSTB002"] as string;
string newmmstb003 = reader["tc_MMSTB003"] as string;
string newmmstb004 = reader["tc_MMSTB004"] as string;
string newmmstb005 = reader["tc_MMSTB005"] as string;
string rowid = reader["rowid"] as string;
mmstbItem newItem = new mmstbItem(newmmstb001, newmmstb002, newmmstb003, newmmstb004, newmmstb005, rowid);
return newItem;
}
protected override System.Data.OracleClient.OracleCommand GetUpdateCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand(@"update whh.tc_mmstbbak_file set tc_mmstb001=:tc_mmstb001,
tc_mmstb002=:tc_mmstb002,
tc_mmstb003=:tc_mmstb003,
tc_mmstb004=:tc_mmstb004,
tc_mmstb005=:tc_mmstb005
where rowid = :rid", conn);
cmd.Parameters.Add("tc_mmstb001", OracleType.VarChar).Value = item.mmstb001.ToString();
cmd.Parameters.Add("tc_mmstb002", OracleType.VarChar).Value = item.mmstb002.ToString();
cmd.Parameters.Add("tc_mmstb003", OracleType.VarChar).Value = item.mmstb003.ToString();
cmd.Parameters.Add("tc_mmstb004", OracleType.VarChar).Value = item.mmstb004.ToString();
cmd.Parameters.Add("tc_mmstb005", OracleType.VarChar).Value = item.mmstb005.ToString();
cmd.Parameters.Add("rid", OracleType.VarChar).Value = item.rid;
return cmd;
}
protected override System.Data.OracleClient.OracleCommand GetInsertCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand("insert into whh.tc_mmstbbak_file(tc_MMSTB001,tc_MMSTB002,tc_MMSTB003,tc_MMSTB004,tc_MMSTB005"
+ ")values(:tc_MMSTB001,:tc_MMSTB002,:tc_MMSTB003,:tc_MMSTB004,:tc_MMSTB005)",conn );
cmd.Parameters.Add("tc_mmstb001", OracleType.VarChar).Value = item.mmstb001;
cmd.Parameters.Add("tc_mmstb002", OracleType.VarChar).Value = item.mmstb002;
cmd.Parameters.Add("tc_mmstb003", OracleType.VarChar).Value = item.mmstb003;
cmd.Parameters.Add("tc_mmstb004", OracleType.VarChar).Value = item.mmstb004;
cmd.Parameters.Add("tc_mmstb005", OracleType.VarChar).Value = item.mmstb005;
return cmd;
}
protected override System.Data.OracleClient.OracleCommand GetDeleteCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand("delete from whh.tc_mmstbbak_file where rowid=:rid",conn);
cmd.Parameters.Add("rid", OracleType.VarChar).Value = item.rid;
return cmd;
}
}
{
protected override System.Data.OracleClient.OracleDataReader GetReader()
{
OracleConnection conn = DataAccess.GetConnection();
try
{
OracleCommand cmd = new OracleCommand(@"select rowid,
tc_MMSTB001,
tc_MMSTB002,
tc_MMSTB003,
tc_MMSTB004,
tc_MMSTB005
FROM whh.TC_MMSTBBAK_FILE
where tc_mmstb005 = 'mmmm'", conn);
conn.Open();
return cmd.ExecuteReader();
}
finally
{
}
}
protected override mmstbItem GetItemFromReader(System.Data.OracleClient.OracleDataReader reader)
{
string newmmstb001 = reader["tc_MMSTB001"] as string;
string newmmstb002 = reader["tc_MMSTB002"] as string;
string newmmstb003 = reader["tc_MMSTB003"] as string;
string newmmstb004 = reader["tc_MMSTB004"] as string;
string newmmstb005 = reader["tc_MMSTB005"] as string;
string rowid = reader["rowid"] as string;
mmstbItem newItem = new mmstbItem(newmmstb001, newmmstb002, newmmstb003, newmmstb004, newmmstb005, rowid);
return newItem;
}
protected override System.Data.OracleClient.OracleCommand GetUpdateCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand(@"update whh.tc_mmstbbak_file set tc_mmstb001=:tc_mmstb001,
tc_mmstb002=:tc_mmstb002,
tc_mmstb003=:tc_mmstb003,
tc_mmstb004=:tc_mmstb004,
tc_mmstb005=:tc_mmstb005
where rowid = :rid", conn);
cmd.Parameters.Add("tc_mmstb001", OracleType.VarChar).Value = item.mmstb001.ToString();
cmd.Parameters.Add("tc_mmstb002", OracleType.VarChar).Value = item.mmstb002.ToString();
cmd.Parameters.Add("tc_mmstb003", OracleType.VarChar).Value = item.mmstb003.ToString();
cmd.Parameters.Add("tc_mmstb004", OracleType.VarChar).Value = item.mmstb004.ToString();
cmd.Parameters.Add("tc_mmstb005", OracleType.VarChar).Value = item.mmstb005.ToString();
cmd.Parameters.Add("rid", OracleType.VarChar).Value = item.rid;
return cmd;
}
protected override System.Data.OracleClient.OracleCommand GetInsertCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand("insert into whh.tc_mmstbbak_file(tc_MMSTB001,tc_MMSTB002,tc_MMSTB003,tc_MMSTB004,tc_MMSTB005"
+ ")values(:tc_MMSTB001,:tc_MMSTB002,:tc_MMSTB003,:tc_MMSTB004,:tc_MMSTB005)",conn );
cmd.Parameters.Add("tc_mmstb001", OracleType.VarChar).Value = item.mmstb001;
cmd.Parameters.Add("tc_mmstb002", OracleType.VarChar).Value = item.mmstb002;
cmd.Parameters.Add("tc_mmstb003", OracleType.VarChar).Value = item.mmstb003;
cmd.Parameters.Add("tc_mmstb004", OracleType.VarChar).Value = item.mmstb004;
cmd.Parameters.Add("tc_mmstb005", OracleType.VarChar).Value = item.mmstb005;
return cmd;
}
protected override System.Data.OracleClient.OracleCommand GetDeleteCommand(mmstbItem item, System.Data.OracleClient.OracleConnection conn)
{
OracleCommand cmd = new OracleCommand("delete from whh.tc_mmstbbak_file where rowid=:rid",conn);
cmd.Parameters.Add("rid", OracleType.VarChar).Value = item.rid;
return cmd;
}
}
好了，看下窗体操作：
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public mmstbCollections data = null;
private void Form1_Load(object sender, EventArgs e)
{
DataAccess.ConnectionString = "Data Source=topprod;Persist Security Info=True;User ID=whh;Password=whh;Unicode=True";
data = new mmstbCollections(true);
DataSet ds = new DataSet();
DataTable TB = new DataTable();
TB.Columns.Add("tc_mmstb001");
TB.Columns.Add("tc_mmstb002");
TB.Columns.Add("tc_mmstb003");
TB.Columns.Add("tc_mmstb004");
TB.Columns.Add("tc_mmstb005");
ds.Tables.Add(TB);
DataRow DR;
Object[] MYVALUES = new Object[5];
foreach (mmstbItem item in data)
{
if (!item.IsDeleted)
{
MYVALUES[0] = item.mmstb001;
MYVALUES[1] = item.mmstb002;
MYVALUES[2] = item.mmstb003;
MYVALUES[3] = item.mmstb004;
MYVALUES[4] = item.mmstb005;
DR = TB.NewRow();
DR.ItemArray = MYVALUES;
ds.Tables[0].Rows.Add(DR);
if (item.mmstb002 == "000")
item.Delete();
}
}
dataGridView1.DataSource = ds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
mmstbDataAccess mda = new mmstbDataAccess();
mda.SaveData(data);
}
private void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
{
int idx = e.RowIndex - 1;
if (idx >= 0)
{
data[idx].mmstb001 = dataGridView1[0, idx].Value.ToString();
data[idx].mmstb002 = dataGridView1[1, idx].Value.ToString();
data[idx].mmstb003 = dataGridView1[2, idx].Value.ToString();
data[idx].mmstb004 = dataGridView1[3, idx].Value.ToString();
data[idx].mmstb005 = dataGridView1[4, idx].Value.ToString();
}
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
int idx = dataGridView1.CurrentRow.Index;
data[idx].Delete();
}
}
{
public Form1()
{
InitializeComponent();
}
public mmstbCollections data = null;
private void Form1_Load(object sender, EventArgs e)
{
DataAccess.ConnectionString = "Data Source=topprod;Persist Security Info=True;User ID=whh;Password=whh;Unicode=True";
data = new mmstbCollections(true);
DataSet ds = new DataSet();
DataTable TB = new DataTable();
TB.Columns.Add("tc_mmstb001");
TB.Columns.Add("tc_mmstb002");
TB.Columns.Add("tc_mmstb003");
TB.Columns.Add("tc_mmstb004");
TB.Columns.Add("tc_mmstb005");
ds.Tables.Add(TB);
DataRow DR;
Object[] MYVALUES = new Object[5];
foreach (mmstbItem item in data)
{
if (!item.IsDeleted)
{
MYVALUES[0] = item.mmstb001;
MYVALUES[1] = item.mmstb002;
MYVALUES[2] = item.mmstb003;
MYVALUES[3] = item.mmstb004;
MYVALUES[4] = item.mmstb005;
DR = TB.NewRow();
DR.ItemArray = MYVALUES;
ds.Tables[0].Rows.Add(DR);
if (item.mmstb002 == "000")
item.Delete();
}
}
dataGridView1.DataSource = ds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
mmstbDataAccess mda = new mmstbDataAccess();
mda.SaveData(data);
}
private void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
{
int idx = e.RowIndex - 1;
if (idx >= 0)
{
data[idx].mmstb001 = dataGridView1[0, idx].Value.ToString();
data[idx].mmstb002 = dataGridView1[1, idx].Value.ToString();
data[idx].mmstb003 = dataGridView1[2, idx].Value.ToString();
data[idx].mmstb004 = dataGridView1[3, idx].Value.ToString();
data[idx].mmstb005 = dataGridView1[4, idx].Value.ToString();
}
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
int idx = dataGridView1.CurrentRow.Index;
data[idx].Delete();
}
}
增加，删除，修改都有。
好了，如果有疑问，相互交流。
其实，看到这些代码，我们应该想到，对所有表而言，只要用到增删改，都有INSERT INTO ,DELETE,
UPDATE,所以对于相同规则的东西，我们可自己写个工具去自动生成它，那么，最后实际操作的东西就少了，
希望能给大家一点帮助。谢谢！！
