OnQueryDefinitionsReadOneDbParameterProcess
(
IDataReader reader
, SqliteParameter parameter
, string connectionString
)
{
var originalDbTypeName = (string)(reader["DATA_TYPE"]);
var dbTypeName = originalDbTypeName;
SqliteType dbType = SqliteType.Integer;
var r = Enum
.TryParse
(
dbTypeName
, true
, out dbType
);
if (r)
{
parameter
.SqliteType = dbType;
}
return(parameter);
}
protected override void AddParameter <R, P>(DbParameterCollection collection, string name, P type, R value)
{
SqliteType typeImplemented = (SqliteType)Convert.ToInt32(type);
var parameter = (collection as SqliteParameterCollection).Add("@" + name, typeImplemented);
parameter.Value = value;
}
internal override object GetValue(SqliteStatement stmt, int index, SqliteType typ)
{
lock (dbLock) {
object result = null;
if (stmt != null && stmt._sqlite_stmt != null && stmt._sqlite_stmt.DataType(index) != SQLitePCL.SQLiteType.NULL)
{
// result = Convert.ChangeType(stmt._sqlite_stmt.GetText(index),
// SqliteConvert.DbTypeToType(SqliteConvert.TypeNameToDbType(stmt._sqlite_stmt.DataType(index).ToString())));
switch (stmt._sqlite_stmt.DataType(index))
{
case SQLitePCL.SQLiteType.BLOB:
result = stmt._sqlite_stmt.GetBlob(index);
break;
case SQLitePCL.SQLiteType.FLOAT:
result = stmt._sqlite_stmt.GetFloat(index);
break;
case SQLitePCL.SQLiteType.INTEGER:
result = stmt._sqlite_stmt.GetInteger(index);
break;
default:
result = stmt._sqlite_stmt.GetText(index);
break;
}
}
return(result);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SqliteParameter" /> class.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="type">The type of the parameter.</param>
public SqliteParameter(string name, SqliteType type)
{
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException(nameof(name));
}
_parameterName = name;
SqliteType = type;
}
public SqliteParameter(string name, SqliteType type)
{
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException(nameof(name));
}
_parameterName = name;
SqliteType = type;
}
private void Isse467_SqliteParameterNaming(bool prefix)
{
using (var connection = GetSQLiteConnection())
{
var cmd = connection.CreateCommand();
cmd.CommandText = "select @foo";
const SqliteType type = SqliteType.Integer;
cmd.Parameters.Add(prefix ? "@foo" : "foo", type).Value = 42;
var i = Convert.ToInt32(cmd.ExecuteScalar());
Assert.Equal(42, i);
}
}
public static SqliteParameterCollection AddWithValue(
this SqliteParameterCollection parameters,
string parameterName,
SqliteType dbType,
object value)
{
var parameter = new SqliteParameter(parameterName, dbType);
parameter.Value = value;
parameters.Add(parameter);
parameter.ResetSqliteType();
return(parameters);
}
private static SqliteParameter CreateParameter(string name, SqliteType type, object?value)
{
SqliteParameter param = new SqliteParameter(name, type);
if (value == null)
{
param.Value = DBNull.Value;
}
else
{
param.Value = value;
}
return(param);
}
/// <summary>
/// Creates a ADD COLUMN from current schema.
/// This MAY change de [DefaultValue] when [NotNull] to Comply with Sqlite
/// </summary>
/// <returns></returns>
public string ExportAddColumnAsStatement()
{
if (string.IsNullOrEmpty(ColumnName))
{
throw new ArgumentNullException("ColumnName can not be null");
}
if (ColumnName.Any(c => char.IsWhiteSpace(c)))
{
throw new ArgumentNullException("ColumnName can not contain whitespaces");
}
if (ColumnName.Any(c => char.IsSymbol(c)))
{
throw new ArgumentNullException("ColumnName can not contain symbols");
}
StringBuilder sb = new StringBuilder();
sb.Append(" ADD COLUMN ");
sb.Append(ColumnName);
sb.Append(" ");
sb.Append(SqliteType.ToString());
sb.Append(" ");
if (IsAI)
{
sb.Append("AUTOINCREMENT ");
}
// Columns with PK cannot be added in Sqlite
// Columns with UNIQUE cannot be added in Sqlite
// Columns with [NOT NULL] MUST HAVE a [DEFAULT VALUE]
if (!AllowNulls)
{
sb.Append("NOT NULL ");
if (DefaultValue == null)
{
setReasonableDefault(this);
}
}
if (DefaultValue != null)
{
sb.Append($"DEFAULT '{DefaultValue}'");
}
return(sb.ToString());
}
/// <summary>
/// Creates a CREATE TABLE column statment from current schema
/// </summary>
public string ExportColumnDefinitionAsStatement()
{
if (string.IsNullOrEmpty(ColumnName))
{
throw new ArgumentNullException("ColumnName can not be null");
}
if (ColumnName.Any(c => char.IsWhiteSpace(c)))
{
throw new ArgumentNullException("ColumnName can not contain whitespaces");
}
if (ColumnName.Any(c => char.IsSymbol(c)))
{
throw new ArgumentNullException("ColumnName can not contain symbols");
}
StringBuilder sb = new StringBuilder();
sb.Append(ColumnName);
sb.Append(" ");
sb.Append(SqliteType.ToString());
sb.Append(" ");
if (IsPK)
{
sb.Append("PRIMARY KEY ");
}
if (IsAI)
{
sb.Append("AUTOINCREMENT ");
}
if (IsUnique)
{
sb.Append("UNIQUE ");
}
if (!AllowNulls)
{
sb.Append("NOT NULL ");
}
if (DefaultValue != null)
{
sb.Append($"DEFAULT '{DefaultValue}'");
}
return(sb.ToString());
}
private void Isse467_SqliteParameterNaming(bool prefix)
{
using (var connection = GetSQLiteConnection())
{
var cmd = connection.CreateCommand();
cmd.CommandText = "select @foo";
#if NET45
const DbType type = DbType.Int32;
#else
const SqliteType type = SqliteType.Integer;
#endif
cmd.Parameters.Add(prefix ? "@foo" : "foo", type).Value = 42;
var i = Convert.ToInt32(cmd.ExecuteScalar());
i.IsEqualTo(42);
}
}
/// <summary>
/// Create a column schema from TypeInfoItem
/// </summary>
public static IColumn FromInfo(TypeInfo info, TypeItemInfo pi)
{
SqliteType dataType = mapType(pi);
//Props
bool isKey = pi.Is(DatabaseWrapper.ColumnAttributes.PrimaryKey);
// Auto select
bool allowNulls = dataType == SqliteType.TEXT ||
dataType == SqliteType.BLOB;
// was specified ?
if (pi.Is(DatabaseWrapper.ColumnAttributes.AllowNull))
{
allowNulls = true;
}
if (pi.Is(DatabaseWrapper.ColumnAttributes.NotNull))
{
allowNulls = false;
}
bool isUnique = pi.Is(DatabaseWrapper.ColumnAttributes.Unique);
object defVal = null;
foreach (var attr in pi.DBAttributes)
{
if (attr.Attribute is DefaultValueAttribute def)
{
defVal = def;
break;
}
}
// create
return(new Column()
{
ColumnName = pi.Name,
AllowNulls = allowNulls,
NativeType = pi.Type,
SqliteType = dataType,
DefaultValue = defVal,
IsPK = isKey,
IsAI = isKey && dataType == SqliteType.INTEGER,
IsUnique = isUnique,
});
}
public static Type ToType(this SqliteType sqliteType)
{
switch (sqliteType)
{
case SqliteType.Integer:
return(typeof(long));
case SqliteType.Real:
return(typeof(double));
case SqliteType.Text:
return(typeof(string));
case SqliteType.Blob:
return(typeof(byte[]));
default:
return(typeof(DBNull));
}
}
public SQLiteColumn(string name, SqliteType type)
{
this.Name = name;
this.Type = type;
}
public SqliteParameter(string name, SqliteType type, int size, string sourceColumn)
: this(name, type, size)
{
SourceColumn = sourceColumn;
}
/// <summary> /// Adds a parameter to the collection. /// </summary> /// <param name="parameterName">The name of the parameter.</param> /// <param name="type">The SQLite type of the parameter.</param> /// <returns>The parameter that was added.</returns> public virtual SqliteParameter Add(string parameterName, SqliteType type) => Add(new SqliteParameter(parameterName, type));
//
// 摘要:
// Initializes a new instance of the Microsoft.Data.Sqlite.SqliteParameter class.
//
// 参数:
// name:
// The name of the parameter.
//
// type:
// The type of the parameter.
//
// size:
// The maximum size, in bytes, of the parameter.
public SqliteParameterNetCore(string name, SqliteType type, int size) : base(name, type, size)
{
}
public SqliteParameter(string name, SqliteType type, int size)
: this(name, type)
{
Size = size;
}
/// <summary>
/// Initializes a new instance of the <see cref="SqliteParameter" /> class.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="type">The type of the parameter.</param>
/// <param name="size">The maximum size, in bytes, of the parameter.</param>
public SqliteParameter(string name, SqliteType type, int size)
: this(name, type)
=> Size = size;
//
// 摘要:
// Initializes a new instance of the Microsoft.Data.Sqlite.SqliteParameter class.
//
// 参数:
// name:
// The name of the parameter.
//
// type:
// The type of the parameter.
//
// size:
// The maximum size, in bytes, of the parameter.
//
// sourceColumn:
// The source column used for loading the value. Can be null.
public SqliteParameterNetCore(string name, SqliteType type, int size, string sourceColumn) : base(name, type, size, sourceColumn)
{
}
/// <summary>
/// 生成Sql参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="value">参数值</param>
/// <param name="type">类型</param>
/// <returns>参数</returns>
public static SqliteParameter CreateParameter(string parameterName, object value, SqliteType type)
{
object val;
switch (value)
{
case null:
case DBNull _:
val = DBNull.Value;
break;
case Enum _:
val = Convert.ToInt32(value);
break;
case bool b:
val = b ? 1 : 0;
break;
default:
val = value;
break;
case string s:
return(CreateParameter(parameterName, s));
}
return(new SqliteParameter(parameterName, type)
{
Value = val
});
}
/// <summary>
/// Initializes a new instance of the <see cref="SqliteParameter" /> class.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="type">The type of the parameter.</param>
public SqliteParameter(string name, SqliteType type)
{
ParameterName = name;
SqliteType = type;
}
public SqliteParameter Add(string parameterName, SqliteType type, int size) =>
Add(new SqliteParameter(parameterName, type, size));
public SqliteParameter Add(string parameterName, SqliteType type, int size) => Add(new SqliteParameter(parameterName, type, size));
public SqliteParameter Add(string parameterName, SqliteType type, int size, string sourceColumn) =>
Add(new SqliteParameter(parameterName, type, size, sourceColumn));
/// <summary>
/// Creates a new instance of <see cref="SqliteTypeAttribute"/> class.
/// </summary>
/// <param name="sqliteType">A target <see cref="Microsoft.Data.Sqlite.SqliteType"/> value.</param>
public SqliteTypeAttribute(SqliteType sqliteType)
: base(typeof(SqliteParameter), nameof(SqliteParameter.SqliteType), sqliteType)
{
}
async Task <int> ImportData(string p_tblName, SqliteConnection p_conn)
{
var dbConn = Kit.Config.GetValue <string>($"SqliteModel:{p_tblName}:DbConn");
var select = Kit.Config.GetValue <string>($"SqliteModel:{p_tblName}:Data");
if (string.IsNullOrEmpty(select))
{
return(0);
}
Table tbl = await new MySqlAccess(dbConn).Query(select);
if (tbl.Count == 0)
{
return(0);
}
using (var cmd = p_conn.CreateCommand())
{
List <string> columns = new List <string>();
string colNames = null;
string paraNames = null;
// 查询表的所有列
using (var cmdCol = p_conn.CreateCommand())
{
cmdCol.CommandText = $"SELECT c.name, c.type FROM sqlite_master AS t, pragma_table_info(t.name) AS c WHERE t.type = 'table' and t.name='{p_tblName}'";
var colReader = cmdCol.ExecuteReader();
if (colReader != null && colReader.FieldCount == 2)
{
while (colReader.Read())
{
var colName = colReader[0].ToString();
columns.Add(colName);
if (colNames == null)
{
colNames = colName;
}
else
{
colNames += "," + colName;
}
if (paraNames == null)
{
paraNames = ":" + colName;
}
else
{
paraNames += ",:" + colName;
}
SqliteType tp = SqliteType.Text;
var colType = colReader[1].ToString().ToLower();
if (colType == "integer")
{
tp = SqliteType.Integer;
}
else if (colType == "real")
{
tp = SqliteType.Real;
}
cmd.Parameters.Add(colName, tp);
}
}
}
cmd.CommandText = $"insert into {p_tblName} ({colNames}) values ({paraNames})";
foreach (var row in tbl)
{
foreach (var col in columns)
{
var obj = row[col];
cmd.Parameters[col].Value = (obj == null ? DBNull.Value : obj);
}
cmd.ExecuteNonQuery();
}
}
return(tbl.Count);
}
public SqliteParameter Add(string parameterName, SqliteType type, int size, string sourceColumn) => Add(new SqliteParameter(parameterName, type, size, sourceColumn));
public SqliteParameter(string name, SqliteType type, int size, string sourceColumn)
: this(name, type, size)
{
SourceColumn = sourceColumn;
}
