/// <summary> /// Return CustomerDataTable using a WHERE CustomerIdentifier IN clause e.g. WHERE CustomerIdentifier IN (1,2,4) /// </summary> /// <param name="dataTable"></param> /// <param name="Values"></param> /// <returns></returns> /// <remarks> /// In this case I created a new SELECT in the xsd for the Customer table with a WHERE condition that /// has a parameter named @InValues which is unique, meaning no other queries in the xsd have this name. /// The code below then finds that query, removes the WHERE condition in place of one with a IN clause and /// uses the int array (converted to a string array) for returning records. /// </remarks> public int FillByInByCustomerIdentifier(DataSet1.CustomersDataTable dataTable, int[] Values) { for (int i = 0; i < CommandCollection.Length; i++) { if (CommandCollection[i].Parameters.Count > 0) { foreach (SqlParameter item in CommandCollection[i].Parameters) { if (item.ParameterName == "@InValues") { Adapter.SelectCommand = CommandCollection[i].Clone(); int whereLocation = Adapter.SelectCommand.CommandText.IndexOf("WHERE", System.StringComparison.Ordinal); var selectTextNoWhereCondition = Adapter.SelectCommand.CommandText.Substring(0, whereLocation); var newSelectStatmentText = selectTextNoWhereCondition + " WHERE CustomerIdentifier IN (" + string.Join(",", Values) + ")"; Adapter.SelectCommand = new SqlCommand() { Connection = this.Connection }; Adapter.SelectCommand.CommandText = newSelectStatmentText; if ((ClearBeforeFill == true)) { dataTable.Clear(); } int returnValue = this.Adapter.Fill(dataTable); return(returnValue); } } } } throw new System.Exception("Failed to locate SELECT"); }
public static IList<Customer> List() { DataSet1.CustomersDataTable cdt = new DataSet1.CustomersDataTable(); cdt.ReadXml(AppDomain.CurrentDomain.BaseDirectory + "customers.xml"); List<Customer> items = new List<Customer>(); foreach (DataSet1.CustomersRow crow in cdt.Rows) { Customer cust = new Customer(); cust.Address = crow.Address; cust.City = crow.City; cust.CompanyName = crow.CompanyName; cust.ContactName = crow.ContactName; cust.ContactTitle = crow.ContactTitle; cust.Country = crow.Country; cust.CustomerID = crow.CustomerID; cust.Fax = crow.Fax; cust.Phone = crow.Phone; cust.PostalCode = crow.PostalCode; items.Add(cust); } return items; }
public static IList <Customer> List() { DataSet1.CustomersDataTable cdt = new DataSet1.CustomersDataTable(); cdt.ReadXml(AppDomain.CurrentDomain.BaseDirectory + "customers.xml"); List <Customer> items = new List <Customer>(); foreach (DataSet1.CustomersRow crow in cdt.Rows) { Customer cust = new Customer(); cust.Address = crow.Address; cust.City = crow.City; cust.CompanyName = crow.CompanyName; cust.ContactName = crow.ContactName; cust.ContactTitle = crow.ContactTitle; cust.Country = crow.Country; cust.CustomerID = crow.CustomerID; cust.Fax = crow.Fax; cust.Phone = crow.Phone; cust.PostalCode = crow.PostalCode; items.Add(cust); } return(items); }