private SQLiteConnection CreateConnection(DataLinkParams dlp)
{
ConnectionStringParser csp = ConnectionStringParser.Parse(dlp.ConnectionString);
if (csp == null)
{
return(null);
}
string dataBasePath = csp.GetDataLocation();
if (string.IsNullOrEmpty(dataBasePath))
{
return(null);
}
StorageFolder basePath = C1FlexReport.GetActualBasePath(dlp.Report);
StorageFile dataBaseFile = FileNameParser.ReplaceSpecialFolderTags(dataBasePath, basePath);
if (dataBaseFile == null)
{
return(null);
}
try
{
return(new SQLiteConnection(dataBaseFile.Path, SQLiteOpenFlags.ReadOnly));
}
catch
{
return(null);
}
}
public override bool Validate(DataLinkParams dlp)
{
SQLiteConnection connection = CreateConnection(dlp);
if (connection == null)
{
return(false);
}
connection.Dispose();
return(true);
}
private DbCommand CreateCommand(DataLinkParams dlp)
{
SQLiteConnection connection = CreateConnection(dlp);
if (connection == null)
{
return(null);
}
try
{
// initialize SQLiteCommand
DbCommand command;
switch (dlp.RecordSourceType)
{
case RecordSourceType.TableDirect:
command = new DbCommand(connection)
{
CommandText = "select * from " + dlp.RecordSource,
};
return(command);
case RecordSourceType.Text:
command = new DbCommand(connection);
PrepareDbCommand(command, dlp, dlp.RecordSource);
return(command);
}
// auto command type
command = new DbCommand(connection);
PrepareDbCommand(command, dlp, dlp.RecordSource);
if (CheckQuery(command))
{
return(command);
}
PrepareDbCommand(command, dlp, "select * from " + dlp.RecordSource);
return(command);
}
catch
{
if (connection != null)
{
connection.Dispose();
}
return(null);
}
}
public override object GetDataObject(DataLinkParams dlp)
{
DbCommand command = CreateCommand(dlp);
if (command == null)
{
return(null);
}
using (command)
{
try
{
var stmt = SQLite3.Prepare2(command.Connection.Handle, command.CommandText);
using (stmt)
{
// setup parameters
BindParameters(stmt, command);
// build Recordset object
Recordset result = new Recordset();
// create columns
GetColumns(stmt, out result.Names, out result.Types);
for (int i = 0; i < result.Names.Length; i++)
{
result.AddColumn();
}
// fill recordset
object[] values = new object[result.Names.Length];
while (SQLite3.Step(stmt) == SQLite3.Result.Row && (dlp.MaxRecords <= 0 || result.Count < dlp.MaxRecords))
{
for (int i = 0; i < result.Names.Length; i++)
{
values[i] = ReadCol(stmt, i, SQLite3.ColumnType(stmt, i));
}
result.Add(values);
}
return(result);
}
}
catch
{
return(null);
}
}
}
private void GetSchema()
{
// note: don't call Clear(), that removes all rows in all tables
// we want to get rid of the tables, relations, etc altogether.
this.Reset();
// go get the schema
EnforceConstraints = false;
DataLinkParams dlp = new DataLinkParams()
{
ConnectionString = _conn,
};
using (OleDbConnection conn = (OleDbConnection)OledbLink.Instance.CreateConnection(dlp))
{
conn.Open();
GetTables(conn);
GetRelations(conn);
GetConstraints(conn);
conn.Close();
}
}
protected override void GetDataSourceInfo(DataLinkParams dlp, DataSourceInfo dsi)
{
DbCommand command = CreateCommand(dlp);
if (command == null)
{
return;
}
using (command)
{
try
{
using (var stmt = SQLite3.Prepare2(command.Connection.Handle, command.CommandText))
{
//
string[] names;
Type[] types;
GetColumns(stmt, out names, out types);
for (int i = 0; i < names.Length; i++)
{
C1.Xaml.FlexReport.FieldInfo fi = new C1.Xaml.FlexReport.FieldInfo(names[i], types[i]);
dsi.Fields.Add(fi);
if (IsImageField(fi.DataType))
{
dsi.ImageFields.Add(fi);
}
else
{
dsi.TextFields.Add(fi);
}
}
}
}
catch
{
}
}
}
private void _cbDataProvider_SelectedIndexChanged(object sender, EventArgs e)
{
if (Updating)
{
return;
}
// check connection string and clear it if it is not valid
// for selected data provider
DataProviderDesc dpd = _cbDataProvider.SelectedItem as DataProviderDesc;
if (dpd.Link != DsgnAutoLink.Instance)
{
if (!dpd.Link.Link.Available)
{
// selected data provider is not installed on the system
// show warning for user
MessageForm.Warn(string.Format(Strings.DataSourcePicker.ProviderNotInstalledFmt, dpd.Link.Caption));
_cbConnectionString.Text = string.Empty;
}
else
{
DataLinkParams dlp = new DataLinkParams();
dlp.ConnectionString = _cbConnectionString.Text;
if (DataSource != null)
{
dlp.Report = DataSource.ParentReport;
}
if (!dpd.Link.Link.Validate(dlp))
{
// connection string is not valid clear it
_cbConnectionString.Text = string.Empty;
}
}
}
ConnectionStringChanged(RecordSource);
}
private void PrepareDbCommandProcessParameter(DbCommand command, DataLinkParams dlp, ReportParameter rp, StringBuilder sb, string s, string lastIdent, int lastIdentEnd, int curIndex)
{
bool addMultiValueBrackets = false;
if (string.Compare(lastIdent, "in", true) == 0)
{
// from lastIdentEnd to curIndex ( should be
addMultiValueBrackets = s.IndexOf('(', lastIdentEnd, curIndex - lastIdentEnd) == -1;
if (addMultiValueBrackets)
{
// need to add brackets
if (dlp.ParameterPassingMode == ParameterPassingMode.Literal && dlp.EncloseParameterValues)
{
addMultiValueBrackets = false;
}
}
}
if (addMultiValueBrackets)
{
sb.Append("(");
}
switch (dlp.ParameterPassingMode)
{
case ParameterPassingMode.Default:
if (IsNull(rp.Value))
{
sb.Append("?");
command.Parameters.Add(CreateParameter(rp, System.DBNull.Value));
}
else
{
Array array = rp.Value as Array;
if (array == null)
{
sb.Append("?");
command.Parameters.Add(CreateParameter(rp, rp.Value));
}
else
{
if (array.Length == 0)
{
sb.Append("?");
command.Parameters.Add(CreateParameter(rp, System.DBNull.Value));
}
else
{
for (int i = 0; i < array.GetLength(0); i++)
{
object v = array.GetValue(i);
sb.Append("?");
if (IsNull(v))
{
command.Parameters.Add(CreateParameter(rp, System.DBNull.Value));
}
else
{
command.Parameters.Add(CreateParameter(rp, v));
}
if (i < array.GetLength(0) - 1)
{
sb.Append(c_MultiValueParamValueDelimiter);
}
}
}
}
}
break;
case ParameterPassingMode.Literal:
string paramValue = ParameterValueToString(dlp, rp);
sb.Append(paramValue);
break;
default:
// should never be here
throw new NotImplementedException();
}
if (addMultiValueBrackets)
{
sb.Append(")");
}
}
private void PrepareDbCommand(
DbCommand command,
DataLinkParams dlp,
string recordSource)
{
command.Parameters.Clear();
if (string.IsNullOrEmpty(recordSource) || dlp.Parameters == null || dlp.Parameters.Count <= 0)
{
command.CommandText = recordSource;
return;
}
int lastIdentEnd = -1;
string lastIdent = string.Empty;
string s = recordSource;
StringBuilder sb = new StringBuilder(s.Length);
int i = 0;
while (i < s.Length)
{
if (s[i] == c_IdentOpenBracket)
{
int j = i;
while (i < s.Length && s[i] != c_IdentCloseBracket)
{
i++;
}
if (i >= s.Length)
{
sb.Append(s, j, i - j);
}
else
{
ReportParameter rp = (ReportParameter)dlp.Parameters.FindByName(s.Substring(j + 1, i - j - 1));
if (rp == null)
{
sb.Append(s, j, i - j + 1);
}
else
{
PrepareDbCommandProcessParameter(command, dlp, rp, sb, s, lastIdent, lastIdentEnd, j);
}
i++;
}
}
else if (StringParser.IsIdentStartChar(s[i]))
{
int j = i;
while (i < s.Length && StringParser.IsIdentChar(s[i]))
{
i++;
}
ReportParameter rp = (ReportParameter)dlp.Parameters.FindByName(s.Substring(j, i - j));
if (rp == null)
{
lastIdent = s.Substring(j, i - j);
lastIdentEnd = i;
sb.Append(s, j, i - j);
}
else
{
PrepareDbCommandProcessParameter(command, dlp, rp, sb, s, lastIdent, lastIdentEnd, j);
}
}
else
{
sb.Append(s[i]);
i++;
}
}
command.CommandText = sb.ToString();
}