public void TestUpdateDatasetDirect2()
{
var dc = DbEntry.Provider;
var sql = new SqlStatement("select [Id],[Name] from [People] where 1=0");
DataSet ds = dc.ExecuteDataset(sql);
DbEntry.UsingConnection(delegate
{
var da = (DbDataAdapter)dc.Driver.GetDbAdapter(dc.GetDbCommand(sql));
var cb = dc.Driver.GetCommandBuilder();
cb.DataAdapter = da;
DataTable dt = ds.Tables[0];
for (int i = 0; i < 10; i++)
{
object[] row = { 0, "jxf" };
dt.Rows.Add(row);
}
da.Update(ds);
ds.AcceptChanges();
});
var list = DbEntry.From<SinglePerson>().Where(Condition.Empty).OrderBy("Id").Select();
Assert.AreEqual(13, list.Count);
Assert.AreEqual("Tom", list[0].Name);
Assert.AreEqual("jxf", list[3].Name);
Assert.AreEqual("jxf", list[12].Name);
}
protected void FillDbParameters(SqlStatement sql, IDbCommand e)
{
if ( (!sql.Parameters.UserSetKey) && (e.CommandType == CommandType.StoredProcedure) )
{
string sKey = sql.SqlCommandText + ":" + ConnectionString;
if ( SpParameters.Contains(sKey) )
{
ArrayList al = CloneSpParameters( (ArrayList)SpParameters[sKey] );
foreach ( IDataParameter ip in al )
{
e.Parameters.Add( ip );
}
}
else
{
DeriveParameters(e);
RemoveReturnParameter(e.Parameters);
ArrayList ps = CloneSpParameters(e.Parameters);
SpParameters[sKey] = ps;
}
for ( int i=0; i<sql.Parameters.Count; i++ )
{
((IDataParameter)e.Parameters[i]).Value = sql.Parameters[i].Value;
}
}
else
{
// TODO: parse SqlCommandText and fill it to Parameters.
foreach ( DataParameter dp in sql.Parameters )
{
e.Parameters.Add(GetDbParameter(dp));
}
}
}
protected override SqlStatement ToSqlStatement(DbDialect dd, List<string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
string sqlString = string.Format(StatementTemplate, dd.QuoteForTableName(_tableName), Where.ToSqlText(dpc, dd, queryRequiredFields));
var sql = new SqlStatement(CommandType.Text, sqlString, dpc);
return sql;
}
public void TestCommonBulkCopy()
{
var sql = new SqlStatement("SELECT [Id],[Name] FROM [Books] ORDER BY [Id]");
var rcs = new List<long>();
DbEntry.Provider.ExecuteDataReader(sql, delegate(IDataReader dr)
{
DbEntry.NewConnection(delegate
{
IDbBulkCopy c = _sqlite.GetDbBulkCopy(false);
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.SqlRowsCopied += ((sender, e) => rcs.Add(e.RowsCopied));
c.WriteToServer(dr);
});
});
Assert.AreEqual(1, rcs.Count);
Assert.AreEqual(3, rcs[0]);
Assert.AreEqual(5, StaticRecorder.Messages.Count);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=1:Int64,@Name_1=Diablo:String)", StaticRecorder.Messages[0]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=2:Int64,@Name_1=Beijing:String)", StaticRecorder.Messages[1]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=3:Int64,@Name_1=Shanghai:String)", StaticRecorder.Messages[2]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=4:Int64,@Name_1=Pal95:String)", StaticRecorder.Messages[3]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=5:Int64,@Name_1=Wow:String)", StaticRecorder.Messages[4]);
}
public void TestUpdateDatasetDirect1()
{
var dc = DbEntry.Provider;
DataSet ds = dc.ExecuteDataset(new SqlStatement("select [Name] from [People] where 1=0"));
DbEntry.UsingConnection(delegate
{
var da = (DbDataAdapter)dc.Driver.GetDbAdapter();
var sql = new SqlStatement("insert into [People] ([Name]) VALUES (@name)");
var c = (DbCommand)dc.GetDbCommand(sql);
c.Parameters.Add(dc.Driver.GetDbParameter(new DataParameter("name", "", "name")));
da.InsertCommand = c;
DataTable dt = ds.Tables[0];
for (int i = 0; i < 10; i++)
{
object[] row = { "jxf" };
dt.Rows.Add(row);
}
da.Update(ds);
ds.AcceptChanges();
});
var list = DbEntry.From<SinglePerson>().Where(Condition.Empty).OrderBy("Id").Select();
Assert.AreEqual(13, list.Count);
Assert.AreEqual("Tom", list[0].Name);
Assert.AreEqual("jxf", list[3].Name);
Assert.AreEqual("jxf", list[12].Name);
}
protected override SqlStatement ToSqlStatement(DbDialect dd, List<string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
string sqlString = string.Format(StatementTemplate, From.ToSqlText(dpc, dd), _valuesOptions.ToSqlText(dpc, dd));
var sql = new SqlStatement(CommandType.Text, sqlString, dpc);
return sql;
}
public DynamicRow ExecuteDynamicRow(SqlStatement sql)
{
var list = ExecuteDynamicList(sql);
if (list.Count >= 1)
{
return list[0];
}
return null;
}
private void button1_Click(object sender, System.EventArgs e)
{
var sql = new SqlStatement(
"SELECT TOP 50 * FROM [SampleData] WHERE [id] > @id AND [Enabled] = @Enabled",
new DataParameter("@id", 5),
new DataParameter("@Enabled", false)
);
DataSet ds = DbEntry.Provider.ExecuteDataset(sql);
dataGrid1.DataSource = ds.Tables[0];
}
public List<DynamicRow> ExecuteDynamicList(SqlStatement sql)
{
var list = new List<DynamicRow>();
ExecuteDataReader(sql, dr =>
{
while (dr.Read())
{
list.Add(GetRow(dr));
}
});
return list;
}
public virtual void DropColumns(ModelContext ctx, params string[] columns)
{
foreach(var column in columns)
{
var sb = new StringBuilder("ALTER TABLE ");
sb.Append(QuoteForTableName(ctx.Info.From.MainTableName));
sb.Append(" DROP COLUMN ");
sb.Append(QuoteForColumnName(column));
sb.Append(";");
var sql = new SqlStatement(sb.ToString());
ctx.Provider.ExecuteNonQuery(sql);
}
}
private void button2_Click(object sender, EventArgs e)
{
var Sql = new SqlStatement("llf_GetOutParam",
new DataParameter("@InParam", 12),
new DataParameter("@OutParam", 0, typeof(int), ParameterDirection.Output),
new DataParameter("@Ret", 0, typeof(int), ParameterDirection.ReturnValue)
);
int i = Convert.ToInt32(DbEntry.Provider.ExecuteScalar(Sql));
string s = string.Format("The Select Value Is: {0}\nThe Out Value Is: {1}\nThe Ret Value Is: {2}",
i, Sql.Parameters[1].Value, Sql.Parameters[2].Value);
MessageBox.Show(s);
}
public List<DbColumnInfo> GetDbColumnInfoList(string tableName)
{
string sqlStr = "SELECT * FROM " + Dialect.QuoteForTableName(tableName) + " WHERE 1<>1";
var sql = new SqlStatement(CommandType.Text, sqlStr);
var ret = new List<DbColumnInfo>();
ExecuteDataReader(sql, CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly, delegate(IDataReader dr)
{
DataTable dt = dr.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
ret.Add(new DbColumnInfo(row));
}
});
return ret;
}
public List<List<DynamicRow>> ExecuteDynamicSet(SqlStatement sql)
{
var set = new List<List<DynamicRow>>();
ExecuteDataReader(sql, dr =>
{
do
{
var list = new List<DynamicRow>();
while (dr.Read())
{
list.Add(GetRow(dr));
}
set.Add(list);
} while (dr.NextResult());
});
return set;
}
public void Test1()
{
SqlRecorder.Start();
var de = new DataProvider("SQLite");
var sql = new SqlStatement("test log") {NeedLog = false};
de.ExecuteNonQuery(sql);
Assert.AreEqual(0, SqlRecorder.List.Count);
sql.NeedLog = true;
de.ExecuteNonQuery(sql);
Assert.AreEqual(1, SqlRecorder.List.Count);
Assert.AreEqual("test log<Text><30>()", SqlRecorder.LastMessage);
SqlRecorder.Stop();
}
public void TestSqlServeerBulkCopy()
{
DbContext dc = new DbContext("SqlServer");
SqlStatement sql = new SqlStatement("select [Id],[Name] from [Books] order by [Id]");
List<long> rcs = new List<long>();
DbEntry.Context.ExecuteDataReader(sql, delegate(IDataReader dr)
{
dc.NewConnection(delegate()
{
IDbBulkCopy c = dc.GetDbBulkCopy();
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.SqlRowsCopied += new SqlRowsCopiedEventHandler(delegate(object sender, SqlRowsCopiedEventArgs e)
{
rcs.Add(e.RowsCopied);
});
c.WriteToServer(dr);
});
});
Assert.AreEqual(1, rcs.Count);
Assert.AreEqual(3, rcs[0]);
}
public DynamicTable ExecuteDynamicTable(SqlStatement sql)
{
var table = new DynamicTable();
ExecuteDataReader(sql, dr =>
{
while (dr.Read())
{
if (table.NeedInit)
{
for (int i = 0; i < dr.FieldCount; i++)
{
table.AddKey(dr.GetName(i), i);
}
table.NeedInit = false;
}
var row = table.NewRow();
var cols = new object[dr.FieldCount];
dr.GetValues(cols);
row.AppendMemberRange(cols);
}
});
return table;
}
public void TestUpdateDataset1()
{
var dc = DbEntry.Provider;
DataSet ds = dc.ExecuteDataset(new SqlStatement("select [Name] from [People] where 1=0"));
var sql = new SqlStatement("insert into [People] ([Name]) VALUES (@name)");
sql.Parameters.Add(new DataParameter("@name", "", "name"));
DataTable dt = ds.Tables[0];
for (int i = 0; i < 10; i++)
{
object[] row = { "jxf" };
dt.Rows.Add(row);
}
dc.UpdateDataset(sql, null, null, ds);
var list = DbEntry.From<SinglePerson>().Where(Condition.Empty).OrderBy("Id").Select();
Assert.AreEqual(13, list.Count);
Assert.AreEqual("Tom", list[0].Name);
Assert.AreEqual("jxf", list[3].Name);
Assert.AreEqual("jxf", list[12].Name);
}
private void ExecuteDataset(SqlStatement sql, DataSet ds)
{
DbEntry.UsingConnection(delegate
{
using (IDbCommand e = GetDbCommand(sql))
{
IDbDataAdapter d = InnerDriver.GetDbAdapter(e);
if (Dialect.ExecuteEachLine)
{
int i = 0;
foreach (string s in Split(e.CommandText))
{
e.CommandText = s;
((DbDataAdapter)d).Fill(ds, 0, DataSettings.MaxRecords, "Table" + i);
i++;
}
}
else
{
d.Fill(ds);
}
PopulateOutParams(sql, e);
}
});
}
public DataSet ExecuteDataset(SqlStatement sql)
{
var ds = new DataSet("Set");
ExecuteDataset(sql, ds);
return ds;
}
public DataSet ExecuteDataset(SqlStatement sql, Type returnType)
{
var ds = (DataSet)ClassHelper.CreateInstance(returnType);
ExecuteDataset(sql, ds);
return ds;
}
public int UpdateDataset(SqlStatement insertSql, SqlStatement updateSql, SqlStatement deleteSql, DataSet ds)
{
return(UpdateDataset(insertSql, updateSql, deleteSql, ds, 1, UpdateRowSource.Both, false));
}
public SqlStatement GetSqlStatement(string sqlStr, params object[] os)
{
CommandType ct = SqlStatement.GetCommandType(sqlStr);
if (ct == CommandType.StoredProcedure)
{
return new SqlStatement(ct, sqlStr, os);
}
var dpc = new DataParameterCollection();
int start = 0, n = 0;
var sql = new StringBuilder();
foreach (Match m in Reg.Matches(sqlStr))
{
if (m.Length == 1)
{
string pn = Dialect.QuoteParameter("p" + n);
sql.Append(sqlStr.Substring(start, m.Index - start));
sql.Append(pn);
start = m.Index + 1;
var dp = new DataParameter(pn, os[n]);
dpc.Add(dp);
n++;
}
}
if (start < sqlStr.Length)
{
sql.Append(sqlStr.Substring(start));
}
var ret = new SqlStatement(ct, sql.ToString(), dpc);
return ret;
}
protected void PopulateOutParams(SqlStatement sql, IDbCommand e)
{
if (sql.Parameters.UserSetKey && (sql.SqlCommandType == CommandType.StoredProcedure))
{
for (int i = 0; i < sql.Parameters.Count; i++)
{
DataParameter p = sql.Parameters[i];
if (p.Direction != ParameterDirection.Input)
{
p.Value = ((IDbDataParameter)e.Parameters[i]).Value;
}
}
}
}
public int UpdateDataset(SqlStatement selectSql, DataSet ds)
{
return(UpdateDataset(selectSql, ds, 1, UpdateRowSource.Both));
}
public void ExecuteDataReader(SqlStatement sql, Action<IDataReader> callback)
{
ExecuteDataReader(sql, CommandBehavior.Default, callback);
}
private void button4_Click(object sender, System.EventArgs e)
{
var dataBase1 = new DataProvider("1");
var sql = new SqlStatement("SELECT TOP 50 * FROM [Sheet1$]");
dataGrid1.DataSource = dataBase1.ExecuteDataset(sql).Tables[0];
}
public int ExecuteNonQuery(SqlStatement sql)
{
int i = 0;
DbEntry.UsingConnection (delegate {
using (IDbCommand e = GetDbCommand (sql)) {
ProcessLines (e, e1 => i += e1.ExecuteNonQuery ());
PopulateOutParams (sql, e);
}
});
return i;
}
public object ExecuteScalar(SqlStatement sql)
{
object obj = null;
DbEntry.UsingConnection(delegate
{
using (IDbCommand e = GetDbCommand(sql))
{
if (Dialect.ExecuteEachLine)
{
ExecuteBeforeLines(e);
}
obj = e.ExecuteScalar();
PopulateOutParams(sql, e);
}
});
return obj;
}
private IDbCommand GetDbCommandForUpdate(SqlStatement sql, UpdateRowSource updateRowSource)
{
var c = GetDbCommand(sql);
c.UpdatedRowSource = updateRowSource;
return c;
}
public int UpdateDataset(SqlStatement insertSql, SqlStatement updateSql, SqlStatement deleteSql, DataSet ds, int updateBatchSize)
{
var updateRowSource = updateBatchSize != 1 ? UpdateRowSource.None : UpdateRowSource.Both;
return(UpdateDataset(insertSql, updateSql, deleteSql, ds, updateBatchSize, updateRowSource, true));
}
public IDbCommand GetDbCommand(SqlStatement sql)
{
if(sql.NeedLog)
{
Logger.SQL.Trace(sql);
}
return GetConnectionContext().GetDbCommand(sql, this);
}
public void ExecuteDataReader(SqlStatement sql, CommandBehavior behavior, Action<IDataReader> callback)
{
DbEntry.UsingConnection (delegate {
using (IDbCommand e = GetDbCommand (sql)) {
ProcessLines (e, e1 => {
using (IDataReader r = e1.ExecuteReader (behavior)) {
PopulateOutParams (sql, e1);
callback (r);
}
});
}
});
}
public int UpdateDataset(SqlStatement selectSql, DataSet ds, int updateBatchSize)
{
var updateRowSource = updateBatchSize != 1 ? UpdateRowSource.None : UpdateRowSource.Both;
return(UpdateDataset(selectSql, ds, updateBatchSize, updateRowSource));
}
// for oracle
internal void ExecuteDataReader(SqlStatement sql, Type returnType, Action<IDataReader> callback)
{
DbEntry.UsingConnection (delegate {
using (IDbCommand e = GetDbCommand (sql)) {
ProcessLines (e, e1 => {
using (IDataReader r = e1.ExecuteReader (CommandBehavior.Default)) {
PopulateOutParams (sql, e1);
using (IDataReader dr = Dialect.GetDataReader (r, returnType)) {
callback (dr);
}
}
});
}
});
}
public void ExecuteDataReader(SqlStatement sql, Action <IDataReader> callback)
{
ExecuteDataReader(sql, CommandBehavior.Default, callback);
}
