public string ShowMatInText(string strCondition) { string[] strArr = strCondition.Split(':'); System.Collections.ArrayList mass = new System.Collections.ArrayList(); System.Collections.ArrayList key1 = new System.Collections.ArrayList(); System.Collections.ArrayList key2 = new System.Collections.ArrayList(); System.Collections.ArrayList key3 = new System.Collections.ArrayList(); using (SqlText sql = new SqlText(String.Format("select m.MassPartsIDFlag, m.ExpansionKey1, m.ExpansionKey2, m.ExpansionKey3 from MaterialNumber as m join Customer as c on m.ShiptoParty = c.Code where c.PlantCode = '{1}' and shiptoParty like '{0}%' and m.customerItemNumber = '{2}'", strArr[0], strArr[1], strArr[2]))) { System.Data.Common.DbDataReader readerSender = sql.ExecuteReader(); while (readerSender.Read()) { mass.Add(readerSender["MassPartsIDFlag"].ToString()); key1.Add(readerSender["ExpansionKey1"].ToString()); key2.Add(readerSender["ExpansionKey2"].ToString()); key3.Add(readerSender["ExpansionKey3"].ToString()); } readerSender.Close(); readerSender.Dispose(); } MN[] l = new MN[mass.Count]; for (int i = 0; i <= mass.Count - 1; i++) { l[i] = new MN(); l[i].MassPartsIDFlag = mass[i].ToString(); l[i].ExpansionKey1 = key1[i].ToString(); l[i].ExpansionKey2 = key2[i].ToString(); l[i].ExpansionKey3 = key3[i].ToString(); } return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l)); }
public async Task <ITEM> LoadItemAsync(int?ID_ITEM) { ITEM temp = new ITEM(); var con = _db.Database.GetDbConnection(); con.Open(); using (var command = con.CreateCommand()) { // this does a few things. First it returns the given item id, the item name, and the current rating aggregate across all ratings for it. // additionally it finds the next seqential ID, and the previous seqential ID. If none found then 0. // this is important for the 'next/previous' buttons. string q = @" SELECT top(1) b.ID ,b.TITLE ,isnull(C.RATING, 0) as RATING ,( SELECT isnull(min(a.id), 0) FROM [test].[dbo].[ITEM] a where a.id > b.ID) as [NEXT] ,( SELECT isnull(max(a.id), 0) FROM [test].[dbo].[ITEM] a where a.id < b.ID) as [PREVIOUS] FROM [test].[dbo].[ITEM] b left JOIN ( SELECT y.[ID] ,AVG(CAST(z.RATING AS FLOAT)) as RATING FROM [dbo].[ITEM] y JOIN [dbo].[ITEM_RATING] z ON y.ID = z.ID_ITEM GROUP BY y.ID ) c ON B.ID = c.ID "; if (ID_ITEM != null) { // IF there is a specific ID (say from someone clicking 'next' then load that specific item's info, next, previous, name, aggregate rating, etc. q += " WHERE b.ID = @ID_ITEM "; DbParameter tempParameter = command.CreateParameter(); tempParameter.ParameterName = "@ID_ITEM"; tempParameter.Value = ID_ITEM; command.Parameters.Add(tempParameter); } command.CommandText = q; System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { reader.Read(); temp = new ITEM(); temp.ID = (int)reader["ID"]; temp.TITLE = (string)reader["TITLE"]; temp.RATING = Convert.ToDecimal(reader["RATING"]); temp.NEXT = (int)reader["NEXT"]; temp.PREVIOUS = (int)reader["PREVIOUS"]; } reader.Dispose(); } return(temp); }
public T[] List <T>(string Sql, System.Data.Common.DbTransaction transaction = null) { DbBase.LastSQL = Sql; System.Data.Common.DbCommand cmd = null; System.Data.Common.DbDataReader dataReader = null; T[] result = null; try { if (transaction == null && DbConnection.State != ConnectionState.Open) { DbConnection.Open(); } cmd = DbBase.DbConnection.CreateCommand(); cmd.CommandText = Sql; if (transaction != null) { cmd.Transaction = transaction; } dataReader = cmd.ExecuteReader(); List <string> columns = GetDataReaderColumns(dataReader); result = List <T>(dataReader, columns); } finally { if (dataReader != null) { dataReader.Close(); dataReader.Dispose(); dataReader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (transaction == null) { DbConnection.Close(); } } if (result == null) { return(new T[] { }); } return(result); }
public static string GetLookupValue(string sDisplayField, string sLinkField, string sTable, string sValue, TypeCode TType, bool isMultiline) { string sGetLookupValue = ""; string sqlSelect = "SELECT " + sDisplayField + " FROM " + sTable + " WHERE "; try { QueryCommand cmd = new QueryCommand(sqlSelect); if (isMultiline) { int i = 0; foreach (string s in sValue.Split(',')) { string paramName = "param" + i.ToString(); cmd.CommandSql += sLinkField + "=@" + paramName + " Or "; cmd.AddParameter(paramName, s); i += 1; } if (cmd.CommandSql.Length > 2) { cmd.CommandSql = cmd.CommandSql.Remove(cmd.CommandSql.Length - 3); } } else { cmd.CommandSql += sLinkField + "= @LinkField"; cmd.AddParameter("LinkField", sValue); } System.Data.Common.DbDataReader dbDr = (System.Data.Common.DbDataReader)DataService.GetReader(cmd); if (dbDr.HasRows) { if (isMultiline) { while (dbDr.Read()) { sGetLookupValue += dbDr[0].ToString() + ","; } sGetLookupValue = sGetLookupValue.Trim(",".ToCharArray()); } else if (dbDr.Read()) { sGetLookupValue = dbDr[0].ToString(); } } dbDr.Dispose(); } finally { } return(sGetLookupValue); }
//Get a list of all the ratings for a given ID. public async Task <List <ITEM_RATING> > LoadItemsRatingsAsync(int ID_ITEM) { List <ITEM_RATING> temp = new List <ITEM_RATING>(); var con = _db.Database.GetDbConnection(); if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (var command = con.CreateCommand()) { string q = @"SELECT [ID_ITEM] ,[ID_USER] ,b.NAME ,[RATING] ,[DATE_ENTERED] FROM dbo.ITEM_RATING a WITH(NOLOCK) join [USER] b WITH(NOLOCK) ON a.ID_USER = b.ID WHERE [ID_ITEM] = @ID_ITEM ORDER BY DATE_ENTERED DESC"; command.CommandText = q; DbParameter tempParameter = command.CreateParameter(); tempParameter.ParameterName = "@ID_ITEM"; tempParameter.Value = ID_ITEM; command.Parameters.Add(tempParameter); System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync(); temp = new List <ITEM_RATING>(); if (reader.HasRows) { while (reader.Read()) { var row = new ITEM_RATING { ID_ITEM = (int)reader["ID_ITEM"], ID_USER = (int)reader["ID_USER"], RATING = (int)(reader["RATING"]), DATE_ENTERED = Convert.ToDateTime(reader["DATE_ENTERED"]), USER_NAME = (string)(reader["NAME"]), }; temp.Add(row); } } reader.Dispose(); } return(temp); }
static int Dispose(IntPtr L) { try { ToLua.CheckArgsCount(L, 1); System.Data.Common.DbDataReader obj = (System.Data.Common.DbDataReader)ToLua.CheckObject(L, 1, typeof(System.Data.Common.DbDataReader)); obj.Dispose(); return(0); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
/// <summary> /// Execute a command that returns multiple result sets, and access each in turn /// </summary> public static async Task <GridReader> QueryMultipleAsync(this IDbConnection cnn, CommandDefinition command) { object param = command.Parameters; Identity identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(GridReader), param?.GetType(), null); CacheInfo info = GetCacheInfo(identity, param, command.AddToCache); DbCommand cmd = null; IDataReader reader = null; bool wasClosed = cnn.State == ConnectionState.Closed; try { if (wasClosed) { await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false); } cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader); reader = await cmd.ExecuteReaderAsync(wasClosed?CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false); var result = new GridReader(cmd, reader, identity, command.Parameters as DynamicParameters, command.AddToCache, command.CancellationToken); wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader // with the CloseConnection flag, so the reader will deal with the connection; we // still need something in the "finally" to ensure that broken SQL still results // in the connection closing itself return(result); } catch { if (reader != null) { if (!reader.IsClosed) { try { cmd.Cancel(); } catch { /* don't spoil the existing exception */ } } reader.Dispose(); } cmd?.Dispose(); if (wasClosed) { cnn.Close(); } throw; } }
public string cbPlantCode(string strCondition) { System.Collections.ArrayList receiverList = new System.Collections.ArrayList(); using (SqlText sql = new SqlText(String.Format("select distinct(PlantCode) from Customer where Code like '{0}%' and PlantCode is not null", strCondition))) { System.Data.Common.DbDataReader readerSender = sql.ExecuteReader(); while (readerSender.Read()) { receiverList.Add(readerSender["PlantCode"].ToString()); } readerSender.Close(); readerSender.Dispose(); } MN[] l = new MN[receiverList.Count]; for (int i = 0; i <= receiverList.Count - 1; i++) { l[i] = new MN(); l[i].strItem = receiverList[i].ToString(); } return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l)); }
public string cbMatCode(string strCondition) { string[] strArr = strCondition.Split(':'); System.Collections.ArrayList receiverList = new System.Collections.ArrayList(); using (SqlText sql = new SqlText(String.Format("select m.customerItemNumber from MaterialNumber as m join Customer as c on m.ShiptoParty = c.Code where c.PlantCode = '{1}' and shiptoParty like '{0}%'", strArr[0], strArr[1]))) { System.Data.Common.DbDataReader readerSender = sql.ExecuteReader(); while (readerSender.Read()) { receiverList.Add(readerSender["customerItemNumber"].ToString()); } readerSender.Close(); readerSender.Dispose(); } MN[] l = new MN[receiverList.Count]; for (int i = 0; i <= receiverList.Count - 1; i++) { l[i] = new MN(); l[i].strItem = receiverList[i].ToString(); } return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l)); }
public static List<object> GetColumnFromDataReader(DbDataReader dr, string columnName) { List<object> column = new List<object>(); try { while (dr.Read()) { column.Add(dr[columnName]); } } finally { if (dr != null && !dr.IsClosed) { dr.Close(); // Data Reader is done (read only, forward only) dr.Dispose(); } } return column; }
public String ViewTableTxt(String TableName) { ESData.GetInst.Open_Conn(); String txt = "TabeName:" + TableName + "\n"; System.Data.Common.DbDataReader dr = ESData.GetInst.Reader(String.Format("select * from {0};", TableName)); for (int i = 0; i < dr.FieldCount; i++) { txt += dr.GetName(i) + "\t "; } txt += "\n"; //if (dr.HasRows) while (dr.Read()) { for (int i = 0; i < dr.FieldCount; i++) { try{ txt += dr[i].ToString() + "\t "; } catch { MessageBox.Show(dr[0].ToString()); } } txt += "\n"; } dr.Close(); dr.Dispose(); return(txt); }
/// <summary> /// Closes the current connection and disposes of the reader passed in /// </summary> /// <param name="reader">The reader object to dispose of (if there is one, else pass 'null')</param> public void CleanUp(DbDataReader reader) { if (_connection != null && _connection.State == ConnectionState.Open && _transaction == null) _connection.Close(); if (reader != null) { if (!reader.IsClosed) reader.Close(); reader.Dispose(); } }
/// <summary> /// Disposes the reader. /// </summary> /// <param name="reader"></param> protected void SafeDispose(DbDataReader reader) { if (reader != null) { reader.Dispose(); } }
public static DataTable JoinData(DbDataReader dr1, DbDataReader dr2, KeyValuePair<string, string> relation) { // Create the DataTables DataTable dt1 = new DataTable("Table1"); DataTable dt2 = new DataTable("Table2"); dt1.Load(dr1); dt2.Load(dr2); dr1.Close(); dr1.Dispose(); dr2.Close(); dr2.Dispose(); return JoinData(dt1, dt2, relation); }
/// <summary> /// Converts a DbDataReader object with one row to a dictionary. /// Dict Keys are DbDataReader Columns and Values is the Row. /// Will throw if there is more or less than one result. /// Note that DbDataReader is read only / forward only, so at then end, the reader object is closed and disposed. /// </summary> /// <param name="dr"></param> /// <returns></returns> public static Dictionary<string, object> GetDictFromDataReader(DbDataReader dr) { Dictionary<string, object> dict = new Dictionary<string, object>(); try { if (!dr.HasRows) { throw new Exception("DataReader has no rows. Needs to have one."); } dr.Read(); for (int i = 0; i < dr.FieldCount; i++) { dict.Add(dr.GetName(i), dr[i]); } if (dr.Read()) { throw new Exception("DataReader has more than one row. Needs to have only one."); } } finally { if (dr != null && !dr.IsClosed) { dr.Close(); // DataReader is done (read only, forward only) dr.Dispose(); } } return dict; }
private void button2_Click(object sender, EventArgs e) { try { //////Microsoft.Office.Tools.Excel.Workbook theWorkbook = Microsoft.Office.Tools.Excel.ex .ExcelObj.Workbooks.Open( ////// this.txtXmlPath.Text, 0, true, 5, ////// "", "", true, Microsoft.Office.Tools.Excel.xl.xlWindows, "\t", false, false, ////// 0, true); //////Excel.Sheets sheets = theWorkbook.Worksheets; //////Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); //////int i=1; //////string[] strArray; //////System.Array myvalues; //////do { ////// Excel.Range range = worksheet.get_Range("A" + i.ToString(), "O" + i.ToString()); ////// myvalues = (System.Array)range.Cells.Value; ////// strArray = ConvertToStringArray(myvalues); //////} while (strArray[0].Length > 0); string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.txtXlsPath.Text + @";Extended Properties=""Excel 8.0;HDR=YES;"""; DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; ////using (DbCommand command = connection.CreateCommand()) ////{ //// command.CommandText = "INSERT INTO [Cities$] //// (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")"; connection.Open(); //// command.ExecuteNonQuery(); ////} System.Data.Common.DbCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM [volumes$]"; System.Data.Common.DbDataReader drdr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(drdr); drdr.Dispose(); cmd.Dispose(); XmlDocument docXml = new XmlDocument(); XmlNode nodeRoot = docXml.AppendChild(docXml.CreateNode(XmlNodeType.Element, "root", docXml.NamespaceURI)); XmlNode nodeVolumes = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volumes", docXml.NamespaceURI)); XmlNode nodeVolume; XmlNode nodeColumns = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "columns", docXml.NamespaceURI)); foreach (DataColumn dc in dt.Columns) { XmlNode nodeColumn = nodeColumns.AppendChild(docXml.CreateNode(XmlNodeType.Element, "column", docXml.NamespaceURI)); XmlAttribute attFldName = nodeColumn.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "FldName", docXml.NamespaceURI)); attFldName.InnerText = dc.ColumnName; } foreach (DataRow dr in dt.Rows) { nodeVolume = nodeVolumes.SelectSingleNode("./volume[@volume_code = '" + dr["Volume"].ToString() + "']"); if (nodeVolume == null) { nodeVolume = nodeVolumes.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volume", docXml.NamespaceURI)); XmlAttribute attVolumeCode = nodeVolume.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "volume_code", docXml.NamespaceURI)); attVolumeCode.InnerText = dr["volume"].ToString(); } XmlNode nodeIssue = nodeVolume.AppendChild(docXml.CreateNode(XmlNodeType.Element, "issue", docXml.NamespaceURI)); foreach (DataColumn dc in dt.Columns) { if (string.Compare(dc.ColumnName, "volume", true) != 0) { XmlNode nodeFld; nodeFld = nodeIssue.AppendChild(docXml.CreateNode(XmlNodeType.Element, "Fld", docXml.NamespaceURI)); XmlAttribute attFldName = nodeFld.Attributes.Append((XmlAttribute)(docXml.CreateNode(XmlNodeType.Attribute, "Name", docXml.NamespaceURI))); attFldName.InnerText = dc.ColumnName;//.Replace(" ", "_"); nodeFld.InnerText = dr[dc].ToString(); } } } string strXmlFilePath = this.txtXlsPath.Text.Substring(0, this.txtXlsPath.Text.LastIndexOf(".xls")) + ".xml"; docXml.Save(strXmlFilePath); this.txtXmlPath.Text = strXmlFilePath; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }