internal static string FormatIsNull(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
int index = text.IndexOf(SqlValue.ISNULL);//isnull (isnull(aaa),'3,3') iff(isnull (aaa),333,aaa)
if (index > -1)
{
do
{
index = text.IndexOf('(', index);
int end = text.IndexOf(',', index);
string key = text.Substring(index + 1, end - index - 1);//�ҵ� aaa
text = text.Insert(end, ")");//
end = text.IndexOf(')', end + 3);
text = text.Insert(end, "," + key);
index = text.IndexOf(SqlValue.ISNULL, end);//Ѱ�һ���û�еڶ��γ��ֵĺ����ֶ�
}
while (index > -1);
return text.Replace(SqlValue.ISNULL, "iff(isnull");
}
break;
case DalType.SQLite:
case DalType.MySql:
return text.Replace(SqlValue.ISNULL, "IfNull");
case DalType.MsSql:
case DalType.Sybase:
return text.Replace(SqlValue.ISNULL, "IsNull");
case DalType.Oracle:
return text.Replace(SqlValue.ISNULL, "NVL");
}
return text;
}
public DbBase(ConnObject co)
{
this.connObject = co;
this.conn = co.Master.Conn;
this.providerName = co.Master.ProviderName;
dalType = co.Master.ConnDalType;
_fac = GetFactory(providerName);
_con = _fac.CreateConnection();
_con.ConnectionString = DalCreate.FormatConn(dalType, conn);
_com = _con.CreateCommand();
if (_com != null)//Txt| Xml 时返回Null
{
_com.Connection = _con;
_com.CommandTimeout = AppConfig.DB.CommandTimeout;
}
if (IsAllowRecordSql)//开启秒表计算
{
_watch = new Stopwatch();
}
//if (AppConfig.DB.LockOnDbExe && dalType == DalType.Access)
//{
// string dbName = DataBase;
// if (!_dbOperator.ContainsKey(dbName))
// {
// try
// {
// _dbOperator.Add(dbName, false);
// }
// catch
// {
// }
// }
//}
//_com.CommandTimeout = 1;
}
public DbBase(string conn, string providerName)
{
dalType = DalAction.GetDalType(providerName);
_fac = DbProviderFactories.GetFactory(providerName);
_con = _fac.CreateConnection();
_con.ConnectionString = FormatConn(conn);
_com = _con.CreateCommand();
_com.Connection = _con;
}
/// <summary>
/// Sql���ݿ���ݺ�Sqlע�봦��
/// </summary>
public static string Compatible(object where, DalType dalType, bool isFilterInjection)
{
string text = GetIFieldSql(where);
if (isFilterInjection)
{
text = SqlInjection.Filter(text, dalType);
}
text = SqlCompatible.Format(text, dalType);
return RemoveWhereOneEqualsOne(text);
}
/// <summary>
/// 字母型返回0；数字型返回1；日期型返回2；bool返回3；guid返回4；其它返回999
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static int GetGroup(SqlDbType sqlDbType, DalType dalType)
{
switch (sqlDbType)
{
case SqlDbType.Int:
case SqlDbType.TinyInt:
case SqlDbType.BigInt:
case SqlDbType.SmallInt:
case SqlDbType.Float:
case SqlDbType.Real:
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
return(1);
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.NChar:
case SqlDbType.Char:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.Time:
return(0);
case SqlDbType.Date:
case SqlDbType.DateTimeOffset:
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.DateTime2:
return(2);
case SqlDbType.Bit:
return(3);
case SqlDbType.UniqueIdentifier:
return(4);
default:
if (sqlDbType == SqlDbType.Timestamp)
{
if (dalType != DalType.MsSql && dalType != DalType.Sybase)
{
return(2);
}
}
return(999);
}
}
private static string FormatPara(string text, DalType dalType)
{
switch (dalType)
{
case DalType.MySql:
return(text.Replace("=:?", "=?"));
case DalType.Oracle:
case DalType.PostgreSQL:
return(text.Replace("=:?", "=:"));
default:
return(text.Replace("=:?", "=@"));
}
}
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <param name="dalType">数据库类型</param>
public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database)
{
dalType = DalType.None;
database = string.Empty;
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
{
return(false);
}
using (DalBase helper = DalCreate.CreateDal(conn))
{
dalType = helper.DataBaseType;
database = helper.DataBase;
}
return(TableSchema.Exists(tableName, "U", conn));
}
private static string FormatDate(string text, DalType dalType, string key, string func)
{
int index = text.IndexOf(key, StringComparison.OrdinalIgnoreCase); //[#year](字段)
if (index > -1) //存在[#year]函数
{
string format = GetFormatDateKey(dalType, key);
int found = 0;
switch (dalType)
{
case DalType.Oracle:
do
{
text = text.Insert(index + 2, "_"); //[#_year](字段)
found = text.IndexOf(')', index + 4); //从[#_year(字段)]找到 ')'的位置
text = text.Insert(found, format); //->[#_year](字段,'yyyy')
index = text.IndexOf(key, StringComparison.OrdinalIgnoreCase); //寻找还有没有第二次出现的函数字段
}while (index > -1);
text = text.Replace("#_", "#");
text = Replace(text, key, "to_char"); //[#year](字段,'yyyy')
break;
case DalType.SQLite:
do
{
text = text.Insert(index + 2, "_"); //[#_year](字段)
found = text.IndexOf('(', index + 4); //从[#_year(字段)]找到 '('的位置
text = text.Insert(found + 1, format); //->[#_year]('%Y',字段)
found = text.IndexOf(')', found + 1);
text = text.Insert(found + 1, " as int)");
index = text.IndexOf(key, StringComparison.OrdinalIgnoreCase); //寻找还有没有第二次出现的函数字段
}while (index > -1);
text = text.Replace("#_", "#");
text = Replace(text, key, "cast(strftime"); //cast(strftime('%Y', UpdateTime) as int) [%Y,%m,%d]
break;
case DalType.Sybase:
text = Replace(text, key + "(", "datepart(" + format);
//// [#YEAR](getdate()) datepart(mm,getdate()) datepart(mm,getdate()) datepart(mm,getdate())
break;
default:
text = Replace(text, key, func);
break;
}
}
return(text);
}
private static string GetFormatDateKey(DalType dalType, string key)
{
switch (dalType)
{
case DalType.SQLite:
switch (key)
{
case SqlValue.Year:
return("'%Y',");
case SqlValue.Month:
return("'%m',");
case SqlValue.Day:
return("'%d',");
}
break;
case DalType.Sybase:
switch (key)
{
case SqlValue.Year:
return("yy,");
case SqlValue.Month:
return("mm,");
case SqlValue.Day:
return("dd,");
}
break;
default:
switch (key)
{
case SqlValue.Year:
return(",'yyyy'");
case SqlValue.Month:
return(",'MM'");
case SqlValue.Day:
return(",'dd'");
}
break;
}
return(string.Empty);
}
private static string FormatTrueFalseAscDesc(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
text = Replace(text, SqlValue.True, "true");
text = Replace(text, SqlValue.False, "false");
text = Replace(text, SqlValue.Desc, "asc");
return(Replace(text, SqlValue.Asc, "desc"));
default:
text = Replace(text, SqlValue.True, "1");
text = Replace(text, SqlValue.False, "0");
text = Replace(text, SqlValue.Desc, "desc");
return(Replace(text, SqlValue.Asc, "asc"));
}
}
private static string MDataTableToFile(MDataTable dt, bool keepID, DalType dalType)
{
string path = Path.GetTempPath() + dt.TableName + ".txt";
using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
{
if (dalType == DalType.Oracle)
{
sw.WriteLine();//先输出空行（Oracle需要空行在前）
}
MCellStruct ms;
string value;
foreach (MDataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
ms = dt.Columns[i];
if (!keepID && ms.IsAutoIncrement)
{
continue;
}
else
{
value = row[i].ToString();
if (ms.SqlType == SqlDbType.Bit && value != "1")
{
value = (value.ToLower() == "true") ? "1" : "0";
}
value = value.Replace("\\", "\\\\");//处理转义符号
sw.Write(value);
}
if (i != dt.Columns.Count - 1)//不是最后一个就输出
{
sw.Write(AppConst.SplitChar);
}
}
sw.WriteLine();
}
}
if (Path.DirectorySeparatorChar == '\\')
{
path = path.Replace(@"\", @"\\");
}
return(path);
}
/*
* private static MDictionary<string, ConnEntity> connCache = new MDictionary<string, ConnEntity>();
*
*
* internal static ConnEntity GetConnString(string dbConn) // 思考备份链接的问题。=》下一步思考读写分离的问题。
* {
* if (connCache.ContainsKey(dbConn))
* {
* return connCache[dbConn];
* }
*
* ConnEntity cEntity = new ConnEntity();
*
* cEntity.Conn = string.IsNullOrEmpty(dbConn) ? AppConfig.DB.DefaultConn : dbConn;
*
* if (cEntity.Conn.Length < 32 && cEntity.Conn.Split(' ').Length == 1)//配置项
* {
* if (ConfigurationManager.ConnectionStrings[cEntity.Conn] == null && cEntity.Conn != AppConfig.DB.DefaultConn)
* {
* cEntity.Conn = AppConfig.DB.DefaultConn;//转取默认配置；
* if (cEntity.Conn.Length >= 32 || cEntity.Conn.Trim().Contains(" "))
* {
* goto er;
* }
* }
* if (ConfigurationManager.ConnectionStrings[cEntity.Conn] != null)
* {
* string p = string.Empty, c = string.Empty;
#region 获取备份链接
* string bakKey = cEntity.Conn + "_Bak";
* if (ConfigurationManager.ConnectionStrings[cEntity.Conn + "_Bak"] == null && cEntity.Conn == AppConfig.DB.DefaultConn && AppConfig.DB.DefaultConnBak != cEntity.Conn + "_Bak")
* {
* bakKey = AppConfig.DB.DefaultConnBak;
* }
* string connBak = AppConfig.GetConn(bakKey, out p);
* if (connBak.Length >= 32 || connBak.Trim().Contains(" "))//如果有完整的数据库链接
* {
* cEntity.ConnBak = AppConfig.GetConn(bakKey, out p);
* cEntity.ProviderNameBak = p;
* }
#endregion
*
* cEntity.Conn = AppConfig.GetConn(cEntity.Conn, out p);
* cEntity.ProviderName = p;
* }
* else
* {
* Error.Throw(string.Format("Can't find the connection key '{0}' from web.config!", cEntity.Conn));
* }
* }
* else if (cEntity.Conn == AppConfig.DB.DefaultConn && string.IsNullOrEmpty(cEntity.ConnBak))
* {
* string connBak = AppConfig.GetConn(AppConfig.DB.DefaultConnBak);//先赋默认备份值。
* if (connBak.Length >= 32 || connBak.Trim().Contains(" "))//如果有完整的数据库链接
* {
* cEntity.ConnBak = connBak;
* }
* }
* er:
* if (string.IsNullOrEmpty(cEntity.ProviderName))
* {
* cEntity.ProviderName = GetProvider(cEntity.Conn);
* cEntity.ConnDalType = GetDalType(cEntity.ProviderName);
* }
*
* if (string.IsNullOrEmpty(cEntity.ProviderNameBak) && !string.IsNullOrEmpty(cEntity.ConnBak))
* {
* cEntity.ProviderNameBak = DalCreate.GetProvider(cEntity.ConnBak);
* cEntity.ConnBakDalType = GetDalType(cEntity.ProviderNameBak);
* }
* cEntity.Conn = string.Format(cEntity.Conn, AppConfig.WebRootPath);
* cEntity.ConnBak = string.Format(cEntity.ConnBak ?? string.Empty, AppConfig.WebRootPath);
* if (!connCache.ContainsKey(dbConn))
* {
* connCache.Set(dbConn, cEntity);
* }
* return cEntity;
* }
*/
#endregion
internal static string FormatConn(DalType dal, string connString)
{
if (dal != DalType.Access)
{
string conn = connString.ToLower();
int index = conn.IndexOf("provider");
if (index > -1 && index < connString.Length - 5 && (connString[index + 8] == '=' || connString[index + 9] == '='))
{
int end = conn.IndexOf(';', index);
if (end > index)
{
connString = connString.Remove(index, end - index + 1);
}
}
}
return(connString);
}
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <param name="dalType">数据库类型</param>
public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database)
{
dalType = DalType.None;
database = string.Empty;
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
{
return(false);
}
DbBase helper = DalCreate.CreateDal(conn);
dalType = helper.dalType;
database = helper.DataBase;
bool result = TableSchema.Exists("U", tableName, ref helper);
helper.Dispose();
return(result);
}
internal static string FormatGetDate(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
case DalType.MySql:
return text.Replace(SqlValue.GetDate, "now()");
case DalType.MsSql:
case DalType.Sybase:
return text.Replace(SqlValue.GetDate, "getdate()");
case DalType.Oracle:
return text.Replace(SqlValue.GetDate, "current_date");
case DalType.SQLite:
return text.Replace(SqlValue.GetDate, "datetime('now','localtime')");
}
return text;
}
internal static string FormatGUID(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
return text.Replace(SqlValue.GUID, "GenGUID()");
case DalType.MySql:
return text.Replace(SqlValue.GUID, "UUID()");
case DalType.MsSql:
case DalType.Sybase:
return text.Replace(SqlValue.GUID, "newid()");
case DalType.Oracle:
return text.Replace(SqlValue.GUID, "SYS_GUID()");
case DalType.SQLite:
return text.Replace(SqlValue.GUID, "");
}
return text;
}
private static string FormatCaseWhen(string text, DalType dalType)
{
//CASE when languageid=1 THEN 1000 ELSE 10 End
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
case DalType.MySql:
case DalType.SQLite:
case DalType.Sybase:
case DalType.PostgreSQL:
if (text.IndexOf(SqlValue.Case, StringComparison.OrdinalIgnoreCase) > -1 || text.IndexOf(SqlValue.CaseWhen, StringComparison.OrdinalIgnoreCase) > -1)
{
text = Replace(text, SqlValue.Case, "Case");
text = Replace(text, SqlValue.CaseWhen, "Case When");
text = Replace(text, "[#WHEN]", "when");
text = Replace(text, "[#THEN]", "then");
text = Replace(text, "[#ELSE]", "else");
text = Replace(text, "[#END]", "end");
}
break;
case DalType.Access:
if (text.IndexOf(SqlValue.Case, StringComparison.OrdinalIgnoreCase) > -1)
{
text = Replace(text, SqlValue.Case, string.Empty);
text = Replace(text, " [#WHEN] ", "iif(");
text = Replace(text, " [#THEN] ", ",");
text = Replace(text, " [#ELSE] ", ",");
text = Replace(text, " [#END]", ")");
}
else if (text.IndexOf(SqlValue.CaseWhen, StringComparison.OrdinalIgnoreCase) > -1)
{
text = Replace(text, SqlValue.CaseWhen, "SWITCH(");
text = Replace(text, "[#THEN]", ",");
text = Replace(text, "[#ELSE]", "TRUE,");
text = Replace(text, "[#END]", ")");
}
break;
}
return(text);
}
public DbBase(ConnObject co)
{
this.connObject = co;
this.useConnBean = co.Master;
this.conn = co.Master.Conn;
this.providerName = co.Master.ProviderName;
dalType = co.Master.ConnDalType;
_fac = GetFactory(providerName);
_con = _fac.CreateConnection();
try
{
_con.ConnectionString = DalCreate.FormatConn(dalType, conn);
}
catch (Exception err)
{
Error.Throw("check the connectionstring is be ok!" + AppConst.BR + "error:" + err.Message + AppConst.BR + conn);
}
_com = _con.CreateCommand();
if (_com != null)//Txt| Xml 时返回Null
{
_com.Connection = _con;
_com.CommandTimeout = AppConfig.DB.CommandTimeout;
}
if (IsAllowRecordSql)//开启秒表计算
{
_watch = new Stopwatch();
}
//if (AppConfig.DB.LockOnDbExe && dalType == DalType.Access)
//{
// string dbName = DataBase;
// if (!_dbOperator.ContainsKey(dbName))
// {
// try
// {
// _dbOperator.Add(dbName, false);
// }
// catch
// {
// }
// }
//}
//_com.CommandTimeout = 1;
}
/// <summary>
/// 同语句多数据库兼容处理
/// </summary>
internal static string Format(string text, DalType dalType)
{
if (!string.IsNullOrEmpty(text))
{
text = FormatPara(text, dalType);
text = FormatTrueFalseAscDesc(text, dalType);
text = FormatDateDiff(text, dalType);//必须在日期替换之前出现
text = FormatGetDate(text, dalType);
text = FormatCaseWhen(text, dalType);
text = FormatCharIndex(text, dalType);
text = FormatLen(text, dalType);
text = FormatGUID(text, dalType);
text = FormatIsNull(text, dalType);
text = FormatDate(text, dalType, SqlValue.Year, "Year");
text = FormatDate(text, dalType, SqlValue.Month, "Month");
text = FormatDate(text, dalType, SqlValue.Day, "Day");
}
return(text);
}
/// <summary>
/// ͬ�������ݿ���ݴ���
/// </summary>
internal static string Format(string text, DalType dalType)
{
if (!string.IsNullOrEmpty(text))
{
text = FormatPara(text, dalType);
text = FormatTrueFalseAscDesc(text, dalType);
text = FormatDateDiff(text, dalType);//�����������滻֮ǰ����
text = FormatGetDate(text, dalType);
text = FormatCaseWhen(text, dalType);
text = FormatCharIndex(text, dalType);
text = FormatLen(text, dalType);
text = FormatGUID(text, dalType);
text = FormatIsNull(text, dalType);
text = FormatDate(text, dalType, SqlValue.Year, "Year");
text = FormatDate(text, dalType, SqlValue.Month, "Month");
text = FormatDate(text, dalType, SqlValue.Day, "Day");
}
return text;
}
/// <summary>
/// 检测GUID，若空，补值。
/// </summary>
private bool CheckGUIDAndDateTime(DalType dal)
{
bool fillGUID = false;
int groupID;
for (int i = 0; i < mdt.Columns.Count; i++)
{
MCellStruct ms = mdt.Columns[i];
groupID = DataType.GetGroup(ms.SqlType);
if (groupID == 2)
{
for (int j = 0; j < mdt.Rows.Count; j++)
{
if (dal == DalType.MsSql && mdt.Rows[j][i].StringValue == DateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
else if (dal == DalType.Oracle && mdt.Rows[j][i].StringValue == SqlDateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
}
}
else if (ms.IsPrimaryKey && (groupID == 4 || (groupID == 0 && ms.MaxSize >= 36)))
{
string defaultValue = Convert.ToString(ms.DefaultValue);
bool isGuid = defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid;
if (isGuid && !fillGUID)
{
fillGUID = true;
}
for (int k = 0; k < mdt.Rows.Count; k++)
{
if (mdt.Rows[k][i].IsNullOrEmpty)
{
mdt.Rows[k][i].Value = isGuid ? Guid.NewGuid().ToString() : defaultValue;
}
}
}
}
return(fillGUID);
}
/// <summary>
/// 缓存表架构Key
/// </summary>
internal static string GetSchemaKey(string tableName, string dbName, DalType dalType)
{
string key = tableName;
int start = key.IndexOf('(');
int end = key.LastIndexOf(')');
if (start > -1 && end > -1)//自定义table
{
key = "View" + Tool.MD5.Get(key);
}
else
{
if (key.IndexOf('.') > 0)
{
dbName = key.Split('.')[0];
}
key = SqlFormat.NotKeyword(key);
}
return("ColumnsCache:" + dalType + "_" + dbName + "_" + key);
}
internal static string FormatGetDate(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
case DalType.MySql:
return(text.Replace(SqlValue.GetDate, "now()"));
case DalType.MsSql:
case DalType.Sybase:
return(text.Replace(SqlValue.GetDate, "getdate()"));
case DalType.Oracle:
return(text.Replace(SqlValue.GetDate, "current_date"));
case DalType.SQLite:
return(text.Replace(SqlValue.GetDate, "datetime('now','localtime')"));
}
return(text);
}
private static string FormatLen(string text, DalType dalType)
{
switch (dalType)//处理函数替换
{
case DalType.Access:
case DalType.MsSql:
return(text.Replace(SqlValue.Len, "len").Replace(SqlValue.Substring, "substring"));
case DalType.Oracle:
case DalType.SQLite:
return(text.Replace(SqlValue.Len, "length").Replace(SqlValue.Substring, "substr"));
case DalType.MySql:
return(text.Replace(SqlValue.Len, "char_length").Replace(SqlValue.Substring, "substring"));
case DalType.Sybase:
return(text.Replace(SqlValue.Len, "datalength").Replace(SqlValue.Substring, "substring"));
}
return(text);
}
/// <summary>
/// 修改表的列结构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列结构</param>
/// <param name="conn">数据库链接</param>
/// <returns></returns>
public static bool AlterTable(string tableName, MDataColumn columns, string conn)
{
if (columns == null)
{
return(false);
}
List <string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqls.Count > 0)
{
DalType dalType = DalType.None;
string database = string.Empty;
using (MProc proc = new MProc(null, conn))
{
dalType = proc.DalType;
database = proc.dalHelper.DataBase;
proc.SetAopState(Aop.AopOp.CloseAll);
if (proc.DalType == DalType.MsSql)
{
proc.BeginTransation();//仅对mssql有效。
}
foreach (string sql in sqls)
{
proc.ResetProc(sql);
if (proc.ExeNonQuery() == -2)
{
proc.RollBack();
_ErrorMsg.AppendLine("AlterTable:" + proc.DebugInfo);
Log.WriteLogToTxt(proc.DebugInfo);
return(false);
}
}
proc.EndTransation();
}
RemoveCache(tableName, database, dalType);
return(true);
}
return(false);
}
/// <summary>
/// 移除一张表
/// <param name="conn">数据库链接</param>
/// </summary>
public static bool DropTable(string tableName, string conn)
{
bool result = false;
using (DbBase helper = DalCreate.CreateDal(conn))
{
DalType dalType = helper.dalType;
switch (dalType)
{
case DalType.Txt:
case DalType.Xml:
string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName);
string path = folder + ".ts";
try
{
if (File.Exists(path))
{
result = IOHelper.Delete(path);
}
path = folder + (dalType == DalType.Txt ? ".txt" : ".xml");
if (File.Exists(path))
{
result = IOHelper.Delete(path);
}
}
catch
{
}
break;
default:
result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2;
if (result)
{
RemoveCache(tableName, helper.DataBase, dalType);
}
break;
}
}
return(result);
}
private static string FormatCharIndex(string text, DalType dalType)
{
string key = SqlValue.CharIndex;
//select [#charindex]('ok',xxx) from xxx where [#charindex]('ok',xx)>0
int index = text.IndexOf(key, StringComparison.OrdinalIgnoreCase);
if (index > -1)//存在charIndex函数
{
switch (dalType)
{
case DalType.Access:
case DalType.Oracle:
int found = 0;
string func = string.Empty;
do
{
int start = index + key.Length;
text = text.Insert(index + 2, "_"); //select [#_charindex]('ok',xxx) from xxx where [#charindex]('ok',xx)>0
found = text.IndexOf(')', index + 4);
func = text.Substring(start + 2, found - start - 2);
string[] funs = func.Split(',');
text = text.Remove(start + 2, found - start - 2); //移除//select [#_charindex]() from xxx where [#charindex]('ok',xx)>0
text = text.Insert(start + 2, funs[1] + "," + funs[0]);
index = text.IndexOf(key, StringComparison.OrdinalIgnoreCase);
}while (index > -1);
text = text.Replace("#_", "#");
return(Replace(text, key, "instr"));
case DalType.MySql:
return(Replace(text, key, "locate"));
case DalType.MsSql:
case DalType.Sybase:
return(Replace(text, key, "charindex"));
case DalType.SQLite:
return(Replace(text, key, "charindex"));
}
}
return(text);
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)语句
/// </summary>
internal static string CreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
switch (dalType)
{
case DalType.Txt:
case DalType.Xml:
return(columns.ToJson(true));
default:
string createSql = string.Empty;
createSql = "CREATE TABLE " + SqlFormat.Keyword(tableName, dalType) + " \n(";
//读取主键的个数，如果是联合主键，则不设置主键。
List <MCellStruct> primaryKeyList = new List <MCellStruct>();
foreach (MCellStruct column in columns)
{
if (column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
foreach (MCellStruct column in columns)
{
createSql += "\n " + GetKey(column, dalType, ref primaryKeyList, version);
}
if (primaryKeyList.Count > 0)
{
createSql += GetUnionPrimaryKey(dalType, primaryKeyList);
}
createSql = createSql.TrimEnd(',') + " \n)";
// createSql += GetSuffix(dalType);
if (dalType == DalType.MySql && createSql.IndexOf("CURRENT_TIMESTAMP") != createSql.LastIndexOf("CURRENT_TIMESTAMP"))
{
createSql = createSql.Replace("Default CURRENT_TIMESTAMP", string.Empty); //mysql不允许存在两个以上的CURRENT_TIMESTAMP。
}
primaryKeyList.Clear();
return(createSql);
}
}
internal static string FormatGUID(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
return(text.Replace(SqlValue.GUID, "GenGUID()"));
case DalType.MySql:
return(text.Replace(SqlValue.GUID, "UUID()"));
case DalType.MsSql:
case DalType.Sybase:
return(text.Replace(SqlValue.GUID, "newid()"));
case DalType.Oracle:
return(text.Replace(SqlValue.GUID, "SYS_GUID()"));
case DalType.SQLite:
return(text.Replace(SqlValue.GUID, ""));
}
return(text);
}
internal static string FormatIsNull(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
int index = text.IndexOf(SqlValue.IsNull, StringComparison.OrdinalIgnoreCase); //isnull (isnull(aaa),'3,3') iif(isnull (aaa),333,aaa)
if (index > -1)
{
do
{
index = text.IndexOf('(', index);
int end = text.IndexOf(',', index);
string key = text.Substring(index + 1, end - index - 1); //找到 aaa
text = text.Insert(end, ")"); //
end = text.IndexOf(')', end + 3);
text = text.Insert(end, "," + key);
index = text.IndexOf(SqlValue.IsNull, end, StringComparison.OrdinalIgnoreCase); //寻找还有没有第二次出现的函数字段
}while (index > -1);
return(Replace(text, SqlValue.IsNull, "iif(isnull"));
}
break;
case DalType.SQLite:
case DalType.MySql:
return(Replace(text, SqlValue.IsNull, "IfNull"));
case DalType.Oracle:
return(Replace(text, SqlValue.IsNull, "NVL"));
case DalType.PostgreSQL:
return(Replace(text, SqlValue.IsNull, "COALESCE"));
case DalType.MsSql:
case DalType.Sybase:
default:
return(Replace(text, SqlValue.IsNull, "IsNull"));
}
return(text);
}
/// <summary>
/// Sql关键字处理
/// </summary>
public static string Keyword(string name, DalType dalType)
{
if (!string.IsNullOrEmpty(name))
{
name = name.Trim();
if (name.IndexOfAny(new char[] { ' ', '[', ']', '`', '"', '(', ')' }) == -1)
{
string pre = null;
int i = name.LastIndexOf('.');// 增加跨库支持（demo.dbo.users）
if (i > 0)
{
string[] items = name.Split('.');
pre = items[0];
name = items[items.Length - 1];
}
switch (dalType)
{
case DalType.Access:
return("[" + name + "]");
case DalType.MsSql:
case DalType.Sybase:
return((pre == null ? "" : pre + "..") + "[" + name + "]");
case DalType.MySql:
return((pre == null ? "" : pre + ".") + "`" + name + "`");
case DalType.SQLite:
case DalType.PostgreSQL:
return("\"" + name + "\"");
case DalType.Txt:
case DalType.Xml:
return(NotKeyword(name));
}
}
}
return(name);
}
private static string GetUnionPrimaryKey(DalType dalType, List <MCellStruct> primaryKeyList)
{
string suffix = "\n ";
switch (dalType)
{
//case DalType.Access:
//case DalType.SQLite:
//case DalType.MySql:
//case DalType.Oracle:
//case DalType.MsSql:
//case DalType.Sybase:
default:
suffix += "PRIMARY KEY (";
foreach (MCellStruct st in primaryKeyList)
{
suffix += SqlFormat.Keyword(st.ColumnName, dalType) + ",";
}
suffix = suffix.TrimEnd(',') + ")";
break;
}
return(suffix);
}
internal static string FormatGetDate(string text, DalType dalType)
{
switch (dalType)
{
case DalType.Access:
case DalType.MySql:
return(Replace(text, SqlValue.GetDate, "now()"));
case DalType.MsSql:
case DalType.Sybase:
return(Replace(text, SqlValue.GetDate, "getdate()"));
case DalType.Oracle:
return(Replace(text, SqlValue.GetDate, "current_date"));
case DalType.SQLite:
return(Replace(text, SqlValue.GetDate, "datetime('now','localtime')"));
case DalType.Txt:
case DalType.Xml:
return(Replace(text, SqlValue.GetDate, "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'"));
}
return(text);
}
/// <summary>
/// 检测GUID，若空，补值。
/// </summary>
private bool CheckGUIDAndDateTime(DalType dal)
{
bool fillGUID = false;
int groupID;
for (int i = 0; i < mdt.Columns.Count; i++)
{
MCellStruct ms = mdt.Columns[i];
groupID = DataType.GetGroup(ms.SqlType);
if (groupID == 2)
{
for (int j = 0; j < mdt.Rows.Count; j++)
{
if (dal == DalType.MsSql && mdt.Rows[j][i].strValue == DateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
else if (dal == DalType.Oracle && mdt.Rows[j][i].strValue == SqlDateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
}
}
else if (ms.IsPrimaryKey && (groupID == 4 || (groupID == 0 && ms.MaxSize >= 36)))
{
string defaultValue = Convert.ToString(ms.DefaultValue);
bool isGuid = defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.GUID;
if (isGuid && !fillGUID)
{
fillGUID = true;
}
for (int k = 0; k < mdt.Rows.Count; k++)
{
if (mdt.Rows[k][i].IsNullOrEmpty)
{
mdt.Rows[k][i].Value = isGuid ? Guid.NewGuid().ToString() : defaultValue;
}
}
}
}
return fillGUID;
}
public NoSqlAction(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
Reset(ref row, fileName, filePath, dalType);
}
/// <summary>
/// �������ݿ�Ĭ��ֵ��ʽ���ɱ�ֵ������ֵ��ԭ�ɸ����ݿ�Ĭ��ֵ
/// </summary>
/// <param name="flag">[0:ת�ɱ�ֵ],[1:ת�ɸ����ݿ�ֵ],[2:ת�ɸ����ݿ�ֵ�������ַ���ǰ��]</param>
/// <param name="sqlDbType">���е�ֵ</param>
/// <returns></returns>
public static string FormatDefaultValue(DalType dalType, object value, int flag, SqlDbType sqlDbType)
{
string defaultValue = Convert.ToString(value).TrimEnd('\n');//oracle���Դ�\n��β
if (dalType != DalType.Access)
{
defaultValue = defaultValue.Replace("GenGUID()", string.Empty);
}
if (defaultValue.Length == 0)
{
return null;
}
int groupID = DataType.GetGroup(sqlDbType);
if (flag == 0)
{
if (groupID == 2)//���ڵı�ֵ
{
return SqlValue.GetDate;
}
else if (groupID == 4)
{
return SqlValue.GUID;
}
switch (dalType)
{
case DalType.MySql://��ת\' \"�����Բ����滻��
defaultValue = defaultValue.Replace("\\\"", "\"").Replace("\\\'", "\'");
break;
case DalType.Access:
case DalType.SQLite:
defaultValue = defaultValue.Replace("\"\"", "��");
break;
default:
defaultValue = defaultValue.Replace("''", "��");
break;
}
switch (defaultValue.ToLower().Trim('(', ')'))
{
case "newid":
case "guid":
case "sys_guid":
case "genguid":
case "uuid":
return SqlValue.GUID;
}
}
else
{
if (defaultValue == SqlValue.GUID)
{
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
case DalType.Sybase:
return SqlCompatible.FormatGUID(defaultValue, dalType);
default:
return "";
}
}
}
switch (dalType)
{
case DalType.Access:
if (flag == 0)
{
if (defaultValue[0] == '"' && defaultValue[defaultValue.Length - 1] == '"')
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "Now()").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DalType.MsSql:
case DalType.Sybase:
if (flag == 0)
{
if (defaultValue.StartsWith("(") && defaultValue.EndsWith(")"))//���� (newid()) ��ȥ��()
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
defaultValue = defaultValue.Trim('N', '\'');//'(', ')',
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "getdate()").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "(N'" + defaultValue + "')";
}
}
break;
case DalType.Oracle:
if (flag == 0)
{
defaultValue = defaultValue.Trim('\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "'" + defaultValue + "'";
}
}
break;
case DalType.MySql:
if (flag == 0)
{
defaultValue = defaultValue.Replace("b'0", "0").Replace("b'1", "1").Trim('\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("'", "\\'").Replace("\"", "\\\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DalType.SQLite:
if (flag == 0)
{
defaultValue = defaultValue.Trim('"');
if (groupID > 0)//����һЩ���淶��д�����������͵ļ������� '0'
{
defaultValue = defaultValue.Trim('\'');
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
}
if (flag == 0)
{
return defaultValue.Replace("��", "\"").Replace("��", "'");
}
return defaultValue;
}
internal static void CreateSelectBaseProc(DalType dal, string conn)
{
try
{
switch (dal)
{
//case DalType.Oracle:
// if (!flag.Contains("oracle"))
// {
// flag.Add("oracle");
// using (DbBase db = DalCreate.CreateDal(conn))
// {
// db.AllowRecordSql = false;
// object o = db.ExeScalar(string.Format(ExistOracle.Replace("TABLE", "PROCEDURE"), "MyPackage.SelectBase"), false);
// if (o != null && Convert.ToInt32(o) < 1)
// {
// db.ExeNonQuery(SqlPager.GetPackageHeadForOracle(), false);
// db.ExeNonQuery(SqlPager.GetPackageBodyForOracle(), false);
// }
// }
// }
// break;
case DalType.MsSql:
if (!flag.Contains("sql"))
{
flag.Add("sql");//考虑到一个应用不太可能同时使用mssql的不同版本，只使用一个标识。
using (DbBase db = DalCreate.CreateDal(conn))
{
db.IsAllowRecordSql = false;
object o = null;
if (!db.Version.StartsWith("08"))
{
// o = db.ExeScalar(string.Format(Exist2000.Replace("U", "P"), "SelectBase"), false);
// if (o != null && Convert.ToInt32(o) < 1)
// {
// db.ExeNonQuery(SqlPager.GetSelectBaseForSql2000(), false);
// }
//}
//else
//{
o = db.ExeScalar(string.Format(TableSchema.Exist2005, "SelectBase", "P"), false);
if (o != null && Convert.ToInt32(o) < 1)
{
db.ExeNonQuery(SqlCreateForPager.GetSelectBaseForSql2005(), false);
}
}
}
}
break;
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
/// <summary>
/// ���
/// </summary>
/// <param name="row">�����нṹ</param>
/// <param name="fileName">�ļ�����</param>
/// <param name="filePath">�ļ�·��</param>
/// <param name="dalType">��������</param>
public void Reset(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
_insertRows.Clear();//�л����ʱ�����á�
_Row = row;
string exName = Path.GetExtension(fileName);
if (string.IsNullOrEmpty(exName))
{
switch (dalType)
{
case DalType.Txt:
_FileName = fileName + ".txt";
break;
case DalType.Xml:
_FileName = fileName + ".xml";
break;
}
}
else
{
_FileName = fileName;
}
_FileFullName = filePath + _FileName;
_DalType = dalType;
if (!_needToSaveState.ContainsKey(_FileFullName))
{
_needToSaveState.Add(_FileFullName, 0);
}
if (!_lockNextIDObj.ContainsKey(_FileFullName))
{
_lockNextIDObj.Add(_FileFullName, new object());
}
if (!_lockWriteTxtObj.ContainsKey(_FileFullName))
{
_lockWriteTxtObj.Add(_FileFullName, new object());
}
if (!_maxID.ContainsKey(_FileFullName))
{
_maxID.Add(_FileFullName, 0);
}
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
public static string GetCreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
return SqlCreateForSchema.CreateTableSql(tableName, columns, dalType, version);
}
/// <summary>
/// ��DbType�����ַ��������ʽ��Ӧӳ�䵽DbType����
/// </summary>
/// <param name="typeName">��������</param>
/// <returns></returns>
public static DbType GetDbType(string typeName, DalType dalType)
{
switch (typeName.ToLower().Replace("system.", ""))
{
case "ansistring":
return DbType.AnsiString;
case "ansistringfixedlength":
return DbType.AnsiStringFixedLength;
case "text":
case "ntext":
case "unitext":
case "string":
case "nvarchar":
return DbType.String;
case "char":
return DbType.AnsiStringFixedLength;
case "unichar":
case "nchar":
case "stringfixedlength":
return DbType.StringFixedLength;
case "varchar":
return DbType.AnsiString;
case "varbinary":
case "binary":
case "image":
case "byte[]":
return DbType.Binary;
case "bit":
case "boolean":
return DbType.Boolean;
case "tinyint":
case "byte":
return DbType.Byte;
case "smallmoney":
case "currency":
return DbType.Currency;
case "date":
return DbType.Date;
case "time":
return DbType.Time;
case "smalldatetime":
case "datetime":
return DbType.DateTime;
case "timestamp":
switch (dalType)
{
case DalType.MsSql:
case DalType.Sybase:
return DbType.Binary;
default:
return DbType.DateTime;
}
case "udt":
case "numeric":
case "decimal":
return DbType.Decimal;
case "real":
case "money":
case "double":
return DbType.Double;
case "uniqueidentifier":
case "guid":
return DbType.Guid;
case "smallint":
case "int16":
case "uint16":
return DbType.Int16;
case "int":
case "int32":
case "uint32":
return DbType.Int32;
case "bigint":
case "int64":
case "uint64":
return DbType.Int64;
case "variant":
case "object":
return DbType.Object;
case "sbyte":
return DbType.SByte;
case "float":
case "single":
return DbType.Single;
case "varnumeric":
return DbType.VarNumeric;
case "xml":
return DbType.Xml;
}
return DbType.String;
}
/// <summary>
/// 获取指定数据库的数据类型
/// </summary>
/// <param name="ms">单元格结构</param>
/// <param name="dalType">数据库类型</param>
/// <param name="version">数据库版本号</param>
/// <returns></returns>
public static string GetDataType(MCellStruct ms, DalType dalType, string version)
{
return(DataType.GetDataType(ms, dalType, version));
}
/// <summary>
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
/// </summary>
/// <param name="flag">[0:转成标准值],[1:转成各数据库值]</param>
/// <returns></returns>
public static string FormatDefaultValue(DalType dalType, object value, int flag, SqlDbType sqlDbType)
{
return(SqlFormat.FormatDefaultValue(dalType, value, flag, sqlDbType));
}
/// <summary>
/// ��ȡ�����еı��������
/// </summary>
internal static string GetTableDescriptionSql(string tableName, MCellStruct mcs, DalType dalType, bool isAdd)
{
switch (dalType)
{
case DalType.MsSql:
string spName = isAdd ? "sp_addextendedproperty" : "sp_updateextendedproperty";
return string.Format("exec {3} N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}'", mcs.Description, tableName, mcs.ColumnName, spName);
case DalType.Oracle:
return string.Format("comment on column {0}.{1} is '{2}'", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
return string.Empty;
}
private static string GetKey(MCellStruct column, DalType dalType, ref List<MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType);//������
int groupID = DataType.GetGroup(column.SqlType);//���ݿ����͡�
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement;//�Ƿ������������С�
if (dalType != DalType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DalType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DalType.MySql))//MySql ����������������.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DalType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else// ������
{
if (groupID == 4)//��������GUID
{
key += " default GenGUID()";
}
}
break;
case DalType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (groupID == 4)//��������GUID
{
key += " Default (newid())";
}
}
break;
case DalType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.GUID)//��������GUID
{
key += " Default (SYS_GUID())";
}
break;
case DalType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (groupID == 4)//��������GUID
{
key += " Default (newid())";
}
}
break;
case DalType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DalType.SQLite://sqlite��AUTOINCREMENT����д��primarykeyǰ,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear();//����������ӣ�ֻ���������һ��������
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && groupID < 5)//Ĭ��ֵֻ���ǻ��������С�
{
if (dalType == DalType.MySql)
{
if ((groupID == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (groupID == 2 && key.Contains("datetime"))) //ֻ�ܶ�TIMESTAMP���͵ĸ�Ĭ��ֵ��
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DalType.MySql) { defaultValue = defaultValue.Trim('(', ')'); }
key += " Default " + defaultValue;
}
}
er:
if (dalType != DalType.Access)
{
if (dalType == DalType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit ������ΪNull
}
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DalType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return key + ",";
}
/// <summary>
/// ��ȡָ���ı�ܹ����ɵ�SQL(Create Table)���
/// </summary>
internal static string CreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
switch (dalType)
{
case DalType.Txt:
case DalType.Xml:
return columns.ToJson(true);
default:
string createSql = string.Empty;
createSql = "CREATE TABLE " + SqlFormat.Keyword(tableName, dalType) + " \n(";
//��ȡ�����ĸ��������������������������������
List<MCellStruct> primaryKeyList = new List<MCellStruct>();
foreach (MCellStruct column in columns)
{
if (column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
foreach (MCellStruct column in columns)
{
createSql += "\n " + GetKey(column, dalType, ref primaryKeyList, version);
}
if (primaryKeyList.Count > 0)
{
createSql += GetUnionPrimaryKey(dalType, primaryKeyList);
}
createSql = createSql.TrimEnd(',') + " \n)";
// createSql += GetSuffix(dalType);
if (dalType == DalType.MySql && createSql.IndexOf("CURRENT_TIMESTAMP") != createSql.LastIndexOf("CURRENT_TIMESTAMP"))
{
createSql = createSql.Replace("Default CURRENT_TIMESTAMP", string.Empty);//mysql����������������ϵ�CURRENT_TIMESTAMP��
}
primaryKeyList.Clear();
return createSql;
}
}
/// <summary>
/// ��ȡָ���ı�ܹ����ɵ�SQL(Create Table)��˵�����
/// </summary>
internal static string CreateTableDescriptionSql(string tableName, MDataColumn columns, DalType dalType)
{
string result = string.Empty;
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
StringBuilder sb = new StringBuilder();
foreach (MCellStruct mcs in columns)
{
if (!string.IsNullOrEmpty(mcs.Description))
{
if (dalType == DalType.MsSql)
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}';\r\n", mcs.Description, tableName, mcs.ColumnName);
}
else if (dalType == DalType.Oracle)
{
sb.AppendFormat("comment on column {0}.{1} is '{2}';\r\n", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
}
}
result = sb.ToString().TrimEnd(';');
break;
}
return result;
}
private static void SetDefault()
{
DbBase db = DalCreate.CreateDal(DefaultConn);
if (db != null)
{
_DefaultDataBase = db.DataBase;
_DefaultDalType = db.dalType;
db.Dispose();
}
}
/// <summary>
/// ����������Ͷ�Ӧ��Type
/// </summary>
public static Type GetType(SqlDbType sqlType, DalType dalType)
{
switch (sqlType)
{
case SqlDbType.BigInt:
return typeof(Int64);
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.VarBinary:
return typeof(byte[]);
case SqlDbType.Bit:
return typeof(Boolean);
case SqlDbType.Text:
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Time:
case SqlDbType.Xml:
return typeof(String);
case SqlDbType.SmallDateTime:
case SqlDbType.DateTimeOffset:
case SqlDbType.DateTime2:
case SqlDbType.DateTime:
case SqlDbType.Date:
return typeof(DateTime);
case SqlDbType.Timestamp:
switch (dalType)
{
case DalType.MsSql:
case DalType.Sybase:
return typeof(byte[]);
default:
return typeof(DateTime);
}
case SqlDbType.Money:
case SqlDbType.Decimal:
case SqlDbType.SmallMoney:
case SqlDbType.Udt://����Ƕ�Numeric���͡�
return typeof(Decimal);
case SqlDbType.Float:
return typeof(Single);
case SqlDbType.Int:
return typeof(int);
case SqlDbType.Real:
return typeof(double);
case SqlDbType.TinyInt:
return typeof(Byte);
case SqlDbType.SmallInt:
return typeof(Int16);
case SqlDbType.UniqueIdentifier:
return typeof(Guid);
default:
return typeof(object);
}
}
/// <summary>
/// 将DbType类型字符串表达形式对应映射到DbType类型
/// </summary>
/// <param name="typeName">类型名称</param>
/// <returns></returns>
public static DbType GetDbType(string typeName, DalType dalType)
{
switch (typeName.ToLower().Replace("system.", ""))
{
case "ansistring":
return(DbType.AnsiString);
case "ansistringfixedlength":
return(DbType.AnsiStringFixedLength);
case "text":
case "ntext":
case "unitext":
case "string":
case "nvarchar":
return(DbType.String);
case "char":
return(DbType.AnsiStringFixedLength);
case "unichar":
case "nchar":
case "stringfixedlength":
return(DbType.StringFixedLength);
case "varchar":
return(DbType.AnsiString);
case "varbinary":
case "binary":
case "image":
case "byte[]":
return(DbType.Binary);
case "bit":
case "boolean":
return(DbType.Boolean);
case "tinyint":
case "byte":
return(DbType.Byte);
case "smallmoney":
case "currency":
return(DbType.Currency);
case "date":
return(DbType.Date);
case "time":
return(DbType.Time);
case "smalldatetime":
case "datetime":
return(DbType.DateTime);
case "timestamp":
switch (dalType)
{
case DalType.MsSql:
case DalType.Sybase:
return(DbType.Binary);
default:
return(DbType.DateTime);
}
case "udt":
case "numeric":
case "decimal":
return(DbType.Decimal);
case "real":
case "money":
case "double":
return(DbType.Double);
case "uniqueidentifier":
case "guid":
return(DbType.Guid);
case "smallint":
case "int16":
case "uint16":
return(DbType.Int16);
case "int":
case "int32":
case "uint32":
return(DbType.Int32);
case "bigint":
case "int64":
case "uint64":
return(DbType.Int64);
case "variant":
case "object":
return(DbType.Object);
case "sbyte":
return(DbType.SByte);
case "float":
case "single":
return(DbType.Single);
case "varnumeric":
return(DbType.VarNumeric);
case "xml":
return(DbType.Xml);
}
return(DbType.String);
}
/// <summary>
/// 获取指定数据库的数据类型
/// </summary>
/// <param name="ms">单元格结构</param>
/// <param name="dalType">数据库类型</param>
/// <param name="version">数据库版本号</param>
/// <returns></returns>
public static string GetDataType(MCellStruct ms, DalType dalType, string version)
{
return DataType.GetDataType(ms, dalType, version);
}
/// <summary>
/// ��ĸ�ͷ���0�������ͷ���1�������ͷ���2��bool����3��guid����4����������999
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static int GetGroup(SqlDbType sqlDbType, DalType dalType)
{
switch (sqlDbType)
{
case SqlDbType.Int:
case SqlDbType.TinyInt:
case SqlDbType.BigInt:
case SqlDbType.SmallInt:
case SqlDbType.Float:
case SqlDbType.Real:
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
return 1;
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.NChar:
case SqlDbType.Char:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.Time:
return 0;
case SqlDbType.Date:
case SqlDbType.DateTimeOffset:
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.DateTime2:
return 2;
case SqlDbType.Bit:
return 3;
case SqlDbType.UniqueIdentifier:
return 4;
default:
if (sqlDbType == SqlDbType.Timestamp)
{
if (dalType != DalType.MsSql && dalType != DalType.Sybase)
{
return 2;
}
}
return 999;
}
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
public static string GetCreateTableDescriptionSql(string tableName, MDataColumn columns, DalType dalType)
{
return SqlCreateForSchema.CreateTableDescriptionSql(tableName, columns, dalType);
}
/// <summary>
/// �����ܹ�Key
/// </summary>
internal static string GetSchemaKey(string tableName, string dbName, DalType dalType)
{
string key = tableName;
int start = key.IndexOf('(');
int end = key.LastIndexOf(')');
if (start > -1 && end > -1)//�Զ���table
{
key = "View" + Tool.MD5.Get(key);
}
else
{
if (key.IndexOf('.') > 0)
{
dbName = key.Split('.')[0];
}
key = SqlFormat.NotKeyword(key);
}
return "ColumnsCache:" + dalType + "_" + dbName + "_" + key;
}
private static string GetUnionPrimaryKey(DalType dalType, List<MCellStruct> primaryKeyList)
{
string suffix = "\n ";
switch (dalType)
{
//case DalType.Access:
//case DalType.SQLite:
//case DalType.MySql:
//case DalType.Oracle:
//case DalType.MsSql:
//case DalType.Sybase:
default:
suffix += "PRIMARY KEY (";
foreach (MCellStruct st in primaryKeyList)
{
suffix += SqlFormat.Keyword(st.ColumnName, dalType) + ",";
}
suffix = suffix.TrimEnd(',') + ")";
break;
}
return suffix;
}
/// <summary>
/// Sql�ؼ��ִ���
/// </summary>
public static string Keyword(string name, DalType dalType)
{
if (!string.IsNullOrEmpty(name))
{
name = name.Trim();
if (name.IndexOfAny(new char[] { ' ', '[', ']', '`', '"', '(', ')' }) == -1)
{
string pre = null;
int i = name.LastIndexOf('.');// ���ӿ��֧�֣�demo.dbo.users��
if (i > 0)
{
string[] items = name.Split('.');
pre = items[0];
name = items[items.Length - 1];
}
switch (dalType)
{
case DalType.Access:
return "[" + name + "]";
case DalType.MsSql:
case DalType.Sybase:
return (pre == null ? "" : pre + "..") + "[" + name + "]";
case DalType.MySql:
return (pre == null ? "" : pre + ".") + "`" + name + "`";
case DalType.SQLite:
return "\"" + name + "\"";
case DalType.Txt:
case DalType.Xml:
return NotKeyword(name);
}
}
}
return name;
}
/// <summary>
/// 为字段或表名添加关键字标签：如[],''等符号
/// </summary>
/// <param name="name">表名或字段名</param>
/// <param name="dalType">数据类型</param>
/// <returns></returns>
public static string Keyword(string name, DalType dalType)
{
return(SqlFormat.Keyword(name, dalType));
}
/// <summary>
/// 为字段或表名添加关键字标签：如[],''等符号
/// </summary>
/// <param name="name">表名或字段名</param>
/// <param name="dalType">数据类型</param>
/// <returns></returns>
public static string Keyword(string name, DalType dalType)
{
return SqlFormat.Keyword(name, dalType);
}
/// <summary>
/// 获得数据类型对应的Type
/// </summary>
public static Type GetType(SqlDbType sqlType, DalType dalType)
{
switch (sqlType)
{
case SqlDbType.BigInt:
return(typeof(Int64));
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.VarBinary:
return(typeof(byte[]));
case SqlDbType.Bit:
return(typeof(Boolean));
case SqlDbType.Text:
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Time:
case SqlDbType.Xml:
return(typeof(String));
case SqlDbType.SmallDateTime:
case SqlDbType.DateTimeOffset:
case SqlDbType.DateTime2:
case SqlDbType.DateTime:
case SqlDbType.Date:
return(typeof(DateTime));
case SqlDbType.Timestamp:
switch (dalType)
{
case DalType.MsSql:
case DalType.Sybase:
return(typeof(byte[]));
default:
return(typeof(DateTime));
}
case SqlDbType.Money:
case SqlDbType.Decimal:
case SqlDbType.SmallMoney:
case SqlDbType.Udt: //这个是顶Numeric类型。
return(typeof(Decimal));
case SqlDbType.Float:
return(typeof(Single));
case SqlDbType.Int:
return(typeof(int));
case SqlDbType.Real:
return(typeof(double));
case SqlDbType.TinyInt:
return(typeof(Byte));
case SqlDbType.SmallInt:
return(typeof(Int16));
case SqlDbType.UniqueIdentifier:
return(typeof(Guid));
default:
return(typeof(object));
}
}
/// <summary>
/// 获取缓存表架构Key
/// </summary>
public static string GetSchemaCacheKey(string tableName, string dbName, DalType dalType)
{
return TableSchema.GetSchemaKey(tableName, dbName, dalType);
}
internal static string FormatConn(DalType dal, string connString)
{
if (dal != DalType.Access)
{
string conn = connString.ToLower();
int index = conn.IndexOf("provider");
if (index > -1 && index < connString.Length - 5 && (connString[index + 8] == '=' || connString[index + 9] == '='))
{
int end = conn.IndexOf(';', index);
if (end > index)
{
connString = connString.Remove(index, end - index + 1);
}
}
}
return connString;
}
public static string Filter(string text, DalType dalType)
{
string[] items = null;
if (text.IndexOf("--") > -1)
{
items = text.Split(new string[] { "--" }, StringSplitOptions.None);
for (int i = 0; i < items.Length - 1; i++)
{
if (items[i].Split('\'').Length % 2 == (i == 0 ? 1 : 0))
{
text = text.Replace("--", string.Empty);//name like'% --aaa' --or name='--aa' ǰ��� ' �ű����ǵ���
break;
}
}
items = null;
}
//foreach (string item in replaceSqlInjection.Split(','))
//{
// text = text.Replace(item, string.Empty);
//}
//text = text.Replace("--", "").Replace(";", "").Replace("&", "").Replace("*", "").Replace("||", "");
items = text.Split(' ', '(', ')');
if (items.Length == 1 && text.Length > 30)
{
if (text.IndexOf("%20") > -1)
{
Log.WriteLog(true, text);//��¼��־
return "SqlInjection error:" + text;
}
}
else
{
switch (dalType)
{
case DalType.MySql:
case DalType.Oracle:
case DalType.SQLite:
for (int i = 0; i < items.Length; i++)//ȥ���ֶε�[�ֶ�]����������
{
if (!items[i].StartsWith("[#") && items[i].StartsWith("[") && items[i].EndsWith("]"))
{
text = text.Replace(items[i], items[i].Replace("[", string.Empty).Replace("]", string.Empty));
}
}
break;
}
}
string lowerText = text.ToLower();
items = lowerText.Split(' ', '(', ')');
int keyIndex = -1;
bool isOK = false;
string tempKey = string.Empty;
string filterKey = string.Empty;
string[] filterSpitItems = null;
for (int i = 0; i < FilterKeyList.Length; i++)
{
filterSpitItems = filterKeyList[i].Split(';');//�ָ�
filterKey = filterSpitItems[0];//ȡ��һ��Ϊ�ؼ���
if (filterSpitItems.Length > 2)
{
continue;
}
else if (filterSpitItems.Length == 2) // ����������ʵġ�
{
keyIndex = Math.Min(lowerText.IndexOf(filterKey), lowerText.IndexOf(filterSpitItems[1]));
}
else
{
keyIndex = lowerText.IndexOf(filterKey);//���˵Ĺؼ��ʻ����
}
if (keyIndex > -1)
{
foreach (string item in items) // �û���������ÿһ�������Ĵ�
{
tempKey = item.Trim('\'', '|', '!', '%', '^');
if (tempKey.IndexOf(filterKey) > -1 && tempKey.Length > filterKey.Length)
{
isOK = true;
break;
}
}
if (!isOK)
{
Log.WriteLog(true, FilterKeyList[i] + ":" + text);//��¼��־
return "SqlInjection error:" + text;
}
else
{
isOK = false;
}
}
}
return text;
}
