本人在乐趣搜索网的开发过程中,要求将用户的一个搜索结果集缓存到数据库中,出于性能和速度方面的考虑,要求将一个DataSet的搜索结果集作为数据库一条记录的一个Text字段来进行存储,作者经过查找资料和实践,总结出了以下简单的办法:
将DataSet数据集写入数据库:
string strConnect = "Provider=SQLOLEDB.1;Password=1234;Persist Security Info=True;" +
"User ID=username;Initial Catalog=MyTempDb;Data Source=localhost";
OleDbConnection dbConn = new OleDbConnection;
dbConn.ConnectionString = strConnect;
try
{
StringBuilder strXml = new StringBuilder();
StringWriter strWriter = new StringWriter( strXml );
XmlTextWriter writer = new XmlTextWriter(strWriter);
writer.WriteStartDocument();
m_dsDataSet.WriteXml(writer);
string strSQL = "INSERT INTO temp(Temp_date,Temp_data) VALUES(GETDATE(),?)";
OleDbCommand aCommand = new OleDbCommand( strSQL , dbConn );
aCommand.Parameters.Add("Temp_data", OleDbType.VarChar).Value = strXml.ToString();
aCommand.Connection.Open();
aCommand.ExecuteNonQuery();
}
catch ( OleDbException e )
{
Debug.WriteLine("发生异常:" + e.ToString());
}
"User ID=username;Initial Catalog=MyTempDb;Data Source=localhost";
OleDbConnection dbConn = new OleDbConnection;
dbConn.ConnectionString = strConnect;
try
{
StringBuilder strXml = new StringBuilder();
StringWriter strWriter = new StringWriter( strXml );
XmlTextWriter writer = new XmlTextWriter(strWriter);
writer.WriteStartDocument();
m_dsDataSet.WriteXml(writer);
string strSQL = "INSERT INTO temp(Temp_date,Temp_data) VALUES(GETDATE(),?)";
OleDbCommand aCommand = new OleDbCommand( strSQL , dbConn );
aCommand.Parameters.Add("Temp_data", OleDbType.VarChar).Value = strXml.ToString();
aCommand.Connection.Open();
aCommand.ExecuteNonQuery();
}
catch ( OleDbException e )
{
Debug.WriteLine("发生异常:" + e.ToString());
}
从数据库中读出DataSet数据集:
// 这里省略千篇一律的数据库的Query操作
OleDbDataReader aReader = aCommand.ExecuteReader( );
if (aReader.Read() )
{
StringBuilder strXml = new StringBuilder( aReader["temp_data"].ToString() );
StringReader strReader = new StringReader( strXml.ToString() );
XmlTextReader reader = new XmlTextReader(strReader);
try
{
m_dsDataSet.ReadXml(reader);
aReader.Close( );
}
catch( OleDbException e )
{
Debug.WriteLine("发生异常:" + e.ToString());
}
}
OleDbDataReader aReader = aCommand.ExecuteReader( );
if (aReader.Read() )
{
StringBuilder strXml = new StringBuilder( aReader["temp_data"].ToString() );
StringReader strReader = new StringReader( strXml.ToString() );
XmlTextReader reader = new XmlTextReader(strReader);
try
{
m_dsDataSet.ReadXml(reader);
aReader.Close( );
}
catch( OleDbException e )
{
Debug.WriteLine("发生异常:" + e.ToString());
}
}
注意以上例子所引用的变量m_dsDataSet的原始定义为DataSet m_dsDataSet,在这里是一个作者自定义包含有两个表和记录的DataSet实例,其中的记录来源于搜索引擎分析出的每条记录。
在该基础上,还可以增加诸如Zip算法的压缩/解压处理,从而减少数据大小。