Пример #1
0
 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));
 }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
    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);
    }
Пример #5
0
        //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);
        }
Пример #6
0
 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));
     }
 }
Пример #7
0
        /// <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;
            }
        }
Пример #8
0
 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));
 }
Пример #9
0
 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));
 }
Пример #10
0
        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;
        }
Пример #11
0
        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);
        }
Пример #12
0
        /// <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();
     }
 }
Пример #14
0
        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);
        }
Пример #15
0
        /// <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;
        }
Пример #16
0
        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);
            }
        }