Atomic “insert if not exists” with NHibernate

Consider an application that does something along the lines of: var user = session.QueryOver<User>().Where(x => x.Name==name).SingleOrDefault(); if (user == null) { user = new User(name); session.Save(user); } Business rules states that a user’s name must be unique, and this is backed by a UNIQUE INDEX in the database. The code above works just fine, until […]

How do I work with a comma-separated-string in SQL Server

Hy there! So we have some kind of array in our domain. eg [ 123, 234, 345 ] When we are going to query this against SQL Server, we usually transform this array to 123,234,345 (string), so that we can do something like (with string replace): SELECT * FROM [dbo].[myTable] WHERE [myColumn] IN (123,234,345) This […]

can not retrieve data from sql server

I can not retrieve data from db in sql server. I use the c3p0 as the pool,this is the c3p0.properties: c3p0.user=test c3p0.password=123456 c3p0.jdbcUrl=jdbc:sqlserver://xxxxxx:1433;databaseName=TIMDB;SelectMethod=cursor c3p0.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver c3p0.maxPoolSize=15 c3p0.testConnectionOnCheckin=true c3p0.idleConnectionTestPeriod=3600 In the sql server,I have create a new user named test,and its default db is TIMDB,the server roles is public,and this is the user mapping: But when I […]

SqlDataAdapter.Update: Are there SQL version requirements?

I know there are questions like this already, but I’ve combined all the common code to the answers and still am getting no success, so here I am. Here’s the deal. I have a block of code using SqlDataAdapter.Update to insert new rows into an existing table… string command = “SELECT * FROM ” + […]

Command to script database structure and data

Every time I want to create a script of the database structure (and data) of our deployment database (a brand new database with the software’s basic data), I have go to SSMS, select the database, Tasks -> Scripts -> Generate Scripts. Then I have a very specific setting which I have to change every single […]

Can I disable a table's primary index and re-enable when delete is finished?

When I am performing deletes from an Sql table, how would I do the following programmatically? // SqlCommand ??? // do: ALTER TABLE tbl_CustomerInfo DROP CONSTRAINT PK_tbl_CustomerInfo // …perform deletions // SqlCommand ??? // do: ALTER TABLE tbl_CustomerInfo ADD CONSTRAINT PK_tbl_CustomerInfo PRIMARY KEY CLUSTERED (RecordID) I guess my question really is can I create an […]

number of row that repeated

table tbl1 field id,name id=1 name=a id=1 name=a id=1 name=w id=1 name=f id=2 name=c id=2 name=a how can get number of field repeated. id=1 ,name=a ==>2

setting connection string

I have been building my web application with visual studio and sql server express and now I’m in the process of deploying it on a server. I need to change the connection string This is what I have: <add name=”MySiteDBConnectionString” connectionString=”Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MySiteDB.mdf;Integrated Security=True;User Instance=True” providerName=”System.Data.SqlClient” /> This is what I need to replace it with: […]

Row level permissions for entities based on roles

Using role based permissions, and say each row in a table represents an entity e.g. the Products table, each row represents a Product entity. How could you provide Product level security based on roles? e.g. the Sales group has access to Products with ID’s 1,234,432,532,34 Multiple roles can be given permissions on any given product. […]

unixODBC error IM001

I get the following error when trying to do any simple query. I just can’t figure out what’s changed. Here is an example of the sqlalchemy (Elixir) class: class Song(Entity): using_options(tablename=’tbSongs’, autoload=True) using_table_options(schema=’apra’) The database connection is ok and the classes construct ok because I can do dir(Song) and see all the correct columns. However, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.