private static void InitDatabase()
{
sqlite3Operate = SQLite3Factory.OpenOrCreate(MD5Utility.GetBytesMD5(Encoding.UTF8.GetBytes("SQLite3Prefs.db")));
if (!sqlite3Operate.TableExists(tableName))
{
sqlite3Operate.Exec(sql);
}
}
public static void Creator(ref TableData InTableData, string InDatabasePath)
{
string path = Application.dataPath + "/" + InDatabasePath;
SQLite3Operate handle = new SQLite3Operate(path, SQLite3OpenFlags.Create | SQLite3OpenFlags.ReadWrite);
StringBuilder sb = new StringBuilder(256);
handle.Exec("DROP TABLE IF EXISTS " + InTableData.TableName);
sb.Append("CREATE TABLE ")
.Append(InTableData.TableName)
.Append("(");
int length = InTableData.ColumnName.Length;
for (int i = 0; i < length; i++)
{
if (InTableData.IsColumnEnables[i])
{
sb.Append(InTableData.ColumnName[i])
.Append(" ")
.Append(InTableData.SQLite3Types[i])
.Append(GetConnstraint(InTableData.SQLite3Constraints[i]))
.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
handle.Exec(sb.ToString());
if (null != InTableData.ExcelContents)
{
length = InTableData.ExcelContents.Length;
int subLength;
ICell cell;
for (int i = 0; i < length; i++)
{
subLength = InTableData.ExcelContents[i].Length;
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO ").Append(InTableData.TableName).Append(" VALUES(");
for (int j = 0; j < subLength; j++)
{
if (InTableData.IsColumnEnables[j])
{
cell = InTableData.ExcelContents[i][j];
switch (InTableData.SQLite3Types[j])
{
case SQLite3ValueType.INTEGER:
if (null == cell)
{
sb.Append(0);
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append((int)cell.NumericCellValue);
break;
case CellType.String:
int result;
sb.Append(int.TryParse(cell.StringCellValue, out result)
? result
: 0);
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
}
break;
case SQLite3ValueType.REAL:
if (null == cell)
{
sb.Append(0);
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append(cell.NumericCellValue);
break;
case CellType.String:
double result;
sb.Append(double.TryParse(cell.StringCellValue, out result)
? result
: 0);
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
}
break;
default:
if (null == cell)
{
sb.Append("''");
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append("\'")
.Append(cell.NumericCellValue)
.Append("\'");
break;
case CellType.String:
sb.Append(ConvertTypeUtility.CheckSingleQuoteMatch(cell.StringCellValue));
break;
case CellType.Boolean:
sb.Append("\'")
.Append(cell.BooleanCellValue.ToString())
.Append("\'");
break;
default:
sb.Append("''");
break;
}
}
break;
}
sb.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
handle.Exec(sb.ToString());
}
}
handle.CloseDB();
}
public static void Creator(ref TableData InTableData, string InDatabasePath)
{
SQLite3Operate handle = new SQLite3Operate(InDatabasePath, SQLite3OpenFlags.Create | SQLite3OpenFlags.ReadWrite);
StringBuilder sb = new StringBuilder(256);
handle.Exec("DROP TABLE IF EXISTS " + InTableData.TableName);
sb.Append("CREATE TABLE ")
.Append(InTableData.TableName)
.Append("(");
int length = InTableData.ColumnName.Length;
for (int i = 0; i < length; i++)
{
if (InTableData.IsColumnEnables[i])
{
sb.Append(InTableData.ColumnName[i])
.Append(" ")
.Append(InTableData.SQLite3Types[i])
.Append(GetConstraint(InTableData.SQLite3Constraints[i]))
.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
handle.Exec(sb.ToString());
if (null != InTableData.ExcelContents)
{
length = InTableData.ExcelContents.Length;
for (int i = 0; i < length; i++)
{
var subLength = InTableData.ExcelContents[i].Length;
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO ").Append(InTableData.TableName).Append(" VALUES(");
for (int j = 0; j < subLength; j++)
{
if (InTableData.IsColumnEnables[j])
{
var cell = InTableData.ExcelContents[i][j];
switch (InTableData.SQLite3Types[j])
{
case SQLite3ValueType.Integer:
if (null == cell)
{
sb.Append(0);
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append((int)cell.NumericCellValue);
break;
case CellType.String:
{
int result;
sb.Append(int.TryParse(cell.StringCellValue, out result)
? result
: 0);
}
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
sb.Append((int)cell.NumericCellValue);
break;
case CellType.String:
{
int result;
sb.Append(int.TryParse(cell.StringCellValue, out result)
? result
: 0);
}
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
}
break;
case SQLite3ValueType.Real:
if (null == cell)
{
sb.Append(0);
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append(cell.NumericCellValue);
break;
case CellType.String:
{
double result;
sb.Append(double.TryParse(cell.StringCellValue, out result)
? result
: 0);
}
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
sb.Append(cell.NumericCellValue);
break;
case CellType.String:
{
double result;
sb.Append(double.TryParse(cell.StringCellValue, out result)
? result
: 0);
}
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
break;
case CellType.Boolean:
sb.Append(cell.BooleanCellValue ? 1 : 0);
break;
default:
sb.Append(0);
break;
}
}
break;
case SQLite3ValueType.Text:
if (null == cell)
{
sb.Append("''");
}
else
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append("\'")
.Append(cell.NumericCellValue)
.Append("\'");
break;
case CellType.String:
sb.Append(SQLite3Utility.CheckSqlValue(cell.StringCellValue));
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
sb.Append("\'")
.Append(cell.NumericCellValue)
.Append("\'");
break;
case CellType.String:
sb.Append(SQLite3Utility.CheckSqlValue(cell.StringCellValue));
break;
case CellType.Boolean:
sb.Append("\'")
.Append(cell.BooleanCellValue.ToString())
.Append("\'");
break;
default:
sb.Append("''");
break;
}
break;
case CellType.Boolean:
sb.Append("\'")
.Append(cell.BooleanCellValue.ToString())
.Append("\'");
break;
default:
sb.Append("''");
break;
}
}
break;
}
sb.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
handle.Exec(sb.ToString());
}
}
handle.CloseDb();
UpdateSQLite3Version(InDatabasePath);
}