C# and SQL problem



  • ok, I'm absolutely lost when it comes to .NET.  I used to do everything in ASP and now I'm trying to make the painful switch over.

    I'm making a site that has a table on it that essentially will show 2 "things".  "New things" and "total things".  New being a count of "things" since a given date.  My problem is that there will be N columns where N is the unique name of individual departments that are located in a table in a different database from the COUNT I need to do on the "things" ( that make up the departments).

    To summarize: the top row will have column names from table_old in database_old, and the row below it will have the COUNT of people in those departments that will take place on table_new in database_new.  My problem arises when I have to do operations comparing values from each database.  In ASP I know how to do this, in ASP.NET I haven't a clue.

    The only way I know how to do this is to bind the department names to 1 datalist, then immediately place the datalist of the counts right below it - basically faking 1 datalist with 2.  This can't be the way to do it(I hope) and some pointers would be greatly appreciated.  BTW, it's C#.

    Thanks



  • In case any of you were wondering - and I know all of you are, I haven't solved this yet.  Don't be shy.


  • ♿ (Parody)

    I didn't quite understand.

    It sounds pretty strange that you're:
     - putting departments as columns.
     - trying to combining two datasets in the front end

    Perhaps you want to do all this as a VIEW?



  •         <FONT color=#ff0000>Dept. 1</FONT> <FONT color=#ff0000>Dept. 2</FONT> <FONT color=#ff0000>Dept. 3</FONT> <FONT color=#ff0000>Dept. 4</FONT>
    New Employees This Month 7 1 19 6
    Total Employees 35 22 61 10

     

    The red text is found in database_new.  The employee names that will be COUNTed are in database_old.  The problem is making this all one fluid table, like I could do in ASP with the  spaghetti code html/asp thrown together and looping.  With ASP.NET the solution doesn't follow as easily for me.

     

    Thanks



  • good.  the red text is the top row - "Dept. 1", etc.


  • ♿ (Parody)

    OK, I think I get it now. First things first, you need to rotate your way of thinking. You're using rows as columns and visa versa.

    You're query (to the OLD_DB) should be fairly simple to do ...

    SELECT
      Dept_Name,
      New_Emp_Ct = (SELECT COUNT(*) FROM Emps 
    	               WHERE Hire_Dt > DATEADD(month, -1, GETDATE())
    								   AND Dept_Cd = Depts.Dept_Cd),
      Ttl_Emp_ct = (SELECT COUNT(*) FROM Emps 
    	               WHERE Dept_Cd = Depts.Dept_Cd)
    FROM NEW_DB..Depts

    I'm assuming you can do a cross-db join (hence the SQL Server notation for this). If not, this is a fairly trivial thing to translate a Dept_Cd into a Dept_Name in the front end.

    Now your next step is to build your in-memory DataTable and bind it (syntax from memory, you get the idea though):

    Dim resultTable As New DataTable
    resultTable.Columns.Add("")
    resultTable.Rows.Add(new Array("New Employees This Month"))
    resultTable.Rows.Add(new Array("Total Employees"))
    

    Dim sql As String = "(SQL Query Above)"
    Dim conn As New SqlConnection("{ConnectionString}")
    Dim cmd As New SqlCommand(conn, sql)
    conn.Open
    Dim dr As SqlDataReader = cmd.ExecuteDataReader()
    Do While dr.Read
    Dim colName as string = CStr(dr("Dept_Name"))
    'colName = DepartmentCodeToName(colName)
    dr.Columns.Add(colName)
    dr.Rows(0).Columns(colName).Value = CStr(dr("New_Emp_Ct"))
    dr.Rows(1).Columns(colName).Value = CStr(dr("Ttl_Emp_ct"))
    Loop
    conn.Close
    cmd.Dispose
    conn.Dispose

    ReportDataGrid.DataSource = resultTable
    ReportDataGrid()

    is that what you were looking for?



  • Thanks for the help Alex.  Most of it makes sense, but I'm having a big problem with the cross-db part.  One table is in a database on an entirely different server than the other table.

    So how do I say  "select name from table_1 located at 12.345.67.890 and select departments from table_2 located at 09.876.54.321" and then do operations on them??



  • ok, after a bunch of reading it appears that you can't do this very easily.  a friend said something about configuring the SQL Servers to link to eachother, but since the servers are hosted somewhere else it doesn't look like that's gonna happen.

    if someone can give me a confirmation that interfacing between 2 tables on 2 separate dbs on 2 separate servers isn't practical (or possible?) then I'd appreciate it.

    Thanks again for the help Alex.



  • i dont know man.i dont do this sort of thing myself but it seems to me you're going to have to use voodoo. if it were me however, i'd connect to each database one at a time and cache the data locally.then once everything is gathered up, cast magic missile. i suppose the best thing would be to code whatever it is you're trying to do from scratch and apply it to the data, or the data to it, whatever. i'm lost already. or you could just do a wtf, copy the table from one server to a temp table on the other, do your thing, then delete the temp table after you have your result. aside from that, i cant really think of anything else since i'm not entirely certain of what it is you're attempting to do because i just woke up and probably wont be able to think straight for another hour or two.

     

    so um... disreguard that, i'm gonna go find some socks.[{]



  • Yeah, you need to link the servers together to do cross-server, cross-database joins.  sp_addlinkedserver (MS SQL 2K)  However, it's a royal PITA for many many reasons - and in any case, if your other server is hosted elsewhere then you'll need a SQL port open through the firewall which is a Very Bad Idea.  Of course, you apparently can query this server now?

    Could you retrieve them separately and then do something client- or web-server-side to "join" them?  It looks like one query populates the first row and another query populates the second.


  • ♿ (Parody)

    @ItsAllGeekToMe said:

    Thanks for the help Alex.  Most of it makes sense, but I'm having a big problem with the cross-db part.

    Since servers are not at the same locaion, this doesn't make sense to try. Usually, they are. But this doesn't change the solution much -- just simply have a function that translates Dept_Cd to Dept_Nme.  This function would retreive the data from the NEW_DB and store the _Cd and _Nme in a StringDictionary.

    The query would need a very slight modification ...

    SELECT
      Dept_Cd,
      New_Emp_Ct = (SELECT COUNT(*) FROM Emps 
    	               WHERE Hire_Dt > DATEADD(month, -1, GETDATE())
    								   AND Dept_Cd = Depts.Dept_Cd),
      Ttl_Emp_ct = (SELECT COUNT(*) FROM Emps 
    	               WHERE Dept_Cd = Depts.Dept_Cd)
    FROM (SELECT DISTINCT Dept_Cd FROM Emps) Depts


  • If the departments are absolutely fixed, then a SQL solution might be possible or maybe even simple.  However, before SQL 2005, PIVOT isn't a native database operation.  It either has to be hacked with dynamic SQL (and has to fit in a varchar(8000)), or done client-side.

     

    In this case, it is probably best to do it in data-access code.  

    First retrieve the department list and make a DataTable with the proper DataColumns.

    Next, retrieve each departments employees, sorted by the PK of the employee from each server.  Walk down each result set simultaneously.  Where a result shows up in one set, but not the other, you've found a new employee in that dept, add one to a counter.  Fill in the dataset data for the department and continue with the other departments.

    Alternately, you could do this step with an ad-hoc multi-server query with the OPENROWSET function.  It'll be a little ugly, but then you won't have to pull the entire resultset from one of the servers (the OPENROWSET will pull the whole resultset from one server to the other and do the processing there).  It will look something like this:

    SELECT COUNT(*)
    FROM DeptMember
    WHERE EmpID NOT IN (SELECT EmpID FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
         'SELECT EmpID FROM DeptMembers WHERE DeptID=7') AS OtherServerResults
    AND DeptID = 7

    No server linking necessary, so you could do this on a server where you have no administrative rights.  Makes you a little more frightened about the possibilities of a SQL Injection attack on a web app, doesn't it?


Log in to reply