예제 #1
0
        /// <summary>
        ///     Queries the database and populates a <see cref="DataTable" /> with the resulting data from the specified SQL
        ///     statement.
        /// </summary>
        /// <param name="source">The process application reference.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns>
        ///     Returns a <see cref="DataTable" /> representing the records returned by the command text.
        /// </returns>
        /// <exception cref="ArgumentNullException">commandText</exception>
        public static DataTable ExecuteQuery(this IMMPxApplication source, string commandText)
        {
            if (source == null)
            {
                return(null);
            }
            if (commandText == null)
            {
                throw new ArgumentNullException("commandText");
            }

            var table = new DataTable();

            table.Locale = CultureInfo.InvariantCulture;

            using (var cr = new ComReleaser())
            {
                Recordset recordset = new RecordsetClass();
                recordset.Open(commandText, source.Connection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, 0);
                cr.ManageLifetime(recordset);

                var adapter = new OleDbDataAdapter();
                adapter.Fill(table, recordset);
                recordset.Close();
            }

            return(table);
        }
예제 #2
0
        //ВНИМАНИЕ!!!: Сделано только для таблицы vars!
        private void FillDataRestruct(ConnectionClass conn, string tableName, jrows[] rows)
        {
            ArrayList      al = new ArrayList();
            RecordsetClass rs = new RecordsetClass();

            try
            {
                int adCmdTable = 2;
                rs.Open(tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);
                if (rs.RecordCount != 0)
                {
                    rs.MoveFirst();
                    while (!rs.EOF)
                    {
                        al.Add(rs.Fields[0].Value);
                        rs.MoveNext();
                    }
                }
            }
            finally
            {
                rs.Close();
            }

            int    adCmdText = 1;
            object recCount  = new object();

            for (int i = 0; i < rows.Length; ++i)
            {
                jcolvalue[]   cols  = rows[i].jcolvalues;
                StringBuilder sbCol = new StringBuilder();
                StringBuilder sbVal = new StringBuilder();

                bool insert = true;
                for (int j = 0; j < cols.Length; ++j)
                {
                    if (cols[j].name == "name" && al.Contains(cols[j].colvalue))
                    {
                        insert = false;
                        break;
                    }
                    sbCol.Append(cols[j].name);
                    sbVal.Append('"' + cols[j].colvalue + '"');
                    if (j != (cols.Length - 1))
                    {
                        sbCol.Append(",");
                        sbVal.Append(",");
                    }
                }
                if (insert)
                {
                    string strSQL = String.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, sbCol.ToString(), sbVal.ToString());
                    //System.Windows.Forms.MessageBox.Show(strSQL);
                    conn.Execute(strSQL, out recCount, adCmdText);
                }
            }
        }
예제 #3
0
        public DataTable ExecuteSql(string sql, int samples)
        {
            Connection target = new ConnectionClass();

            target.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, target, new object[] { this.connectionString });
            if ((this.defaultDatabase != null) && (this.defaultDatabase != ""))
            {
                target.DefaultDatabase = this.defaultDatabase;
            }
            Recordset rs = new RecordsetClass();

            rs.Open(sql, target, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, -1);
            rs.PageSize = samples;
            DataTable table = ConvertRecordset(rs);

            rs.Close();
            target.Close();
            return(table);
        }
예제 #4
0
파일: Form1.cs 프로젝트: summy00/COM
        private void mainForm_Load(object sender, System.EventArgs e)
        {
            // First make use of an ADO Connection type.
            ConnectionClass cn = new ConnectionClass();

            cn.Open("Provider=SQLOLEDB.1;data source=.;initial catalog=pubs;", "sa", "", -1);

            // Now make use of an ADO Recordset.
            RecordsetClass rs = new RecordsetClass();

            rs.Open("Authors", cn, CursorTypeEnum.adOpenKeyset,
                    LockTypeEnum.adLockOptimistic, -1);

            // Using the recordset, construct a DataTable
            // which will be bound to the DataGrid widget.
            DataTable theTable = new DataTable();

            // Fill in column names.
            for (int i = 0; i < rs.Fields.Count; i++)
            {
                theTable.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string)));
            }

            // Fill in rows.
            while (!rs.EOF)
            {
                DataRow currRow;
                currRow = theTable.NewRow();
                for (int i = 0; i < rs.Fields.Count; i++)
                {
                    currRow[i] = rs.Fields[i].Value.ToString();
                }
                theTable.Rows.Add(currRow);
                rs.MoveNext();
            }

            // Now bind to the DataGrid.
            theDataGrid.DataSource = theTable;

            // Close up ADO.
            rs.Close();
            cn.Close();
        }
예제 #5
0
        //Заполняем m_schema из БД
        public void FillFromADOX(string pathDB, bool withData)
        {
            ConnectionClass conn = null;

            try
            {
                conn = new ConnectionClass();
                conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB, "", "", 0);
                m_catalogADOX.ActiveConnection = conn;

                m_schema.tables = new jtables[m_catalogADOX.Tables.Count];
                for (int i = 0; i < m_catalogADOX.Tables.Count; i++)
                {
                    if (m_catalogADOX.Tables[i].Name.Substring(0, 4) != "MSys")
                    {
                        jtables table = new jtables();
                        table.name    = m_catalogADOX.Tables[i].Name;
                        table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count];

                        for (int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++)
                        {
                            jcolumns column = new jcolumns();
                            column.name          = m_catalogADOX.Tables[i].Columns[j].Name;
                            column.type          = m_catalogADOX.Tables[i].Columns[j].Type;
                            column.precision     = m_catalogADOX.Tables[i].Columns[j].Precision;
                            column.definedSize   = m_catalogADOX.Tables[i].Columns[j].DefinedSize;
                            column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value;
                            column.nullable      = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value;
                            column.fixedLength   = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value;
                            //tab.columns.SetValue(col, j);
                            table.columns[j] = column;
                        }

                        ArrayList arrIndex = new ArrayList();

                        //index
                        table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count];
                        for (int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++)
                        {
                            if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey")
                            {
                                string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
                                if (arrIndex.Contains(nameIndex))
                                {
                                    continue;
                                }

                                jindexs index = new jindexs();
                                //index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки
                                index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;

                                index.clustered  = m_catalogADOX.Tables[i].Indexes[j].Clustered;
                                index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey;
                                index.unique     = m_catalogADOX.Tables[i].Indexes[j].Unique;
                                index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls;
                                //tab.indexs.SetValue(ind, j);
                                table.indexs[j] = index;

                                arrIndex.Add(index.name);
                            }
                        }

                        //keys
                        table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count];
                        for (int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++)
                        {
                            if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey")
                            {
                                jkeys key = new jkeys();
                                key.name      = m_catalogADOX.Tables[i].Keys[j].Name;
                                key.column    = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name;
                                key.type      = m_catalogADOX.Tables[i].Keys[j].Type;
                                table.keys[j] = key;
                            }
                        }


                        //data
                        string tableName = m_catalogADOX.Tables[i].Name;
                        if (withData && tableName == "vars")
                        {
                            RecordsetClass rs = new RecordsetClass();
                            try
                            {
                                //int adCmdText = 1;
                                int adCmdTable = 2;
                                rs.Open(tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);

                                if (rs.RecordCount != 0)
                                {
                                    table.rows = new jrows[rs.RecordCount];
                                    int rc = 0;
                                    rs.MoveFirst();
                                    while (!rs.EOF)
                                    {
                                        jrows row = new jrows();

                                        row.jcolvalues = new jcolvalue[rs.Fields.Count];

                                        for (int c = 0; c < rs.Fields.Count; c++)
                                        {
                                            jcolvalue colvalue = new jcolvalue();
                                            colvalue.name     = rs.Fields[c].Name;
                                            colvalue.colvalue = Convert.ToString(rs.Fields[c].Value);

                                            row.jcolvalues[c] = colvalue;
                                        }

                                        table.rows[rc] = row;
                                        rc++;
                                        rs.MoveNext();
                                    }
                                }
                            }
                            finally
                            {
                                rs.Close();
                            }
                            //version
                            try
                            {
                                int adCmdText = 1;
                                rs.Open("SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText);
                                if (rs.RecordCount != 0)
                                {
                                    rs.MoveFirst();
                                    while (!rs.EOF)
                                    {
                                        m_schema.version = Convert.ToInt32(rs.Fields[0].Value);
                                        rs.MoveNext();
                                    }
                                }
                            }
                            finally
                            {
                                rs.Close();
                            }
                        }

                        //m_schema.tables.SetValue(table, i);
                        m_schema.tables[i] = table;
                    }
                }
                m_isSchema = true;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                m_catalogADOX.ActiveConnection = null;
            }
        }
예제 #6
0
파일: Form1.cs 프로젝트: rojac07/COM
        private void mainForm_Load(object sender, System.EventArgs e)
        {
            // First make use of an ADO Connection type.
            ConnectionClass cn = new ConnectionClass();
            cn.Open("Provider=SQLOLEDB.1;data source=.;initial catalog=pubs;", "sa", "", -1);

            // Now make use of an ADO Recordset.
            RecordsetClass rs = new RecordsetClass();
            rs.Open("Authors", cn, CursorTypeEnum.adOpenKeyset,
                LockTypeEnum.adLockOptimistic, -1);

            // Using the recordset, construct a DataTable
            // which will be bound to the DataGrid widget.
            DataTable theTable = new DataTable();

            // Fill in column names.
            for(int i = 0; i < rs.Fields.Count; i++)
                theTable.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string)));

            // Fill in rows.
            while(!rs.EOF)
            {
                DataRow currRow;
                currRow = theTable.NewRow();
                for(int i = 0; i < rs.Fields.Count; i++)
                    currRow[i] = rs.Fields[i].Value.ToString();
                theTable.Rows.Add(currRow);
                rs.MoveNext();
            }

            // Now bind to the DataGrid.
            theDataGrid.DataSource = theTable;

            // Close up ADO.
            rs.Close();
            cn.Close();
        }
예제 #7
0
파일: SchemaDB.cs 프로젝트: rsdn/janus
		//ВНИМАНИЕ!!!: Сделано только для таблицы vars!
		private void FillDataRestruct( ConnectionClass conn, string tableName, jrows[] rows)
		{

			ArrayList al = new ArrayList();
			RecordsetClass rs = new RecordsetClass();
			try
			{
				int adCmdTable = 2;
				rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);
				if (rs.RecordCount != 0)
				{
					rs.MoveFirst();
					while (!rs.EOF)
					{
						al.Add(rs.Fields[0].Value);
						rs.MoveNext();
					}
				}
			}
			finally
			{
				rs.Close();
			}

			int adCmdText = 1;
			object recCount = new object();

			for ( int i = 0; i < rows.Length; ++i )
			{
				jcolvalue[] cols = rows[i].jcolvalues;
				StringBuilder sbCol = new StringBuilder();
				StringBuilder sbVal = new StringBuilder();

				bool insert = true;
				for ( int j = 0; j < cols.Length; ++j )
				{
					if (cols[j].name == "name" && al.Contains(cols[j].colvalue))
					{
						insert = false;
						break;
					}
					sbCol.Append(cols[j].name);
					sbVal.Append('"'+cols[j].colvalue+'"');
					if (j != (cols.Length-1))
					{
						sbCol.Append(",");
						sbVal.Append(",");
					}
				}
				if (insert)
				{
					string strSQL = String.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, sbCol.ToString(), sbVal.ToString());
					//System.Windows.Forms.MessageBox.Show(strSQL);
					conn.Execute( strSQL, out recCount, adCmdText);
				}
			}
		}
예제 #8
0
파일: SchemaDB.cs 프로젝트: rsdn/janus
		//Заполняем m_schema из БД
		public void FillFromADOX( string pathDB, bool withData)
		{
			ConnectionClass conn = null;
			try
			{
				conn = new ConnectionClass();
				conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathDB,"","",0);
				m_catalogADOX.ActiveConnection = conn;

				m_schema.tables = new jtables[m_catalogADOX.Tables.Count];
				for(int i = 0; i < m_catalogADOX.Tables.Count; i++)
				{
					if (m_catalogADOX.Tables[i].Name.Substring(0,4) != "MSys")
					{
						jtables table = new jtables();
						table.name = m_catalogADOX.Tables[i].Name;
						table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count];

						for(int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++)
						{
							jcolumns column = new jcolumns();
							column.name = m_catalogADOX.Tables[i].Columns[j].Name;
							column.type = m_catalogADOX.Tables[i].Columns[j].Type;
							column.precision = m_catalogADOX.Tables[i].Columns[j].Precision;
							column.definedSize = m_catalogADOX.Tables[i].Columns[j].DefinedSize;
							column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value;
							column.nullable = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value;
							column.fixedLength = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value;
							//tab.columns.SetValue(col, j);
							table.columns[j] = column;
						}

						ArrayList arrIndex = new ArrayList();

						//index
						table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count];
						for(int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++)
						{
							if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey")
							{
								string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
								if ( arrIndex.Contains(nameIndex))
									continue;

								jindexs index = new jindexs();
								//index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки
								index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;

								index.clustered = m_catalogADOX.Tables[i].Indexes[j].Clustered;
								index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey;
								index.unique = m_catalogADOX.Tables[i].Indexes[j].Unique;
								index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls;
								//tab.indexs.SetValue(ind, j);
								table.indexs[j] = index;

								arrIndex.Add( index.name);
							}
						}

						//keys
						table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count];
						for(int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++)
						{
							if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey")
							{
								jkeys key = new jkeys();
								key.name = m_catalogADOX.Tables[i].Keys[j].Name;
								key.column = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name;
								key.type = m_catalogADOX.Tables[i].Keys[j].Type;
								table.keys[j] = key;
							}
						}


						//data
						string tableName = m_catalogADOX.Tables[i].Name;
						if (withData && tableName == "vars")
						{
					
							RecordsetClass rs = new RecordsetClass();
							try
							{
								//int adCmdText = 1;
								int adCmdTable = 2;
								rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);

								if (rs.RecordCount != 0)
								{
									table.rows = new jrows[rs.RecordCount];
									int rc = 0;
									rs.MoveFirst();
									while (!rs.EOF)
									{
										jrows row = new jrows();

										row.jcolvalues = new jcolvalue[rs.Fields.Count];

										for (int c = 0; c < rs.Fields.Count; c++)
										{
											jcolvalue colvalue = new jcolvalue();
											colvalue.name = rs.Fields[c].Name;
											colvalue.colvalue = Convert.ToString(rs.Fields[c].Value);

											row.jcolvalues[c] = colvalue;
										}
								
										table.rows[rc] = row;
										rc++;
										rs.MoveNext();
									}
								}
							}
							finally
							{
								rs.Close();
							}
							//version
							try
							{
								int adCmdText = 1;
								rs.Open( "SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText);
								if (rs.RecordCount != 0)
								{
									rs.MoveFirst();
									while (!rs.EOF)
									{
										m_schema.version = Convert.ToInt32(rs.Fields[0].Value);
										rs.MoveNext();
									}
								}
							}
							finally
							{
								rs.Close();
							}
						}

						//m_schema.tables.SetValue(table, i);
						m_schema.tables[i] = table;
					}
				}
				m_isSchema = true;
			}
			finally
			{
				if (conn != null)
					conn.Close();
				m_catalogADOX.ActiveConnection = null;
			}
		}