本文介绍了一个通用的数据操作框架，包括在不同数据源（如SQL Server、Oracle、Access、Excel、CSV和XML）上执行数据查看、插入、删除和修改等基本操作的方法。提供了详细的源代码示例，展示了如何使用DbHelper类简化这些操作。
编译环境：VS2008+Win7
数据源：
SQL Server 2005，Oracle 10g，Access 2003，Excel 2003，CSV，Xml
功能：
查看所有数据：读取数据表中所有数据显示在DataGridView中
查看学生总数：显示数据库中学生总数
插入一条记录：插入 003，李，23，0，1993-3-1记录
删除一条记录：删除userid为003的记录
修改一条记录：将userid为003的记录的usersex修改为1
读取/修改配置文件
DbHelper类用法
DOM模型操作Xml文件
示例源码下载地址：
http://download.youkuaiyun.com/detail/daxiang12092205/4516239
DbHelper类源码参考：
http://blog.youkuaiyun.com/charlsechang/article/details/6249284
XML文件操作参考：
http://hi.baidu.com/wangweixp/blog/item/bca10dfa43732e9759ee90ff.html
读取修改配置文件源码：
private void OperAppConfig()
{
//读取
string strName = ConfigurationManager.AppSettings["DbHelperProvider"];
//Console.WriteLine("------name1 is ：" + strName);
Configuration cfa = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
//修改
cfa.AppSettings.Settings["DbHelperProvider"].Value = "System.Data.SqlClient";
cfa.AppSettings.Settings["DbHelperConnectionString"].Value = "Data Source=.\\sqlexpress;Initial Catalog=mytest;Integrated Security=True";
cfa.Save();
//操作结束刷新节点，否则下次取值不变，所有的操作未映射到磁盘文件App.Config
ConfigurationManager.RefreshSection("appSettings");
strName = ConfigurationManager.AppSettings["DbHelperProvider"];
//Console.WriteLine("------name2 is is：" + strName);
}
SQL Server删除数据源码：
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DbHelper db = new DbHelper();
tsslStatus.Text = "正在删除数据";
string sqlInsert = "delete from [student] where userid='003' ";
DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);
int execresult = db.ExecuteNonQuery(dbcmd);
//Console.WriteLine("------:" + execresult);
if (execresult > 0)
{
tsslStatus.Text = "删除成功";
}
else
{
tsslStatus.Text = "删除失败";
}
}
catch (System.Data.SqlClient.SqlException)
{
tsslStatus.Text = "删除失败";
}
}
Oracle查看学生总数源码：
private void btnGetNum_Click(object sender, EventArgs e)
{
try
{
DbHelper db = new DbHelper();
tsslStatus.Text = "正在获取数据";
string sqlInsert = "select count(*) from userinfo";
DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);
//不能用int execresult = (int)db.ExecuteScalar(dbcmd);出现InvalidCaseException异常
object obj = db.ExecuteScalar(dbcmd);
int execresult = Convert.ToInt32(obj);
//Console.WriteLine("------:" + execresult);
if (execresult >= 0)
{
tsslStatus.Text = "共计："+execresult.ToString();
}
else
{
tsslStatus.Text = "获取失败";
}
}
catch (System.Data.OracleClient.OracleException ex)
{
//Console.WriteLine("------" + ex.Message);
tsslStatus.Text = "获取失败";
}
}
Access修改数据源码：
private void btnModify_Click(object sender, EventArgs e)
{
try
{
DbHelper db = new DbHelper();
tsslStatus.Text = "正在修改数据";
string sqlInsert = "update [student] set usersex='1' where userid='003' ";
DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);
int execresult = db.ExecuteNonQuery(dbcmd);
//Console.WriteLine("------:" + execresult);
if (execresult > 0)
{
tsslStatus.Text = "修改成功";
}
else
{
tsslStatus.Text = "修改失败";
}
}
catch (System.Data.OleDb.OleDbException)
{
tsslStatus.Text = "修改失败";
}
}
Excel删除数据源码：
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DbHelper db = new DbHelper();
tsslStatus.Text = "正在删除数据";
//Excel工作表中的行与真正的关系型数据库的行是有些不一样的，
//删除Excel数据时，受到的限制要比从关系数据源中删除数据时更多,故不能用delete语句删除
//string sqlInsert = "delete from [Sheet1$] where userid=1003 ";
//通过这种方法，这一行并没有真正删除掉，所以在select时要进行一定筛选，
//如select * from [Sheet1$] where userid is not null
string sqlInsert = "update [Sheet1$] set userid=null,username=null,userage=null,usersex=null,userbirth=null where userid=1003";
DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);
int execresult = db.ExecuteNonQuery(dbcmd);
//Console.WriteLine("------:" + execresult);
if (execresult > 0)
{
tsslStatus.Text = "删除成功";
}
else
{
tsslStatus.Text = "删除失败";
}
}
catch (System.Data.OleDb.OleDbException)
{
tsslStatus.Text = "删除失败";
}
}
CSV删除数据源码：
private void btnDelete_Click(object sender, EventArgs e)
{
DbHelper db = new DbHelper();
tsslStatus.Text = "正在删除数据";
//csv不允许更新或删除记录，详细解释见：http://support.microsoft.com/kb/828892
//故不能用sql语句直接操作，采用 读取数据到datatable-修改datatable-删除源文件-导出datatable到新文件 方法
DataTable dt = new DataTable();
dt = GetAllData();
if (dt != null && dt.Rows.Count >= 0)
{
for (int i = 0; i < dt.Rows.Count;i++ )
{
if (dt.Rows[i][0].ToString().Trim() == "1003")
{
dt.Rows.RemoveAt(i);
break;
}
}
if (File.Exists(filepath))
{
File.Delete(filepath);
}
ExportToFile(dt);
tsslStatus.Text = "删除成功";
}
else
{
tsslStatus.Text = "删除失败";
}
}
/// <summary>
/// 获取csv文件所有数据，保存到DataTable中并返回
/// </summary>
/// <returns></returns>
private DataTable GetAllData()
{
try
{
DbHelper db = new DbHelper();
string sqlInsert = "select * from [student.csv]";
DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);
DataTable dt = db.ExecuteDataTable(dbcmd);
if (dt != null && dt.Rows.Count > 0)
{
return dt;
}
else
{
return null;
}
}
catch (Exception ex)
{
//Console.WriteLine("------ ex :" + ex.Message);
return null;
}
}
/// <summary>
/// 将DataTable数据导出到csv文件
/// </summary>
/// <param name="dataTable"></param>
public static void ExportToFile(DataTable dataTable)
{
try
{
using (StreamWriter sw = new StreamWriter(filepath, false, Encoding.Default))
{
string repStr = ",";
string colHead = string.Empty;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
colHead += dataTable.Columns[i].ColumnName + repStr;
}
if (colHead.Contains(repStr))
{
colHead = colHead.Remove(colHead.LastIndexOf(repStr));
}
sw.WriteLine(colHead);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
int cell = dataTable.Rows[i].ItemArray.Count();
string content = string.Empty;
for (int j = 0; j < cell; j++)
{
content += dataTable.Rows[i][j] + repStr;
}
if (content.Contains(repStr))
{
content = content.Remove(content.LastIndexOf(repStr));
}
sw.WriteLine(content);
}
}
}
catch (Exception ex)
{
//Console.WriteLine("------ :" + ex.Message);
}
}XML获取所有数据源码：
private static string dirpath = Application.StartupPath + "\\";
private static string filepath = Application.StartupPath + \\student.xml;
private void btnGetAllData_Click(object sender, EventArgs e)
{
tsslStatus.Text = "正在获取";
//利用DOM模型读取xml文件
XmlDocument xmldoc = new XmlDocument();
XmlElement xmlelemF;
XmlElement xmlelemS1;
xmldoc.Load(filepath);
//将读取的数据保存到DataTable里
DataTable dt = new DataTable();
//获取用户数和属性数
int numF = xmldoc.DocumentElement.ChildNodes.Count;
int numS = xmldoc.DocumentElement.ChildNodes[0].ChildNodes.Count;
//Console.WriteLine("------num :" + numF+","+numS);
//获取所有的属性名，作为DataTable的列名
xmlelemF = (XmlElement)xmldoc.DocumentElement.ChildNodes[0];
for (int i = 0; i < numS; i++)
{
xmlelemS1 = (XmlElement)xmlelemF.ChildNodes[i];
dt.Columns.Add(xmlelemS1.LocalName, System.Type.GetType("System.String"));
}
//遍历所有节点，将节点值添加到DataTable中
for (int i = 0; i < numF; i++)
{
xmlelemF = (XmlElement)xmldoc.DocumentElement.ChildNodes[i];
DataRow dr = dt.NewRow();
for (int j = 0; j < numS; j++)
{
xmlelemS1 = (XmlElement)xmlelemF.ChildNodes[j];
//Console.WriteLine("------" +i+","+j+":"+ xmlelemS1.FirstChild.Value);
dr[j] = xmlelemS1.FirstChild.Value;
}
dt.Rows.Add(dr);
}
//Console.WriteLine("------row num :" + dt.Rows.Count);
dgvStudent.DataSource = dt;
tsslStatus.Text = "获取成功";
}
细节问题：
1 CSV数据源，判断userid为1003记录是否存在时，语句select username from [student.csv] where userid=1003，其中1003不能改为‘1003’
2 csv不允许更新或删除记录，详细解释见：http://support.microsoft.com/kb/828892，
故不能用sql语句直接操作，采用 读取数据到datatable-修改datatable-删除源文件-导出datatable到新文件方法
3 Excel工作表中的行与真正的关系型数据库的行是有些不一样的，删除Excel数据时，受到的限制要比从关系数据源中删除数据时更多,故不能用delete语句删除。用update 表 set 字段=null 方法，通过这种方法，这一行并没有真正删除掉，所以在select时要进行一定筛选，如select * from [Sheet1$] where userid is not null
4 Oracle数据源修改数据时，不能用下面方法，执行结果obj为null，出现NullReferenceException
object obj = db.ExecuteScalar(dbcmd);
int execresult = Convert.ToInt32(obj);
可用 int execresult = db.ExecuteNonQuery(dbcmd);
5 Oracle数据源，命令语句如：select * from userinfo，userinfo不能写为[userinfo]，否则出现OracleException ORA-00903:表名无效异常
如有问题，欢迎留言。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
