public void putSqlDbData(xFerDb db, DataTable dt, xFerTransferType t, xFerQuery q)
{
CommandType ct = CommandType.StoredProcedure;
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (q.QueryType == xFerQueryType.Text)
{
ct = CommandType.Text;
}
if (q.QueryText.ToString() != string.Empty && db.ConnectionString != string.Empty)
{
SqlConnection oSqlConn = new SqlConnection(db.ConnectionString);
SqlCommand com = new SqlCommand();
try
{
com.Connection = oSqlConn;
com.CommandTimeout = db.CommandTimeout;
com.Connection = oSqlConn;
com.CommandType = ct;
com.CommandText = q.QueryText.ToString();
com.Connection.Open();
foreach (DataRow dr in dt.Rows)
{
if (com.Parameters.Count > 0)
{
com.Parameters.Clear();
}
if (q.Paramaters != null)
{
if (q.Paramaters.Count == 0)
{
foreach (DataColumn c in dt.Columns)
{
q.Paramaters.Add(new xFerParamater(c.ColumnName.Trim(), ""));
}
}
foreach (xFerParamater p in q.Paramaters)
{
try
{
com.Parameters.AddWithValue("@" + p.Name, dr[p.Name].ToString());
}
catch
{
_errors.Add("Insert missing column " + p.Name);
}
}
}
com.ExecuteNonQuery();
}
com.Connection.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
finally
{
if (com.Connection.State == ConnectionState.Open)
{
com.Connection.Close();
}
}
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
private void putData(xFerDb db, DataTable dt, xFerTransferType t, xFerQuery xOut, string mongoCollection)
{
switch (db.DbType)
{
case xFerDbType.mongoDb:
putMongoDBData(db, dt, t, mongoCollection);
break;
case xFerDbType.MSSQL:
putSqlDbData(db, dt, t, xOut);
break;
case xFerDbType.MySql:
putMySqlDbData(db, dt, t, xOut);
break;
}
}
private DataTable getData(xFerDb x, xFerQuery q, string mongoCollection)
{
DataTable dt = new DataTable();
switch (x.DbType)
{
case xFerDbType.MSSQL:
dt = sqlGetData(q.QueryText.ToString(), x.ConnectionString, q.Paramaters, q.QueryType, x.CommandTimeout);
break;
case xFerDbType.mongoDb:
dt = getMongoData(x, q, mongoCollection);
break;
case xFerDbType.MySql:
dt = sqlMyGetData(q.QueryText.ToString(), x.ConnectionString, q.Paramaters, q.QueryType, x.CommandTimeout);
break;
}
return dt;
}
public DataTable getMongoData(xFerDb db, xFerQuery q, string mongoCollection)
{
DataTable dt = new DataTable();
if (q.QueryText != null)
{
try
{
MongoServer server = MongoServer.Create(db.ConnectionString);
MongoDatabase database = server.GetDatabase(db.DbName);
MongoCollection<BsonDocument> collection = database.GetCollection<BsonDocument>(mongoCollection);
setQuery(q.QueryText.ToString());
QueryDocument _q = new QueryDocument();
if (q.QueryText.ToString() == string.Empty)
{
q.QueryText = "{}";
}
BsonDocument query = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(_Query);
_q.Add(query);
FieldsDocument _fields = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<FieldsDocument>(_Field);
MongoCursor<BsonDocument> _ret = collection.Find(_q).SetFields(_fields).SetSortOrder(_Sort).SetLimit(_Limit);
bool isSet = false;
foreach (BsonDocument i in _ret)
{
if (!isSet)
{
foreach (BsonElement mei in i.Elements)
{
dt.Columns.Add(mei.Name);
isSet = true;
}
}
DataRow _dr = dt.NewRow();
foreach (BsonElement mei in i.Elements)
{
_dr[mei.Name] = mei.Value;
}
dt.Rows.Add(_dr);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
}
return dt;
}
public void createQuery(List<xFerParamater> _vars)
{
string packageName = _vars[0].Value.ToString().Trim();
string queryName = _vars[1].Value.ToString().Trim();
string queryType = _vars[2].Value.ToString().Trim();
string queryText = _vars[3].Value.ToString().Trim();
List<xFerParamater> _params = new List<xFerParamater>();
if (_vars.Count > 4)
{
string paramtext = _vars[4].Value.ToString().Trim();
try
{
_params = getParametersPassed(paramtext);
}
catch (Exception ex)
{
Console.WriteLine("query parameters failed.");
}
}
//SEARCH QUERY FOR EXISTING QUERIES
bool packageExists = false;
foreach (xFerPackage _x in _xFerPackages)
{
if (_x.PackageName == packageName)
{
packageExists = true;
bool exists = false;
foreach (xFerQuery _q in _x.Queries)
{
if (_q.QueryName == queryName)
{
exists = true;
if (queryType == "SP")
{
_q.QueryType = xFerQueryType.StoredProcedure;
}
else if (queryType == "T")
{
_q.QueryType = xFerQueryType.Text;
}
_q.QueryText = queryText;
// newQuery.MongoQuery = "";
//newQuery.Paramaters
if (_params.Count > 0)
{
_q.Paramaters = _params;
}
savePackage(_x.PackageName);
}
}
if (!exists)
{
xFerQuery newQuery = new xFerQuery();
newQuery.QueryName = queryName;
if (queryType == "SP")
{
newQuery.QueryType = xFerQueryType.StoredProcedure;
}
else if (queryType == "T")
{
newQuery.QueryType = xFerQueryType.Text;
}
newQuery.QueryText = queryText;
// newQuery.MongoQuery = "";
//newQuery.Paramaters
if (_params.Count > 0)
{
newQuery.Paramaters = _params;
}
_x.Queries.Add(newQuery);
savePackage(_x.PackageName);
}
}
}
if (!packageExists)
{
//CREATE PACKAGE
xFerPackage xPack = new xFerPackage();
xPack.PackageName = packageName;
xFerQuery newQuery = new xFerQuery();
newQuery.QueryName = queryName;
if (queryType == "SP")
{
newQuery.QueryType = xFerQueryType.StoredProcedure;
}
else if (queryType == "T")
{
newQuery.QueryType = xFerQueryType.Text;
}
newQuery.QueryText = queryText;
//newQuery.Paramaters
if (_params.Count > 0)
{
newQuery.Paramaters = _params;
}
xPack.Queries.Add(newQuery);
_xFerPackages.Add(xPack);
savePackage(xPack.PackageName);
}
}