protected void Page_Load(object sender, EventArgs e)
{
//string m_connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SQLDataProvider prov = new SQLDataProvider("Some Connection String");
StationaryLogger.Info("Application is working");
}
public void Delete(IList <int> docIds)
{
StringBuilder sql = new StringBuilder();
sql.Append("delete ");
sql.AppendFormat(" {0} where docId in (", Table.DBTableName);
int i = 0;
foreach (int docId in docIds)
{
if (i++ == 0)
{
sql.AppendFormat("{0}", docId);
}
else
{
sql.AppendFormat(",{0}", docId);
}
}
sql.Append(")");
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
}
}
public void Insert(IList <Hubble.Core.Data.Document> docs)
{
StringBuilder insertString = new StringBuilder();
foreach (Hubble.Core.Data.Document doc in docs)
{
insertString.AppendFormat("Insert {0} ([DocId]", _Table.DBTableName);
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
insertString.AppendFormat(", [{0}]", fv.FieldName);
}
insertString.AppendFormat(") Values({0}", doc.DocId);
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
switch (fv.Type)
{
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.NChar:
case Hubble.Core.Data.DataType.DateTime:
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.Data:
insertString.AppendFormat(",'{0}'", fv.Value.Replace("'", "''"));
break;
case DataType.Float:
insertString.AppendFormat(",{0}", fv.Value);
//insertString.AppendFormat(",{0:E}", double.Parse(fv.Value)); //We can change it like this in furture.
break;
default:
insertString.AppendFormat(",{0}", fv.Value);
break;
}
}
insertString.Append(")\r\n");
}
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(insertString.ToString());
}
}
public void QuerySQL_LITE_InsertPerson()
{
string Message;
PersonModel NewPerson = new PersonModel();
NewPerson.PersonFirstName = "Sean";
NewPerson.PersonLastName = "Gilmore";
NewPerson.CurrentAge = 5000;
SQLDataProvider provider = new SQLDataProvider();
provider.CreateConnection();
string commandString = "insert into Person(FirstName,LastName,Age) values(@FirstName,@LastName,@Age)";
provider.CreateCommand(commandString, System.Data.CommandType.Text, true);
provider.CreateQueryParameter("@FirstName", System.Data.DbType.String, NewPerson.PersonFirstName);
provider.CreateQueryParameter("@LastName", System.Data.DbType.String, NewPerson.PersonLastName);
provider.CreateQueryParameter("@Age", System.Data.DbType.Int32, NewPerson.CurrentAge);
provider.CreateTransaction(true, System.Data.IsolationLevel.ReadCommitted);
if (provider.ExecuteNonQuery() > 0)
{
Message = "New Person Added Succesfully";
}
}
public void InstantiateProviderWithCorrectSettings()
{
// arrange
SQLDataProvider provider = new SQLDataProvider();
Assert.IsFalse(provider.ConfigurationsSet, "Failed to Initialize DataProvider");
}
public void MirrorUpdate(IList <FieldValue> fieldValues, IList <List <FieldValue> > docValues,
IList <Query.DocumentResultForSort> docs)
{
StringBuilder sql = new StringBuilder();
for (int index = 0; index < docs.Count; index++)
{
sql.AppendFormat("update {0} set ", Table.DBTableName);
for (int i = 0; i < fieldValues.Count; i++)
{
Data.FieldValue fvFieldName = fieldValues[i];
Data.FieldValue fv = docValues[index][i];
string value;
if (fv.Value == null)
{
value = "NULL";
}
else
{
value = fv.Type == Data.DataType.Varchar || fv.Type == Data.DataType.NVarchar ||
fv.Type == Data.DataType.NChar || fv.Type == Data.DataType.Char ||
fv.Type == Data.DataType.Date || fv.Type == Data.DataType.DateTime ||
fv.Type == Data.DataType.SmallDateTime || fv.Type == Data.DataType.Data ?
string.Format("'{0}'", fv.Value.Replace("'", "''")) : fv.Value;
}
if (i == 0)
{
sql.AppendFormat("[{0}]={1}", fvFieldName.FieldName, value);
}
else
{
sql.AppendFormat(",[{0}]={1}", fvFieldName.FieldName, value);
}
}
int docid = docs[index].DocId;
if (DocIdReplaceField == null)
{
sql.AppendFormat(" where docId = {0}; \r\n", docid);
}
else
{
long replaceFieldValue = this.DBProvider.GetDocIdReplaceFieldValue(docid);
sql.AppendFormat(" where {0} = {1}; \r\n", DocIdReplaceField, replaceFieldValue);
}
}
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
}
}
public void MirrorDelete(IList <int> docIds)
{
StringBuilder sql = new StringBuilder();
sql.Append("delete ");
if (Table.DocIdReplaceField != null)
{
sql.AppendFormat(" {0} where {1} in (", Table.DBTableName, Table.DocIdReplaceField);
}
else
{
sql.AppendFormat(" {0} where docId in (", Table.DBTableName);
}
int i = 0;
foreach (int docId in docIds)
{
long id;
if (Table.DocIdReplaceField != null)
{
id = this.DBProvider.GetDocIdReplaceFieldValue(docId);
if (id == long.MaxValue)
{
//does not find this record
continue;
}
}
else
{
id = docId;
}
if (i++ == 0)
{
sql.AppendFormat("{0}", id);
}
else
{
sql.AppendFormat(",{0}", id);
}
}
if (i == 0)
{
//No records need to delete
return;
}
sql.Append(")");
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
}
}
public DataSet GetAgeGroup()
{
DataSet response = new DataSet();
string sqlText = "SELECT Id, MinAge, MaxAge, Description FROM AgeGroup";
response = SQLDataProvider.ExecuteStoresProcedure(sqlText);
return(response);
}
public void DataProviderConfigurationSettings()
{
// arrange
SQLDataProvider provider = new SQLDataProvider();
bool ExpectedResult = true;
// act
bool ActualResult = provider.ConfigurationsSet;
// assert
Assert.AreEqual(ExpectedResult, ActualResult, "Configurations Have not been Correctly Set");
}
private void barbtn_capnhatsoxe_ItemClick(object sender, ItemClickEventArgs e)
{
SQLDataProvider connect = new SQLDataProvider();
SqlCommand cmd = new SqlCommand("sp_ThayDoiSoXeSuaChuaToiDa", connect.GetConnection());
int SoLuotSua = int.Parse(barEdit_soxe.EditValue.ToString());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@SoXeSuaChuaToiDa", SoLuotSua));
cmd.ExecuteNonQuery();
SQLDataProvider.connection.Close();
MessageBox.Show("Cập nhật số xe sửa chửa tối đa trong ngày thành công!!");
}
public System.Data.DataSet GetSchema(string tableName)
{
string sql = string.Format("select * from {0}", tableName);
using (SQLDataProvider sqlData = new SQLDataProvider())
{
string connectionString = this.ConnectionString;
sqlData.Connect(connectionString);
return(sqlData.GetSchema(sql));
}
}
public void Truncate(string tableName)
{
Debug.Assert(Table != null);
string sql = string.Format("truncate table {0}",
tableName);
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql);
}
}
public void Drop()
{
Debug.Assert(Table != null);
string sql = string.Format("if exists (select * from sysobjects where id = object_id('{0}') and type = 'u') drop table {0}",
Table.DBTableName);
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql);
}
}
private void RegisterIoC()
{
var container = TinyIoCContainer.Current;
container.Register <IPreferences, CustomPreferences>();
var databasePath = DependencyService.Get <IFileAccess>().GetLocalFilePath(GetDatabaseFilename());
var dataProvider = new SQLDataProvider(databasePath);
container.Register <IDataProvider, SQLDataProvider>(dataProvider);
container.Register <ICloudService, FirebaseCloudService>();
container.Register <INewsService, NewsService>(new NewsService(new FirebaseCloudService(), dataProvider));
container.Register <IFirebaseStorage, Storage>().AsSingleton();
container.Register <IScheduleUpdater, ScheduleUpdater>();
}
public PersonDTO AddUpdatePersonInformation(PersonDTO personDTO)
{
string sqlText = "INSERT INTO Person(FirstName,LastName,Age)";
sqlText += string.Format(" VALUES('{0}','{1}',{2})", personDTO.FirstName, personDTO.LastName, personDTO.Age);
if (personDTO.Id > 0)
{
sqlText = string.Format("Update Person SET FirstName='{0}', LastName='{1}', Age={2} WHERE Id={3}", personDTO.FirstName, personDTO.LastName, personDTO.Age, personDTO.Id);
}
if (SQLDataProvider.ExecuteNonQuery(sqlText))
{
personDTO.Id = Convert.ToInt32(SQLDataProvider.ExecuteScalar(sqlTextId));
}
return(personDTO);
}
public void QuerySQL_LITE_DeletePerson()
{
string Message;
SQLDataProvider provider = new SQLDataProvider();
provider.CreateConnection();
string commandString = "delete from Person where Id = @ID";
provider.CreateCommand(commandString, System.Data.CommandType.Text, true);
provider.CreateQueryParameter("@ID", System.Data.DbType.Int64, 2);
provider.CreateTransaction(true, System.Data.IsolationLevel.ReadCommitted);
if (provider.ExecuteNonQuery() > 0)
{
Message = "Person Deleted";
}
}
public void ConnectionTest()
{
using (SQLDataProvider sqlData = new SQLDataProvider())
{
string connectionString;
if (Table == null)
{
connectionString = this.ConnectionString;
}
else
{
connectionString = Table.ConnectionString;
}
sqlData.Connect(connectionString);
}
}
public System.Data.DataSet QuerySql(string sql)
{
using (SQLDataProvider sqlData = new SQLDataProvider())
{
string connectionString;
if (Table == null)
{
connectionString = this.ConnectionString;
}
else
{
connectionString = Table.ConnectionString;
}
sqlData.Connect(connectionString);
return(sqlData.QuerySql(sql));
}
}
public int ExcuteSql(string sql)
{
using (SQLDataProvider sqlData = new SQLDataProvider())
{
string connectionString;
if (Table == null)
{
connectionString = this.ConnectionString;
}
else
{
connectionString = Table.ConnectionString;
}
sqlData.Connect(connectionString);
return(sqlData.ExcuteSql(sql));
}
}
public DataSet GetPersonInformation(PersonDTO personDTO = null)
{
DataSet response = new DataSet();
string sqlText = "SELECT a.Id, a.FirstName, a.LastName, a.Age, b.Description AS AgeGroup";
sqlText += " FROM Person a";
sqlText += " INNER JOIN AgeGroup b";
sqlText += " ON (a.Age>=b.MinAge OR b.MinAge IS NULL)";
sqlText += " AND (a.Age<b.MaxAge OR b.MaxAge IS NULL)";
//string sqlText = "SELECT Id, FirstName, LastName, Age, AgeGroup FROM PersonAgeGroupView";
if (personDTO != null)
{
sqlText += string.Format(" WHERE FirstName='{0}' AND LastName='{1}'", personDTO.FirstName, personDTO.LastName);
}
response = SQLDataProvider.ExecuteStoresProcedure(sqlText);
return(response);
}
public void QuerySQL_LITE_GetPerson()
{
// arrange
SQLDataProvider provider = new SQLDataProvider();
provider.CreateConnection();
string commandString = @"SELECT FirstName,LastName,Age,
CASE WHEN Age <= 2 THEN 'Toddler'
WHEN Age > 4999 THEN 'Kauri Tree'
ELSE(Select Description from AgeGroup where ps.Age >= MinAge and ps.Age <= MaxAge)
END[Description]
,Id
FROM Person ps
where ps.FirstName like @FirstName";
provider.CreateCommand(commandString, System.Data.CommandType.Text, true);
provider.CreateQueryParameter("@FirstName", System.Data.DbType.String, "Corbin%");
provider.CreateTransaction(true, System.Data.IsolationLevel.ReadCommitted);
string Message = string.Empty;
List <PersonModel> People = provider.ExecuteDataReader(ref Message);
}
public AgeRange()
{
DataAccess = SQLDataProvider.SQLProvider;
}
public JsonResult dbconnect(string Subscribtions_p, string Server, string User_Name_p, string Ip_test_P)
{
string XML_Path = ConfigurationManager.AppSettings.Get("XMLPath");
XDocument doc = XDocument.Load(XML_Path);
List <string> allDb = new List <string>();
List <string> allUser = new List <string>();
List <string> allPassword = new List <string>();
var Server_Name = Server;
var Subscribtions = Subscribtions_p;
var User_Name = User_Name_p;
var Ip_test = Ip_test_P;
var Config = doc.Descendants("MasterDB");
foreach (var e in Config)
{
allDb.Add(e.Value);
}
String DB_string = (string)allDb[1];
var U_list = doc.Descendants("Environment").Where(s => (string)s.Element("ServerName") == Server_Name).Select(s => s.Element("MasterDBUserName"));
foreach (var e in U_list)
{
allUser.Add(e.Value);
}
String DB_User = (string)allUser[0];
var P_list = doc.Descendants("Environment").Where(s => (string)s.Element("ServerName") == Server_Name).Select(s => s.Element("MasterDBPassword"));
foreach (var e in P_list)
{
allPassword.Add(e.Value);
}
String DB_Pass = (string)allPassword[0];
string sql_add = null;
string sql_show = null;
//test string Ip_test2 = "50.202.178.92";
string connetionString = "Data Source=" + Server_Name.ToString() + ";Initial Catalog=" + DB_string + ";Integrated Security=False;User Id=" + DB_User + ";Password="******";MultipleActiveResultSets=True";
sql_show = @"SELECT * FROM sys.firewall_rules WHERE start_ip_address='" + Ip_test + "'";
sql_add = @"EXECUTE sp_set_firewall_rule @name = N'" + User_Name.ToString() + "', @start_ip_address = '" + Ip_test + "', @end_ip_address = '" + Ip_test + "'";
SqlCommand cmd1 = new SqlCommand();
cmd1.CommandText = sql_show;
SQLDataProvider objSQLDP = new SQLDataProvider();
try
{
var getValue = objSQLDP.ExecuteQueryWithCustomRetry(cmd1, connetionString, Constants.Scalar);
if (getValue != null)
{
String show_result = getValue.ToString();
string result = "Firewall rule already exists for current IP ! check connection!";
return(new JsonResult()
{
Data = result
});
}
else
{
SqlCommand cmd2 = new SqlCommand();
cmd2.CommandText = sql_add;
SQLDataProvider objSQLDP2 = new SQLDataProvider();
var result = objSQLDP2.ExecuteQueryWithCustomRetry(cmd2, connetionString, Constants.NonQuery);
string result1 = "Firewall rule updated ! check connection in 5 mins";
return(new JsonResult()
{
Data = result1
});
}
}
catch (Exception ex)
{
string result = "Error in SQL Query - Retry in 5 Mins! or Contact RM Team for immediate assitance! \n" + ex;
return(new JsonResult()
{
Data = result
});
}
}
public Core.SFQL.Parse.DocumentResultWhereDictionary GetDocumentResults(int end, string where, string orderby)
{
string sql;
if (end >= 0)
{
sql = string.Format("select top {0} ", end + 1);
}
else
{
sql = "select ";
}
if (string.IsNullOrEmpty(where))
{
if (DocIdReplaceField == null)
{
sql += string.Format(" docid from [{0}] ", Table.DBTableName);
}
else
{
sql += string.Format(" [{0}] from [{1}] ", DocIdReplaceField, Table.DBTableName);
}
}
else
{
if (DocIdReplaceField == null)
{
sql += string.Format(" docid from [{0}] where {1}", Table.DBTableName, where);
}
else
{
sql += string.Format(" [{0}] from [{1}] where {2}", DocIdReplaceField, Table.DBTableName, where);
}
}
if (!string.IsNullOrEmpty(orderby))
{
sql += " order by " + orderby;
}
Core.SFQL.Parse.DocumentResultWhereDictionary result = new Core.SFQL.Parse.DocumentResultWhereDictionary();
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
foreach (System.Data.DataRow row in sqlData.QuerySql(sql).Tables[0].Rows)
{
int docId;
if (DocIdReplaceField == null)
{
docId = int.Parse(row[0].ToString());
}
else
{
docId = DBProvider.GetDocIdFromDocIdReplaceFieldValue(long.Parse(row[DocIdReplaceField].ToString()));
if (docId < 0)
{
continue;
}
}
result.Add(docId, new Hubble.Core.Query.DocumentResult(docId));
}
System.Data.DataSet ds;
if (string.IsNullOrEmpty(where))
{
ds = sqlData.QuerySql(string.Format("select count(*) cnt from {0}",
Table.DBTableName));
}
else
{
ds = sqlData.QuerySql(string.Format("select count(*) cnt from {0} where {1}",
Table.DBTableName, where));
}
result.RelTotalCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
return(result);
}
public System.Data.DataTable Query(IList <Hubble.Core.Data.Field> selectFields, IList <Query.DocumentResultForSort> docs, int begin, int end)
{
StringBuilder sql = new StringBuilder();
sql.Append("select ");
int i = 0;
foreach (Hubble.Core.Data.Field field in selectFields)
{
if (DocIdReplaceField != null)
{
if (field.Name.Equals("DocId", StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
}
if (i++ == 0)
{
sql.AppendFormat("[{0}]", field.Name);
}
else
{
sql.AppendFormat(",[{0}]", field.Name);
}
}
if (DocIdReplaceField != null)
{
sql.AppendFormat(",[{0}]", DocIdReplaceField);
}
if (DocIdReplaceField == null)
{
sql.AppendFormat(" from {0} where docId in (", Table.DBTableName);
}
else
{
sql.AppendFormat(" from {0} where {1} in (", Table.DBTableName, DocIdReplaceField);
}
i = 0;
Dictionary <long, int> replaceFieldValueToDocId = null;
if (DocIdReplaceField != null)
{
replaceFieldValueToDocId = new Dictionary <long, int>();
}
for (int j = begin; j <= end; j++)
{
if (j >= docs.Count)
{
break;
}
int docId = docs[j].DocId;
if (DocIdReplaceField == null)
{
if (i++ == 0)
{
sql.AppendFormat("{0}", docId);
}
else
{
sql.AppendFormat(",{0}", docId);
}
}
else
{
long replaceFieldValue = this.DBProvider.GetDocIdReplaceFieldValue(docId);
replaceFieldValueToDocId.Add(replaceFieldValue, docId);
if (i++ == 0)
{
sql.AppendFormat("{0}", replaceFieldValue);
}
else
{
sql.AppendFormat(",{0}", replaceFieldValue);
}
}
}
sql.Append(")");
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
System.Data.DataTable table = sqlData.QuerySql(sql.ToString()).Tables[0];
if (DocIdReplaceField != null)
{
table.Columns.Add(new System.Data.DataColumn("DocId", typeof(int)));
for (i = 0; i < table.Rows.Count; i++)
{
table.Rows[i]["DocId"] =
replaceFieldValueToDocId[long.Parse(table.Rows[i][DocIdReplaceField].ToString())];
}
}
return(table);
}
}
public void Update(Data.Document doc, IList <Query.DocumentResultForSort> docs)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("update {0} set ", Table.DBTableName);
int i = 0;
foreach (Data.FieldValue fv in doc.FieldValues)
{
string value;
if (fv.Value == null)
{
value = "NULL";
}
else
{
value = fv.Type == Data.DataType.Varchar || fv.Type == Data.DataType.NVarchar ||
fv.Type == Data.DataType.NChar || fv.Type == Data.DataType.Char ||
fv.Type == Data.DataType.Date || fv.Type == Data.DataType.DateTime ||
fv.Type == Data.DataType.SmallDateTime || fv.Type == Data.DataType.Data ?
string.Format("'{0}'", fv.Value.Replace("'", "''")) : fv.Value;
}
if (i++ == 0)
{
sql.AppendFormat("[{0}]={1}", fv.FieldName, value);
}
else
{
sql.AppendFormat(",[{0}]={1}", fv.FieldName, value);
}
}
if (DocIdReplaceField == null)
{
sql.Append(" where docId in (");
}
else
{
sql.AppendFormat(" where {0} in (", DocIdReplaceField);
}
i = 0;
foreach (Query.DocumentResultForSort docResult in docs)
{
int docId = docResult.DocId;
if (DocIdReplaceField == null)
{
if (i++ == 0)
{
sql.AppendFormat("{0}", docId);
}
else
{
sql.AppendFormat(",{0}", docId);
}
}
else
{
long replaceFieldValue = this.DBProvider.GetDocIdReplaceFieldValue(docId);
if (i++ == 0)
{
sql.AppendFormat("{0}", replaceFieldValue);
}
else
{
sql.AppendFormat(",{0}", replaceFieldValue);
}
}
}
sql.Append(")");
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
}
}
public void MirrorInsert(IList <Hubble.Core.Data.Document> docs)
{
StringBuilder insertString = new StringBuilder();
foreach (Hubble.Core.Data.Document doc in docs)
{
if (Table.DocIdReplaceField == null)
{
insertString.AppendFormat("Insert {0} ([DocId]", _Table.DBTableName);
}
else
{
insertString.AppendFormat("Insert {0} (", _Table.DBTableName);
}
int i = 0;
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
if (i == 0 && Table.DocIdReplaceField != null)
{
insertString.AppendFormat("[{0}]", fv.FieldName);
}
else
{
insertString.AppendFormat(", [{0}]", fv.FieldName);
}
i++;
}
if (Table.DocIdReplaceField == null)
{
insertString.AppendFormat(") Values({0}", doc.DocId);
}
else
{
insertString.Append(") Values(");
}
i = 0;
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
switch (fv.Type)
{
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.NChar:
case Hubble.Core.Data.DataType.DateTime:
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
case Hubble.Core.Data.DataType.Data:
if (i == 0 && Table.DocIdReplaceField != null)
{
insertString.AppendFormat("'{0}'", fv.Value.Replace("'", "''"));
}
else
{
insertString.AppendFormat(",'{0}'", fv.Value.Replace("'", "''"));
}
break;
case DataType.TinyInt:
{
int temp;
if (fv.Value.Equals("True", StringComparison.CurrentCultureIgnoreCase))
{
temp = 1;
}
else if (fv.Value.Equals("False", StringComparison.CurrentCultureIgnoreCase))
{
temp = 0;
}
else if (!int.TryParse(fv.Value, out temp))
{
temp = (int)double.Parse(fv.Value);
}
if (i == 0 && Table.DocIdReplaceField != null)
{
insertString.AppendFormat("{0}", temp);
}
else
{
insertString.AppendFormat(",{0}", temp);
}
}
break;
default:
if (i == 0 && Table.DocIdReplaceField != null)
{
insertString.AppendFormat("{0}", fv.Value);
}
else
{
insertString.AppendFormat(",{0}", fv.Value);
}
break;
}
i++;
}
insertString.Append(")\r\n");
}
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(insertString.ToString());
}
}
public IList <Core.Query.DocumentResultForSort> GetDocumentResultForSortList(int end, string where, string orderby)
{
string sql;
if (end >= 0)
{
sql = string.Format("select top {0} ", end + 1);
}
else
{
sql = "select ";
}
if (string.IsNullOrEmpty(where))
{
if (DocIdReplaceField == null)
{
sql += string.Format(" docid from {0} ", Table.DBTableName);
}
else
{
sql += string.Format(" {0} from {1} ", DocIdReplaceField, Table.DBTableName);
}
}
else
{
if (DocIdReplaceField == null)
{
sql += string.Format(" docid from {0} where {1}", Table.DBTableName, where);
}
else
{
sql += string.Format(" {0} from {1} where {2}", DocIdReplaceField, Table.DBTableName, where);
}
}
if (!string.IsNullOrEmpty(orderby))
{
sql += " order by " + orderby;
}
List <Core.Query.DocumentResultForSort> result = new List <Core.Query.DocumentResultForSort>();
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
foreach (System.Data.DataRow row in sqlData.QuerySql(sql).Tables[0].Rows)
{
int docId;
if (DocIdReplaceField == null)
{
docId = int.Parse(row[0].ToString());
}
else
{
docId = DBProvider.GetDocIdFromDocIdReplaceFieldValue(long.Parse(row[DocIdReplaceField].ToString()));
if (docId < 0)
{
continue;
}
}
result.Add(new Core.Query.DocumentResultForSort(docId));
}
}
return(result);
}
public void CreateMirrorTable()
{
Debug.Assert(Table != null);
List <string> primaryKeys = new List <string>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("create table {0} (", Table.DBTableName);
if (Table.DocIdReplaceField != null)
{
primaryKeys.Add(Table.DocIdReplaceField);
}
else
{
primaryKeys.Add("DocId");
sql.Append("DocId Int NOT NULL,");
}
int i = 0;
for (i = 0; i < Table.Fields.Count; i++)
{
Data.Field field = Table.Fields[i];
string fieldSql = GetFieldLine(field);
if (fieldSql != null)
{
sql.Append(fieldSql);
if (i < Table.Fields.Count - 1)
{
sql.Append(",");
}
}
}
if (primaryKeys.Count > 0)
{
i = 0;
sql.Append(" primary key (");
foreach (string pkName in primaryKeys)
{
if (i == 0)
{
sql.Append(pkName);
}
else
{
sql.Append("," + pkName);
}
i++;
}
sql.Append(")");
}
sql.Append(")");
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
if (!string.IsNullOrEmpty(Table.SQLForCreate))
{
sqlData.ExcuteSql(Table.SQLForCreate);
}
}
}
public void Update(Data.Document doc, IList <Query.DocumentResultForSort> docs)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("DECLARE @ptrval binary(16)");
int textFieldCount = 0;
StringBuilder writeTextString = new StringBuilder();
foreach (Data.FieldValue fv in doc.FieldValues)
{
switch (fv.Type)
{
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.NChar:
if (fv.DataLength <= 0)
{
textFieldCount++;
foreach (Query.DocumentResultForSort mydoc in docs)
{
writeTextString.AppendLine(BuildWriteTextLine(Table.DBTableName,
fv.FieldName, fv.Value.Replace("'", "''"), mydoc.DocId, fv.Type));
}
}
break;
}
}
if (textFieldCount < doc.FieldValues.Count)
{
sql.AppendFormat("update {0} set ", Table.DBTableName);
int i = 0;
foreach (Data.FieldValue fv in doc.FieldValues)
{
string value;
if (fv.Value == null)
{
value = "NULL";
}
else
{
value = fv.Type == Data.DataType.Varchar || fv.Type == Data.DataType.NVarchar ||
fv.Type == Data.DataType.NChar || fv.Type == Data.DataType.Char ||
fv.Type == Data.DataType.Date || fv.Type == Data.DataType.DateTime ||
fv.Type == Data.DataType.SmallDateTime || fv.Type == Data.DataType.Data ?
string.Format("'{0}'", fv.Value.Replace("'", "''")) : fv.Value;
}
if (i++ == 0)
{
sql.AppendFormat("[{0}]={1}", fv.FieldName, value);
}
else
{
sql.AppendFormat(",[{0}]={1}", fv.FieldName, value);
}
}
if (DocIdReplaceField == null)
{
sql.Append(" where docId in (");
}
else
{
sql.AppendFormat(" where {0} in (", DocIdReplaceField);
}
i = 0;
foreach (Query.DocumentResultForSort docResult in docs)
{
int docId = docResult.DocId;
if (DocIdReplaceField == null)
{
if (i++ == 0)
{
sql.AppendFormat("{0}", docId);
}
else
{
sql.AppendFormat(",{0}", docId);
}
}
else
{
long replaceFieldValue = this.DBProvider.GetDocIdReplaceFieldValue(docId);
if (i++ == 0)
{
sql.AppendFormat("{0}", replaceFieldValue);
}
else
{
sql.AppendFormat(",{0}", replaceFieldValue);
}
}
}
sql.Append(")");
}
sql.AppendLine(writeTextString.ToString());
using (SQLDataProvider sqlData = new SQLDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(sql.ToString());
}
}
