System.Data.SqlClient.SqlDataReader.Read()

Here are the examples of the csharp api System.Data.SqlClient.SqlDataReader.Read() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

855 Examples 7

19 Source : SqlConnectionExtensions.cs
with MIT License
from gordon-matt

public static IEnumerable<string> GetSchemaNames(this SqlConnection connection)
        {
            const string CMD_SELECT_SCHEMA_NAMES =
@"SELECT s.[name] as [Name]
FROM sys.schemas S
WHERE [schema_id] < 1000
ORDER BY S.[name]";

            var schemaNames = new List<string>();

            bool alreadyOpen = (connection.State != ConnectionState.Closed);

            if (!alreadyOpen)
            {
                connection.Open();
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = CMD_SELECT_SCHEMA_NAMES;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        schemaNames.Add(reader.GetString(0));
                    }
                }
            }

            if (!alreadyOpen)
            {
                connection.Close();
            }

            return schemaNames;
        }

19 Source : Form1.cs
with MIT License
from hanyang0721

private void GetCurrentOrder()
        {
            SqlConnection connection = null;
            try
            {
                string sqltext = "";
                foreach(int interval in targetinterval)
                {
                    sqltext += " SignalTime=DATEADD(minute, -"+ interval+ ", @execution_dt) OR";
                }
                sqltext = sqltext.Substring(0, sqltext.Length - 3);//remove last OR
                using (connection = new SqlConnection(connectionstr))
                {
                    connection.Open();
                    //int intervalMins = Convert.ToInt32(textBox1.Text.ToString()) / 1000 / 60;//convert to minute
                    //intervalMins = 5;
                    SqlCommand sqlcmd = new SqlCommand();
                    sqlcmd.Connection = connection;
                    DateTime nearestMinute = util.RoundToNearest(DateTime.Now, TimeSpan.FromMinutes(1));
                    SqlParameter prar_execution_dt = sqlcmd.Parameters.Add("@execution_dt",System.Data.SqlDbType.DateTime);
                    //SqlParameter interval_para = sqlcmd.Parameters.Add("@intervalMins", System.Data.SqlDbType.Int);
                    prar_execution_dt.Value = nearestMinute;
                    //interval_para.Value = intervalMins;

                    util.RecordLog(connectionstr, "4.2 GetCurrentOrder Start", util.INFO);
                    sqlcmd.CommandText = "SELECT * FROM [dbo].[Orders] WHERE " + sqltext ;

                    //sqlcmd.CommandText = "SELECT TOP 2 * FROM [Stock].[dbo].[Orders] WHERE orderid=53787";
                    using (SqlDataReader reader = sqlcmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            FUTUREORDER pFutureOrder = new FUTUREORDER();
                            //將三個order欄位資訊放入stratName, 方便委託成交回報串資料
                            string stratName = reader["StrategyName"].ToString() + ";"+ reader["SignalTime"].ToString() +";"+ reader["Price"].ToString();
                            pFutureOrder.bstrFullAccount = GetMorningOrNightVars(0);//return account
                            pFutureOrder.bstrPrice = reader["DealPrice"].ToString();
                            pFutureOrder.bstrStockNo = reader["stockNo"].ToString();
                            pFutureOrder.nQty = Convert.ToInt16(reader["Size"].ToString());
                            pFutureOrder.sBuySell = Convert.ToInt16(reader["BuyOrSell"].ToString());
                            pFutureOrder.sDayTrade = Convert.ToInt16(reader["DayTrade"].ToString());
                            //(short)Convert.ToInt32(reader["DayTrade"].ToString()); //當沖0:否 1:是
                            pFutureOrder.sTradeType = Convert.ToInt16(reader["TradeType"].ToString());
                            //(short)Convert.ToInt32(reader["TradeType"].ToString()); ;//0:ROD  1:IOC  2:FOK
                            pFutureOrder.sNewClose = 2;//0新倉 1平倉 2自動
                            OnFutureOrderSignal?.Invoke("", true, pFutureOrder, stratName);//true 為非同步委託
                            util.RecordLog(connectionstr, "4.0 Order loop is finished !", util.INFO);
                        }
                    }
                    util.RecordLog(connectionstr, "4.3 GetCurrentOrder Done", util.INFO);
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                util.RecordLog(connectionstr, "GetCurrentOrder " + ex.ToString(), util.ALARM);
            }
            finally
            {
                connection.Close();
            }
        }

19 Source : ExtendedPropertiesHelper.cs
with MIT License
from havit

private static Dictionary<ExtendedPropertiesKey, Dictionary<string, string>> GetAllExtendedProperties()
        {
            if (allExtendedProperties == null)
            {
                SqlCommand cmd = new SqlCommand("SELECT clreplaced_desc, major_id, minor_id, name, value FROM sys.extended_properties");

                List<Tuple<ExtendedPropertiesKey, string, string>> data = new List<Tuple<ExtendedPropertiesKey, string, string>>();
                using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(cmd))
                {
                    while (dataReader.Read())
                    {
                        string clreplacedDesc = dataReader.GetString(0);
                        long majorId = dataReader.GetInt32(1);
                        long minorId = dataReader.GetInt32(2);
                        string name = (string)dataReader["name"];
                        object value = dataReader["value"];

                        if (value is string)
                        {
                            name = name.Replace("_", ".");
                            var key = new ExtendedPropertiesKey(clreplacedDesc, majorId, minorId);
                            data.Add(new Tuple<ExtendedPropertiesKey, string, string>(key, name, (string)value));
                        }
                    }
                }
                allExtendedProperties = data.GroupBy(item => item.Item1 /* Key */).ToDictionary(group => group.Key, group => group.ToDictionary(item => item.Item2 /* Name */, item => item.Item3 /* Value */, StringComparer.CurrentCultureIgnoreCase));

            }
            return allExtendedProperties;
        }

19 Source : ResourceHelper.cs
with MIT License
from havit

public static List<ResourceClreplaced> GetResourceClreplacedes(Table resourceClreplaced)
		{
			if (_getResourceClreplacedesResult == null)
			{
				_getResourceClreplacedesResult = new List<ResourceClreplaced>();

				using (SqlCommand command = GetResourceClreplacedes_GetSqlCommand(resourceClreplaced))
				{
					using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(command))
					{
						while (dataReader.Read())
						{
							int id = dataReader.GetInt32(0);
							string clreplacedName = dataReader.GetString(1);
							string comment = dataReader.IsDBNull(2) ? "" : dataReader.GetString(2);

							_getResourceClreplacedesResult.Add(new ResourceClreplaced
							{
								ID = id,
								ClreplacedName = clreplacedName,
								Comment = comment
							});
						}
					}
				}
				_getResourceClreplacedesResult.Sort((clreplaced1, clreplaced2) => String.Compare(clreplaced1.ClreplacedName, clreplaced2.ClreplacedName, StringComparison.InvariantCultureIgnoreCase));
			}
			return _getResourceClreplacedesResult;
		}

19 Source : ResourceHelper.cs
with MIT License
from havit

public static List<ResourceItem> GetResourceItems(int resourceClreplacedID)
		{
			if (_getResourceItemsResult == null)
			{
				_getResourceItemsResult = new List<ResourceItem>();

				using (SqlCommand command = GetResourceItems_GetSqlCommand())
				{
                    using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(command))
                    {
                        while (dataReader.Read())
                        {
                            int id = dataReader.GetInt32(0);
                            string name = dataReader.GetString(1);
                            int clreplacedID = dataReader.GetInt32(2);

                            _getResourceItemsResult.Add(new ResourceItem
                            {
                                ID = id,
                                Name = name,
                                ResourceClreplacedID = clreplacedID
                            });
                        }
                    }
				}
			}
			return _getResourceItemsResult.FindAll(item => item.ResourceClreplacedID == resourceClreplacedID);
		}

19 Source : TableHelper.cs
with MIT License
from havit

public static List<EnumMember> GetEnumMembers(Table table)
		{
            List<EnumMember> result = new List<EnumMember>();

			string idColumn = TableHelper.GetPrimaryKey(table).Name;

			string nameColumn;

			nameColumn = ExtendedPropertiesHelper.GetString(ExtendedPropertiesKey.FromTable(table), "EnumPropertyNameField");
			if (nameColumn != null)
			{
				if (!table.Columns.Contains(nameColumn))
				{
					throw new ApplicationException(
						String.Format("Sloupec '{0}' nebyl v tabulce '{1}' nalezen (EnumPropertyNameField).", nameColumn, table.Name));
				}
			}
			if (nameColumn == null)
			{
				nameColumn = ColumnHelper.FindFirstExistingColumn(table, "PropertyName", "Symbol", "Nazev", "Name");
			}

			if (nameColumn == null)
			{
				throw new ApplicationException("Nepodařilo se určit název sloupce pro název property výčtu.");
			}

			string commentColumn = ColumnHelper.FindFirstExistingColumn(table, "Komentar", "Comment");

			using (
				SqlCommand command =
					new SqlCommand(
						String.Format("SELECT [{2}], [{3}]{4} FROM [{0}].[{1}] WHERE LEN([{3}]) > 0",
							table.Schema, // 0
							table.Name, // 1
							idColumn, // 2
							nameColumn, // 3
							String.IsNullOrEmpty(commentColumn) ? "" : ", [" + commentColumn + "]"))) // 4				
			{
                using (SqlDataReader reader = ConnectionHelper.GetDataReader(command))
                {
                    while (reader.Read())
                    {
                        int id = (int)reader[idColumn];

                        string name = (string)reader[nameColumn];
                        string[] names = name.Trim().Split(' ');
                        for (int i = 0; i < names.Length; i++)
                        {
                            names[i] = names[i].Substring(0, 1).ToUpper() + names[i].Substring(1);
                        }
                        name = String.Join("", names);

                        string comment = null;
                        if (commentColumn != null)
                        {
                            comment = (string)reader[commentColumn];
                        }

                        result.Add(new EnumMember(id, name, comment));
                    }
                }
			}

			return result;
		}

19 Source : ImportFileTests.cs
with GNU General Public License v3.0
from HicServices

[Test]
        public void ImportFile()
         {
            string file = Path.GetTempFileName();
            string databaseName = TestDatabaseNames.GetConsistentName(GetType().Name);
            
            try
            {
                using (var sw = new StreamWriter(file))
                {
                    sw.WriteLine("Name,Surname,Age,Healthiness,DateOfImagining");
                    sw.WriteLine("Frank,\"Mortus,M\",41,0.00,2005-12-01");
                    sw.WriteLine("Bob,Balie,12,1,2013-06-11");
                    sw.WriteLine("Munchen,'Smith',43,0.3,2002-01-01");
                    sw.WriteLine("Carnage,Here there is,29,0.91,2005-01-01");
                    sw.WriteLine("Nathan,Crumble,51,0.78,2005-01-01");
                    sw.Close();
                }

                var source = new DelimitedFlatFileDataFlowSource
                {
                    Separator = ",",
                    IgnoreBlankLines = true,
                    MakeHeaderNamesSane = true,
                    StronglyTypeInputBatchSize = -1,
                    StronglyTypeInput = true
                };

                source.PreInitialize(new FlatFileToLoad(new FileInfo(file)), new ThrowImmediatelyDataLoadEventListener());//this is the file we want to load
                source.Check(new ThrowImmediatelyCheckNotifier());
                
                var server = DiscoveredServerICanCreateRandomDatabasesAndTablesOn;
                var database = server.ExpectDatabase(databaseName);

                //recreate it
                database.Create(true);
                
                server.ChangeDatabase(databaseName);

                var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
                
                var tbl = database.CreateTable(dt.TableName, dt);
                string tableName = tbl.GetRuntimeName();

                source.Dispose(new ThrowImmediatelyDataLoadEventListener(), null);

                var tablesInDatabase = server.ExpectDatabase(databaseName).DiscoverTables(false);

                //there should be 1 table in this database
                replacedert.AreEqual(1, tablesInDatabase.Length);

                //it should be called the same as the file loaded
                replacedert.AreEqual(Path.GetFileNameWithoutExtension(file), tablesInDatabase[0].GetRuntimeName());

                replacedert.AreEqual("varchar(7)", GetColumnType(database, tableName, "Name"));
                replacedert.AreEqual("varchar(13)", GetColumnType(database, tableName, "Surname"));
                replacedert.AreEqual("int", GetColumnType(database, tableName, "Age"));
                replacedert.AreEqual("decimal(3,2)", GetColumnType(database, tableName, "Healthiness"));
                replacedert.AreEqual("datetime2", GetColumnType(database, tableName, "DateOfImagining"));

                using (var con = (SqlConnection) server.GetConnection())
                {
                    con.Open();

                    SqlCommand cmdReadData =
                        new SqlCommand(
                            "Select * from " + tablesInDatabase[0].GetRuntimeName() + " WHERE Name='Frank'", con);
                    SqlDataReader r = cmdReadData.ExecuteReader();

                    //expected 1 record only
                    replacedert.IsTrue(r.Read());

                    replacedert.AreEqual("Frank", r["Name"]);
                    replacedert.AreEqual("Mortus,M", r["Surname"]);
                    replacedert.AreEqual(41, r["Age"]);
                    replacedert.AreEqual(0.0f, r["Healthiness"]);
                    replacedert.AreEqual(new DateTime(2005, 12, 1), r["DateOfImagining"]);

                    //and no more records
                    replacedert.IsFalse(r.Read());

                    con.Close();
                }

                server.ExpectDatabase(databaseName).Drop();
                replacedert.IsFalse(server.ExpectDatabase(databaseName).Exists());
            }
            finally 
            {
                try
                {
                    File.Delete(file);
                }
                catch (IOException)
                {
                    //Couldn't delete temporary file... oh well
                }
                
            }

        }

19 Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices

[Test]
        public void JoinablesWithCache()
        {
            const string queryCachingDatabaseName = "MyQueryCachingDatabase";
            var builder = new CohortQueryBuilder(aggregate1, null);

            //make aggregate 2 a joinable
            var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
            joinable2.AddUser(aggregate1);

            //make aggregate 2 have an additional column (dtCreated)
            var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
            aggregate2.AddDimension(anotherCol);

            _queryCachingDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName);
            
            if (_queryCachingDatabase.Exists())
                _queryCachingDatabase.Drop(); //make sure it doesn't exist

            MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase);
            scripter.CreateAndPatchDatabaseWithDotDatabasereplacedembly(typeof(QueryCaching.Database.Clreplaced1).replacedembly, new AcceptAllCheckNotifier());

            var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName);
            queryCachingDatabaseServer.SetProperties(_queryCachingDatabase);
            
            //make the builder use the query cache we just set up
            builder.CacheServer = queryCachingDatabaseServer;
            try
            {
                
               var builderForCaching = new CohortQueryBuilder(aggregate2, null, true);

                var cacheDt = new DataTable();
                using (SqlConnection con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
                {
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con));
                    da.Fill(cacheDt);
                }

                var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer);
                cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null,30));

                try
                {
                    Console.WriteLine(builder.SQL);

                    using (var con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
                    {
                        con.Open();

                        var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();

                        //can read at least one row
                        replacedert.IsTrue(dbReader.Read());
                    }

                    string expectedTableAlias = "ix" + joinable2.ID;

                    //after joinables
                    replacedert.AreEqual(
                        CollapseWhitespace(
                        string.Format(
        @"/*cic_{2}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM 
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
LEFT Join (
	/*Cached:cic_{2}_UnitTestAggregate2*/
	select * from [MyQueryCachingDatabase]..[JoinableInceptionQuery_AggregateConfiguration{1}]

){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi", expectedTableAlias,aggregate2.ID,cohortIdentificationConfiguration.ID)),
     CollapseWhitespace(builder.SQL));

                }
                finally
                {
                    joinable2.Users[0].DeleteInDatabase();
                    joinable2.DeleteInDatabase();
                }
            }
            finally 
            {

                queryCachingDatabaseServer.DeleteInDatabase();
                DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop();
                
            }
            
            

           
        }

19 Source : MasterDatabaseScriptExecutor.cs
with GNU General Public License v3.0
from HicServices

public Patch[] GetPatchesRun()
        { 
            List<Patch> toReturn = new List<Patch>();
            
            using (var con = new SqlConnection(CreateConnectionString()))
            {
                
                con.Open();

                SqlCommand cmd = new SqlCommand("Select * from " +RoundhouseSchemaName +"."+ RoundhouseScriptsRunTable, con);
                var r = cmd.ExecuteReader();

                while (r.Read())
                {
                    string text_of_script = (string)r["text_of_script"];
                    string script_name = (string)r["script_name"] ;

                    if(script_name.Equals(InitialDatabaseScriptName))
                        continue;

                    Patch p = new Patch(script_name,text_of_script);
                    toReturn.Add(p);
                }
                
                con.Close();
            }
            return toReturn.ToArray();
        }

19 Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices

[Test]
        public void QueryBuilderTest()
        {
            var builder = new CohortQueryBuilder(aggregate1, null);
            
            //make aggregate 2 a joinable
            var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository,cohortIdentificationConfiguration, aggregate2);
            joinable2.AddUser(aggregate1);


            Console.WriteLine(builder.SQL);
            try
            {
                using (var con = (SqlConnection) DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
                {
                    con.Open();

                    var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
                    
                    //can read at least one row
                    replacedert.IsTrue(dbReader.Read());
                }

                string expectedTableAlias = "ix" + joinable2.ID;

                //after joinables
                replacedert.AreEqual(
                    string.Format(
    @"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM 
["+TestDatabaseNames.Prefix+ @"ScratchArea]..[BulkData]
LEFT Join (
	/*cic_{1}_UnitTestAggregate2*/
	SELECT
	distinct
	[" + TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
	FROM 
	["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi",expectedTableAlias,cohortIdentificationConfiguration.ID), builder.SQL);

            }
            finally
            {
                joinable2.Users[0].DeleteInDatabase();
                joinable2.DeleteInDatabase();
            }
        }

19 Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices

[Test]
        public void QueryBuilderTest_AdditionalColumn()
        {
            var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
            aggregate2.AddDimension(anotherCol);

            //make aggregate 2 a joinable
            var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
            joinable2.AddUser(aggregate1);

            string expectedTableAlias = "ix" + joinable2.ID;

            var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
            var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);

            var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1);
            var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2);

            filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].dtCreated)) <= 1",expectedTableAlias);
            filter1.SaveToDatabase();

            filter2.WhereSQL = "dtCreated > '2001-01-01'";
            filter2.SaveToDatabase();

            aggregate1.RootFilterContainer_ID = filterContainer1.ID;
            aggregate1.SaveToDatabase();

            aggregate2.RootFilterContainer_ID = filterContainer2.ID;
            aggregate2.SaveToDatabase();

            var builder = new CohortQueryBuilder(aggregate1, null);


            Console.WriteLine(builder.SQL);

            
            try
            {
                using (var con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
                {
                    con.Open();

                    var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();

                    //can read at least one row
                    replacedert.IsTrue(dbReader.Read());
                }


                //after joinables
                replacedert.AreEqual(
                    CollapseWhitespace(
                    string.Format(
    @"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM 
["+TestDatabaseNames.Prefix+ @"ScratchArea]..[BulkData]
LEFT Join (
	/*cic_{1}_UnitTestAggregate2*/
	SELECT distinct
	[" + TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi], ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[dtCreated]
	FROM 
	["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
	WHERE
	(
	/*DateAfter2001*/
	dtCreated > '2001-01-01'
	)
){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi

WHERE
(
/*Within 1 year of event*/
ABS(DATEDIFF(year, {0}.dtCreated, ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].dtCreated)) <= 1
)", expectedTableAlias,cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL));

            }
            finally
            {
                filter1.DeleteInDatabase();
                filter2.DeleteInDatabase();

                filterContainer1.DeleteInDatabase();

                filterContainer2.DeleteInDatabase();
                
                joinable2.Users[0].DeleteInDatabase();
                joinable2.DeleteInDatabase();
            }
        }

19 Source : WordAccessRightsByDatabase.cs
with GNU General Public License v3.0
from HicServices

private void CreateDatabaseTable(DocX doreplacedent, string database)
        {
            using (var con = (SqlConnection) _dbInfo.Server.GetConnection())
            {
                con.Open();
                SqlCommand cmdNumberOfUsers = GetCommandForDatabase(con, database, true);

                int numberOfUsers = int.Parse(cmdNumberOfUsers.ExecuteScalar().ToString());

                Table table = InsertTable(doreplacedent, numberOfUsers + 1, 5);

                var fontSize = 5;

                int tableLine = 0;

                SetTableCell(table, tableLine, 0, "DatabaseName", fontSize);
                SetTableCell(table, tableLine, 1, "UserName", fontSize);
                SetTableCell(table, tableLine, 2, "Role", fontSize);
                SetTableCell(table, tableLine, 3, "PermissionType", fontSize);
                SetTableCell(table, tableLine, 4, "PermissionState", fontSize);
                tableLine++;

                SqlCommand cmdUsers = GetCommandForDatabase(con, database, false);
                SqlDataReader r = cmdUsers.ExecuteReader();

                while (r.Read())
                {
                    SetTableCell(table, tableLine, 0, r["DatabaseName"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 1, r["DatabaseUserName"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 2, r["Role"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 3, r["PermissionType"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 4, r["PermissionState"].ToString(), fontSize);
                    tableLine++;
                }

                r.Close();
                con.Close();
            }
        }

19 Source : WordAccessRightsByUser.cs
with GNU General Public License v3.0
from HicServices

private string[] UserAccounts()
        {
            List<string> toReturn = new List<string>();

            using (var c = (SqlConnection) _dbInfo.Server.GetConnection())
            {
                c.Open();

                //get the current users with login rights or database permissions
                string sql =
                    @"select distinct name from {0}.[dbo].[Logins] where name not like '%##%' and name not like '%NT %' and name not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole')
union
select distinct DatabaseUserName as name from {0}.[dbo].[DatabasePermissions] where DatabaseUserName not like '%##%' and DatabaseUserName not like '%NT %'  and DatabaseUserName not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole') 
";
                //and get the expired user accounts / expired permissions too
                if (!_currentUsersOnly)
                    sql += @"union
select distinct name from {0}.[dbo].[Logins_Archive] where name not like '%##%' and name not like '%NT %'  and name not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole') 
union
select distinct DatabaseUserName as name  from {0}.[dbo].[DatabasePermissions_Archive] where DatabaseUserName not like '%##%'  and DatabaseUserName not like '%NT %' and DatabaseUserName not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole') ";

                SqlCommand cmd = new SqlCommand(
                    string.Format(sql, _dbInfo.GetRuntimeName()), c);
                SqlDataReader r = cmd.ExecuteReader();

                while (r.Read())
                    toReturn.Add(r["name"] as string);
            }

            return toReturn.ToArray();
        }

19 Source : WordAccessRightsByDatabase.cs
with GNU General Public License v3.0
from HicServices

private void CreateAdministratorsTable(DocX doreplacedent)
        {

            using (var con = (SqlConnection) _dbInfo.Server.GetConnection())
            {
                con.Open();
                SqlCommand cmdNumberOfAdmins = new SqlCommand("Select count(*) " + adminsFromAndWhere, con);

                int numberOfAdmins = int.Parse(cmdNumberOfAdmins.ExecuteScalar().ToString());

                Table table = InsertTable(doreplacedent, numberOfAdmins + 1, 9);

                var fontSize = 5;

                int tableLine = 0;

                SetTableCell(table, tableLine, 0, "name", fontSize);
                SetTableCell(table, tableLine, 1, "sysadmin", fontSize);
                SetTableCell(table, tableLine, 2, "securityadmin", fontSize);
                SetTableCell(table, tableLine, 3, "serveradmin", fontSize);
                SetTableCell(table, tableLine, 4, "setupadmin", fontSize);
                SetTableCell(table, tableLine, 5, "processadmin", fontSize);
                SetTableCell(table, tableLine, 6, "diskadmin", fontSize);
                SetTableCell(table, tableLine, 7, "dbcreator", fontSize);
                SetTableCell(table, tableLine, 8, "bulkadmin", fontSize);
                tableLine++;

                SqlCommand cmdAdmins = new SqlCommand("Select * " + adminsFromAndWhere + " ORDER BY name", con);
                SqlDataReader r = cmdAdmins.ExecuteReader();

                while (r.Read())
                {
                    SetTableCell(table, tableLine, 0, r["name"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 1, r["sysadmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 2, r["securityadmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 3, r["serveradmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 4, r["setupadmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 5, r["processadmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 6, r["diskadmin"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 7, r["dbcreator"].ToString(), fontSize);
                    SetTableCell(table, tableLine, 8, r["bulkadmin"].ToString(), fontSize);
                    tableLine++;
                }

                r.Close();
                con.Close();
            }
            
        }

19 Source : SoftSqlOperate.cs
with GNU Lesser General Public License v3.0
from HslCommunication-Community

public static int ExecuteSelectCount(SqlConnection conn, string cmdStr)
        {
            using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
            {
                int temp = 0;
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    temp = Convert.ToInt32(sdr[0]);
                }
                sdr.Close();
                return temp;
            }
        }

19 Source : Login_Page.cs
with BSD 3-Clause "New" or "Revised" License
from Ideasis

protected void Page_Load()
        {
            
            int flag = 0;
            string connectionString = "Data Source=.\\SQLEXPRESS; Integrated Security=True;";
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            if (dr[0].ToString() == "Vivos")
                            {
                                flag = 1;
                                con.Close();
                                break;
                            }
                        }
                    }
                }

                if (flag == 0)
                {
                    string startuppath = System.IO.Path.GetFullPath(".\\");
                    string MDFCreator = startuppath + "\\VivoosMDFCreator.sql";
                    string TableCreator = startuppath + "\\VivoosTableCreator.sql";
                    string elementAdder = startuppath + "\\ElementAdder.sql";
                    try
                    {
                        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
                        string script = File.ReadAllText(MDFCreator);
                        SqlConnection conn = new SqlConnection(sqlConnectionString);
                        Server server = new Server(new ServerConnection(conn));
                        server.ConnectionContext.ExecuteNonQuery(script);

                        string sqlConnectionString1 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
                        string script1 = File.ReadAllText(TableCreator);
                        SqlConnection conn1 = new SqlConnection(sqlConnectionString1);
                        Server server1 = new Server(new ServerConnection(conn));
                        server.ConnectionContext.ExecuteNonQuery(script);

                        string sqlConnectionString2 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
                        string script2 = File.ReadAllText(elementAdder);
                        SqlConnection conn2 = new SqlConnection(sqlConnectionString2);
                        Server server2 = new Server(new ServerConnection(conn2));
                        server.ConnectionContext.ExecuteNonQuery(script2);

                        try
                        {
                            add_scenarios(Properties.Resources.Fear_Of_Dog_Park_File.ToString(), Properties.Resources.Fear_Of_Dog_Park_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Height_Balcony_File.ToString(), Properties.Resources.Fear_Of_Height_Balcony_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Spider_File.ToString(), Properties.Resources.Fear_Of_Spider_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Dark_File.ToString(), Properties.Resources.Fear_Of_Dark_Image);
                            add_scenarios(Properties.Resources.Social_Anxiety_Clreplacedroom_File.ToString(), Properties.Resources.Social_Anxiety_Clreplacedroom_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Height_Elevator_File.ToString(), Properties.Resources.Fear_Of_Height_Elevator_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Flight_File.ToString(), Properties.Resources.Fear_Of_Flight_Image);
                            add_scenarios(Properties.Resources.Park_Ayda_File.ToString(), Properties.Resources.Park_Ayda_Image);
                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show(ex.ToString());
                        }
                        
                    }
                    catch (Exception)
                    {
                        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
                        string script = File.ReadAllText(TableCreator);
                        SqlConnection conn = new SqlConnection(sqlConnectionString);
                        Server server = new Server(new ServerConnection(conn));
                        server.ConnectionContext.ExecuteNonQuery(script);

                        string sqlConnectionString1 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
                        string script1 = File.ReadAllText(elementAdder);
                        SqlConnection conn1 = new SqlConnection(sqlConnectionString1);
                        Server server1 = new Server(new ServerConnection(conn1));
                        server.ConnectionContext.ExecuteNonQuery(script1);


                        try
                        {
                            add_scenarios(Properties.Resources.Fear_Of_Dog_Park_File.ToString(), Properties.Resources.Fear_Of_Dog_Park_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Height_Balcony_File.ToString(), Properties.Resources.Fear_Of_Height_Balcony_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Spider_File.ToString(), Properties.Resources.Fear_Of_Spider_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Dark_File.ToString(), Properties.Resources.Fear_Of_Dark_Image);
                            add_scenarios(Properties.Resources.Social_Anxiety_Clreplacedroom_File.ToString(), Properties.Resources.Social_Anxiety_Clreplacedroom_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Height_Elevator_File.ToString(), Properties.Resources.Fear_Of_Height_Elevator_Image);
                            add_scenarios(Properties.Resources.Fear_Of_Flight_File.ToString(), Properties.Resources.Fear_Of_Flight_Image);
                            add_scenarios(Properties.Resources.Park_Ayda_File.ToString(), Properties.Resources.Park_Ayda_Image);
                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show(ex.ToString());
                        }
                    }
                }
            }
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static FindItemResponse FindItem(dynamic jsonRequestData, SqlConnection connection, ILogger log)
        {
            string item = jsonRequestData;

            string nameKey = QueryHelper.Instance.SingularizeAndLower(item);

            List<Item> items = new List<Item>();

            var queryString = $"SELECT Name,Quanreplacedy,Row,Col FROM dbo.Items WHERE Items.NameKey LIKE @param1";

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", nameKey);

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        items.Add(
                            new Item
                            {
                                Name = (string)reader[Dbo.Items.Name],
                                Quanreplacedy = (int)reader[Dbo.Items.Quanreplacedy],
                                Row = (int)reader[Dbo.Items.Row],
                                Col = (int)reader[Dbo.Items.Col]
                            });
                    }

                    if (items.Count > 0)
                    {
                        return new FindItemResponse(items);
                    }

                    // Todo: match tags extracted from input word and return top 3
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }

            return new FindItemResponse(null);
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static FindItemResponse TryFindItem(string item, SqlConnection conn, ILogger log)
        {
            string singularizedItem = QueryHelper.Instance.SingularizeAndLower(item);

            List<Item> items = new List<Item>();

            var queryString = $"SELECT * FROM dbo.Items WHERE Items.NameKey LIKE @param1";

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", singularizedItem);

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        items.Add(
                            new Item
                            {
                                Name = (string)reader[Dbo.Items.Name],
                                Quanreplacedy = (int)reader[Dbo.Items.Quanreplacedy],
                                Row = (int)reader[Dbo.Items.Row],
                                Col = (int)reader[Dbo.Items.Col],
                                IsSmallBox = (bool)reader[Dbo.Items.IsSmallBox],
                                DateCreated = (DateTime)reader[Dbo.Items.DateCreated],
                                LastUpdated = (DateTime)reader[Dbo.Items.LastUpdated]
                            });
                    }
                    return new FindItemResponse(items);
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);

                    return new FindItemResponse(null);
                }
                finally
                {
                    reader.Close();
                }
            }
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static FindTagsResponse FindTags(TagSet tagSet, SqlConnection connection, ILogger log, int maxResults = 10)
        {
            if (tagSet.Count == 0) return new FindTagsResponse(-1, null);

            // Take a string of words: "Green motor driver"
            // Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
            // Format as params for SQL query, to defend against SQL injection attacks:
            //     "@param2,@param3,@param4"
            string paramList = string.Join(",", tagSet.Select((_, index) => $"@param{index + 2}"));
            log.LogInformation(paramList);

            var queryString = $@"
SELECT i.NameKey, i.Name, i.Quanreplacedy, i.Row, i.Col, t.TagsMatched
FROM dbo.Items i JOIN
(
    SELECT NameKey, COUNT(NameKey) TagsMatched
    FROM dbo.Tags
    WHERE Tag IN({paramList})
    GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", maxResults);

                int index = 2;
                foreach (string tag in tagSet)
                {
                    command.Parameters.AddWithValue($"@param{index++}", tag);
                }

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    int i = 0;

                    List<int[]> coordsAndMatches = new List<int[]>();
                    while (reader.Read() && i++ < maxResults)
                    {
                        coordsAndMatches.Add(new int[] { (int)reader["Row"], (int)reader["Col"], (int)reader["TagsMatched"] });
                    }

                    return new FindTagsResponse(tagSet.Count, coordsAndMatches);
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);
                    return new FindTagsResponse(tagSet.Count, null);
                }
                finally
                {
                    reader.Close();
                }
            }
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static BundleWithResponse BundleWithTags(string text, int quanreplacedy, SqlConnection connection, ILogger log)
        {
            string[] itemAndTags = text.Split(" with tags ", StringSplitOptions.RemoveEmptyEntries);

            if (itemAndTags.Length != 2)
            {
                return new BundleWithResponse();
            }

            string newItem = itemAndTags[0].Trim();
            TagSet existingItemTags = new TagSet(itemAndTags[1]);

            // Take a string of words: "Green motor driver"
            // Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
            // Format as params for SQL query, to defend against SQL injection attacks:
            //     "@param2,@param3,@param4"
            string paramList = string.Join(",", existingItemTags.Select((tag, index) => $"@param{index+2}"));
            log.LogInformation(paramList);

            int maxResults = 3;

            var queryString = $@"
SELECT TOP (@param1) i.Name, i.Quanreplacedy, i.Row, i.Col, i.IsSmallBox, t.TagsMatched
FROM dbo.Items i JOIN
(
    SELECT NameKey, COUNT(NameKey) TagsMatched
    FROM dbo.Tags
    WHERE Tag IN({paramList})
    GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";

            List<TaggedItem> items = new List<TaggedItem>();

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = queryString;
                command.Parameters.AddWithValue("@param1", maxResults);

                int index = 2;
                foreach (string tag in existingItemTags)
                {
                    command.Parameters.AddWithValue($"@param{index++}", tag);
                }
                
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        items.Add(new TaggedItem
                        {
                            Name = (string)reader["Name"],
                            Row = (int)reader["Row"],
                            Col = (int)reader["Col"],
                            TagsMatched = (int)reader["TagsMatched"],
                            IsSmallBox = (bool)reader["IsSmallBox"]
                        });
                    }
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);
                    return new BundleWithResponse();
                }
                finally
                {
                    reader.Close();
                }
            }

            IEnumerable<TaggedItem> fullyMatchedItems = items.Where(a => a.TagsMatched.Value == existingItemTags.Count);

            if (fullyMatchedItems.Count() == 1)
            {
                TaggedItem existingItem = fullyMatchedItems.First();
                Item inserreplacedem = new Item(
                    newItem, 
                    quanreplacedy, 
                    existingItem.Row.Value, 
                    existingItem.Col.Value, 
                    existingItem.IsSmallBox.Value);

                TagSet newItemTags = new TagSet(newItem);

                InserreplacedemResponse resp = InserreplacedemWithTags(inserreplacedem, newItemTags, connection, log);

                if (resp.Success)
                {
                    return new BundleWithResponse(true, newItem, quanreplacedy, existingItem.Name, inserreplacedem.Row, inserreplacedem.Col);
                }
                else
                {
                    return new BundleWithResponse(false, newItem, quanreplacedy, existingItem.Name);
                }
            }

            return new BundleWithResponse();
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static ICommandResponse Inserreplacedem(dynamic jsonRequestData, SqlConnection connection, ILogger log)
        {
            string info = jsonRequestData["Info"];
            int quanreplacedy = jsonRequestData["Quanreplacedy"];

            string infoLower = info.ToLowerInvariant();

            bool hasBox = TryGetBoxInfo(infoLower, out int boxIndex, out string boxSearch, out bool useSmallBox);
            bool hasTags = TryGetTagsInfo(infoLower, boxIndex, out int tagsIndex, out TagSet tagSet);
            string itemName = GereplacedemInfo(info, hasBox, hasTags, boxIndex, tagsIndex);

            tagSet.ParseAndUnionWith(itemName);

            FindItemResponse findItemResponse = FindItem(itemName, connection, log);

            if (findItemResponse.Count > 0)
            {
                return findItemResponse;
            }

            // Item doesn't exist; insert.
            // Find existing boxes
            var sqlAllConsumedBoxes = string.Format("SELECT DISTINCT ROW,COL FROM dbo.Items");
            MatrixModel matrix = new MatrixModel();

            using (SqlCommand command = new SqlCommand(sqlAllConsumedBoxes, connection))
            {
                SqlDataReader reader = command.ExecuteReader();

                try
                {
                    while (reader.Read())
                    {
                        matrix.AddItem((int)reader[Dbo.Items.Row], (int)reader[Dbo.Items.Col]);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            var (row, col) = matrix.GetNextAvailableBox(useSmallBox);

            if (row == -1 && col == -1)
            {
                return new InserreplacedemResponse(false);
            }

            Item item = new Item(itemName, quanreplacedy, row, col, useSmallBox);

            return InserreplacedemWithTags(item, tagSet, connection, log);
        }

19 Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22

public static ShowAllBoxesResponse ShowAllBoxes(SqlConnection connection, ILogger log)
        {
            string allBoxesQuery = $@"SELECT DISTINCT Row,Col FROM dbo.Items";

            using (SqlCommand command = new SqlCommand(allBoxesQuery, connection))
            {
                SqlDataReader reader = command.ExecuteReader();

                try
                {
                    StringBuilder sb = new StringBuilder();

                    while(reader.Read())
                    {
                        sb.Append((char)((int)reader[Dbo.Items.Row] + 'a'));
                        sb.Append((char)((int)reader[Dbo.Items.Col] + 'a'));
                    }

                    string coords = sb.ToString();
                    
                    return new ShowAllBoxesResponse(coords.Length / 2, coords);
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);

                    return new ShowAllBoxesResponse();
                }
                finally
                {
                    reader.Close();
                }
            }
        }

19 Source : SeedDataGenerator.cs
with Apache License 2.0
from iQuarc

private IEnumerable<IEnumerable<object>> GetTableValues(Table table)
        {
            var connectionString = configurationRoot.GetConnectionString(ConnectionName);
            using (var cnn = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand())
            {
                var columns = table.Columns
                    .Where(columnsFilter)
                    .ToList();
                var where = whereClause(table);
                cmd.CommandText = $"SELECT {CommaJoin(columns, ColumnExpression)} FROM [{table.Schema.Name}].[{table.Name}] WHERE {(String.IsNullOrEmpty(where) ? "1=1" : where)}";
                cnn.Open();
                cmd.Connection = cnn;
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var arr = new object[rdr.FieldCount];
                        rdr.GetValues(arr);
                        yield return arr;
                    }
                }
            }
        }

19 Source : Sample12.cs
with GNU Lesser General Public License v2.1
from jet-global

private static List<SalesDTO> GetDataFromSQL(string sqlServerName)
		{
			string connectionStr = string.Format(@"server={0};database=AdventureWorks;Integrated Security=true;", sqlServerName);
			var ret = new List<SalesDTO>();
			// lets connect to the AdventureWorks sample database for some data
			using (SqlConnection sqlConn = new SqlConnection(connectionStr))
			{
				sqlConn.Open();
				using (SqlCommand sqlCmd = new SqlCommand("select h.replacedle, FirstName, MiddleName, LastName, SubTotal, OrderDate, TaxAmt, Freight, TotalDue  from Sales.SalesOrderHeader s inner join HumanResources.Employee h on s.SalesPersonID = h.EmployeeID inner join Person.Contact c on c.ContactID = h.ContactID order by LastName, FirstName, MiddleName;", sqlConn))
				{
					using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
					{
						//Get the data and fill rows 5 onwards
						while (sqlReader.Read())
						{
							ret.Add(new SalesDTO
							{
								replacedle = sqlReader["replacedle"].ToString(),
								FirstName = sqlReader["FirstName"].ToString(),
								MiddleName = sqlReader["MiddleName"].ToString(),
								LastName = sqlReader["LastName"].ToString(),
								OrderDate = (DateTime)sqlReader["OrderDate"],
								SubTotal = (decimal)sqlReader["SubTotal"],
								Tax = (decimal)sqlReader["TaxAmt"],
								Freight = (decimal)sqlReader["Freight"]
							});
						}
					}
				}
			}
			return ret;
		}

19 Source : Sample3.cs
with GNU Lesser General Public License v2.1
from jet-global

public static string RunSample3(DirectoryInfo outputDir, string connectionString)
		{

			string file = outputDir.FullName + @"\sample3.xlsx";
			if (File.Exists(file)) File.Delete(file);
			FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample3.xlsx");

			// ok, we can run the real code of the sample now
			using (ExcelPackage xlPackage = new ExcelPackage(newFile))
			{
				// uncomment this line if you want the XML written out to the outputDir
				//xlPackage.DebugMode = true; 

				// get handle to the existing worksheet
				ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
				var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");   //This one is language dependent
				namedStyle.Style.Font.UnderLine = true;
				namedStyle.Style.Font.Color.SetColor(Color.Blue);
				if (worksheet != null)
				{
					const int startRow = 5;
					int row = startRow;
					//Create Headers and format them 
					worksheet.Cells["A1"].Value = "AdventureWorks Inc.";
					using (ExcelRange r = worksheet.Cells["A1:G1"])
					{
						r.Merge = true;
						r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
						r.Style.Font.Color.SetColor(Color.White);
						r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
						r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
						r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
					}
					worksheet.Cells["A2"].Value = "Year-End Sales Report";
					using (ExcelRange r = worksheet.Cells["A2:G2"])
					{
						r.Merge = true;
						r.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));
						r.Style.Font.Color.SetColor(Color.Black);
						r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
						r.Style.Fill.PatternType = ExcelFillStyle.Solid;
						r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
					}

					worksheet.Cells["A4"].Value = "Name";
					worksheet.Cells["B4"].Value = "Job replacedle";
					worksheet.Cells["C4"].Value = "Region";
					worksheet.Cells["D4"].Value = "Monthly Quota";
					worksheet.Cells["E4"].Value = "Quota YTD";
					worksheet.Cells["F4"].Value = "Sales YTD";
					worksheet.Cells["G4"].Value = "Quota %";
					worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
					worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
					worksheet.Cells["A4:G4"].Style.Font.Bold = true;


					// lets connect to the AdventureWorks sample database for some data
					using (SqlConnection sqlConn = new SqlConnection(connectionString))
					{
						sqlConn.Open();
						using (SqlCommand sqlCmd = new SqlCommand("select LastName + ', ' + FirstName AS [Name], EmailAddress, Jobreplacedle, CountryRegionName, ISNULL(SalesQuota,0) AS SalesQuota, ISNULL(SalesQuota,0)*12 AS YearlyQuota, SalesYTD from Sales.vSalesPerson ORDER BY SalesYTD desc", sqlConn))
						{
							using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
							{
								// get the data and fill rows 5 onwards
								while (sqlReader.Read())
								{
									int col = 1;
									// our query has the columns in the right order, so simply
									// iterate through the columns
									for (int i = 0; i < sqlReader.FieldCount; i++)
									{
										// use the email address as a hyperlink for column 1
										if (sqlReader.GetName(i) == "EmailAddress")
										{
											// insert the email address as a hyperlink for the name
											string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
											worksheet.Cells[row, 1].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
										}
										else
										{
											// do not bother filling cell with blank data (also useful if we have a formula in a cell)
											if (sqlReader.GetValue(i) != null)
												worksheet.Cells[row, col].Value = sqlReader.GetValue(i);
											col++;
										}
									}
									row++;
								}
								sqlReader.Close();

								worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
								worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
								worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";

								worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";

								//Set column width
								worksheet.Column(1).Width = 25;
								worksheet.Column(2).Width = 28;
								worksheet.Column(3).Width = 18;
								worksheet.Column(4).Width = 12;
								worksheet.Column(5).Width = 10;
								worksheet.Column(6).Width = 10;
								worksheet.Column(7).Width = 12;
							}
						}
						sqlConn.Close();
					}

					// lets set the header text 
					worksheet.HeaderFooter.OddHeader.CenteredText = "AdventureWorks Inc. Sales Report";
					// add the page number to the footer plus the total number of pages
					worksheet.HeaderFooter.OddFooter.RightAlignedText =
						string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
					// add the sheet name to the footer
					worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
					// add the file path to the footer
					worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
				}
				// we had better add some doreplacedent properties to the spreadsheet 

				// set some core property values
				xlPackage.Workbook.Properties.replacedle = "Sample 3";
				xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
				xlPackage.Workbook.Properties.Subject = "ExcelPackage Samples";
				xlPackage.Workbook.Properties.Keywords = "Office Open XML";
				xlPackage.Workbook.Properties.Category = "ExcelPackage Samples";
				xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";

				// set some extended property values
				xlPackage.Workbook.Properties.Company = "AdventureWorks Inc.";
				xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.codeplex.com/MSFTDBProdSamples");

				// set some custom property values
				xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
				xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
				xlPackage.Workbook.Properties.SetCustomPropertyValue("replacedemblyName", "ExcelPackage");

				// save the new spreadsheet
				xlPackage.Save();
			}

			// if you want to take a look at the XML created in the package, simply uncomment the following lines
			// These copy the output file and give it a zip extension so you can open it and take a look!
			//FileInfo zipFile = new FileInfo(outputDir.FullName + @"\sample3.zip");
			//if (zipFile.Exists) zipFile.Delete();
			//newFile.CopyTo(zipFile.FullName);

			return newFile.FullName;
		}

19 Source : Sample4.cs
with GNU Lesser General Public License v2.1
from jet-global

public static string RunSample4(string connectionString, FileInfo template, DirectoryInfo outputdir)
		{
			using (ExcelPackage p = new ExcelPackage(template, true))
			{
				//Set up the headers
				ExcelWorksheet ws = p.Workbook.Worksheets[1];
				ws.Cells["A20"].Value = "Date";
				ws.Cells["B20"].Value = "EOD Rate";
				ws.Cells["B20:D20"].Merge = true;
				ws.Cells["E20"].Value = "Change";
				ws.Cells["E20:G20"].Merge = true;
				ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
				using (ExcelRange row = ws.Cells["A20:G20"])
				{
					row.Style.Fill.PatternType = ExcelFillStyle.Solid;
					row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
					row.Style.Font.Color.SetColor(Color.White);
					row.Style.Font.Bold = true;
				}
				ws.Cells["B21"].Value = "USD/JPY";
				ws.Cells["C21"].Value = "USD/EUR";
				ws.Cells["D21"].Value = "USD/GBP";
				ws.Cells["E21"].Value = "USD/JPY";
				ws.Cells["F21"].Value = "USD/EUR";
				ws.Cells["G21"].Value = "USD/GBP";
				using (ExcelRange row = ws.Cells["A21:G21"])
				{
					row.Style.Fill.PatternType = ExcelFillStyle.Solid;
					row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
					row.Style.Font.Color.SetColor(Color.Black);
					row.Style.Font.Bold = true;
				}

				int startRow = 22;
				//Connect to the database and fill the data
				using (SqlConnection sqlConn = new SqlConnection(connectionString))
				{
					int row = startRow;
					sqlConn.Open();
					using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [AdventureWorks].[Sales].[CurrencyRate]  where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate  ORDER BY CurrencyRateDate", sqlConn))
					{
						using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
						{
							// get the data and fill rows 22 onwards
							while (sqlReader.Read())
							{
								ws.Cells[row, 1].Value = sqlReader[0];
								ws.Cells[row, 2].Value = sqlReader[1];
								ws.Cells[row, 3].Value = sqlReader[2];
								ws.Cells[row, 4].Value = sqlReader[3];
								row++;
							}
						}
						//Set the numberformat
						ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
						ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
						//Set the Formulas 
						ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
						ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
					}

					//Set the series for the chart. The series must exist in the template or the program will crash.
					ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]);
					chart.replacedle.Text = "Exchange rate %";
					chart.Series[0].Header = "USD/JPY";
					chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
					chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);

					chart.Series[1].Header = "USD/EUR";
					chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
					chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);

					chart.Series[2].Header = "USD/GBP";
					chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
					chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
				}
				//Get the doreplacedet as a byte array from the stream and save it to disk.  (This is usefull in a webapplication) ... 
				Byte[] bin = p.GetAsByteArray();

				string file = outputdir + "\\sample4.xlsx";
				File.WriteAllBytes(file, bin);
				return file;
			}
		}

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void btnLoad_Click(object sender, EventArgs e)
        {
            Dictionary<int, string> runIDs = new Dictionary<int, string>();
            Dictionary<int, string> selectedRunIDs = new Dictionary<int, string>();
            RunSelection dlgSelectRun = new RunSelection();

            sqlConnection = new SqlConnection(this.connectionString);
            sqlConnection.Open();

            graphViewer.Enabled = false;
            graphViewer.Graph = null;
            objectIDToNameMap.Clear();
            objectIDToTypeMap.Clear();
            dependencyGraph.Clear();

            numberInbound = (int)nbBefore.Value;
            numberOutbound = (int)nbAfter.Value;
            // Get the list of Run's
            using (SqlCommand runsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveRunIDs];", sqlConnection))
            {
                using (SqlDataReader sqlReader = runsCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        runIDs.Add(sqlReader.GetInt32(0), sqlReader.GetString(1));
                    }
                }
            }

            dlgSelectRun.LoadItems(runIDs);

            if (dlgSelectRun.ShowDialog() == DialogResult.OK)
            {
                selectedRunIDs = dlgSelectRun.Gereplacedems();

                runIDsInList = "";
                foreach (KeyValuePair<int, string> runText in selectedRunIDs)
                {
                    if (runIDsInList == "")
                    {
                        runIDsInList = runText.Key.ToString();
                    }
                    else
                    {
                        runIDsInList += ", " + runText.Key.ToString();
                    }
                }

                if (runIDsInList != "")
                {
                    graphViewer.Enabled = true;
                    // Initialize list of objects
                    InitializeObjectsMap();

                    // initialize the lineage map graph
                    InitializeLineageMap();

                    PopulateObjectList();
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

void AddObjectDetails(int objectID)
        {
            string objectType, objectDesc;
            using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectDetails] @RunList, @ObjectKey", sqlConnection))
            {
                objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                objectsCommand.Parameters.Add("@ObjectKey", SqlDbType.Int).Value = objectID;
                using (SqlDataReader attributes = objectsCommand.ExecuteReader())
                {
                    attributes.Read();
                    objectType = attributes["ObjectTypeString"] as string;
                    objectDesc = attributes["ObjectDesc"] as string;
                }
            }

            // try to convert object type name to readable name in ObjectTypes table
            using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectTypes] @ObjectTypeKey", sqlConnection))
            {
                objectsCommand.Parameters.Add(new SqlParameter("@ObjectTypeKey", objectType));
                using (SqlDataReader attributes = objectsCommand.ExecuteReader())
                {
                    if (attributes.Read())
                    {
                        objectType = (string)attributes["ObjectTypeName"];
                    }
                }
            }

            string groupName = string.Format("{0} [{1}] [ID: {2}]", this.objectIDToNameMap[objectID], objectType, objectID);
            ListViewGroup group = new ListViewGroup(groupName);
            this.lvObjectProperties.Groups.Add(group);

            if (!System.Windows.Forms.VisualStyles.VisualStyleInformation.IsSupportedByOS)
            {
                // if OS does not support groups, we need to create a list view item, since groups are ignored
                ListViewItem grp = new ListViewItem(this.objectIDToNameMap[objectID]);
                grp.SubItems.Add(string.Format("[{0}] [ID: {1}]", objectType, objectID));
                grp.Font = new Font(this.lvObjectProperties.Font, System.Drawing.FontStyle.Bold);
                this.lvObjectProperties.Items.Add(grp);
            }

            // we need at least one item in a group for the group to show up
            // thus show the description even if it is empty
            lvObjectProperties.Items.Add(CreateAttributeViewItem(group, "Description", objectDesc));

            using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectAttributes] @RunList, @ObjectKey", sqlConnection))
            {
                objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                objectsCommand.Parameters.Add("@ObjectKey", SqlDbType.Int).Value = objectID;
                using (SqlDataReader attributes = objectsCommand.ExecuteReader())
                {
                    while (attributes.Read())
                    {
                        string attribName = (string)attributes["ObjectAttrName"];
                        string attribValue = (string)attributes["ObjectAttrValue"];

                        if (!string.IsNullOrEmpty(attribValue))
                        {
                            lvObjectProperties.Items.Add(CreateAttributeViewItem(group, attribName, attribValue));
                        }
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void ShowreplacedysisServicesObjects()
        {
            TreeNode replacedysisServicesRoot = tvObjectList.Nodes.Add("replacedysis Services");

            using (SqlCommand replacedysisServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSASObjects] @RunList", sqlConnection))
            {
                replacedysisServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader sqlReader = replacedysisServersCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        TreeNode objectNode = replacedysisServicesRoot.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        // for each node add a dummy child node so that we get a '+' and can dig in deeper
                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void ShowConnections()
        {
            TreeNode connectionsRootNode = tvObjectList.Nodes.Add("Relational Database");
            using (SqlCommand connectionsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSQLSObjects] @RunList", sqlConnection))
            {
                connectionsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader sqlReader = connectionsCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        TreeNode objectNode = connectionsRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        // for each node add a dummy child node so that we get a '+' and can dig in deeper
                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void ShowFileServers()
        {
            TreeNode filesRootNode = tvObjectList.Nodes.Add("Files");
            using (SqlCommand fileServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveFileObjects] @RunList", sqlConnection))
            {
                fileServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader sqlReader = fileServersCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        TreeNode objectNode = filesRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        // for each node add a dummy child node so that we get a '+' and can dig in deeper
                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void ShowPackages()
        {
            TreeNode packageRootNode = tvObjectList.Nodes.Add("Integration Services");

            using (SqlCommand packageCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSISObjects] @RunList", this.sqlConnection))
            {
                packageCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader sqlReader = packageCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        TreeNode objectNode = packageRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        // for each node add a dummy child node so that we get a '+' and can dig in deeper
                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void ShowReports()
        {
            TreeNode reportServersRootNode = tvObjectList.Nodes.Add("Reporting Servers");
            using (SqlCommand reportServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSRSObjects] @RunList", sqlConnection))
            {
                reportServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader sqlReader = reportServersCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        TreeNode objectNode = reportServersRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        // for each node add a dummy child node so that we get a '+' and can dig in deeper
                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void AddChildren(int parentID, TreeNode parentNode)
        {
            using (SqlCommand childrenCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveContained] @SrcObjectKey", sqlConnection))
            {
                childrenCommand.Parameters.Add("@SrcObjectKey", SqlDbType.Int).Value = parentID;
                using (SqlDataReader sqlReader = childrenCommand.ExecuteReader())
                {
                    string lastCategory = null;
                    TreeNode categoryNode = null;
                    while (sqlReader.Read())
                    {
                        // get the type category
                        string currentCategory = sqlReader.GetString(2);

                        if (lastCategory != currentCategory)
                        {
                            lastCategory = currentCategory;
                            categoryNode = parentNode.Nodes.Add(currentCategory);
                        }

                        TreeNode objectNode = categoryNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));

                        objectNode.Nodes.Add(new DummyTreeNode());
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void InitializeObjectsMap()
        {
            objectIDToNameMap.Clear();
            objectIDToTypeMap.Clear();

            using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjects] @RunList", sqlConnection))
            {
                objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader objectsList = objectsCommand.ExecuteReader())
                {
                    while (objectsList.Read())
                    {
                        int objID = objectsList.GetInt32(0);
                        string objName = objectsList.GetString(1);
                        string objType = objectsList.IsDBNull(3) ? objectsList.GetString(2) : objectsList.GetString(3);
                        objectIDToNameMap.Add(objID, objName);
                        objectIDToTypeMap.Add(objID, objType);
                    }
                }
            }
        }

19 Source : MainFrom.cs
with Microsoft Public License
from keif888

private void InitializeLineageMap()
        {
            using (SqlCommand lineageMapCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveLineageMap] @RunList", sqlConnection))
            {
                lineageMapCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
                using (SqlDataReader lineageMapReader = lineageMapCommand.ExecuteReader())
                {
                    while (lineageMapReader.Read())
                    {
                        int from = lineageMapReader.GetInt32(0);
                        int to = lineageMapReader.GetInt32(1);
                        string depType = lineageMapReader.GetString(2);
                        if (depType == "Map" || depType == "Use")
                        {
                            dependencyGraph.AddDependency(from, to, depType);
                        }
                        else if (depType == "Containment")
                        {
                            dependencyGraph.AddContainment(from, to);
                        }
                    }
                }
            }
        }

19 Source : getlinked.cs
with GNU General Public License v3.0
from klezVirus

public void Execute(Dictionary<string, string> arguments)
        {
            string connectInfo = "";
            bool verbose;

            ArgumentSet argumentSet;

            try
            {
                argumentSet = ArgumentSet.FromDictionary(
                    arguments,
                    new List<string>() {
                        "/server"
                    });
            }
            catch (Exception e)
            {
                Console.WriteLine($"[x] Error: {e.Message}");
                return;
            }

            argumentSet.GetExtraBool("/verbose", out verbose);


            SqlConnection connection;
            SQLExecutor.ConnectionInfo(arguments, argumentSet.connectserver, argumentSet.database, argumentSet.sqlauth, out connectInfo);
            if (String.IsNullOrEmpty(connectInfo))
            {
                return;
            }
            if (!SQLExecutor.Authenticate(connectInfo, out connection))
            {
                return;
            }

            // I am confused about why it is necessary to perform this step as a separate procedure
            // But it seems in-line impersonation doesn't work properly
            if (!String.IsNullOrEmpty(argumentSet.impersonate))
            {
                Console.WriteLine("[*] Attempting impersonation as {0}", argumentSet.impersonate);
                SQLExecutor.ExecuteProcedure(connection, "", argumentSet.impersonate);
            }

            if (!verbose && String.IsNullOrEmpty(argumentSet.target))
            {
                string procedure = "EXECUTE sp_linkedservers;";
                procedure = SQLExecutor.PrepareSimpleStatement(procedure, argumentSet.impersonate);

                SqlCommand command = new SqlCommand(procedure, connection);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("[*] Linked SQL server: " + reader[0]);
                    }
                }
            }
            else
            {
                string query = @"SELECT 
    name AS 'SQL Server', 
    is_linked AS 'Linked', 
    is_remote_login_enabled AS 'Remote Login', 
    is_data_access_enabled AS 'Data Access', 
    is_rpc_out_enabled AS 'RPC Out'
FROM sys.servers;
";
                if (String.IsNullOrEmpty(argumentSet.target) && String.IsNullOrEmpty(argumentSet.intermediate))
                {
                    SQLExecutor.ExecuteQuery(
                        connection,
                        query,
                        argumentSet.impersonate,
                        true
                        );
                }
                else if (String.IsNullOrEmpty(argumentSet.intermediate))
                {
                    SQLExecutor.ExecuteLinkedQuery(
                        connection,
                        query,
                        argumentSet.target,
                        argumentSet.impersonate,
                        argumentSet.impersonate_linked,
                        true
                        );
                }
                else
                {
                    SQLExecutor.ExecuteDoublyLinkedQuery(
                        connection,
                        query,
                        argumentSet.target,
                        argumentSet.intermediate,
                        argumentSet.impersonate,
                        argumentSet.impersonate_linked,
                        argumentSet.impersonate_intermediate,
                        true
                        );
                }
            }
            connection.Close();
        }

19 Source : SQLExecutor.cs
with GNU General Public License v3.0
from klezVirus

public static void TrySqlExecute(SqlConnection connection, string procedure, bool separator)
        {
            
            try
            {
                SqlCommand command = new SqlCommand(procedure, connection);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    try
                    {

                        while (reader.Read())
                        {
                            if (reader.FieldCount == 0) { break; }
                            else
                            {
                                int written = 0;
                                string msg = String.Empty;

                                for (var i = 0; i < reader.FieldCount; i++)
                                {
                                    string name = reader.GetName(i);

                                    string value;
                                    if (reader.GetFieldType(i) == typeof(bool))
                                    {
                                        value = reader.GetBoolean(i).ToString();
                                    }
                                    else if (reader.GetFieldType(i) == typeof(Int32))
                                    {
                                        value = reader.GetInt32(i).ToString();
                                    }
                                    else if (reader.GetFieldType(i) == typeof(String))
                                    {
                                        value = reader.GetString(i);
                                    }
                                    else
                                    {
                                        value = reader.GetValue(i).ToString();
                                    }
                                    msg = (written == 0) ?
                                        String.Format("[+] {0:-15}: {1}", name, value) :
                                        String.Format("    {0:-15}: {1}", name, value);
                                    Console.WriteLine(msg);
                                    written++;
                                }
                                if (written > 1 && separator) {
                                    Console.WriteLine("   {0}", new String('-', 40));
                                }
                                written = 0;
                            }
                        }

                    }
                    catch (Exception e){ Console.WriteLine(e); }
                }
            }
            catch (SqlException e)
            {
                if (e.Message.Contains("Execution Timeout Expired"))
                {
                    Console.WriteLine("[*] The SQL Query hit the timeout. If you were executing a reverse shell, this is normal");
                    connection.Open();
                }
                else if (e.Message.Contains("Could not find server"))
                {
                    throw new Exception("Couldn't connect to linked server. Check the spelling");
                }
                else
                {
                    Console.WriteLine($"[-] Exception: {e.Message}");
                    return;
                }
            }
        }

19 Source : getdbuser.cs
with GNU General Public License v3.0
from klezVirus

public void Execute(Dictionary<string, string> arguments)
        {
            string connectInfo;
            bool permissions;

            ArgumentSet argumentSet;
            try
            {
                argumentSet = ArgumentSet.FromDictionary(
                    arguments,
                    new List<string>() {
                        "/server"
                    });
            }
            catch (Exception e)
            {
                Console.WriteLine($"[x] Error: {e.Message}");
                return;
            }

            argumentSet.GetExtraBool("/permissions", out permissions);

            SqlConnection connection;
            SQLExecutor.ConnectionInfo(arguments, argumentSet.connectserver, argumentSet.database, argumentSet.sqlauth, out connectInfo);
            if (String.IsNullOrEmpty(connectInfo))
            {
                return;
            }
            if (!SQLExecutor.Authenticate(connectInfo, out connection))
            {
                return;
            }

            // I am confused about why it is necessary to perform this step as a separate procedure
            // But it seems in-line impersonation doesn't work properly
            if (!String.IsNullOrEmpty(argumentSet.impersonate))
            {
                Console.WriteLine("[*] Attempting impersonation as {0}", argumentSet.impersonate);
                SQLExecutor.ExecuteProcedure(connection, "", argumentSet.impersonate);
            }

            var queries = new List<string>();
            
            queries.Add("SELECT SYSTEM_USER as 'Logged in as', CURRENT_USER as 'Mapped as';");
            queries.Add("SELECT IS_SRVROLEMEMBER('public') as 'Public role';");
            queries.Add("SELECT IS_SRVROLEMEMBER('sysadmin') as 'Sysadmin role';");

            foreach (string query in queries)
            {
                if (String.IsNullOrEmpty(argumentSet.target) && String.IsNullOrEmpty(argumentSet.intermediate))
                {
                    SQLExecutor.ExecuteQuery(
                        connection, 
                        query,
                        argumentSet.impersonate);
                }
                else if (String.IsNullOrEmpty(argumentSet.intermediate))
                {
                    SQLExecutor.ExecuteLinkedQuery(
                        connection, 
                        query, 
                        argumentSet.target, 
                        argumentSet.impersonate, 
                        argumentSet.impersonate_linked
                        );
                }
                else
                {
                    SQLExecutor.ExecuteDoublyLinkedQuery(
                        connection, 
                        query, 
                        argumentSet.target, 
                        argumentSet.intermediate, 
                        argumentSet.impersonate, 
                        argumentSet.impersonate_linked, 
                        argumentSet.impersonate_intermediate
                        );
                }
            }

            /* 
             The following query is quite difficult to wrap within my SQLExecutor, mostly due to the fact I implemented the output in tabular format
             */

            if (permissions)
            {
                Console.WriteLine("[*] Checking user permissions..");

                string query = @"SELECT *
    FROM(SELECT 'OBJECT' AS enreplacedy_clreplaced,
                NAME,
                subenreplacedy_name,
                permission_name
        FROM   sys.objects
                CROSS APPLY fn_my_permissions(QUOTENAME(NAME), 'OBJECT') a
        UNION ALL
        SELECT 'DATABASE' AS enreplacedy_clreplaced,
                NAME,
                subenreplacedy_name,
                permission_name
        FROM   sys.databases
                CROSS APPLY fn_my_permissions(QUOTENAME(NAME), 'DATABASE') a
        UNION ALL
        SELECT 'SERVER'     AS enreplacedy_clreplaced,
                @@SERVERNAME AS NAME,
                subenreplacedy_name,
                permission_name
        FROM   fn_my_permissions(NULL, 'SERVER')) p
    ORDER  BY enreplacedy_clreplaced,
            NAME";

                if (!String.IsNullOrEmpty(argumentSet.intermediate) && !String.IsNullOrEmpty(argumentSet.target))
                {
                    query = SQLExecutor.PrepareDoublyLinkedQuery(
                        query, 
                        argumentSet.target, 
                        argumentSet.intermediate, 
                        argumentSet.impersonate, 
                        argumentSet.impersonate_linked, 
                        argumentSet.impersonate_intermediate
                        );
                }
                else if (!String.IsNullOrEmpty(argumentSet.target))
                {
                    query = SQLExecutor.PrepareLinkedQuery(
                        query, 
                        argumentSet.target, 
                        argumentSet.impersonate, 
                        argumentSet.impersonate_linked
                        );
                }

                SqlCommand command = new SqlCommand(query, connection);

                TablePrinter.PrintRow("ENreplacedY", "NAME", "SUBENreplacedY", "PERMISSION");
                TablePrinter.PrintLine();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TablePrinter.PrintRow(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3));
                    }
                }
                TablePrinter.PrintLine();
            }
            connection.Close();

        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetLinkedServers()
        {
            List<string> linkedSrvs = new List<string>();
            string query = "EXEC sp_linkedservers;";
            SqlCommand command = new SqlCommand(query, sqlConn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                LinkedSqlServers.Add(reader[0].ToString());
                Console.WriteLine("::: Linked SQL Server {0} :::", reader[0]);
                linkedSrvs.Add(reader[0].ToString());

            }
            linkedSrvs.Add("DC01");
            linkedSrvs.Add("dc01");
            LinkedSqlServers = linkedSrvs;
            reader.Close();
            return;
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void LinkedServerEnumeration()
        {
            Console.WriteLine("::: Enumerating Linked Servers :::");
            string q;
            SqlCommand command;
            SqlDataReader reader;
            // check who you are running as 
            try
            {
                foreach (string srv in LinkedSqlServers)
                {
                    q = String.Format("select r from openquery(\"{0}\",'select SYSTEM_USER as r');", srv);
                    command = new SqlCommand(q, sqlConn);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine(" :::Executing as {0} on {1} :::", reader[0], srv);
                    }
                    reader.Close();
                }
            } catch 
            {
                Console.Write("");
            }
            // check role on server "select * from openquery(`"$currentSrv`",'SELECT IS_SRVROLEMEMBER(''sysadmin'')');"
            try
            {
                foreach (string srv in LinkedSqlServers)
                {
                    q = String.Format("select * from openquery(\"{0}\",'select IS_SRVROLEMEMBER(''sysadmin'')');", srv);
                    command = new SqlCommand(q, sqlConn);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader[0].ToString().Contains("1"))
                        {
                            Console.WriteLine("::: You are sysadmin on {0} :::", srv);
                        } else
                        {
                            Console.WriteLine("->> {0}", reader[0].ToString());
                        }
                    }
                    reader.Close();
                }
            }
            catch
            {
                Console.Write("");
            }
            // check if links have outbound RPC enabled to perform xp CMD over link
            //"EXEC ('sp_configure ''show advanced options''') AT $currentSrv"
            // SELECT is_rpc_out_enabled FROM sys.servers WHERE name = 'APPSRV01'
            try
            {
                foreach (string srv in LinkedSqlServers)
                {
                    q = String.Format("SELECT is_rpc_out_enabled FROM sys.servers WHERE name = '{0}'", srv);
                    command = new SqlCommand(q, sqlConn);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader[0].ToString() == "False")
                        {
                            Console.WriteLine("::: rpc out disabled on {0} :::", srv);
                        }
                        else
                        {
                            Console.WriteLine("::: rpc out enabled on {0} :::", srv);
                        }
                    }
                    reader.Close();
                }
            }
            catch
            {
                Console.Write("");

            }
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public bool PerformQuery(string cmd)
        {
            SqlCommand command;
            SqlDataReader reader;
            try
            {
                command = new SqlCommand(cmd, sqlConn);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    // if multiple columns print them all out
                    if (reader.FieldCount > 1)
                    {
                        for (int x = 1; x < reader.FieldCount; x++)
                        {
                            Console.WriteLine("{0} -> {1}", reader.GetName(x).ToString(), reader.GetValue(x));
                        }
                    } else
                    {
                        Console.WriteLine("{0} -> {1}", reader.GetName(0).ToString(), reader.GetValue(0));
                    }
                }
                reader.Close();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }

        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetCurrentDb()
        {
            string query = "SELECT db_name();";
            SqlCommand command = new SqlCommand(query, sqlConn);
            SqlDataReader commandReader = command.ExecuteReader();
            commandReader.Read();
            Console.WriteLine("::: Current DB {0} :::", commandReader[0]);
            startingDb = commandReader[0].ToString();
            commandReader.Close();
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetCurrentDbUser()
        {
            //Send a command to get current user
            string queryLogin = "SELECT SYSTEM_USER;";
            SqlCommand command = new SqlCommand(queryLogin, sqlConn);
            SqlDataReader commandReader = command.ExecuteReader();
            commandReader.Read();
            Console.WriteLine("::: Logged in as user {0} :::", commandReader[0]);
            commandReader.Close();
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetCurrentDbUserContext()
        {
            //Send a command to get current user
            string queryLogin = "SELECT USER_NAME();";
            SqlCommand command = new SqlCommand(queryLogin, sqlConn);
            SqlDataReader commandReader = command.ExecuteReader();
            commandReader.Read();
            Console.WriteLine("::: Executing in context of user {0} :::", commandReader[0]);
            commandReader.Close();
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetLoggedInUser()
        {
            //Send a command to get current user
            string queryLogin = "SELECT CURRENT_USER;";
            SqlCommand command = new SqlCommand(queryLogin, sqlConn);
            SqlDataReader commandReader = command.ExecuteReader();
            commandReader.Read();
            Console.WriteLine("::: Logged in as {0} :::", commandReader[0]);
            commandReader.Close();
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public bool CheckRole(string roleToCheck)
        {
            // send command to get current user role membership
            string queryRole = "SELECT IS_SRVROLEMEMBER('" + roleToCheck + "');";
            SqlCommand command = new SqlCommand(queryRole, sqlConn);
            SqlDataReader commandReader = command.ExecuteReader();
            commandReader.Read();
            Int32 role = Int32.Parse(commandReader[0].ToString());
            if (role == 1)
            {
                Console.WriteLine("::: User is member of {0} role :::", roleToCheck);
                commandReader.Close();
                return true;
            }
            else
            {
                Console.WriteLine("::: User is NOT member of {0} role :::", roleToCheck);
                commandReader.Close();
                return false;
            }
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void GetImpersonatableUsers()
        {
            String query = "SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE';";
            SqlCommand command = new SqlCommand(query, sqlConn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read() == true)
                {
                    Console.WriteLine("::: Logins that can be impersonated: {0} :::", reader[0]);
                    LoginsCanBeImpersonated.Add(reader[0].ToString());
                }
            reader.Close();
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void CheckXpShellEnabled()
        {
            // check for show advanced
            Console.WriteLine("::: Checking if xpCMDShell is enabled :::");
            string res = "sp_configure 'Show Advanced Options'";
            SqlCommand command = new SqlCommand(res, sqlConn);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read() == true)
            {
                if (reader.GetValue(3).ToString() == "0")
                {
                    Console.WriteLine("::: Show Advanced Not Enabled :::");
                } else {
                    Console.WriteLine("::: Show Advanced Enabled :::");
                }
            }
            reader.Close();
            // check for xp cmd shell
            try
            {
                res = "sp_configure 'xp_cmdshell'";
                command = new SqlCommand(res, sqlConn);
                reader = command.ExecuteReader();
                while (reader.Read() == true)
                {
                    if (reader.GetValue(3).ToString() == "0")
                    {
                        Console.WriteLine("::: xpCMDShell Not Enabled :::");
                    }
                    else
                    {
                        Console.WriteLine("::: xpCMDShell Enabled :::");
                    }
                }
                reader.Close();
                return;
            } catch
            {
                Console.WriteLine("::: Need to enable advanced options first to enable cmd shell :::");
                reader.Close();
                return;

            }
        }

19 Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71

public void ExecutionViaCmdShell(string cmd)
        {
            try
            {
                Console.WriteLine("::: You must have sysadmin role membership for this method (via impersonation or other means) :::");
                string enable_xpcmd = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
                string execCmd = "EXEC xp_cmdshell " + cmd;
                // enable xpcmd
                SqlCommand command = new SqlCommand(enable_xpcmd, sqlConn);
                SqlDataReader reader = command.ExecuteReader();
                reader.Close();
                // exec command
                command = new SqlCommand(execCmd, sqlConn);
                reader = command.ExecuteReader();
                reader.Read();
                Console.WriteLine("::: cmd res -> {0} :::", reader[0]);
                reader.Close();
                // revert settings?
            }
            catch (Exception e)
            {
                Console.WriteLine("Failed! Try impersonating a user before running this function");

            }
        }

See More Examples