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()); } }