以前没接触过数据库的东西,不巧项目需要管理数据库,无奈最后选中了我,正好可以学习学习关于数据库的东西,以后会一点一点添加一些我认为好用的关于数据库的东西。
1. Bulk Copy:如果不会使用事务,想用一种快速上手的方式提升存储速度,强烈推荐BulkCopy,比普通查询至少能提高20倍速度,实现特别简单:
首先是创建和数据库的连接(当然可以直接写数据库的链接):
SqlBulkCopy bulkCopy = new SqlBulkCopy(DatabaseManager.GetConnection());
然后将要保存的数据组装到DataTable中
DataTable personTable = new DataTable();
for(int i = 0; i < personList.count; i ++)
{
DataRow personRow = personTable.NewRow();
personRow["ID"] = personList.ElementAt(i).Value.ID;
personRow["Name"] = personList.ElementAt(i).value.Name;
/*剩下的Person属性也依次加入*/
personTable .Rows.Add(leftPartRow);
personTable .AcceptChanges();
}
最后就是以Bulk Copy的形式将数据写入数据库:
bulkCopy.DestinationTableName = "dbo.tb_Person";
bulkCopy.WriteToServer(personTable);
Redis: (推荐教程:http://labs.alcacoop.it/doku.php?id=articles:redis_land)对于实时性要求比较高的数据操作(通信系统、银行数据、库管等),听说某博、某信、某Q等都是使用Redis管理数据,所以强烈推荐Redis。Redis作为一个部分开源(开始完全开源,现在新增的部分功能需要付费)、Key-Value存储系统,用ANSI-C语言实现,可以在几乎所有的操作系统使用(Linux、Mac OS、Solaris等),最大的优势就是响应速度快(基于内存),而且解决了断电后数据丢失的情况,还支持更多的Value类型,除了String外,还有List、Sets、ZSets(有序集合)等等。Redis还可以设置数据过期自清理功能,个人觉得非常实用,因为如果项目需要启动之后就一直运行,那会在内存中积累很多过期的垃圾数据,可以让Redis自己去实时清理掉。
首先去官网下载源码:http://redis.googlecode.com/files/redis-2.4.6.tar.gz,解压之后可以看到Makefile安装文件,直接执行make命令就能安装(make install)。
具体有四个可执行文件:redis-benchmark(Redis的性能测试工具,测试Redis在系统中读写性能)、redis-cli(Redis的命令操作工具,也可以更具Telnet纯文本操作)、redis-server(Redis服务器的启动程序)、redis-stat(Redis的状态监测工具,可以监测Redis当前状态参数及延迟状态),然后加上一个redis.config就可以用了。
启动Redis:Redis-server /etc/redis.config(如果是在程序中使用,直接启动Redis-server就可以直接在程序中使用Redis操作数据了),下面代码是在程序中的app.config中配置Redis
<RedisConfig WriteServerList="127.0.0.1:6379"
ReadServerList="127.0.0.1:6379"
MaxWritePoolSize="40"
MaxReadPoolSize="40"
AutoStart="true"
LocalCacheTime="36000000"
RecordeLog="false">
</RedisConfig>
其中poolsize是可以允许启动多少个客户端,LocalCacheTime是数据过期时间。下面是读取配置文件:
public sealed class RedisConfigInfo : ConfigurationSection
{
public static RedisConfigInfo GetConfig()
{
try
{
var section = ConfigurationManager.GetSection("RedisConfig");
return section as RedisConfigInfo;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return null;
}
public static RedisConfigInfo GetConfig(string sectionName)
{
var section = (RedisConfigInfo)ConfigurationManager.GetSection(sectionName);
if (section == null)
{
throw new ConfigurationErrorsException("Section " + sectionName + " is not found.");
}
return section;
}
}
下面是创建Redismanager:
public class RedisManager
{
/// <summary>
/// redis配置文件信息
/// </summary>
private static readonly RedisConfigInfo RedisConfigInfo = RedisConfigInfo.GetConfig();
private static PooledRedisClientManager pooledRedisClientManager;
public static PooledRedisClientManager PooledRedisClientManager { get { return pooledRedisClientManager; } }
/// <summary>
/// 静态构造方法,初始化链接池管理对象
/// </summary>
static RedisManager()
{
CreateManager();
}
/// <summary>
/// 创建链接池管理对象
/// </summary>
private static void CreateManager()
{
#if true
IDictionary IDRedis = (IDictionary)ConfigurationManager.GetSection("RedisConfig"); // ConfigurationSettings.GetConfig("RedisConfig");
var writeServerList = SplitString((string)IDRedis["WriteServerList"], ",");
var readServerList = SplitString((string)IDRedis["ReadServerList"], ",");
pooledRedisClientManager = new PooledRedisClientManager(readServerList, writeServerList,
new RedisClientManagerConfig
{
MaxWritePoolSize = Convert.ToInt16((string)IDRedis["MaxWritePoolSize"]),
MaxReadPoolSize = Convert.ToInt16((string)IDRedis["MaxReadPoolSize"]),
AutoStart = Convert.ToBoolean((string)IDRedis["AutoStart"]),
})
{
PoolTimeout = Convert.ToInt32((string)IDRedis["LocalCacheTime"]),
};
#else
var writeServerList = SplitString(RedisConfigInfo.WriteServerList, ",");
var readServerList = SplitString(RedisConfigInfo.ReadServerList, ",");
pooledRedisClientManager = new PooledRedisClientManager(readServerList, writeServerList,
new RedisClientManagerConfig
{
MaxWritePoolSize = RedisConfigInfo.MaxWritePoolSize,
MaxReadPoolSize = RedisConfigInfo.MaxReadPoolSize,
AutoStart = RedisConfigInfo.AutoStart
})
{
PoolTimeout = RedisConfigInfo.LocalCacheTime
};
#endif
}
private static IEnumerable<string> SplitString(string strSource, string split)
{
return strSource.Split(split.ToArray());
}
/// <summary>
/// 客户端缓存操作对象
/// </summary>
static Dictionary<Thread, RedisClient> ClientDictionary = new Dictionary<Thread, RedisClient>();
public static RedisClient Client
{
get
{
//Log.d("you", "ClientDictionary.Count=" + ClientDictionary.Count);
RedisClient rc;
lock (ClientDictionary)
{
if (pooledRedisClientManager == null)
{
CreateManager();
}
if (ClientDictionary.ContainsKey(Thread.CurrentThread))
{
rc = ClientDictionary[Thread.CurrentThread];
}
else
{
Thread threadMonitor = null;
foreach (Thread thread in ClientDictionary.Keys)
{
if (thread.IsAlive == false)
{
threadMonitor = thread;
break;
}
}
if (threadMonitor != null)
{
ClientDictionary.Add(Thread.CurrentThread, ClientDictionary[threadMonitor]);
rc = ClientDictionary[Thread.CurrentThread];
ClientDictionary.Remove(threadMonitor);
}
else
{
rc = (RedisClient)pooledRedisClientManager.GetClient(); ClientDictionary[Thread.CurrentThread] = rc;
}
}
}
return rc;
}
}
}
View Code
下面是Redis基础操作:
public abstract class RedisOperatorBase : IDisposable
{
protected IRedisClient Redis { get; private set; }
private bool isDisposed = false;
protected RedisOperatorBase()
{
Redis = RedisManager.Client;
}
protected virtual void Dispose(bool needDispose)
{
if (!this.isDisposed)
{
if (needDispose)
{
Redis.Dispose();
Redis = null;
}
}
this.isDisposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// 保存数据DB文件到硬盘
/// </summary>
public void Save()
{
Redis.Save();
}
/// <summary>
/// 异步保存数据DB文件到硬盘
/// </summary>
public void SaveAsync()
{
Redis.SaveAsync();
}
}
View Code
下面是Save方法:
public void Save(DataProvider data)
{
var redisPerson = RedisManager.Client.As<Person>();
redisPerson.StoreAll(data.person.List)
}
View Code
下面是Get方法:
public Person GetPerson(string id)
{
var redisPerson = RedisManager.Client.As<Person>();
Person person = redisPerson.GetById(id);
}
View Code
将文本文件保存到数据库:
a.将文本文件转换成二进制
/// <summary>
/// 将文件转换为Bytes
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static byte[] File2Bytes(string fileName)
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read);
byte[] fileDatas = new byte[fs.Length];
fs.Read(fileDatas, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
return fileDatas;
}
/// <summary>
/// 将文件转换为Bytes
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static byte[] File2Bytes(string fileName)
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read);
byte[] fileDatas = new byte[fs.Length];
fs.Read(fileDatas, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
return fileDatas;
}
b.保存文件到数据库:
/// <summary>
/// 保存文件到数据库
/// </summary>
/// <param name="id"></param>
/// <param name="fileBytes"></param>
/// <returns></returns>
public static void SaveFile1Table(string id, byte[] fileBytes)
{
string sql = string.Format("insert into tb_FileContents values ('" + id + "',@CONTRACT_FILE)");
SqlParameter para = new SqlParameter("@CONTRACT_FILE", SqlDbType.Image);
para.Value = fileBytes;
DatabaseManager.ExecNonQuery(sql, CommandType.Text, para);
}
c. 从数据库获取文件并转换成文本文件
/// <summary>
/// 从数据库获取文件
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static byte[] GetFile(string id)
{
string sql = "SELECT fileContent FROM tb_FileContents WHERE ID='{0}'";
sql = string.Format(sql, id);
object getFile;
getFile = DatabaseManager.ExecScalar(sql);
if (getFile == null)
{
return new byte[0];
}
else
{
return (byte[])getFile;
}
}
/// <summary>
/// 将二进制文件转换成TXT文件
/// </summary>
/// <param name="id"></param>
public static void Byts2File(string id, string filePath)
{
byte[] fileBytes = GetFile(id);
if (fileBytes.Length == 0)
{
Console.WriteLine("未找到文件!");
return;
}
string saveFilePath = filePath+".txt";
if (!File.Exists(saveFilePath ))
{
StreamWriter sw = File.CreateText(saveFilePath );
sw.Close();
}
int arraysize = new int();//注意这句话
arraysize = fileBytes.GetUpperBound(0);
FileStream fs = new FileStream(saveFilePath , FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(fileBytes, 0, arraysize);
fs.Close();
}