Hi everyone,
I€™ve got a few Script Task in my dtsx. Up to the moment I don€™t need have generic functions but I was wondering if it is possible to use a such functions.
I attach you a sample of I say you:
Script Task1
General()
ScriptTask2
Stuff
WritingLog()
ScriptTask3
Stuff
Call General()
Thanks for your thoughts,
Hi-
I have a number of tables that contain data that we want to concatenate as a part of our reporting processes. Instead of building a whole series of functions that each concatenate data from only one table, I want to create a function (or functions that work together) to concatenate data from ANY specified table, since the logic is identical.
The basic logic is:
Code Snippet
DECLARE @x varchar(max)
DECLARE @theReturn varchar(max)
DECLARE theValues CURSOR FOR
SELECT DISTINCT TOP 100 PERCENT <col name to concatenate>
FROM <the table in question>
WHERE <the identity column in the table> = <a specific value>
ORDER BY <col name to concatenate>
SET @x = ''
SET @theReturn = ''
OPEN theValues
FETCH NEXT FROM theValues INTO @x
WHILE @@FETCH_STATUS = 0
BEGIN
SET @theReturn = @theReturn + '; ' + @x
FETCH NEXT FROM theValues INTO @x
END
CLOSE theValues
DEALLOCATE theValues
SET @theReturn = LEFT(@theReturn, Len(@theReturn) - 2)
RETURN @theReturn
The problem I am running into is how to create the "generic" or "abstract" version of the SELECT statement in the cursor. I can certainly pass in the values surrounded by < and > as arguments, but I cannot figure out how to insert them into the SQL and make it work. I can't create a text string and then use the EXEC statement, because in a scalar function SQL Server won't allow it.
Any thoughts, work-arounds, or solutions?
Thanks-
Input Columns
Jan,Feb,March,April,May,June,July......December
All these columns Have Boolean 'Y' Or 'N' Data
Here is what i want as output
Example
Jan 'Y'
Feb 'Y'
March'Y'
April'Y'
May 'N'
June 'Y'
July 'Y'
..
..
..
Dec 'Y'
Output should be:
Start date:jan/year
End date:April/year
Again the start date:May/year
End Date: dec/year
So basically For the same person i should have two recods...
Text file to sql server 2005
How can i achieve this
Please let me know
Hello experts,
Is there a possibility that one script task have the access to another script task?
My first script task has a function I would like to use at my second script task.
Hope to hear you soon!
Best regards,
Alex
In general (I know.. lots of factors) but in general will adding more RAM to a SQL box help the performance? I'm working w/ 256MB right now, and deciding whether to add 256MB or 512MB? Any advice
Thanks and sorry so generic but this is a vendored app and they know of design issues but for now that can't be changed.
Hi guys
I have a simple problem:
I want to be able to query with a start date(@start) and then calculate 1 year from there. I figured since the date is of type integer just add 364 and it will give me the day before 1 year.
So if its 1998-05-01, Id get 1999-04-31.
Code:
DECLARE @FinStart DATETIME
DECLARE @FinEndDATETIME
SET @FinStart = '1998-05-01' --is of type int .:. +365 is adding 1 year
SET @FinEnd = @FinStart + 364
SELECT @FinStart
SELECT @FinEnd
Problem, this doesnt cater for leap years.
Any suggestions?
The help is always appreciated!
Justin
Here's the scenario:
whenever a change (insert/update/delete) has occured in any of the tables, it needs to be logged by adding the changed table's name, and changed columns' names (if any) into the special ChangeLog table.
So, is it possible for me to write a generic trigger that would get the name of the table it just executed upon and names of every column affected by the UPDATE and then insert those into the Log table?
If this is not possible, how do you suggest it should be done?
I'm familiar with standard SQL but T-SQL is completely new to me, so please bear with me if my question makes no sense...
Thanks!
Hallo all :
How to disappear the generic bar in the report, or to disappear some options (example export). I speak about the bar which in the top of the report or there are the numbers of pages.
Thank's.
Hello,I have a DataSet which results from accessing an SQL database.Each row is an Article and has various columns like Title, Description, etc.I have created a Generic List as follows:Dim Articles As Generic.List(Of Article)Article is a class with various properties. Each property is the same as each column in the dataset.I want to fill the Generic List with the data from my DataSet.How can I do this?Thanks,Miguel
View 1 Replies View RelatedHello,
Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way.
Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties.
This way, I design my database once, and customize it to fit any type of applications I need.
Is there something like that or just dreaming?
thanks
I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.
I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.
For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @tablename, @code, @desc, and then create a new record in the table specified by @tablename.
I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.
Any help would be greatly appreciated!
I am attempting to create a generic stored procedure in SQL Server 6.5 which returns a record count of a specific table passed in as a parameter. The stored procedure looks like this"
CREATE PROCEDURE asp_GetRecordCount
@tablename varchar(30), @recordcount integer OUTPUT
AS
/* Generic stored procedure which returns
number of records in a table.
*/
SELECT @recordcount = COUNT(*) FROM @tablename
RETURN
GO
The problem is that when I save the stored procedure, SQL Server balks at the FROM @tablename entry. Any suggestions? Thanks.
Hi,
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
My frontend scripting language is php
Thanks d'advance
Celia
I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:
T_Member:
MemberNo, FirstName, LastName, (etc)
T_Fixture:
FixtureNo, SeasonNo, MatchDate, (etc)
T_Season:
SeasoNo, SeasonDescription, SeasonStartDate, SeasonEndDate, CurrentSeason
T_FixtureTeam:
FixtureNo, MemberNo, 2Points, 3Points,Fouls,FreeShotAttempts,FreeShotsHit,Assists
Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.
Ready?
The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to:
- Find all fixtures in that season
- Find all members who played in those fixtures
- Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches
- Sort the resulting data
I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.
TIA
I'm receiving "a generic error occurred in gdi+" processing error while
running a preview of my report inside visual studio. When this report
is opened by a client, their browser will just hang (Not Responding).
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
Hope anyone can help. Thanks!
I have to make a store procedure for every table like
create proc ins_all (@alltable varchar(100),@allfields nvarchar(1024),@allvalues nvarchar(1024))
as
insert into @alltable (@allfields) values (@allvalues)
this procedure generates error, Can anyone give a good idea for the required procedure
Thanks in advance
Hi,
We want our database to be unicode complaint and i need a generic collation that should do the work irrespective of the fact what data is entered in the database. Can someone suggest me any generic collation for that.
Thanks and regards
Salil
Hey I'm using this DAL to populate a generic list called assetDetailListHowever I'm adding a 2nd result set to my stored proc, protected void GenerateAssetDetails<T>(DbDataReader dataReader, ref List<AssetDetails> assetDetailList) { while (dataReader.Read()) { // Create a new culture AssetDetails assetDetail = new AssetDetails(); // Set the culture properties assetDetail.FileName = dataReader.GetString(dataReader.GetOrdinal("filename")); assetDetail.Title = dataReader.GetString(dataReader.GetOrdinal("title")); assetDetail.AltTag = dataReader.GetString(dataReader.GetOrdinal("alt_tag")); assetDetailList.Add(assetDetail); dataReader.NextResult();// this is the 2nd result set AssetContent assetContent = new AssetContent(); assetContent.content_id = dataReader.GetInt32(dataReader.GetOrdinal("content_id")); how do I creatre another list .Add(assetContent); } // Close and dispose of the reader dataReader.Close(); dataReader.Dispose(); } #endregion }
View 2 Replies View RelatedHi,
This is really a Visual Studio 2005 Report Designer problem but I didn't see a good place to post this question in the Visual Studion forum.
On some reports when looking at the Data tab the Generic Query Designer is disabled. Anybody know why? The report runs. Other reports have it enabled.
I am using Visual Studio 2005 sp1.
Thanks,
Darren
Here is my situation. I have a fairly large query that I am working on in visual studio (data tab). I am at the point where the program will NOT let me type (add SQL code) anymore. I found that clicking the Generic Query Designer (turning the query builder on) button switches the format then allows me to add more code. Well, this solves the running out of room issue, BUT now it does something to my code and it will not process it. Prior to clicking the Generic Query Designer button, everything worked w/ no errors. I did not change anything, but simply pressed the Generic Query Designer button (turning the query builder on) and now it dose not work now. Just for kicks, I have pasted my working origional code into SSMS, added code and it executed just fine in SSMS. But then I pasted back into visual studio and it gave me the same errors?
Has anyone experienced this? Is there a maximum for lines of code? Why does the Generic Query Designer (having query builder on) change my code? All comments are welcome!
-Smylie
I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.
1. Have a generic table which would have table name and description and whatever else that was required.
2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.
This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.
Not sure if I am making sense but I hope someone understands the attempt.
thanks
Hello every body.
First of all sorry for my bad english but I'm french ;-)
My internship consits into making a generic table exporter (with a table list). Export into csv files.
I have tried 2 solutions :
1 - Create a DTS with a Dynamic Properties Task. The problem is I can easily change the destination file but when I change the table source I don't know how to remap the transformations between source and destination (do you see what I mean ?). Any idea ?
2 - Use the bcp command. Very simple but how to do when tables contains the separator caracter ? for example : If a table line is "toto" | "I am , very happy" --> the csv file will look like this : toto, I am , very happy --> problem to get back the data ( to much comma ).
Does someone has a solution ?
Last point is how to export the table structure ? For the moment, using the table structure, I generate an sql query which creates a table (I write this query in a file). Isn't there any "cleaner" solution ?
Thanks in advance and have a nice day all
I want to use ONE stored procedure to update/insert about 300 tables in a database. I hoped this code would work but it gives me an error:
ALTER PROCEDURE [dbo].[InsertRowsTick]
@tableName varchar,
@bid float,
@ask float,
@last float,
@volume int,
@dateTimed DateTime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tableName (
bid,
ask,
last,
volume,
dateTimed)
Values (
@bid,
@ask,
@last,
@volume,
@dateTimed)
END
When I (1) commented out @tableName parameter and (2) instead of INSERT INTO tableName wrote: INSERT INTO <realtableName> the code (C#) executed and the table was filled.
How can I use a generic table name as a parameter?
Thanks.
I need the SQL to calculate the duration between a day and time, no specific date.Â
For example:
Sun 00:00 > Mon 08:00 = 5.67 days
So there is no date, it's just the general duration (in days), from a specific day and time to a specific day and time.
Hello,
I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?
It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
Thanks,
John
I've been studying this tutorial over here at: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/databases.aspx#updatedelete
but I can't seem to get my gridview working. I want to be able to edit the fields and also delete an entire row from the table if needed.
I hard coded in the update and delete commands into my objectdatasource but I still get the error message. Also, I can't even refresh the aspx page:
<%@ Page Language="VB" MasterPageFile="~/templates/admin.master" AutoEventWireup="false" CodeFile="article_comments.aspx.vb" Inherits="admin_Default2" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="PageHeading" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="PageContents" Runat="Server">
`<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" Width="536px" DataKeyNames="ratingID">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ratingID" HeaderText="ratingID" InsertVisible="False"
ReadOnly="True" SortExpression="ratingID" />
<asp:BoundField DataField="rating" HeaderText="rating" SortExpression="rating" />
<asp:BoundField DataField="ip" HeaderText="ip" SortExpression="ip" />
<asp:BoundField DataField="itemID" HeaderText="itemID" SortExpression="itemID" />
<asp:BoundField DataField="comment" HeaderText="comment" SortExpression="comment" />
<asp:CheckBoxField DataField="active" HeaderText="active" SortExpression="active" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetRatings" TypeName="articlesTableAdapters.tblArticleRatingTableAdapter" DataObjectTypeName="articles+tblArticleRatingDataTable" DeleteMethod="DELETE FROM [tblArticleRating] WHERE [ratingID] = @ratingID" UpdateMethod="UPDATE [tblArticleRating] SET [ratingID] = @ratingID, [rating] = @rating, [ip] = @ip, [itemID] = @itemID, [comment] = @comment WHERE [ratingID] = @ratingID">
<UpdateParameters>
<asp:Parameter Name="dataTable" Type="Object" />
<asp:Parameter Name="itemID" Type="Int32" />
</UpdateParameters></asp:ObjectDataSource>
</asp:Content>
>
Greetings to all database professionals and laymen,Let us make a bold assumption that we have developed a softwaretool for the SQL Server environment which simply acts as an interfacebetween an end-user in an organization and the database, throughthe exclusive use of stored procedures which are authored by theorganization or by software developers.All development work at the application software level may therebybe conducted within SQL, by the development of TSQL storedprocedures and their coordination across an organization.The question then needs to be asked what are the advantages of thisarrangement and what are the disadvantages. I would appreciateyour comments here, as it is difficult for folk heavily involved (eg: me)in something to obtain objective opinion otherwise.Potentially it is possible to construct an entire database applicationsoftware package using only TSQL stored procedures and thistool. When a database backup is conducted not only the data isbacked up but also all the "program development" suite, in theform of stored procedures (and their scheduling, for example).One of the advantages from my perspective is that this arrangementimplies the possibility that all software external to the database maybe made redundant (except this tool), and along with it, all the otherredundancies of managing application development (life-cycles) withinthe database and then coordinating these changes with softwareexternal the database (particulary loading it to the client environment)You see, I believe that (for example) any and every application writtenin VB, C, etc, etc, etc (external to rdbms) expressly for database software(ie: rdbms software such as SQL Server) involves a redundancy of datadefinitions required ---- once within the VB and once within the db.I would appreciate any feedback on any of the above issues, andwish everyone the compliments of the season.Pete BrownFalls CreekNSWOz~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~EDITOR:BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}Thematic Threading: Publications of Peace and Of Great SoulsWebulous Coordinates: http://www.mountainman.com.auQuoteForTheDay:"Consciousness is never experienced in the plural, only inthe singular. How does the idea of plurality (emphaticallyopposed by the Upanishad writers arise at all? .... theonly possible alternative is simply to keep the immediateexperience that consciousness is a singular of which theplural is unkown; that there *is* only one thing and thatwhat seems to be a plurality is merely a series of differentaspects of this one thing produced by deception (the Indianmaya) - in much the same way Gaurisankar and Mt Everest turnout to be the same peak seen from different valleys."- E. Schrodinger, "What is Life"--------------------------------------------------------------------
View 18 Replies View RelatedHi,
We are building an application with
a database that contains Jobs. These Jobs have properties like Name, Code etc.
and some custom properties, definable by the application admin. For bulk import
of Jobs, we want to allow the import of an Excel sheet with the columns Name,
Code and a variable amount of columns. If the header names of these columns in
the Excel sheet match the name of a custom property in the system we want to add
the value of that cell into the database as property
value.
In our Data Flow of our Import
Package in SSIS we added an Excel Source that points to a test excel sheet with
the Name and Code columns and €“ for this example - 3 custom property columns
(Area, Department, Job Family). When we configure the Excel Source in the Excel
Source Editor, we have the option to select the Columns from the Available
External Columns table. But here lays the problem, we do not know at design
time, what custom property columns to expect. We DO expect the Name and Code
columns, but the rest is uncertain at design-time.
That raises the question: Is there
some way to select all of any incoming columns (something like a SELECT * in
T-SQL)? This looks like a big problem since it would mean that the .DTSX XML that is
being generated at design-time would need to be updated at run-time to reflect
the variability of the columns that might be encountered while reading the excel
sheet.
Then, we thought, we could add a Script
Component to our data flow that passes some kind of DataSet (or DataReader) in
which we can walk through the columns ourselves? But then still, we miss the
option to include ANY of the columns while reading an Excel sheet (or any other
datasource by the looks of it)
We are aware of the option of
optional columns in combination with the RaggedRight option, but it seems that
we would have to put all of the columns of a row in just one column and then
extract all the columns later with Derived Columns. But then, since the source
import file is being prepared by an application admin, we want don€™t want to
burden him with this horrendous task of putting everything in one
column.
We would like to have some way of
iterating through all the columns, either in a Script Component or maybe with a
Pivot/Unpivot mechanism.
Does anyone have any suggestions? Are there other options we should have considered?
I was wondering if it is possible to use a generic list object to use as the datasource/data for a report? Right now we use these business objects to display information on our website and would be great it we could re-use those objects and create reports based on them. We are doing some calculations etc in the business layer to build these objects, so if we could re-use, that would be best.
Thanks!
I need to export multiple tables from a database to multiple csv files (one for each table).
Rather than use SSIS and have multiple OLEDB sources and destinations (one for each table), is there a way to have a generic package that will export all the tables in the database ?
One way I can see is to use BCP in a loop - with the loop powered by a select statement that links to something like sys.tables etc, (or another table that i prepped with just the tables I want if I dont want them all).
i.e I would use a stored procedure that uses BCP (called via XPcmdShell) - so not via SSIS - although I could wrap up the whole thing in SSIS - but there is no realy need.
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.