存储过程我可以写在CS文件里吗? 存储过程我可以写在CS文件里吗?只要执行这个CS文件。存储过程就可以加载到数据库。有这样的写发吗? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 可以,运行时通过执行SQL语句创建存储过程 string[] spstring={ "CREATE PROCUDERE spname AS SELECT...", "CREATE PROCUDERE spname AS UPDATE..." //注意,SqlCommand中的CommandText中不能有GO,这句是C#中是不执行的.}...SqlCommand cmd=new SqlCommand;foreach(string sp in spstring){cmd.CommandText=sp;con.open();cmd.ExecuteNonQuery();con.close();...} 当然可以,CLR 存储过程。如果你的存储过程逻辑比较复杂,用 CLR 存储过程就幸福了。我在新项目里全部使用了 CLR Store Procedure逻辑处理相当灵活 贴一段代码给你看详细用法参考 SQL 文档 public class Groups { /// <summary> /// by vengair /// </summary> /// <param name="groupsInfo"></param> [SqlProcedure] public static void sp_Account_GetRelativeGroupsByGroupsInfo(string groupsInfo) { StringBuilder myBuilder = new StringBuilder(); List<string> groupInfos = new List<string>(groupsInfo.Split(';')); foreach (string groupInfo in groupInfos) { if (groupInfos.IndexOf(groupInfo) > 0) { myBuilder.AppendLine("INTERSECT"); } myBuilder.AppendLine("SELECT DISTINCT gB.*"); myBuilder.AppendLine("FROM dbo.[Account.Users] u"); myBuilder.AppendLine("\tINNER JOIN dbo.[Account.UserGroupRelations] ugA"); myBuilder.Append("\tON u.[UserId] = ugA.[UserId]"); List<string> groupInfoItems = new List<string>(groupInfo.Split('|')); int leftValue = 0; int rightValue = 0; using (SqlConnection myConnection = new SqlConnection("context connection=true")) { myConnection.Open(); SqlCommand myCommand = new SqlCommand("SELECT [LeftValue], [RightValue] FROM dbo.[Account.Groups] WHERE [GroupId] = @groupId", myConnection); myCommand.Parameters.AddWithValue("@groupId", groupInfoItems[0]); SqlDataReader myReader = myCommand.ExecuteReader(); using (myReader) { while (myReader.Read()) { leftValue = myReader.GetInt32(0); rightValue = myReader.GetInt32(1); } } } if (groupInfoItems[1] == "1") { myBuilder.AppendLine(" AND ugA.[GroupId] = '" + groupInfoItems[0] + "'"); } else { myBuilder.AppendLine(); myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] g"); myBuilder.AppendLine("\tON ugA.[GroupId] = g.[GroupId] AND g.[LeftValue] >= " + leftValue + " AND g.[RightValue] <= " + rightValue); } myBuilder.AppendLine("\tINNER JOIN dbo.[Account.UserGroupRelations] ugB"); myBuilder.AppendLine("\tON u.[UserId] = ugB.[UserId]"); myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] gA"); myBuilder.Append("\tON ugB.[GroupId] = gA.[GroupId]"); myBuilder.AppendLine(" AND gA.[GroupId] NOT IN (SELECT [GroupId] FROM dbo.[Account.Groups] g WHERE (g.[LeftValue] <= " + leftValue + " AND g.[RightValue] >= " + rightValue + ") OR (g.[LeftValue] > " + leftValue + " AND g.[RightValue] < " + rightValue + "))"); myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] gB"); myBuilder.AppendLine("\tON gA.[LeftValue] >= gB.[LeftValue] AND gA.[RightValue] <= gB.[RightValue]"); } myBuilder.AppendLine("ORDER BY [GroupName]"); using (SqlConnection myConnection = new SqlConnection("context connection=true")) { myConnection.Open(); SqlCommand myCommand = new SqlCommand(myBuilder.ToString(), myConnection); SqlContext.Pipe.ExecuteAndSend(myCommand); } } } 这段反射代码有错吗,请教各位。 this.document.write("")写到页面上汉字乱码 如何实现将纯文本转换成HTML? 新手问问踢,关于生成静态页的 如何让每次察看页面,获得的javascript程序都不一样 软件开发过程一般有几个阶段?每个阶段的作用? 高分求救! 关于Frame的问题,js版没有解决。 通宵达旦写了个数据库操作类,大家给指点一下。 SQL中的中文怎么变成了乱码? (希望有人能回答)如何用javascript读取treeview的值和其父结点的值 asp.net(vb)实现无刷新DropDownList联动???高手指教!
{
"CREATE PROCUDERE spname AS SELECT...",
"CREATE PROCUDERE spname AS UPDATE..."
//注意,SqlCommand中的CommandText中不能有GO,这句是C#中是不执行的.
}
...
SqlCommand cmd=new SqlCommand;
foreach(string sp in spstring)
{
cmd.CommandText=sp;
con.open();
cmd.ExecuteNonQuery();
con.close();
...
}
如果你的存储过程逻辑比较复杂,用 CLR 存储过程就幸福了。
我在新项目里全部使用了 CLR Store Procedure
逻辑处理相当灵活 贴一段代码给你看
详细用法参考 SQL 文档 public class Groups
{
/// <summary>
/// by vengair
/// </summary>
/// <param name="groupsInfo"></param>
[SqlProcedure]
public static void sp_Account_GetRelativeGroupsByGroupsInfo(string groupsInfo)
{
StringBuilder myBuilder = new StringBuilder(); List<string> groupInfos = new List<string>(groupsInfo.Split(';'));
foreach (string groupInfo in groupInfos)
{
if (groupInfos.IndexOf(groupInfo) > 0)
{
myBuilder.AppendLine("INTERSECT");
} myBuilder.AppendLine("SELECT DISTINCT gB.*");
myBuilder.AppendLine("FROM dbo.[Account.Users] u");
myBuilder.AppendLine("\tINNER JOIN dbo.[Account.UserGroupRelations] ugA");
myBuilder.Append("\tON u.[UserId] = ugA.[UserId]"); List<string> groupInfoItems = new List<string>(groupInfo.Split('|')); int leftValue = 0;
int rightValue = 0; using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
myConnection.Open(); SqlCommand myCommand = new SqlCommand("SELECT [LeftValue], [RightValue] FROM dbo.[Account.Groups] WHERE [GroupId] = @groupId", myConnection);
myCommand.Parameters.AddWithValue("@groupId", groupInfoItems[0]); SqlDataReader myReader = myCommand.ExecuteReader();
using (myReader)
{
while (myReader.Read())
{
leftValue = myReader.GetInt32(0);
rightValue = myReader.GetInt32(1);
}
}
} if (groupInfoItems[1] == "1")
{
myBuilder.AppendLine(" AND ugA.[GroupId] = '" + groupInfoItems[0] + "'");
}
else
{
myBuilder.AppendLine();
myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] g");
myBuilder.AppendLine("\tON ugA.[GroupId] = g.[GroupId] AND g.[LeftValue] >= " + leftValue + " AND g.[RightValue] <= " + rightValue);
} myBuilder.AppendLine("\tINNER JOIN dbo.[Account.UserGroupRelations] ugB");
myBuilder.AppendLine("\tON u.[UserId] = ugB.[UserId]");
myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] gA");
myBuilder.Append("\tON ugB.[GroupId] = gA.[GroupId]");
myBuilder.AppendLine(" AND gA.[GroupId] NOT IN (SELECT [GroupId] FROM dbo.[Account.Groups] g WHERE (g.[LeftValue] <= " + leftValue + " AND g.[RightValue] >= " + rightValue + ") OR (g.[LeftValue] > " + leftValue + " AND g.[RightValue] < " + rightValue + "))");
myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] gB");
myBuilder.AppendLine("\tON gA.[LeftValue] >= gB.[LeftValue] AND gA.[RightValue] <= gB.[RightValue]");
} myBuilder.AppendLine("ORDER BY [GroupName]"); using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
myConnection.Open(); SqlCommand myCommand = new SqlCommand(myBuilder.ToString(), myConnection); SqlContext.Pipe.ExecuteAndSend(myCommand);
}
}
}